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()