view · edit · sidebar · attach · print · history

20101206-update-outline-spreadsheet

<< Masa.20101207-update-outline-spreadsheet | 2010 | Masa.20101203-update-outline-spreadsheet >>

Check the reading part of GUTS entry
Check the writing part of GUTS entry
Read Biff format to find description regarding outline
Check outline function in row
Implement outline function

Goal

Update outline spreadsheet / 80%

Milestones

~~Review~~
Edit GUTS entry
- ~~BraSt~~
- ~~Check Reading part~~ 9:45
- ~~Check Writing part~~ 11:00
~~Read Excel format to find the description of outline~~ 11:50
~~Check outline (grouping) in row~~ 14:45
Implement outline
- ~~BraSt (Desgin)~~ 16:10
1. ~~WINDOW2 flag masked by 0x0080 should be always '1'~~ 16:25
2. read_guts => Worksheet#guts
3. write_guts <= Worksheet#guts
(Outline symbol '-' when opened)

Summary

http://masay.ch/2010/12/06/update-outline-spreadsheet-2/

Commits
ToDo Tomorrow
Keep in Mind

VirtualBox kernel module
todos
BAG: Files published obviously only on the first ch.ODDB.org Report - Error: Generikaliste - 12/2010 log passwd
Testcase of 'Object Stream' parser (for rpdf2txt/lib/rpdf2txt/parser.rb#rebuild_object_catalogue method) 20101126-update-rpdf2txt
Testcases of lib/oddb/html/state/global.rb#grant_download, lib/oddb/html/view/download.rb#to_html
Debug testcases in test/export/test_server.rb de.oddb.org
A bug import_gkv Tue Nov 16 02:00:10 2010: de.oddb.org Zubef (PDF)
Compression (refer to lib/oddb/export/server.rb), Test cases (grant_download, Logging, Reporting)
Log Error: on production server, de.oddb.org/log/import_dimdi, import_pharmnet
On Ice
emerge --sync

Check the reading and writing part of GUTS entry

Question

How do I get the GUTS information?

Experiment

lib/spreadsheet/excel/reader.rb#read_worksheet

  def read_worksheet worksheet, offset
    @pos = offset
    @detected_rows = {}
    previous = nil
    while tuple = get_next_chunk
      pos, op, len, work = tuple
      if((offset = @current_row_block_offset) && !in_row_block?(op, previous))
        @current_row_block_offset = nil
        offset[1] = pos - offset[0]
      end
      case op
      when :eof        # &#9679;  EOF &#10140; 6.36
        postread_worksheet worksheet
        return

      when :guts
        p "found guts"
...

test.rb (edit a little bit and just copy a xls file)

require 'spreadsheet'

file = ARGV[0]
book = Spreadsheet.open(file, 'rb')
sheet= book.worksheet(0)
5.times {|i| sheet[0,i] = i}
book.write "out.xls"

Result

masa@masa ~/ywesee/spreadsheet $ ruby -I lib test.rb hidden.xls 
"found guts"
"found guts"
"found guts"
masa@masa ~/ywesee/spreadsheet $ ruby -I lib test.rb outline.xls
"found guts"
"found guts"
"found guts"
masa@masa ~/ywesee/spreadsheet $ ruby -I lib test.rb hidden_by_sp.xls 
masa@masa ~/ywesee/spreadsheet $

Notes

hidden.xls and outline.xls are the files which I have made last Friday on Windows 7 ('C' and 'D' columns are hidden or outlined)
hidden_by_sp.xls is made by spreadsheet
There are 3 GUTS entries in hidden.xls and outline.xls
If a xls file is made by MSOffice, it has a GUTS entry.
The Biff entry names (GUTS, COLINFO, etc.) are somewhere converted into Ruby Symbols

Question

What is the GUTS?

Reference

OpenOffice.org's Documentation of the Microsoft Excel File Format 5.53 GUTS (p.179)

This record contains information about the layout of outline symbols.

Offset Size Contents
0 2 Width of the area to display row outlines (left of the sheet), in pixel
2 2 Height of the area to display column outlines (above the sheet), in pixel
4 2 Number of visible row outline levels (used row levels + 1; or 0, if not used)
6 2 Number of visible column outline levels (used column levels + 1; or 0, if not used)

References

Big and little endian
- Wikipedia (English) http://en.wikipedia.org/wiki/Endianness
- Wikipedia (Japanese) http://ja.wikipedia.org/wiki/%E3%82%A8%E3%83%B3%E3%83%87%E3%82%A3%E3%82%A2%E3%83%B3
Ruby unpack http://ruby-doc.org/core/classes/String.html#M000760
- Japanese http://www.ruby-lang.org/ja/man/html/pack_A5C6A5F3A5D7A5ECA1BCA5C8CAB8BBFACEF3.html

Memo

1.3 Byte order p.9 OpenOffice.org's Documentation of the Microsoft Excel File Format

1.3 Byte Order
All data items containing more than one byte are stored using the Little-Endian method.

Biff GUTS of hidden.xls and outline.xls (8 byte)

hidden.xls:  BIFF GUTS (80h) 	8 	00 00 00 00 00 00 00 00 
outline.xls: BIFF GUTS (80h) 	8 	00 00 1D 00 00 00 02 00

outline.xls

'00 00': Width of the area to display row outlines
'1D 00': Height of the area to display column outlines
'00 00': Number of visible row outline levels
'02 00': Number of visible column outline levels

These are hex number in little endian, so

'1D 00'(16) = 29(10)
'02 00'(16) = 2(10)

Experiment

lib/spreadsheet/excel/reader.rb#read_worksheet

  def read_worksheet worksheet, offset
    @pos = offset
    @detected_rows = {}
    previous = nil
    while tuple = get_next_chunk
      pos, op, len, work = tuple
      if((offset = @current_row_block_offset) && !in_row_block?(op, previous))
        @current_row_block_offset = nil
        offset[1] = pos - offset[0]
      end
      case op
...
      when :guts       #    GUTS      5.53
        read_guts worksheet, work, pos, len
...
    end
...
  def read_guts worksheet, work, pos, len
    # Offset Size Contents
    #      0    2 Width of the area to display row outlines (left of the sheet), in pixel
    #      2    2 Height of the area to display column outlines (above the sheet), in pixel
    #      4    2 Number of visible row outline levels (used row levels + 1; or 0, if not used)
    #      6    2 Number of visible column outline levels (used column levels + 1; or 0, if not used)
    p "found guts"
    print "pos=\t\t", pos, "\n"
    print "len=\t\t", len, "\n"
    width, height, row_level, col_level = work.unpack 'v4'
    print "width=\t\t", width, "\n"
    print "height=\t\t", height, "\n"
    print "row_level=\t", row_level, "\n"
    print "col_level=\t", col_level, "\n"
    puts

  end

Result

masa@masa ~/ywesee/spreadsheet $ ruby -I lib test.rb hidden.xls 
"found guts"
pos=            12162
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"found guts"
pos=            12638
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"found guts"
pos=            13028
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

masa@masa ~/ywesee/spreadsheet $ ruby -I lib test.rb outline.xls
"found guts"
pos=            12162
len=            12
width=          0
height=         29
row_level=      0
col_level=      2

"found guts"
pos=            12638
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"found guts"
pos=            13028
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

Notes

'work.unpack "v4"' looks working correctly
unpack 'v4' = unpack 'vvvv'
'v' means 'convert unsigned 16 bit in little endian'

Check the writing part of GUTS entry

Memo

OPCODES looks Record ID

Experiment

lib/spreadsheet/excel/writer/worksheet.rb

  def write_guts
    data = [0,29,0,2]
    write_op opcode(:guts), data.pack('v4')
  end

create_new_file.rb

require 'spreadsheet'

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
5.times {|i| sheet[0,i] = i}
sheet.column(2).hidden = true
#sheet.column(2).outline_level = 256
sheet.column(3).hidden = true
#sheet.column(3).outline_level = 256
book.write 'out.xls'

Create a new file

masa@masa ~/ywesee/spreadsheet $ ruby -I lib create_new_file.rb

Note

I confirmed that 'C' and 'D' columns are hidden
But there is no outline (grouping) button

Check GUTS

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb out.xls 
"found guts"
pos=            641
len=            12
width=          0
height=         29
row_level=      0
col_level=      2

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb outline.xls 
"found guts"
pos=            12162
len=            12
width=          0
height=         29
row_level=      0
col_level=      2

"found guts"
pos=            12638
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"found guts"
pos=            13028
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

Notes

There is only one GUTS entry in the file made by spreadsheet
GUTS entry looks same but the button is not shown

Experiment

lib/spreadsheet/excel/reader.rb#read_colinfo

  def read_colinfo worksheet, work, pos, len
p "getin read_colinfo"
    # Offset  Size  Contents
    #      0     2  Index to first column in the range
    #      2     2  Index to last column in the range
    #      4     2  Width of the columns in 1/256 of the width of the zero
    #               character, using default font (first FONT record in the
    #               file)
    #      6     2  Index to XF record (&#10140; 6.115) for default column formatting
    #      8     2  Option flags:
    #               Bits  Mask    Contents
    #                  0  0x0001  1 = Columns are hidden
    #               10-8  0x0700  Outline level of the columns (0 = no outline)
    #                 12  0x1000  1 = Columns are collapsed
    #     10     2  Not used
    first, last, width, xf, opts = work.unpack binfmt(:colinfo)[0..-2]
#print "binfmt(:colinfo)=", binfmt(:colinfo), "\n"
#print "binfmt(:colinfo)[0..-2]=", binfmt(:colinfo)[0..-2], "\n"
print "first - last=\t", first, " - ", last, "\n"
print "width=\t\t", width, "\n"
print "xl=\t\t", xf, "\n"
print "hidden=\t\t", (opts & 0x0001) > 0,"\n"
print "collapsed=\t", (opts & 0x1000) > 0, "\n"
print "outline_level=\t",(opts & 0x0700),"\n\n"

lib/spreadsheet/excel/writer/worksheet.rb#write_colinfo

  def write_colinfo bunch
    col = bunch.first
    width = col.width.to_f * 256
    xf_idx = @workbook.xf_index @worksheet.workbook, col.default_format
    opts =  0
    opts |= 0x0001 if col.hidden?
    #opts |= col.outline_level.to_i << 8
opts |= col.outline_level

create_new_file.rb

require 'spreadsheet'

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
5.times {|i| sheet[0,i] = i}
sheet.column(2).hidden = true
sheet.column(2).outline_level = 256
sheet.column(3).hidden = true
sheet.column(3).outline_level = 256
book.write 'out.xls'

Result

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb test.xls 
"found guts"
pos=            641
len=            12
width=          0
height=         29
row_level=      0
col_level=      2

"getin read_colinfo"
first - last=   0 - 1
width=          2560
xl=             0
hidden=         false
collapsed=      false
outline_level=  0

"getin read_colinfo"
first - last=   2 - 3
width=          2560
xl=             0
hidden=         true
collapsed=      false
outline_level=  256

"getin read_colinfo"
first - last=   4 - 4
width=          2560
xl=             0
hidden=         false
collapsed=      false
outline_level=  0

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb outline.xls 
"found guts"
pos=            12162
len=            12
width=          0
height=         29
row_level=      0
col_level=      2

"getin read_colinfo"
first - last=   2 - 3
width=          2925
xl=             15
hidden=         false
collapsed=      false
outline_level=  256

"found guts"
pos=            12638
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"found guts"
pos=            13028
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

Notes

GUTS entry data looks same but the outline (grouping) button does not come

Read Biff format to find description regarding outline

OpenOffice.org's Documentation of the Microsoft Excel File Format 5.110 WINDOWS (p.216)

5.110.2 Option Flags
Option flags, BIFF3-BIFF8:
Bits Mask Contents
0 0001H 0 = Show formula results 1 = Show formulas
1 0002H 0 = Do not show grid lines 1 = Show grid lines
2 0004H 0 = Do not show sheet headers 1 = Show sheet headers
3 0008H 0 = Panes are not frozen 1 = Panes are frozen (freeze)
4 0010H 0 = Show zero values as empty cells 1 = Show zero values
5 0020H 0 = Manual grid line colour 1 = Automatic grid line colour
6 0040H 0 = Columns from left to right 1 = Columns from right to left
7 0080H 0 = Do not show outline symbols 1 = Show outline symbols
8 0100H 0 = Keep splits if pane freeze is removed 1 = Remove splits if pane freeze is removed
9 0200H 0 = Sheet not selected 1 = Sheet selected (BIFF5-BIFF8)
10 0400H 0 = Sheet not active 1 = Sheet active (BIFF5-BIFF8)
11 0800H 0 = Show in normal view 1 = Show in page break preview (BIFF8)

Note

There is a flag to show outline symbols

Check Biff data

hidden.xls http://dev.ywesee.com/uploads/$PageName//hidden.xls.html
outline.xls http://dev.ywesee.com/uploads/$PageName//outline.xls.html
hidden_by_sp.xls http://dev.ywesee.com/uploads/$PageName//hidden_by_sp.xls.html

Experiment

http://scm.ywesee.com/?p=spreadsheet/.git;a=blob;f=lib/spreadsheet/excel/reader.rb;h=4d0d4ad502b491560a4a3155a56be6fa31488b48;hb=HEAD#l720lib/spreadsheet/excel/reader.rb#read_window2

  def read_window2 worksheet, work, pos, len
...
    flags, _ = work.unpack 'v'
p "getin read_window2"
print "show outline symbol=\t", (flags & 0x0080) > 0, "\n\n"
    worksheet.selected = flags & 0x0200 > 0
  end

Result

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb hidden.xls 

"getin read_window2"
show outline symbol=    true

"getin read_window2"
show outline symbol=    true

"getin read_window2"
show outline symbol=    true

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb outline.xls

"getin read_window2"
show outline symbol=    true

"getin read_window2"
show outline symbol=    true

"getin read_window2"
show outline symbol=    true

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb hidden_by_sp.xls 
"getin read_window2"
show outline symbol=    false

Note

The flag is 'true' in both hidden.xls and outline.xls
But the flag of a file made by spreadsheet is 'false'

Experiment

lib/spreadsheet/excel/writer/worksheet.rb#write_window2

  def write_window2
    flags = 0x0536  # Show grid lines, sheet headers, zero values. Automatic
                    # grid line colour, Remove slits if pane freeze is removed,
                    # Sheet is active.
    if @worksheet.selected
      flags |= 0x0200
    end
flags |= 0x0080
    data = [ flags, 0, 0, 0, 0, 0 ].pack binfmt(:window2)
    write_op opcode(:window2), data
  end

Create out.xls

masa@masa ~/ywesee/spreadsheet $ cat create_new_file.rb 
require 'spreadsheet'

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
5.times {|i| sheet[0,i] = i}
sheet.column(2).hidden = true
sheet.column(2).outline_level = 256
sheet.column(3).hidden = true
sheet.column(3).outline_level = 256
book.write 'out.xls' 

masa@masa ~/ywesee/spreadsheet $ ruby -I lib create_new_file.rb

Result

Note

I can show the buttons but the start symbol is '-' not '+'
But it is '+' when the file is opened in Windows 7 => keep in mind

Experiment

create_new_file.rb

require 'spreadsheet'

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
5.times {|i| sheet[0,i] = i}
sheet.column(2).hidden = true
#sheet.column(2).outline_level = 256
sheet.column(3).hidden = true
#sheet.column(3).outline_level = 256
book.write 'out.xls'

Create a new file

masa@masa ~/ywesee/spreadsheet $ ruby -I lib create_new_file.rb

Result

I have confirmed that the outline symbol is not shown

Experiment

create_new_file.rb

require 'spreadsheet'

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
5.times {|i| sheet[0,i] = i}
#sheet.column(2).hidden = true
sheet.column(2).outline_level = 256
#sheet.column(3).hidden = true
sheet.column(3).outline_level = 256
book.write 'out.xls'

Result

I have confirmed that the outline symbol is shown but the columns are not hidden

Summary

In order to show the outline symbol (grouping button),
- Column#outline_level should be 256
- The flag masked by 0x0080 in WINDOW2 Entry should be '1'
- GUTS entry may not be so important for Open Office (even if there is no entry of GUTS, outline symbol is shown),
- But GUTS looks important for Windows 7 (If there is no GUTS entry, the outline buttion is not shown)

Check outline function in row

Experiment

create_new_file.rb

require 'spreadsheet'

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
5.times {|j| 5.times {|i| sheet[j,i] = (i+1)*10**j}}

# col
sheet.column(2).hidden = true
sheet.column(2).outline_level = 256
sheet.column(3).hidden = true
sheet.column(3).outline_level = 256

# row
sheet.row(2).hidden = true
sheet.row(2).outline_level = 256
sheet.row(3).hidden = true
sheet.row(3).outline_level = 256

book.write 'out.xls'

Create a file

masa@masa ~/ywesee/spreadsheet $ ruby -I lib create_new_file.rb

Result

'3' and '4' rows are hidden but outline buttion is not shown

Hypothesis

The outline_level about row is not correctly converted

Check write method about row outline level

Preparing sample xls files made by MS Office

Diff

diff normal.xls.html hidden.xls.html Attach:diff_normal_hidden.20101206.html
diff normal.xls.html outline.xls.html Attach:diff_normal_outline.20101206.html
diff hidden.xls.html outline.xls.html Attach:diff_hidden_outline.20101206.html

Notes

There are so many differences between hidden.xls and outline.xls

Experiment (check row.outline_level and row.hidden?)

edit_little.rb

require 'spreadsheet'

file = ARGV[0]
book = Spreadsheet.open(file, 'rb')
sheet= book.worksheet(0)
5.times {|i| sheet[0,i] = i}
book.write "out.xls"

Result

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb normal.xls 
"found guts"
pos=            12162
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"getin read_window2"
show outline symbol=    true

"found guts"
pos=            12810
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"getin read_window2"
show outline symbol=    true

"found guts"
pos=            13200
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"getin read_window2"
show outline symbol=    true

"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=false

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb hidden.xls 
"found guts"
pos=            12162
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"getin read_window2"
show outline symbol=    true

"found guts"
pos=            12810
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"getin read_window2"
show outline symbol=    true

"found guts"
pos=            13200
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"getin read_window2"
show outline symbol=    true

"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=true
"getin write_row"
row.outline_level=0
row.hidden?=true
"getin write_row"
row.outline_level=0
row.hidden?=false

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb outline.xls 
"found guts"
pos=            12162
len=            12
width=          29
height=         0
row_level=      2
col_level=      0

"getin read_window2"
show outline symbol=    true

"found guts"
pos=            12810
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"getin read_window2"
show outline symbol=    true

"found guts"
pos=            13200
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"getin read_window2"
show outline symbol=    true

"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=1
row.hidden?=false
"getin write_row"
row.outline_level=1
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=false

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb outline_hidden.xls 
"found guts"
pos=            12162
len=            12
width=          29
height=         0
row_level=      2
col_level=      0

"getin read_window2"
show outline symbol=    true

"found guts"
pos=            12810
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"getin read_window2"
show outline symbol=    true

"found guts"
pos=            13200
len=            12
width=          0
height=         0
row_level=      0
col_level=      0

"getin read_window2"
show outline symbol=    true

"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=1
row.hidden?=true
"getin write_row"
row.outline_level=1
row.hidden?=true
"getin write_row"
row.outline_level=0
row.hidden?=false

Notes

row.outline_level should be '1' not '256'

Experiment

create_new_file.rb

require 'spreadsheet'

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
5.times {|j| 5.times {|i| sheet[j,i] = (i+1)*10**j}}

# col
sheet.column(2).hidden = true
sheet.column(2).outline_level = 256
sheet.column(3).hidden = true
sheet.column(3).outline_level = 256

# row
sheet.row(2).hidden = true
sheet.row(2).outline_level = 1
sheet.row(3).hidden = true
sheet.row(3).outline_level = 1

book.write 'out.xls'

Create a new file

masa@masa ~/ywesee/spreadsheet $ ruby -I lib create_new_file.rb 
"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=1
row.hidden?=true
"getin write_row"
row.outline_level=1
row.hidden?=true
"getin write_row"
row.outline_level=0
row.hidden?=false

Result

Outline buttons are shown in Open Office but
Outline buttons are NOT shown in MS Office

Experiment

lib/spreadsheet/excel/writer/worksheet.rb#write_guts

  def write_guts
    #data = [0,29,0,2]
    data = [29,29,2,2]
    write_op opcode(:guts), data.pack('v4')
  end

create_new_file.rb

require 'spreadsheet'

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
5.times {|j| 5.times {|i| sheet[j,i] = (i+1)*10**j}}

# col
sheet.column(2).hidden = true
sheet.column(2).outline_level = 256
sheet.column(3).hidden = true
sheet.column(3).outline_level = 256

# row
sheet.row(2).hidden = true
sheet.row(2).outline_level = 1
sheet.row(3).hidden = true
sheet.row(3).outline_level = 1

book.write 'out.xls'

Create a xls file

masa@masa ~/ywesee/spreadsheet $ ruby -I lib create_new_file.rb 
"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=1
row.hidden?=true
"getin write_row"
row.outline_level=1
row.hidden?=true
"getin write_row"
row.outline_level=0
row.hidden?=false

Check GUTS entry

masa@masa ~/ywesee/spreadsheet $ mv out.xls test.xls
masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb test.xls
"found guts"
pos=            641
len=            12
width=          29
height=         29
row_level=      2
col_level=      2

"getin read_window2"
show outline symbol=    true

"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=0
row.hidden?=false
"getin write_row"
row.outline_level=1
row.hidden?=true
"getin write_row"
row.outline_level=1
row.hidden?=true
"getin write_row"
row.outline_level=0
row.hidden?=false

Result

Outline buttions are shown also in MS Office

Note

GUTS information is important in rows and columns outline (grouping)

Implement outline function

BraSt

Main

In order to show outline button,
- WINDOWS flag masked by 0x0080 should be '1'
- GUTS entry should be written
In order to hide rows and columns,
- hidden property should be 'true'
outline_level (in column) should be 256(2**8), 512(2**9), 768(2**8+2**9), 1024(2**10),,, masked by 0x0700 (5.18 COLINFO)
outline_level (in row) should be 1,2,3,,,, masked by 0x00000007 (5.88 ROW)
- This means that GUTS outline_level should be under 7 (we cannot show over 8 outline levels)

Memo

The parts that should be modified are
1. write_guts method (new)
2. write_row method
3. write_colinfo method
4. write_window2
5. read_guts method (new)
6. read_window2
I have to check the relations between outline_level and width (height) (in GUTS and COLINFO and ROW)
I have to understand why 3 GUTS entries are there in a xls file made by MS Office
- probably because there are 3 worksheets
- It means that I have to store the GUTS information in each worksheet object
It looks no problem that the WINDOWS2 flag masked 0x0080 always equals'1' (Show outline symbols)
- when outline_level == 0, the outline buttons are not shown, even though WINDOW2 flag masked 0x0080 equals'1'
Reading part should be implemented first, and then Writing part next
- GUTS information should be stored in Worksheet object
I should update Column class and Row class, and Worksheet class (for GUTS)

WINDOWS2 flag masked by 0x0080

lib/spreadsheet/excel/writer/worksheet.rb|write_window2

  def write_window2
    flags = 0x0536  # Show grid lines, sheet headers, zero values. Automatic
                    # grid line colour, Remove slits if pane freeze is removed,
                    # Sheet is active.
    if @worksheet.selected
      flags |= 0x0200
    end
flags |= 0x0080 # Show outline symbols
    data = [ flags, 0, 0, 0, 0, 0 ].pack binfmt(:window2)
    write_op opcode(:window2), data
  end

read_guts => Worksheet#guts

The property 'guts' should be implemented in lib/spreadsheet/excel/worksheet.rb (Spreadsheet::Excel::Worksheet class)

lib/spreadsheet/excel/worksheet.rb

module Spreadsheet
  module Excel
##
# Excel-specific Worksheet methods. These are mostly pertinent to the Excel
# reader, and to recording changes to the Worksheet. You should have no reason
# to use any of these.
class Worksheet < Spreadsheet::Worksheet
  include Spreadsheet::Excel::Offset
  offset :dimensions
  attr_reader :offset, :ole, :links, :guts
  def initialize opts = {}
    @row_addresses = nil
    super
    @offset, @ole, @reader = opts[:offset], opts[:ole], opts[:reader]
    @dimensions = nil
    @links = {}
    @guts = {}
  end

lib/spreadsheet/excel/reader.rb

  def read_guts worksheet, work, pos, len
    # Offset Size Contents
    #      0    2 Width of the area to display row outlines (left of the sheet), in pixel
    #      2    2 Height of the area to display column outlines (above the sheet), in pixel
    #      4    2 Number of visible row outline levels (used row levels + 1; or 0, if not used)
    #      6    2 Number of visible column outline levels (used column levels + 1; or 0, if not used)
    width, height, row_level, col_level = work.unpack 'v4'
    worksheet.guts[:width] = width
    worksheet.guts[:height] = height
    worksheet.guts[:row_level] = row_level
    worksheet.guts[:col_level] = col_level
  end

write_guts <= Worksheet#guts

lib/spreadsheet/excel/writer/worksheet.rb

  def write_guts
    data = [
      self.guts[:width],
      self.guts[:height],
      self.guts[:row_level],
      self.guts[:col_level]
    ]
    write_op opcode(:guts), data.pack('v4')
  end

Experiment

edit_little.rb

require 'spreadsheet'

file = ARGV[0]
book = Spreadsheet.open(file, 'rb')
sheet= book.worksheet(0)
5.times {|i| sheet[0,i] = i}

sheet.row(2).hidden = true
sheet.row(3).hidden = true
sheet.row(2).outline_level = 1
sheet.row(3).outline_level = 1

book.write "out.xls"

Run

masa@masa ~/ywesee/spreadsheet $ ruby -I lib edit_little.rb normal.xls

Result

It looks working

ywesee Developer-Wiki
Dieses Wiki richtet sich an alle ywesee-Entwickler

About

EBPS

Bbmb

ODBA

Oddb

Rpdf2txt

YDPM

YDIM

XmlConv

20101206-update-outline-spreadsheet

Check the reading and writing part of GUTS entry

Check the writing part of GUTS entry

Read Biff format to find description regarding outline

Check outline function in row

Check write method about row outline level

Implement outline function

WINDOWS2 flag masked by 0x0080

read_guts => Worksheet#guts

write_guts <= Worksheet#guts

ywesee Developer-Wiki Dieses Wiki richtet sich an alle ywesee-Entwickler

About

EBPS

Bbmb

ODBA

Oddb

Rpdf2txt

YDPM

YDIM

XmlConv

20101206-update-outline-spreadsheet

Check the reading and writing part of GUTS entry

Check the writing part of GUTS entry

Read Biff format to find description regarding outline

Check outline function in row

Check write method about row outline level

Implement outline function

WINDOWS2 flag masked by 0x0080

read_guts => Worksheet#guts

write_guts <= Worksheet#guts

ywesee Developer-Wiki
Dieses Wiki richtet sich an alle ywesee-Entwickler