python处理Excel 电子表格

一、读取电子表格

# 1 打开excel文档
import openpyxl
wb = openpyxl.load_workbook(r'D:\XXX\spider_document\example.xlsx')
type(wb)

# 2 从工作簿中取得工作表
import openpyxl
wb =openpyxl.load_workbook(r'D:\XXX\spider_document\example.xlsx')
wb.get_sheet_names()
sheet = wb.get_sheet_by_name('Sheet3')
type(sheet)
sheet.title
anothersheet = wb.get_active_sheet()

# 3 从表中取得单元格
import openpyxl
wb =openpyxl.load_workbook(r'D:\XXX\spider_document\example.xlsx')
sheet= wb.get_sheet_by_name('Sheet1')

# 1）sheet['A1']的形式
sheet['A1']
sheet['A1'].value
c = sheet['B1']
c.value
'Row ' + str(c.row) + ', Column' + ' is ' + c.value
sheet['C1'].value

# 2）sheet.cell(row=1, column=2)
sheet.cell(row=1, column=2)  # row、column可以省略
sheet.cell(row=1, column=2).value
for i in range(1, 8, 2):
    print(i, sheet.cell(row=i, column=2).value)

# 3）输出数据最大行列数
import openpyxl
wb =openpyxl.load_workbook(r'D:\XXX\spider_document\example.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
sheet.calculate_dimension()  # excel 数据范围：'A1:C7'
sheet.get_highest_row()  # 如果报错用：sheet.max_row
sheet.get_highest_column()  # 如果报错用：sheet.max_row

# 4 列字母和数字之间的转换
import openpyxl
# from openpyxl.cell import get_column_letter, column_index_from_string
# 已经重写，改成以下形式：
from openpyxl.utils import get_column_letter,column_index_from_string
get_column_letter(1)
get_column_letter(2)
get_column_letter(27)
get_column_letter(900)
wb =openpyxl.load_workbook(r'D:\XXX\spider_document\example.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
get_column_letter(sheet.max_column)
column_index_from_string('A')
column_index_from_string('AA')


# 5 从表中取得行和列

# 1）选取A1：C3的内容
import openpyxl
wb =openpyxl.load_workbook(r'D:\XXX\spider_document\example.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
tuple(sheet['A1':'C3'])  # 对excel区域切片，选择A1：C3的一个矩形框
for Row_of_CellObjects in sheet['A1':'C3']:
    for CellObj in Row_of_CellObjects:
        print(CellObj.coordinate,CellObj.value)
    print('---END OF ROW---')

# 2）选取第一列（A列）的内容
import openpyxl
wb =openpyxl.load_workbook(r'D:\XXX\spider_document\example.xlsx')
sheet = wb.get_active_sheet()

for i in list(sheet.columns)[0]:  # 因为新版本的openpyxl使用rows或者columns返回一个生成器所以可以使用List来解决报错问题
    print(i.value)

二、写入excel文档

import os
os.getcwd()  # 获取当前文件夹
os.chdir('D:\\XXX\\vscode_document')  # 修改当前文件夹

# 1 创建并保存excel文档
import openpyxl
wb = openpyxl.Workbook()
wb.get_sheet_names()
sheet = wb.get_active_sheet()
sheet.title
sheet.title = 'Spam Bacon Eggs Sheet'
wb.get_sheet_names()

# 建立、修改sheet名字，并保存
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
sheet.title = 'Spam Sapm Spam'
wb.save('example_copy.xlsx')

# 2 创建和删除工作表
import openpyxl
wb = openpyxl.Workbook()
wb.get_sheet_names()
# 创建
wb.create_sheet()
wb.get_sheet_names()

wb.create_sheet(index=0, title='First Sheet')
wb.get_sheet_names()

wb.create_sheet(index=2, title='Middle Sheet')
wb.get_sheet_names()
# 删除
wb.remove_sheet(wb.get_sheet_by_name('Middle Sheet'))
wb.remove_sheet(wb.get_sheet_by_name('Sheet1'))
wb.get_sheet_names()

# 3 将值写入单元格
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_sheet_by_name('Sheet')
sheet['A1'] = 'Hello world!'
sheet['A1'].value

三、格式等设置

# 1 设置单元格字体风格
import openpyxl
from openpyxl.styles import Font
'''
Font 的参数：
1、name：字符串，字体名称，如：'Times New Roman'
2、size：整型，大小点数
3、blod：布尔型，True表示粗体
4、italic：布尔型，True表示斜体
'''
wb = openpyxl.Workbook()
sheet = wb.get_sheet_by_name('Sheet')
italic24Font = Font(size=24,italic=True)
sheet['A1'].font = italic24Font
sheet['A1'] = 'Hello world!'
wb.save('styled.xlsx')

# 2 Font对象
import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook()
sheet = wb.get_sheet_by_name('Sheet')

FontObj_1 = Font(name='Times New Roman', bold=True)
sheet['A1'].font = FontObj_1
sheet['A1'] = 'Bold Times New Roman'

FontObj_2 = Font(size=24, italic=True)
sheet['B3'].font = FontObj_2
sheet['B3'] = '24 pt Italic'

wb.save('styles.xlsx')

# 3 公式
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=sum(A1:A2)'  # 输入公式的表达：输入和excel一样等号开头
sheet['A3'].value  # 输出为公式的文本：'=sum(A1:A2)'
wb.save('writeFormula.xlsx')

wbDataOnly = openpyxl.load_workbook('writeFormula.xlsx', data_only=True)  # data_only，只读取结果，不显示公式的文本
sheet = wb.get_active_sheet()
sheet['A3'].value  # 输出为数值：500

# 4 调整行和列

# 1) 设置行高和列宽
# Worksheet对象有row_dimension和column_dimensions属性，控制行高和列宽
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
sheet['A1'] = 'Tall Row'
sheet['B2'] = 'Wide column'
sheet.row_dimensions[1].hihgt = 70
sheet.column_dimensions['B'].width = 20
wb.save('dimensions.xlsx')

# 2) 合并和拆分单元格
# （1）合并
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()

sheet.merge_cells('A1:D3')
sheet['A1'] = 'Twelve cells merged together'

sheet.merge_cells('C5:D5')
sheet['C5'] = 'Two cells merged together'

wb.save('merged.xlsx')

# （2）取消合并
import openpyxl
wb = openpyxl.load_workbook('merged.xlsx')
sheet = wb.get_active_sheet()
sheet.unmerge_cells('A1:D3')
sheet.unmerge_cells('C5:D5')
wb.save('merged.xlsx')

# 3) 冻结窗格
import openpyxl
wb = openpyxl.load_workbook('freeze.xlsx')
sheet = wb.get_active_sheet()
sheet.freeze_panes = 'A2'  # 单元格上边、左边的部分会冻结，但单元格所在的行和列不会冻结
wb.save('freeze.xlsx')

四、创建图表

# 1)
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
# 创建数据
for i in range(1,11):  
    sheet['A' + str(i)] = i
# 设置表格样式
chartObj = openpyxl.chart.BarChart()
chartObj.type = "col"
chartObj.style = 10
chartObj.title = "Bar Chart"
chartObj.y_axis.title = 'Test number'
chartObj.x_axis.title = 'Sample length (mm)'

data = openpyxl.chart.Reference(sheet,min_col=1, min_row=1, max_row=10, max_col=1)  # 选择数据
chartObj.add_data(data, titles_from_data=False)
sheet.add_chart(chartObj, "A10")

wb.save("bar_1.xlsx")

# 2) 
from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference
 
wb = Workbook(write_only=True)
ws = wb.create_sheet()
 
rows = [
    ('Number', 'Batch 1', 'Batch 2'),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]
 
for row in rows:
    ws.append(row)
 
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'
 
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
 
from copy import deepcopy
 
chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"
ws.add_chart(chart2, "G10")
 
chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'
ws.add_chart(chart3, "A27")
 
chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'
ws.add_chart(chart4, "G27")
 
wb.save("bar_2.xlsx")