使用win32com在Python中将数组写入Excel

Question

我正在编写一个Python脚本来解析数据文件。然后将解析后的数据发送到Excel文件中。这些数据可以非常庞大，通常是10至20列，但行数可能超过100,000。

使用win32com将这么多数据写入Excel需要相当长的时间。我的第一步是迭代Excel文件中的每个单元格，这非常耗时。经过一番探索，我发现了如何通过单个调用写入一行，从而大大减少所需时间。

然而，当我需要向Excel发送100,000行数据时，仍然需要很长时间。我相信我可以通过单个调用发送完整的数组来进一步加速此过程。但是，到目前为止，我无法做到这一点。

请参见下面的代码，其中演示了问题。该代码显示了时间上的差异。然而，第三步，使用单个调用将完整数组发送到范围内，未在Excel中产生正确的数据。我做错了什么？

import win32com.client
import time

#create data array
row = range(0,10)
i = 0
data_array = []
while i < 1000:
    data_array.append(row)
    i += 1

#write the array to an excel file
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = True
excel.ScreenUpdating = False
book = excel.Workbooks.Add()
sh1 = book.Worksheets(1)
sh2 = book.Worksheets(2)
sh3 = book.Worksheets(3)

#double loop, writing individual cells
print "Writing with double loop to inidividual cells."
start = time.time()
row = 0
for line in data_array:
    row += 1
    col = 0
    for field in line:
        col += 1
        sh1.Cells(row, col).Value = field
print "Processing time: " + str(time.time() - start) + " seconds."

#single loop, writing a row to a range
print "Writing with double loop to inidividual cells."
start = time.time()
row = 0
for line in data_array:
    row += 1
    sh2.Range(sh2.Cells(row,1), sh2.Cells(row, len(line))).Value = line
print "Processing time: " + str(time.time() - start) + " seconds."

#no loop, write array to range
print "Writing with double loop to inidividual cells."
start = time.time()
try:
    sh3.Range(sh3.Cells(row,1), sh3.Cells(len(data_array), len(data_array[0]))).Value = data_array
    print "Processing time: " + str(time.time() - start) + " seconds."
except:
    print "Failed to write array to excel file."

excel.ScreenUpdating = True
sheet = None
book = None
excel.Quit()
excel = None

- Eric

---

尝试一下这个：ws.Range（“A1：A4”）。Value = [[i] for i in range（1,5）] - lloydyu24

Answer 1

我一直在研究这个问题，并得出了一些有趣的结论。

确实有多种解决方案可以用python将数据写入excel，我最终专注于三个模块。

win32com.client

速度较慢，但文档可以在Excel中打开。因此，用户可以在Excel中使用最终结果开始工作。不适合大量数据。

在我的计算机（核心i5）上写入10,000行和10列需要约70秒。

import win32com.client
import time

#create data array
row = range(0,10)
i = 0
data_array = []
while i < 10000:
    data_array.append(row)
    i += 1

#write the array to an excel file
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = True
excel.ScreenUpdating = False
book = excel.Workbooks.Add()
sheet = book.Worksheets(1)

#single loop, writing a row to a range
print "Writing using win32com.client"
start = time.time()
row = 0
for line in data_array:
    row += 1
    sheet.Range(sheet.Cells(row,1), sheet.Cells(row, len(line))).Value = line
print "Processing time: " + str(time.time() - start) + " seconds."

print "Completed: " + str(time.time() - start) + " seconds."
excel.ScreenUpdating = True
sheet = None
book = None
excel.Quit()
excel = None

openpyxl

这是一个速度比较快的模块，但仍然不是很理想。该模块在将数据传输到Excel内存对象方面速度较慢，但保存速度很快。它可以在22.3秒内创建包含10列的10,000行数据，并在另外0.5秒内保存文件。当我使用100,000行和10列进行测试时，数据的创建需要228.3秒，并且在另外2.9秒内完成文件的保存。相当缓慢，但文件保存得很快。因此，openpyxl可能适用于对现有数据进行更改（格式化），尽管我还没有测试过。

另一个优点是使用openpyxl编码比使用win32com.client更容易。

import openpyxl
import sys
import time

#create data array
row = range(0,10)
i = 0
data_array = []
while i < 10000:
    data_array.append(row)
    i += 1

#create an excel workbook and sheet object
book = openpyxl.Workbook(optimized_write = True)

#single loop, writing rows
start = time.time()
print "Writing with single loop using openpyxl"
sheet = book.create_sheet()
for line in data_array:
    sheet.append(line)
print "Processing time: " + str(time.time() - start) + " seconds."

#close and save the file.
book.save('openpyxl.xlsx')
print "Total time: " + str(time.time() - start) + " seconds."

我在使用openpyxl时遇到了另一个问题。在我的实际工具中，openpyxl保存大量数据时出现问题（> 10,000行）。我仍然没有找出原因，也许我不会再去深究它。

PyExcelerate

首先这个模块是非常快的。它仅用0.17秒即可创建10,000行和10列。但是保存文件却需要2.2秒。这是三种选项中最快的。 当我尝试使用该模块保存100,000行和10列的数据时，Excel数据仅需1.8秒创建。但保存文件需要21.7秒。 因此，该模块的速度非常快，但写入文件存在一定的惩罚。总体来说，仍然是所有选项中最快的。 另一个PyExcelerate的优点是编程非常简单，比openpyxl更加容易。

import pyexcelerate
import sys
import time

#create data array
row = range(0,10)
i = 0
data_array = []
while i < 10000:
    data_array.append(row)
    i += 1

print sys.version

#create an excel workbook and sheet object
book = pyexcelerate.Workbook()

#single loop, writing rows
start = time.time()
print "Writing with single loop using PyExcelerate"
book.new_sheet("data", data = data_array)

print "Processing time: " + str(time.time() - start) + " seconds."

#close and save the file.
book.save('pyexcelerate.xlsx')
print "Total time: " + str(time.time() - start) + " seconds."

因此，我的结论是PyExcelerate是迄今为止最快的。win32com.client的优点是所创建的Excel文件可以在Excel中打开，以使用户开始使用已创建的数据。Openpyxl可能对创建后进行样式更改很有趣。但是，我尚未测试过这一点。因此，在一个应用程序中结合win32com.client、openpyxl和PyExcelerate可能是有益的。

- Eric

---

非常有趣，我不知道pyexcelerate。它似乎还支持单元格格式等功能。我想要补充的唯一一件事是，win32com打开文件为用户提供的优势是非常差的优势。从Python中打开文件只需要输入一行代码-并且比通过win32com更快。干杯。 - bgusach

你怎么在Excel中打开Excel文件呢？我非常想知道。 - Eric

就像 os.system('start ' + filepath) 一样简单。它会使用默认程序打开 Excel 文件，通常是 Excel :) - bgusach

有时候世界就是那么简单。 :-) 感谢您指出这一点。非常好用。根据Python文档，它是：os.startfile(path)，非常好用。 - Eric

Answer 2

我知道这篇文章有点旧。
然而，由于其他原因（宏执行）被困在win32com中，并需要类似于您第三个表格的解决方案（一次完整数组），我尝试了您最初发布的代码并发现了一个小错误，阻止了它的工作。
所以回答您最初的问题“我做错了什么？”：
在“#no loop，write array to range”部分使用row变量之前，您忘记了将其重新初始化为1。
以下是更新后的比较文章的win32com部分：

print "Writing using win32com.client"
start = time.time()
row = 1
sheet.Range(sheet.Cells(row,1), sheet.Cells(row+len(data_array)-1, len(data_array[0]))).Value = data_array
print "Processing time: " + str(time.time() - start) + " seconds."

book.SaveAs(Filename="C:\Temp\Temp.xls", FileFormat=56)
print "Completed: " + str(time.time() - start) + " seconds."

然后，时间不是太糟糕：
使用win32com.client编写
处理时间：0.322000026703秒。
完成时间：1.73699998856秒。
非常快。也许您的比较可以更新，因为PyExcelerate的数字现在已经不那么不同了（而且我的电脑速度更慢）。

- Thomas F

---

太好了！这完美地回答了问题！ - ZygD

@ThomasF 当我使用上述方法时，我收到了以下 ValueError：ndarray is not C-contiguous。任何想法为什么会这样？参考链接：https://stackoverflow.com/questions/61743292/can-we-copy-a-dataframe-with-rows-and-cols-1-into-an-excel-sheet - Aroosh Rana

Answer 3

使用COM读取Excel文件中的文件是一种极其浪费时间的做法。这就像用坦克杀苍蝇。请注意，win32com会对Windows API进行复杂的调用，与Excel通信，检索数据并将其发送回Python。既然信息已经存在于文件中，为什么要这样做呢？

有一些库可以直接解析Excel文件，而且它们可以比你想象的快100倍，因为没有过度复杂的调用Win API。

我已经成功地使用openpyxl等库工作了很多次，但是还有其他的库也可以做得很好甚至更好。

这里只是一个巨大数据的例子（使用生成器而不是将所有内容加载到内存中）：

from openpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', use_iterators=True)
ws = wb.get_sheet_by_name(name='big_data') # ws is now an IterableWorksheet

for row in ws.iter_rows(): # it brings a new method: iter_rows()
     for cell in row:
         print cell.internal_value

等效的方法可用于写入单元格。甚至可以对其进行格式化，虽然这并不完整（或曾经是这样）。

编辑

如何将大量信息写入 xlsx 文件的示例：

from openpyxl import Workbook
from openpyxl.cell import get_column_letter

wb = Workbook()
dest_filename = r'empty_book.xlsx'
ws = wb.active
ws.title = "range names"

for col_idx in xrange(1, 40):
    col = get_column_letter(col_idx)
    for row in xrange(1, 600):
        ws.cell('%s%s'%(col, row)).value = '%s%s' % (col, row)

ws = wb.create_sheet()
ws.title = 'Pi'
ws['F5'] = 3.14
wb.save(filename=dest_filename)

- bgusach

---

我不是在尝试从Excel中读取任何内容。我正在尝试将数据写入Excel文件。 win32com的好处在于，数据将被发送到Excel，并且用户已经打开该文件并可以开始使用它。 - Eric

使用 openpyxl，您可以做任何 Excel 相关的事情，速度比使用 Excel 和 win32com 快 100 倍。除了巨大的速度优势之外，直接使用 .xlsx 文件工作甚至无需安装 Excel。 - bgusach