Python xlsxwriter：有没有办法在我的工作簿中打开现有的工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18002133/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
xlsxwriter: is there a way to open an existing worksheet in my workbook?
提问by Nick
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?
我能够打开我预先存在的工作簿，但我没有看到任何方法可以打开该工作簿中预先存在的工作表。有没有办法做到这一点？
回答by dylnmc
You cannot append to an existing xlsx file with xlsxwriter.
您不能使用 .xlsx 文件附加到现有的 xlsx 文件xlsxwriter。
There is a module called openpyxlwhich 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.
有一个名为openpyxl的模块，它允许您读取和写入预先存在的 excel 文件，但我确信这样做的方法涉及从 excel 文件读取，以某种方式存储所有信息（数据库或数组），然后在您调用workbook.close()它，然后将所有信息写入您的 xlsx 文件。
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 xlrdand then run through the rows and columns...
同样，您可以使用自己的方法“附加”到 xlsx 文档。我最近不得不附加到一个 xlsx 文件，因为我有很多不同的测试，其中我将 GPS 数据输入到主工作表中，然后每次测试开始时我也必须附加一个新表。我可以在没有 openpyxl 的情况下解决此问题的唯一方法是使用xlrd读取 excel 文件，然后遍历行和列...
i.e.
IE
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()).
但是，我发现读取数据并存储到二维数组中更容易，因为我正在操作数据并且一遍又一遍地接收输入并且不想写入excel文件，直到测试结束（您可以使用 xlsxwriter 轻松地做到这一点，因为在您致电之前这可能是他们所做的.close()）。
回答by Dikshit Kathuria
After searching a bit about the method to open the existing sheet in xlxs, i discovered
在搜索了一些关于在 xlxs 中打开现有工作表的方法后，我发现
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. I hope it helps. Thanks
您现在可以将任何数据附加/写入打开的工作表。我希望它有帮助。谢谢
回答by jeppoo1
You can use the workbook.get_worksheet_by_name()feature: https://xlsxwriter.readthedocs.io/workbook.html#get_worksheet_by_name
您可以使用workbook.get_worksheet_by_name()功能：https: //xlsxwriter.readthedocs.io/workbook.html#get_worksheet_by_name
According to https://xlsxwriter.readthedocs.io/changes.htmlthe feature has been added on May 13, 2016.
根据https://xlsxwriter.readthedocs.io/changes.html，该功能已于 2016 年 5 月 13 日添加。
"Release 0.8.7 - May 13 2016
“版本 0.8.7 - 2016 年 5 月 13 日
-Fix for issue when inserting read-only images on Windows. Issue #352.
- 修复了在 Windows 上插入只读图像时出现的问题。问题 #352。
-Added get_worksheet_by_name() method to allow the retrieval of a worksheet from a workbook via its name.
-添加了 get_worksheet_by_name() 方法以允许通过其名称从工作簿中检索工作表。
-Fixed issue where internal file creation and modification dates were in the local timezone instead of UTC."
- 修复了内部文件创建和修改日期在本地时区而不是 UTC 的问题。”
