数据丢失，使用 pandas 在具有多个工作表的.xlsx文件中更改格式，同时在现有的.xlsx文件中添加新工作表时使用openpyxl

问题描述

我有一个Final.xlsx，其中包含多个工作表-shee1, sheet2 ,sheet3，每个工作表都有一些图形和数据.我还有另一个文件file5.xlsx，我想在选项卡的Final.xlsx中添加.下面的代码可以正常工作，但是Final.xlsx现有工作表数据丢失了(内容，格式，grpahs等).需要帮助解决此问题.

I have a Final.xlsx that contains multiple sheet - shee1, sheet2 ,sheet3 , each having some graphs and data. I have another file file5.xlsx that i want to add in Final.xlsx in tab . The below code is working but the Final.xlsx existing sheets data is getting missed(contents,formats, grpahs, and others) . need help to fix this.

    import pandas
    from openpyxl import load_workbook

    book = load_workbook('foo.xlsx')
    writer = pandas.ExcelWriter('foo.xlsx', engine='openpyxl')
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
    df1=pd.read_excel('file5.xlsx')
    df1.to_excel(writer, "new",index=False)

    writer.save()

推荐答案

在内部，Pandas使用xlrd库读取xlsx文件.这个库很快，但是，因为它本质上是固定在对BIFF格式的支持上，所以它对OOXML的支持是有限的.看到Pandas对图表一无所知，反正也无法保留它们.

Internally Pandas uses the xlrd library to read xlsx files. This library is fast but, because it is essentially bolted onto support for the BIFF format, it's support for OOXML is limited. Seeing as Pandas doesn't know anything about charts, it couldn't keep them anyway.

openpyxl在openpyxl.utils.dataframe中提供了实用程序，用于在XLSX的行和Pandas Dataframe之间进行切换，从而使您在工作时拥有完全的控制权，同时几乎将所有其他内容保留在文件中.但是，就您而言，您甚至不需要熊猫，因为您只需在"file5.xlsx"中的单元格上循环，然后将其复制到其他文件中即可.

openpyxl provides utilities in openpyxl.utils.dataframe for going between XLSX's rows and Pandas Dataframes giving you full control when working, while keeping nearly everything else in your file. In your case, however, you don't even need Pandas as you can simply loop over the cells from "file5.xlsx" and copy them to your other file.

这篇关于数据丢失，使用 pandas 在具有多个工作表的.xlsx文件中更改格式，同时在现有的.xlsx文件中添加新工作表时使用openpyxl的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！