Generate excel spreadsheet from julia equations?

Specific Domains Data
, , ,
1

Hi all,

Imagine a spreadsheet with columns A, B, C. Suppose A are observations, and B = B(A), C = C(A, B) are defined by excel formulas involving the previous columns.

This is a pretty common scenario in excel. I was just wondering - is there any way to express these relationships between columns in Julia, and automatically generate the excel formulas? Perhaps through a custom ExcelNumber type <: Real, as in autodiff? Or with macros?

It also brings up the idea of a symbolic DataFrame with derived columns, not sure whether that exists in Julia either.

Has anyone heard of anything like this?

Thanks,
Oliver

2

I’m not sure I understand this use case but it is now possible to add Excel formulas to cells in XLSX.jl

If you have, for example, a set of numbers in column A and you want to add a formula in column B to transform them and another formula in column C to combine both A and B in some way:

julia> xf = newxlsx() # get an empty workbook
XLSXFile("blank.xlsx") containing 1 Worksheet
            sheetname size          range        
-------------------------------------------------
               Sheet1 1x1           A1:A1        

julia> sh = xf[1]
1×1 Worksheet: ["Sheet1"](A1:A1) 

julia> sh["A1:A10"] = rand(10) # Put numbers in column A
10-element Vector{Float64}:
 0.9281604119349532
 0.973226606363062
 0.9342114838424147
 0.7498942494260311
 0.403684440253019
 0.8638950077006925
 0.33670290222504884
 0.12828832527907552
 0.9209867010267868
 0.22418973944607234

julia> setFormula(sh, "B1:B10", "=A1^2+4") # Add a formula to Column B
"=A1^2+4"

julia> setFormula(sh, "C1:C10", "=(B1-4)/A1") # Add a formula to Column C
"=(B1-4)/A1"

julia> XLSX.getFormula(sh, "C10") # What formula was added to cell "C10"? Formulas fill down correctly.
"=(B10-4)/A10"

julia> writexlsx("formulas.xlsx", xf)
"...\\formulas.xlsx"

image
image361×286 6.13 KB

You can add pretty much any Excel formula this way. Cell values are not recalculated in XLSX.jl (it doesn’t replicate Excel’s calculation engine), but they recompute automatically when the file is opened by Excel.