pandas 我想将现有的Excel文件作为不同的工作表收集到一个Excel文件中

64jmpszr 于 2022-12-09 发布在其他

关注(0)|答案(1)|浏览(135)

我希望将现有的excel文件作为不同的工作表收集到一个excel文件中，我的代码如下所示

import pandas as pd
from openpyxl import load_workbook
import numpy

path = r'/home/shaumne/Desktop/ds/deneme.xlsx'

book = load_workbook(path)
writer = pd.ExcelWriter(path, engine="openpyxl")
writer.book = book

f1 = r'/home/shaumne/Desktop/ds/SP.xlsx'
df = pd.DataFrame(eval(f1))

f2 = r'/home/shaumne/Desktop/ds/SB.xlsx'
df2 = pd.DataFrame(eval(f2))

f3 = r'/home/shaumne/Desktop/ds/SD.xlsx'
df3 = pd.DataFrame(eval(f3))

df.to_excel(writer, sheet_name="SP")
df2.to_excel(writer, sheet_name="SB")
df3.to_excel(writer, sheet_name="SD")
writer.close()

但我得到“文件不是zip文件”错误

Traceback (most recent call last):
  File "/home/shaumne/Desktop/ds/ds.py", line 7, in <module>
    book = load_workbook(path)
  File "/home/shaumne/.local/lib/python3.10/site-packages/openpyxl/reader/excel.py", line 315, in load_workbook
    reader = ExcelReader(filename, read_only, keep_vba,
  File "/home/shaumne/.local/lib/python3.10/site-packages/openpyxl/reader/excel.py", line 124, in __init__
    self.archive = _validate_archive(fn)
  File "/home/shaumne/.local/lib/python3.10/site-packages/openpyxl/reader/excel.py", line 96, in _validate_archive
    archive = ZipFile(filename, 'r')
  File "/usr/lib/python3.10/zipfile.py", line 1267, in __init__
    self._RealGetContents()
  File "/usr/lib/python3.10/zipfile.py", line 1334, in _RealGetContents
    raise BadZipFile("File is not a zip file")
zipfile.BadZipFile: File is not a zip file

pandas

来源：https://stackoverflow.com/questions/74702189/i-want-to-collect-existing-excel-files-as-different-sheets-in-a-single-excel-fil

1条答案

按热度按时间

cczfrluj1#

没关系，我会回答我的问题，哈哈哈哈。
下面是我找到的解决方案：

import pandas as pd
import openpyxl
import os
print("İşlem Başlatılıyor")
cwd = os.path.abspath('') 
files = os.listdir(cwd)  

deneme = pd.read_csv(r'/home/shaumne/Desktop/ds/BusinessReport-12-06-22.csv')
deneme.to_excel('deneme.xlsx')
workbook=openpyxl.load_workbook('deneme.xlsx')
ss_sheet = workbook['Sheet1']
ss_sheet.title = 'TempExcelSheetForDeleting'
workbook.save('deneme.xlsx')

for file in files:                         
    if file.endswith('.xls') or file.endswith('.xlsx'):
        excel_file = pd.ExcelFile(file)
        sheets = excel_file.sheet_names
        for sheet in sheets:               
            print (file, sheet)
            df = excel_file.parse(sheet_name = sheet)
            with pd.ExcelWriter("deneme.xlsx",mode='a') as writer:  
                df.to_excel(writer, sheet_name=f"{sheet}", index=False)
            
            

workbook=openpyxl.load_workbook('deneme.xlsx')
#std=workbook["TempExcelSheetForDeleting"]
#workbook.remove(std)
workbook.save('deneme.xlsx')
print("Bitirmek için Enter'a bas")
dali=input()

print("Bitti")
dali=input()

赞(0）回复(0）举报 2022-12-09

我来回答

pandas 我想将现有的Excel文件作为不同的工作表收集到一个Excel文件中

1条答案

相关问题

热门标签

最新问答