Python(应用) — Excel操作(二)

Excel案例一

1. 资源

Excel基本操作：Python(应用) — Excel操作(一)
资源链接： automate_online-materials

2. 效果图

• 2.1 原始文档censuspopdata.xlsx
  

• 2.2 新生成的文档new_censuspopdata.xlsx
  

3. 代码实现

#! /usr/local/bin/python3
# -*- coding: utf-8 -*-
# 文件名 

'''
Author: elson
Desc: 统计censuspopdata.xlsx表中的同一个州的投票县的个数，和同一个县的投票总人数
'''
import os
import pprint
import openpyxl
import sys

from openpyxl.styles import Font
from case_demo import censuspopdata

def readOldExcel():
    # 1.打开数据表
    workbook = openpyxl.load_workbook('./data/censuspopdata.xlsx')
    print(workbook)

    print('worksheet name =', workbook.sheetnames)
    # 2.打开对应的表
    sheet = workbook['Population by Census Tract']
    print(sheet)

    return None
    countyData = {}

    # 3.读取数据表中的state、county、pop、tracts列的数据
    for row in range(2, sheet.max_row + 1):
        state = sheet['B' + str(row)].value
        county = sheet['C' + str(row)].value
        pop = sheet['D' + str(row)].value
        # print(state, county, pop)
        countyData.setdefault(state, {})
        countyData[state].setdefault(county, {"tracts": 0, 'pop': 0})

        countyData[state][county]["tracts"] += 1
        countyData[state][county]["pop"] += int(pop)
        print('__row__ = ', row)

    return countyData


def savePyFile(countyData):
    with open('./censuspopdata.py', 'w') as f:
        #pprint.pformat() 输出格式化
        f.write('allData = '+ pprint.pformat(countyData))

def createNewExcel():
    workbook = openpyxl.Workbook()
    print(workbook.sheetnames)
    workbook.remove(workbook[workbook.sheetnames[0]])
    print('remove sheet: ', workbook.sheetnames)
    sheet = workbook[workbook.sheetnames[0]] if workbook.sheetnames else workbook.create_sheet(index=0)
    print('create sheet: ', workbook.sheetnames)
    sheet.title = 'censuspopdata'
    print('rename sheet: ', workbook.sheetnames)
    #设置表格的列宽
    sheet.column_dimensions['B'].width = 30
    sheet.row_dimensions[1].height = 50

    print('_____ write start _____')
    writeData2NewExcel(sheet)
    print('_____ write end _____')
    workbook.save('./data/new_censuspopdata.xlsx')
    print('_____ end _____')

def writeData2NewExcel(sheet):
    font = Font(sz=18, bold=True, color='00ff0000')
    # 创建表头
    items = ['state', 'county', 'pop', 'tracts']
    for index, item in enumerate(items):
        cell = sheet.cell(row=1, column=index + 1)
        cell.font = font
        cell.value = item

    row_num = 2
    #添加表数据
    for (key, value) in censuspopdata.allData.items():
        # print(key, value)
        for (key1, value1) in value.items():
            # print(key, value['pop'], value['tracts'])
            sheet.cell(row=row_num, column=1).value = key
            sheet.cell(row=row_num, column=2).value = key1
            sheet.cell(row=row_num, column=3).value = value1['pop']
            sheet.cell(row=row_num, column=4).value = value1['tracts']
            row_num+=1

if __name__ == '__main__':
    countyData = readOldExcel()
    savePyFile(countyData)
    #print(pprint.pformat(censuspopdata.allData))
    createNewExcel()