后台生成Excel时前端获取下载
Controller控制器:
package com.example.test.controller; import com.example.test.common.HttpRequest; import com.example.test.utils.ExcelUtil; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.util.List; @Controller @RequestMapping(value = "/report") public class TestController { public HttpRequest hr = new HttpRequest(); /** * 导出Excel */ @RequestMapping(value = "/excel", produces = "application/json; charset=utf-8") @ResponseBody public void findExcel(HttpServletRequest request,HttpServletResponse response) { String startTime = request.getParameter("startTime"); startTime = startTime+" 00:00:00"; String endTime = request.getParameter("endTime"); endTime = endTime+" 00:00:00"; String param = "startTime="+startTime+"&endTime="+endTime; String baseURL = "http://localhost:8080"; String res = hr.sendPost(baseURL+"/getFinancingStat", param); System.out.println(res); List<JSONObject> list = JSONArray.fromObject(res); //excel标题 String[] title = {"业务员姓名","单量","业务员电话","业务员id"}; //excel文件名 String fileName = "业务员数据"+startTime+"至"+endTime+".xls"; //sheet名 String sheetName = "业务员数据"; String[][] content=new String[list.size()][title.length]; for (int i = 0; i < list.size(); i++) { content[i] = new String[title.length]; JSONObject obj=list.get(i); content[i][0] = obj.get("业务员姓名").toString(); content[i][1] = obj.get("单量").toString(); content[i][2] = obj.get("业务员电话").toString(); content[i][3] = obj.get("业务员id").toString(); } //创建HSSFWorkbook HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null); //响应到客户端 try { this.setResponseHeader(response, fileName); OutputStream os = response.getOutputStream(); wb.write(os); os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); } } //发送响应流方法 public void setResponseHeader(HttpServletResponse response, String fileName) { try { try { fileName = new String(fileName.getBytes(),"ISO8859-1"); } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block e.printStackTrace(); } response.setContentType("application/octet-stream;charset=ISO8859-1"); response.setHeader("Content-Disposition", "attachment;filename="+ fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } }
ExcelUtil生成Excel的样式:
package com.example.test.utils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class ExcelUtil { /** * 导出Excel * @param sheetName sheet名称 * @param title 标题 * @param values 内容 * @param wb HSSFWorkbook对象 * @return */ public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String [][]values, HSSFWorkbook wb){ // 第一步,创建一个HSSFWorkbook,对应一个Excel文件 if(wb == null){ wb = new HSSFWorkbook(); } // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet(sheetName); // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制 HSSFRow row = sheet.createRow(0); // 第四步,创建单元格,并设置值表头 设置表头居中 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 //声明列对象 HSSFCell cell = null; //创建标题 for(int i=0;i<title.length;i++){ cell = row.createCell(i); cell.setCellValue(title[i]); cell.setCellStyle(style); } //创建内容 for(int i=0;i<values.length;i++){ row = sheet.createRow(i + 1); for(int j=0;j<values[i].length;j++){ //将内容按顺序赋给对应的列对象 row.createCell(j).setCellValue(values[i][j]); } } return wb; } }
前端页面:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script type="text/javascript" src="../js/jquery.js"></script>
</head>
<body>
开始时间:<input class="form-control" type="date" id="startTime" /><br/><br/>
结束时间:<input class="form-control" type="date" id="endTime" /><br/><br/>
<button id="js-export" type="button" class="btn btn-primary">导出Excel</button>
</body>
<script>
$('#js-export').click(function(){
var startTime = document.getElementById("startTime").value;
var endTime = document.getElementById("endTime").value;
window.location.href="/report/excel?startTime="+startTime+"&endTime="+endTime+"";
});
</script>
</html>
maven中的配置:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.6</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-examples</artifactId> <version>3.6</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-excelant --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-excelant</artifactId> <version>3.11</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.6</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.6</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.21</version> </dependency> <dependency> <groupId>net.sf.json-lib</groupId> <artifactId>json-lib</artifactId> <version>2.4</version> <classifier>jdk15</classifier> </dependency>