- 写入数据到excel的操作
# 导入
import xlsxwriter
# 创建一个工作簿并添加一个工作表
workbook = xlsxwriter.Workbook("demo.xlsx")
# 添加sheet
worksheet1 = workbook.add_worksheet("sheet1")
worksheet2 = workbook.add_worksheet("sheet2")
# 设置列宽
worksheet1.set_column("A:A", 20)
# 设置格式
bold = workbook.add_format({"bold": True})
# 设置单元格的值
worksheet1.write("A1", "张三")
worksheet1.write("A2", "李四")
worksheet2.write("A1", "王五")
worksheet2.write("A2", "赵六")
# 写一些数字,用行列标识
worksheet1.write(2, 0, 123)
worksheet2.write(2, 0, 123.456, bold)
# 插入图片
# worksheet1.insert_image("A5", "1.png")
# 关闭文件流
workbook.close()
这样我们得到了包含两个sheet的excel文件demo.xlsx
- 读取excel操作
# 导入
import xlrd
# 读取数据文件
workbook = xlrd.open_workbook(r'demo.xlsx')
# 打印Sheet信息
print(workbook.sheet_names())
# ['sheet1', 'sheet2']
# 根据下标获取表单
print(workbook.sheet_names()[1])
# 根据索引或名称获取数据的名称、行或列
index = workbook.sheet_by_index(0)# 根据索引或者行数列数和名称
print('索引名称:'+str(index.name)+' 索引的行数'+ str(index.nrows)+' 索引的列数'+str(index.ncols))
# 索引名称:sheet1 索引的行数3 索引的列数1
# 同样根据表单的名称也能拿到Sheet对象
index2 = workbook.sheet_by_name('sheet1')
print('索引名称:'+str(index2.name)+' 索引的行数'+ str(index2.nrows)+' 索引的列数'+str(index2.ncols))
获取整行整列的内容
# 首先要拿到表单的对象,通过上面说的两种方法:
sheet1= workbook.sheet_by_name('sheet1')
# 获取了以后:
row_values = sheet1.row_values(1)
print('第2行的内容:'+str(row_values))
col_values = sheet1.col_values(0)
print('第1列的内容'+str(col_values))
得到下面内容
第2行的内容:['李四']
第1列的内容['张三', '李四', 123.0]
- 合并
import xlrd
import xlsxwriter
source_xls = ["1.xlsx", "2.xlsx"]
target_xls = "3.xlsx"
# 读取数据
data = []
for i in source_xls:
wb = xlrd.open_workbook(i)
for sheet in wb.sheets():
for rownum in range(sheet.nrows):
data.append(sheet.row_values(rownum))
print(data)
# 写入数据
workbook = xlsxwriter.Workbook(target_xls)
worksheet = workbook.add_worksheet()
font = workbook.add_format({"font_size":14})
for i in range(len(data)):
for j in range(len(data[i])):
worksheet.write(i, j, data[i][j], font)
# 关闭文件流
workbook.close()
表1,表2的所有内容合并到表3得到
[['jenne', '244'], ['lisa', '23'], ['张三', '23'], ['李四', '34'], ['王五', '87'], ['赵六', '76']]
网友评论