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 软件生成的一种电子表格文件。

下面表格对比两者区别

扩展名	格式	编辑	功能	大小	兼容性
xlsx(xls)	包含复杂的格式，如图表，公式，宏等	可以使用 Microsoft Office 软件进行编辑	Excel 文件具有丰富的分析和计算功能	较大	兼容性取决于使用的软件版本
csv	只包含简单的文本和数字	可以使用任何文本编辑器，如 Notepad 或 Sublime Text	CSV 文件仅提供简单的数据存储功能	较小	具有较高的兼容性

示例

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
)

red = PatternFill("darkGrid", "FF0000", "FF0000")  # 填充颜色红色
green = PatternFill("lightUp", '00FFFF99')  # 填充颜色绿色
yellow = PatternFill("lightUp", "FFFF00")  # 填充颜色黄色
blue = PatternFill("solid", "33ccff")  # 填充颜色蓝色


class DealExport:
    """
    type() 可以打印python对象类型
    如果是字典对象 可以直接使用 dict()强转
    如果是集合对象 可以直接使用 list()强转
    使用 json.loads() 可以将 JSON字符串解码为 Python对象
    使用 json.dumps(obj, ensure_ascii=False) 可以将python对象obj转换成JSON字符串对象, ensure_ascii=False可以解决中文乱码问题
    """

    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 + 2].fill = red
            # ws.row_dimensions[i + 1].height = 25
            # 读取数据表格范围
        for i in range(2, ws.max_row + 1):
            for j in range(1, ws.max_column + 1):
                ws.cell(i, j).fill = yellow
        # 设置列宽，列宽column_dimensions参数是列标签
        ws.column_dimensions['B'].width = 160

        # # 创建饼状图
        # 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字节

补充

操作excel表格除了openpyxl模块以外，还有pandas模块等，其他模块可参见参考链接3。

• openpyxl是Python中操作Excel文件的库，支持读写Excel文件，使用简单方便。
• pandas是Python中广泛使用的数据处理库，支持多种数据类型，如时间序列、表格数据等，同时也支持多种文件格式，如Excel、CSV、JSON等。pandas中主要的数据结构是Series和DataFrame。其中，Series可以视为一维数组，DataFrame可以视为二维数据表。

参考

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