本文共 2316 字，大约阅读时间需要 7 分钟。

openpyxl ? PyCharm ?????

? PyCharm ??? openpyxl ???? Excel ???????????????????????????????

1. ?? openpyxl

????? openpyxl ????????????????????????

pip install openpyxl

????????????????? pywin32 ???? openpyxl ? Windows ??????????

pip install pywin32

2. ?????

?? openpyxl ?? Excel ????????

from openpyxl import load_workbook# ?? Excel ??fp = load_workbook("test.xlsx")

3. ?????

???????????

# ??????????????sheetnames = fp.sheetnames# ????????sheetname = sheetnames[0]

4. ?????

?????????????

# ?? A1 ???print("A1", sheet["A1"].value)# ?? 1 ???????print("1?", sheet["1"])# ?? C4 ?????print("C4", sheet["C4"].value)

5. ???????

????????????

print("???", sheet.max_row)print("???", sheet.max_column)

6. ???????

???????????????

# ?? A1 ?????print("A1", sheet.cell(row=1, column=1).value)

7. ????

????????????????????????? save()???????

fp.save("test.xlsx")

8. ??????

PermissionError: Permission denied

???????????????????????????????

# ??????print(os.path.exists("test.xlsx"))

KeyError: sheetname not found

????????????? get_sheet_names() ??????????

sheetnames = fp.sheetnamessheetname = sheetnames[0]

9. ??????

?? create_sheet() ?????????

from openpyxl import workbookwb = workbook.Workbook()sheet = wb.activesheet.title = "new sheet"wb.save("test.xlsx")

10. ?????

?? delete_sheet() ????????

sheet = wb["Sheet1"]wb.delete_sheet(sheet)

11. ?????

?????????

sheet["C3"].value = "Hello world!"

12. ????

???????

sheet["E1"].value = "=SUM(A:A)"

13. ????

??????????

wb.save("test.xlsx")

14. ????

???????????????????????????

os.chmod("test.xlsx", 0o644)

15. ????

?? max_row() ? min_row() ?????

print("???", sheet.max_row)print("???", sheet.min_row)

16. ?????

??????????

for i in sheet["C"]:    print(i.value, end=" ")

17. ????

?????????

print(sheet["B2"].value)

18. ????

????????

from openpyxl import load_workbook# ?? Excel ??fp = load_workbook("test.xlsx")# ????????sheet = fp["Sheet1"]# ?? A ???????for cell in sheet["A"]:    print(cell.value)# ?? C ???????for cell in sheet["C"]:    print(cell.value)# ?? 2 ???????for cell in sheet["2"]:    print(cell.value)# ????fp.save("test.xlsx")

19. ??

• ????? PyCharm??????????????????
• ????? zipfile.BadZipFile ??????????????
• ????? DeprecationWarning: Call to deprecated function get_sheet_by_name???? wb[sheetname] ?? get_sheet_by_name()?

??????????? PyCharm ?????? openpyxl ???? Excel ???