I haven´t thought about that, but it turns out it just works. As long as your data cells has no formatting applied to them. I would say this gets unstable, so might not work in a complicated workbook.
import XLSX
f = XLSX.openxlsxtemplate("fig_template.xlsx") # opens existing excel file with figure as template
s = f[1] # gets the first worksheet
using DataFrames
df = DataFrame(X=[1,2,3,4,5,6], Y=[0.2, 0.3, 0.4, 0.5, 0.1, 1])
XLSX.writetable!(s, DataFrames.columns(df), DataFrames.names(df)) # write table to worksheet
XLSX.writexlsx("out.xlsx", f) # saves as new excel file
I guess I should improve the package to leave unedited worksheets untouched when saving the Excel file. So you would be able to export data to a worksheet with no formats using Julia, and write a pretty report on another worksheet.
It looks like this problem is not with XLSX but with this line
sheet["C2:C61"] = value.(B)
specifically the function value not being known as the ERROR message suggests: “no method matching value(::Float64)”. What was the function there for?
For me sheet["C2:C61"] wouldn’t work (although it did in an example) but replacing the above line with this:
sheet["C2", dim = 1] = B
seemed to work the way you intended it.
Finally, I think it could benefit your post if you read this suggestions to improve posts for help. In this case, for example, quoting your code, making sure it runs if someone want’s to try it (I happened to know I need to import Distributions but maybe that’s not the case for everyone), or creating a separate thread for your question.
Thank you very much for your help, It was very useful. I will consider your tips for upcoming questions. It was my first time participating in a forum.
Hi Felipe, I have a problem with XLSX. I would like to export results from Julia to an excel file. However, it tells me that excel file already exists in my file although it doesn’t.
I don’t think it is a good idea to revive a 3 year old posting to get help.
As your problem description is a bit vague I’ll try to guess.
It is a bit annoying that this package does not set the file date (at least on win), hence you sometimes just don’t find the file because it is not where you think it should be, if you are sorting by most recent files.
Anyway, you can just try the overwrite mode like so
I hope it is OK to revive this topic. It is the only [ANN] topic I could find relating to XLSX.jl. Happy for it to be moved if that is appropriate.
I have been working on some significant updates to the package recently and have made a series of pull requests. However, the changes I’ve made are quite large and I’m still learning julia, so I wanted to ask if anyone was interested in trying out my updates before @felipenoris creates a new release (if indeed he should).
A fork of the package with my updates can be found here [Edit: link updated latest branch].
In summary, here is a quick list of the functionality I’ve added:
Fairly comprehensive options to control the formatting of cells and cell ranges. This makes it easy to control things like the font characteristics, alignment, colour, size, etc, cell fill colour and pattern, cell borders, change column width or row height, etc, and to apply conditional formatting to cells. Support is provided for much easier number formatting, too. More detail can be found here.
Cell formatting in templates is retained when cells are written - previously, default formats were used when writing values, removing any borders, shading, alignment, etc, originally in the template. This change makes templates much more useful. Details here.
Formatting of cells, in particular, seems to have given rise to many issues and some discussion. For example, I think my changes address the following formatting issues: #281, #275, #259, #234, #63 and #52 (partly). They also address the questions raised on the discourse here, here (formatting but not and now merging, too), and possibly here, too.
I would welcome feedback on this PR if any one wants to give it a try. I’ll do my best to fix any issues.
I’d like to thank @felipenoris for his work to develop the package in the first place. I’d also like to thank @nhz2 for his early suggestions for improvement. I’m sure the community will be able to come up with many more!
I’ve been in the need for such formatting capabilities in XLSX. I’m not sure if I’ll have much time for that, but I’ll try and test your contributions !
Tried out the cell formatting with XLSX.setFill(sheet, c; pattern = "solid", fgColor = "FFFFC7CE")
The 8-digit hex (rgb, alpha) that Excel uses is baffling. Is the hex in reverse order (some stackoverflow posts seem to indicate this)? Does the alpha have any effect; is it also in reverse order?
I’m on macOS and Microsoft 365. Sadly, the color picker does not show hex codes. When I use web tools, the hex received must be entered in reverse order to approximate the color; so 42B480 with FF for alpha must be entered above as fgColor = "FF084B24". Even so, this results in a dark green (no effect by adjusting alpha), not the lighter green shown above.
Mystery solved. Thanks for sticking with me. For the macOS Excel app, use Format Cells > Fill > More Colors (below standard colors in the screenshot). When the next dialog opens, choose RGB Sliders. Hex is shown.
Note that the ignored alpha values come first in the specification ‘FF’. Thereafter, enter the values shown in Hex Color box. Excel complains if the alpha is left out. It also errors if the hex characters are not upper case.
In case others want to try out the various formatting functions in the pull request, here is some example code to get one started.
example package code
(in src/excel.jl)
module excel
using XLSX, DataFrames
export testdata, rdformat2
function testdata(fileout)
df = DataFrame(id=repeat('a':'j', inner=50), A=1:2:1000, C=1:500)
transform!(df, :id => (id -> string.(id)), renamecols = false)
XLSX.writetable(fileout, df, overwrite = true)
end
function rdformat2(file)
wkbk = XLSX.openxlsx(file; mode = "rw")
sheet = wkbk["Sheet1"]
testformat(sheet, "A1:C501")
XLSX.writexlsx(file, wkbk, overwrite = true)
end
function testformat(sheet, rng)
for c in XLSX.CellRange(rng)
if sheet[c] == "a"
XLSX.setFill(sheet, c; pattern = "solid", fgColor = "FF42B480")
end
end
end
end # module excel
(in explore.jl)
using excel
testdata("testdata.xlsx")
rdformat2("testdata.xlsx")
It could be good to interoperate with Colors.jl here so that a user of the package can provide any Colorant and a upper case FF-prefixed hex code is written.
I have no concept of Colors.jl nor any idea of how it might work with XLSX.jl. Definitely something to look into (though it might not be ready very quickly!).
