Python xlrd xlwt 读取写入Excel. drf 下载Excel模板, 读取Excel

import xlrd
import xlwt


#读取
xlrd.Book.encoding = "gbk"
wb = xlrd.open_workbook(filename='sfp.xlsx', )

table = wb.sheets()[3]
nrows = table.nrows

for k in range(1, nrows ):
    print(table1.row_values(k))



#写入

workbook = xlwt.Workbook(encoding='gbk')
sheet = workbook.add_sheet("Miss")
for i in range(0, len(row)):
    sheet.write(0, i, row[i])
style = xlwt.easyxf('align: wrap on')
sheet.col(0).width = 256 * 20
sheet.col(1).width = 256 * 30
sheet.col(2).width = 256 * 30

num = 1
for item in count_list :
    sheet.write(num, 0, item[0], style)
    sheet.write(num, 1, item[1], style)
    sheet.write(num, 2, item[2], style)
    num += 1


workbook.save('xxxx.xls')

---- 2022-2-21 ----

from django.http import FileResponse
from django.shortcuts import HttpResponse
from xlutils.copy import copy


1. 直接返回模板
def export_usercase_excel_temp():
    temp_file = os.path.join(settings.BASE_DIR, "templates", "回归用例模板.xls")
    file = open(temp_file, "rb")
    response = FileResponse(file)
    response['content_type'] = 'application/vnd.ms-excel'
    response['Content-Disposition'] = 'attachment;filename=回归用例模板.xls'.encode('utf-8', 'ISO-8859-1')
    return response


2. 查询query_set 数据并返回
def export_usercase_excel_data(user_cases, xmind_obj, folder_obj):
    temp_file = os.path.join(settings.BASE_DIR, "templates", "回归用例模板.xls")
    xlrd.Book.encoding = "gbk"   
    old = xlrd.open_workbook(temp_file, formatting_info=True)    # 重点
    new = copy(old)
    ws = new.get_sheet(0)
    style = xlwt.easyxf()
    borders = xlwt.Borders()
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    style.borders = borders
    num = 1
    for case in user_cases:
        # ws.write(num, 0, folder_obj.name, style)
        # ws.write(num, 1, xmind_obj.name.split("-")[-1], style)
        ws.write(num, 0, case.title, style)
        ws.write(num, 1, case.operation_content, style)
        ws.write(num, 2, case.pre_operation_content, style)
        ws.write(num, 3, case.priority, style)
        ws.write(num, 4, case.expect_result_content, style)
        ws.write(num, 5, case.operation_mode, style)
        ws.write(num, 6, case.case_creator, style)
        ws.write(num, 7, case.remark, style)
        num += 1
    response = HttpResponse(content_type='application/vnd.ms-excel')
    file_name = escape_uri_path(u"%s-测试用例集.xls" % xmind_obj.name)
    response['Content-Disposition'] = f"attachment; filename*=utf-8''{file_name}"
    new.save(response)
    return response

3. 读取上传的Excel, 并存入数据库
  
  **关键语句**
  file = request.data.get("file")
  xlrd.Book.encoding = "utf-8"
  wb = xlrd.open_workbook(filename=None, file_contents=file.read()) # 重点: 不保存文件直接内存读取文件
  table = wb.sheets()[0]


4. update_or_create
  obj, created = AppUsercase.objects.update_or_create(**kwargs, default)
  created = True 创建数据
  created = False  更新数据