devbox
酷酷是懒虫
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Python 读写 Excel 常用的几种方法_python excel数据 读写 兼容性好一点的

作者：酷酷是懒虫 | 2024-07-13 01:16:22

赞

踩

python excel数据 读写 兼容性好一点的

xlwt、xlrd 

  1. import xlwt
  2. import xlrd
  3.  
  4.  
  5. class ExcelHandler:
  6.     def __init__(self, excel_name):
  7.         self.excel_name = excel_name
  8.  
  9.     def write_title(self, worksheet, title):
  10.         for i in range(len(title)):
  11.             worksheet.write(0, i, title[i])
  12.  
  13.     def write_excel(self, title, rows):
  14.         workbook = xlwt.Workbook(encoding='utf_8_sig')
  15.         worksheet = workbook.add_sheet("Sheet1", cell_overwrite_ok=True)
  16.         self.write_title(worksheet, title)
  17.  
  18.         red_style = xlwt.easyxf("font:colour_index red;")
  19.         for i, row in enumerate(rows):
  20.             for j, col in enumerate(row):
  21.                 worksheet.write(i + 1, j, col, red_style)  # 内容从第二行开始写，避免覆盖表头
  22.         # for row in range(len(rows)):
  23.         #     for column in range(len(rows[row])):
  24.         #         worksheet.write(row, column, rows[row][column])
  25.         workbook.save(self.excel_name)
  26.  
  27.     def read_excel(self):
  28.         workbook = xlrd.open_workbook(self.excel_name)
  29.         worksheet = workbook.sheet_by_index(0)  # 按索引获取 Sheet
  30.         nrows = worksheet.nrows
  31.         for nr in range(nrows):
  32.             print(worksheet.row_values(nr))
  33.  
  34. if __name__ == '__main__':
  35.     title = ["column1", "column2", "column3"]
  36.     # 存入的数据为二维列表形式
  37.     rows = [
  38.         [1, 2, 3],
  39.         [2, 3, 4],
  40.         [4, 5, 6]
  41.     ]
  42.     eh = ExcelHandler("test.xls")
  43.  
  44.     eh.write_excel(title, rows)
  45.     eh.read_excel()

openpyxl

openpyxl 对扩展的 xlsx 支持比较好，对旧版的 xls 用  xlrd 和 xlwt 读写就好了，不然会报错。

openpyxl.utils.exceptions.InvalidFileException: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format.

相对而言，对表单和单元格的操作支持提升了不少。

  1. import openpyxl
  2. from openpyxl.worksheet.worksheet import Worksheet
  3.  
  4.  
  5. class ExcelHandler:
  6.     def __init__(self, excel_name):
  7.         self.excel_name = excel_name
  8.         self.workbook = openpyxl.load_workbook(excel_name)
  9.         self.worksheet: Worksheet = self.workbook['Sheet1']
  10.         # sheet = self.workbook['Sheet1']
  11.         # 上面两种写法等价，只不过添加变量类型声明后， 变量操作时"代码提示"更方便一些
  12.  
  13.     def get_sheet(self):
  14.         # 1.获取 sheet 列表
  15.         print('=====获取 sheet 列表=====')
  16.         print(self.workbook.sheetnames)  # 如：['Sheet1', 'Sheet2']
  17.         print(self.workbook.worksheets)  # 如：[<Worksheet "Sheet1">, <Worksheet "Sheet2">]
  18.         # print(self.workbook.get_sheet_names())  # deprecated function 已弃用
  19.  
  20.         # 2.获取单个 sheet
  21.         print('=====获取单个 sheet =====')
  22.         print(self.workbook['Sheet1'])  # 根据名称
  23.         print(self.workbook.sheetnames[0])  # 根据下标，如：Sheet1
  24.         print(self.workbook.worksheets[0])  # 根据下标，如：<Worksheet "Sheet">
  25.  
  26.     def create_sheet(self):
  27.         # 1.末尾追加
  28.         self.workbook.create_sheet('Sheet2')
  29.  
  30.         # 2.指定位置插入（index 从 0 开始，默认末尾）
  31.         self.workbook.create_sheet('Sheet3', 0)
  32.  
  33.         # 3.注意：若 Sheet 已存在，会默认在名称后面追加数字，如：Sheet31、Sheet32
  34.         self.workbook.create_sheet('Sheet3')
  35.  
  36.         # 4.保存（只有保存后，才会生效哦）
  37.         self.workbook.save("test_sheet.xlsx")
  38.  
  39.     def copy_sheet(self):
  40.         # 1.获取 sheet 对象
  41.         sheet = self.workbook['Sheet1']
  42.  
  43.         # 2.复制 sheet（默认名称 + copy，如：Sheet Copy）
  44.         self.workbook.copy_worksheet(sheet)
  45.  
  46.         # 3.保存（只有保存后，才会生效哦）
  47.         self.workbook.save("test_sheet.xlsx")
  48.  
  49.     def delete_sheet(self):
  50.         # self.copy_sheet()
  51.         # 1.删除 sheet
  52.         del self.workbook['Sheet1 Copy']
  53.  
  54.         # 2.保存后生效
  55.         self.workbook.save("test_sheet.xlsx")
  56.  
  57.     def move_sheet(self):
  58.         # 1.移动 sheet（负数=向左移动、正数=向右移动、数值=移动位置的个数）
  59.         self.workbook.move_sheet('Sheet1', -1)
  60.  
  61.         # 2.保存后生效
  62.         self.workbook.save(self.excel_name)
  63.  
  64.     def rename_sheet(self):
  65.         # 1.重命名 sheet
  66.         self.workbook['Sheet1'].title = 'Sheet111'
  67.  
  68.         # 2.保存后生效
  69.         self.workbook.save(self.excel_name)
  70.  
  71.     def get_title(self, worksheet: Worksheet = None):
  72.         if not worksheet:
  73.             worksheet = self.worksheet
  74.         title = next(worksheet.iter_rows(min_col=1, min_row=1, values_only=True))
  75.         # print(title)
  76.         # ('column1', 'column2', 'column3')
  77.         return title
  78.  
  79.     # 查询表格基本属性（元数据）
  80.     def read_meta_data(self, worksheet: Worksheet = None):
  81.         if not worksheet:
  82.             worksheet = self.worksheet
  83.         # 与下列写法等价，只不过添加类型判断后， "代码提示"更方便一些了
  84.         # sheet = self.workbook['Sheet1']
  85.         print(f'title: {worksheet.title}')  # 表单标题，如：Sheet1
  86.         print(f'dimensions: {worksheet.dimensions}')  # 表格大小，如：A1:C4 表示4行3列，与Excel默认的行列表示一致
  87.         print(f'min_row: {worksheet.min_row}')  # 最小行 1
  88.         print(f'max_row: {worksheet.max_row}')  # 最大行 4
  89.         print(f'rows: {worksheet.rows}')  # 行对象
  90.  
  91.         print(f'min_column: {worksheet.min_column}')  # 最小列 1
  92.         print(f'max_column: {worksheet.max_column}')  # 最大列 3
  93.         print(f'columns: {worksheet.columns}')  # 列对象
  94.  
  95.         print(worksheet['B1'].value)  # 查询单元格的数据 B1 表示第一行第二列对应单元格的数据
  96.  
  97.     # 读取数据
  98.     def read_data(self, worksheet: Worksheet = None):
  99.         if not worksheet:
  100.             worksheet = self.worksheet
  101.         # worksheet: Worksheet = self.workbook["Sheet1"]
  102.         title = self.get_title()
  103.         print("=====读取标题=====")
  104.         print(title)
  105.         # 按行读取数据
  106.         print("=====按行读取数据=====")
  107.         for row in worksheet.iter_rows(min_row=2, min_col=1, values_only=True):
  108.             # min_row从第二行开始，跳过第一行的标题
  109.             print(row)
  110.             # (1, 2, 3)
  111.             # (2, 3, 4)
  112.             # (4, 5, 6)
  113.         print("=====按单元格读取数据=====")
  114.         # 按单元格读取数据，同时给出单元格的行号和列号
  115.         for row in worksheet.iter_rows(min_row=2):
  116.             for cell in row:
  117.                 print((cell.row, cell.column), cell.value)  # openpyxl 单元格的下标都是从1开始的
  118.                 # (1, 1) column1
  119.                 # (1, 2) column2
  120.                 # (1, 3) column3
  121.  
  122.     # 查询和更新单元格数据
  123.     def update_data(self, worksheet: Worksheet = None):
  124.         if not worksheet:
  125.             worksheet = self.worksheet
  126.         # worksheet: Worksheet = self.workbook['Sheet1']
  127.         worksheet["A2"] = "A2"
  128.         # value = worksheet.cell(row=2, column=2).value
  129.         # print(value)
  130.         worksheet.cell(row=2, column=2).value = "B2"
  131.         worksheet.cell(row=2, column=3, value="C2")
  132.         # self.workbook.save(self.excel_name)  # 保存生效到当前文件
  133.         self.workbook.save("test2.xlsx")  # 改动部分文件另存为
  134.  
  135.     # 追加数据
  136.     def add_data(self, worksheet: Worksheet = None):
  137.         if not worksheet:
  138.             worksheet = self.worksheet
  139.         # sheet: Worksheet = self.workbook['Sheet1']
  140.         worksheet.append([5, 6, 7])
  141.         self.workbook.save("test3.xlsx")  # 改动部分文件另存为
  142.  
  143.     # 删除数据
  144.     def delete_data(self, worksheet: Worksheet = None):
  145.         if not worksheet:
  146.             worksheet = self.worksheet
  147.         # sheet: Worksheet = self.workbook['Sheet1']
  148.         worksheet.delete_rows(idx=2, amount=2)  # idx 行开始(含)，往下删除 amount 行
  149.         worksheet.delete_cols(idx=1, amount=2)  # idx 列开始(含)，往右删除 amount 行
  150.         self.workbook.save("test4.xlsx")  # 改动部分文件另存为
  151.  
  152.     # 清除表单数据
  153.     def clear_data(self, worksheet: Worksheet = None):
  154.         if not worksheet:
  155.             worksheet = self.worksheet
  156.         worksheet.delete_rows(idx=1, amount=worksheet.max_row)
  157.         worksheet.delete_cols(idx=1, amount=worksheet.max_column)
  158.  
  159.     def write_data(self, title, rows):
  160.         worksheet: Worksheet = self.workbook['Sheet1']
  161.         self.clear_data(worksheet)
  162.         worksheet.append(title)
  163.         for row in rows:
  164.             worksheet.append(row)
  165.         self.workbook.save("test5.xlsx")
  166.         # self.workbook.save(self.excel_name)
  167.  
  168.  
  169. if __name__ == '__main__':
  170.     title = ["column1", "column2", "column3"]
  171.     rows = [
  172.         [1, 2, 3],
  173.         [2, 3, 4],
  174.         [4, 5, 6]
  175.     ]
  176.     eh = ExcelHandler("test.xlsx")
  177.     # eh.get_sheet()
  178.     # eh.create_sheet()
  179.     # eh.copy_sheet()
  180.     # eh.delete_sheet()
  181.     # eh.move_sheet()
  182.     # eh.rename_sheet()
  183.     # print(eh.get_title())
  184.     # eh.read_meta_data()
  185.     # eh.read_data()
  186.     # eh.update_data()
  187.     # eh.add_data()
  188.     # eh.delete_data()
  189.     eh.write_data(title, rows)

csv.writer、csv.reader 

  1. import csv
  2.  
  3. title = ["第一列", "第二列", "第三列"]
  4. my_list = [
  5.     title,
  6.     [1, 2, 3],
  7.     [2, 3, 4],
  8.     [4, 5, 6]
  9. ]
  10.  
  11. with open('test.csv', 'w', newline='', encoding='utf-8') as f:
  12. # with open('test.csv', 'a+', newline='', encoding='utf-8') as f:
  13.     writer = csv.writer(f, delimiter=' ')
  14.     for row in my_list:
  15.         writer.writerow(row)
  16. # 第一列   第二列	第三列
  17. # 1	       2	    3
  18. # 2	       3	    4
  19. # 4	       5	    6
  20.  
  21. with open('test.csv', 'r', encoding='utf-8') as f:
  22.     reader = csv.reader(f)
  23.     for row in reader:
  24.         print(row)
  25. # ['第一列', '第二列', '第三列']
  26. # ['1', '2', '3']
  27. # ['2', '3', '4']
  28. # ['4', '5', '6']
  29.  
'
运行

csv.DictWriter、csv.DictReader

我比较推荐下面这种 csv 读写方式，可以直接写一行数据，列的顺序由 filenames 决定（就算重新修改列顺序也很方便），这样就不用像其他写法那样刻意去关注 row 中元素的顺序了。 

  1. import csv
  2.  
  3. data = [
  4.     {'age': 18, 'city': 'BeiJing'},
  5.     {'age': 30, 'city': 'ShangHai'},
  6.     {'age': 25, 'city': 'GuangZhou'},
  7.     {'age': 40, 'city': 'ShenZhen'},
  8. ]
  9.  
  10. with open('test.csv', 'w', newline='', encoding='utf-8') as f:
  11.     fieldnames = ['age', 'city']        # fieldnames 的顺序决定了 csv 列的顺序
  12.     writer = csv.DictWriter(f, fieldnames)
  13.     writer.writeheader()
  14.     for row in data:
  15.         writer.writerow(row)
  16. # age	city
  17. # 18	BeiJing
  18. # 30	ShangHai
  19. # 25	GuangZhou
  20. # 40	ShenZhen
  21.  
  22. with open('test.csv', 'r') as f:
  23.     reader = csv.DictReader(f)
  24.     for row in reader:
  25.         print(row)
  26. # OrderedDict([('age', '18'), ('city', 'BeiJing')])
  27. # OrderedDict([('age', '30'), ('city', 'ShangHai')])
  28. # OrderedDict([('age', '25'), ('city', 'GuangZhou')])
  29. # OrderedDict([('age', '40'), ('city', 'ShenZhen')])
'
运行

pandas.to_excel、pandas.read_excel

  1. import pandas as pd
  2.  
  3. data = {
  4.     "age": [18, 30, 25, 40],
  5.     "city": ["BeiJing", "ShangHai", "GuangZhou", "ShenZhen"]
  6. }
  7. index = pd.Index(data=["Tom", "Bob", "Mary", "James"], name="name")
  8. user_info = pd.DataFrame(data=data, index=index)
  9.  
  10. user_info.to_excel('test.xlsx')
  11. # name,age,city
  12. # Tom,18,BeiJing
  13. # Bob,30,ShangHai
  14. # Mary,25,GuangZhou
  15. # James,40,ShenZhen
  16.  
  17. result = pd.read_excel("test.xlsx")
  18. #     name  age       city
  19. # 0    Tom   18    BeiJing
  20. # 1    Bob   30   ShangHai
  21. # 2   Mary   25  GuangZhou
  22. # 3  James   40   ShenZhen
  23.  
  24. title = list(result.keys())
  25. rows = list(result.values)
  26. print(title)
  27. # ['name', 'age', 'city']
  28. print(rows)
  29. # [array(['Tom', 18, 'BeiJing'], dtype=object), 
  30. # array(['Bob', 30, 'ShangHai'], dtype=object), 
  31. # array(['Mary', 25, 'GuangZhou'], dtype=object), 
  32. # array(['James', 40, 'ShenZhen'], dtype=object)]
  33. #

pandas.to_csv、pandas.read_csv

与 to_excel，read_excel 用法类似

  1. import pandas as pd
  2.  
  3. data = {
  4.     "age": [18, 30, 25, 40],
  5.     "city": ["BeiJing", "ShangHai", "GuangZhou", "ShenZhen"]
  6. }
  7. index = pd.Index(data=["Tom", "Bob", "Mary", "James"], name="name")
  8. user_info = pd.DataFrame(data=data, index=index)
  9.  
  10. user_info.to_csv('test.csv')
  11. # name,age,city
  12. # Tom,18,BeiJing
  13. # Bob,30,ShangHai
  14. # Mary,25,GuangZhou
  15. # James,40,ShenZhen
  16.  
  17. result = pd.read_csv("test.csv")
  18. #     name  age       city
  19. # 0    Tom   18    BeiJing
  20. # 1    Bob   30   ShangHai
  21. # 2   Mary   25  GuangZhou
  22. # 3  James   40   ShenZhen
  23.  
  24. title = list(result.keys())
  25. rows = list(result.values)
  26. print(title)
  27. # ['name', 'age', 'city']
  28. print(rows)
  29. # [array(['Tom', 18, 'BeiJing'], dtype=object), 
  30. # array(['Bob', 30, 'ShangHai'], dtype=object), 
  31. # array(['Mary', 25, 'GuangZhou'], dtype=object), 
  32. # array(['James', 40, 'ShenZhen'], dtype=object)]
  33.  
  34. result = pd.read_csv("test.csv")
  35. print(result.shape)
  36. # (4, 3) 行和列数目
  37. columns = list(result.keys())
  38. print(columns)
  39. #  ['name', 'age', 'city']
  40. for i in range(result.shape[0]):  # 对每行进行遍历
  41.     row = result[i:i + 1]
  42.     for column in columns:  # 获取每行的数据
  43.         print(row[column].values[0], end=' ')
  44.     # Tom 18 BeiJing
  45.     # Bob 30 ShangHai
  46.     # Mary 25 GuangZhou 
  47.     # James 40 ShenZhen
  48.     print()
  49.  
  50.  
'
运行
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/酷酷是懒虫/article/detail/816980
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号