《大数据应用技术基础》课件 项目三 处理电子表格

项目三处理电子表格目录任务一：新建电子表格任务二：设置单元格格式任务三：添加合计行任务四：拆分保存工作表任务五：批量生成柱形图表任务六：添加饼形图表任务七：批量插入图片任务八：添加新列并进行计算任务一：新建电子表格【任务分析】

现有一份“商铺用电统计表”，编程在“D:/电子表格”目录下创建“商铺用电统计表.xlsx”，并把“商铺用电统计表”的内容写入表格中，工作表命名为“商铺用电统计表”。表3-1商铺用电统计表序号商铺号本月用电量(度)上月用电量(度)同比增长率用电类别1商业街1号32003000+6.67%商业2商业街2号54005000+8.00%商业3商业街3号23002100+9.52%商业4商业街4号88008000+10.00%娱乐5商业街5号36003400+5.88%商业任务一：新建电子表格【任务实施】1.启动PyCharm，执行File/NewPreject...创建Python项目，在项目的main.py文件中，输入程序代码，逐步实现新建电子表格的功能，如图3-1所示。图3-1输入程序代码任务一：新建电子表格参考代码：fromopenpyxlimportWorkbook

#创建一个工作簿

wb=Workbook()

#激活工作表

ws=wb.active

#给工作表命名

ws.title="商铺用电统计表"

#编写表头

ws.append(["序号","商铺号","本月用电量(度)","上月用电量(度)","同比增长率","用电类别"])

#编写表格内容

data=[

[1,"商业街1号",3200,3000,"+6.67%","商业"],

[2,"商业街2号",5400,5000,"+8.00%","商业"],

[3,"商业街3号",2300,2100,"+9.52%","商业"],

[4,"商业街4号",8800,8000,"+10.00%","娱乐"],

[5,"商业街5号",3600,3400,"+5.88%","商业"]

]

#将数据写入工作表

forrowindata:

ws.append(row)

#保存工作簿到指定的路径

wb.save("D:/电子表格/商铺用电统计表.xlsx")

print("Excel文件已成功创建并写入数据！")任务一：新建电子表格2.程序运行后，打开生成的“商铺用电统计表.xlsx”，如图3-2所示。图3-2打开生成的“商铺用电统计表.xlsx”任务二：设置单元格格式【任务分析】现有一份“公交车乘客数统计表”，编写一个python程序实现以下功能：（1）在“D:/电子表格”目录下创建“公交车乘客数统计表.xlsx”（2）并把“公交车乘客数统计表”的内容写入表格中，工作表命名为“公交车乘客数统计表”。（3）检查每行“星期一至星期日”内的数据，同行最大值的单元格设置黄色底纹，字体加粗。序号公交线路星期一星期二星期三星期四星期五星期六星期日11路12001300125014001500110090022路80085087086090075060033路160016501700168017501300100044路95097094098099080070055路110011501200116012501000800表3-2“公交车乘客数统计表”的内容任务二：设置单元格格式【任务实施】1.启动PyCharm，执行File/NewPreject...创建Python项目，在项目的main.py文件中，输入程序代码，逐步按任务分析完成设置单元格格式的功能，如图3-3所示。图3-3输入程序代码任务二：设置单元格格式参考代码：#导入openpyxl库

importopenpyxl

fromopenpyxl.stylesimportFont,PatternFill

#创建工作簿和工作表

wb=openpyxl.Workbook()

ws=wb.active

ws.title="公交车乘客数统计表"

#编写表格数据

ws['A1']='序号'

ws['B1']='公交线路'

ws['C1']='星期一'

ws['D1']='星期二'

ws['E1']='星期三'

ws['F1']='星期四'

ws['G1']='星期五'

ws['H1']='星期六'

ws['I1']='星期日'

data=[

[1,'1路',1200,1300,1250,1400,1500,1100,900],

[2,'2路',800,950,870,860,900,750,600],

[3,'3路',1920,1650,1700,1680,1750,1300,1000],任务二：设置单元格格式[4,'4路',950,970,940,980,990,1200,700],

[5,'5路',1100,1150,1200,1160,1050,1000,1324]

]

forrowindata:

ws.append(row)

#检查每行的最大值并设置格式

forrowinrange(2,7):

max_value=max(ws.cell(row=row,column=3).value,ws.cell(row=row,column=4).value,ws.cell(row=row,column=5).value,ws.cell(row=row,column=6).value,ws.cell(row=row,column=7).value,ws.cell(row=row,column=8).value,ws.cell(row=row,column=9).value)

forcolinrange(3,10):

ifws.cell(row=row,column=col).value==max_value:

ws.cell(row=row,column=col).font=Font(bold=True)

ws.cell(row=row,column=col).fill=PatternFill(start_color='FFFF00',end_color='FFFF00',fill_type='solid')

#保存文件

wb.save('D:/电子表格/公交车乘客数统计表.xlsx')任务二：设置单元格格式2.程序运行后，打开“D:/电子表格/公交车乘客数统计表.xlsx”的结果如图3-4所示。图3-4打开“D:/电子表格/公交车乘客数统计表.xlsx”任务三：添加合计行【任务分析】

现在一个“到店人数.xlsx”文件，内有两个工作表，记录了一周的各分店到店人数数据，如图3-5所示。编写一个python程序实现以下功能：（1）在每个工作表的最后一行，增加一行，序号写“合计”。（2）计算各列的第2行以下的数据之后，添加到合计行对应列的单元格中。图3-5现在一个“到店人数.xlsx”文件任务三：添加合计行【任务实施】1.启动PyCharm，执行File/NewPreject...创建Python项目，在项目的main.py文件中，输入程序代码逐步按任务分析完成添加合计行的功能，如图3-6所示。图3-6输入程序代码逐步按任务分析完成添加合计行的功能任务三：添加合计行参考代码：importopenpyxl

#定义文件路径

file_path="D:/电子表格/到店人数.xlsx"

#加载工作簿

workbook=openpyxl.load_workbook(file_path)

#遍历所有工作表

forsheetinworkbook.worksheets:

#在最后一行添加"合计"

total_row_index=sheet.max_row+1

sheet.cell(row=total_row_index,column=1).value="合计"

#计算第2行以下各列的数据之和

forcolinrange(3,sheet.max_column+1):#因序号和店名在第一、二列，从第三列开始计算

sum_val=0#初始化列的总和

forrowinrange(2,sheet.max_row):#从第二行开始计算，跳过标题行

cell_value=sheet.cell(row=row,column=col).value

#确保单元格中的值是数字类型

ifisinstance(cell_value,(int,float)):

sum_val+=cell_value

#将计算出的总和填入最后一行的对应列中

sheet.cell(row=total_row_index,column=col).value=sum_val

#保存工作簿

workbook.save(file_path)

print("工作簿已更新，添加了合计行。")任务三：添加合计行2.程序运行后，打开“到店人数xlsx”察看运行结果，可看到各表的最后一行已添加了合计行，如图3-7所示。图3-7打开“到店人数xlsx”察看运行结果任务四：拆分保存工作表【任务分析】

现有一个“部门出货流水账.xlsx”文件，内有各部门的所有记录，如图3-8所示。要求编写一个python程序实现以下功能：（1）以部门为单位，把同一部门的记录分拆成一个独立的工作簿中。（2）例如，把所有“食品部”的记录分拆到“食品部.xlsx”中，文件名命名为“食品部.xlsx”，把所有“家居部”的记录分拆到“家居部.xlsx”中，文件名命名为“家居部.xlsx”，如此类推。（3）“部门出货流水账.xlsx”文件现在于“D:\电子表格”目录中。（4）“部门出货流水账.xlsx”的数据表结构和部分数据。任务四：拆分保存工作表表3-3“部门出货流水账.xlsx”的数据表结构和部分数据序号部门名称产品类型出货时间出货数量单价总金额1食品部饮料2023/11/15002.5元1250元2家居部家具2023/11/220800元16000元3服装部衣服2023/11/3100150元15000元4食品部糖果2023/11/420000.5元1000元5家居部灯具2023/11/550400元20000元6服装部鞋子2023/11/680200元16000元7食品部水果2023/11/78003元2400元8家居部家电2023/11/8103000元30000元9服装部皮包2023/11/950500元25000元任务四：拆分保存工作表图3-8现有一个“部门出货流水账.xlsx”文件任务四：拆分保存工作表【任务实施】1.启动PyCharm，执行File/NewPreject...创建Python项目，在项目的main.py文件中，输入程序代码，逐步按任务分析完成拆分保存工作表的功能，如图3-9所示。图3-9输入程序代码任务四：拆分保存工作表参考代码：importpandasaspd

importos

#定义原始文件路径

original_file_path="D:/电子表格/部门出货流水账.xlsx"

#使用pandas读取Excel文件

df=pd.read_excel(original_file_path)

#将出货时间列的数据转换为日期类型，只保留年月日

df['出货时间']=pd.to_datetime(df['出货时间']).dt.date

#获取部门列的唯一值列表，假设部门列的名称为"部门名称"

unique_departments=df['部门名称'].unique()

#为每个部门创建一个新的Excel文件

fordepartmentinunique_departments:

#筛选出当前部门的所有记录

department_df=df[df['部门名称']==department]

#定义新的Excel文件名

new_file_path=os.path.join("D:/电子表格",f"{department}.xlsx")

#将当前部门的DataFrame保存到新的Excel文件

department_df.to_excel(new_file_path,index=False)

print("所有部门的记录已分拆到独立的工作簿中。")任务四：拆分保存工作表2.程序运行后，在“D:/电子表格”目录中，已增加了“食品部.xlsx”、“家居部.xlsx”、“服装部.xlsx”等多个工作薄文件，如图3-10所示。图3-10“D:/电子表格”目录任务四：拆分保存工作表3.抽查其中的“食品部.xlsx”文件，打开“食品部.xlsx”文件，查看记录看到全部食品的记录，如图3-11所示。图3-11查看记录看到全部食品的记录任务五：批量生成柱形图表【任务分析】现有一份“各类产品出货表.xlsx”，如图3-12所示。编写一个python程序实现以下功能：（1）在“D:/电子表格”目录下有“各类产品出货表.xlsx”。（2）为“服装产品”、“家居产品”、“食物产品”等三个工作表添加柱形图表。（3）在添加柱形图表过程中，为工作表的数据展示服务，不能删除或修改原工作表数据。表3-4“各类产品出货表.xlsx”的结构和内容图3-12现有一份“各类产品出货表.xlsx”任务五：批量生成柱形图表【任务实施】1.启动PyCharm，执行File/NewPreject...创建Python项目，在项目的main.py文件中，输入程序代码，逐步按任务分析完成批量生成柱形图表的功能，如图3-13所示。图3-13输入程序代码任务五：批量生成柱形图表参考代码：importos

importopenpyxl

fromopenpyxl.chartimportBarChart,Reference

#创建目录和文件

output_folder='D:/电子表格'

ifnotos.path.exists(output_folder):

os.makedirs(output_folder)

output_file=os.path.join(output_folder,'各类产品出货表.xlsx')

#打开现有的工作簿

wb=openpyxl.load_workbook(output_file)

#获取或创建工作表

defget_or_create_worksheet(sheet_name):

ifsheet_nameinwb.sheetnames:

returnwb[sheet_name]

else:

returnwb.create_sheet(sheet_name)

#添加柱形图表

defadd_bar_chart(worksheet):

chart=BarChart()

data=Reference(worksheet,min_col=2,min_row=1,max_col=worksheet.max_column,max_row=worksheet.max_row)

categories=Reference(worksheet,min_col=1,min_row=2,max_row=worksheet.max_row)

chart.add_data(data,titles_from_data=True)

chart.set_categories(categories)

worksheet.add_chart(chart,"E5")

#为每个工作表添加柱形图表

forsheet_namein["服装产品","家居产品","食物产品"]:

ws=get_or_create_worksheet(sheet_name)

add_bar_chart(ws)

#保存工作簿

wb.save(output_file)任务五：批量生成柱形图表2.程序运行后，打开“各类产品出货表.xlsx”，查看到各表添加柱形图表的情况，如图3-14所示。图3-14打开“各类产品出货表.xlsx”任务六：添加饼形图表【任务分析】现有一份“业绩比例数据表.xlsx”，编写一个python程序实现以下功能：（1）在“D:/电子表格”目录下创建“业绩比例数据表.xlsx”。（2）根据工作表中的部门数据，添加饼形图表。（3）完成后直接打开“业绩比例数据表.xlsx”文件。表3-4“各类产品出货表.xlsx”的结构和内容序号部门业绩比例(%)1销售部352研发部253运营部204市场部155财务部5任务六：添加饼形图表【任务实施】1.启动PyCharm，执行File/NewPreject...创建Python项目，在项目的main.py文件中，输入程序代码，逐步按任务分析完成添加饼形图表的功能，如图3-15所示。图3-15输入程序代码任务六：添加饼形图表参考代码：importopenpyxl

fromopenpyxl.chartimportPieChart,Reference

importos

#创建一个新的Excel工作簿

workbook=openpyxl.Workbook()

sheet=workbook.active

#向工作表中写入部门业绩比例数据

data=[

['部门','业绩比例(%)'],

['销售部',35],

['研发部',25],

['运营部',20],

['市场部',15],

['财务部',5]

]

forrowindata:

sheet.append(row)

#添加饼形图

pie=PieChart()

labels=Reference(sheet,min_col=1,min_row=2,max_row=6)

data=Reference(sheet,min_col=2,min_row=1,max_row=6)

pie.add_data(data,titles_from_data=True)

pie.set_categories(labels)

pie.title="部门业绩比例"

#将图表添加到工作表

sheet.add_chart(pie,"D10")

#保存工作簿

file_path='D:/电子表格/业绩比例数据表.xlsx'

workbook.save(file_path)

#打开Excel文件

os.system(f'startexcel{file_path}')任务六：添加饼形图表2.程序运行后，打开“业绩比例数据表.xlsx”，查看到表中成功添加了饼形图表的情况，如图3-16所示。图3-16打开“业绩比例数据表.xlsx”任务七：批量插入图片图3-17一份“画作品档案.xlsx”任务七：批量插入图片图3-18png图片文件任务七：批量插入图片【任务实施】1.启动PyCharm，执行File/NewPreject...创建Python项目，在项目的main.py文件中，输入程序代码，逐步按任务分析完成批量插入图片的功能，如图3-19所示。图3-19输入程序代码任务七：批量插入图片参考代码：importos

fromopenpyxlimportload_workbook

fromopenpyxl.drawing.imageimportImage

fromPILimportImageasPILImage

#打开Excel文件

file_path=r"D:/电子表格/画作品档案.xlsx"

wb=load_workbook(file_path)

ws=wb.active

#图片文件夹路径

image_folder=r"D:\画作品图片"

#遍历图片文件夹中的图片文件

image_files=[fileforfileinos.listdir(image_folder)iffile.endswith(".png")]

#在Excel中插入图片

fori,image_fileinenumerate(image_files):

img=PILImage.open(os.path.join(image_folder,image_file))

img=img.resize((200,160))#调整图片大小

img_path=os.path.join(image_folder,image_file)

img.save(img_path)#覆盖原始图片，调整大小后保存

img=Image(img_path)

cell=ws.cell(row=i+2,column=2)#从第2行开始，第2列

ws.add_image(img,f'B{cell.row}')#将图片插入到指定单元格的位置

#保存修改后的Excel文件

wb.save(file_path)经验分享：在执行程序时，因为要写入“D:/电子表格/画作品档案.xlsx”文件，必须要保证该文件关闭，若该文件被其他软件打开，则会导致写入失败，程序执行会报错。任务七：批量插入图片2.程序运行后，打开“画作品档案.xlsx”，查看插入图片的效果，如图3-20所示。图3-20打开“画作品档案.xlsx”任务八：添加新列并进行计算【任务分析】现有一份开发业务登记表的电子表格文件“开发业务登记表.xlsx”，如图3-21所示。请使用Python编程，为表格添加一列“预计利润”，完成后另存为“开发业务登记表(预计利润).xlsx。（1）在最右添加一列“预计利润”，并按要求计算出各项目的利润。（2）“技术难度等级”为“极高”，按“项目投资(万元)”的70%计算预计利润。（3）“技术难度等级”为“高”，按“项目投资(万元)”的50%计算预计利润。（4）“技术难度等级”为“中“，按“项目投资(万元)”的30%计算预计利润。（5）现“技术难度等级”在F列。（6）现“项目投资(万元)”在E列。任务八：添加新列并进行计算图3-21电子表格文件“开发业务登记表.xlsx”任务八：添加新列并进行计算【任务实施】1.启动PyCharm，执行File/NewPreject...创建Python项目，在项目的main.py文件中，输入程序代码，逐步按任务分析完成添加新列并进行计算的功能，如图3-22所示。图3-22输入程序代码任务八：添加新列并进行计算代码参考：importopenpyxl

#定义文件路径

original_file_path="D:/电子表格/开发业务登记表.xlsx"

new_file_path="D:/电子表格/开发业务登记表(预计利润).xlsx"

#加载现有的Excel工作簿

workbook=openpyxl.load_workbook(original_file