CSV文件的读写
基本格式:
import csv
headers = []
rows = []
dict_rows = {}
with open('test1.csv', 'w', newline='') as w:
w_csv = csv.writer(w)
w_csv.writerow(headers)
w_csv.writerows(rows)
with open('test2.csv', 'w', newline='') as w:
w_csv = csv.DictWriter(w, ['字典键1','键2','...'])
w_csv.writeheader()
w_csv.writerows(dict_rows)
with open('text1.csv') as r:
r_csv = csv.DictReader(r)
for row in r_csv:
print(row.get('字典键1'), row.get('字典键2'), row.get('...'))
CSV实例:
import csv
headers = [
'姓名','性别','年龄'
]
rows = [
['a','男','18'],
['b','女','19'],
['c','男','20']
]
with open('test.csv', 'w', newline='') as w:
w_csv = csv.writer(w)
w_csv.writerow(headers)
w_csv.writerows(rows)
Excel表格的读写
使用xlwt
和xlrd
基本格式:
import xlwt
workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet('sheet1')
work.sheet.write(0,0,'内容')
workbook.save('xxx.xls')
import xlrd
workbook = xlrd.open_workbook('excel.xls')
table = workbook.sheet_by_name('sheet1')
row_count = table.nrows
col_count = table.ncols
row_list = table.row_values(行号)
col_list = table.col_values(列号)
val = table.cell(行号,列号).value
使用xlwt
写入Excel实例:
import xlwt
workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet('sheet1')
for i in range(1,9+1):
for j in range(1, i+1):
worksheet.write(i-1,j-1,"%d * %d = %d" % (j,i,i*j))
workbook.save("九九乘法表.xls")
使用openpyxl
基本格式:
- 安装模块:
pip install openpyxl
- 读取
Excel
数据:
from openpyxl import load_workbook
wb = load_workbook('表所在路径')
xxx = wb.sheetnames
xxx = wb["sheet1"]
sheet.call(行号,列号).value
sheet["A1"].value
list(sheet.values)
sheet.max_column
sheet.max_row
for sheet in wb:
cell = sheet["A1"].value
for row in sheet.rows:
row[x].value
for col in sheet.columns:
col[x].value
from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook("xxx.xlsx")
sheet = wb["sheet1"]
for x in range(len(xxx)):
for y in range(len(xxx[x])):
sheet.cell(x+1, y+1).value = xxx[x][y]
wb.save("xxx.xlsx")
wb = Workbook()
sheet = wb.create_sheet("sheet1", -1)
for x in range(len(xxx)):
for y in range(len(xxx[x])):
sheet.cell(x+1, y+1).value = xxx[x][y]
wb.save("xxx.xlsx")
- 其他常用操作:
- 创建新sheet:
wb.create_sheet("名称",下标位置)
- 修改sheet名称:
- 设置默认打开的sheet:
- 复制sheet:
xxx = wb.copy_worksheet(wb["原sheet"])
xxx.title = "新sheet"
- 删除sheet:
- 内容居中:
from openpyxl import Workbook
from openpyxl.styles import Alignment
align = Alignment(horizontal='center',vertical='center',wrap_text=True)
wb = Workbook()
sheet = wb.create_sheet("sheet1", -1)
for x in range(len(xxx)):
for y in range(len(xxx[x])):
sheet.cell(x+1, y+1).value = xxx[x][y]
sheet.cell(x+1, y+1).alignment = algin
wb.save("xxx.xlsx")
- 插入图片:
from openpyxl.drawing.image import Image
img = Image('xxx')
img.width = x
img.height = y
sheet.add_image(img, 'A1')
两者的区别:
- xlwt单个表格只能存储65000多行,而openpyxl单个sheet可以存储101万行;
- xlwt的文件名后缀为xls;而openpyxl的文件名后缀为xlsx;
- xlwt写入数据时从0行0列开始;openpyxl从1行1列开始。