openpyxl模块

openpyxl模块参考：
|
|https://www.cnblogs.com/programmer-tlh/p/10461353.html| |
|-----------------------------------------------------

一，初始化表格

from openpyxl  import load_workbook
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import Font, colors, Alignment
from openpyxl.styles import PatternFill
import os

'''初始化表格,新增sms,wx,push表格'''
def initexcel():

    #存在表格先删除，再初始化
    file_list=os.listdir('.')
    try:
        if message_name  in file_list:
            os.remove(message_name)
        wb = Workbook()
        ws = wb.active

        #将第一个sheet表格去掉，新增sms,wx,push表格，并在第一行插入表头信息
        sheetnames=wb.sheetnames
        if "Sheet" in sheetnames:
            del wb["Sheet"]
        for sheet in ['sms','wx','push']:
            if sheet not in sheetnames:
                ws = wb.create_sheet(sheet)
            ws['A1'],ws['B1'],ws['C1'],ws['D1'],ws['E1'] ,ws['F1'],ws['G1'],ws['H1'],ws['I1']= "code", "content", "url","create_time","title","checkcontent","checkurl","order_no","status"

            #设置列宽
            width = 20
            for i in range(1, ws.max_column + 1):
                ws.column_dimensions[get_column_letter(i)].width = width
            
            #设置填充颜色
            orange_fill = PatternFill(fill_type='solid', fgColor="FFC125")
            ws.cell(row=1,column=6).fill = orange_fill
            ws.cell(row=1,column=7).fill = orange_fill
        wb.save(message_name)

    except Exception as e:
        print('initexcel函数出错：', e)

二，读取exce数据

1，excel样式截图：

2，函数

from openpyxl  import load_workbook
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import Font, colors, Alignment
import os
message_name="message2020-09-28.xlsx"


'''
读取excel表中数据
1，读取一整行：readexcel("sms", nrow=5) ，返回列表
2，读取一整列：readexcel("sms",column=4)，返回列表
3，读取一个单元格：readexcel("sms", nrow=5,column=4)，返回一个值
'''
def readexcel(sheet,nrow=None,column=None):
    data = []
    try:
        file_list = os.listdir('.')
        if message_name  in file_list:
            wb = load_workbook(message_name)
            ws = wb[sheet]

            # 行和列都有值
            if nrow and column:
                data=ws.cell(row=nrow, column=column).value
                print(data)
                return data

            else:
                # 列有值
                if column:
                    ncolumn = get_column_letter(column)  # 数字得到字母
                    codes = ws[ncolumn]
                    for code in codes:
                        data.append(code.value)
                    data.pop(0)  #这里根据实际需要处理

                # 行有值
                elif nrow:
                    codes = ws[nrow]
                    for code in codes:
                        data.append(code.value)
                else:
                    print("行数或者列数必须有一个有赋值")
                return data

    except Exception as e:
        print('readbyrow函数出错：', e)
        return data


if __name__=="__main__":
    readexcel("sms", nrow=5,column=4)
    

from openpyxl  import load_workbook
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import Font, colors, Alignment
import os

'''
excel表写入值
1，color设置非None值，给字体加颜色
2，使用：write_excel(“sms”,“内容”,1,2,color=“1”)

'''
def write_excel(sheet,data,nrow,ncolumn,color=None):
    try:
        wb = load_workbook(message_name)
        ws = wb[sheet]
        ws.cell(row=nrow, column=ncolumn, value=data)

        #设置单元格文案颜色
        if color:
            font_color=Font(color="FFBB00", bold=True)
            name=str(get_column_letter(ncolumn))+str(nrow)
            ws[name].font = font_color
        wb.save(message_name)

    except Exception as e:
        print('write函数中保存表格出错：', e)