Python之Excel数据相关

Excel

Microsoft Excel是Microsoft为使用Windows和Apple Macintosh操作系统的电脑编写的一款电子表格软件。直观的界面、出色的计算功能和图表工具，再加上成功的市场营销，使Excel成为最流行的个人计算机数据处理软件。在1993年，作为Microsoft Office的组件发布了5.0版之后，Excel就开始成为所适用操作平台上的电子制表软件的霸主。

它也为早起Microsoft Office三大办公基本应用（Word、Excel、PowerPoint）之一。

Excel分为两种格式：xls（Excel 97-2003）和xlsx（Excel 2007及以上）

CSV（Comma Separated Values）

它是以逗号为分隔符的纯文本文件，而 Excel 文件是 Microsoft Office 软件生成的一种电子表格文件。

下面表格对比两者区别

示例

import os
import csv
import codecs
import openpyxl
from openpyxl.descriptors import NoneSet
from openpyxl.styles import (Font,Color,PatternFill
)
from openpyxl.chart import (PieChart,Reference
)class DealExport:def __init__(self):self.export_path = os.path.join(os.getcwd(), "data")def exportFileWithCSV(self, file_name, head, content):with open(os.path.join(self.export_path, file_name), mode='w', encoding='utf-8', newline='') as file:if file.tell() == 0:file.write(codecs.BOM_UTF8.decode("utf-8"))writer = csv.writer(file)writer.writerow(head)for row in content:writer.writerow(row)def exportFileWithXlsx(self, file_name, head, content):# 矩阵置换matrix = list(zip(*content))# 创建一个新的Excel文件workbook = openpyxl.Workbook()# 选择默认的活动工作表sheet = workbook.active# 输出工作表名ws = workbook.create_sheet('my_sheet', 0)totalRows = len(head)totalColumn = len(content)# for row in range(1, totalRows + 1):# ws.insert_rows(totalRows, totalColumn) # 在第一行插入rows列for i in range(0, totalRows):# 第 1 行 和 第 i+1 列 设置元素ws.cell(1, i + 1, head[i])self.setStyle(ws, i)for i in range(0, totalColumn):# 内容row = content[i]ws.append(row)# ws.row_dimensions[i].fill = PatternFill('lightDown', '00FFFF99', '00FFFF99')# ws.row_dimensions[i].height = 25# 读取数据表格范围rowsLen = ws.max_rowcolsLen = ws.max_column# for r in range(1, rows + 1):# for c in range(1, cols):# title = head[r]# sheet[r, c] = title[r]# # 设置列宽# sheet.column_dimensions[r].width = 20# # 创建饼状图# pie = PieChart()# # 说明 第 1 列 第 1行 至 第 headLen 行# labels = Reference(ws, 1, 1, totalRows)# # 数据# data = Reference(ws, totalColumn, 2, totalRows, 2)# pie.add_data(data, True)# pie.set_categories(labels)# pie.title = "测试饼状图"## ws.add_chart(pie, "D1")# 保存Excel文件workbook.save(os.path.join(self.export_path, file_name))def setStyle(self, sheet, index):# 设置字体样式font = Font('Arial', True, True, Color('00000000'))sheet['A1'].font = font# 设置单元格背景颜色# state = NoneSet(values=(['visible', 'hidden', 'veryHidden', 'show']))# 首行sheet.cell(1, index + 1).fill = PatternFill('solid', '0000FFFF', '0000FFFF')if __name__ == '__main__':deal = DealExport()head = ("对象", "名称", "描述", "类型", "第一年数量", "第二年数量", "第三年数量", "第四年数量")content = [["TYPE_A", "This must be subclassed", "demo", "add", 1, 2, 3, 4],["TYPE_B", "Defines the interface for stateless encoders/decoders", "demo", "delete", 2, 3, 4, 5],["TYPE_C","Encodes the object input and returns a tuple" + "\n" + "--" * 30 + "\n" + "Decodes the object input and returns a tuple","demo", "update", 3, 4, 5, 6],["TYPE_D", "Creates an IncrementalEncoder instance.", "demo", "add", 4, 5, 6, 7],["TYPE_E","This subclass of IncrementalEncoder can be used as the baseclass for an incremental encoder if the encoder must keep some of the output in a buffer between calls to encode()","demo", "update", 5, 6, 7, 8]]deal.exportFileWithCSV("test.csv", head, content)deal.exportFileWithXlsx("test.xlsx", head, content)

输出csv文件结果如下，大小为675字节

输出xlsx文件结果如下，大小为6KB = 6144字节

参考

1. 腾讯云_一文看懂用Python读取Excel数据
2. CSDN_openpyxl包操作Excel文件