Java后台 查询数据库数据保存到Excel中,供客户端下载。
- 创建实体来,根据需要保存的字段进行实体类的创建
- 编码你的工具类
/发票查询保存Excel
public HSSFWorkbook export(List<ExcelInformation> list) {
String[] excelHeader = {"提单号", "船名", "航次", "船期", "起运港", "目的港", "费用项目", "币别", "金额", "发票号", "订舱人",
"销售", "发票日期"};
HSSFWorkbook wb = new HSSFWorkbook();
//创建HSSFSheet对象
HSSFSheet sheet = wb.createSheet("审核信息表");
HSSFRow row = sheet.createRow((int) 0);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for (int i = 0; i < excelHeader.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(excelHeader[i]);
cell.setCellStyle(style);
sheet.autoSizeColumn(i);
//设置指定列的列宽,256 * 50这种写法是因为width参数单位是单个字符的256分之一
sheet.setColumnWidth(cell.getColumnIndex(), 100 * 50);
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 1);
ExcelInformation applyCard = list.get(i);
row.createCell(0).setCellValue(applyCard.getOblno());
row.createCell(1).setCellValue(applyCard.getVessel());
row.createCell(2).setCellValue(applyCard.getVoy());
row.createCell(3).setCellValue(applyCard.getEtd());
row.createCell(4).setCellValue(applyCard.getPortload());
row.createCell(5).setCellValue(applyCard.getPortdis());
row.createCell(6).setCellValue(applyCard.getName());
row.createCell(7).setCellValue(applyCard.getMoneytype());
row.createCell(8).setCellValue(applyCard.getMoney_sj());
row.createCell(9).setCellValue(applyCard.getInvoiceno());
row.createCell(10).setCellValue(applyCard.getUsername());
row.createCell(11).setCellValue(applyCard.getKf());
row.createCell(12).setCellValue(applyCard.getVouchno());
}
return wb;
}
- 我这点是根据前台传过来一个值(发票号)进行数据库查询,
所以路径中有一个invoiceno,
@RequestMapping(value = "/getEXCEL", produces = "application/octet-stream") @ResponseBody public void downloadFile(HttpServletRequest request, HttpServletResponse response, @RequestParam(value = "invoiceno", required = true) String invoiceno)throws IOException { List<ExcelInformation> list = new ArrayList<ExcelInformation>(); String stringlist=newFrontsService.getExcelInformation(invoiceno); ObjectMapper mapper = new ObjectMapper(); JavaTypejavaType=mapper.getTypeFactory().constructParametricType(ArrayList.class, ExcelInformation.class); List<ExcelInformation> entityList = (List<ExcelInformation>) mapper.readValue(stringlist, javaType); list = entityList; HSSFWorkbook wb = this.export(list); OutputStream output = response.getOutputStream(); response.addHeader("Content-Disposition", "inline;filename=" + new SimpleDateFormat("yyyyMMdd_HHmmssSSS").format(new Date()) + ".xls"); response.setContentType("application/msexcel"); wb.write(output); output.close(); }
我的Sql
//测试 public String getExcelInformation(String invoiceno){ String sql = "SELECT tz.oblno,tz.vessel,tz.voy,tz.etd,tz.portload,tz.portdis,bill.itemcode,bill.moneytype,bill.money_sj,bill.invoiceno," + " mxd.jd,mxd.kf,bill.vouchno,codelib.name,userlist.name AS username FROM bill" + " INNER JOIN tz ON tz.ybh = bill.ybh" + " INNER JOIN mxd ON mxd.ybh = bill.ybh" + " INNER JOIN codelib ON bill.itemcode = codelib.code" + " INNER JOIN userlist ON userlist.userno = mxd.jd" + " WHERE bill.invoiceno = ? and bill.billtype = '0'"; List<Map<String, Object>> list = proxoolManager.getSybaseTemplate().queryForList(sql,invoiceno); return JSON.toJSONString(list.toArray()); }
实体类,工具类,控制层,sql一应俱全。
小弟不才,喜欢多听建议