XLSX cell format

General Usage
, ,
1

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?

2

What did you try?

3

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 CellValues 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
4

it worked! Thank you!

5

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

6

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.