一、xlrd和xlwt模块介绍
xlrd模块提供在任何平台上从excel电子表格(.xls和.xlsx)中提取数据的功能，xlwt模块提供生成与Microsoft Excel 95 到2003版本兼容的excel文件的功能。
1、安装方法
# 方法一： pip3 install xlrd pip3 install xlwt # 方法二： 在python官网http://pypi.python.org/pypi/xlrd/(xlwt)下载xlrd/xlwt的安装包。 压缩包放置在python安装目录下python/Lib/site-packages。 解压压缩包：tar zxvf xlrd-1.0.0.tar.gz 进入到解压的文件夹：cd xlrd-1.0.0 执行命令：python setup,py install
2、xlrd使用详解
（1）excel文档准备
准备excel文档：联系人.xls，内容如下所示：
（2）xlrd获取sheet工作表名称和对象
import xlrd # 打开Excel文件读取器,加上utf-8编码可防止遇到中文字符乱码 data = xlrd.open_workbook('联系人.xls', encoding_override='utf-8') # 获取所有sheet工作表名称 sheetnames = data.sheet_names() print(sheetnames) """ ['银行1', '银行2'] """ # 通过索引获取指定sheet工作表名称 sheet_name = data.sheet_names()[1] print(sheet_name) """ 银行2 """ # 通过工作表名称获取sheet对象 table_name = data.sheet_by_name(sheet_name) print(table_name) """ <xlrd.sheet.Sheet object at 0x10b657160> """ # 通过sheet索引获取sheet工作表对象 table_index = data.sheet_by_index(0) print(table_index) """ <xlrd.sheet.Sheet object at 0x1073b3f28> """
（3）sheet工作表行/列操作
# 获取行数和列数 nrows = table_name.nrows # 总行数 ncols = table_name.ncols # 总列数 print("银行2表,总行数: %s,总列数: %s" % (nrows, ncols)) """ 银行2表,总行数: 5,总列数: 7 """ # 根据sheet对象获取整行和整列的值 sheet_row_val = table_name.row_values(3) sheet_col_val = table_name.col_values(3) print("整行的值：%s , 整列的值: %s" % (sheet_row_val, sheet_col_val)) """ 整行的值：['', '李九', '开发', 'IOS开发', 87888.0, 13213123.0, 'wang1@164.com'] , 整列的值: ['', '工作职责', 'UI设计', 'IOS开发', '硬件维护'] """ # 获取当前行的有效单元格长度 print(table_name.row_len(3)) print(table_index.row_len(3)) """ 7 8 """
（4）sheet工作表单元格操作
单元格是组成表格的最小单位，可以拆分或合并。每个数据的输入和修改都是在单元格中进行的。
# 获取指定单元格内容 print(table_name.cell(1,0).value) # 第二行第一列 print(table_name.cell_value(2,1)) # 第三行第二列 print(table_name.row(1)[0].value) # 第二行第一列 """ 机构名称 张八 机构名称 """ # 获取单元格内容的数据类型 # ctype说明：0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 error print(table_index.cell(1,0).ctype) # 银行1表，2行1列内容 机构名称 print(table_index.cell(3,4).ctype) # 银行1表，4行5列内容 888 print(table_index.cell(2,6).ctype) # 银行1表，3行7列内容 2019/5/7 """ 1 2 3 """ # 日期单元格内容处理 from datetime import datetime, date if table_index.cell(2,6).ctype == 3: print(table_index.cell(2, 6).value) date_value = xlrd.xldate_as_tuple(table_index.cell(2, 6).value, data.datemode) print(date_value) print(date(*date_value[:3])) print(date(*date_value[:3]).strftime('%Y/%m/%d')) """ 43592.0 (2019, 5, 7, 0, 0, 0) 2019-05-07 2019/05/07 """ # 获取number类型的单元格内容 if table_index.cell(3, 4).ctype == 2: print(table_index.cell(3, 4).value) num_value = int(table_index.cell(3, 4).value) # 转为整型 print(num_value) """ 888.0 888 """
（5）获取合并单元格内容
需要使用merged_cells属性，获取merged_cells返回的row和col低位的索引即可。
# 需要在读取文件时添加formatting_info参数，默认是False # 需要设置为True，才能调用merged_cells属性获取到值 xls_data = xlrd.open_workbook('联系人.xls', formatting_info=True) sheet_data = xls_data.sheet_by_name('银行2') print(sheet_data.merged_cells) """ [(0, 1, 0, 7), (2, 5, 0, 1)] """
merged_cells返回的这四个参数的含义：(row,row_range,col,col_range)。
因此，(0,1,0,7)表示第1列~第7列合并；(2,5,0,1)表示第3行~第6行合并。
# 分别获取合并两个单元格内容 merge_value = [] for (row, row_range, col, col_range) in sheet_data.merged_cells: merge_value.append((row, col)) """ [(0, 0), (2, 0)] """ print(merge_value) for i in merge_value: print(sheet_data.cell(i[0], i[1]).value) """ 银行2 银行2 """
3、xlwt使用详解
使用xlwt模块对Excel文件进行创建、设置、保存等操作。
import xlwt # 创建一个工作簿(workbook)，并设置编码 # workbook = xlwt.Workbook(encoding='ascii') workbook = xlwt.Workbook(encoding='utf-8') # 创建一个工作表(worksheet) worksheet = workbook.add_sheet("My Worksheet") # 样式设置(可选) style = xlwt.XFStyle() # 初始化样式 font = xlwt.Font() # 为样式创建字体 font.name = "Times New Roman" font.bold = True # 加粗 font.underline = True # 下划线 font.italic = True # 斜体字 style.font = font # 设定样式 # 写入excel,参数对应 行 列 值 # 不带样式的写入 worksheet.write(1,0, 'Unformatted value') # 带样式的写入 worksheet.write(2,0, 'Formatted value', style) # 保存文件 workbook.save("xlwt_test.xls")
打开Excel文件显示效果如下：
（1）设置单元格宽度
import xlwt workbook = xlwt.Workbook() worksheet = workbook.add_sheet('My Sheet') worksheet.write(0, 0,'My Cell Contents') # 设置单元格宽度 worksheet.col(0).width = 30003 workbook.save('cell_width.xls')
显示效果：
（2）输入日期到单元格
import xlwt import datetime workbook = xlwt.Workbook() worksheet = workbook.add_sheet('My Sheet') style = xlwt.XFStyle() style.num_format_str = 'M/D/YY' # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0 worksheet.write(0, 0, datetime.datetime.now(), style) workbook.save('Excel_Workbook.xls')
显示效果：
（3）添加公式到单元格
import xlwt workbook = xlwt.Workbook() worksheet = workbook.add_sheet('My Sheet') worksheet.write(0, 0, 5) # Outputs 5 worksheet.write(0, 1, 2) # Outputs 2 # 添加公式 worksheet.write(1, 0, xlwt.Formula('A1*B1')) # Should output "10" (A1[5] * A2[2]) worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) # Should output "7" (A1[5] + A2[2]) workbook.save('Excel_formula.xls')
显示效果：
（4）添加超链接到单元格
import xlwt workbook = xlwt.Workbook() worksheet = workbook.add_sheet('My Sheet') worksheet.write(0, 0, xlwt.Formula('HYPERLINK("http://www.baidu.com";"Baidu")')) # Outputs the text "Baidu" linking to http://www.baidu.com workbook.save('Excel_hyperlink.xls')
显示效果：
（5）合并列和行
import xlwt workbook = xlwt.Workbook() worksheet = workbook.add_sheet('My Sheet') worksheet.write_merge(0, 0, 0, 3, 'First Merge') # Merges row 0's columns 0 through 3. font = xlwt.Font() # Create Font font.bold = True # Set font to Bold style = xlwt.XFStyle() # Create Style style.font = font # Add Bold Font to Style worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) # Merges row 1 through 2's columns 0 through 3. workbook.save('Excel_merge.xls')
显示效果：
（6）其他单元格设置
""" 设置单元格内容的对其方式: """ import xlwt workbook = xlwt.Workbook() worksheet = workbook.add_sheet('My Sheet') alignment = xlwt.Alignment() # Create Alignment alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED style = xlwt.XFStyle() # Create Style style.alignment = alignment # Add Alignment to Style worksheet.write(0, 0, 'Cell Contents', style) workbook.save('Excel_Workbook.xls') """ 为单元格议添加边框: """ # Please note: While I was able to find these constants within the source code, on my system (using LibreOffice,) I was only presented with a solid line, varying from thin to thick; no dotted or dashed lines. import xlwt workbook = xlwt.Workbook() worksheet = workbook.add_sheet('My Sheet') borders = xlwt.Borders() # Create Borders borders.left = xlwt.Borders.DASHED DASHED虚线 NO_LINE没有 THIN实线 # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D. borders.right = xlwt.Borders.DASHED borders.top = xlwt.Borders.DASHED borders.bottom = xlwt.Borders.DASHED borders.left_colour = 0x40 borders.right_colour = 0x40 borders.top_colour = 0x40 borders.bottom_colour = 0x40 style = xlwt.XFStyle() # Create Style style.borders = borders # Add Borders to Style worksheet.write(0, 0, 'Cell Contents', style) workbook.save('Excel_Workbook.xls') """ 为单元格设置背景色: """ import xlwt workbook = xlwt.Workbook() worksheet = workbook.add_sheet('My Sheet') pattern = xlwt.Pattern() # Create the Pattern pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12 pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on... style = xlwt.XFStyle() # Create the Pattern style.pattern = pattern # Add Pattern to Style worksheet.write(0, 0, 'Cell Contents', style) workbook.save('Excel_Workbook.xls')
二、导出excel表（读django字段数据）
在django项目中实现用excel导出数据库中数据。
1、添加导出excel路由
这里是基于crm项目实现，在customer配置类中添加excel路由：
class CustomerConfig(ModelStark): """代码省略""" def extra_url(self): """扩展路由""" temp = [] temp.append(url(r"mycustomer/", self.mycustomer)) temp.append(url(r'export/', self.excel_export)) return temp
2、导出excel视图函数
# 注册客户(学生)到stark class CustomerConfig(ModelStark): """代码省略""" def excel_export(self, request): """导出excel表格""" list_obj = models.Customer.objects.all().order_by("create_time") if list_obj: # 创建工作薄 ws = Workbook(encoding="UTF-8") w = ws.add_sheet(u'数据报表第一页') w.write(0, 0, 'id') w.write(0, 1, u'姓名') w.write(0, 2, u'性别') w.write(0, 3, u'民族') w.write(0, 4, u'籍贯') w.write(0, 5, u'身份证号') w.write(0, 6, u'通知书邮寄地址') w.write(0, 7, u'邮编') w.write(0, 8, u'联系电话') w.write(0, 9, u'联系电话2') w.write(0, 10, u'院校') w.write(0, 11, u'专业') w.write(0, 12, u'创建日期') w.write(0, 13, u'生源人') w.write(0, 14, u'备注') # 写入数据 excel_row = 1 for obj in list_obj: data_id = obj.id data_name = obj.name # data_gender = obj.gender_choices # 只显示数字 data_gender = obj.get_gender_display() data_nation = obj.nation data_birth = obj.birth_place data_identity_num = obj.identity_num data_address = obj.address data_postcode = obj.postcode data_tel = obj.tel data_tel2 = obj.tel_2 data_school = obj.stu_school.title data_course = obj.course.first().name data_time = obj.create_time.strftime('%Y-%m-%d %H:%M:%S') data_consultant = obj.consultant.name data_memo = obj.memo w.write(excel_row, 0, data_id) w.write(excel_row, 1, data_name) w.write(excel_row, 2, data_gender) w.write(excel_row, 3, data_nation) w.write(excel_row, 4, data_birth) w.write(excel_row, 5, data_identity_num) w.write(excel_row, 6, data_address) w.write(excel_row, 7, data_postcode) w.write(excel_row, 8, data_tel) w.write(excel_row, 9, data_tel2) w.write(excel_row, 10, data_school) w.write(excel_row, 11, data_course) w.write(excel_row, 12, data_time) w.write(excel_row, 13, data_consultant) w.write(excel_row, 14, data_memo) excel_row += 1 # 检测文件是否存在 # 方框中代码是保存本地文件使用，如不需要请删除该代码 ########################### exist_file = os.path.exists("stu_info.xls") if exist_file: os.remove(r"stu_info.xls") ws.save("customer_info.xls") ############################ sio = BytesIO() ws.save(sio) sio.seek(0) response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=stu_info.xls' response.write(sio.getvalue()) return response
（1）获取日期并转化格式
data_time = obj.create_time.strftime('%Y-%m-%d %H:%M:%S')
（2）获取一对多对象名称
data_consultant = obj.consultant.name
（3）获取多对多对象名称
data_course = obj.course.first().name
3、将注册的模型类添加到字典_registry中
site.register(models.Customer, CustomerConfig)
三、导入excel表（插入django字段数据）
导入时，一对多字段和多对多字段处理需要特别注意。
1、添加导入Excel路由
# 注册客户(学生)到stark class CustomerConfig(ModelStark): def extra_url(self): """扩展路由""" temp = [] temp.append(url(r"mycustomer/", self.mycustomer)) temp.append(url(r'export/', self.excel_export)) temp.append(url(r'import/', self.excel_import)) return temp
2、导入Excel视图函数
# 注册客户(学生)到stark class CustomerConfig(ModelStark): """省略代码""" def excel_import(self, request): """批量导入数据""" if request.method == 'GET': return render(request, 'excel_import.html', locals()) # return JsonResponse({'msg': '不是post请求'}) else: user_id = request.session.get("user_id") file_obj = request.FILES.get('my_file') type_excel = file_obj.name.split('.')[1] if 'xls' == type_excel: # 开始解析上传的excel表格 wb = xlrd.open_workbook(filename=None, file_contents=file_obj.read()) table = wb.sheets()[0] nrows = table.nrows # 行数 # ncole = table.ncols # 列数 try: # 正常的数据库操作应该是原子性操作 with transaction.atomic(): for i in range(1, nrows): # i/o row_value = table.row_values(i) # 一行的数据 # 添加多对多字段 # 主要这里不能使用get，否则报错：'Course' object is not iterable course_obj = models.Course.objects.filter(name=row_value[9]) print(row_value[10], type(row_value[10])) # 1002.0 <class 'float'> consultant_obj = models.UserInfo.objects.get(code=str(int(row_value[10]))) # 生成学生对象，添加 customer_obj = models.Customer.objects.create( name=row_value[0], gender=row_value[1], nation=row_value[2], birth_place=row_value[3], identity_num=row_value[4], address=row_value[5], postcode=row_value[6], tel=row_value[7], tel_2=row_value[8], stu_school_id=1, create_time=datetime.datetime.now(), consultant=consultant_obj, # 一对多字段 memo=row_value[11] ) # create的时候不写多对多字段，写完后单独设置即可 customer_obj.course.set(course_obj) except Exception as e: return HttpResponse('出现错误...%s' % e) return HttpResponse("上传成功") return HttpResponse('上传文件格式不是xls')
（1）一对多字段
consultant字段是一对多字段，使用如下方法添加：
consultant_obj = models.UserInfo.objects.get(code=str(int(row_value[10]))) # 生成学生对象，添加 customer_obj = models.Customer.objects.create( """代码省略""" consultant=consultant_obj, # 一对多字段 )
（2）多对多字段
course字段是多对多字段，使用如下方法添加：
# 添加多对多字段 # 主要这里不能使用get，否则报错：'Course' object is not iterable course_obj = models.Course.objects.filter(name=row_value[9]) print(row_value[10], type(row_value[10])) # 1002.0 <class 'float'> # 生成学生对象，添加 customer_obj = models.Customer.objects.create("""代码省略""") # create的时候不写多对多字段，写完后单独设置即可 customer_obj.course.set(course_obj)