1 '''

 2     #利用openpyxl向excel模板写入数据之mysql篇

 3 '''

 4 #写mysql的

 5 import xlwt

 6 import xlrd

 7 import openpyxl

 8 import pymysql

 9

10 #首先连接数据库

11 database = pymysql.connect('localhost','databaseusername','databasepassword','databasename',charset='utf8')

12 #初始化数据库指针

13 cursor = database.cursor()

14

15 #定义sql语句

16 sql = "SELECT company, COUNT(company), SUM(weight), SUM(weight * price) FROM tablename GROUP BY company;"

17 #执行sql语句

18 cursor.execute(sql)

19 #将结果保存下来

20 result = cursor.fetchall()

21 print(result) #打印看是否符合预期

22

23 #取出结构后开始遍历结果

24 for i in result:

25     if i[0] == "张三粮配":

26         a_che = i[1]

27         a_total_weight = i[2]

28         a_total_price = i[3]

29     if i[0] == "李四粮食":

30         b_che = i[1]

31         b_total_weight = i[2]

32         b_total_price = i[3]

33     if i[0] == "王五麦子":

34         c_che = i[1]

35         c_total_weight = i[2]

36         c_total_price = i[3]

37     if i[0] == "赵六麦子专营":

38         d_che = i[1]

39         d_total_weight = i[2]

40         d_total_price = i[3]

41

42 #导入模板文件

43 tem_excel = openpyxl.load_workbook('路径/统计表_openpyxl.xlsx')

44 tem_sheet = tem_excel['Sheet1']

45

46 #开始向单元格写入数据

47 #先写张三的，张三的在第三行第二列到第四列

48 tem_sheet['B3'] = a_che

49 tem_sheet['C3'] = a_total_weight

50 tem_sheet['D3'] = a_total_price

51 #再写李四，李四的在第四行第二到第四列

52 tem_sheet['B4'] = b_che

53 tem_sheet['C4'] = b_total_weight

54 tem_sheet['D4'] = b_total_price

55 #再写王五的，王五的在第五行第二到第四列

56 tem_sheet['B5'] = c_che

57 tem_sheet['C5'] = c_total_weight

58 tem_sheet['D5'] = c_total_price

59 #最后些赵六的，赵六的在第六行第二到第四列

60 tem_sheet['B6'] = d_che

61 tem_sheet['C6'] = d_total_weight

62 tem_sheet['D6'] = d_total_price

63

64 #最后将工作簿另存

65 tem_excel.save('路径/2020-11-04-openpyxl-mysql.xlsx')