# Exporting Excel data to an already existing .xlsx file

**URL:** <https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790>\
**Category:** General Usage\
**Created:** [January 18, 2019, 5:09pm UTC](https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790 "2019-01-18T17:09:40Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![ale](https://avatars.discourse-cdn.com/v4/letter/a/f19dbf/32.png) [@ale](https://discourse.julialang.org/u/ale)\
**Post date:** [January 18, 2019, 5:09pm UTC](https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790/1 "2019-01-18T17:09:40Z")

</div>

Hi,

I am trying to export different arrays to different sheets of an already existing Excel file. The format of this file is .xlsx.  
Since I need to have several sheets in the same file, I can’t use “CSV”.  
Secondly, it seems to me that “ExcelReaders” works just for reading files, which is great and easy to use, but doesn’t help now in the exporting phase.  
Thirdly, I have tried “XLSX” ([https://felipenoris.github.io/XLSX.jl/stable/tutorial.html#Writing-Excel-Files-1](https://felipenoris.github.io/XLSX.jl/stable/tutorial.html#Writing-Excel-Files-1)). It seems to me that if I try to export data in DataFrame format, I am forced to export them to a new Excel file all the times.  
However, exporting them as explained in the section “[Edit Existing Files](https://felipenoris.github.io/XLSX.jl/stable/tutorial.html#Edit-Existing-Files-1)” at the same link seems extremely time consuming and would require many lines of code to simply export different arrays to different sheets in the same .xslx file.

Does anyone have any suggestions?

P.S. I am aware that “Taro” exists, however I don’t have Java on my computer and I would like to avoid installing it if possible!

---

<div class="post-metadata">

**Author:** ![felipenoris](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/felipenoris/32/553_2.png) [@felipenoris](https://discourse.julialang.org/u/felipenoris)\
**Post date:** [February 16, 2019, 4:49pm UTC](https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790/2 "2019-02-16T16:49:36Z")

</div>

I guess you can try to use:

```julia
XLSX.writetable!(sheet::Worksheet, data, columnnames; anchor_cell::CellRef=CellRef("A1"))

```

where `sheet` is a reference to the target worksheet, `data` is a vector of columns, and `columnnames` is a vector of column names.

From a `df::DataFrame`, you can set:

```julia
data = DataFrames.columns(df)
columnnames = DataFrames.names(df)

```

You must first open workbook in read-write mode:

```julia
XLSX.openxlsx("file.xlsx", mode="rw") do xf
    sheet = xf[1]
    XLSX.writetable!(sheet, data, columnnames)
end

```

---

<div class="post-metadata">

**Author:** ![ale](https://avatars.discourse-cdn.com/v4/letter/a/f19dbf/32.png) [@ale](https://discourse.julialang.org/u/ale)\
**Post date:** [February 18, 2019, 11:20am UTC](https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790/3 "2019-02-18T11:20:25Z")

</div>

@felipenoris thank you so much, this worked! 🙂

For the sake of completeness, let me just add how to create a new worksheet (if not already existing in the workbook, like it was in my case):

```julia
XLSX.openxlsx(file_name, mode="rw") do xf
    XLSX.addsheet!(xf, "new sheet name")
    sheet = xf[2] # EDIT: this works if there was only 1 sheet before. 
                      # If there were already 2 or more sheets: see comments below.
    XLSX.writetable!(sheet, data, columnnames; anchor_cell=XLSX.CellRef("A1"))
end

```

---

<div class="post-metadata">

**Author:** ![ale](https://avatars.discourse-cdn.com/v4/letter/a/f19dbf/32.png) [@ale](https://discourse.julialang.org/u/ale)\
**Post date:** [February 22, 2019, 6:34pm UTC](https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790/4 "2019-02-22T18:34:35Z")

</div>

@felipenoris I have a new problem ☹

> [@ale](#):
>
> sheet = xf[2]

only works if before there was just one sheet in the Excel file.  
That is, if there were e.g. two sheets already, I should write:

```julia
sheet = xf[3]

```

Now I wonder: aiming to keep the code more general, **is there a way to count the number of sheets contained in an Excel file**?

If that was possible, let’s denote that value by `numb_of_sheets`, the issue would be solved by something like:

```julia
XLSX.openxlsx(file_name, mode="rw") do xf
    XLSX.addsheet!(xf, "new sheet name")
    sheet = xf[numb_of_sheets+1]
    XLSX.writetable!(sheet, data, columnnames; anchor_cell=XLSX.CellRef("A1"))
end

```

Thanks in advance!

---

<div class="post-metadata">

**Author:** ![felipenoris](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/felipenoris/32/553_2.png) [@felipenoris](https://discourse.julialang.org/u/felipenoris)\
**Post date:** [February 22, 2019, 7:09pm UTC](https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790/5 "2019-02-22T19:09:26Z")

</div>

You can use `XLSX.sheetcount` method.

```julia
"""
Counts the number of sheets in the Workbook.
"""
@inline sheetcount(wb::Workbook) = length(wb.sheets)
@inline sheetcount(xl::XLSXFile) = sheetcount(xl.workbook)

```

---

<div class="post-metadata">

**Author:** ![ale](https://avatars.discourse-cdn.com/v4/letter/a/f19dbf/32.png) [@ale](https://discourse.julialang.org/u/ale)\
**Post date:** [February 22, 2019, 9:27pm UTC](https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790/6 "2019-02-22T21:27:34Z")

</div>

@felipenoris thank you very much for your prompt reply, this works!

---

<div class="post-metadata">

**Author:** ![sylvaticus](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sylvaticus/32/203883_2.png) [@sylvaticus](https://discourse.julialang.org/u/sylvaticus)\
**Post date:** [February 23, 2019, 12:12am UTC](https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790/7 "2019-02-23T00:12:55Z")

</div>

If you can use ods, there is also OdsIO that allows export of Array or DF to a specific place in the destination spreadsheet:

julia\> ods\_write(“TestSpreadsheet.ods”,Dict((“TestSheet”,3,2)=\>[[1,2,3,4,5] [6,7,8,9,10]]))

---

<div class="post-metadata">

**Author:** ![ale](https://avatars.discourse-cdn.com/v4/letter/a/f19dbf/32.png) [@ale](https://discourse.julialang.org/u/ale)\
**Post date:** [March 5, 2019, 6:13pm UTC](https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790/8 "2019-03-05T18:13:55Z")

</div>

@felipenoris I would have another question related to the topic.  
As you suggested above, the following works:

```julia
XLSX.openxlsx(file_name, mode="rw") do xf
    numb_of_sheets = XLSX.sheetcount(xf)
    XLSX.addsheet!(xf, "new sheet name")
    sheet = xf[numb_of_sheets+1]
    XLSX.writetable!(sheet, data, columnnames; anchor_cell=XLSX.CellRef("A1"))
end

```

I’m now wondering if it is also possible to overwrite a certain existing sheet in a given Excel file. I would be interested in deleting the content of such a sheet, and replace with new data.

I tried adding `overwrite = true` in different part of the previous expression and made different attempts in changing/removing some lines, but still unsuccessfully. Would you have any suggestion?

Thank you in advance!

---

<div class="post-metadata">

**Author:** ![felipenoris](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/felipenoris/32/553_2.png) [@felipenoris](https://discourse.julialang.org/u/felipenoris)\
**Post date:** [March 12, 2019, 9:07pm UTC](https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790/9 "2019-03-12T21:07:26Z")

</div>

@ale, if you just assign data to an existing cell, the data will be overwritten.  
You should probably open an [issue](https://github.com/felipenoris/XLSX.jl/issues) asking for a method for deleting sheet data, if you want to wipe out everything.

---

<div class="post-metadata">

**Author:** ![Chris\_Swan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/chris_swan/32/13764_2.png) [@Chris\_Swan](https://discourse.julialang.org/u/Chris_Swan)\
**Post date:** [July 10, 2021, 2:21am UTC](https://discourse.julialang.org/t/exporting-excel-data-to-an-already-existing-xlsx-file/19790/10 "2021-07-10T02:21:08Z")

</div>

Awesome, this whole post is super useful!