手工导出Excel

maven

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>

示例

示例1

@ApiOperation(value = "导出NgGrid样式的调整数据")
    @RequestMapping(value = "/exportAdjNgGrid", method = RequestMethod.GET)
    public void exportAdjNgGrid(HttpServletRequest request, HttpServletResponse response, @ApiParam(value = "FilingId", required = true) @RequestParam Map params) {
        Map datas = getAdjNgGrid(params);
        List<Map<String, Map>> auditCols=(List<Map<String, Map>>)datas.get("auditCols");
        List<Map<String, Map>> taxCols=(List<Map<String, Map>>)datas.get("taxCols");
        List<Map<String, Object>> rows=(List<Map<String, Object>>)datas.get("rows");
        log.info("exportAdjNgGrid");
        HttpSession session = request.getSession();
        session.setAttribute("state", null);
        // 生成提示信息，
        response.setContentType("application/vnd.ms-excel");
        String codedFileName = null;
        OutputStream fOut = null;
        try {
            // 进行转码，使其支持中文文件名
            codedFileName = java.net.URLEncoder.encode("科目汇总", "UTF-8");
            response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
            // response.addHeader("Content-Disposition", "attachment;   filename=" + codedFileName + ".xls");
            // 产生工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            //居中
            HSSFCellStyle cellStyle = workbook.createCellStyle();  //新建单元格样式
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

            HSSFSheet sheet = workbook.createSheet();
            sheet.setAutoFilter(CellRangeAddress.valueOf("A2:O2"));
            //第一个参数代表列id(从0开始),第2个参数代表宽度值  参考 ："2012-08-10"的宽度为2500
            for (int i = 0; i < 36; i++){
                sheet.setColumnWidth(i, 4000);
            }
            HSSFRow sheetRow0 = sheet.createRow(0);
            CellRangeAddress regionAudit = new CellRangeAddress(0, 0, (short) 3, 3+(short)auditCols.size()); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
            sheet.addMergedRegion(regionAudit);
            CellRangeAddress regionTax = new CellRangeAddress(0, 0, (short) 3+(short)auditCols.size()+4, 3+(short)auditCols.size()+4+taxCols.size()); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
            sheet.addMergedRegion(regionTax);
            HSSFCell cellAudit=sheetRow0.createCell(3);
            cellAudit.setCellValue("审计调整");
            cellAudit.setCellStyle(cellStyle);

            HSSFCell cellTax=sheetRow0.createCell(3+(short)auditCols.size()+4);
            cellTax.setCellValue("税务调整");
            cellTax.setCellStyle(cellStyle);

            //冻结表头
            sheet.createFreezePane(0, 2);

            HSSFRow sheetRow = sheet.createRow(1);

            int cellindex=0;
            HSSFCell cell = sheetRow.createCell((int) cellindex++);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("源科目代码");

            cell = sheetRow.createCell((int)  cellindex++);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("源科目名称");

            cell = sheetRow.createCell((int)  cellindex++);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue("期末余额");

            cell = sheetRow.createCell((int)  cellindex++);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue("审计调整额");

            //todo 审计调整单
            for(Map<String, Map> col: auditCols){
                String name=col.get("adjNumber")+("1".equals(col.get("isFreezed"))?"-已冻结":"");
                cell = sheetRow.createCell((int)  cellindex++);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(name);
            }

            cell = sheetRow.createCell((int)  cellindex++);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue("审计调整后金额");

            cell = sheetRow.createCell((int)  cellindex++);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("税务科目代码");

            cell = sheetRow.createCell((int)  cellindex++);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("税务科目名称");

            cell = sheetRow.createCell((int)  cellindex++);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue("税务调整额");

            //todo 税务调整单
            for(Map<String, Map> col: taxCols){
                String name=col.get("adjNumber")+("1".equals(col.get("isFreezed"))?"-已冻结":"");
                cell = sheetRow.createCell((int)  cellindex++);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(name);
            }

            cell = sheetRow.createCell((int)  cellindex++);//创建一列
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue("税务调整后金额");

            int rowindex=2;
            for (Map<String, Object> row : rows) {
                sheetRow = sheet.createRow(rowindex);
                cellindex=0;
                cell = sheetRow.createCell((int) cellindex++);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue((String) row.get("sourceAccountCode2"));

                cell = sheetRow.createCell((int)  cellindex++);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue((String) row.get("sourceAccountName2"));

                cell = sheetRow.createCell((int)  cellindex++);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                if(AssertHelper.notEmpty(row.get("endingBalance"))){
                    cell.setCellValue((Double) row.get("endingBalance"));
                }

                cell = sheetRow.createCell((int)  cellindex++);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                if(AssertHelper.notEmpty(row.get("auditAdjAmount"))) {
                    cell.setCellValue((Double) row.get("auditAdjAmount"));
                }

                //todo 审计调整单
                for(Map<String, Map> col: auditCols){
                    cell = sheetRow.createCell((int)  cellindex++);//创建一列
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(row.get(col.get("adjNumber"))==null?"0.00":row.get(col.get("adjNumber")).toString());
                }

                cell = sheetRow.createCell((int)  cellindex++);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                if(AssertHelper.notEmpty(row.get("afterAudAdjAmount"))) {
                    cell.setCellValue((Double) row.get("afterAudAdjAmount"));
                }
                cell = sheetRow.createCell((int)  cellindex++);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue((String) row.get("accountCode"));

                cell = sheetRow.createCell((int)  cellindex++);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue((String) row.get("accountName"));

                cell = sheetRow.createCell((int)  cellindex++);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                if(AssertHelper.notEmpty(row.get("taxAdjAmount"))) {
                    cell.setCellValue((Double) row.get("taxAdjAmount"));
                }

                //todo 税务调整单
                for(Map<String, Map> col: taxCols){
                    cell = sheetRow.createCell((int)  cellindex++);//创建一列
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(row.get(col.get("adjNumber"))==null?"0.00":row.get(col.get("adjNumber")).toString());
                }

                cell = sheetRow.createCell((int)  cellindex++);//创建一列
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                if(AssertHelper.notEmpty(row.get("afterTaxAdjAmount"))) {
                    cell.setCellValue((Double) row.get("afterTaxAdjAmount"));
                }

                rowindex++;
            }

            fOut = response.getOutputStream();
            workbook.write(fOut);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                fOut.flush();
                fOut.close();
            } catch (Exception e) {
            }
            session.setAttribute("state", "open");
        }
        System.out.println("文件生成...");
    }

excel模板填充数据后下载

package com.deloitte.tms.vat.vat.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.deloitte.tms.vat.srv.affirm.entity.TMSMDTrxAffSet;
import com.deloitte.tms.vat.srv.affirm.service.TMSMDTrxAffSetService;
import com.deloitte.tms.vat.srv.base.entity.BaseOrg;
import com.deloitte.tms.vat.srv.base.service.BaseOrgService;
import com.deloitte.tms.vat.srv.codelist.YesNoCodeListModel;

import net.ibizsys.paas.db.ISelectCond;
import net.ibizsys.paas.db.SelectCond;
import net.ibizsys.paas.service.ServiceGlobal;
import net.ibizsys.paas.util.StringHelper;
import net.ibizsys.paas.util.WebUtility;
import net.ibizsys.paas.web.Page;

/**
 * 导出文件处理页面
 * 
 * @author Administrator
 * 
 */
public class ExportFilePageReqHXM extends Page {
	public ExportFilePageReqHXM() {

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see SA.SRFramework.WebEx.SRFExPage#onInit()
	 */
	@Override
	protected void onInit() throws Exception {
		super.onInit();

		String strFileName1 = "vat" + File.separator + "my" + File.separator + "templ" + File.separator + "xm"
				+ File.separator + "req.xlsm";
		;

		HttpServletRequest req = this.getWebContext().getRequest();
		String realPath = req.getSession().getServletContext().getRealPath("/");
		String strTempFilePath1 = StringHelper.format("%1$s%2$s", realPath, strFileName1);

		String userAge = req.getHeader("user-agent").toLowerCase();
		if (userAge.contains("msie") || userAge.contains("like gecko")) {
			strFileName1 = URLEncoder.encode(strFileName1, "UTF-8");
		} else {
			strFileName1 = new String(strFileName1.getBytes("UTF-8"), "iso-8859-1");
		}

		downloadFile(strTempFilePath1, strFileName1, this.getResponse());

		// SmartUpload su = new SmartUpload();
		// su.initialize(this.getPageContext());
		// // su.downloadFile(strTempFilePath,"",strNewFileName);
		// su.setContentDisposition(null);
		//
		// su.downloadFile(strTempFilePath, "", strNewFileName);
	}

	/**
	 * 文件下载
	 * 
	 * @param filePath
	 * @param response
	 */
	public static void downloadFile(String filePath, String fileName, HttpServletResponse response) {
		InputStream inputStream = null;
		OutputStream outputStream = null;
		try {
			outputStream = response.getOutputStream();
			File file = new File(filePath);
			if (file != null && file.isFile() && file.canRead()) {
				response.setCharacterEncoding("utf-8");

				response.setContentType(WebUtility.getHttpContentType(fileName.substring(fileName.lastIndexOf("."))));
				response.setHeader("Pragma", "No-cache");
				response.setHeader("Cache-Control", "no-cache");
				response.setDateHeader("Expires", 0);
				// response.setHeader("Content-Disposition", "attachment;fileName="+new
				// String(fileName.getBytes("GBK"),"iso8859-1"));
				fileName = WebUtility.encodeURLParamValue("手工批量开票导入模板转换工具.xlsm");

				response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
				response.setHeader("Cache-Control", "max-age=0");
				try {
					XSSFWorkbook workbook = null;
					XSSFSheet sheet = null;
					inputStream = new FileInputStream(file);
					workbook = new XSSFWorkbook(inputStream);// 创建个workbook，
					// 获取第一个sheet
					//物料-商品服务编码
					sheet = workbook.getSheetAt(1);
					if (sheet != null) {
						List<TMSMDTrxAffSet> entities=getAllTMSMDTrxAffSets();
						int i=1;// 从第2行开始
						for(TMSMDTrxAffSet entity:entities) {
							// 根据excel模板格式写入数据....
							// RENT00001 租赁费 0.17 是 304050202990000001 有形动产经营租赁服务
							int j = 0;
							XSSFRow row = sheet.getRow(i); 
							if(row==null) {
								row=sheet.createRow(i);
							}
							createRowAndCell(entity.getTaxTrxTypeCode(), row, j++);
							createRowAndCell(entity.getTaxTrxTypeName(), row, j++);
							createRowAndCell(entity.getTaxRate(), row, j++);
							createRowAndCell(YesNoCodeListModel.ITEM_0.equals(entity.getIsTax())?"否":"是", row, j++);
							createRowAndCell(entity.getInventoryItemNumber(), row, j++);
							createRowAndCell(entity.getInventoryItemDescription(), row, j++);
							i++;
						}
					}
					//机构
					sheet = workbook.getSheetAt(5);
					if (sheet != null) {
						List<BaseOrg> orgs=getAllOrgs();
						int i=0;// 从第1行开始
						for(BaseOrg entity:orgs) {
							XSSFRow row = sheet.getRow(i); 
							if(row==null) {
								row=sheet.createRow(i);
							}
							// 根据excel模板格式写入数据....
							// RENT00001 租赁费 0.17 是 304050202990000001 有形动产经营租赁服务
							int j = 0;
							createRowAndCell(entity.getBaseorgname(), row, j++);
							createRowAndCell(entity.getOrgCode(), row, j++);
							i++;
						}
					}
					workbook.write(outputStream);
				}catch (Exception e) {
					System.err.println("模板载入数据错误,使用原始模板:"+e.getMessage());
					inputStream = new FileInputStream(file);
					byte[] b = new byte[1024];
					int length;
					while ((length = inputStream.read(b)) > 0) {
						outputStream.write(b, 0, length);
					}
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (inputStream != null) {
					inputStream.close();
				}
				if (outputStream != null) {
					outputStream.close();
				}

			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	static List<BaseOrg> getAllOrgs() throws Exception{
		BaseOrgService orgService=(BaseOrgService) ServiceGlobal.getService(BaseOrgService.class);
		ISelectCond cond=new SelectCond();
		ArrayList<BaseOrg> orgs=orgService.select(cond);
		return orgs;
	}

	static List<TMSMDTrxAffSet> getAllTMSMDTrxAffSets() throws Exception{
		TMSMDTrxAffSetService service=(TMSMDTrxAffSetService) ServiceGlobal.getService(TMSMDTrxAffSetService.class);
		ISelectCond cond=new SelectCond();
		ArrayList<TMSMDTrxAffSet> results=service.select(cond);
		return results;
	}

	public static void main(String[] args) {
		String file = "C:\\Users\\jasonbwang\\Desktop\\手工批量开票导入模板转换工具 (1).xlsm";
		InputStream is = null;
		XSSFWorkbook workbook = null;
		XSSFSheet sheet = null;
		FileOutputStream outputStream = null;
		try {
			outputStream = new FileOutputStream("C:\\Users\\jasonbwang\\Desktop\\手工批量开票导入模板转换工具2.xlsm");
		} catch (FileNotFoundException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}
		try {
			is = new FileInputStream(file);// 将excel文件转为输入流
			workbook = new XSSFWorkbook(is);// 创建个workbook，
			// 获取第一个sheet
			sheet = workbook.getSheetAt(5);
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		if (sheet != null) {
			try {
				XSSFCell cell =null;
				XSSFRow row = sheet.getRow(12); // 从第三行开始
				if(row==null) {
					row=sheet.createRow(12);
				}
				// 根据excel模板格式写入数据....
				// RENT00001 租赁费 0.17 是 304050202990000001 有形动产经营租赁服务
				int j = 0;
				createRowAndCell("RENT00001", row,  j++);
				createRowAndCell("租赁费", row,  j++);
				createRowAndCell("0.17", row, j++);
				createRowAndCell("是", row, j++);
				createRowAndCell("304050202990000001", row, j++);
				createRowAndCell("有形动产经营租赁服务", row, j++);

				workbook.write(outputStream);
				outputStream.flush();
				outputStream.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
	}

	/**
	 * 根据当前row行，来创建index标记的列数,并赋值数据
	 */
	private static void createRowAndCell(Object obj, XSSFRow row, int index) {
		XSSFCell cell = row.getCell(index);
		if (cell == null) {
			cell = row.createCell(index);
		}

		if (obj != null)
			cell.setCellValue(obj.toString());
		else
			cell.setCellValue("");
	}
}

示例2

@RequestMapping(value = "/exportConfigPort", method = RequestMethod.GET)
    public void exportConfigPort(HttpServletRequest request, HttpServletResponse response) throws Exception {
        HttpSession session = request.getSession();
        session.setAttribute("state", null);
        // 生成提示信息，
        response.setContentType("application/vnd.ms-excel");
        String codedFileName = null;
        OutputStream fOut = null;
        try {
            // 进行转码，使其支持中文文件名
            codedFileName = java.net.URLEncoder.encode("端口配置清单", "UTF-8");
            response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
            // response.addHeader("Content-Disposition", "attachment;   filename=" + codedFileName + ".xls");
            // 产生工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            //居中
            HSSFCellStyle cellStyle = workbook.createCellStyle();  //新建单元格样式
            cellStyle.setAlignment(HorizontalAlignment.CENTER);

            HSSFSheet sheet = workbook.createSheet();
            //第一个参数代表列id(从0开始),第2个参数代表宽度值  参考 ："2012-08-10"的宽度为2500
            for (int i = 0; i < 5; i++) {
                sheet.setColumnWidth(i, 4000);
            }

            HSSFRow sheetRow = sheet.createRow(0);

            int cellindex = 0;
            HSSFCell cell = sheetRow.createCell((int) cellindex++);//创建一列
            cell.setCellType(CellType.STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue("纳税主体");

            cell = sheetRow.createCell((int) cellindex++);//创建一列
            cell.setCellType(CellType.STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue("纳税人识别号");

            cell = sheetRow.createCell((int) cellindex++);//创建一列
            cell.setCellType(CellType.STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue("U-Key助手设备编号");

            cell = sheetRow.createCell((int) cellindex++);//创建一列
            cell.setCellType(CellType.STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue("U-Key设备端口");

            cell = sheetRow.createCell((int) cellindex++);//创建一列
            cell.setCellType(CellType.STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue("批次号");

            int rowindex = 1;
            List<Port> ports = getConfig().getPorts();
            for (Port port : ports) {
                sheetRow = sheet.createRow(rowindex);
                cellindex = 0;
                cell = sheetRow.createCell(cellindex++);//创建一列
                cell.setCellType(CellType.STRING);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(port.getTaxPayerName());

                cell = sheetRow.createCell(cellindex++);//创建一列
                cell.setCellType(CellType.STRING);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(port.getTaxPayerCode());

                cell = sheetRow.createCell(cellindex++);//创建一列
                cell.setCellType(CellType.STRING);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(port.getSerialNo());

                cell = sheetRow.createCell(cellindex++);//创建一列
                cell.setCellType(CellType.STRING);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(port.getSerialPort());

                cell = sheetRow.createCell(cellindex++);//创建一列
                cell.setCellType(CellType.STRING);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(port.getBatchNo());

                rowindex++;
            }

            fOut = response.getOutputStream();
            workbook.write(fOut);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                fOut.flush();
                fOut.close();
            } catch (Exception e) {
            }
            session.setAttribute("state", "open");
        }
    }

ExcelResultAction

package com.ling2.onlinedoc.excel.view.action;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import com.deloitte.tr.project.model.ItemEntity;
import com.itextpdf.text.pdf.AcroFields.Item;
import com.ling2.core.commons.exception.BusinessException;
import com.ling2.core.commons.utils.AssertHelper;
import com.ling2.core.controller.IController;
import com.ling2.onlinedoc.excel.model.KeySumInParam;
import com.ling2.onlinedoc.excel.model.NotKeySelectInfoInParam;
import com.ling2.onlinedoc.excel.service.KeySumService;
import com.ling2.onlinedoc.excel.service.NotKeySelectInfoService;

@Component
public class ExcelResultAction implements IController {
	
	@Resource
	NotKeySelectInfoService notKeySelectInfoService;
	@Resource
	KeySumService keySumService;
	
	@Override
	public boolean anonymousAccess() {
		return false;
	}

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response)
			throws IOException, ServletException {
		
		
		String businessId=request.getParameter("businessId");
		if(AssertHelper.empty(businessId)){
			erro(response, new BusinessException("业务代码丢失"));
		}
		ItemEntity entity=(ItemEntity) keySumService.get(ItemEntity.class, Long.parseLong(businessId));
		
		try {
			OutputStream os=response.getOutputStream();
			response.reset();// 清空输出流   
		    response.setHeader("Content-disposition", "attachment; filename="+entity.getCustomerCode()+"_Testing Result.xlsx");// 设定输出文件头   
		    response.setContentType("application/msexcel");// 定义输出类型
		    
		    //读取模板
//		    String fromfile="template1.0.xlsx";
//			FileInputStream tin=new FileInputStream(fromfile);
			InputStream tin=getClass()
					.getResourceAsStream("template2.0.xlsx");
			XSSFWorkbook wb=new XSSFWorkbook(tin);
			
			//关键字
			Map<String, Object> params=new HashMap<String, Object>();
			AssertHelper.notEmpty_assert(businessId,"查询业务主键不能为空");
			params.put("businessGroup", "TRKEY");
			params.put("businessId", businessId);
			List<KeySumInParam> keyresults=keySumService.findKeySumByParams(params);
			
			//非关键字
			List<NotKeySelectInfoInParam> notkeyresults=notKeySelectInfoService.findNotKeySelectInfoByParams(params);
			
			//替换关键字公式
			Sheet keysheet=wb.getSheetAt(0);
			for (Iterator rowIt = keysheet.rowIterator(); rowIt.hasNext();) {  
				XSSFRow row = (XSSFRow) rowIt.next();  
	            for (Iterator cellIt = row.cellIterator(); cellIt.hasNext();) {  
	                XSSFCell cell = (XSSFCell) cellIt.next();  
	                // 不同数据类型处理  
	                int cellType = cell.getCellType();  
	                if (cellType == XSSFCell.CELL_TYPE_STRING) {
	                	XSSFRichTextString richtext=cell.getRichStringCellValue();
	                	String text=richtext.getString();
	                	if(text.indexOf("$")>=0){
	                		for(KeySumInParam key:keyresults){
	                			text=text.replace("${"+key.getKeyname()+"}", key.getValue().toString());
	                		}
	                		if(text.indexOf("$")>=0){
	                			richtext.setString(null);
	                			cell.setCellValue(richtext);
	                		}else{
	                			formatNumberCell(cell,wb);
		                		cell.setCellValue(Double.parseDouble(text));
	                		}
	                		
	                	}
                    }   
	            }  
	        } 
			//输出非关键字表格
			XSSFSheet notkeysheet;
			if(wb.getNumberOfSheets()>1){
				notkeysheet=wb.getSheetAt(1);
			}else{
				notkeysheet=wb.createSheet("非关键字");
			}
			int rowindex=notkeysheet.getLastRowNum();
			if(rowindex==0){
				XSSFRow row=notkeysheet.createRow(rowindex);
				XSSFCell cell0=row.createCell(0);
				cell0.setCellValue("非关键字");
				
				XSSFCell cell1=row.createCell(1);
				cell1.setCellValue("期初");
				
				XSSFCell cell2=row.createCell(2);				
				cell2.setCellValue("期末");
				
				XSSFCell cell3=row.createCell(3);				
				cell3.setCellValue("本期调增");
				
				XSSFCell cell4=row.createCell(4);				
				cell4.setCellValue("本期调减");
			}
			rowindex++;			
			for(NotKeySelectInfoInParam infoInParam:notkeyresults){
				XSSFRow row=notkeysheet.createRow(rowindex++);
				
				if(infoInParam.getText()!=null){
					XSSFCell cell0=row.createCell(0);
					cell0.setCellValue(infoInParam.getText());
					formatNumberCell(cell0,wb);
				}
				
				if(infoInParam.getValue1()!=null){
					XSSFCell cell1=row.createCell(1);
					cell1.setCellValue(infoInParam.getValue1());
					formatNumberCell(cell1,wb);
				}
				
				if(infoInParam.getValue2()!=null){
					XSSFCell cell2=row.createCell(2);				
					cell2.setCellValue(infoInParam.getValue2());
					formatNumberCell(cell2,wb);
				}
				
				if(infoInParam.getValue3()!=null){
					XSSFCell cell3=row.createCell(3);				
					cell3.setCellValue(infoInParam.getValue3());
					formatNumberCell(cell3,wb);
				}
				if(infoInParam.getValue4()!=null){
					XSSFCell cell4=row.createCell(4);				
					cell4.setCellValue(infoInParam.getValue4());
					formatNumberCell(cell4,wb);
				}
			}			
			XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
			//完成,关闭流
			wb.write(os);// 写入文件  
			tin.close();  
			os.close(); // 关闭流
		} catch (Exception e) {
			e.printStackTrace();
			erro(response, e);
		}
	}
	private void formatNumberCell(XSSFCell cell,XSSFWorkbook wb){
		XSSFCellStyle style=cell.getCellStyle();
		if(style==null){
			style=wb.createCellStyle();
		}
    	XSSFDataFormat format=wb.createDataFormat();
    	style.setDataFormat(format.getFormat("#,##0"));
		cell.setCellStyle(style);
	}
	@Override
	public String getUrl() {
		return "/excelresult";
	}

	@Override
	public boolean isDisabled() {
		return false;
	}
	private void erro(HttpServletResponse response,Exception e) throws IOException {
		response.reset();// 清空输出流   
		response.setCharacterEncoding("UTF-8");
		response.setContentType("text/html;charset=utf8");
		StringBuffer sb=new StringBuffer();
		sb.append("<html>\n");
		sb.append("<head>\n");
		sb.append("<META http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">\n");
		sb.append("</head>\n");
		sb.append("<body class=\"b1\">\n");
		sb.append("<table style=\"width:100%;border:solid 1px\">");
		sb.append("<tr style=\"background:#DDDDDD\">");
		sb.append("<td>");
		sb.append("<strong>"+e.getMessage()+"</strong>");
		sb.append("</td>");
		sb.append("</tr>");
		sb.append("</table>");
		sb.append("</body>\n");
		sb.append("</html>\n");
		response.setCharacterEncoding("UTF-8");
		response.setContentType("text/html;charset=utf8");
		PrintWriter pw=response.getWriter();
		pw.write(sb.toString());
		pw.flush();
		pw.close();
	}
}