How to approach Cell formatting?

[rverton]

Hi,
first of all thanks for this library. I currently have a solution with https://github.com/xou/elixlsx but I'm running into OOM issues with large datasets.

What is your suggested approach for adding formatting with exceed? I saw that it's possible to use protocols for own types, so I can think of adding own types for different kind of cells. But I wonder if this is the right approach. What do you think?

Thanks

[sax]

The protocol is there so that you can pass through structs or other data types without having to map the data to strings or numbers as a stream transform.

What kinds of formatting are you doing with your existing spreadsheets? Is it color or font, or is there a data type in the stdlib that we're missing?

[rverton]

I want to do some basic header formatting: Cell height, cell width, text position in the cell (center), bold, cell background color.

[sax]

Yeah, this is just not implemented yet. We just haven't needed it yet, but it seems like a great feature for this library to have.

Can you share a code snippet of what you're doing with elixlsx? That would help to know a minimum feature set.

[rverton]

Sure, I'm using this code to retrieve a list of objects. When constructing the sheet, I'm using the first row as the 'header' with formatting. All other rows are just default cells:

  defp generate_sheets(workspace) do
    alias Elixlsx.Workbook
    alias Elixlsx.Sheet

    objects =
      Objects.list_objects(workspace.id)

    sheet_objects = %Sheet{
      name: "Objects",
      rows: [
        [
          excel_header_cell("Identifier"),
          excel_header_cell("Name"),
          excel_header_cell("Type"),
          excel_header_cell("Tags"),
          excel_header_cell("Tag Exclusions"),
          excel_header_cell("URL")
        ]
        | objects
      ],
      row_heights: %{1 => 30},
      col_widths: %{1 => 20, 2 => 30, 3 => 20, 4 => 60, 5 => 40, 6 => 60}
    }

    %Workbook{}
    |> Workbook.append_sheet(sheet_objects)
  end


  defp excel_header_cell(value) do
    [value, bold: true, bg_color: "#f0f0f0", align_vertical: :center]
  end

As you can see there is no streaming involved here and the whole sheet is build in memory. Having a lot of objects (like in my test workspace) will results in OOM.

Here is a screenshot of the resulting header:

I would also use AutoFilter to make the columns filterable, but even Elixlsx does not support it.

[sax]

One preference I have is that cell styles be declared, and then the cell should reference the style rather than have to declare all its styles per-cell. I know this is possible, but may take a bit of experimentation—I am not the best at reading the OpenXml docs.

References:

• https://learn.microsoft.com/en-us/openspecs/office_file_formats/ms-xls/df39e66a-8f99-4f41-beeb-4f5c8a1d2593
• https://learn.microsoft.com/en-us/openspecs/office_file_formats/ms-xls/f9425d3f-1275-45be-8389-bf80f65b56b7

[rverton]

@sax are you working on this or should I give it a try?

[sax]

All I've done so far is manually styled some spreadsheets and looked at the XML generated by Excel. I think it will be a little while before I can put in the time to seriously tackle this, so if you are able to I would not stop you.

Elixlsx stores a few maps on the workbook struct as "databases". I was considering starting there... the difference is that since Elixlsx creates the entire spreadsheet in memory, it can calculate all cell style variations based on enumerating over every cell.

Exceed will not be able to that—the xl/styles.xml file needs to be written before any spreadsheets, so all cell style permutations need to known ahead of time.

[rverton]

You wrote about pre-defined styles above, this still sounds like a viable option to me:

1. Allow to define styles like %{header: [:bold, :underlined], highlight: [bgcolor: ...]} upfront. This will be written to the styles.xml when initializing exceed (or before writing it to a file).
2. Allow to specify a style by name/index when appending rows/cells.

The styles.xml can look like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <fonts count="2">
    <font>
      <sz val="11"/>
      <color theme="1"/>
      <name val="Calibri"/>
      <family val="2"/>
      <scheme val="minor"/>
    </font>
    <font>
      <b/>
      <sz val="11"/>
      <color theme="1"/>
      <name val="Calibri"/>
      <family val="2"/>
      <scheme val="minor"/>
    </font>
  </fonts>
  <fills count="2">
    <fill>
      <patternFill patternType="none"/>
    </fill>
    <fill>
      <patternFill patternType="gray125"/>
    </fill>
  </fills>
  <borders count="1">
    <border>
      <left/>
      <right/>
      <top/>
      <bottom/>
      <diagonal/>
    </border>
  </borders>
  <cellStyleXfs count="1">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
  </cellStyleXfs>
  <cellXfs count="2">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    <xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1"/>
  </cellXfs>
</styleSheet>

This contains a font definition with the ⁠<b/> tag which represents bold text. The style with index 1 (the second style) applies this bold font. The first style is the default formatting.

And the sheet1.xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <sheetData>
    <row r="1">
      <c r="A1" s="1" t="s">
        <v>0</v>
      </c>
    </row>
  </sheetData>
</worksheet>

This contains a single row with a cell at position A1. The ⁠s="1" attribute references the bold style defined in styles.xml.

If you like this structure I will see if I can find some free time to make a PR.

[sax]

Some questions and thoughts:

• Is there a reason you suggested header and highlight in the elixir definition for cell styles? I did not think the styles would be specific to a header... it's just that you would apply a specific cell style to every cell of row 1.
• Just to make sure, I don't think we should apply a default font of Calibri. I think that Excel applies a default font of Aptos, even on Macs where the font is not installed by default. Maybe Exceed should not specify the font family unless it's set by the user?

I had an idea where maybe styles are named at definition, and you could specify a pattern applying cell styles, like {"A7", :bold_14}, {"A*", :underlined_calibri}, or {"*3", :aptos_11}, and then during streaming the style would be applied based on matching the cell positions.

Most of the work (I suspect) will be in defining the styles and generating the style XML, so if you able to devote time to that it would be wonderful.

[rverton]

Some questions and thoughts:

• Is there a reason you suggested header and highlight in the elixir definition for cell styles? I did not think the styles would be specific to a header... it's just that you would apply a specific cell style to every cell of row 1.

No, this were only examples I could think of. I would use a header class for my first rows then and highlight for some specific cells maybe.

• Just to make sure, I don't think we should apply a default font of Calibri. I think that Excel applies a default font of Aptos, even on Macs where the font is not installed by default. Maybe Exceed should not specify the font family unless it's set by the user?

This was also just an example. I just checked on macOS, and this is the default generated:

<font>
	<sz val="12"/>
	<color theme="1"/>
	<name val="Aptos Narrow"/>
	<family val="2"/>
	<scheme val="minor"/>
</font>

I had an idea where maybe styles are named at definition, and you could specify a pattern applying cell styles, like {"A7", :bold_14}, {"A*", :underlined_calibri}, or {"*3", :aptos_11}, and then during streaming the style would be applied based on matching the cell positions.

Most of the work (I suspect) will be in defining the styles and generating the style XML, so if you able to devote time to that it would be wonderful.

All right, I will see if I can find some time!

[sax]

Awesome! So in your example, header and highlight are user-named styles, which looks good to me.

Thanks!