自动调格式python_Python自动转换数据为Excel格式

时间：2018-12-12

概述：Excel 自动转换

Python自动转换数据为Excel格式，这是一种数据导出方式，要么存储于数据库，要么导出成指定的文件格式，今天这个Python例子，是将数据按照Excel的规则格式生成文件，也就是导出报表为Excel格式，生成报表可将基础数据做初步的统计整理，然后做统计分析图表，下面是核心文件代码：

import openpyxl

from openpyxl.styles import Font

from openpyxl.chart import BarChart, Series, Reference

wb = openpyxl.load_workbook('C:\\Users\\Administrator\\Desktop\\CustomerReact\\AssignSelectReact\\CustomerReact.xlsx')

wb2 = openpyxl.Workbook()

sheet2 = wb2.get_active_sheet()

sheet2['A1'] = '任务号'

sheet2['B1'] = '活动名称'

sheet2['C1'] = '坐席账号'

sheet2['D1'] = '电话一'

sheet2['E1'] = '电话一属性'

sheet2['F1'] = '是否接通'

sheet2['G1'] = '拨打次数'

sheet2['H1'] = '电话二'

sheet2['I1'] = '电话二属性'

sheet2['J1'] = '是否接通'

sheet2['K1'] = '拨打次数'

sheet2['L1'] = '电话三'

sheet2['M1'] = '电话三属性'

sheet2['N1'] = '是否接通'

sheet2['O1'] = '拨打次数'

#给表头设置字体

fontObj = Font(name='Segoe UI',size=9,bold=True)

for i in range(1,16):

sheet2.cell(row=1,column=i).font = fontObj

#固定首行

sheet2.freeze_panes = 'A2'

sheet = wb.get_sheet_by_name('sheet')

taskIdList = {}

for x in range(1,sheet.max_row+1):

for y in range(1,sheet.max_column+1):

cellObj = sheet.cell(row= x+1,column = y).value

if y == 1:

#如果任务id已经存在字典中，就不必打印其他行了

if cellObj in taskIdList:

#取得已经存在任务号的行号

oldRow = taskIdList[cellObj]

print(oldRow,x)

print(sheet.cell(row= x+1,column = 11).value)

#将电话的拨打状态和次数同步

if sheet.cell(row= x+1,column = 11).value == '电话一':

print('电话一')

sheet2.cell(row = oldRow,column =6).value = sheet.cell(row= x+1,column = 12).value

sheet2.cell(row = oldRow,column =7).value = sheet.cell(row= x+1,column = 13).value

elif sheet.cell(row= x+1,column = 11).value == '电话二':

print('电话一')

sheet2.cell(row = oldRow,column =10).value = sheet.cell(row= x+1,column = 12).value

sheet2.cell(row = oldRow,column =11).value = sheet.cell(row= x+1,column = 13).value

elif sheet.cell(row= x+1,column = 11).value == '电话三':

print('电话一')

sheet2.cell(row = oldRow,column =14).value = sheet.cell(row= x+1,column = 12).value

sheet2.cell(row = oldRow,column =15).value = sheet.cell(row= x+1,column = 13).value

#如果任务id已经存在字典中，就不必打印其他行了，用break能够跳出一层for循环

break

else:

#将任务号作为键，行号作为值，存入字典

taskIdList[sheet.cell(row = x+1,column = y).value] = x+1

#将第一列的值存入创建的单元格

sheet2.cell(row= x+1,column = y).value = cellObj

#将电话的拨打状态和次数同步

if sheet.cell(row= x+1,column = 11).value == '电话一':

sheet2.cell(row = x+1,column =6).value = sheet.cell(row= x+1,column = 12).value

sheet2.cell(row = x+1,column =7).value = sheet.cell(row= x+1,column = 13).value

elif sheet.cell(row= x+1,column = 11).value == '电话二':

sheet2.cell(row = x+1,column =10).value = sheet.cell(row= x+1,column = 12).value

sheet2.cell(row = x+1,column =11).value = sheet.cell(row= x+1,column = 13).value

elif sheet.cell(row= x+1,column = 11).value == '电话三':

sheet2.cell(row = x+1,column =14).value = sheet.cell(row= x+1,column = 12).value

sheet2.cell(row = x+1,column =15).value = sheet.cell(row= x+1,column = 13).value

elif y in (2,3,4,5):

sheet2.cell(row= x+1,column = y).value = cellObj

elif y == 6:

sheet2.cell(row = x+1,column =8).value = sheet.cell(row= x+1,column = y).value

elif y == 7:

sheet2.cell(row = x+1,column =9).value = sheet.cell(row= x+1,column = y).value

elif y == 8:

sheet2.cell(row = x+1,column =12).value = sheet.cell(row= x+1,column = y).value

elif y == 9:

sheet2.cell(row = x+1,column =13).value = sheet.cell(row= x+1,column = y).value

#print(cellObj,end=' ')

#print('/n')

#生成报表-可将基础数据做初步的统计整理，在另一张sheet，然后在这张sheet里做统计分析图表

chart1 = BarChart()

chart1.type = "col"

chart1.style = 10

chart1.title = "名单点选情况"

chart1.y_axis.title = '点选情况个数'

chart1.x_axis.title = '点选情况'

data = Reference(sheet2, min_col=7, min_row=2, max_row=sheet.max_row,max_col=7)

cats = Reference(sheet2, min_col=3, min_row=1, max_row=sheet.max_row, max_col=3)

chart1.add_data(data, titles_from_data=True)

chart1.set_categories(cats)

chart1.shape = 4

sheet2.add_chart(chart1, "A10")

wb2.save('C:\\Users\\Administrator\\Desktop\\CustomerReact\\AssignSelectReact\\CustomerReactV2.xlsx')