正常数据量导出
name = ["梧桐街道", "凤鸣街道", "河山镇", "崇福镇", "洲泉镇"] for i in name: station_qs = air_models.GreanKprMonitorDataDeviceDay.objects.filter(year=y, street_name=i, number=sum) # 2.站点序列化 station_ser = serializers.Daily_Export_DataModelSerializer(station_qs, many=True).data for i in station_ser: data_list.append((i.get("gmt_create"), ("日报"), i.get("station_name"), i.get("street_name"), i.get("pollutions"), i.get("quality"), i.get("aqi"), i.get("pm2_5"), i.get("pm10"), i.get("no2"), i.get("o3"), i.get("so2"), i.get("co"))) columns = ( "时间", "类型", "站点名称", "街道名称", "首要污染物", "质量指数", "AQI", "PM2.5(μg/m³)", "PM10(μg/m³)", "SO2(μg/m³)", "NO2(μg/m³)", "O3(μg/m³)", "CO(mg/m³)") workbook = xlwt.Workbook(encoding='utf-8') # 添加一个表 参数为表名 sheet = workbook.add_sheet('demo') for col, column in enumerate(columns): sheet.write(0, col, column) for i, item in enumerate(data_list): for j, columns in enumerate(item): sheet.write(i + 1, j, columns) excel_name = "全部街道日报" + date_str.replace("-", "") + ".xls" sio = io.BytesIO() workbook.save(sio) sio.seek(0) response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel') # 告诉浏览器是一xls response['Content-Type'] = 'application/octet-stream' response['Access-Control-Expose-Headers'] = "Content-Disposition, Content-Type" response['Content-Disposition'] = 'attachment; filename={}'.format( urlquote(excel_name)) # excel加头部 名称 response.write(sio.getvalue()) sio.close() return response
大数据导出
sheets = [] wbk = xlwt.Workbook() filename = date_str + "省控站站点.xls" filename = urlquote(filename) response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=%s' % (filename) + time.strftime('%Y%m%d', time.localtime( time.time())) + '.xls' sheet = wbk.add_sheet("Log_0", cell_overwrite_ok=True) # 创建工作页 row0 = ["时间", "类型", "地点名称", "AQI", "PM2.5(μg/m³)", "PM10(μg/m³)", "SO2(μg/m³)", "NO2(μg/m³)", "O3(μg/m³)", "CO(mg/m³)"] sheets.append(sheet) for i in range(0, len(row0)): sheets[0].write(0, i, row0[i]) cursor = connections["default"].cursor() sql1 = """SELECT pubtime ,a1.district,g.station,aqi,pm2_5,pm10,o3,no2,so2,co FROM txair_aircontrol as a1 RIGHT JOIN txair_stationcontrol as g ON g.station_code = a1.station_code WHERE DATE_FORMAT(pubtime,'%Y-%m-%d') BETWEEN '2021-01-01' and '2021-12-31'""" cursor.execute(sql1) ret = cursor.fetchall() # print(len(data)) num = 1 # 写入第几行 count = 1 # 写入第几条数据 if (len(ret)) < 0: sheet.write(num, 0, "没有可以导出的日志信息") # 工号 for d in ret: scount = int(count / 65000) if (len(sheets) <= scount): sheets.append(wbk.add_sheet("Log_%s" % (scount + 1), cell_overwrite_ok=True)) for i in range(0, len(row0)): sheets[scount].write(0, i, row0[i]) num = 1 sheet.write(num, 0, str(d[0])) # 日志ID sheet.write(num, 1, "省控站日报") # 日志ID sheet.write(num, 2, d[1]) # 操作内容 sheet.write(num, 3, d[2]) # 操作类型 sheet.write(num, 4, d[3]) # 操作人ID sheet.write(num, 5, d[4]) # 操作人姓名 sheet.write(num, 6, d[5]) # 操作时间 sheet.write(num, 7, d[6]) # 操作时间 sheet.write(num, 8, d[7]) # 操作时间 sheet.write(num, 9, d[8]) # 操作时间 sheet.write(num, 10, d[9]) # 操作时间 num = num + 1 count = count + 1 wbk.save(response) return response