How do I hide columns using rubyXL 3.4.0 or later?

The example here (https://github.com/weshatheleopard/rubyXL/issues/145) appears to be out-of-date. (sheet.cols.find now returns an Enumerator, so doesn't have a hidden method.)

Code from rubyXL issue #145:

# Assuming that the cells/rows/cols are respectively locked in the test file:
doc = RubyXL::Parser.parse('test.xlsx')
sheet = doc.worksheets[0]
sheet.sheet_data.rows[0].hidden
=> nil
sheet.sheet_data.rows[1].hidden
=> true
(c = sheet.cols.find(0)) && c.hidden
=> nil
(c = sheet.cols.find(1)) && c.hidden
=> true

xf = doc.workbook.cell_xfs[c.style_index || 0]
xf.apply_protection && xf.protection.locked
=> true
xf.apply_protection && xf.protection.hidden
=> true

CodePudding user response：

the example you offer is not to hide columns
but to get the hidden status of columns

looks like rubyXL doesn't have feature to hide columns
its main purpose is to parse xlsx file
the hidden attribute is only for reading, you can't change it with rubyXL
I found this gem, write_xlsx to have capability to hide columns / rows
but in contrast, it's for creating xlsx
they have example in the github repo about how to hide
I didn't find any other gem that can achive this goal easily(hide specific columns from an existed xlsx file)
maybe you can

1. read the original xlsx file with rubyXL
2. use the data parsed from rubyXL and make a whole new xlsx file with write_xlsx gem (hide the target column in this step)
3. replace original xlsx file

CodePudding user response：

I figured it out:

The following code does generate a new workbook with column B hidden:

require "rubyXL"
require "rubyXL/convenience_methods"

workbook = RubyXL::Workbook.new
sheet = workbook.worksheets.first

sheet.add_cell(0,0, "Sam")
sheet.add_cell(0,1, "George")
sheet.add_cell(0,2, "John")

sheet.cols.get_range(1).hidden = true

workbook.write('hide_b.xlsx')