先用map集合从数据库获取数据,在获取导出的excel模板,循环map集合将数据,代码如下:
@Override
public void download(ShipDynamicForm dyform, HttpServletResponse response) {
if(StringUtils.isEmpty(dyform.getVdeptlist())){
dyform.setVdeptlist(null);
}
List<Map<String,Object>> list = shipmanagerRepositoryImpl.getShipDynamicExport2 (dyform);
response.setCharacterEncoding("utf-8");
response.setHeader("Content-type", "text/html;charset=UTF-8");
ResourceLoader loader = new DefaultResourceLoader();
Resource file = loader.getResource("file:./config/temp/report/"+"shipdynamic"+".xlsx");
try {
response.setHeader("content-disposition", "attachment;filename="+ URLEncoder.encode("shipdynamic" + ".xlsx", "UTF-8"));
ServletOutputStream out=response.getOutputStream();
XSSFWorkbook xwb = new XSSFWorkbook(file.getInputStream());
XSSFSheet st = xwb.getSheetAt(0);
if(!CollectionUtils.isEmpty(list)){
for(int i =0;i<list.size();i++){
XSSFRow row = st.createRow(i+1);
Map<String,Object> m = list.get(i);
row.createCell(0).setCellValue(m.get("userdeptname") == null ?"":m.get("userdeptname")+"");
row.createCell(1).setCellValue(m.get("vshipname") == null ?"":m.get("vshipname")+"");
row.createCell(2).setCellValue(m.get("vestvoyagenos") == null ?"":m.get("vestvoyagenos")+"");
row.createCell(3).setCellValue(m.get("dynamictime") == null ?"":m.get("dynamictime")+"");
row.createCell(5).setCellValue(m.get("vovportname") == null ?"":m.get("vovportname")+"");
row.createCell(7).setCellValue(m.get("tasknonum") == null ?"":m.get("tasknonum")+"");
row.createCell(8).setCellValue(m.get("swtasknonum")==null?"":m.get("swtasknonum")+"");
row.createCell(9).setCellValue(m.get("invoicenonum")==null?0:((BigDecimal) m.get("invoicenonum")).intValue());
row.createCell(10).setCellValue(m.get("gsftotalnonum")==null?"":m.get("gsftotalnonum")+"");
row.createCell(11).setCellValue(m.get("vfuel")==null?"":m.get("vfuel")+"");
ReportDataShipForm form = new ReportDataShipForm();
if(StringUtils.isNotEmpty(m.get("vovvoyageid")==null?"":m.get("vovvoyageid")+"")){
form.setVshipid(m.get("vovshipid").toString());
form.setVvoyageid(m.get("vovvoyageid").toString());
String vovvoyageid = m.get("vovvoyageid").toString();
Response re = shipPlanDynamic(vovvoyageid);
ShipDynamicForm VoyageStatusform = (ShipDynamicForm)re.getResultData();
if(VoyageStatusform.getIshipstatus() != null && VoyageStatusform.getIshipstatus() == 0){
row.createCell(4).setCellValue("在港");
row.createCell(6).setCellValue(VoyageStatusform.getVshipplandynamic().replace("<br>", " "));
}else if(VoyageStatusform.getIshipstatus() != null && VoyageStatusform.getIshipstatus() == 1){
row.createCell(4).setCellValue("在航");
row.createCell(6).setCellValue(VoyageStatusform.getVshipplandynamic().replace("<br>", " "));
}
}
}
xwb.write(out);
out.flush();
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}