# XLSX cell format

**URL:** <https://discourse.julialang.org/t/xlsx-cell-format/113975>\
**Category:** General Usage\
**Tags:** question, package, xlsx\
**Created:** [May 7, 2024, 9:49pm UTC](https://discourse.julialang.org/t/xlsx-cell-format/113975 "2024-05-07T21:49:58Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![Graham\_Gill](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/graham_gill/32/9269_2.png) [@Graham\_Gill](https://discourse.julialang.org/u/Graham_Gill)\
**Post date:** [May 13, 2024, 8:20pm UTC](https://discourse.julialang.org/t/xlsx-cell-format/113975/3 "2024-05-13T20:20:01Z")

</div>

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.

```julia
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,

```julia
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.

---

_[View the full topic](https://discourse.julialang.org/t/xlsx-cell-format/113975)._