一、用openpyxl实现导入与导出
基本用法 excel之导出excel表
from openpyxl import Workbook sheet = Workbook() # 激活一个表格 word = sheet.active # 创建一个新表格 # word2 = sheet.create_sheet() # 给表格起名字 # word.title = 'table' # 指定行列填入数据 # word.cell(1, 1, "hello, world") # 直接根据单元格填入 # word['A2'] = '大家好' # 也可以按行追加 word.append(['type','disease_guiding_id','dis_attr_guiding_id','symptom_guiding_id','sym_attr_guiding_id','order','guide_guiding_id','promote','disease_name']) # 保存表格 sheet.save("test1.xlsx")
基本用法之导入exel表
from openpyxl import load_workbook workbook = load_workbook('专病引导导入测试.xlsx') # 加载xlsx文件 sheets = workbook.sheetnames # 获取文件里所有表的名字 booksheet = workbook[sheets[0]] #获取第一张表里的内容 rows = booksheet.rows # 获取第一张表的里的行数 columns = booksheet.columns # 获取第一张表里的列数 for row in rows: line = [col.value for col in row] # row_3 = list(booksheet.rows)[2] #获取第一张表里第三行的数据，注意返回值里的元素为cell类型，需要转化 # 获取列的内容类似，将rows换成columns line3 = [col.value for col in row_3] #转换第一张表里第三行的数据，用list类型存储
二、用xlsxwriter导出excel表
import xlsxwriter
import io
queryset = self.get_queryset()
ser = self.serializer_class(queryset, many=True)
disease_guide_headers = [
"名称", "状态（0=关闭，1=开启）", "优先级", "年龄下限", "年龄上限",
"适用性别", "自动设置", "自动设置", "提示触发", "提示触发"
]
disease_step_headers = [
"名称", "步骤提示"
]
disease_content_headers = [
"名称", "步骤提示", "内容类型", "内容名", "内容通用属性", "内容排序"
]
output = io.BytesIO() # ????
with xlsxwriter.Workbook(output) as workbook:
worksheet_guide = self.add_sheet(workbook, disease_guide_headers, column='A:J', sheet_name='引导设置') # 生成多个sheet
worksheet_step = self.add_sheet(workbook, disease_step_headers, column='A:B', sheet_name='步骤设置')
worksheet_content = self.add_sheet(workbook, disease_content_headers, column='A:F', sheet_name='内容设置')
row_guide = 1 # 做标记，用来表示，写入的是那一行
row_step = 1
row_content = 1
for disease_guide in ser.data:
name = disease_guide.get('name')
guiding_steps = disease_guide.pop('guiding_steps')
data_guide = self.disease_guide_data(
disease_guide, self.get_resource_data.get('sym_dic'), self.get_resource_data.get('dis_dic'))
worksheet_guide.write_row(row_guide, 0, data_guide)
row_guide += 1
for guiding_step in guiding_steps:
guiding_step_name = guiding_step.get('prompt')
disease_content = guiding_step.pop('disease_symptom')
data_step = self.disease_steps_data(guiding_step, name)
worksheet_step.write_row(row_step, 0, data_step)
row_step += 1
for disease_symptom in disease_content:
data_content = self.disease_content_data(
name,
guiding_step_name,
disease_symptom,
self.get_resource_data.get('sym_dic'),
self.get_resource_data.get('dis_dic'),
self.get_resource_data.get('dis_attr_dic'),
self.get_resource_data.get('sym_attr_dic'),
)
worksheet_content.write_row(row_content, 0, data_content)
row_content += 1
from django.http import FileResponse
output.seek(0) # 将指针移到最开始处，
response = FileResponse(output) # 将FileResponse(output)赋值给response
response['Content-Type'] = 'application/octet-stream' # 二进制流数据（文件下载）
response['Content-Disposition'] = 'attachment;filename="{}"'.format("disease_guide.xlsx")
return response