Miscellaneous Issues

[0xCLARITY]

I recently used axlsx to convert a rather complex data object to Excel, and here are some issues I came across. some of these may be due to me not finding relevant documentation, but I read through all the examples, and dove into the source code on occasion to figure out how to accomplish things.

• Setting a column style should not clear unrelated pre-existing styles in that column

  • I encountered this when trying to set a num_fmt in a column after setting bg_color in all the rows. My expectation is that since num_fmt has nothing to do with background colors, it should not clear them.

• Outline level collapsible menu outline symbols are reversed. The [+] symbol show at the bottom of the outline group, not at the top as is expected (Picture below).

  • Also related, the outline level API is not clear from the examples. I had to dive into source to understand that outline_level_rows( ) took four parameters as

outline_level_rows(start_index, end_index, level = 1 (0 - 7), collapsed = true)

• It is confusing why column_widths end up being calculated as column_width * 6 in pixels, as opposed to using the actual Excel width value. For example, a column_width of 12 is rendered in Excel as a column_width of 72 Pixels, which is an Excel width of 11.17, as opposed to an Excel width of 12, which is 77 Pixels.
• I did not come across any way to set the font for the entire workbook / sheet.
• I am still confused as to why I need to enable use_shared_strings to keep \r\n control characters intact in my cell values. I feel that stripping control characters by default is anti-helpful.

Additionally, it would be nice if axlsx had a helper method to take an integer and return the relevant column letters. I wrote out my own limited helper function, which is shown below. Some examples illustrate using the column letters as opposed to an index to access them, and it was unclear from the examples whether it was possible to use the index over the alphabetical column.

# Only handles two characters deep - but thats still 676
def excel_column_letters(num)
  alpha = ("A".."Z").to_a
  first_letter, second_letter = (num).divmod(26)
  if first_letter == 0
    return alpha[second_letter]
  else
    alpha[first_letter - 1]+alpha[second_letter - 1]
  end
end

[kashifnaseer]

+1 for The [+] symbol show at the bottom of the outline group, not at the top. @hbergren did you find any solution for it ?

[0xCLARITY]

Nope. Decided that outlining functioned correctly, so it was good enough for my use case.

[lehf]

+1 for The [+] symbol show at the bottom of the outline group, not at the top. @hbergren did you find any solution for it ?

add code sheet.sheet_pr.outline_pr.summary_below = false exmple

workbook.add_worksheet(name: worksheet_name) do |sheet|
  sheet.sheet_pr.outline_pr.summary_below = false
  sheet.add_row('row1')
  sheet.add_row('row2')
  sheet.add_row('row3')
  sheet.outline_level_rows 1,2
end