合并两个具有多个工作表的excel文件而不丢失格式

Question

我想分别使用python将多个excel文件与多个工作表合并。我不想丢失工作表中的任何格式。它应该复制所有工作表，只创建一个excel文件。

我只能合并第一张，而且所有的格式都丢失了。

这是我的代码：

import os
import os.path
import xlrd
import xlsxwriter

file_name = input("merge")
merged_file_name = file_name + ".xls"
dest_book = xlsxwriter.Workbook('m.xls')
dest_sheet_1 = dest_book.add_worksheet()
dest_row = 1
temp = 0
path = input("C:\\test")
out = os.path.isdir("")
print(out)

print("File path: " + path)
for root,dirs, files in os.walk("C:\\test"):
    for xlsfile in files:
        print ("File in mentioned folder is: " + xlsfile)
        temp_book = xlrd.open_workbook(os.path.join(root,xlsfile))
        temp_sheet = temp_book.sheet_by_index(0)
        if temp == 0:
            for col_index in range(temp_sheet.ncols):
                str = temp_sheet.cell_value(0, col_index)
                dest_sheet_1.write(0, col_index, str)
            temp = temp + 1
        for row_index in range(1, temp_sheet.nrows):
            for col_index in range(temp_sheet.ncols):
                str = temp_sheet.cell_value(row_index, col_index)
                dest_sheet_1.write(dest_row, col_index, str)
            dest_row = dest_row + 1
dest_book.close()
book = xlrd.open_workbook("m.xls")
sheet = book.sheet_by_index(0)
print ("number of rows in destination file are: "), sheet.nrows
print ("number of columns in destination file are: "), sheet.ncols

Answer 1

关于萨钦·英格尔面临的错误，

（-2147352567，异常发生。，（0，'Microsoft Excel'，'该名称已被使用。'，'xlmain11.chm'，0, -2146827284)，无）面对此错误

这可能是因为您以前创建过一个同名文件。尝试用不同的名称创建它。

雪糕上的答案

new_wb.SaveAs(Filename='MasterMerge.xlsx', FileFormat=xlWorkbookDefault)

将生成名为“MasterMerge.xlsx”的文件，可能您已经创建了该文件。

顺便说一句，您可以在finally:block中添加xlapp.Quit（）来解决正在使用的问题

我对冻糕的答案做了一些修改（谢谢伙计）

def merge_excel_files(filepath_list,filename,delete_original_files=False):
    import os, errno
    import win32com.client as win32
    try:
        # INITIALIZE EXCEL COM APP
        xlapp = win32.gencache.EnsureDispatch('Excel.Application')

        # ASSIGN CONSTANTS
        xlPasteValues = -4163; lPasteFormats = -4122; xlWorkbookDefault = 51

        # CREATE NEW WOKRBOOK (PROMPTS IF EXISTS)
        new_wb = xlapp.Workbooks.Add()
        new_wb.SaveAs(Filename=filename, FileFormat=xlWorkbookDefault)

        # Gain filename in a directory
        # xl_files = [f for f in os.listdir(path) if f.endswith('.xls') or f.endswith('.xlsx')]

        for wb in filepath_list:
            xlwb = xlapp.Workbooks.Open(wb)

            # LOOP THROUGH EVERY WORKSHEET, COPYING TO NEW WORKSHEET
            for xlsh in xlwb.Worksheets:
                new_sh = new_wb.Worksheets.Add()
                new_sh.Name = xlsh.Name
                new_wb.Save()
                new_sh.Move(After=new_wb.Worksheets(new_wb.Worksheets.Count))

                xlsh.Cells.Copy(new_sh.Cells)
                new_sh = None

            xlwb.Close(False)
            xlwb = None

        # REMOVNIG DEFAULT SHEET AND LAUNCHING TO SCREEN
        new_wb.Worksheets('Sheet1').Delete()
        new_wb.Save()
        # xlapp.Visible = True
    except Exception as e:
        print(e)
    finally:
        # Close the Excel file since done writing
        xlapp.Quit()
        # RELEASE RESOURCES
        xlsh = None; new_sh = None;
        xlwb = None; new_wb = None; xlapp = None

        # Delete the initial file
        if delete_original_files:
            for count,x in enumerate(filepath_list):
                print(f"Deleting the {count+1}/{len(filepath_list)} original file(s)...")
                try:
                    os.remove(x)
                except OSError as e:
                    # No such file or directory
                    if e.errno != errno.ENOENT:
                        raise
                else:
                    # If there's no exception
                    print(f"Deleted {x}")

    ## Merge Excel files into one workbook with keeping the sheets and styling/formatting
    # => https://stackoverflow.com/questions/51986517/merge-two-excel-files-with-multiple-sheet-without-losing-formatting
    # => https://stackoverflow.com/questions/44593705/how-to-copy-over-an-excel-sheet-to-another-workbook-in-python/44596301#44596301 [openpyxl (Can't keep formatting), pywin32, xlwings]
    # => https://stackoverflow.com/questions/56687602/copy-excel-sheet-from-one-worksheet-to-another-in-python/56688138#56688138 [xlwings]

    ## Solve file in use problem with pywin32 solution from questions/51986517
    # => https://stackoverflow.com/questions/6337595/python-win32-com-closing-excel-workbook/6338030

    ## Basic Python: Pythonic way to delete a files, running code if try statements were successful
    # => https://stackoverflow.com/questions/10840533/most-pythonic-way-to-delete-a-file-which-may-not-exist
    # => https://stackoverflow.com/questions/2792568/running-code-if-try-statements-were-successful-in-python

    ## Research on openpyxl copy_worksheet(); Conclusion: it can only copy and paste sheet within same workbook. =(
    # => https://stackoverflow.com/questions/44593705/how-to-copy-over-an-excel-sheet-to-another-workbook-in-python/44596301
    # => https://openpyxl.readthedocs.io/en/latest/tutorial.html?highlight=copy_worksheet#manipulating-a-workbook-in-memory

Answer 2

由于您需要像格式化之类的Excel特定需求，请考虑用COM接口直接连接到Excel对象库。当然，这假设您的计算机上安装了Excel。对于Windows，Python可以使用win32com库运行COM，这不仅可以连接Excel，还可以连接大多数Windows应用程序和对象，包括记事本、画图，甚至ADODB。

本质上，这是使用Workbooks.Add、Sheets.Add、Range.Copy和其他方法镜像VBA（它具有与Excel对象库类似的接口）。所有其他API（如xlrd和xlwriter等）都不直接使用Excel方法，因此即使是图形也会丢失格式，而不会丢失数据。

import os
import win32com.client as win32

path = input("C:\\test")
file_name = input("merge")
merged_file_name = file_name + ".xlsx"

try:
    # INITIALIZE EXCEL COM APP
    xlapp = win32.gencache.EnsureDispatch('Excel.Application')

    # ASSIGN CONSTANTS   
    xlPasteValues = -4163; lPasteFormats = -4122; xlWorkbookDefault = 51

    # CREATE NEW WOKRBOOK (PROMPTS IF EXISTS)
    new_wb = xlapp.Workbooks.Add()
    new_wb.SaveAs(Filename='MasterMerge.xlsx', FileFormat=xlWorkbookDefault)

    # LOOP THROUGH WORKBOOKS
    xl_files = [f for f in os.listdir(path) if f.endswith('.xls') or f.endswith('.xlsx')]

    for wb in xl_files:
        xlwb = xlapp.Workbooks.Open(os.path.join(path, wb))

        # LOOP THROUGH EVERY WORKSHEET, COPYING TO NEW WORKSHEET
        for xlsh in xlwb.Worksheets:
            new_sh = new_wb.Worksheets.Add()
            new_sh.Name = xlsh.Name
            new_wb.Save()            
            new_sh.Move(After=new_wb.Worksheets(new_wb.Worksheets.Count))

            xlsh.Cells.Copy(new_sh.Cells)
            new_sh = None

        xlwb.Close(False)
        xlwb = None

    # REMOVNIG DEFAULT SHEET AND LAUNCHING TO SCREEN
    new_wb.Worksheets('Sheet1').Delete()
    new_wb.Save()
    xlapp.Visible = True

except Exception as e:
    print(e)

finally:
    # RELEASE RESOURCES
    xlsh = None; new_sh = None; 
    xlwb = None; new_wb = None; xlapp = None