Class | Spreadsheet::Excel::Writer::Worksheet |
In: |
lib/spreadsheet/excel/writer/worksheet.rb
|
Parent: | Object |
worksheet | [R] |
# File lib/spreadsheet/excel/writer/worksheet.rb, line 18 18: def initialize workbook, worksheet 19: @workbook = workbook 20: @worksheet = worksheet 21: @io = StringIO.new '' 22: @biff_version = 0x0600 23: @bof = 0x0809 24: @build_id = 3515 25: @build_year = 1996 26: @bof_types = { 27: :globals => 0x0005, 28: :visual_basic => 0x0006, 29: :worksheet => 0x0010, 30: :chart => 0x0020, 31: :macro_sheet => 0x0040, 32: :workspace => 0x0100, 33: } 34: end
The number of bytes needed to write a Boundsheet record for this Worksheet Used by Writer::Worksheet to calculate various offsets.
# File lib/spreadsheet/excel/writer/worksheet.rb, line 38 38: def boundsheet_size 39: name.size + 10 40: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 41 41: def data 42: @io.rewind 43: @io.read 44: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 45 45: def encode_date date 46: return date if date.is_a? Numeric 47: if date.is_a? Time 48: date = DateTime.new date.year, date.month, date.day, 49: date.hour, date.min, date.sec 50: end 51: base = @workbook.date_base 52: value = date - base 53: if LEAP_ERROR > base 54: value += 1 55: end 56: value 57: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 58 58: def encode_rk value 59: # Bit Mask Contents 60: # 0 0x00000001 0 = Value not changed 1 = Value is multiplied by 100 61: # 1 0x00000002 0 = Floating-point value 1 = Signed integer value 62: # 31-2 0xFFFFFFFC Encoded value 63: cent = 0 64: int = 2 65: higher = value * 100 66: if higher.is_a?(Float) && higher < 0xfffffffc 67: cent = 1 68: if higher == higher.to_i 69: value = higher.to_i 70: else 71: value = higher 72: end 73: end 74: if value.is_a?(Integer) 75: ## although not documented as signed, 'V' appears to correctly pack 76: # negative numbers. 77: value <<= 2 78: else 79: # FIXME: precision of small numbers 80: int = 0 81: value, = [value].pack(EIGHT_BYTE_DOUBLE).unpack('x4V') 82: value &= 0xfffffffc 83: end 84: value | cent | int 85: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 86 86: def name 87: unicode_string @worksheet.name 88: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 89 89: def need_number? cell 90: if cell.is_a?(Numeric) && cell.abs > 0x1fffffff 91: true 92: elsif cell.is_a?(Float) 93: higher = cell * 100 94: if higher == higher.to_i 95: need_number? higher.to_i 96: else 97: test1, test2 = [cell * 100].pack(EIGHT_BYTE_DOUBLE).unpack('V2') 98: test1 > 0 || need_number?(test2) 99: end 100: else 101: false 102: end 103: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 104 104: def row_blocks 105: # All cells in an Excel document are divided into blocks of 32 consecutive 106: # rows, called Row Blocks. The first Row Block starts with the first used 107: # row in that sheet. Inside each Row Block there will occur ROW records 108: # describing the properties of the rows, and cell records with all the cell 109: # contents in this Row Block. 110: blocks = [] 111: @worksheet.reject do |row| row.empty? end.each_with_index do |row, idx| 112: blocks << [] if idx % 32 == 0 113: blocks.last << row 114: end 115: blocks 116: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 120 120: def strings 121: @worksheet.inject [] do |memo, row| 122: strings = row.select do |cell| cell.is_a?(String) && !cell.empty? end 123: memo.concat strings 124: end 125: end
Write a blank cell
# File lib/spreadsheet/excel/writer/worksheet.rb, line 128 128: def write_blank row, idx 129: write_cell :blank, row, idx 130: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 131 131: def write_bof 132: data = [ 133: @biff_version, # BIFF version (always 0x0600 for BIFF8) 134: 0x0010, # Type of the following data: 135: # 0x0005 = Workbook globals 136: # 0x0006 = Visual Basic module 137: # 0x0010 = Worksheet 138: # 0x0020 = Chart 139: # 0x0040 = Macro sheet 140: # 0x0100 = Workspace file 141: @build_id, # Build identifier 142: @build_year, # Build year 143: 0x000, # File history flags 144: 0x006, # Lowest Excel version that can read 145: # all records in this file 146: ] 147: write_op @bof, data.pack("v4V2") 148: end
Write a cell with a Boolean or Error value
# File lib/spreadsheet/excel/writer/worksheet.rb, line 151 151: def write_boolerr row, idx 152: value = row[idx] 153: type = 0 154: numval = 0 155: if value.is_a? Error 156: type = 1 157: numval = value.code 158: elsif value 159: numval = 1 160: end 161: data = [ 162: numval, # Boolean or error value (type depends on the following byte) 163: type # 0 = Boolean value; 1 = Error code 164: ] 165: write_cell :boolerr, row, idx, *data 166: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 167 167: def write_calccount 168: count = 100 # Maximum number of iterations allowed in circular references 169: write_op 0x000c, [count].pack('v') 170: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 171 171: def write_cell type, row, idx, *args 172: xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx) 173: data = [ 174: row.idx, # Index to row 175: idx, # Index to column 176: xf_idx, # Index to XF record (➜ 6.115) 177: ].concat args 178: write_op opcode(type), data.pack(binfmt(type)) 179: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 180 180: def write_cellblocks row 181: # BLANK ➜ 6.7 182: # BOOLERR ➜ 6.10 183: # INTEGER ➜ 6.56 (BIFF2 only) 184: # LABEL ➜ 6.59 (BIFF2-BIFF7) 185: # LABELSST ➜ 6.61 (BIFF8 only) 186: # MULBLANK ➜ 6.64 (BIFF5-BIFF8) 187: # MULRK ➜ 6.65 (BIFF5-BIFF8) 188: # NUMBER ➜ 6.68 189: # RK ➜ 6.82 (BIFF3-BIFF8) 190: # RSTRING ➜ 6.84 (BIFF5/BIFF7) 191: multiples, first_idx = nil 192: row = row.formatted 193: row.each_with_index do |cell, idx| 194: cell = nil if cell == '' 195: ## it appears that there are limitations to RK precision, both for 196: # Integers and Floats, that lie well below 2^30 significant bits, or 197: # Ruby's Bignum threshold. In that case we'll just write a Number 198: # record 199: need_number = need_number? cell 200: if multiples && (!multiples.last.is_a?(cell.class) || need_number) 201: write_multiples row, first_idx, multiples 202: multiples, first_idx = nil 203: end 204: nxt = idx + 1 205: case cell 206: when NilClass 207: if multiples 208: multiples.push cell 209: elsif nxt < row.size && row[nxt].nil? 210: multiples = [cell] 211: first_idx = idx 212: else 213: write_blank row, idx 214: end 215: when TrueClass, FalseClass, Error 216: write_boolerr row, idx 217: when String 218: write_labelsst row, idx 219: when Numeric 220: ## RK encodes Floats with 30 significant bits, which is a bit more than 221: # 10^9. Not sure what is a good rule of thumb here, but it seems that 222: # Decimal Numbers with more than 4 significant digits are not represented 223: # with sufficient precision by RK 224: if need_number 225: write_number row, idx 226: elsif multiples 227: multiples.push cell 228: elsif nxt < row.size && row[nxt].is_a?(Numeric) 229: multiples = [cell] 230: first_idx = idx 231: else 232: write_rk row, idx 233: end 234: when Formula 235: write_formula row, idx 236: when Date, Time 237: write_number row, idx 238: end 239: end 240: write_multiples row, first_idx, multiples if multiples 241: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 242 242: def write_changes reader, endpos, sst_status 243: reader.seek @worksheet.offset 244: blocks = row_blocks 245: lastpos = reader.pos 246: offsets = {} 247: row_offsets = [] 248: changes = @worksheet.changes 249: @worksheet.offsets.each do |key, pair| 250: if changes.include?(key) \ 251: || (sst_status == :complete_update && key.is_a?(Integer)) 252: offsets.store pair, key 253: end 254: end 255: ## FIXME it may be smarter to simply write all rowblocks, instead of doing a 256: # song-and-dance routine for every row... 257: work = offsets.invert 258: work.each do |key, (pos, len)| 259: case key 260: when Integer 261: row_offsets.push [key, [pos, len]] 262: when :dimensions 263: row_offsets.push [-1, [pos, len]] 264: end 265: end 266: row_offsets.sort! 267: row_offsets.reverse! 268: control = changes.size 269: @worksheet.each do |row| 270: key = row.idx 271: if changes.include?(key) && !work.include?(key) 272: row, pair = row_offsets.find do |idx, _| idx <= key end 273: work.store key, pair 274: end 275: end 276: if changes.size > control 277: warn "Your Worksheet was modified while it was being written. This should not happen.\nPlease contact the author (hannes dot wyss at gmail dot com) with a sample file\nand minimal code that generates this warning. Thanks!\n" 278: end 279: work = work.sort_by do |key, (pos, len)| 280: [pos, key.is_a?(Integer) ? key : -1] 281: end 282: work.each do |key, (pos, len)| 283: @io.write reader.read(pos - lastpos) if pos > lastpos 284: if key.is_a?(Integer) 285: if block = blocks.find do |rows| rows.any? do |row| row.idx == key end end 286: write_rowblock block 287: blocks.delete block 288: end 289: else 290: send "write_#{key}" 291: end 292: lastpos = pos + len 293: reader.seek lastpos 294: end 295: @io.write reader.read(endpos - lastpos) 296: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 302 302: def write_colinfo bunch 303: col = bunch.first 304: width = col.width.to_f * 256 305: xf_idx = @workbook.xf_index @worksheet.workbook, col.default_format 306: opts = 0 307: opts |= 0x0001 if col.hidden? 308: opts |= col.outline_level.to_i << 8 309: opts |= 0x1000 if col.collapsed? 310: data = [ 311: col.idx, # Index to first column in the range 312: bunch.last.idx, # Index to last column in the range 313: width.to_i, # Width of the columns in 1/256 of the width of the zero 314: # character, using default font (first FONT record in the 315: # file) 316: xf_idx.to_i, # Index to XF record (➜ 6.115) for default column formatting 317: opts, # Option flags: 318: # Bits Mask Contents 319: # 0 0x0001 1 = Columns are hidden 320: # 10-8 0x0700 Outline level of the columns 321: # (0 = no outline) 322: # 12 0x1000 1 = Columns are collapsed 323: ] 324: write_op opcode(:colinfo), data.pack(binfmt(:colinfo)) 325: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 326 326: def write_colinfos 327: cols = @worksheet.columns 328: bunch = [] 329: cols.each_with_index do |column, idx| 330: if column 331: bunch << column 332: if cols[idx.next] != column 333: write_colinfo bunch 334: bunch.clear 335: end 336: end 337: end 338: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 339 339: def write_defaultrowheight 340: data = [ 341: 0x00, # Option flags: 342: # Bit Mask Contents 343: # 0 0x01 1 = Row height and default font height do not match 344: # 1 0x02 1 = Row is hidden 345: # 2 0x04 1 = Additional space above the row 346: # 3 0x08 1 = Additional space below the row 347: 0xf2, # Default height for unused rows, in twips = 1/20 of a point 348: ] 349: write_op 0x0225, data.pack('v2') 350: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 351 351: def write_defcolwidth 352: # Offset Size Contents 353: # 0 2 Column width in characters, using the width of the zero 354: # character from default font (first FONT record in the 355: # file). Excel adds some extra space to the default width, 356: # depending on the default font and default font size. The 357: # algorithm how to exactly calculate the resulting column 358: # width is not known. 359: # 360: # Example: The default width of 8 set in this record results 361: # in a column width of 8.43 using Arial font with a size of 362: # 10 points. 363: write_op 0x0055, [8].pack('v') 364: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 365 365: def write_dimensions 366: # Offset Size Contents 367: # 0 4 Index to first used row 368: # 4 4 Index to last used row, increased by 1 369: # 8 2 Index to first used column 370: # 10 2 Index to last used column, increased by 1 371: # 12 2 Not used 372: write_op 0x0200, @worksheet.dimensions.pack(binfmt(:dimensions)) 373: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 374 374: def write_eof 375: write_op 0x000a 376: end
Write a cell with a Formula. May write an additional String record depending on the stored result of the Formula.
# File lib/spreadsheet/excel/writer/worksheet.rb, line 380 380: def write_formula row, idx 381: xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx) 382: cell = row[idx] 383: data1 = [ 384: row.idx, # Index to row 385: idx, # Index to column 386: xf_idx, # Index to XF record (➜ 6.115) 387: ].pack 'v3' 388: data2 = nil 389: case value = cell.value 390: when Numeric # IEEE 754 floating-point value (64-bit double precision) 391: data2 = [value].pack EIGHT_BYTE_DOUBLE 392: when String 393: data2 = [ 394: 0x00, # (identifier for a string value) 395: 0xffff, # 396: ].pack 'Cx5v' 397: when true, false 398: value = value ? 1 : 0 399: data2 = [ 400: 0x01, # (identifier for a Boolean value) 401: value, # 0 = FALSE, 1 = TRUE 402: 0xffff, # 403: ].pack 'CxCx3v' 404: when Error 405: data2 = [ 406: 0x02, # (identifier for an error value) 407: value.code, # Error code 408: 0xffff, # 409: ].pack 'CxCx3v' 410: when nil 411: data2 = [ 412: 0x03, # (identifier for an empty cell) 413: 0xffff, # 414: ].pack 'Cx5v' 415: else 416: data2 = [ 417: 0x02, # (identifier for an error value) 418: 0x2a, # Error code: #N/A! Argument or function not available 419: 0xffff, # 420: ].pack 'CxCx3v' 421: end 422: opts = 0x03 423: opts |= 0x08 if cell.shared 424: data3 = [ 425: opts # Option flags: 426: # Bit Mask Contents 427: # 0 0x0001 1 = Recalculate always 428: # 1 0x0002 1 = Calculate on open 429: # 3 0x0008 1 = Part of a shared formula 430: ].pack 'vx4' 431: write_op opcode(:formula), data1, data2, data3, cell.data 432: if cell.value.is_a?(String) 433: write_op opcode(:string), unicode_string(cell.value, 2) 434: end 435: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 438 438: def write_from_scratch 439: # ● BOF Type = worksheet (➜ 5.8) 440: write_bof 441: # ○ UNCALCED ➜ 5.105 442: # ○ INDEX ➜ 4.7 (Row Blocks), ➜ 5.59 443: # ○ Calculation Settings Block ➜ 4.3 444: write_calccount 445: write_refmode 446: write_iteration 447: write_saverecalc 448: # ○ PRINTHEADERS ➜ 5.81 449: # ○ PRINTGRIDLINES ➜ 5.80 450: # ○ GRIDSET ➜ 5.52 451: # ○ GUTS ➜ 5.53 452: # ○ DEFAULTROWHEIGHT ➜ 5.31 453: write_defaultrowheight 454: # ○ WSBOOL ➜ 5.113 455: write_wsbool 456: # ○ Page Settings Block ➜ 4.4 457: # ○ Worksheet Protection Block ➜ 4.18 458: # ○ DEFCOLWIDTH ➜ 5.32 459: write_defcolwidth 460: # ○○ COLINFO ➜ 5.18 461: write_colinfos 462: # ○ SORT ➜ 5.99 463: # ● DIMENSIONS ➜ 5.35 464: write_dimensions 465: # ○○ Row Blocks ➜ 4.7 466: write_rows 467: # ● Worksheet View Settings Block ➜ 4.5 468: # ● WINDOW2 ➜ 5.110 469: write_window2 470: # ○ SCL ➜ 5.92 (BIFF4-BIFF8 only) 471: # ○ PANE ➜ 5.75 472: # ○○ SELECTION ➜ 5.93 473: # ○ STANDARDWIDTH ➜ 5.101 474: # ○○ MERGEDCELLS ➜ 5.67 475: # ○ LABELRANGES ➜ 5.64 476: # ○ PHONETIC ➜ 5.77 477: # ○ Conditional Formatting Table ➜ 4.12 478: # ○ Hyperlink Table ➜ 4.13 479: write_hyperlink_table 480: # ○ Data Validity Table ➜ 4.14 481: # ○ SHEETLAYOUT ➜ 5.96 (BIFF8X only) 482: # ○ SHEETPROTECTION Additional protection, ➜ 5.98 (BIFF8X only) 483: # ○ RANGEPROTECTION Additional protection, ➜ 5.84 (BIFF8X only) 484: # ● EOF ➜ 5.36 485: write_eof 486: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 487 487: def write_hlink row, col, link 488: # FIXME: only Hyperlinks are supported at present. 489: cell_range = [ 490: row, row, # Cell range address of all cells containing this hyperlink 491: col, col, # (➜ 3.13.1) 492: ].pack 'v4' 493: guid = [ 494: # GUID of StdLink: 495: # D0 C9 EA 79 F9 BA CE 11 8C 82 00 AA 00 4B A9 0B 496: # (79EAC9D0-BAF9-11CE-8C82-00AA004BA90B) 497: "d0c9ea79f9bace118c8200aa004ba90b", 498: ].pack 'H32' 499: opts = 0x01 500: opts |= 0x02 501: opts |= 0x14 unless link == link.url 502: opts |= 0x08 if link.fragment 503: opts |= 0x80 if link.target_frame 504: # TODO: UNC support 505: options = [ 506: 2, # Unknown value: 0x00000002 507: opts, # Option flags 508: # Bit Mask Contents 509: # 0 0x00000001 0 = No link extant 510: # 1 = File link or URL 511: # 1 0x00000002 0 = Relative file path 512: # 1 = Absolute path or URL 513: # 2 and 4 0x00000014 0 = No description 514: # 1 (both bits) = Description 515: # 3 0x00000008 0 = No text mark 516: # 1 = Text mark 517: # 7 0x00000080 0 = No target frame 518: # 1 = Target frame 519: # 8 0x00000100 0 = File link or URL 520: # 1 = UNC path (incl. server name) 521: 522: ].pack('V2') 523: tail = [] 524: ## call internal to get the correct internal encoding in Ruby 1.9 525: nullstr = internal "\000" 526: unless link == link.url 527: desc = internal(link).dup << nullstr 528: tail.push [desc.size / 2].pack('V'), desc 529: end 530: if link.target_frame 531: frme = internal(link.target_frame).dup << nullstr 532: tail.push [frme.size / 2].pack('V'), frme 533: end 534: url = internal(link.url).dup << nullstr 535: tail.push [ 536: # 6.53.2 Hyperlink containing a URL (Uniform Resource Locator) 537: # These data fields occur for links which are not local files or files 538: # in the local network (for instance HTTP and FTP links and e-mail 539: # addresses). The lower 9 bits of the option flags field must be 540: # 0.x00x.xx112 (x means optional, depending on hyperlink content). The 541: # GUID could be used to distinguish a URL from a file link. 542: # GUID of URL Moniker: 543: # E0 C9 EA 79 F9 BA CE 11 8C 82 00 AA 00 4B A9 0B 544: # (79EAC9E0-BAF9-11CE-8C82-00AA004BA90B) 545: 'e0c9ea79f9bace118c8200aa004ba90b', 546: url.size # Size of character array of the URL, including trailing zero 547: # word (us). There are us/2-1 characters in the following 548: # string. 549: ].pack('H32V'), url 550: if link.fragment 551: frag = internal(link.fragment).dup << nullstr 552: tail.push [frag.size / 2].pack('V'), frag 553: end 554: write_op opcode(:hlink), cell_range, guid, options, *tail 555: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 556 556: def write_hyperlink_table 557: # TODO: theoretically it's possible to write fewer records by combining 558: # identical neighboring links in cell-ranges 559: links = [] 560: @worksheet.each do |row| 561: row.each_with_index do |cell, idx| 562: if cell.is_a? Link 563: write_hlink row.idx, idx, cell 564: end 565: end 566: end 567: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 568 568: def write_iteration 569: its = 0 # 0 = Iterations off; 1 = Iterations on 570: write_op 0x0011, [its].pack('v') 571: end
Write a cell with a String value. The String must have been stored in the Shared String Table.
# File lib/spreadsheet/excel/writer/worksheet.rb, line 575 575: def write_labelsst row, idx 576: write_cell :labelsst, row, idx, @workbook.sst_index(self, row[idx]) 577: end
Write multiple consecutive blank cells.
# File lib/spreadsheet/excel/writer/worksheet.rb, line 580 580: def write_mulblank row, idx, multiples 581: data = [ 582: row.idx, # Index to row 583: idx, # Index to first column (fc) 584: ] 585: # List of nc=lc-fc+1 16-bit indexes to XF records (➜ 6.115) 586: multiples.each_with_index do |blank, cell_idx| 587: xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx + cell_idx) 588: data.push xf_idx 589: end 590: # Index to last column (lc) 591: data.push idx + multiples.size - 1 592: write_op opcode(:mulblank), data.pack('v*') 593: end
Write multiple consecutive cells with RK values (see write_rk)
# File lib/spreadsheet/excel/writer/worksheet.rb, line 596 596: def write_mulrk row, idx, multiples 597: fmt = 'v2' 598: data = [ 599: row.idx, # Index to row 600: idx, # Index to first column (fc) 601: ] 602: # List of nc=lc-fc+1 16-bit indexes to XF records (➜ 6.115) 603: multiples.each_with_index do |cell, cell_idx| 604: xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx + cell_idx) 605: data.push xf_idx, encode_rk(cell) 606: fmt << 'vV' 607: end 608: # Index to last column (lc) 609: data.push idx + multiples.size - 1 610: write_op opcode(:mulrk), data.pack(fmt << 'v') 611: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 612 612: def write_multiples row, idx, multiples 613: case multiples.last 614: when NilClass 615: write_mulblank row, idx, multiples 616: when Numeric 617: if multiples.size > 1 618: write_mulrk row, idx, multiples 619: else 620: write_rk row, idx 621: end 622: end 623: end
Write a cell with a 64-bit double precision Float value
# File lib/spreadsheet/excel/writer/worksheet.rb, line 626 626: def write_number row, idx 627: # Offset Size Contents 628: # 0 2 Index to row 629: # 2 2 Index to column 630: # 4 2 Index to XF record (➜ 6.115) 631: # 6 8 IEEE 754 floating-point value (64-bit double precision) 632: value = row[idx] 633: case value 634: when Date, Time 635: value = encode_date(value) 636: end 637: write_cell :number, row, idx, value 638: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 639 639: def write_op op, *args 640: data = args.join 641: @io.write [op,data.size].pack("v2") 642: @io.write data 643: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 644 644: def write_refmode 645: # • The “RC” mode uses numeric indexes for rows and columns, for example 646: # “R(1)C(-1)”, or “R1C1:R2C2”. 647: # • The “A1” mode uses characters for columns and numbers for rows, for 648: # example “B1”, or “$A$1:$B$2”. 649: mode = 1 # 0 = RC mode; 1 = A1 mode 650: write_op 0x000f, [mode].pack('v') 651: end
Write a cell with a Numeric or Date value.
# File lib/spreadsheet/excel/writer/worksheet.rb, line 654 654: def write_rk row, idx 655: write_cell :rk, row, idx, encode_rk(row[idx]) 656: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 657 657: def write_row row 658: # Offset Size Contents 659: # 0 2 Index of this row 660: # 2 2 Index to column of the first cell which 661: # is described by a cell record 662: # 4 2 Index to column of the last cell which is 663: # described by a cell record, increased by 1 664: # 6 2 Bit Mask Contents 665: # 14-0 0x7fff Height of the row, in twips = 1/20 of a point 666: # 15 0x8000 0 = Row has custom height; 667: # 1 = Row has default height 668: # 8 2 Not used 669: # 10 2 In BIFF3-BIFF4 this field contains a relative offset to 670: # calculate stream position of the first cell record for this 671: # row (➜ 5.7.1). In BIFF5-BIFF8 this field is not used 672: # anymore, but the DBCELL record (➜ 6.26) instead. 673: # 12 4 Option flags and default row formatting: 674: # Bit Mask Contents 675: # 2-0 0x00000007 Outline level of the row 676: # 4 0x00000010 1 = Outline group starts or ends here 677: # (depending on where the outline 678: # buttons are located, see WSBOOL 679: # record, ➜ 6.113), and is collapsed 680: # 5 0x00000020 1 = Row is hidden (manually, or by a 681: # filter or outline group) 682: # 6 0x00000040 1 = Row height and default font height 683: # do not match 684: # 7 0x00000080 1 = Row has explicit default format (fl) 685: # 8 0x00000100 Always 1 686: # 27-16 0x0fff0000 If fl = 1: Index to default XF record 687: # (➜ 6.115) 688: # 28 0x10000000 1 = Additional space above the row. 689: # This flag is set, if the upper 690: # border of at least one cell in this 691: # row or if the lower border of at 692: # least one cell in the row above is 693: # formatted with a thick line style. 694: # Thin and medium line styles are not 695: # taken into account. 696: # 29 0x20000000 1 = Additional space below the row. 697: # This flag is set, if the lower 698: # border of at least one cell in this 699: # row or if the upper border of at 700: # least one cell in the row below is 701: # formatted with a medium or thick 702: # line style. Thin line styles are 703: # not taken into account. 704: height = row.height || ROW_HEIGHT 705: opts = row.outline_level & 0x00000007 706: opts |= 0x00000010 if row.collapsed? 707: opts |= 0x00000020 if row.hidden? 708: opts |= 0x00000040 if height != ROW_HEIGHT 709: if fmt = row.default_format 710: xf_idx = @workbook.xf_index @worksheet.workbook, fmt 711: opts |= 0x00000080 712: opts |= xf_idx << 16 713: end 714: opts |= 0x00000100 715: height = if height == ROW_HEIGHT 716: (height * TWIPS).to_i | 0x8000 717: else 718: height * TWIPS 719: end 720: # TODO: Row spacing 721: data = [ 722: row.idx, 723: row.first_used, 724: row.first_unused, 725: height, 726: opts, 727: ].pack binfmt(:row) 728: write_op opcode(:row), data 729: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 730 730: def write_rowblock block 731: # ●● ROW Properties of the used rows 732: # ○○ Cell Block(s) Cell records for all used cells 733: # ○ DBCELL Stream offsets to the cell records of each row 734: block.each do |row| 735: write_row row 736: end 737: block.each do |row| 738: write_cellblocks row 739: end 740: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 741 741: def write_rows 742: row_blocks.each do |block| 743: write_rowblock block 744: end 745: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 746 746: def write_saverecalc 747: # 0 = Do not recalculate; 1 = Recalculate before saving the document 748: write_op 0x005f, [1].pack('v') 749: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 750 750: def write_window2 751: # This record contains additional settings for the document window 752: # (BIFF2-BIFF4) or for the window of a specific worksheet (BIFF5-BIFF8). 753: # It is part of the Sheet View Settings Block (➜ 4.5). 754: # Offset Size Contents 755: # 0 2 Option flags: 756: # Bits Mask Contents 757: # 0 0x0001 0 = Show formula results 758: # 1 = Show formulas 759: # 1 0x0002 0 = Do not show grid lines 760: # 1 = Show grid lines 761: # 2 0x0004 0 = Do not show sheet headers 762: # 1 = Show sheet headers 763: # 3 0x0008 0 = Panes are not frozen 764: # 1 = Panes are frozen (freeze) 765: # 4 0x0010 0 = Show zero values as empty cells 766: # 1 = Show zero values 767: # 5 0x0020 0 = Manual grid line colour 768: # 1 = Automatic grid line colour 769: # 6 0x0040 0 = Columns from left to right 770: # 1 = Columns from right to left 771: # 7 0x0080 0 = Do not show outline symbols 772: # 1 = Show outline symbols 773: # 8 0x0100 0 = Keep splits if pane freeze is removed 774: # 1 = Remove splits if pane freeze is removed 775: # 9 0x0200 0 = Sheet not selected 776: # 1 = Sheet selected (BIFF5-BIFF8) 777: # 10 0x0400 0 = Sheet not active 778: # 1 = Sheet active (BIFF5-BIFF8) 779: # 11 0x0800 0 = Show in normal view 780: # 1 = Show in page break preview (BIFF8) 781: # 2 2 Index to first visible row 782: # 4 2 Index to first visible column 783: # 6 2 Colour index of grid line colour (➜ 5.74). 784: # Note that in BIFF2-BIFF5 an RGB colour is written instead. 785: # 8 2 Not used 786: # 10 2 Cached magnification factor in page break preview (in percent) 787: # 0 = Default (60%) 788: # 12 2 Cached magnification factor in normal view (in percent) 789: # 0 = Default (100%) 790: # 14 4 Not used 791: flags = 0x0536 # Show grid lines, sheet headers, zero values. Automatic 792: # grid line colour, Remove slits if pane freeze is removed, 793: # Sheet is active. 794: if @worksheet.selected 795: flags |= 0x0200 796: end 797: data = [ flags, 0, 0, 0, 0, 0 ].pack binfmt(:window2) 798: write_op opcode(:window2), data 799: end
# File lib/spreadsheet/excel/writer/worksheet.rb, line 800 800: def write_wsbool 801: bits = [ 802: # Bit Mask Contents 803: 1, # 0 0x0001 0 = Do not show automatic page breaks 804: # 1 = Show automatic page breaks 805: 0, # 4 0x0010 0 = Standard sheet 806: # 1 = Dialogue sheet (BIFF5-BIFF8) 807: 0, # 5 0x0020 0 = No automatic styles in outlines 808: # 1 = Apply automatic styles to outlines 809: 1, # 6 0x0040 0 = Outline buttons above outline group 810: # 1 = Outline buttons below outline group 811: 1, # 7 0x0080 0 = Outline buttons left of outline group 812: # 1 = Outline buttons right of outline group 813: 0, # 8 0x0100 0 = Scale printout in percent (➜ 6.89) 814: # 1 = Fit printout to number of pages (➜ 6.89) 815: 0, # 9 0x0200 0 = Save external linked values 816: # (BIFF3-BIFF4 only, ➜ 5.10) 817: # 1 = Do not save external linked values 818: # (BIFF3-BIFF4 only, ➜ 5.10) 819: 1, # 10 0x0400 0 = Do not show row outline symbols 820: # 1 = Show row outline symbols 821: 0, # 11 0x0800 0 = Do not show column outline symbols 822: # 1 = Show column outline symbols 823: 0, # 13-12 0x3000 These flags specify the arrangement of windows. 824: # They are stored in BIFF4 only. 825: # 00 = Arrange windows tiled 826: # 01 = Arrange windows horizontal 827: 0, # 10 = Arrange windows vertical 828: # 11 = Arrange windows cascaded 829: # The following flags are valid for BIFF4-BIFF8 only: 830: 0, # 14 0x4000 0 = Standard expression evaluation 831: # 1 = Alternative expression evaluation 832: 0, # 15 0x8000 0 = Standard formula entries 833: # 1 = Alternative formula entries 834: ] 835: weights = [4,5,6,7,8,9,10,11,12,13,14,15] 836: value = bits.inject do |a, b| a | (b << weights.shift) end 837: write_op 0x0081, [value].pack('v') 838: end