xlsxwriter: is there a way to open an existing worksheet in my workbook?

Question

I'm able to open my pre-existing workbook, but I don't see any way to open pre-existing worksheets within that workbook. Is there any way to do this?

Edwardoedwards

xlsxwriter library is for writing excel files - it cannot read them. – Lelia

see this #18850035 – Kilowatthour

Answer 1

You cannot append to an existing xlsx file with xlsxwriter.

There is a module called openpyxl which allows you to read and write to preexisting excel file, but I am sure that the method to do so involves reading from the excel file, storing all the information somehow (database or arrays), and then rewriting when you call workbook.close() which will then write all of the information to your xlsx file.

Similarly, you can use a method of your own to "append" to xlsx documents. I recently had to append to a xlsx file because I had a lot of different tests in which I had GPS data coming in to a main worksheet, and then I had to append a new sheet each time a test started as well. The only way I could get around this without openpyxl was to read the excel file with xlrd and then run through the rows and columns...

i.e.

cells = []
for row in range(sheet.nrows):
    cells.append([])
    for col in range(sheet.ncols):
        cells[row].append(workbook.cell(row, col).value)

You don't need arrays, though. For example, this works perfectly fine:

import xlrd
import xlsxwriter

from os.path import expanduser
home = expanduser("~")

# this writes test data to an excel file
wb = xlsxwriter.Workbook("{}/Desktop/test.xlsx".format(home))
sheet1 = wb.add_worksheet()
for row in range(10):
    for col in range(20):
        sheet1.write(row, col, "test ({}, {})".format(row, col))
wb.close()

# open the file for reading
wbRD = xlrd.open_workbook("{}/Desktop/test.xlsx".format(home))
sheets = wbRD.sheets()

# open the same file for writing (just don't write yet)
wb = xlsxwriter.Workbook("{}/Desktop/test.xlsx".format(home))

# run through the sheets and store sheets in workbook
# this still doesn't write to the file yet
for sheet in sheets: # write data from old file
    newSheet = wb.add_worksheet(sheet.name)
    for row in range(sheet.nrows):
        for col in range(sheet.ncols):
            newSheet.write(row, col, sheet.cell(row, col).value)

for row in range(10, 20): # write NEW data
    for col in range(20):
        newSheet.write(row, col, "test ({}, {})".format(row, col))
wb.close() # THIS writes

However, I found that it was easier to read the data and store into a 2-dimensional array because I was manipulating the data and was receiving input over and over again and did not want to write to the excel file until it the test was over (which you could just as easily do with xlsxwriter since that is probably what they do anyway until you call .close()).

Iver

I really wouldn't say "extremely complex structure". The structure is just a zipfile archive of sheets in which each sheet is an XML file with the cell contents. It's quite easy to access, read, and edit, just XlsxWriter intends to be a writer. Not a reader. – Clubhaul

@AlexanderHuszagh: I think how complex it seems to be depends on what you're trying to get out of it. If you just need cell values, then it is indeed fairly simple to read. But if you want to extract formulas and formatting and such, it is not as simple. Maybe still not "extremely complex", but complex enough that no one is rushing in to plug the functionality gaps in xlrd and OpenPyXL. (Even Apache's POI, written in Java, has a few gaps.) Also, I think it's complex enough that you can screw up a preexisting file if you try to write to it without knowing what you're doing. – Inconsecutive

xlrd==1.2.0 -- This version reads .xlsx extension. Latest xlrd is throwing error. – Rosalinarosalind

Seems as though you can now install and use xlrd2 in my instance I used. import xlrd2 as xlrd so that the rest of the code would function. – Agueda

Answer 2

After searching a bit about the method to open the existing sheet in xlxs, I discovered

existingWorksheet = wb.get_worksheet_by_name('Your Worksheet name goes here...')
existingWorksheet.write_row(0,0,'xyz')

You can now append/write any data to the open worksheet.

Hippie

Hi, how do you open the Workbook for reading/writing? When I tried this function it can only get the worksheet after it has been created by your script. Based on the documentation, you cannot read or modify (xlsxwriter.readthedocs.io/introduction.html) – Bluebonnet

I still wonder how you can open the existing excel file which was the original question. I don't think this answers that one. – Musket

Why does this answer have any upvotes? The question is "xlsxwriter: is there a way to open an existing worksheet in my workbook". The answer is that Xlsxwriter only creates new workbook from scratch, there is no way to open and edit an existing Excel file so this answer is wrong. – Acetylate

Answer 3

Although it is mentioned in the last two answers with it's documentation link, and from the documentation it seems indeed there are new methods to work with the "worksheets", I couldn't able to find this methods in the latest package of "xlsxwriter==3.0.3"

"xlrd" has removed support for anything other than xls files now.

Hence I was able to workout with "openpyxl" this gives you the expected functionality as mentioned in the first answer above.

Chlori

Answer 4

You can use the workbook.get_worksheet_by_name() feature: https://xlsxwriter.readthedocs.io/workbook.html#get_worksheet_by_name

According to https://xlsxwriter.readthedocs.io/changes.html the feature has been added on May 13, 2016.

"Release 0.8.7 - May 13 2016

-Fix for issue when inserting read-only images on Windows. Issue #352.

-Added get_worksheet_by_name() method to allow the retrieval of a worksheet from a workbook via its name.

-Fixed issue where internal file creation and modification dates were in the local timezone instead of UTC."

Coition

This only works for a workbook/worksheet created with XlsxWriter. XlsxWriter cannot read an existing file. – Cupcake

That is good to know @jmcnamara! I was just struggling with getting a Nonetype Error from trying to access an existing dummy file, and this explains the error. – Coition

Answer 5

xlsxwriter is very problematic for writing into an existing workbook/worksheet. However, using openpyxl is very simple to do this. Here is a short tutorial: https://statusneo.com/excel-automation-with-pythons-openpyxl-a-comprehensive-guide/

Nummary

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Complected