python操作excel
python操作excel主要用到xlrd和xlwt这两个库,即xlrd是读excel,xlwt是写excel的库。可从这里下载https://pypi.python.org/pypi。下面分别记录python读和写excel.
python读excel——xlrd
这个过程有几个比较麻烦的问题,比如读取日期、读合并单元格内容。下面先看看基本的操作
import xlrd import xlwt import uuid from datetime import datetime workbook = xlrd.open_workbook('shujuzhengli.xls') print(workbook.sheet_names()) li = [] tem = [] fields = ['name', 'full_name', 'model', 'brand', 'leader', 'entry_date', 'start_date'] for sheet in workbook.sheet_names(): table = workbook.sheet_by_name(sheet) #根据名称获取sheet内容 nrows = table.nrows#有效行数 ncols = table.ncols#有效列数 print(nrows,ncols) if nrows and ncols: for nrows_num in range(nrows): #行 print("nrows_num",nrows) dic = {} for ncols_num in range(ncols): #列 print("ncols_num",ncols_num) value = table.cell_value(nrows_num, ncols_num) dic[fields[ncols_num]]=value print("dic",dic) li.append(dic) print("li",li) import pymysql conn = pymysql.connect(host='192.168.99.200',port=3306,database='bms',user='root',passwd='Cwtc@2018',charset='utf8') cursor = conn.cursor() t = datetime.now() for i in li: name = i["name"] sql = "select * from bms_system_equipment where name = '%s'"%(name) cursor.execute(sql ) ret = cursor.fetchone() u = str(uuid.uuid4()).replace("-","") if not ret: sql = "insert into bms_system_equipment(uuid,name,full_name,model,brand,leader,entry_date,start_date,status,created_at,updated_at,deleted) values ('%s','%s','%s','%s','%s','%s','%s','%s',%s,'%s','%s',%s) "\ %(u,i["name"],i["full_name"],i["model"],i["brand"],i["leader"],datetime.strptime(str(i["entry_date"]), "%Y.%m"),datetime.strptime(str(i["start_date"],), "%Y.%m"),2,t,t,0) print(sql)
# 根据sheet索引或者名称获取sheet内容 sheet2 = workbook.sheet_by_index(1) # sheet索引从0开始 sheet2 = workbook.sheet_by_name('sheet2')
# 获取整行和整列的值(数组) rows = sheet2.row_values(3) # 获取第四行内容 cols = sheet2.col_values(2) # 获取第三列内容 print rows print cols # 获取单元格内容 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')
# 获取单元格内容的数据类型 print sheet2.cell(1,0).ctype
python读取excel中单元格内容为日期的方式
python读取excel中单元格的内容返回的有5种类型,即上面例子中的ctype:
ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
即date的ctype=3,这时需要使用xlrd的xldate_as_tuple来处理为date格式,先判断表格的ctype=3时xldate才能开始操作。现在命令行看下:
>>> sheet2.cell(2,2).ctype #1990/2/22 >>> sheet2.cell(2,1).ctype #24 >>> sheet2.cell(2,0).ctype #小胖 >>> sheet2.cell(2,4).ctype #空值(这里是合并单元格的原因) >>> sheet2.cell(2,2).value #1990/2/22 33656.0 >>> xlrd.xldate_as_tuple(sheet2.cell_value(2,2),workbook.datemode) (1992, 2, 22, 0, 0, 0) >>> date_value = xlrd.xldate_as_tuple(sheet2.cell_value(2,2),workbook.datemode) >>> date_value (1992, 2, 22, 0, 0, 0)>>> date(*date_value[:3])datetime.date(1992, 2, 22)>>> date(*date_value[:3]).strftime('%Y/%m/%d') '1992/02/22'
即可以做下简单处理,判断ctype是否等于3,如果等于3,则用时间格式处理
if (sheet.cell(row,col).ctype == 3): date_value = xlrd.xldate_as_tuple(sheet.cell_value(rows,3),book.datemode) date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d')
读取合并单元格的内容
只能获取合并单元格的第一个cell的行列索引,才能读到值,读错了就是空值。
即合并行单元格读取行的第一个索引,合并列单元格读取列的第一个索引
合并单元格可能出现空值,但是表格本身的普通单元格也可能是空值,要怎么获取单元格所谓的"第一个行或列的索引"呢?
这就要先知道哪些是单元格是被合并的!
获取合并的单元格
读取文件的时候需要将formatting_info参数设置为True,默认是False,所以上面获取合并的单元格数组为空,
>>> workbook = xlrd.open_workbook(r'F:\demo.xlsx',formatting_info=True) >>> sheet2 = workbook.sheet_by_name('sheet2') >>> sheet2.merged_cells [(7, 8, 2, 5), (1, 3, 4, 5), (3, 6, 4, 5)]
merged_cells返回的这四个参数的含义是:(row,row_range,col,col_range),其中[row,row_range)包括row,不包括row_range,col也是一样,即(1, 3, 4, 5)的含义是:第1到2行(不包括3)合并,(7, 8, 2, 5)的含义是:第2到4列合并。
利用这个,可以分别获取合并的三个单元格的内容:
>>> print sheet2.cell_value(1,4) #(1, 3, 4, 5) 好朋友 >>> print sheet2.cell_value(3,4) #(3, 6, 4, 5) 同学 >>> print sheet2.cell_value(7,2) #(7, 8, 2, 5) 暂无
发现规律了没?是的,获取merge_cells返回的row和col低位的索引即可! 于是可以这样一劳永逸
>>> merge = [] >>> for (rlow,rhigh,clow,chigh) in sheet2.merged_cells: merge.append([rlow,clow]) >>> merge [[7, 2], [1, 4], [3, 4]] >>> for index in merge: print sheet2.cell_value(index[0],index[1]) 暂无 好朋友 同学 >>>
写EXCEL--xlwt
def write_excel(): # 创建工作簿 workbook = xlwt.Workbook(encoding='utf-8') # 创建sheet data_sheet = workbook.add_sheet('demo') row0 = [u'状态', u'设备编号', u'楼层', u'系统',u"类型"] row1 = [u'测试', '15:50:33-15:52:14', 22706, 4190202] i = 0 for k in range(len(row0)): data_sheet.write(i, k, row[k], set_style('Times New Roman', 220, True)) # 保存文件 workbook.save('demo2.xls') if __name__ == '__main__': write_excel() print(u'创建demo.xlsx文件成功')
flask 下载excel文件 filename = "serverInfo.xlsx" file_path =os.path.join(home_path,filename) #f'../test.xlsx' f.save(file_path) return send_file(file_path, as_attachment=True, attachment_filename="服务器信息.xlsx") fastapi下载Excel文件 file_path = '../attachment/1607762573.docx' response = FileResponse(file_path, media_type='application/octet-stream', filename='报告.docx')
参考:https://www.cnblogs.com/zhoujie/p/python18.html
I can feel you forgetting me。。 有一种默契叫做我不理你,你就不理我