task02

最新推荐文章于 2024-01-03 11:48:43 发布

Rachelwen

最新推荐文章于 2024-01-03 11:48:43 发布

阅读量177

点赞数

分类专栏：笔记

本文链接：https://blog.csdn.net/qq_39303320/article/details/118076605

版权

笔记专栏收录该内容

22 篇文章 0 订阅

订阅专栏

本文档展示了使用Python的openpyxl库读取和写入Excel文件时遇到的`BadZipFile`异常，以及如何处理这种错误。同时，提供了从工作簿中获取工作表名称、读取单元格内容、修改单元格值等基本操作的代码示例。通过这些示例，读者可以了解如何进行有效的Excel文件操作。

摘要由CSDN通过智能技术生成

from openpyxl import load_workbook
exl_1=load_workbook(filename='test.xlsx')
print (exl_1.sheetnames)

---------------------------------------------------------------------------

BadZipFile                                Traceback (most recent call last)

<ipython-input-8-b3bf9383ac19> in <module>
      1 from openpyxl import load_workbook
----> 2 exl_1=load_workbook(filename='test.xlsx')
      3 print (exl_1.sheetnames)


~/opt/anaconda3/lib/python3.8/site-packages/openpyxl/reader/excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
    313 
    314     """
--> 315     reader = ExcelReader(filename, read_only, keep_vba,
    316                         data_only, keep_links)
    317     reader.read()


~/opt/anaconda3/lib/python3.8/site-packages/openpyxl/reader/excel.py in __init__(self, fn, read_only, keep_vba, data_only, keep_links)
    122     def __init__(self,  fn, read_only=False, keep_vba=KEEP_VBA,
    123                   data_only=False, keep_links=True):
--> 124         self.archive = _validate_archive(fn)
    125         self.valid_files = self.archive.namelist()
    126         self.read_only = read_only


~/opt/anaconda3/lib/python3.8/site-packages/openpyxl/reader/excel.py in _validate_archive(filename)
     94             raise InvalidFileException(msg)
     95 
---> 96     archive = ZipFile(filename, 'r')
     97     return archive
     98 


~/opt/anaconda3/lib/python3.8/zipfile.py in __init__(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps)
   1267         try:
   1268             if mode == 'r':
-> 1269                 self._RealGetContents()
   1270             elif mode in ('w', 'x'):
   1271                 # set the modified flag so central directory gets written


~/opt/anaconda3/lib/python3.8/zipfile.py in _RealGetContents(self)
   1334             raise BadZipFile("File is not a zip file")
   1335         if not endrec:
-> 1336             raise BadZipFile("File is not a zip file")
   1337         if self.debug > 1:
   1338             print(endrec)


BadZipFile: File is not a zip file

sheet=exl_1['work']

---------------------------------------------------------------------------

NameError                                 Traceback (most recent call last)

<ipython-input-7-8fe1271ea388> in <module>
----> 1 sheet=exl_1['work']


NameError: name 'exl_1' is not defined

sheet=exl_1.active
print (sheet.dimensions)#获取内容大小

---------------------------------------------------------------------------

NameError                                 Traceback (most recent call last)

<ipython-input-3-33e04b2089ff> in <module>
----> 1 sheet=exl_1.active
      2 print (sheet.dimensions)#获取内容大小


NameError: name 'exl_1' is not defined

#获取某个单元格对应的行列坐标
cell=sheet.cell(row=1,column=2)
print (cell.value)

CONTENT

cell_1=sheet['A1']
print (cell_1.value)

hello world

print (cell_1.row,cell_1.column,cell.coordinate)

1 1 B1

cells=sheet['A1:C8']

row=sheet[1]
rows=sheet[1:2]
column=sheet['A']
columns=sheet['A:C']

#行获取
for row in sheet.iter_rows(min_row=1,max_row=5,
                           min_col=2,max_col=6):
    print(row)
    for cell in row:
        print (cell.value)

(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>, <Cell 'Sheet1'.D1>, <Cell 'Sheet1'.E1>, <Cell 'Sheet1'.F1>)
CONTENT
AUTHOR
SRC_URL
SOURCE_TYPE
PUBLISH_SITE
(<Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>, <Cell 'Sheet1'.E2>, <Cell 'Sheet1'.F2>)
正文
作者
下载源地址
文章类型
来源
(<Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>, <Cell 'Sheet1'.E3>, <Cell 'Sheet1'.F3>)
联化科技（德州）有限公司发生爆炸 两人死亡
/
/
/
/
(<Cell 'Sheet1'.B4>, <Cell 'Sheet1'.C4>, <Cell 'Sheet1'.D4>, <Cell 'Sheet1'.E4>, <Cell 'Sheet1'.F4>)
2005.02.06青岛啤酒股份有限公司火灾
/
/
/
/
(<Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>, <Cell 'Sheet1'.D5>, <Cell 'Sheet1'.E5>, <Cell 'Sheet1'.F5>)
2004.02.14河南链鑫科技有限公司火灾
/
/
/
/

from openpyxl import load_workbook
exl=load_workbook('test.xlsx')
sheet=exl.active
for row in sheet.iter_rows(min_row=1,max_row=100,
                          min_col=1,max_col=20):
    for cell in row:
        if not cell.value:
            print (cell.coordinate)

---------------------------------------------------------------------------

BadZipFile                                Traceback (most recent call last)

<ipython-input-63-bb2be71e8dff> in <module>
      1 from openpyxl import load_workbook
----> 2 exl=load_workbook('test.xlsx')
      3 sheet=exl.active
      4 for row in sheet.iter_rows(min_row=1,max_row=100,
      5                           min_col=1,max_col=20):


~/opt/anaconda3/lib/python3.8/site-packages/openpyxl/reader/excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
    313 
    314     """
--> 315     reader = ExcelReader(filename, read_only, keep_vba,
    316                         data_only, keep_links)
    317     reader.read()


~/opt/anaconda3/lib/python3.8/site-packages/openpyxl/reader/excel.py in __init__(self, fn, read_only, keep_vba, data_only, keep_links)
    122     def __init__(self,  fn, read_only=False, keep_vba=KEEP_VBA,
    123                   data_only=False, keep_links=True):
--> 124         self.archive = _validate_archive(fn)
    125         self.valid_files = self.archive.namelist()
    126         self.read_only = read_only


~/opt/anaconda3/lib/python3.8/site-packages/openpyxl/reader/excel.py in _validate_archive(filename)
     94             raise InvalidFileException(msg)
     95 
---> 96     archive = ZipFile(filename, 'r')
     97     return archive
     98 


~/opt/anaconda3/lib/python3.8/zipfile.py in __init__(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps)
   1267         try:
   1268             if mode == 'r':
-> 1269                 self._RealGetContents()
   1270             elif mode in ('w', 'x'):
   1271                 # set the modified flag so central directory gets written


~/opt/anaconda3/lib/python3.8/zipfile.py in _RealGetContents(self)
   1334             raise BadZipFile("File is not a zip file")
   1335         if not endrec:
-> 1336             raise BadZipFile("File is not a zip file")
   1337         if self.debug > 1:
   1338             print(endrec)


BadZipFile: File is not a zip file

from openpyxl import load_workbook
exl=load_workbook(filename='test.xlsx')#载入工作簿
sheet=exl.active#激活工作表
sheet['A1']='hello world'
exl.save(filename='test.xlsx')

---------------------------------------------------------------------------

BadZipFile                                Traceback (most recent call last)

<ipython-input-64-b521c601b73b> in <module>
      1 from openpyxl import load_workbook
----> 2 exl=load_workbook(filename='test.xlsx')#载入工作簿
      3 sheet=exl.active#激活工作表
      4 sheet['A1']='hello world'
      5 exl.save(filename='test.xlsx')


~/opt/anaconda3/lib/python3.8/site-packages/openpyxl/reader/excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
    313 
    314     """
--> 315     reader = ExcelReader(filename, read_only, keep_vba,
    316                         data_only, keep_links)
    317     reader.read()


~/opt/anaconda3/lib/python3.8/site-packages/openpyxl/reader/excel.py in __init__(self, fn, read_only, keep_vba, data_only, keep_links)
    122     def __init__(self,  fn, read_only=False, keep_vba=KEEP_VBA,
    123                   data_only=False, keep_links=True):
--> 124         self.archive = _validate_archive(fn)
    125         self.valid_files = self.archive.namelist()
    126         self.read_only = read_only


~/opt/anaconda3/lib/python3.8/site-packages/openpyxl/reader/excel.py in _validate_archive(filename)
     94             raise InvalidFileException(msg)
     95 
---> 96     archive = ZipFile(filename, 'r')
     97     return archive
     98 


~/opt/anaconda3/lib/python3.8/zipfile.py in __init__(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps)
   1267         try:
   1268             if mode == 'r':
-> 1269                 self._RealGetContents()
   1270             elif mode in ('w', 'x'):
   1271                 # set the modified flag so central directory gets written


~/opt/anaconda3/lib/python3.8/zipfile.py in _RealGetContents(self)
   1334             raise BadZipFile("File is not a zip file")
   1335         if not endrec:
-> 1336             raise BadZipFile("File is not a zip file")
   1337         if self.debug > 1:
   1338             print(endrec)


BadZipFile: File is not a zip file

import xlwt
workbook=xlwt.Workbook(encoding='utf-8')
sheet=workbook.add_sheet('my worksheet')
sheet.write(1,0,label='this is test')
workbook.save('new_test.xls')

import xlwt
exl=xlwt.Workbook(encoding='utf-8')
worksheet=exl.add_sheet('my worksheet')
data=[['hello',22,'hi'],
      ['hell',23,'h'],
      ['he',25,'him']]
for i in range(len(data)):
    for j in range(len(data[i])):
        worksheet.write(i,j,data[i][j])
exl.save('test.xlsx')

import openpyxl
print (openpyxl.__version__)

3.0.7

sheet['A2']='=SUM(A1:D1)'
exl.save(filename='test.xlsx')

---------------------------------------------------------------------------

TypeError                                 Traceback (most recent call last)

<ipython-input-68-d9fff6c759ce> in <module>
----> 1 sheet['A2']='=SUM(A1:D1)'
      2 exl.save(filename='test.xlsx')


TypeError: 'Worksheet' object does not support item assignment