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

**URL:** <https://discourse.julialang.org/t/how-to-append-a-single-row-to-an-excel-file-using-xlsx/56203>\
**Category:** New to Julia\
**Tags:** xlsx, io\
**Created:** [February 28, 2021, 6:16pm UTC](https://discourse.julialang.org/t/how-to-append-a-single-row-to-an-excel-file-using-xlsx/56203 "2021-02-28T18:16:43Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Michael\_Barmann](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/michael_barmann/32/20389_2.png) [@Michael\_Barmann](https://discourse.julialang.org/u/Michael_Barmann)\
**Post date:** [February 28, 2021, 6:16pm UTC](https://discourse.julialang.org/t/how-to-append-a-single-row-to-an-excel-file-using-xlsx/56203/1 "2021-02-28T18:16:43Z")

</div>

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:

```julia
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:

```julia
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:

```julia
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

```julia
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”.:

```julia
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!

---

<div class="post-metadata">

**Author:** ![Michael\_Barmann](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/michael_barmann/32/20389_2.png) [@Michael\_Barmann](https://discourse.julialang.org/u/Michael_Barmann)\
**Post date:** [March 4, 2021, 11:44pm UTC](https://discourse.julialang.org/t/how-to-append-a-single-row-to-an-excel-file-using-xlsx/56203/2 "2021-03-04T23:44:20Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![Iulian.Cioarca](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/iulian.cioarca/32/30166_2.png) [@Iulian.Cioarca](https://discourse.julialang.org/u/Iulian.Cioarca)\
**Post date:** [March 5, 2021, 7:32am UTC](https://discourse.julialang.org/t/how-to-append-a-single-row-to-an-excel-file-using-xlsx/56203/3 "2021-03-05T07:32:33Z")

</div>

Hi,

Please check the API refference:  
[https://felipenoris.github.io/XLSX.jl/stable/api/](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.

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

```

---

<div class="post-metadata">

**Author:** ![Michael\_Barmann](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/michael_barmann/32/20389_2.png) [@Michael\_Barmann](https://discourse.julialang.org/u/Michael_Barmann)\
**Post date:** [March 5, 2021, 7:39pm UTC](https://discourse.julialang.org/t/how-to-append-a-single-row-to-an-excel-file-using-xlsx/56203/4 "2021-03-05T19:39:18Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![PeterSimon](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/petersimon/32/25193_2.png) [@PeterSimon](https://discourse.julialang.org/u/PeterSimon)\
**Post date:** [March 5, 2021, 10:29pm UTC](https://discourse.julialang.org/t/how-to-append-a-single-row-to-an-excel-file-using-xlsx/56203/5 "2021-03-05T22:29:37Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Michael\_Barmann](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/michael_barmann/32/20389_2.png) [@Michael\_Barmann](https://discourse.julialang.org/u/Michael_Barmann)\
**Post date:** [March 7, 2021, 7:07pm UTC](https://discourse.julialang.org/t/how-to-append-a-single-row-to-an-excel-file-using-xlsx/56203/6 "2021-03-07T19:07:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 7, 2021, 8:24pm UTC](https://discourse.julialang.org/t/how-to-append-a-single-row-to-an-excel-file-using-xlsx/56203/7 "2021-03-07T20:24:21Z")

</div>

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](https://felipenoris.github.io/XLSX.jl/stable/tutorial/#Create-New-Files), I end up with a file that looks as follows:

![image](https://global.discourse-cdn.com/julialang/original/3X/9/5/95fa5468f827a2b4feb3ba40667fb8d736257e4a.png)

When I then slightly amend the code in the following section [Edit Existing Files](https://felipenoris.github.io/XLSX.jl/stable/tutorial/#Edit-Existing-Files) to read:

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

```

I get:  
 ![image](https://global.discourse-cdn.com/julialang/original/3X/5/6/56ac6b4e2a614581680d5a2226f282f67b5523af.png)

So the new data has been added at row 10.

---

<div class="post-metadata">

**Author:** ![Michael\_Barmann](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/michael_barmann/32/20389_2.png) [@Michael\_Barmann](https://discourse.julialang.org/u/Michael_Barmann)\
**Post date:** [March 12, 2021, 7:09pm UTC](https://discourse.julialang.org/t/how-to-append-a-single-row-to-an-excel-file-using-xlsx/56203/8 "2021-03-12T19:09:49Z")

</div>

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](https://stackoverflow.com/questions/66520379/how-to-append-a-single-row-to-an-excel-file-using-xlsx) on Stack Overflow that allowed me to figure it out.

---

<div class="post-metadata">

**Author:** ![Michael\_Barmann](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/michael_barmann/32/20389_2.png) [@Michael\_Barmann](https://discourse.julialang.org/u/Michael_Barmann)\
**Post date:** [March 12, 2021, 7:17pm UTC](https://discourse.julialang.org/t/how-to-append-a-single-row-to-an-excel-file-using-xlsx/56203/9 "2021-03-12T19:17:58Z")

</div>

Here’s the solution I was looking for (special thanks to the person on Stack Overflow who [helped](https://stackoverflow.com/questions/66520379/how-to-append-a-single-row-to-an-excel-file-using-xlsx) me!):

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