xlsxwriter:有没有办法在我的工作簿中打开现有的工作表? [英] xlsxwriter: is there a way to open an existing worksheet in my workbook?

问题描述

我能够打开我预先存在的工作簿，但我没有看到任何方法可以打开该工作簿中预先存在的工作表.有没有办法做到这一点?

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?

推荐答案

您不能使用 xlsxwriter 附加到现有的 xlsx 文件.

You cannot append to an existing xlsx file with xlsxwriter.

有一个名为 openpyxl 的模块，它允许您读取和写入预先存在的 excel 文件，但我确信该方法可以这样做涉及从 excel 文件中读取，以某种方式存储所有信息(数据库或数组)，然后在您调用 workbook.close() 时重写，然后将所有信息写入您的 xlsx 文件.

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.

同样，您可以使用自己的方法附加"到 xlsx 文档.我最近不得不附加到一个 xlsx 文件，因为我有很多不同的测试，其中我将 GPS 数据输入到主工作表中，然后每次测试开始时我也必须附加一个新表.在没有 openpyxl 的情况下，我可以解决此问题的唯一方法是使用 xlrd 读取 excel 文件，然后遍历行和列...

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

即

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

但是，我发现读取数据并存储到二维数组中更容易，因为我正在操作数据并且一遍又一遍地接收输入并且不想写入excel文件直到它测试结束(您可以使用 xlsxwriter 轻松完成，因为在您调用 .close() 之前这可能是他们所做的).

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

这篇关于xlsxwriter:有没有办法在我的工作簿中打开现有的工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！