1:pom.xml导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId> //本地仓库位置
<version>${poi.version}</version>
</dependency>
2:controller
@ApiOperation(value = "分析数据表格导出", httpMethod = "GET", notes = " 说明:导出CSV格式数据", response = String.class)
@RequestMapping(value = "/passengerFlowDataGridExport", method = RequestMethod.GET)
@ResponseBody
public String passengerFlowDataGridExport(@ApiParam(required = true, name = "dateType", value = "日期类型", defaultValue = "day",
allowableValues = "day") @RequestParam(required = true) String dateType,
@ApiParam(required = false, name = "startTime", value = "开始时间", defaultValue = "2017-06-10")
@RequestParam(required = false) String startTime,
@ApiParam(required = false, name = "endTime", value = "结束时间", defaultValue = "2017-06-11")
@RequestParam(required = false) String endTime,
@ApiParam(required = true, name = "storeCode", value = "门店编码", defaultValue = "871L")
@RequestParam(required = true) String storeCode
){
long begin = System.currentTimeMillis();
logger.info("dataGridExport dateType:{},startTime:{},endTime:{},storeCode:{}" , dateType ,startTime ,endTime ,storeCode);
StorefrontQueryParam queryParam = new StorefrontQueryParam(dateType, startTime, endTime, storeCode);
storefrontPassengerFlowAnalysisService.dataGridExport(queryParam, response);
logger.info("dataGridExport costs:" + (System.currentTimeMillis() - begin) + " ms");
return null;
}
3:service:
@Override
//数据导出
public void dataGridExport(StorefrontQueryParam queryParam, HttpServletResponse response) {
long begin = System.currentTimeMillis();
//待导出的数据
StorefrontPassengerFlowAnalysisDataGridVO storefrontPassengerFlowAnalysisDataGridVO = dataGrid(queryParam);
//定义文件名
String fileName = queryParam.getStoreCode() + "_" + DayUtil.dateFormatLong().format(new Date()) + "_店面进店人次";
// 声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
workbook.setCompressTempFiles(true);
// 生成一个(带标题)表格
SXSSFSheet sheet = workbook.createSheet("进店人次数据表格");
int rowIndex = 0;
SXSSFRow headerDataRow = sheet.createRow(rowIndex);
createExcelHeader(workbook,headerDataRow);//创建表头
rowIndex++; //行号递增
if(storefrontPassengerFlowAnalysisDataGridVO !=null && storefrontPassengerFlowAnalysisDataGridVO.getData() != null){
for (Map<String, Object> timeQuantumMap : storefrontPassengerFlowAnalysisDataGridVO.getData()){
SXSSFRow dataRow = sheet.createRow(rowIndex);
int columnIndex = 0;
SXSSFCell timeQuantumCell = dataRow.createCell(columnIndex);
timeQuantumCell.setCellValue(timeQuantumMap.get("timeQuantum") !=null? timeQuantumMap.get("timeQuantum").toString():"");
columnIndex++;
SXSSFCell inCountCell = dataRow.createCell(columnIndex);
inCountCell.setCellValue(timeQuantumMap.get("inCount") !=null? timeQuantumMap.get("inCount").toString():"");
columnIndex++;
SXSSFCell percentageCell = dataRow.createCell(columnIndex);
percentageCell.setCellValue(timeQuantumMap.get("percentage") !=null? timeQuantumMap.get("percentage").toString():"");
rowIndex++;
}
}
try {
// 设置response参数,可以打开下载页面
response.reset();
ServletOutputStream outputStream = response.getOutputStream();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
workbook.write(outputStream);
workbook.close();
workbook.dispose();
outputStream.flush();
outputStream.close();
logger.info("end dataGridExport costs:{} ms",(System.currentTimeMillis() - begin));
} catch (IOException e) {
logger.error("dataGridExport error:", ExceptionUtils.getStackTrace(e));
}
}
dataGrid:
/**
*
* @param queryParam 查询参数
* @return
*/
@Override
//表格数据
public StorefrontPassengerFlowAnalysisDataGridVO dataGrid(StorefrontQueryParam queryParam) {
long begin = System.currentTimeMillis();
StorefrontBaseProcessor processor;
StorefrontPassengerFlowAnalysisDataGridVO storefrontPassengerFlowAnalysisDataGridVO = new StorefrontPassengerFlowAnalysisDataGridVO();
//单天处理
if (queryParam.getStorefrontQueryDateType()== StorefrontAnalysisQueryDateType.DAY_ONE){
List<Map<String, Object>> statByHourMap = storefrontAnalysisDao.statByHour(queryParam.getStoreCode(), queryParam.getStartTimeStr(), queryParam.getEndTimeStr());
processor = new StorefrontPassengerFlowDayProcessor(queryParam,statByHourMap);
}
//多天处理
else if (queryParam.getStorefrontQueryDateType()== StorefrontAnalysisQueryDateType.DAY_MORE){
List<Map<String, Object>> statByDayMap = storefrontAnalysisDao.statByDay(queryParam.getStoreCode(), queryParam.getStartTimeStr(), queryParam.getEndTimeStr());
processor = new StorefrontPassengerFlowDayProcessor(queryParam,statByDayMap);
}
else{
throw new ValidationException("不支持的查询方式!");
}
//获取返回的结果
List<Map<String, Object>> dataGrids = processor.getDataGrid();
storefrontPassengerFlowAnalysisDataGridVO.setData(dataGrids);
storefrontPassengerFlowAnalysisDataGridVO.setRecordsTotal(dataGrids.size());
logger.info("end dataGrid costs:{} ms",(System.currentTimeMillis() - begin));
return storefrontPassengerFlowAnalysisDataGridVO;
}
/** * 创建Excel表头 * * @param dataRow 行数据 */ public void createExcelHeader(SXSSFWorkbook workbook,SXSSFRow dataRow) { int columnIndex = 0;//列计数据 SXSSFCell channelCell = dataRow.createCell(columnIndex); channelCell.setCellValue("时间段"); columnIndex++; SXSSFCell inCountCell = dataRow.createCell(columnIndex); inCountCell.setCellValue("进店人次"); columnIndex++; SXSSFCell percentage = dataRow.createCell(columnIndex); percentage.setCellValue("进店人次占比"); }