第七章：编程处理

7.1 为什么用编程处理Excel

除了在Excel中使用公式和函数，我们还可以使用编程语言来处理Excel文件。这种方式特别适合批量处理、自动化任务和复杂的数据分析。

编程处理Excel的优势

适用场景：
  • 定期需要生成相同格式的报表
  • 需要处理大量Excel文件
  • 数据需要与其他系统集成
  • 需要复杂的数据清洗和分析
  • 需要自动化任务调度

7.2 Python处理Excel

Python有多个强大的库可以处理Excel文件，最常用的是openpyxl、pandas和xlsxwriter。

Python选择建议：Python适合数据分析、机器学习、快速原型开发，生态系统非常丰富，有大量的第三方库支持。

7.2.1 使用openpyxl

openpyxl是一个用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。

安装

pip install openpyxl

基本操作

from openpyxl import Workbook, load_workbook

# 创建新工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售数据"

# 写入数据
headers = ["产品", "数量", "单价", "金额"]
ws.append(headers)

# 添加数据行
data = [
["产品A", 100, 50, 5000],
["产品B", 200, 80, 16000],
["产品C", 150, 60, 9000]
]

for row in data:
ws.append(row)

# 使用公式计算金额
for row in range(2, 5):
ws[f"D{row}"] = f"=B{row}*C{row}"

# 保存文件
wb.save("销售数据.xlsx")

读取现有文件

from openpyxl import load_workbook

# 读取现有Excel文件
wb = load_workbook("销售数据.xlsx")
ws = wb.active

# 读取数据
for row in ws.iter_rows(min_row=2, values_only=True):
print(f"产品: {row[0]}, 数量: {row[1]}, 单价: {row[2]}, 金额: {row[3]}")

样式设置

from openpyxl.styles import PatternFill, Font, Alignment

wb = load_workbook("销售数据.xlsx")
ws = wb.active

# 设置标题样式
header_fill = PatternFill(start_color="4CAF50", end_color="4CAF50", fill_type="solid")
header_font = Font(bold=True, color="FFFFFF")
header_alignment = Alignment(horizontal="center")

for cell in ws[1]:
cell.fill = header_fill
cell.font = header_font
cell.alignment = header_alignment

# 设置条件格式
red_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")

for row in ws.iter_rows(min_row=2):
if row[3].value > 10000: # 如果金额大于10000
row[3].fill = red_fill

wb.save("销售数据_格式化.xlsx")

7.2.2 使用pandas处理Excel

pandas是数据分析的神器，非常适合处理Excel数据。

安装

pip install pandas openpyxl

读取Excel文件

import pandas as pd

# 读取Excel文件
df = pd.read_excel("销售数据.xlsx", sheet_name="销售数据")

# 查看数据
print(df.head())
print(df.info())
print(df.describe())

数据分析

# 计算统计信息
total_sales = df["金额"].sum()
avg_price = df["单价"].mean()
max_amount = df["金额"].max()

print(f"总销售额: {total_sales}")
print(f"平均单价: {avg_price}")
print(f"最大金额: {max_amount}")

# 数据筛选
high_sales = df[df["金额"] > 10000]
print("高销售额产品:")
print(high_sales)

# 数据分组
product_stats = df.groupby("产品").agg({
"数量": "sum",
"金额": "sum"
})
print("产品统计:")
print(product_stats)

数据处理

# 添加新列（类似Excel公式）
df["折扣后金额"] = df["金额"] * 0.9 # 9折
df["销售等级"] = df["金额"].apply(
lambda x: "高" if x > 10000 else ("中" if x > 5000 else "低")
)

# 数据清洗
# 删除空值
df_clean = df.dropna()

# 删除重复值
df_unique = df.drop_duplicates()

# 填充空值
df_filled = df.fillna(0)

保存到Excel

# 保存到Excel
df.to_excel("销售数据_处理后.xlsx", index=False)

# 多个sheet写入
with pd.ExcelWriter("销售报表.xlsx") as writer:
df.to_excel(writer, sheet_name="原始数据", index=False)
high_sales.to_excel(writer, sheet_name="高销售额", index=False)
product_stats.to_excel(writer, sheet_name="产品统计")

与Excel公式结合

from openpyxl import load_workbook

# 保存数据
df.to_excel("销售数据_带公式.xlsx", index=False)

# 使用openpyxl添加公式
wb = load_workbook("销售数据_带公式.xlsx")
ws = wb.active

# 在Excel中添加公式列
ws["E1"] = "折扣金额"
for row in range(2, len(df) + 2):
ws[f"E{row}"] = f"=D{row}*0.1"

wb.save("销售数据_带公式.xlsx")

7.2.3 使用xlsxwriter创建报表

xlsxwriter专门用于创建Excel文件，支持图表和丰富的格式。

安装

pip install xlsxwriter

创建带格式的报表

import xlsxwriter

# 创建新工作簿
workbook = xlsxwriter.Workbook("月度报表.xlsx")
worksheet = workbook.add_worksheet("销售报表")

# 定义格式
header_format = workbook.add_format({
"bold": True,
"bg_color": "#4CAF50",
"font_color": "white",
"border": 1
})

number_format = workbook.add_format({
"num_format": "#,##0.00",
"border": 1
})

percent_format = workbook.add_format({
"num_format": "0.0%",
"border": 1
})

# 写入标题
headers = ["月份", "销售额", "成本", "利润", "利润率"]
for col, header in enumerate(headers):
worksheet.write(0, col, header, header_format)

# 写入数据
data = [
["1月", 100000, 60000, 40000],
["2月", 120000, 70000, 50000],
["3月", 150000, 85000, 65000],
["4月", 180000, 100000, 80000]
]

for row_idx, row_data in enumerate(data, start=1):
worksheet.write(row_idx, 0, row_data[0])
worksheet.write(row_idx, 1, row_data[1], number_format)
worksheet.write(row_idx, 2, row_data[2], number_format)
worksheet.write_formula(row_idx, 3, f"B{row_idx+1}-C{row_idx+1}", number_format)
worksheet.write_formula(row_idx, 4, f"D{row_idx+1}/B{row_idx+1}", percent_format)

添加条件格式

# 添加条件格式
worksheet.conditional_format("D2:D5", {
"type": "cell",
"criteria": ">",
"value": 50000,
"format": workbook.add_format({"bg_color": "#FFC107"})
})

创建图表

# 创建图表
chart = workbook.add_chart({"type": "column"})
chart.add_series({
"name": "销售额",
"categories": ["销售报表", 0, 0, 0, 3],
"values": ["销售报表", 1, 1, 4, 1],
"fill": {"color": "#4CAF50"}
})
chart.add_series({
"name": "成本",
"categories": ["销售报表", 0, 0, 0, 3],
"values": ["销售报表", 1, 2, 4, 2],
"fill": {"color": "#FF5722"}
})
chart.set_title({"name": "月度销售图表"})
chart.set_x_axis({"name": "月份"})
chart.set_y_axis({"name": "金额"})
worksheet.insert_chart("F2", chart)

workbook.close()

7.3 GoLang处理Excel

Go语言也有优秀的Excel处理库，最常用的是excelize。

GoLang选择建议：Go适合高性能服务、并发处理、企业级应用，性能优秀，部署简单，适合构建稳定的后端服务。

7.3.1 使用excelize

excelize是Go语言中最流行的Excel处理库，功能强大且性能优秀。

安装

go get github.com/xuri/excelize/v2

创建Excel文件

package main

import (
"fmt"

"github.com/xuri/excelize/v2"
)

func main() {
// 创建新工作簿
f := excelize.NewFile()

// 设置工作表名称
f.SetSheetName("Sheet1", "销售数据")

// 设置标题行
headers := []string{"产品", "数量", "单价", "金额"}
for i, header := range headers {
cell, _ := excelize.CoordinatesToCellName(i+1, 1)
f.SetCellValue("销售数据", cell, header)
}

// 添加数据行
data := [][]interface{}{
{"产品A", 100, 50},
{"产品B", 200, 80},
{"产品C", 150, 60},
{"产品D", 300, 45},
{"产品E", 250, 70},
}

for i, row := range data {
for j, value := range row {
cell, _ := excelize.CoordinatesToCellName(j+1, i+2)
f.SetCellValue("销售数据", cell, value)
}
}

// 使用公式计算金额
for i := 2; i <= len(data)+1; i++ {
cell, _ := excelize.CoordinatesToCellName(4, i)
formula := fmt.Sprintf("=B%d*C%d", i, i)
f.SetCellFormula("销售数据", cell, formula)
}

// 保存文件
if err := f.SaveAs("销售数据.xlsx"); err != nil {
fmt.Println(err)
}
}

设置样式

package main

import (
"fmt"

"github.com/xuri/excelize/v2"
)

func main() {
f := excelize.NewFile()
// ... 写入数据代码 ...

// 设置标题样式
headerStyle, _ := f.NewStyle(&excelize.Style{
Font: &excelize.Font{
Bold: true,
Color: "FFFFFFFF",
},
Fill: excelize.Fill{
Type: "pattern",
Color: []string{"#4CAF50"},
Pattern: 1,
},
Alignment: &excelize.Alignment{
Horizontal: "center",
},
})
f.SetCellStyle("销售数据", "A1", "D1", headerStyle)

// 设置数字格式
numberStyle, _ := f.NewStyle(&excelize.Style{
NumFmt: 34, // 千分位格式
})
f.SetCellStyle("销售数据", "C2", "D6", numberStyle)

// 添加边框
borderStyle, _ := f.NewStyle(&excelize.Style{
Border: []excelize.Border{
{Type: "left", Color: "000000", Style: 1},
{Type: "top", Color: "000000", Style: 1},
{Type: "bottom", Color: "000000", Style: 1},
{Type: "right", Color: "000000", Style: 1},
},
})
f.SetCellStyle("销售数据", "A1", "D6", borderStyle)

// 调整列宽
f.SetColWidth("销售数据", "A", "A", 12)
f.SetColWidth("销售数据", "B", "B", 10)
f.SetColWidth("销售数据", "C", "C", 12)
f.SetColWidth("销售数据", "D", "D", 15)

if err := f.SaveAs("销售数据.xlsx"); err != nil {
fmt.Println(err)
}
}

读取Excel文件

package main

import (
"fmt"
"log"

"github.com/xuri/excelize/v2"
)

func main() {
// 打开Excel文件
f, err := excelize.OpenFile("销售数据.xlsx")
if err != nil {
log.Fatal(err)
}
defer func() {
if err := f.Close(); err != nil {
fmt.Println(err)
}
}()

// 获取所有工作表
sheets := f.GetSheetList()
fmt.Println("工作表列表:", sheets)

// 读取数据
rows, err := f.GetRows("销售数据")
if err != nil {
log.Fatal(err)
}

fmt.Println("销售数据:")
for i, row := range rows {
for j, cell := range row {
fmt.Printf("[%d,%d]: %s\t", i, j, cell)
}
fmt.Println()
}

// 获取单元格值
cellValue, _ := f.GetCellValue("销售数据", "D2")
fmt.Printf("D2单元格的值: %s\n", cellValue)

// 获取公式
formula, _ := f.GetCellFormula("销售数据", "D2")
fmt.Printf("D2单元格的公式: %s\n", formula)
}

数据分析和统计

package main

import (
"fmt"
"log"
"sort"

"github.com/xuri/excelize/v2"
)

type SalesData struct {
Product string
Quantity int
Price float64
Amount float64
}

func main() {
f, err := excelize.OpenFile("销售数据.xlsx")
if err != nil {
log.Fatal(err)
}
defer f.Close()

// 读取数据到结构体切片
var sales []SalesData
rows, _ := f.GetRows("销售数据")

// 跳过标题行
for _, row := range rows[1:] {
if len(row) >= 4 {
data := SalesData{
Product: row[0],
Quantity: parseInt(row[1]),
Price: parseFloat(row[2]),
Amount: parseFloat(row[3]),
}
sales = append(sales, data)
}
}

// 计算统计信息
var totalAmount float64
var maxAmount float64 var minAmount float64 = sales[0].Amount

for _, s := range sales {
totalAmount += s.Amount
if s.Amount > maxAmount {
maxAmount = s.Amount
} if s.Amount < minAmount {
minAmount = s.Amount
} }

avgAmount := totalAmount / float64(len(sales))

fmt.Printf("总销售额: %.2f\n", totalAmount)
fmt.Printf("平均销售额: %.2f\n", avgAmount)
fmt.Printf("最大销售额: %.2f\n", maxAmount)
fmt.Printf("最小销售额: %.2f\n\n", minAmount)

// 按销售额排序
sort.Slice(sales, func(i, j int) bool {
return sales[i].Amount > sales[j].Amount
})

fmt.Println("销售额排行:")
for i, s := range sales {
fmt.Printf("%d. %s: %.2f\n", i+1, s.Product, s.Amount)
}
}

// 辅助函数
func parseInt(s string) int {
var n int
fmt.Sscanf(s, "%d", &n)
return n
}

func parseFloat(s string) float64 {
var f float64
fmt.Sscanf(s, "%f", &f)
return f
}

创建图表

package main

import (
"fmt"

"github.com/xuri/excelize/v2"
)

func main() {
f := excelize.NewFile()
f.SetSheetName("Sheet1", "数据")

// 准备数据
data := [][]interface{}{
{"月份", "销售额", "成本", "利润"},
{"1月", 100000, 60000, 40000},
{"2月", 120000, 70000, 50000},
{"3月", 150000, 85000, 65000},
{"4月", 180000, 100000, 80000},
{"5月", 200000, 110000, 90000},
}

// 写入数据
for i, row := range data {
for j, value := range row {
cell, _ := excelize.CoordinatesToCellName(j+1, i+1)
f.SetCellValue("数据", cell, value)
}
}

// 创建柱状图
if err := f.AddChart("数据", "F2", &excelize.Chart{
Type: excelize.Col,
Series: []excelize.ChartSeries{
{
Name: "数据!$B$1",
Categories: "数据!$A$2:$A$6",
Values: "数据!$B$2:$B$6",
Format: excelize.ChartFormat{
Fill: excelize.Fill{Color: []string{"#4CAF50"}},
},
},
{
Name: "数据!$C$1",
Categories: "数据!$A$2:$A$6",
Values: "数据!$C$2:$C$6",
Format: excelize.ChartFormat{
Fill: excelize.Fill{Color: []string{"#FF5722"}},
},
},
},
Format: excelize.ChartFormat{
Legend: excelize.ChartLegend{
Position: "bottom",
},
},
Title: []excelize.ChartTitle{{
Name: "月度销售数据",
}},
XAxis: excelize.ChartAxis{
Name: "月份",
},
YAxis: excelize.ChartAxis{
Name: "金额",
},
}); err != nil {
fmt.Println(err)
}

// 创建折线图
if err := f.AddChart("数据", "F20", &excelize.Chart{
Type: excelize.Line,
Series: []excelize.ChartSeries{
{
Name: "数据!$D$1",
Categories: "数据!$A$2:$A$6",
Values: "数据!$D$2:$D$6",
Format: excelize.ChartFormat{
Fill: excelize.Fill{Color: []string{"#2196F3"}},
},
Marker: excelize.ChartMarker{
Symbol: "circle",
Size: 8,
},
},
},
Title: []excelize.ChartTitle{{
Name: "利润趋势",
}},
XAxis: excelize.ChartAxis{
Name: "月份",
},
YAxis: excelize.ChartAxis{
Name: "利润",
},
}); err != nil {
fmt.Println(err)
}

// 保存文件
if err := f.SaveAs("图表示例.xlsx"); err != nil {
fmt.Println(err)
}
}

7.4 实践练习

让我们通过一个实际案例来练习编程处理Excel：

Python练习

  1. 创建一个包含销售数据的Excel文件（产品、数量、单价、金额）
  2. 使用openpyxl设置样式和条件格式
  3. 使用pandas读取数据并进行分析
  4. 筛选出销售额前3的产品
  5. 按产品类别进行分组统计
  6. 使用xlsxwriter创建带图表的月度报表

GoLang练习

  1. 使用excelize创建销售数据Excel文件
  2. 设置单元格样式和数字格式
  3. 读取数据并计算统计信息
  4. 按销售额进行排序
  5. 创建柱状图和折线图
  6. 将处理后的数据保存到新的Excel文件

7.5 本章小结

本章介绍了如何使用编程语言处理Excel文件，你现在应该能够：

选择建议总结：
  • Python：适合数据分析、机器学习、快速原型开发，生态丰富
  • GoLang：适合高性能服务、并发处理、企业级应用，性能优秀
  • 混合使用：可以用Python做数据分析，用Go做服务端处理

编程处理Excel是一个非常强大的技能，掌握后可以大大提高工作效率，实现自动化的数据处理和报表生成。建议多实践，探索更多高级功能！