从python 3.x写入Excel 2003文件 [英] Write excel 2003 files from python 3.x

问题描述

我有一个非常大的CSV数据集(几百万条记录).我已经过滤并处理了此列表，并将其拆分为客户规范.这些都是在Python3.3中完成的

I have a very large CSV data set (several million records). I have already filtered and massaged and split this list to a clients specification. This was all done in Python3.3

最后一个要求是将这些拆分列表以Excel格式保存.他们有一个实用程序，可以在进行一些计算并检查数据库中现有的重复项之后，将Excel电子表格(以特定格式)导入其数据库.我的问题是，它们的实用程序仅适用于Excel 2003 .xls文件……我不知道这一点.

The last requirement is that these split lists be saved in Excel format. They have a utility that imports an Excel spreadsheet (in a specific format) into their database after doing some calculations and checking for existing duplicates in the DB. My problem is that their utility only works on Excel 2003 .xls files... I didn't know this ahead of time.

因此，我已经可以使用OpenPyXl以正确的格式为Excel 2007写入数据，但是这些文件将不起作用.我可以编写CSV文件，但这些文件也不起作用，其导入程序需要xls文件.也许有一种方法可以将所有文件从Excel 2007 xlsx格式批量转换为xls格式，或从csv格式转换为xls格式?文件数以千计，因此无法手动完成.

So I can already write the data in the correct format for Excel 2007 using OpenPyXl, but these files won't work. I can write CSV files but those don't work either, their importer needs xls files. Maybe there is a way to batch convert all the files from Excel 2007 xlsx format to xls format, or from csv format to xls format? There are thousands of files so it can't be done by hand.

最好的办法是以正确的格式输出它们，但是我似乎找不到能够与Excel 2003格式兼容的python 3兼容方式. xlwt仅适用于python2.x.

The best thing to do would be output them in the correct format, but I can't seem to find a python 3 compatible way that will work with Excel 2003 format. xlwt is python 2.x only.

有人建议我如何做到这一点吗?

Does anyone have suggestions how I can finish this?

这就是解决方案的样子.

This is what the solution looked like.

按照stenci的建议关闭工作簿.

Added the workbook close as suggested by stenci.

import os
import errno
import glob 
import time 
import win32com.client    

def xlsx_to_xls(path):
    xlsx_files = glob.glob(path+'\\*.xlsx') 

    if len(xlsx_files) == 0: 
        raise RuntimeError('No XLSX files to convert.') 

    xlApp = win32com.client.Dispatch('Excel.Application') 

    for file in xlsx_files: 
        xlWb = xlApp.Workbooks.Open(os.path.join(os.getcwd(), file)) 
        xlWb.SaveAs(os.path.join(os.getcwd(), file.split('.xlsx')[0] + '.xls'), FileFormat=1) 
        xlWb.Close()

    xlApp.Quit() 

    time.sleep(2) # give Excel time to quit, otherwise files may be locked 
    for file in xlsx_files: 
        os.unlink(file) 

推荐答案

使用Excel 2007打开它们并将其另存为Excel2003.您可以使用简单的VBA宏或通过Python进行操作，甚至无需显示Excel应用程序即可.用户.唯一的问题是您的计算机中需要Excel.

Open them with Excel 2007 and save them as Excel 2003. You can do it with a simple VBA macro, or from Python, without even showing the Excel application to the user. The only problem is that you need Excel in your computer.

这是VBA代码:

Sub ConvertTo2003(FileName As String)
  Dim WB As Workbook
  Set WB = Workbooks.Open(FileName, ReadOnly:=True)
  WB.SaveAs Replace(FileName, ".xlsx", ".xls"), FileFormat:=xlExcel8
  WB.Close
End Sub

这是Python代码:

Here is the Python code:

xlApp = Excel.ExcelApp(False)
xlApp.convertTo2003('FileName.xlsx')

class ExcelApp(object):
    def __init__(self, visible):
        self.app = win32com.client.Dispatch('Excel.Application')
        if visible:
            self.app.Visible = True

    def __exit__(self):
        self.app.Quit()

    def __del__(self):
        self.app.Quit()

    def convertTo2003(self, fileName):
        if self.app:
            wb = self.app.WorkBooks.Open(fileName, ReadOnly = True)
            wb.SaveAs(fileName[:-1], FileFormat = 56)
            wb.Close()

    def quit(self):
        if self.app:
            self.app.Quit()

这篇关于从python 3.x写入Excel 2003文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！