How to append a single row to an Excel file using XLSX?

user_12115498 0
Viewed 669

(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:

  1. How do I find the first blank row and insert the new row there?
  2. Why is it that when I set 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!

openxlsx julia xlsx excel
2 Answers

I understand your question is about two part - adding a sheet and finding where is the last row in a sheet.

Ad. 1

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

Ad. 2

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

user_99577100

Here is the function that does what I was looking for (credit for this goes to Przemyslaw Szufel!):

function append_xl_row(workbook_path::String, sheet_name::String, row_data::Array)
    
    XLSX.openxlsx(workbook_path, mode="rw") do xf
        sheet = xf[sheet_name]
        num_rows = XLSX.get_dimension(sheet).stop.row_number
        
        if num_rows == 1
            sheet[1,1] = row_data
        else
            sheet[num_rows+1,:] = row_data
        end
    end
end

The reason for the if num_rows == 1 part is that the number of rows in a new (blank) spreedsheet is 1 apparently. So in that case, we want to add the row data to the very first row. Otherwise, num_rows + 1 is the row we add it to.

user_121154980
Related