python使用xlwt、xlsxwriter生成Excel

• 2020年5月8日 下午3:55:57 更新
• 试了几种方式，发现关闭不了科学计数法，定义一个方法转换数据的格式， 如果有好的方法请留言分享，多谢！

  def data_processing(data):
      ‘’‘ 数据处理, 把大于10000000000的数据转为str类型 ’‘’
      res = []
      for i in data:
          try:
              if i is None:
                  r = ''
              elif '.' in str(i):
                  r = float(i)
              else:
                  r = int(i)
          except ValueError:
              r = i
          if isinstance(r, (int, float)):
              if r > 10000000000:
                  r = str(r)
          else:
              r = str(r)
          res.append(r)
      return res
  

使用xlwt

• 示例

  def excel_(headers, result, file_name, horizontal=True):
      f = xlwt.Workbook()
      default_easyxf = xlwt.easyxf(num_format_str='0.00')
      sheet1 = f.add_sheet('sheet1')
      # 标红加粗
      # style = "font:colour_index 10, bold False"
      # style = xlwt.easyxf(style)
      if horizontal:
          for i in range(0, len(headers)):
              if isinstance(headers[i], tuple):
                  sheet1.write(0, i, headers[i][0], xlwt.easyxf(headers[i][1]))
                  continue
              sheet1.write(0, i, headers[i])
          row_index = 0
      else:
          r = []
          for k, v in zip(headers, result):
              r.append([k, v])
          result = r
          row_index = -1
      for rows in result:
          row_index += 1
          for j in range(0, len(rows)):
              try:
                  if not rows[j]:
                      r = ''
                  elif '.' in str(rows[j]):
                      r = float(rows[j])
                  else:
                      r = int(rows[j])
              except ValueError:
                  r = rows[j]
              if isinstance(r, (int, float)):
                  # 数值格式
                  if r < 10000000000:
                      sheet1.write(row_index, j, r, default_easyxf)
                  else:
                      sheet1.write(row_index, j, str(r))
              else:
                  sheet1.write(row_index, j, str(r))
      # 保存到本地
      # f.save("/User/name/{}".format(file_name))
      excel_stream = io.BytesIO()
      # BytesIO流（在内存中读写）
      f.save(excel_stream)
      res = excel_stream.getvalue()
      excel_stream.close()
      response = HttpResponse(content_type='application/vnd.ms-excel')
      from urllib import parse
      response['Content-Disposition'] = 'attachment;filename=' + parse.quote(file_name)
      response.write(res)
      return response
  headers = ["姓名", ("手机号", "font:colour_index 10, bold False")]
  results = [["李白", "1888888888"], ["杜甫", "19999999999"]]
  file_name = "test.xlsx"
  excel_(headers, results, file_name)
  

---

使用xlsxwriter

• 示例

  # excel下拉列表导出,数据校验
  def xlsx_excel(headers, results, filename):
      excel_stream = io.BytesIO()
      workbook = xlsxwriter.Workbook(excel_stream)
      worksheet = workbook.add_worksheet()
      # 数字格式
      header_format = workbook.add_format({
          'num_format': '0',
      })
      # 文本格式
      str_format = workbook.add_format()
      str_format.set_num_format('@')
      # 下拉列表校验格式
      data_validate = {'validate': 'list', 'source': ['是', '否']}
      data_validate1 = {'validate': 'list', 'source': ['有', '无']}
      # 日期格式校验
      date_validate = {'validate': 'date',
                       'criteria': 'between',
                       'minimum': date(2018, 1, 1),
                       'maximum': date(9999, 12, 12)}
      for i in range(len(headers)):
          # 写入校验规则
          if isinstance(headers[i], tuple):
              # 写入计算公式
              if "自动计算" in headers[i][0]:
                  worksheet.write(0, i, headers[i][0])
                  worksheet.write_formula(1, i, headers[i][1], header_format)
              # 使用之前定义的header_format数字格式
              elif "text" in headers[i][1]:
                  r = headers[i][1].replace('text', '')
                  worksheet.write(0, i, headers[i][0], workbook.add_format({'font_color': 'red'}))
                  # 单独设置文本格式
                  worksheet.set_column("{}:{}".format(r, r), None, str_format)
              # 使用之前定义的date_validate日期格式
              elif "date" in headers[i][1]:
                  r = headers[i][1].replace('date', '')
                  worksheet.write(0, i, headers[i][0], workbook.add_format({'font_color': 'red'}))
                  worksheet.data_validation('{}2:{}1048576'.format(r, r), date_validate)
              # 使用之前定义的data_validate下拉规则
              else:
                  worksheet.write(0, i, headers[i][0], workbook.add_format({'font_color': 'red'}))
                  worksheet.data_validation('{}2:{}1048576'.format(headers[i][1], headers[i][1]), data_validate)
          else:
              worksheet.write(0, i, headers[i], workbook.add_format({'font_color': 'red'}))
      # 整行写入数据
      for x in range(len(results)):
          worksheet.write_row("A{}".format(x + 2), results[x])
      workbook.close()
      xlsx_data = excel_stream.getvalue()
      response = HttpResponse(content_type='application/ms-excel')
      response['Content-Disposition'] = 'attachment; filename=%s.xlsx' % filename
      response.write(xlsx_data)
      return response
  
  headers = [("日期", 'dateA'), "姓名", ("手机号", "textC"), "数字一", "数字二",
             ("合计（自动计算）", "=D2+E2")]
  results = [["2019/1/1", "张三", "18888888888", "1000", 1000]]
  filename = "test_excel"
  xlsx_excel(headers, results, filename)
  

---

• 参考链接：python生成Excel的两种方式