if (colFirst colLast) list(colFirst, colLast) array(colLast, colFirst);

header pack(, record, length);

data pack(, pnn, rwAct, colAct, irefAct, cref, rwFirst, rwLast, colFirst, colLast);

this_append(header.data);

Write BIFF record EXTERNCOUNT to indicate the number of external sheet references in a worksheet.

Excel only stores references to external sheets that are used in formulas.

For simplicity we store references to all the sheets in the workbook regardless of whether they are used or not. This reduces the overall complexity and eliminates the need for a two way dialogue between the formula parser the worksheet objects.

param integer count The number of external sheet references in this worksheet function _store_externcount(count) record 0x0016; Record identifier length 0x0002; Number of bytes to follow header pack(, record, length);

data pack(, count);

this_prepend(header.data);

Writes the Excel BIFF EXTERNSHEET record. These references are used by formulas. A formula references a sheet name via an index. Since we store a reference to all of the external worksheets the EXTERNSHEET index is the same as the worksheet index.

param string sheetname The name of a external worksheet function _store_externsheet(sheetname) record 0x0017; Record identifier References to the current sheet are encoded differently to references to external sheets.

if (thisname sheetname) { sheetname "";

length 0x02; The following 2 bytes cch 1; The following byte rgch 0x02; Self reference else { length 0x02 + strlen(sheetname);

cch strlen(sheetname);

rgch 0x03; Reference to a sheet in the current workbook header pack(, record, length);

data pack(, cch, rgch);

this_prepend(header.data.sheetname);

Writes the Excel BIFF PANE record.

The panes can either be frozen or thawed (unfrozen).

Frozen panes are specified in terms of an integer number of rows and columns.

Thawed panes are specified in terms of Excel"s units for rows and columns.

param array panes This is the only parameter received and is composed of the following:

0 Vertical split position, 1 Horizontal split position 2 Top row visible 3 Leftmost column visible 4 Active pane function _store_panes(panes) y panes;

x panes;

rwTop panes;

colLeft panes;

if(count(panes) 4) { if Active pane was received pnnAct panes;

else { pnnAct NULL;

record 0x0041; Record identifier length 0x000A; Number of bytes to follow Code specific to frozen or thawed panes.

if (this_frozen) { Set default values for rwTop and colLeft if(!isset(rwTop)) { rwTop y;

if(!isset(colLeft)) { colLeft x;

else { Set default values for rwTop and colLeft if(!isset(rwTop)) { rwTop 0;

if(!isset(colLeft)) { colLeft 0;

Convert Excel"s row and column units to the internal units.

The default row height is 12.75

The default column width is 8.43

The following slope and intersection values were interpolated.

y 20y + 255;

x 113.879x + 390;

Determine which pane should be active. There is also the undocumented option to override this should it be necessary: may be removed later.

if (!isset(pnnAct)) if (x ! 0 and y ! 0)

pnnAct 0; Bottom right if (x ! 0 and y 0) pnnAct 1; Top right if (x 0 and y ! 0)

pnnAct 2; Bottom left if (x 0 and y 0) pnnAct 3; Top left this_active_pane pnnAct; Used in _store_selection header pack(, record, length);

data pack(, x, y, rwTop, colLeft, pnnAct);

this_append(header.data);

Store the page setup SETUP BIFF record.

function _store_setup() record 0x00A1; Record identifier length 0x0022; Number of bytes to follow iPaperSize this_paper_size; Paper size iScale this_print_scale; Print scaling factor iPageStart 0x01; Starting page number iFitWidth this_fit_width; Fit to number of pages wide iFitHeight this_fit_height; Fit to number of pages high grbit 0x00; Option flags iRes 0x0258; Print resolution iVRes 0x0258; Vertical print resolution numHdr this_margin_head; Header Margin numFtr this_margin_foot; Footer Margin iCopies 0x01; Number of copies fLeftToRight 0x0; Print over then down fLandscape this_orientation; Page orientation fNoPls 0x0; Setup not read from printer fNoColor 0x0; Print black and white fDraft 0x0; Print draft quality fNotes 0x0; Print notes fNoOrient 0x0; Orientation not set fUsePage 0x0; Use custom starting page grbit fLeftToRight;

---

grbit | fLandscape 1;

grbit | fNoPls 2;

grbit | fNoColor 3;

grbit | fDraft 4;

grbit | fNotes 5;

grbit | fNoOrient 6;

grbit | fUsePage 7;

numHdr pack(, numHdr);

numFtr pack(, numFtr);

if (this_byte_order) if it"s Big Endian numHdr strrev(numHdr);

numFtr strrev(numFtr);

header pack(, record, length);

data1 pack(, iPaperSize, iScale, iPageStart, iFitWidth, iFitHeight, grbit, iRes, iVRes);

data2 numHdr.numFtr;

data3 pack(, iCopies);

this_prepend(header.data1.data2.data3);

Store the header caption BIFF record.

function store_header() record 0x0014; Record identifier str this_header; header string cch strlen(str); Length of header string length 1 + cch; Bytes to follow header pack(, record, length);

data pack(, cch);

this_append(header.data.str);

Store the footer caption BIFF record.

function store_footer() record 0x0015; Record identifier str this_footer; Footer string cch strlen(str); Length of footer string length 1 + cch; Bytes to follow header pack(, record, length);

data pack(, cch);

this_append(header.data.str);

Store the horizontal centering HCENTER BIFF record.

function store_hcenter() record 0x0083; Record identifier length 0x0002; Bytes to follow fHCenter this_hcenter; Horizontal centering header pack(, record, length);

data pack(, fHCenter);

this_append(header.data);

Store the vertical centering VCENTER BIFF record.

function store_vcenter() record 0x0084; Record identifier length 0x0002; Bytes to follow fVCenter this_vcenter; Horizontal centering header pack(, record, length);

data pack(, fVCenter);

this_append(header.data);

Store the LEFTMARGIN BIFF record.

function _store_margin_left() record 0x0026; Record identifier length 0x0008; Bytes to follow margin this_margin_left; Margin in inches header pack(, record, length);

data pack(, margin);

if (this_byte_order) if it"s Big Endian { data strrev(data);

this_append(header.data);

Store the RIGHTMARGIN BIFF record.

function _store_margin_right() record 0x0027; Record identifier length 0x0008; Bytes to follow margin this_margin_right; Margin in inches header pack(, record, length);

data pack(, margin);

if (this_byte_order) if it"s Big Endian { data strrev(data);

this_append(header.data);

Store the TOPMARGIN BIFF record.

function _store_margin_top() record 0x0028; Record identifier length 0x0008; Bytes to follow margin this_margin_top; Margin in inches header pack(, record, length);

data pack(, margin);

if (this_byte_order) if it"s Big Endian { data strrev(data);

this_append(header.data);

Store the BOTTOMMARGIN BIFF record.

function _store_margin_bottom() record 0x0029; Record identifier length 0x0008; Bytes to follow margin this_margin_bottom; Margin in inches header pack(, record, length);

data pack(, margin);

if (this_byte_order) if it"s Big Endian { data strrev(data);

this_append(header.data);

This is an Excel972000 method. It is required to perform more complicated merging than the normal set_align("merge"). It merges the area given by its arguments.

access public param integer first_row First row of the area to merge param integer first_col First column of the area to merge param integer last_row Last row of the area to merge param integer last_col Last column of the area to merge function merge_cells(first_row, first_col, last_row, last_col) record 0x00E5; Record identifier length 0x000A; Bytes to follow cref 1; Number of refs Swap last rowcol for first rowcol as necessary if (first_row last_row) { list(first_row, last_row) array(last_row, first_row);

if (first_col last_col) { list(first_col, last_col) array(last_col, first_col);

header pack(, record, length);

data pack(, cref, first_row, last_row, first_col, last_col);

this_append(header.data);

Write the PRINTHEADERS BIFF record.

function _store_print_headers() record 0x002a; Record identifier length 0x0002; Bytes to follow fPrintRwCol this_print_headers; Boolean flag header pack(, record, length);

data pack(, fPrintRwCol);

this_prepend(header.data);

Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the GRIDSET record.

function _store_print_gridlines() record 0x002b; Record identifier length 0x0002; Bytes to follow fPrintGrid this_print_gridlines; Boolean flag header pack(, record, length);

---

data pack(, fPrintGrid);

this_prepend(header.data);

Write the GRIDSET BIFF record. Must be used in conjunction with the PRINTGRIDLINES record.

function _store_gridset() record 0x0082; Record identifier length 0x0002; Bytes to follow fGridSet !(this_print_gridlines); Boolean flag header pack(, record, length);

data pack(, fGridSet);

this_prepend(header.data);

Write the WSBOOL BIFF record, mainly for fittopage. Used in conjunction with the SETUP record.

function _store_wsbool() record 0x0081; Record identifier length 0x0002; Bytes to follow The only option that is of interest is the flag for fit to page. So we set all the options in one go.

if (this_fit_page) { grbit 0x05c1;

else { grbit 0x04c1;

header pack(, record, length);

data pack(, grbit);

this_prepend(header.data);

Write the HORIZONTALPAGEBREAKS BIFF record.

function _store_hbreak() Return if the user hasn"t specified pagebreaks if(empty(this_hbreaks)) { return;

Sort and filter array of page breaks breaks this_hbreaks;

sort(breaks,SORT_NUMERIC);

if(breaks 0) { don"t use first break if it"s 0 array_shift(breaks);

record 0x001b; Record identifier cbrk count(breaks); Number of page breaks length (cbrk + 1) 2; Bytes to follow header pack(, record, length);

data pack(, cbrk);

Append each page break foreach(breaks as break) { data. pack(, break);

this_prepend(header.data);

Write the VERTICALPAGEBREAKS BIFF record.

function _store_vbreak() Return if the user hasn"t specified pagebreaks if(empty(this_vbreaks)) { return;

1000 vertical pagebreaks appears to be an internal Excel 5 limit.

It is slightly higher in Excel 97200, approx. 1026

breaks array_slice(this_vbreaks,0,1000);

Sort and filter array of page breaks sort(breaks,SORT_NUMERIC);

if(breaks 0) { don"t use first break if it"s 0 array_shift(breaks);

record 0x001a; Record identifier cbrk count(breaks); Number of page breaks length (cbrk + 1) 2; Bytes to follow header pack(, record, length);

data pack(, cbrk);

Append each page break foreach (breaks as break) { data. pack(, break);

this_prepend(header.data);

Set the Biff PROTECT record to indicate that the worksheet is protected.

function _store_protect() Exit unless sheet protection has been specified if(this_protect 0) { return;

record 0x0012; Record identifier length 0x0002; Bytes to follow fLock this_protect; Worksheet is protected header pack(, record, length);

data pack(, fLock);

this_prepend(header.data);

Write the worksheet PASSWORD record.

function _store_password() Exit unless sheet protection and password have been specified if((this_protect 0) or (!isset(this_password))) { return;

record 0x0013; Record identifier length 0x0002; Bytes to follow wPassword this_password; Encoded password header pack(, record, length);

data pack(, wPassword);

this_prepend(header.data);

Insert a 24bit bitmap image in a worksheet. The main record required is IMDATA but it must be proceeded by a OBJ record to define its position.

access public param integer row The row we are going to insert the bitmap into param integer col The column we are going to insert the bitmap into param string bitmap The bitmap filename param integer x The horizontal position (offset) of the image inside the cell.

param integer y The vertical position (offset) of the image inside the cell.

param integer scale_x The horizontal scale param integer scale_y The vertical scale function insert_bitmap(row, col, bitmap, x 0, y 0, scale_x 1, scale_y 1) list(width, height, size, data) this_process_bitmap(bitmap);

Scale the frame of the image.

width scale_x;

height scale_y;

Calculate the vertices of the image and write the OBJ record this_position_image(col, row, x, y, width, height);

Write the IMDATA record to store the bitmap data record 0x007f;

length 8 + size;

cf 0x09;

env 0x01;

lcb size;

header pack(, record, length, cf, env, lcb);

this_append(header.data);

Calculate the vertices that define the position of the image as required by the OBJ record.

+++

| A | B | ++++ | |(x1,y1) | | | 1 |(A1)._______|______ | | | | | | | | | | | ++| BITMAP |+ | | | | | | 2 | |______________. | | | | (B2)| | | | (x2,y2)| + +++ Example of a bitmap that covers some of the area from cell A1 to cell B2.

---

Based on the width and height of the bitmap we need to calculate 8 vars:

col_start, row_start, col_end, row_end, x1, y1, x2, y2.

The width and height of the cells are also variable and have to be taken into account.

The values of col_start and row_start are passed in from the calling function. The values of col_end and row_end are calculated by subtracting the width and height of the bitmap from the width and height of the underlying cells.

The vertices are expressed as a percentage of the underlying cell width as follows (rhs values are in pixels):

x1 X W 1024 y1 Y H 256 x2 (X1) W 1024 y2 (Y1) H 256 Where: X is distance from the left side of the underlying cell Y is distance from the top of the underlying cell W is the width of the cell H is the height of the cell note the SDK incorrectly states that the height should be expressed as a percentage of 1024.

param integer col_start Col containing upper left corner of object param integer row_start Row containing top left corner of object param integer x1 Distance to left side of object param integer y1 Distance to top of object param integer width Width of image frame param integer height Height of image frame function _position_image(col_start, row_start, x1, y1, width, height) Initialise end cell to the same as the start cell col_end col_start; Col containing lower right corner of object row_end row_start; Row containing bottom right corner of object Zero the specified offset if greater than the cell dimensions if (x1 thissize_col(col_start)) x1 0;

if (y1 thissize_row(row_start)) y1 0;

width width + x1 1;

height height + y1 1;

Subtract the underlying cell widths to find the end cell of the image while (width thissize_col(col_end)) { width thissize_col(col_end);

col_end++;

Subtract the underlying cell heights to find the end cell of the image while (height thissize_row(row_end)) { height thissize_row(row_end);

row_end++;

Bitmap isn"t allowed to start or finish in a hidden cell, i.e. a cell with zero eight or width.

if (thissize_col(col_start) 0) return;

if (thissize_col(col_end) 0) return;

if (thissize_row(row_start) 0) return;

if (thissize_row(row_end) 0) return;

Convert the pixel values to the percentage value expected by Excel x1 x1 thissize_col(col_start) 1024;

y1 y1 thissize_row(row_start) 256;

x2 width thissize_col(col_end) 1024; Distance to right side of object y2 height thissize_row(row_end) 256; Distance to bottom of object this_store_obj_picture( col_start, x1, row_start, y1, col_end, x2, row_end, y2 );

Convert the width of a cell from user"s units to pixels. By interpolation the relationship is: y 7x +5. If the width hasn"t been set by the user we use the default value. If the col is hidden we use a value of zero.

param integer col The column return integer The width in pixels function size_col(col) Look up the cell value to see if it has been changed if (isset(thiscol_sizes)) { if (thiscol_sizes 0) { return(0);

else { return(floor(7 thiscol_sizes + 5));

else { return(64);

Convert the height of a cell from user"s units to pixels. By interpolation the relationship is: y 43x. If the height hasn"t been set by the user we use the default value. If the row is hidden we use a value of zero. (Not possible to hide row yet).

param integer row The row return integer The width in pixels function size_row(row) Look up the cell value to see if it has been changed if (isset(thisrow_sizes)) { if (thisrow_sizes 0) { return(0);

else { return(floor(43 thisrow_sizes));

else { return(17);

Store the OBJ record that precedes an IMDATA record. This could be generalise to support other Excel objects.

param integer colL Column containing upper left corner of object param integer dxL Distance from left side of cell param integer rwT Row containing top left corner of object param integer dyT Distance from top of cell param integer colR Column containing lower right corner of object param integer dxR Distance from right of cell param integer rwB Row containing bottom right corner of object param integer dyB Distance from bottom of cell function _store_obj_picture(colL,dxL,rwT,dyT,colR,dxR,rwB,dyB) record 0x005d; Record identifier length 0x003c; Bytes to follow cObj 0x0001; Count of objects in file (set to 1) OT 0x0008; Object type. 8 Picture id 0x0001; Object ID grbit 0x0614; Option flags cbMacro 0x0000; Length of FMLA structure Reserved1 0x0000; Reserved Reserved2 0x0000; Reserved icvBack 0x09; Background colour icvFore 0x09; Foreground colour fls 0x00; Fill pattern fAuto 0x00; Automatic fill icv 0x08; Line colour lns 0xff; Line style lnw 0x01; Line weight fAutoB 0x00; Automatic border frs 0x0000; Frame style cf 0x0009; Image format, 9 bitmap Reserved3 0x0000; Reserved cbPictFmla 0x0000; Length of FMLA structure Reserved4 0x0000; Reserved grbit2 0x0001; Option flags Reserved5 0x0000; Reserved header pack(, record, length);

---

data pack(, cObj);

data. pack(, OT);

data. pack(, id);

data. pack(, grbit);

data. pack(, colL);

data. pack(, dxL);

data. pack(, rwT);

data. pack(, dyT);

data. pack(, colR);

data. pack(, dxR);

data. pack(, rwB);

data. pack(, dyB);

data. pack(, cbMacro);

data. pack(, Reserved1);

data. pack(, Reserved2);

data. pack(, icvBack);

data. pack(, icvFore);

data. pack(, fls);

data. pack(, fAuto);

data. pack(, icv);

data. pack(, lns);

data. pack(, lnw);

data. pack(, fAutoB);

data. pack(, frs);

data. pack(, cf);

data. pack(, Reserved3);

data. pack(, cbPictFmla);

data. pack(, Reserved4);

data. pack(, grbit2);

data. pack(, Reserved5);

this_append(header.data);

Convert a 24 bit bitmap into the modified internal format used by Windows.

This is described in BITMAPCOREHEADER and BITMAPCOREINFO structures in the MSDN library.

param string bitmap The bitmap to process return array Array with data and properties of the bitmap function _process_bitmap(bitmap) Open file.

bmp_fd fopen(bitmap,);

if (!bmp_fd) { die("Couldn"t import bitmap");

Slurp the file into a string.

data fread(bmp_fd, filesize(bitmap));

Check that the file is big enough to be a bitmap.

if (strlen(data) 0x36) { die("bitmap doesn"t contain enough data.\n");

The first 2 bytes are used to identify the bitmap.

identity unpack(, data);

if (identity ! ) { die("bitmap doesn"t appear to be a valid bitmap image.\n");

Remove bitmap data: ID.

data substr(data, 2);

Read and remove the bitmap size. This is more reliable than reading the data size at offset 0x22.

size_array unpack(, substr(data, 0, 4));

size size_array;

data substr(data, 4);

size 0x36; Subtract size of bitmap header.

size + 0x0C; Add size of BIFF header.

Remove bitmap data: reserved, offset, header length.

data substr(data, 12);

Read and remove the bitmap width and height. Verify the sizes.

width_and_height unpack(, substr(data, 0, 8));

width width_and_height;

height width_and_height;

data substr(data, 8);

if (width 0xFFFF) { die("bitmap: largest image width supported is 65k.\n");

if (height 0xFFFF) { die("bitmap: largest image height supported is 65k.\n");

Read and remove the bitmap planes and bpp data. Verify them.

planes_and_bitcount unpack(, substr(data, 0, 4));

data substr(data, 4);

if (planes_and_bitcount ! 24) { Bitcount die("bitmap isn"t a 24bit true color bitmap.\n");

if (planes_and_bitcount ! 1) { die("bitmap: only 1 plane supported in bitmap image.\n");

Read and remove the bitmap compression. Verify compression.

compression unpack(, substr(data, 0, 4));

data substr(data, 4);

compression 0;

if (compression ! 0) { die("bitmap: compression not supported in bitmap image.\n");

Remove bitmap data: data size, hres, vres, colours, imp. colours.

data substr(data, 20);

Add the BITMAPCOREHEADER data header pack(, 0x000c, width, height, 0x01, 0x18);

data header. data;

return (array(width, height, size, data));

Store the window zoom factor. This should be a reduced fraction but for simplicity we will store all fractions with a numerator of 100.

function _store_zoom() If scale is 100 we don"t need to write a record if (this_zoom 100) { return;

record 0x00A0; Record identifier length 0x0004; Bytes to follow header pack(, record, length);

data pack(, this_zoom, 100);

this_append(header.data);

1. Harrison E. Frank. Сущность управленческого решения.- http://www.e-xecutive.ru/publications/aspects/decision/article_964/ ↑

2. Лившиц А. Управленческие решения. – М.: Кнорус, 2009. – 248 с. ↑

3. Управление организацией: Учебник / Под ред. А.Г. Поршнева, З.П. Румянцевой, Н.А. Саломатина. - 2-е изд , перераб. и доп. - М.: ИНФРА-М, 2009. - 669 с. ↑

4. Кулагин О. Принятие решений в организациях. - СПб.: издательский дом «Сентябрь», 2001 ↑

5. Кулагин О. Принятие решений в организациях. - СПб.: издательский дом «Сентябрь», 2001 ↑

---

Смотрите также файлы

• Моделирование предметной области «Управление домашними финансами» с помощью UML..pdf

• Проектирование интерфейса программ.pdf

• Роль организационной культуры в стратегическом управлении.pdf

• Развитие человеческих ресурсов. Классификация методов обучения.pdf

• Сущность толкающих и тянущих систем управления материальными потоками.pdf