How can I write an array to a specific excel sheet beginning at specific cell using Julia XLSX.jl?

user_18063060 0
Viewed 198

Coming from MATLAB, the MATLAB way to write an array to a specific sheet and from a specific cell is straight forward:

xlswrite("filename",array,"sheetname2","cell")

I've tried reading the tutorials on how do do this using the Julia XLSX.jl package, but I just can't understand what's going on or how to do it. Is there a "straight forward" way to do this in Julia using XLSX.jl, or is there another package I could use that has easier syntax? At the beginning of my Julia script I have:

using DataFrames, XLSX
data = DataFrame(XLSX.readtable("filename","sheetname1")...)

which works just fine.

I must say, Julia is fast and free, but many Julia tutorials aren't written for the Julia newbie and leave alot of the syntax unexplained.

julia export-to-excel
2 Answers

I believe the other answer is not correct so here is mine.

Suppose you have the following Excel file with two sheets (BTW notice this very useful syntax for creating multi-sheet Excel files):

using XLSX, DataFrames
df = DataFrame(a=1:3, b=string.('a':'c'), d=0.1:0.1:0.3)
XLSX.writetable("file.xlsx", sheet1=(eachcol(df), names(df)),
                             sheet2=(eachcol(df), names(df)))

I understand the goal is to add some data to let's say sheet1. This can be accomplished by:

XLSX.openxlsx("file.xlsx", mode="rw") do xf
   XLSX.writetable!(xf["sheet1"], eachcol(df), names(df); anchor_cell=XLSX.CellRef("A6"))
end

Let us see the result:

enter image description here

If you rather want just to put a matrix use setdata!:

julia> mx = rand(3,2)
3×2 Matrix{Float64}:
 0.626637  0.245274
 0.560975  0.59444
 0.439289  0.0400645

julia> XLSX.openxlsx("file.xlsx", mode="rw") do xf
                XLSX.setdata!(xf["sheet1"], XLSX.CellRef("A6"), mx)
       end

enter image description here

user_99577100

This is what finally did it for me (A is the array name):

XLSX.openxlsx("filename", mode="rw") do xf
for i = 1:size(A,1)
XLSX.setdata!(xf["sheet"], XLSX.CellRef(1+i,4), A[i,1])
XLSX.setdata!(xf["sheet"], XLSX.CellRef(1+i,5), A[i,2])
end
end
user_180630600
Related