(Full disclosure: I posted this on Discourse a week ago, but it didn't receive much attention or help.)
I’m trying to write a simple function that appends a row to an existing Excel spreadsheet. The new row should be appended to the “bottom” of the data in the spreadsheet. For my purposes, I’m assuming that if there are existing rows in the sheet, there are no blank rows in between, so the function just needs to insert the new row into the first blank row.
Here’s what I have so far:
using XLSX
function append_xl_row(wb_path::String, sheet_name::String, row_data::Array)
XLSX.openxlsx(wb_path, mode = "w") do xf
sheet = xf[sheet_name]
sheet["A1"] = row_data #change this so it appends to the end
end
end
To test the function out, I created a new workbook and sheet:
wb_path = "C:/Users/Michael/Documents/test.xlsx"
sheet_name = "Sheet1"
row_data = [1, 1, 2, 3, 5, 8, 13, 21]
XLSX.openxlsx(wb_path, mode="w") do xf
XLSX.addsheet!(xf, sheet_name)
end
This currently writes the data to the first row of the Excel sheet.
My questions are:
sheet_name to be anything other than “Sheet1” (for example, “Sheet2” or “my_sheet”), I get the following error:Sheet2 is not a valid sheetname or cell/range reference.
Stacktrace:
[1] error(::String) at .\error.jl:33
[2] getdata(::XLSX.XLSXFile, ::String) at C:\Users\Michael\.julia\packages\XLSX\A7wWu\src\workbook.jl:130
[3] getindex(::XLSX.XLSXFile, ::String) at C:\Users\Michael\.julia\packages\XLSX\A7wWu\src\workbook.jl:93
[4] (::var"#103#104"{String,Array{Int64,1}})(::XLSX.XLSXFile) at .\In[125]:5
[5] openxlsx(::var"#103#104"{String,Array{Int64,1}}, ::String; mode::String, enable_cache::Bool) at C:\Users\Michael\.julia\packages\XLSX\A7wWu\src\read.jl:129
[6] append_xl_row(::String, ::String, ::Array{Int64,1}) at .\In[125]:4
[7] top-level scope at In[129]:1
[8] include_string(::Function, ::Module, ::String, ::String) at .\loading.jl:1091
...Huh?? If I just do If I just do
wb_path = "C:/Users/Michael/Documents/test.xlsx"
sheet_name = "Sheet2"
row_data = [1, 1, 2, 3, 5, 8, 13, 21]
XLSX.openxlsx(wb_path, mode="w") do xf
XLSX.addsheet!(xf, sheet_name)
end
then I confirmed it does create a new sheet named "Sheet2".:
1×1 XLSX.Worksheet: ["Sheet2"](A1:A1)
I checked and the new sheet appears in the Excel workbook. Yet I still get the "not a valid sheetname or cell/range reference" error when I execute append_xl_row....Why does this happen?
Would greatly appreciate any help!
I understand your question is about two part - adding a sheet and finding where is the last row in a sheet.
Try this code:
XLSX.openxlsx("my_new_file.xlsx", mode="w") do xf
sheet = xf[1]
XLSX.rename!(sheet, "My new name")
sheet["A1"] = "this is a new file"
sheet["E3:G5"] = collect(reshape(1:9,(3,3)))
XLSX.addsheet!(xf, "Name2")
sheet = xf[2]
sheet["B1"] = "this is some data in the second sheet"
end
Let us open the workbook from the previous example:
julia> f = XLSX.openxlsx("my_new_file.xlsx")
XLSXFile("my_new_file.xlsx") containing 2 Worksheets
sheetname size range
-------------------------------------------------
My new name 5x7 A1:G5
Name2 1x2 A1:B1
We want to find out the number of rows. This can be done by:
julia> XLSX.get_dimension(f[1])
A1:G5
You can see that we get the range. It seems that currently XLSX does not have methods to manipulate ranges. In such situation one uses dump:
julia> dump(XLSX.get_dimension(f[1]))
XLSX.CellRange
start: XLSX.CellRef
name: String "A1"
row_number: Int64 1
column_number: Int64 1
stop: XLSX.CellRef
name: String "G5"
row_number: Int64 5
column_number: Int64 7
So now it is easy what to do
julia> number_of_rows = XLSX.get_dimension(f[1]).stop.row_number
5
All you need now is to start mutating your spreadsheet starting from the row number_of_rows+1