java-SpringMVC框架导出Excel格式数据

1.控制层Controller，使用getLCCnsFeeCalMainExcel.do去接收

@SuppressWarnings("rawtypes")  //去除警告@RequestMapping("getLCCnsFeeCalMainExcel.do")public ModelAndView queryExcel(@RequestParam("req") String hreq)throws ParseException, IOException, SQLException {// 根据保单号查询其他信息hreq = URLDecoder.decode(hreq,"UTF-8");LCCnsFeeCalMainDto tLCCnsFeeCalMainDto = JsonUtils.toJsonObject(hreq,LCCnsFeeCalMainDto.class);List<Map> ptpList = tLCCnsFeeCalMainBlo.queryExcel(tLCCnsFeeCalMainDto);// 数据导出数据查询String excelName = "LCCnsFeeCalMain.xls";// 创建生成文件的名字LCCnsFeeCalMainViewExcel tLCCnsFeeCalMainViewExcel = new LCCnsFeeCalMainViewExcel(excelName, ptpList);// 保单跟进文件生成方法return new ModelAndView(tLCCnsFeeCalMainViewExcel);}

2.LCCnsFeeCalMainViewExcel

     LCCnsFeeCalMainViewExcel类继承AbstractExcelView，调用构造方法及重写的buildExcelDocument方法

       构造方法：创建变量templetFilePath，内容为模板地址，并赋值，赋值过程在步骤3中展示

package com.yunhui.oversea.policy.view;import java.io.BufferedOutputStream;import java.io.OutputStream;import java.util.List;import java.util.Map;import java.util.Properties;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.springframework.web.servlet.view.document.AbstractExcelView;import com.yunhui.oversea.policy.util.ExcelUtil;public class LCCnsFeeCalMainViewExcel extends AbstractExcelView { String templetFilePath; String generateFilePath; Properties datas; @SuppressWarnings("rawtypes") List<Map> list;    /***     * 构造方法     * @param generateFilePath     * @param list     */ @SuppressWarnings("rawtypes") public LCCnsFeeCalMainViewExcel(String generateFilePath,List<Map> list) {  //获取文件模板  this.templetFilePath = System.getProperty("webServerRoot")+"/excel/LCCnsFeeCalMainView.xls";  this.generateFilePath = generateFilePath;  this.list = list; }     /***    * 向Excel表中写数据，生成对应的Excel文档    */ @Override protected void buildExcelDocument(Map<String, Object> arg0,   HSSFWorkbook arg1, HttpServletRequest request,   HttpServletResponse response) throws Exception {  response.setContentType("application/vnd.ms-excel");  response.setHeader("Content-disposition", "attachment;filename="    + this.generateFilePath);  ExcelUtil excel = new ExcelUtil(this.templetFilePath);  excel.insertRow(1,this.list);    OutputStream ouputStream = response.getOutputStream();    BufferedOutputStream bos = new BufferedOutputStream(ouputStream);  excel.wb.write(bos);   bos.flush();        bos.close(); }}

      重写的buildExcelDocument：将参数使用ExcelUtil类，插入Excel表中，代码如下：

public void insertRow(int starRow,List<Map> list) throws IOException {HSSFSheet sheet = this.wb.getSheetAt(0);// 选择一个区域，从startRow+1直到最后一行sheet.shiftRows(starRow + 1, sheet.getLastRowNum(), list.size(), true, false);//System.out.println("starRow1:" + starRow);starRow = starRow - 1;for (int i = 0; i < list.size(); i++) {HSSFRow sourceRow = null;HSSFRow targetRow = null;HSSFCell sourceCell = null;HSSFCell targetCell = null;short m;starRow = starRow + 1;sourceRow = sheet.getRow(starRow);//System.out.println("starRow2:" + starRow);if (sourceRow == null) {sourceRow = sheet.createRow(starRow);}// 從start創建新的一行targetRow = sheet.createRow(starRow + 1);targetRow.setHeight(sourceRow.getHeight());// sourceRow.setRowNum(sourceRow.getRowNum());// 处理刚刚创建的一行Map cellmap = (HashMap)list.get(i);    Object s[] = cellmap.keySet().toArray();for(m = 0; m < cellmap.size(); m++) {//for (m = sourceRow.getFirstCellNum(); m < cellList.size(); m++) {sourceCell = sourceRow.getCell(m);targetCell = targetRow.createCell(m);// 风格一样HSSFCellStyle style = wb.createCellStyle();style.setBorderBottom(HSSFCellStyle.BORDER_THIN);style.setBorderLeft(HSSFCellStyle.BORDER_THIN);style.setBorderRight(HSSFCellStyle.BORDER_THIN);style.setBorderTop(HSSFCellStyle.BORDER_THIN);HSSFFont fontNormal = wb.createFont();fontNormal.setFontHeightInPoints((short) 10);fontNormal.setFontName("宋体");fontNormal.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中style.setFont(fontNormal);targetCell.setCellStyle(style);//targetCell.setCellStyle(sourceCell.getCellStyle());//targetCell.setCellType(sourceCell.getCellType());String value = "";if (cellmap.get(s[m]) != null) {if (cellmap.get(s[m]).getClass().equals(Timestamp.class)) {Timestamp timestamp = (Timestamp) cellmap.get(s[m]);Date date = new Date(timestamp.getTime());value = DateUtils.format(date, DateUtils.FORMAT_LONG);} else if (cellmap.get(s[m]).getClass().equals(BigDecimal.class)) {value = StringUtils.twoDecimal(((BigDecimal) cellmap.get(s[m])).doubleValue());} else {value = cellmap.get(s[m]) + "";}}targetCell.setCellType(HSSFCell.CELL_TYPE_STRING);targetCell.setEncoding(HSSFCell.ENCODING_UTF_16);targetCell.setCellValue(value);// 設置值}}}

3.web.xml配置

        在tomcat下部署两个或多个项目时，web.xml文件中最好定义webAppRootKey参数，如果不定义，将会缺省为“webapp.root”，最好保证每个项目的参数值不同，以免引起项目冲突。

<!--   <context-param>      <param-name>webAppRootKey</param-name>      <param-value>webServerRoot</param-value>    </context-param>  -->

<listener>    <listener-class>com.yunhui.oversea.policy.servlet.GeneralListener</listener-class>  </listener>

4.配置监听器GeneralListener类

       使用event.getServletContext().getRealPath("/")方法取得物理路径，并把其作为key和value放到system.properties系统属性中

public class GeneralListener implements ServletContextListener {@Overridepublic void contextInitialized(ServletContextEvent event) {String webServerRoot = event.getServletContext().getRealPath("/");System.setProperty("webServerRoot", webServerRoot);Properties properties = new Properties();          try          {              InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("system.properties");             properties.load(inputStream);              inputStream.close(); //关闭流          }          catch (IOException e)          {              e.printStackTrace();          }  LogUtils.info(Log.BIZ_LOGGER, "webServerRoot:"+event.getServletContext().getRealPath("/"));}

5.ModelAndView

        最后使用ModelAndView接收excel格式数据参数，并返回给前端展示

6.Excel文件导出成功