python操作excel

xlrd excel读

import xlrdfrom datetime import date, datetimeworkbook = xlrd.open_workbook(r'F:\demo.xlsx')  # 打开文件workbook.sheet_names() # 获取所有sheet名字  [u'sheet1', u'sheet2'] sheet2 = workbook.sheet_names()[1] # 获取某个sheet名字# 获取某个sheetsheet2 = workbook.sheets()[1] sheet2 = workbook.sheet_by_index(1)sheet2 = workbook.sheet_by_name('sheet2')print sheet2.name,sheet2.nrows,sheet2.ncols # sheet的名称，行数，列数# 获取整行和整列的值（list）rows = sheet2.row_values(3) # 获取第四行内容cols = sheet2.col_values(2) # 获取第三列内容print sheet2.cell(1,0).ctype # 获取单元格内容的数据类型# 获取单元格内容print sheet2.cell(1,0).value.encode('utf-8')print sheet2.cell_value(1,0).encode('utf-8')print sheet2.row(1)[0].value.encode('utf-8')# 读取excel中单元格内容为日期的方式ctype :  0 empty,1 string, 2 number, 3 date, 4 boolean, 5 errorsheet2.cell(2,2).ctype # 3if (sheet.cell(row,col).ctype == 3):    date_value = xlrd.xldate_as_tuple(sheet2.cell_value(rows,col), workbook.datemode)    date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d')# 合并单元格的情况，合并的第一个单元格的有值，其它的为空# 获取合并单元格内容需要做如下处理workbook = xlrd.open_workbook(r'F:\demo.xlsx',formatting_info=True)sheet2 = workbook.sheets()[0]# sheet2.merged_cells  # 获取合并的单元格  [(7, 8, 2, 5), (1, 3, 4, 5), (3, 6, 4, 5)] merged_cells返回的这四个参数的含义是：(row,row_range,col,col_range)merge = []for (rlow,rhigh,clow,chigh) in sheet2.merged_cells:    merge.append([rlow,clow]) # 低位索引即合并的第一个单元格索引for index in merge:    print sheet2.cell_value(index[0],index[1])

xlwt excel写

import xlwtdef set_style(name,height,bold=False):    # 设置单元格样式    style = xlwt.XFStyle()  # 初始化样式    font = xlwt.Font()  # 为样式创建字体    font.name = name  # 'Times New Roman'    font.height = height    font.bold = bold    font.color_index = 4    style.font = font    return stylef = xlwt.Workbook() # 创建工作簿sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True) # 创建sheetrow0 = [u'业务',u'状态',u'北京',u'上海',u'广州',u'深圳',u'状态小计',u'合计']column0 = [u'机票',u'船票',u'火车票',u'汽车票',u'其它']status = [u'预订',u'出票',u'退票',u'业务小计']# 生成第一行 for i in range(0,len(row0)):        sheet1.write(0, i, row0[i], set_style('Times New Roman', 220, True))#生成第一列和最后一列(合并4行)i, j = 1, 0while i < 4*len(column0) and j < len(column0):    sheet1.write_merge(i,i+3,0,0,column0[j],set_style('Arial',220,True)) #第一列    sheet1.write_merge(i,i+3,7,7) #最后一列"合计"    i += 4    j += 1f.save(file) #保存文件

openpyxl

from openpyxl import load_workbookfile_name = r'E:\test.xlsx'worksheet = load_workbook(file_name)sheet2 = worksheet.get_sheet_by_name("Sheet2")sheet2.get_highest_column() # 获取列总数sheet2.get_highest_row() # 获取行总数for row in sheet2.rows:  # 获取所有行(columns 列)    for i in row:        print(i.value)sheet2.get_cell_collection() # 获取所有cellsheet2.cell('A1').value # 获取值sheet2.cell(row=1, column=1).value # 获取值sheet2.cell(row=1, column=2).value = 'sssssss'sheet2.append({'A':'新增列A'})  # 新增列sheet2.append({1:'新增行'})  # 新增行worksheet.save(file)