Here’s a snippet that may help:
julia> using Colors
julia> somecolor = colorant"coral"
RGB{N0f8}(1.0, 0.498, 0.314)
julia> hex(somecolor, :AARRGGBB)
"FFFF7F50"
2 Likes
Would it make sense to use GitHub - KristofferC/Crayons.jl: Colored and styled strings for terminals. for the formatting ? May be a bit overkill ?
Actually, and as I’m sure @tecosaur knew when I was a little hesitant before, it is trivially easy to add support for all the named colors is Colors.jl.
This is very exciting. Is there documentation how to install this fork to help test?
You can download the package as a zip file from github and unzip to a local folder. Then do something like
using Pkg
Pkg.develop(path="pathToXLSX.jl")
using XLSX
(I think that’ll work).
Note: This link is to a slightly updated branch than the one I linked to before (link in previous post also updated).
You can add branches directly from Pkg like
(jl_9UWoAq) pkg> add https://github.com/TimG1964/XLSX.jl#Add-Row-Ranges-to-formatting
although I can’t get it to work for the Bug-fixing-post-#289 branch, Pkg claims it can’t find that rev in the repo, maybe because it gets confused by the # in the branch name (I did try the URL Bug-fixing-post-%23289 as well to no avail)
Does that merit an issue on Pkg, or is # something I shouldn’t have used in the branch name?
I honestly don’t know, my first guess is that I’m too dumb to properly escape the # character and there’s a way to make it work.
@TimG I would also try to reach out to @felipenoris to see if he could add you as the new maintainer of the original XLSX.jl package. I believe he is not using Julia these days.
3 Likes
Does the zip package approach work with #290? Also, it’d be nice to have a solution to the “hash in name” problem.
julia> using Pkg
julia> Pkg.add(url="https://github.com/TimG1964/XLSX.jl", rev="Bug-fixing-post-#289")
Cloning git-repo `https://github.com/TimG1964/XLSX.jl`
Updating git-repo `https://github.com/TimG1964/XLSX.jl`
Resolving package versions...
Installed InputBuffers ─ v1.0.1
Installed XML ────────── v0.3.5
Installed ColorTypes ─── v0.12.1
Updating `C:\Users\Sundar\.julia\environments\v1.12\Project.toml`
[fdbf4ff8] + XLSX v0.10.5-dev `https://github.com/TimG1964/XLSX.jl#Bug-fixing-post-#289`
Updating `C:\Users\Sundar\.julia\environments\v1.12\Manifest.toml`
[...]
Precompiling packages finished.
20 dependencies successfully precompiled in 12 seconds. 17 already precompiled.
Using the function call syntax instead of the pkg REPL mode seems to work fine.
2 Likes
IMO the fact that the pkg REPL mode doesn’t handle this correctly is itself worth creating an issue for, even if there’s an alternate way around that - not many people may know about or think to try out the Pkg.add function, so this seems a usability issue worth fixing.
From Nils’ comment, it looks like pkg>'s parsing is looking for the last # in the URL, whereas based on the URI specification, it should be using the first # in the URL as the start of the fragment part (the branch in this case).
(It’s pretty late for me, so I’ll just leave this comment here; please let me know if you’d prefer that I create the issue myself, I’ll try and get to it within a few days.)
2 Likes
I knew I should have tried that - thanks, issue created
3 Likes
There is a file, customXml.xlsx in the \data folder of this project on GitHub (here) that looks like a template file - a form to be filled in. I thought I’d use the version of XLSX.jl in this PR (#290) to try to create a replica of this form from scratch. The code below does a pretty good job, and makes extensive use of vector indexing for rows and columns and of non-contiguous ranges:
using XLSX
f = XLSX.newxlsx()
s = f[1]
s["A1:K116"] = ""
s["B2"] = "Catalogue Entry Form"
s["B5"] = "User Data"
s["B7"] = "Recipient ID"
s["B9"] = "Recipient Name"
s["B11"] = "Address 1"
s["B12"] = "Address 2"
s["B13"] = "Address 3"
s["B14"] = "Town"
s["B16"] = "Postcode"
s["B18"] = "Ward"
s["B20"] = "Region"
s["H18"] = "Local Authority"
s["H20"] = "UK Constituency"
s["B22"] = "GrantID"
s["D22"] = "Grant Date"
s["F22"] = "Grant Amount"
s["H22"] = "Grant Title"
s["J22"] = "Distributor"
s["B32"] = "Distributor"
s["B30"] = "Creator"
s["B34"] = "Created by"
s["D36"] = "Email"
s["H36"] = "Phone"
s["B38"] = "Grant Manager"
s["D40"] = "Email"
s["H40"] = "Phone number"
s["B43"] = "Summary"
s["B45"] = "Summary ID"
s["H45"] = "Date Created"
s["B47"] = "Summary Name"
s["B49"] = "Headline"
s["B51"] = "Short Description"
s["B55"] = "Long Description"
s["B62"] = "Quote 1"
s["D65"] = "Quote Attribution"
s["H65"] = "Quote Date"
s["B67"] = "Quote 2"
s["D70"] = "Quote Attribution"
s["H70"] = "Quote Date"
s["B72"] = "Keywords"
s["B74"] = "Website"
s["B76"] = "Social media handles"
s["D76"] = "Twitter"
s["D78"] = "Facebook"
s["D80"] = "Instagram"
s["H76"] = "LinkedIn"
s["H78"] = "TikTok"
s["H80"] = "YouTube"
s["B82"] = "Image 1 filename"
s["D84"] = "Alt-Text"
s["D86"] = "Image Attribution"
s["D88"] = "Image Date"
s["D90"] = "Confirm permission to use image"
s["B92"] = "Image 2 filename"
s["D94"] = "Alt-Text"
s["D96"] = "Image Attribution"
s["D98"] = "Image Date"
s["D100"] = "Confirm permission to use image"
s["B103"] = "Penultimate category"
s["B105"] = "Competition Details"
s["D105"] = "Last year of entry"
s["D107"] = "Year of last win"
s["H105"] = "Categories of entry"
s["H107"] = "Categories of win"
s["B110"] = "Last category"
s["B112"] = "Use for Comms"
s["D112"] = "Comms Priority"
s["F112"] = "Comms End Date"
XLSX.setColumnWidth(s, 1:2:11; width=1.3)
XLSX.setColumnWidth(s, 2:2:10; width=18)
XLSX.setRowHeight(s, :; height=15)
XLSX.setRowHeight(s, [3, 4, 19, 28, 29, 35, 39, 41, 42, 64, 69, 77, 79, 83, 85, 87, 89, 93, 95, 97, 99, 101, 102, 106, 108, 109, 116]; height=5.5)
XLSX.setRowHeight(s, [5, 30, 43, 103, 110]; height=18)
XLSX.setRowHeight(s, 2; height=23)
XLSX.setFont(s, "B2"; size=18, bold=true)
XLSX.setUniformFont(s, [5, 30, 43, 103, 110], 2; size=14, bold=true)
XLSX.setUniformFill(s, [1, 2, 3, 4, 5, 6, 8, 10, 15, 17, 19, 21, 28, 29, 30, 31, 33, 35, 37, 39, 41, 42, 43, 44, 46, 48, 50, 52, 53, 54, 56, 57, 58, 59, 60, 61, 63, 64, 66, 68, 69, 71, 73, 75, 77, 79, 81, 83, 85, 87, 89, 91, 93, 95, 97, 99, 101, 102, 103, 104, 106, 108, 109, 110, 111, 115, 116], :; pattern="solid", fgColor="lightgrey")
XLSX.setUniformFill(s, :, [1, 3, 5, 7, 9, 11]; pattern="solid", fgColor="lightgrey")
XLSX.setFill(s, "F7,H7,J7,J9,H11:J16,F14,F16:F20,H32:J32,B36,B40,F45,J47:J49,B65,B70,B78:B80,B84:B90,B94:B100,H88:J90,H98:J100,B107,F114,H112:J115"; pattern="solid", fgColor="lightgrey")
XLSX.setFill(s, "D18,D20,J18,J20,D45"; pattern="solid", fgColor="darkgrey")
XLSX.setFill(s, "B112:B114,D112:D115"; pattern="solid", fgColor="white")
XLSX.setFill(s, "E90,E100,D115"; pattern="none")
XLSX.mergeCells(s, "D9:H9")
XLSX.mergeCells(s, "D11:G11,D12:G12,D13:G13")
XLSX.mergeCells(s, "D32:F32,D34:J34,D38:J38")
XLSX.mergeCells(s, "D47:H47,D49:H49")
XLSX.mergeCells(s, "D51:J53,D55:J60")
XLSX.mergeCells(s, "D62:J63,D67:J68")
XLSX.mergeCells(s, "D72:J72,D74:J74")
XLSX.mergeCells(s, "D82:J82,F84:J84,F86:J86")
XLSX.mergeCells(s, "D92:J92,F94:J94,F96:J96")
XLSX.setAlignment(s, "D51:J53,D55:J60,D62:J63,D67:J68"; vertical="top", wrapText=true)
XLSX.setBorder(s, "A1:K3"; outside = ["style" => "medium", "color" => "black"])
XLSX.setBorder(s, "A4:K28"; outside = ["style" => "medium", "color" => "black"])
XLSX.setBorder(s, "A29:K41"; outside = ["style" => "medium", "color" => "black"])
XLSX.setBorder(s, "A42:K101"; outside = ["style" => "medium", "color" => "black"])
XLSX.setBorder(s, "A102:K108"; outside = ["style" => "medium", "color" => "black"])
XLSX.setBorder(s, "A109:K116"; outside = ["style" => "medium", "color" => "black"])
XLSX.setBorder(s, "B7:D7,B9:H9"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B11:G13,B14:D14,B16:D16"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B18:D18,B20:D20,H18:J18,H20:J20"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setUniformBorder(s, "B22:J27"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B32:F32"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B34:C34,D34:J34,D36:F36,H36:J36"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B38:C38,D38:J38,D40:F40,H40:J40"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "D34:J36,D38:J40"; outside = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B45:D45,H45:J45"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B47:H47,B49:H49"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B51:C51,B55:C55"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "D51:J53,D55:J60"; outside = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B62:C62,D65:F65,H65:J65"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B67:C67,D70:F70,H70:J70"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "D62:J63,D67:J68"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "D62:J65,D67:J70"; outside = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B72:J72,B74:J74"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B76:F76,H76:J76,D78:F78,H78:J78,D80:F80,H80:J80"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "D76:J80"; outside = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B82:J82,D84:J84,D86:J86,D88:F88,D90:F90"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "D82:J90"; outside = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B92:J92,D94:J94,D96:J96,D98:F98,D100:F100"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "D92:J100"; outside = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B105:F105,H105:J105,D107:F107,H107:J107"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "D105:J107"; outside = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "F112,F113"; allsides = ["style" => "thin", "color" => "black"])
XLSX.setBorder(s, "B112:B114,D112:D115"; outside = ["style" => "thin", "color" => "black"])
XLSX.writexlsx("myNewTemplate.xlsx", f, overwrite=true)
1 Like
Another quick update. I’ve now added support for all of Excel’s conditional formatting:
I’ve also written a formatting guide as part of the docs.
Latest repo is here.
Feedback welcome!
12 Likes
Very nice ! Can you provide the location of this formatting guide in the docs ? I checked the dev / stable docs, but I could not see any section about this.
The admonishments don’t look quite right - they look different when they have been built by Documenter (as they would be, I think, if the PR is merged/released).
4 Likes
Lovely to see good detailed docs like this, thanks for putting in the effort.
1 Like
Thanks for the link. I think I see the problem. The links in your readme still point to felipenoris’ (official) branch so the docs are not containing your modifications. Cheers !
1 Like
I had not updated the readme.md (my bad!) file but I have now. It still points to the official branch for the docs because (I hope) this is where my code will end up if the PR is accepted.