Java用POI实现根据Excel表格模板生成新的Excel并实现数据输出

一、

模板excel

结果excel

二、

项目所需jar包

poi-3.11-20141221.jar

poi-examples-3.11-20141221.jar

poi-excelant-3.11-20141221.jar

poi-ooxml-3.11-20141221.jar

poi-ooxml-schemas-3.11-20141221.jar

poi-scratchpad-3.11-20141221.jar

xmlbeans-2.6.0.jar

curvesapi-1.04.jar

三、项目源代码

controller部分

	/**导出到excel
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/excel")
	public ModelAndView exportExcel() throws Exception{
		logBefore(logger, Jurisdiction.getUsername()+"导出Excel到excel");
		if(!Jurisdiction.buttonJurisdiction(menuUrl, "cha")){return null;}
		ModelAndView mv;
		//获取页面表单传递过来的参数
		PageData pd = this.getPageData();
		//dataMap作为model，存入数据
		Map<String,Object> dataMap = new HashMap<>();
		//存储模板Excel路径和生成Excel路径
		List<String> urls=new ArrayList<>();
		//存储所有heads
		List<String> heads=new ArrayList<>();
		//存储尾部的数值
		List<String> tails=new ArrayList<>();
		//获取当前日期
		Date date = new Date();
		//模板Excel文件路径
		//后缀为xls的文件路径
		String demoUrl=ExcelController.class.getClassLoader().getResource("reportxls/ExportExcel.xls").getPath().replaceFirst("/","");
		//按日期设定当前Excel表格的名字
		String fileName = Tools.date2Str(date, "yyyyMMddHHmmss");
		//生成Excel文件名称
//		String excelUrl=fileName+".xlsx";
		//后缀为xlsx文件的路径
		String excelUrl=fileName+".xls";
		//将路径添加到urls当中
		urls.add(demoUrl);
		urls.add(excelUrl);
		//将urls路径存入到dataMap当中，作为model传入到视图中
		dataMap.put("urls",urls);
		//表格所需要的标题1
		String head1="标题1";
		//表格所需要的标题2
		String head2="标题2";
		//表格所需要的标题3
		String head3="标题3";
		//表格所需要的标题4
		String head4="标题4";
		//表格所需要的标题5
//		String head5="标题5";
		//存入这些标题
		heads.add(head1);
		heads.add(head2);
		heads.add(head3);
		heads.add(head4);
//		heads.add(head5);
		//表格所需要的尾部1
		String tail1="尾部1";
		//存入表格所需要的尾部
		tails.add(tail1);
		//表格所需要的尾部2
		String tail2="尾部2";
		//存入表格所需的尾部
		tails.add(tail2);
		//将tails存入到dataGrip中
		dataMap.put("tails",tails);
		//将标题存放到存入到dataGrip当中
		dataMap.put("heads",heads);
		//获取当前Excel表格所需要的内容
		List<PageData> varOList = excelService.listAll(pd);
		dataMap.put("varOList",varOList);
		//创建需要渲染的Excel表格视图
		//进行文件后缀类型判断
		if (demoUrl.endsWith(".xls")){
			XlsExcelView erv=new XlsExcelView();
			mv=new ModelAndView(erv,dataMap);
		}else {
			XlsxExcelView erv=new XlsxExcelView();
			mv=new ModelAndView(erv,dataMap);
		}
		return mv;
	}

视图部分

public class XlsExcelView extends AbstractXlsView {
    @Override
    protected void buildExcelDocument(Map<String, Object> map, Workbook workbook, HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) throws Exception {

        //获取当前的urls地址
        List<String> urls=(List<String>)map.get("urls");
        //表格所要存储的标题
        List<String> heads=(List<String>)map.get("heads");
        //表格所要存储的尾部
        List<String> tails=(List<String>)map.get("tails");
        //获取查询到的结果集
        List<PageData> varOList=(List<PageData>)map.get("varOList");
        //获取主表信息
        PageData mainInfo=(PageData)map.get("mainInfo");
        //创建Excel文件的输入流对象
        FileInputStream fis=new FileInputStream(urls.get(0));
        //根据模板创建Excel工作簿
        workbook=new HSSFWorkbook(fis);
        //创建Excel文件输出流对象
        OutputStream out=httpServletResponse.getOutputStream();
        httpServletResponse.setContentType("application/octet-stream");
        httpServletResponse.setHeader("Content-Disposition","attachment;filename="+urls.get(1));

        //将workboo强转成需要的HSSFBook
        HSSFWorkbook HSSFWorkbook=(HSSFWorkbook)workbook;
        //获取创建工作簿的第一页
        HSSFSheet sheet=HSSFWorkbook.getSheetAt(0);
        //给指定的sheet命名
        HSSFWorkbook.setSheetName(0,"dataSheet");
        //初始化当前的索引，设为当前sheet的最后一行行数
        int currentLastRowIndex=sheet.getLastRowNum();
        //存储当前表格的样式
        HSSFCellStyle cellStyle=HSSFWorkbook.createCellStyle();
        //获取当前工作簿的行数
        int totalRows=sheet.getPhysicalNumberOfRows();
        /***********************遍历模板sheet，根据当中的设定进行赋值*******************************************************************/
        for (int i=0;i<totalRows;i++){
            HSSFRow row=sheet.getRow(i);
            int column=row.getLastCellNum();
            //插入主表信息
            if (mainInfo!=null){
                for (int j=0;j<mainInfo.size();j++){
                    HSSFCell cell=row.getCell(j)!=null?row.getCell(j):row.createCell(j);
                    String cellValue=cell.getStringCellValue();
                    if (cellValue.startsWith("#")){
                        String str=cellValue.replace("#","");
                        cell.setCellValue(mainInfo.get(str).toString());
                    }
                }

            }
            for (int j=0;j<column;j++){
                HSSFCell cell=row.getCell(j)!=null?row.getCell(j):row.createCell(j);
                String cellValue=cell.getStringCellValue();
                if (cellValue.startsWith("$")){
                    //设置新的单元格存储的值
                    cell.setCellValue(heads.get(0));
                    heads.remove(0);
                }
                if (cellValue.startsWith("&")){
                    //取出表格的样式，作为新插入单元格的样式
                    cellStyle=cell.getCellStyle();
                    //获取当前要插入记录的行数
                    currentLastRowIndex=i;
                    continue;
                }
                if (cellValue.startsWith("#")){
                    cell.setCellValue(tails.get(0));
                    tails.remove(0);

                }

            }
        }
        //增加一行
        int lastRow=sheet.getLastRowNum()+1;
        sheet.setDefaultColumnWidth(lastRow);
        /*************************************************************************************************************/
        //在该行的进行插入(在删除了特定行的字段进行插入）
        int newRowIndex=currentLastRowIndex;
        //获取模板对应的数据表字段的值
        HSSFRow dataRow=sheet.getRow(currentLastRowIndex);
        //获得该行对应的字段的数量
        int columnNum=dataRow.getPhysicalNumberOfCells();
        //根据生成字段的单元格数量，生成对应数量的数组
        String[] cells=new String[columnNum];
        //获取数据集字段的名称，依次存入到cells数组当中
        for (int i=0;i<columnNum;i++){
            HSSFCell cell=dataRow.getCell(i);
            //去掉&符号,并将从excel表格当中获得的值统一转换成大写处理
            cells[i]=cell.getStringCellValue().replaceFirst("&","").toUpperCase();
        }
        //完成数据集字段的收集后，删除该段
        HSSFRow sprRow=sheet.getRow(currentLastRowIndex);
        sheet.removeRow(sprRow);
        /*************************插入新的行数********************************************/
        //将结果集渲染到当前sheet当中
        for (PageData pageData:varOList){
            sheet.shiftRows(newRowIndex,newRowIndex+1,1);
            HSSFRow newRow=sheet.createRow(newRowIndex++);
            //创建需要插入的目标行，该值需要在每次完成一行记录值插入后重新归0
            int cellIndex=0;
            //ic此处为cells数组的结果集的字段的位置
            for (int i=0;i<columnNum;i++){
                //从pageData当中取出目标单元格需要的值
                String cellContent=pageData.get(cells[i])!=null?pageData.get(cells[i]).toString():"";
                //设置目标单元格的位置和类型
                HSSFCell cell=newRow.createCell(cellIndex++,Cell.CELL_TYPE_STRING);
                //设置目标单元格的样式
                cell.setCellStyle(cellStyle);
                //设置目标单元格的值
                cell.setCellValue(cellContent);
            }
        }

        /*********************************************************************************/

        HSSFWorkbook.write(out);
        fis.close();
        out.flush();
        out.close();

    }
}

附注：对于.xlsx文件修改一下引用类名即可