python openpyxl 简单使用

1. 加载excel

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

# 加载example.xlsx，参数data_only=False表示如果单元格是公式，则显示公式，而不是公式的计算结果
wb = openpyxl.load_workbook('example.xlsx',data_only=False)

# 列出所有sheet名称，返回一个list
sheets = wb.sheetnames
print('Sheets:',sheets)

# sheet对象，获取激活的sheet
activesheet = wb.active

# 打开特定的sheet
ws = wb['sheetName']

# 获取sheet的名称
print(activesheet.title)

# 获取单元格对象
a1 = activesheet['A1']

# 单元格的值
print(a1.value)

# 获取单元格对象的行列,以及单元格名称，coordinate返回单元格的名称。
print(a1.row,a1.column,a1.coordinate,sep=',')

# 根据 行列 来定位一个单元格
a2 = activesheet.cell(row=2,column=2)
print('A2 value:',a2.value)

# 获取表中的最大行数和列数
highest = activesheet.max_row
wid = activesheet.max_column
print('MaxRow,MaxCol:',highest,wid)

# 转换列名和数字
print(get_column_letter(77),column_index_from_string('AA'))

# 使用切片来获取一个区域，返回元组形式
field = activesheet['A1':'C3']
print('Field:',field)

for x in activesheet['A1':'C3']:  # 先获取元组的元素：一行的所有单元格（元组格式）
    for y in x:                   # 再获取元组的每一个元素：单元格
        print(y.coordinate,y.value)


wb.save('new.xlsx')   # 保存文件

2. 创建新的excel文件

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


# 创建写入excel
if os.path.exists('new.xlsx'):
    os.unlink('new.xlsx')   # 如果存在 new.xlsx ,先删除

# 创建工作簿对象
wb = openpyxl.Workbook()

# 获取激活的表单
active = wb.active

# 获取sheet名称
print(active.title)

# 给sheet重命名
active.title = 'Abc'

# 创建带索引的sheet，也就是sheet表单的顺序，索引为1代表放在第一个
wb.create_sheet(index=1,title='New_sheet')
wb.create_sheet(index=2,title='New_sheet2')

# 获取所有sheet名
sheets = wb.get_sheet_names
print(sheets)

# 删除一个工作表
wb.remove(wb['New_sheet2'])
for x in range(1,100):     # 写入excel
    active.cell(row=x,column=1,value=x)

# 设置单元格样式
active.row_dimensions[1].height = 30   # 设置行高
active.column_dimensions['A'].width = 30 #设置列宽

# 合并单元格并赋值
active.merge_cells('A1:C1')
active['A1'] = 'hebkdjyrge'

#拆分单元格
active.unmerge_cells('A1:C1')

# 冻结 A3 以前的单元格,即 A1 和 A2 冻结
active.freeze_panes = 'A3'

# 设置字体
geui = Font(size=15,name='Arial',bold=True,italic=False,color='FFFFFF')
colorr = PatternFill('solid',bgColor='4F4F4F')
active['A1'].fill = colorr
active['A1'].font = geui

# 创建图表，没搞明白
for i in range(1,11):
    active['A'+str(i)] = i
refObj = openpyxl.chart.Reference(active, min_row=1, min_col=1, max_row=10, max_col=1)
seriesObj = openpyxl.chart.Series(refObj, title='First series')
chartObj = openpyxl.chart.BarChart()
chartObj.title = 'My Chart'
chartObj.append(seriesObj)

active.add_chart(chartObj, 'C5')  # C5表示图标开始位置

wb.save('new.xlsx')   # 保存文件

3. 读取Excel的某列

import openpyxl
import shutil
import sys


def read_Excel(file,row=2,*col):
    '''
    file: excel的文件名
    row：默认从第几行开始，比如第一行是title，则可以从第二行开始
    *col: 列名，获取哪些列的数据
    '''
    case_list = {}
    workbook = openpyxl.load_workbook(file)
    sheet0 = workbook.active  # 获取当前激活的工作表
    highest = sheet0.max_row  # 获取表单有数据的最大行数
    first_col = col[0]        # 第一个列参数
    for i in range(row,highest+1):
        value_list = []
        v1 = sheet0[first_col+str(i)].value
        # 除去第一列的其他列
        for j in range(1,len(col)):
            v2 = sheet0[col[j]+str(i)].value
            value_list.append(v2)
        case_list[v1] = value_list
    print(case_list)
    return case_list

read_Excel('结果.xlsx',2,'a','b','c')

待处理数据：

 结果：

{'N1': ['B1', 'C1'], 'N2': ['B2', 'C2'], 'N3': ['B3', 'C3']}

 4. 设置边框

import openpyxl

from openpyxl import load_workbook
from openpyxl.styles import Border, Side, colors,Font,PatternFill

excel_address = "112report.xlsx"
wb = load_workbook(excel_address)
sht = wb.worksheets[0]

sht["D5"] = "测试"
# 全边框
border_all = Border(left=Side(style='thin', color=colors.BLACK),
                    right=Side(style='thin', color=colors.BLACK),
                    top=Side(style='thin', color=colors.BLACK),
                    bottom=Side(style='thin', color=colors.BLACK))
# 无上边框
border_set = Border(left=Side(style='thin', color=colors.BLACK),
                    right=Side(style='thin', color=colors.BLACK),
                    top=Side(style='thin', color=colors.BLACK),
                    bottom=Side(style='thin', color=colors.BLACK))
# 背景色
fill_1 = PatternFill("solid", fgColor="E6E6E6")

# 字体
title = Font(name='Arial',bold=True)
con = Font(name='Arial',bold=False)

sht["D5"].border = border_set
sht["D5"].fill = fill_1
sht["D5"].font = title


wb.save(excel_address)