I tried different approaches to specify a numerical format with 4 decimal places of all the cells in an Excel sheet generated using XLSX.jl.

None of them worked. Any suggestions?

I tried different approaches to specify a numerical format with 4 decimal places of all the cells in an Excel sheet generated using XLSX.jl.

None of them worked. Any suggestions?

What did you try?

Recently I got sick of having a prototype spit out unformatted numbers into Excel via XLSX.jl.

I could have formatted them in julia first, but that would mean that the Excel workbook would store already-rounded numbers rather than just displaying them rounded but storing their full values, as full as I care about anyway.

After poking around in XLSX.jl hereâ€™s what I ended up with. You may find this example helpful.

```
function setworkbooknumstyles(xf)
wb = XLSX.get_workbook(xf)
numfmt2dec = XLSX.styles_add_numFmt(wb, "0.00;-0.00;-")
numfmt3decpct = XLSX.styles_add_numFmt(wb, "0.000%;-0.000%;-")
numfmtdec = XLSX.styles_add_cell_xf(wb, Dict("applyNumberFormat"=>"1", "numFmtId"=>"$numfmt2dec"))
numfmtpct = XLSX.styles_add_cell_xf(wb, Dict("applyNumberFormat"=>"1", "numFmtId"=>"$numfmt3decpct"))
return numfmtdec, numfmtpct
end
applyXLSXnumformat(df, cols, numstyle) = select(df, All(), cols .=> ByRow(x -> XLSX.CellValue(round(x, digits = 10), numstyle)) .=> cols)
```

`setworkbooknumstyles`

takes an `XLSX.XLSXFile`

and sets two number styles in the workbook. `numfmt2dec`

shows numbers with two decimal places but shows true zero as just a dash `-`

. `numfmt3decpct`

shows numbers as percentages with 3 decimal places, and again shows true zero as just a dash. (Dash makes it easier to see the other numbers when there are a lot of zeros in a table.) It returns the two styles it has added to the workbook.

`XLSX.get_workbook`

and the two `XLSX.styles_add_...`

functions arenâ€™t in the XLSX.jl documentation but you can figure it out by searching for the `Styles`

testset in the `test/runtests.jl`

file of the repo on github.

The available number formats are documented by Microsoft for Excel (or on the Libreoffice website for their compatible office software).

For the `Dict`

in the second parameter of `XLSX.styles_add_cell_xf`

, I donâ€™t know what all of the available keys are or what they all do. I just took the keys and values I used from examples I saw. If someone can point to comprehensive documentation that would be helpful.

Then `applyXLSXformat`

takes a `DataFrame`

`df`

, a dataframe column selector `cols`

, and a workbook (number) style and applies it to all of the rows in each of the specified columns. It returns the new dataframe. To do this I use the `XLSX.CellValue`

constructor, not documented, replacing my numerical data with `CellValue`

s containing the same data plus a format specification. (Well, I round the number to 10 decimal places first - effective â€śzerosâ€ť which come in the form of `1e-17`

and similar become true zeros. My application doesnâ€™t care about numerical differences that small.)

You could of course make the dataframe update in place using `select!`

if that works for you.

Then write out the dataframe to the Excel file using `XLSX.writetable!`

etc.

In this example, where `sh`

is a workbook sheet,

```
sh[row, col] = XLSX.CellValue.(sum(a_wts, dims = 1), Ref(numpctstyle))
```

I set a horizontal group of cells in a row, anchored at cell `(row, col)`

, to percentage formatted values by broadcasting over a `1 x n`

matrix.

I hope that helps.

10 Likes

it worked! Thank you!

@Graham_Gill, do you have any experience in adding colors? Like coloring the cells based on the values. Thank you!

Short answer is no, I donâ€™t.

Having an answer to my question could help for setting other kinds of cell styles, not just numeric.