python - win32com 错误 - 内部错误 - 缓冲区长度不是使用 dataframe.torecords() 遇到的序列长度

Question

我使用下面的代码通过 win32com.client 将现有数据框复制到 Excel 工作表。下面是代码

    import win32com.client as win32,sys
    import pandas as pd
    excel_application = win32.Dispatch("Excel.Application")
    excel_application.Visible = True
    lta_df = pd.read_excel("C:/Temp/temp_lta.xlsx",sheetname=0,
                                    header=0,na_filter=False)
    lta_df["Updated"] = pd.to_datetime(lta_df["Updated"])
    workbook = excel_application.Workbooks.Open("C:/Temp/temp_lta.xlsx")
    ws= workbook.Sheets.Add(After=workbook.Sheets(workbook.Sheets.count))
    start_row= 1
    start_col = 5
    lta_df= lta_df.reset_index()

    ws.Range(ws.Cells(start_row,start_col),
     ws.Cells(start_row+len(lta_df.index)-1,start_col+len(lta_df.columns))
     ).Value =  lta_df.to_records(index=False)

当我使用 to_records() 时出现以下错误

    Traceback (most recent call last):

      File "<ipython-input-779-91e88023cb75>", line 3, in <module>
        ).Value =  lta_df.to_records(index=False)

      File "C:\anaconda3\lib\site-packages\win32com\client\dynamic.py", line 565, in __setattr__
          self._oleobj_.Invoke(entry.dispid, 0, invoke_type, 0, value)

      TypeError: Internal error - the buffer length is not the sequence length!

有什么办法可以解决这个问题吗？所有值都在 str 中我使用的时候也遇到同样的错误

      start_row = 1
      start_col = 1
      arr_temp = lta_df.values.copy(order="C")
      ws.Range(ws.Cells(start_row,start_col),
     ws.Cells(start_row+len(lta_df.index)-1,start_col+len(lta_df.columns))
     ).Value = arr_temp

Answer 1

出现错误是因为 win32 不理解您的数据类型(pd.Dataframe 或 np.ndarray)。我的做法是

# first by converting dataframe to contiguous array 
# this is needed because array has to be C_CONTIGUOUS in order to
# write it using win32com
# you can check whether your array is contiguous by using .flags method
lta_df2 = np.ascontiguousarray(lta_df) 
# second step is to convert the array to list
lta_df3 = lta_df2.tolist()

# now you can write lta_df3 to excel using win32com
start_row = 1
start_col = 1
ws.Range(ws.Cells(start_row,start_col),
 ws.Cells(start_row+len(lta_df.index)-1,start_col+len(lta_df.columns))
 ).Value = lta_df3 

另外，我建议您添加 python 和 pandas 标签

还有#2，您可能想从 len(lta_df.columns) 中减去 1就像您对 start_row+len(lta_df.index)-1 所做的那样