# 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:** 9
**Page:** 1
### Author:  [@monty](https://discourse.julialang.org/u/monty)
#### Post date: [May 7, 2024, 9:49pm UTC](https://discourse.julialang.org/t/xlsx-cell-format/113975/1 "2024-05-07T21:49:58Z")
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?
---
### Author:  [@mkitti](https://discourse.julialang.org/u/mkitti)
#### Post date: [May 9, 2024, 9:39pm UTC](https://discourse.julialang.org/t/xlsx-cell-format/113975/2 "2024-05-09T21:39:31Z")
What did you try?
---
### Author:  [@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")
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.
---
### Author:  [@monty](https://discourse.julialang.org/u/monty)
#### Post date: [May 16, 2024, 9:53pm UTC](https://discourse.julialang.org/t/xlsx-cell-format/113975/4 "2024-05-16T21:53:20Z")
it worked! Thank you!
---
### Author:  [@monty](https://discourse.julialang.org/u/monty)
#### Post date: [May 17, 2024, 4:24pm UTC](https://discourse.julialang.org/t/xlsx-cell-format/113975/5 "2024-05-17T16:24:47Z")
@Graham_Gill, do you have any experience in adding colors? Like coloring the cells based on the values. Thank you!
---
### Author:  [@Graham\_Gill](https://discourse.julialang.org/u/Graham_Gill)
#### Post date: [June 11, 2024, 6:38pm UTC](https://discourse.julialang.org/t/xlsx-cell-format/113975/6 "2024-06-11T18:38:53Z")
Short answer is no, I don’t.
> [@Graham\_Gill](#):
>
> 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.
Having an answer to my question could help for setting other kinds of cell styles, not just numeric.
---
### Author:  [@hmcgowan01](https://discourse.julialang.org/u/hmcgowan01)
#### Post date: [December 29, 2024, 8:55pm UTC](https://discourse.julialang.org/t/xlsx-cell-format/113975/8 "2024-12-29T20:55:21Z")
Fairly new to Julia. Is there documentation for XLSX.styles\_add\_cell\_xf especially for the Dict second parameter and the available keys. Trying to export a DataFrame to Excel and would like column headers to be in bold and all float[64] columns to be formatted to 4 dec places. Its painful to have to format the Excel file each time and would be much better to be able to do this automatically.
---
### Author:  [@Dan](https://discourse.julialang.org/u/Dan)
#### Post date: [December 29, 2024, 11:54pm UTC](https://discourse.julialang.org/t/xlsx-cell-format/113975/9 "2024-12-29T23:54:33Z")
While trying to address the issue in the post, at this stage I’ve a proof-of-concept of setting the color of some cell in a spreadsheet. This could be generalizable and made into a better interface with some work:
```julia
using XLSX, EzXML
# initialize a new spreadsheet
filename = "new.xlsx"
xfile = XLSX.open_empty_template()
wb = XLSX.get_workbook(xfile)
sheet = xfile["Sheet1"]
# obtains fills XML vector
xroot = XLSX.styles_xmlroot(wb)
fills = findfirst("/xpath:styleSheet/xpath:fills", xroot, XLSX.SPREADSHEET_NAMESPACE_XPATH_ARG)
prev_count = fills["count"]
# construct new fill
fills["count"] = string(parse(Int,prev_count) + 1)
newfill = addelement!(fills, "fill")
newfill2 = addelement!(newfill, "patternFill")
newfill2["patternType"] = "solid"
newcolor = addelement!(newfill2, "fgColor")
newcolor["rgb"] = "FF00FF00"
# use prev_count as this is the 0-based number of the new fill
newstyleindex = XLSX.styles_add_cell_xf(wb, Dict("fillId"=>prev_count))
# set some values using the new fill
XLSX.setdata!(sheet, XLSX.CellRef("A1"), XLSX.CellValue(1000, newstyleindex))
# (over)write XLSX for later reading
XLSX.writexlsx(filename, xfile; overwrite=true)
```
After installing XLSX and EzXML, and running this code, a `new.xlsx` file is created with a single colored cell.
---
### Author:  [@TimG](https://discourse.julialang.org/u/TimG)
#### Post date: [August 30, 2026, 8:11am UTC](https://discourse.julialang.org/t/xlsx-cell-format/113975/10 "2026-08-30T08:11:49Z")
Since v0.11, XLSX.jl supports a full set of functions for cell formats and conditional formats.