python处理excel文件

一、用openpyxl设置单元格格式

        下面的代码是像已经存在的excel中，写入数据，并且设置字体、颜色、边框、背景、合并单元格等格式

# -*- coding:utf-8 -*-
from openpyxl.styles import Border, Side, PatternFill, Font, Alignment
from openpyxl import load_workbook

file_name = 'sample.xlsx'
sheet_name = 'Sheet1'


def write_data(row=2, col=2, value=None):
    print('select sheet {}, input {} to cell X-{} Y-{}'.format(sheet_name, value, row, col))
    wb = load_workbook(file_name)  # 打开文件
    ws = wb[sheet_name]  # 选择sheet

    # 写入值
    ws.cell(row, col, value)
    
    # 读取值
    print(ws.cell(row,col).value)    

    # 设置边框
    side = Side(style="thin", )
    ws.cell(row, col).border = Border(top=side, bottom=side, left=side, right=side)

    # 设置背景
    fgcolor = 'D3D3D3'  # 灰色
    ws.cell(row, col).fill = PatternFill(patternType="solid", fgColor=fgcolor)

    # 设置字体颜色，加粗
    ws.cell(row, col).font = Font(name='等线', size=11)
    ws.cell(row, col).font = Font(color='FF3030', bold=True, )

    # 合并单元格
    ws.merge_cells(start_row=row, start_column=col, end_row=row, end_column=col + 1)

    # 设置对齐
    ws.cell(row, col).alignment = Alignment(horizontal='center', vertical='center')

    # 保存文件
    wb.save(file_name)


if __name__ == '__main__':
    write_data(value='你好')

效果：

 

二、新建、删除 sheet

    wb = Workbook()  # 创建一个工作簿
    wb.create_sheet('s1') # 新建sheet
    wb.create_sheet('s2')
    print(wb.sheetnames)
    wb.remove(wb['Sheet']) #删除sheet
    ws = wb['s1']
    ws.cell(1, 1, '888')

    wb.save('sample1.xlsx')

三、pandas数据查找

import pandas as pd


file_name = 'xx.xlsx'
sheet_name = 'Sheet1'

def read_nl(dire='wan-wifi', fre='5g', cha=36, bw=80, ip='ipv4', qos='off'):
    df = pd.read_excel(io=file_name, sheet_name=0)  # 读入数据
    df['Bw'] = df['Bw'].apply(pd.to_numeric) # 转化为数字
    df['Chan'] = df['Chan'].apply(pd.to_numeric)
    df['Tx'] = df['Tx'].apply(pd.to_numeric)
    df['Rx'] = df['Rx'].apply(pd.to_numeric)
    
    # 过滤每列的值
    myfilter = (df['Direction'] == dire) & (df['Fre'] == fre) & (df['Chan'] == cha) \
               & (df['Bw'] == bw) & (df['IpVer'] == ip) & (df['QoS'] == qos)

    res = df.loc[myfilter]
    print(res) 
    #    Fre  Chan    Bw Direction IpVer  QoS     Tx     Rx
    #10  5g  36.0  80.0  wan-wifi  ipv4  off  800.0  800.0
    
    if res.empty:
        print('未找到标准值')
    else:
        tmp = {
            'tx': int(res['Tx']),
            'rx': int(res['Rx'])
        }
        print(tmp)
        return tmp


if __name__ == '__main__':
    read_nl() # 输出 {'tx': 800, 'rx': 800}

四、excel中画折线图

# -*- coding:utf-8 -*-
from openpyxl.chart import LineChart, Reference
from openpyxl import load_workbook

file_name = 'sample.xlsx'
sheet_name = 'Sheet1'


def draw(title='picture'):
    s_row = 7    # 数据起始行
    min_col = 3  # 数据起始列
    length = 12  # 数据长度

    # 画折线图
    wb = load_workbook(file_name)
    ws = wb[sheet_name]

    chart = LineChart()
    chart.width = 16
    chart.height = 10
    chart.legend.position = 'b'

    # # 设置图表样式
    # chart.style = 26
    # 图的标题
    chart.title = title
    # y轴标题
    chart.y_axis.title = "销售额"
    # x轴标题
    chart.x_axis.title = "月份"

    # 数据来源
    da = Reference(worksheet=ws, min_row=s_row + 1, max_row=s_row + 2, min_col=min_col,
                   max_col=min_col + length)
    # 设定x轴
    categories = Reference(ws, min_row=s_row, max_row=s_row, min_col=min_col + 1,
                           max_col=min_col + length)
    # 设置图例为数据的第一列
    chart.add_data(da, from_rows=True, titles_from_data=True)
    chart.set_categories(categories)

    # 设置图片的位置
    ws.add_chart(chart, "D11" )

    s1 = chart.series[0]  # 第一条线
    s1.marker.symbol = "circle"  # 节点出的形状  三角形
    s1.graphicalProperties.line.width = 30000  # 设置线宽

    s1 = chart.series[1]  # 第2条线
    s1.marker.symbol = "circle"  # 节点出的形状
    s1.graphicalProperties.line.width = 30000  # 设置线宽

    wb.save(file_name)


if __name__ == '__main__':
    draw()

效果：