excel数据的读写
读取工作表
- 使用xlrd,和xlwt两个库进行excel的读写操作
- 先打开工作簿,然后获取工作表,最后获取工作表中的数据
import xlrd
class Get_Excel(object):
def get_data(self):
excelbook= xlrd.open_workbook(f"G:\excel操作\薪资表.xls")
sh = excelbook.sheets()[0]
cellvalue=sh.cell_value(rowx=4,colx=1)
print(cellvalue)
for i in range(sh.nrows):
print(sh.row(i),"\n")
r=Get_Excel()
r.get_data()
写入excel数据
import xlwt
class Write_Excel:
def write_data(self):
newwb = xlwt.Workbook()
worksheet = newwb.add_sheet("A组薪资表")
stars = ["职工号", "姓名", "性别", "年龄", "所属部门", "职工类型", "基本工资", "事假天数", "病假天数"]
for i in range(len(stars)):
worksheet.write(0, i, stars[i])
newwb.save(r"G:\excel操作\2019年某公司薪资表.xls")
r = Write_Excel()
r.write_data()
复制工作簿
- 将A文件中的工作簿复制到B文件中,并更改工作簿名称需注意以下几个方面:
- 在使用xlrd读取工作簿使用formatting_info = True参数保证格式不会变化
- 使用xlutils表格工具进行复制
import xlrd
from xlutils.copy import copy
wookbook = xlrd.open_workbook(r"G:\excel操作\薪资表.xls", formatting_info=True)
sheet = wookbook.sheet_by_index(0)
new_workbook = copy(wookbook)
new_sheet = new_workbook.get_sheet(0)
new_workbook.save(r"G:\excel操作\我的薪资表.xls")
设置行高和列宽
- 在工作表中设置行高和列宽,列宽中256是一个衡量单位,行高中20是一个衡量单位
new_sheet.col(1).width = 256 * 60
new_sheet.row(1).height_mismatch = True
new_sheet.row(1).height = 20 * 40
XFStyle风格样式
XFStyle包含font(字体)、Borders(边界)、Alignment(对齐)、Pattern(模式)
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = "宋体"
font.bold = False
font.height = 20 * 20
font.colour_index = 12
style.font = font
borders = xlwt.Borders()
borders.top = 2
borders.bottom = 2
borders.left = 2
borders.right = 2
style.borders = borders
alignment = xlwt.Alignment()
alignment.vert = xlwt.Alignment.VERT_TOP
alignment.horz = xlwt.Alignment.HORZ_CENTER
style.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 3
style.pattern = pattern
合并单元格
- 合并单元格调用write_merge调用单元格的起始位置和结束位置
- sheetwork.write_merge(x,y,z,m,“单元格文本”,style)
- x:起始位置合并的行,z:起始位置合并的列,y:结束位置合并的行,m:结束位置合并的列
example
随机生成多条商品信息数据,并把数据存入到excel中
分析过程
- 1.生成多条数据存到excel中首先需要创建工作簿
- 2.创建工作表
- 3.使用字典和random函数生成数据
- 4.把生成的数据保存在excel中
- 5.设置表格风格进行数据的更形象的展示-XFStyle
import xlwt
import random
class Commodity(object):
def get_data(self):
goods = {}
goods['商品id'] = ["品名", "数量", "单位", "单价", "总价"]
for i in range(250):
pinming = random.choice(["鸡", "鸭", "鱼", "螃蟹", "虾"])
shuliang = random.randint(1, 5)
danwei = random.choice(["包", "千克", "箱"])
danjia = random.randint(10, 100)
lists = [pinming, shuliang, danwei, danjia]
goods[random.randint(10000, 99999)] = lists
return goods
def style0(self):
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = "黑体"
font.bold = True
font.height = 20 * 20
style.font = font
borders = xlwt.Borders()
borders.top = 1
borders.bottom = 1
borders.left = 1
borders.right = 1
style.borders = borders
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 44
style.pattern
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
style.alignment = alignment
return style
def style1(self):
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = "宋体"
font.height = 20 * 18
style.font = font
borders = xlwt.Borders()
borders.top = 1
borders.bottom = 1
borders.left = 1
borders.right = 1
style.borders = borders
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 22
style.pattern
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
style.alignment = alignment
return style
def style2(self):
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = "宋体"
font.height = 20 * 18
style.font = font
borders = xlwt.Borders()
borders.top = 1
borders.bottom = 1
borders.left = 1
borders.right = 1
style.borders = borders
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
style.alignment = alignment
return style
def run(self):
goods = self.get_data()
style0 = self.style0()
style1 = self.style1()
style2 = self.style2()
i = 0
for key, value in goods.items():
worksheet.col(i).width = 256 * 20
if i == 0:
worksheet.write(i, 0, key, style0)
elif i > 0 and i % 2 == 1:
worksheet.write(i, 0, key, style1)
else:
worksheet.write(i, 0, key, style2)
for j in range(len(value)):
if i == 0:
worksheet.write(i, j + 1, value[j], style0)
elif i > 0 and i % 2 == 1:
worksheet.write(i, j + 1, value[j], style1)
else:
worksheet.write(i, j + 1, value[j], style2)
i += 1
m = 0
for key, value in goods.items():
if m > 0:
if m % 2 == 0:
worksheet.write(m, len(value) + 1, value[1] * value[3], style2)
else:
worksheet.write(m, len(value) + 1, value[1] * value[3], style1)
m += 1
workbook.save(r"G:\零食售卖清单.xls")
if __name__ == '__main__':
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet("零食售卖清单")
res = Commodity()
res.run()