Python操作Excel文件（2）

Exploring

发布于 2022-09-20 14:52:26

3190

发布于 2022-09-20 14:52:26

文章被收录于专栏：数据处理与编程实践

文章背景：Excel是Window环境下流行的、强大的电子表格应用。openpyxl模块让Python程序能够读取和修改Excel电子表格文件。下面介绍如何通过Python操作Excel文件。

2 写入Excel文档

(1) 创建并保存Excel文档

import openpyxl, os
os.chdir("E:\python123")

wb = openpyxl.Workbook() # Create a blank workbook.
wb.sheetnames            # It starts with one sheet.

['Sheet']

wb.save('new.xlsx')      # Save the workbook.

(2) 创建和删除工作表

wb.create_sheet()        # Add a new sheet.
wb.sheetnames

['Sheet', 'Sheet1']

# Create a new sheet at index 0.
wb.create_sheet(index = 0, title ='First Sheet')
wb.sheetnames

['First Sheet', 'Sheet', 'Sheet1']

del wb['Sheet1']
wb.sheetnames

['First Sheet', 'Sheet']

wb.remove(wb['Sheet'])
wb.sheetnames

['First Sheet']

(3) 将值写入单元格

sheet = wb['First Sheet']

sheet['A1'] = 'Hello, world!' # Edit the cell's value.
sheet['A1'].value

'Hello, world!'

3 设置单元格的字体风格

import openpyxl, os
os.chdir('E:\\python123')

from openpyxl.styles import Font
wb = openpyxl.Workbook()
sheet = wb['Sheet']

italic24Font = Font(size=24, italic=True)  # Create a font.
sheet['A1'].font = italic24Font            # Apply the font to A1.

sheet['A1'] = 'Hello, world!'
wb.save('styles.xlsx')

Keyword Arguments for Font Objects：

4 公式

sheet['A3'] = '=SUM(A1:A2)' # Set the formula.

5 调整行和列

(1) 设置行高和列宽

# Set the height and width:
sheet.row_dimensions[1].height = 70
sheet.column_dimensions['B'].width = 20

(2) 合并和拆分单元格

import openpyxl, os
os.chdir('E:\\python123')

wb = openpyxl.Workbook()
sheet = wb.active

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

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

wb.save('merged.xlsx')

import openpyxl, os
os.chdir('E:\\python123')

wb = openpyxl.load_workbook('merged.xlsx')
sheet = wb.active

sheet.unmerge_cells('A1:D3')   # Split these cells up.
sheet.unmerge_cells('C5:D5')

wb.save('unmerged.xlsx')

6 冻结窗口

import openpyxl, os
os.chdir('E:\\python123')

wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb.active

sheet.freeze_panes = 'A2'   # Freeze the rows above A2.
wb.save('freezeExample.xlsx')

Frozen Pane Examples:

7 图表

To make a chart, you need to do the following:

Create a Reference object from a rectangular selection of cells.
Create a Series object by passing in the Reference object.
Create a Chart object.
Append the Series object to the Chart object.
Add the Chart object to the Worksheet object, optionally specifying which cell should be the top-left corner of the chart.

import openpyxl, os
os.chdir('E:\\python123')

wb = openpyxl.Workbook()
sheet = wb.active

# create some data in column A
for i in range(1,11):
    sheet['A'+str(i)] = i
    
refObj = openpyxl.chart.Reference(sheet,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.append(seriesObj)
chartObj.title = 'My chart'

sheet.add_chart(chartObj,'C5')
wb.save('sampleChart.xlsx')