Python操作Excel 之 openpyxl

一、基础

1. 安装openpyxl 模块
   pip install openpyxl 或者通过轮子安装

• font(字体类)：字号、字体颜色、下划线等

• fill(填充类)：颜色等

• border(边框类)：设置单元格边框

• alignment(位置类)：对齐方式

• number_format(格式类)：数据格式

• protection(保护类)：写保护

1.1 创建excel文件

from openpyxl import Workbook
wb = Workbook()   #创建文件对象

wb.remove(wb["sheet"])

wb.remove(self.wb["sheet"])  #移除掉第一个sheet

ws = wb.create_sheet("{0}蒸汽压力记录表".format(self.sample_name), 0)

#生成所含列总数的大写字母
upper_string_list = string.ascii_uppercase[:self.all_col]

wb.save("e:\sample.xlsx")

1.2 操作单元格

from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet")           #创建一个sheet

ws1["A1"]=123.11
ws1["B2"]="你好"
d = ws1.cell(row=4, column=2, value=10)

print ws1["A1"].value
print ws1["B2"].value
print d.value

# Save the file
wb.save("e:\sample.xlsx")

1.3 对多个单元格

import uuid
import string
import json
import time
import os
import base64

from openpyxl.workbook import Workbook
from openpyxl.styles import Font, Alignment, Side, Border

from Lib.Utils import Utils

class ExportReport:

    def __init__(self, start_time, water_temp, test_people,
                 sample_name, pressure_value, pressure_list,*args, **kwargs):
        """

        :param start_time: 实验开始时间
        :param water_temp: 水浴温度
        :param test_people: 试验人
        :param sample_name: 样品名称
        :param pressure_value: 最终压力
        :param args: 实验过程压力记录列表
        :param kwargs:
        """
        self.start_time = start_time
        self.water_temp = str(water_temp)+' ℃'
        self.test_people = test_people
        self.sample_name = sample_name
        self.pressure_value = pressure_value
        self.all_col = 5+len(pressure_list) #一共多少列
        self.pressure_record = pressure_list #第六列到最后一列的数据列表

        #所有的数据列
        self.data_list = [
            self.start_time, self.water_temp,
            self.test_people,self.sample_name,
            self.pressure_value
        ]
        self.data_list.extend(self.pressure_record)
        self.col_list = ["开始时间", "水浴温度", "测试人", "样品名称", "最终压力值"]

        # base64转化为图片
        # self.bs64 = bs64
        # self.img_path = Utils.change_base64_as_img(self.bs64)

        self.wb = Workbook()

        #self.wb.remove(self.wb["sheet"])

        self.ws = self.wb.create_sheet("{0}蒸汽压力记录表".format(self.sample_name), 0)
        #生成所含列总数的大写字母
        self.upper_string_list = string.ascii_uppercase[:self.all_col]
        # 水平对齐，居中对齐
        self.alignment_style = Alignment(horizontal='center', vertical='center')
        #定义border 边框样式
        left, right, top, bottom = [Side(style='thin', color='000000')]*4
        self.border_style = Border(left=left, right=right, top=top, bottom=bottom)
        #定义字体
        self.font_size = Font(size=9)
        for col in self.upper_string_list:
            self.ws.column_dimensions[col].width = 20

    #创建表头第一行
    def create_row1(self):
        #把所有列合并
        self.ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=self.all_col)
        #写入值
        # self.ws.cell(row=1, column=1).value = value
        self.ws.cell(row=1, column=1).value = "{0}蒸汽压力记录表".format(self.sample_name)
        self.ws['A1'].alignment = self.alignment_style
        self.ws['A1'].font = Font(size=16, bold=True)
        self.create_row2_3()

    def create_row2_3(self):
        #把前五列，二三行单元格合并，并写入值
        for col in range(1, len(self.col_list)+1):
            self.ws.merge_cells(start_row=2, end_row=3, start_column=col, end_column=col)
            col_str = self.upper_string_list[col-1]+"2"
            self.ws[col_str] = self.col_list[col-1]
            self.ws[col_str].alignment = self.alignment_style
            self.ws[col_str].font = Font(size=12, bold=True)

        #把第二行第六列开始到最后列合并
        self.ws.merge_cells(start_row=2, end_row=2, start_column=len(self.col_list)+1, end_column=self.all_col)
        col_str = self.upper_string_list[len(self.col_list)]+"2"
        self.ws[col_str] = "实验过程压力记录"
        self.ws[col_str].alignment = self.alignment_style
        self.ws[col_str].font = Font(size=12, bold=True)

        #第三行第六列开始到最后列写入值
        for index, col_ltr in enumerate(self.upper_string_list[5:]):
            col_str = col_ltr+'3'
            self.ws[col_str] = "第{0}次压力记录".format(index+1)
            self.ws[col_str].alignment = self.alignment_style
            self.ws[col_str].font = Font(size=12, bold=True)

    def add_data(self):
        #第四行开始写入数据,所有数据居中对齐，水平居中
        for index, col in enumerate(self.upper_string_list):
            col_str = col+"4"
            self.ws[col_str] = self.data_list[index]
            self.ws[col_str].alignment = self.alignment_style
            self.ws[col_str].font = Font(size=12, bold=True)

    def create(self,value=None):
        if value:
            self.ws.cell(row=1, column=1).value = value
        self.create_row1()
        self.add_data()

    def save(self, filename):
        try:
            self.wb.save(filename)
        except:
            self.wb.save(filename[:-5] + str('_' + Utils.getFileName()) + filename[-5:])

        # 关闭excel
        self.close()

    def close(self):
        self.wb.close()

if __name__ == '__main__':
    er = ExportReport(1, 2, 3, 4, 5, ['x', 's', 's', 'b','w'])
    er.create()
    er.wb.save('17表.xlsx')