Python操作Excel常用代码

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

import xlrd
from tqdm import tqdm
from datetime import datetime

workbook = xlrd.open_workbook('some.xlsx')
sheet = workbook.sheet_by_name('sheet1')
#sheet = workbook.sheet_by_index(0)

nrows = sheet.nrows
for idx in tqdm(range(1, nrows)):
    data = sheet.cell_value(idx, 0)

    # 将Excel日期转换为Python datetime对象
    cell = sheet.cell(row_idx, col_idx)
    dt_tuple = xlrd.xldate_as_tuple(cell.value, workbook.datemode)
    # 转换为datetime对象
    dt = datetime(*dt_tuple)
    # 格式化日期为"年-月-日"
    date_str = dt.strftime("%Y-%m-%d")

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

import xlwt

workbook = xlwt.Workbook()
sheet = workbook.add_sheet('sheet1')

sheet.write(0, 0, 'Head_1')

idx = 1
for row in tqdm(datas):
    sheet.write(idx, 0, row['data_1'])
    idx += 1

workbook.save('some.xls')

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

import xlsxwriter

workbook = xlsxwriter.Workbook('some.xls')
sheet = workbook.add_worksheet('sheet1')

sheet.write(0, 0, 'Head_1')

idx = 1
for row in tqdm(datas):
    sheet.write(idx, 0, row['data_1'])
    idx += 1

workbook.close()