python编程快速上手——让繁琐工作自动化第12章实践项目

12.13.1乘法表

def chengfabiao(num) :
    import openpyxl
    from openpyxl.styles import Font
    wb = openpyxl.Workbook()
    wblist = wb.sheetnames
    sheet = wb[wblist[0]]

    fontobj = Font(bold=True)

    for i in range(2,num + 2) :
        sheet.cell(row = 1, column = i).font = fontobj
        sheet.cell(row = 1, column = i).value = i-1
        sheet.cell(row = i, column = 1).font = fontobj
        sheet.cell(row = i, column = 1).value = i -1
    for i in range(2,num + 2) :
        for j in range(2,num + 2) :
            sheet.cell(row = i, column = j).value = (i-1) * (j-1)

    wb.save(r'c:\python\chengfabiao.xlsx')

chengfabiao(9)

12.13.2 空行插入程序

def charukongh(n,m,filename) :
    import openpyxl, os
    from openpyxl.styles import Font
    
    wb = openpyxl.load_workbook(filename)
    sheets = wb.sheetnames  #取得表名列表
    sheet = wb[sheets[0]]  #取得第一张表

    sheet.insert_rows(n,m) # 在第n行前插入m行
    wb.save(r'c:\python\1.xlsx') # 注意r,定义原义字符串

charukongh(2,3,r'c:\python\1.xlsx') # 注意r,定义原义字符串

12.13.3 电子表格单元格翻转程序

def fanzhuan(filename) :
    import openpyxl, os
    from openpyxl.styles import Font

    print(filename)
    
    wb = openpyxl.load_workbook(filename)
    sheets = wb.sheetnames  #取得表名列表
    sheet = wb[sheets[0]]  #取得第一张表

    rows = sheet.max_row
    cols = sheet.max_column
    mydata = [[],[]]

    for i in range(1,rows+1) :
        mydata[0].append(sheet.cell(row=i,column=1).value)
        mydata[1].append(sheet.cell(row=i,column=2).value)

    wbnew = openpyxl.Workbook()
    sheetnew = wbnew['Sheet']

    for i in range(1,rows+1):
        sheetnew.cell(row=1,column=i).value = mydata[0][i-1]
        sheetnew.cell(row=2,column=i).value = mydata[1][i-1]

    wbnew.save(r'c:\python\new.xlsx') # 注意r,定义原义字符串

fanzhuan(r'c:\python\1.xlsx') # 注意r,定义原义字符串

12.13.4 文本文件到电子表格

#! python3
import openpyxl, os

txtfile = open('c:\\python\\1\\1.txt','r',encoding='utf-8') #注意文件夹的表示方式

txtlines = txtfile.readlines()

wb = openpyxl.Workbook()
sheet = wb['Sheet']

for i in range(len(txtlines)):
    sheet.cell(row = 1, column = i+1).value = txtlines[i]

wb.save(r'c:\python\1\1.xlsx')

12.13.5 电子表格到文本文件

import openpyxl, os
os.chdir('c:\\python\\1')
wb = openpyxl.load_workbook('1.xlsx')  # 打开工作簿
sheets = wb.sheetnames #取得工作簿的表名列表
sheet = wb[sheets[0]]  # 取得工作簿的第一个表

txt1 = open('txt1.txt','w',encoding='utf-8')  #以只读方式打开文件
txt2 = open('txt2.txt','w',encoding='utf-8')

rows = sheet.max_row  # 取得最大行数

for i in range(1,rows+1) :
    cell1 = sheet.cell(row = i,column = 1).value
    cell2 = sheet.cell(row = i,column = 2).value

    # 由于write()方法只能写入字符串，因此，如果表格中int或float类型的数据
    # 需要先转换成字符串
    if type(cell1) == type(1) or type(cell1) == type(1.23) :
        cell1 = str(cell1)
    if type(cell2) == type(1) or type(cell2) == type(1.23) :
        cell2 = str(cell2)
    
    txt1.write(cell1)
    txt2.write(cell2)

txt1.close()
txt2.close()