导出Execl模板并带有函数计算

项目开发中遇到一个问题，需要讲给到的Execl模板进行赋值并导出，废话不多说直接上代码。

@RequestMapping(value = "/exportXlsxById", method = RequestMethod.GET)
	 public Result<?> exportXlsxById(HttpServletRequest request, HttpServletResponse response,@RequestParam(name="id",required = true) String id){
		 try {
			 //获取模板文件

			 String path = "C:/Users/Administrator/Desktop/hzbb.xlsx";
//			 String path = wordPath+"/hzbb.docx";
			 File demoFile = new File(path);
			 FileInputStream in = new FileInputStream(demoFile);
			 XSSFWorkbook workbook = new XSSFWorkbook(in);
			 XSSFSheet sheet=workbook.getSheetAt(0);
			 XSSFFormulaEvaluator formulaEvaluator =
					 (XSSFFormulaEvaluator) workbook.getCreationHelper().createFormulaEvaluator();
			 //获取经轴委外单数据
			 EpOutsourceWorder worder = worderService.getById(id);
			 SimpleDateFormat format = new SimpleDateFormat("MM月dd日");
             //region 第二行
			 XSSFRow row1=sheet.getRow(1);
			 if(row1!=null){
				 row1.getCell(2).setCellValue(worder.getCompanyName());
				 row1.getCell(5).setCellValue(worder.getBilldate());
				 row1.getCell(9).setCellValue(worder.getCode());
			 }
			 //endregion

			 //region 第三行
			 XSSFRow row2=sheet.getRow(2);
			 if(row2!=null){
				 row2.getCell(2).setCellValue(worder.getYarnCount()==null?"": worder.getYarnCount().toString());
				 //拼接批号
				 StringBuffer buffer = new StringBuffer("");
				 if (null != worder.getAbatchNo()){
					 buffer.append(worder.getAbatchNo());
				 }
				 if (null != worder.getBbatchNo()){
					 buffer.append(worder.getBbatchNo());
				 }
				 if (null != worder.getCbatchNo()){
					 buffer.append(worder.getCbatchNo());
				 }
				 row2.getCell(5).setCellValue(buffer.toString());
			 }
			 //endregion
			 //region 第四行
			 XSSFRow row3=sheet.getRow(3);
			 //计算总重量：委外单内的筒子数X筒子重量
			 if(row3!=null){
				 row3.getCell(2).setCellValue(worder.getTzWidth());
				 row3.getCell(5).setCellFormula("C4*C5");

			 }
			 //endregion
			 //region 第五行
			 XSSFRow row4=sheet.getRow(4);
			 if(row4!=null){
				 row4.getCell(2).setCellValue(worder.getTzNumber());
				 row4.getCell(6).setCellFormula("F4-K43");
				 row4.getCell(9).setCellFormula("I43");
			 }
			 //执行总重量公式
			 formulaEvaluator.evaluateFormulaCell(row3.getCell(5));
			 //endregion
			 int mNumber = 0;
			 //region 查询明细
			 List<EpOutsourceWreceiveDetailDto> list = service.getExeclDetail(id);
			 String time = "";
			 if(list!=null&&list.size()>0){
				 int rowindex=7;
				 for(EpOutsourceWreceiveDetailDto detailDto:list){
					 XSSFRow drow=sheet.getRow(rowindex);
					 if(drow!=null){
						 Date billdate = detailDto.getBilldate();
						 String date = format.format(billdate);
						 if (!time.equals(date)){
							 time = date;
							 drow.getCell(1).setCellValue(time);
						 }
						 mNumber+=detailDto.getWarpLength()==null?0:detailDto.getWarpLength();
						 drow.getCell(2).setCellValue(detailDto.getPtNumber()==null?"":detailDto.getPtNumber().toString());
						 drow.getCell(3).setCellValue(detailDto.getBNumber()==null?"":detailDto.getBNumber().toString());
						 drow.getCell(4).setCellValue(detailDto.getWarpCode()==null?"":detailDto.getWarpCode());
						 drow.getCell(5).setCellValue(detailDto.getZjgs()==null?"":detailDto.getZjgs().toString());
						 drow.getCell(6).setCellValue(detailDto.getWarpLength()==null?"":detailDto.getWarpLength().toString());
						 drow.getCell(7).setCellValue(String.valueOf(detailDto.getPrice()));
						 drow.getCell(8).setCellFormula("G"+(rowindex+1)+"*H"+(rowindex+1));
						 drow.getCell(9).setCellValue(detailDto.getRemark());
						 //头份×纱支/9000=每米重量×米数=消耗重量
						 drow.getCell(10).setCellFormula("F"+(rowindex+1)+"*C3/9000*G"+(rowindex+1));
						 formulaEvaluator.evaluateFormulaCell(drow.getCell(8));
						 formulaEvaluator.evaluateFormulaCell(drow.getCell(10));
					 }
					 rowindex++;
					 if(rowindex>=35){
						 break;
					 }
				 }
			 }
			 //endregion
			 //获取最后一行小计
			 XSSFRow lastRow=sheet.getRow(42);
			 lastRow.getCell(3).setCellValue(list.size());
			 lastRow.getCell(6).setCellValue(mNumber);
			 formulaEvaluator.evaluateFormulaCell(lastRow.getCell(8));
			 formulaEvaluator.evaluateFormulaCell(lastRow.getCell(10));
			 //执行剩余总量   牵经费用
			 formulaEvaluator.evaluateFormulaCell(row4.getCell(6));
			 formulaEvaluator.evaluateFormulaCell(row4.getCell(9));
			 XSSFRow row5=sheet.getRow(5);
			 row5.getCell(2).setCellValue(mNumber);

			 response.setContentType("application/octet-stream;charset=ISO8859-1");
			 response.addHeader("Pargam", "no-cache");
			 response.addHeader("Cache-Control", "no-cache");
			 String fileName = "文件名";
			 try {
				 response.setHeader("content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8")+".xlsx");
			 } catch (UnsupportedEncodingException e) {
				 e.printStackTrace();
			 }

			 try {
				 ServletOutputStream out = response.getOutputStream();
				 workbook.write(out);
				 out.flush();
				 out.close();
//                OutputStream outputStream = response.getOutputStream();
//                outputStream.flush();
//                workbook.write(outputStream);
//                outputStream.close();
			 } catch (IOException e) {
				 e.printStackTrace();
			 }
		 } catch (IOException e) {
			 e.printStackTrace();
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return null;
	 }

首先一行一行的给模板里的表头赋值，在进行循环给下面的表格内赋值然后就完成了，这里需要注意的是的

//头份×纱支/9000=每米重量×米数=消耗重量
						 drow.getCell(10).setCellFormula("F"+(rowindex+1)+"*C3/9000*G"+(rowindex+1));
						 formulaEvaluator.evaluateFormulaCell(drow.getCell(8));

上面一句话是给单元格内附计算公式 ，下面一句是要执行这个公式在导出的时候，这个不能忘记了 不然就会出现导出完公式不生效需要一个个点一下那种情况才会生效所以这个要执行下的。最后附下导出的模板