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

New to Julia
,
1

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. 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

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!

2

Update 3/4/21: I’m still searching for an answer to this question and would greatly any help! If I don’t get any replies here, I’ll be reposting this on Stack Overflow…

3

Hi,

Please check the API refference:
https://felipenoris.github.io/XLSX.jl/stable/api/

You need to XLSX.openxlsx(wb_path, mode="rw"). If you select only w mode, it will create a new file.

Filemodes

The mode argument controls how the file is opened. The following modes are allowed:

r : read mode. The existing data in filepath will be accessible for reading. This is the default mode.

w : write mode. Opens an empty file that will be written to filepath.

rw : edit mode. Opens filepath for editing. The file will be saved to disk when the function ends.
1 Like
4

Hi, thanks for your reply.

I have read the API and am able to write to new and existing files (as shown in the code I posted). My question is specifically on how to append a single row to the end of an existing set of rows in a spreadsheet.

To provide some more context:
I have a long-running program (takes up to ~7 hours to finish) which produces a line of output every few minutes. I could wait for the program to completely finish running and then write all of the data to Excel at once. However, the program occasionally crashes, so it would be much safer for me to write each line of output to Excel as it is produced. Otherwise, I risk losing data.

Is my question more clear now?

5

Perhaps as a workaround you could at each loop iteration read all previous lines in the sheet into a matrix or other structure, append the new row, then write all the data out to the file, overwriting the previous contents.

6

Thanks, I may try that for now, though it seems inefficient. I’ll see if anyone on SO has some additional advice.

7

I might be missing something here, but isn’t that exactly what the documentation explains?

Running the example that shows how to Create New Files, I end up with a file that looks as follows:

image

When I then slightly amend the code in the following section Edit Existing Files to read:

julia> XLSX.openxlsx("my_new_file.xlsx", mode="rw") do xf
           sheet = xf[1]
           sheet["A10"] = collect(1:3)
       end

I get:
image

So the new data has been added at row 10.

8

Hi, thanks for your reply. Basically I wanted to have a function that would determine the number of existing rows, so that I wouldn’t have to actually open the workbook and check. Someone gave a very helpful answer on Stack Overflow that allowed me to figure it out.

9

Here’s the solution I was looking for (special thanks to the person on Stack Overflow who helped me!):

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.

2 Likes