java读写Excel模板文件，应用于负载均衡多个服务器

首先，需要大家明白一点，对于多服务器就不能用导出文件用a标签访问链接方式去导出excel文件了，原因相信大家也明白，可能也做过尝试。

现在开始第一步：get请求，productPath 为你的项目路径

var url=productPath + "/CtrlService"+"?action=ZcjqrGlfExport&UserName="+ username+"&FILENAME="+ filePath;
window.open(url);

第二步：

    public Object doCtrlService(StubObject arg0, JParamObject arg1, HttpServletRequest req,
            HttpServletResponse resp)
            throws Exception {
        String filePath = req.getParameter("FILENAME");
        try {
            JParamObject PO=JParamObject.getInstance();
            PO.SetValueByParamName("filePath", filePath);
            PO.setEnvValue("DBNO", "FMIS_DB01");
            PO.setEnvValue("DataBaseName", "FMIS_DB01");
            JResponseObject RO=(JResponseObject) EAI.DAL.SVR("FMIS_ZCJQR_GLFFT_EXPORT", PO);
            String fileName=(String)RO.getResponseObject("fileName");
            byte[] fileBytes=(byte[])RO.getResponseObject("fileBytes");
            if(fileBytes==null){
                return null;
            }
            setResponseHeader(resp,fileName,".xls");
            ServletOutputStream  out=resp.getOutputStream();
            try{
                out.write(fileBytes);
                out.close();
            }catch(Exception e){
                if(e.getClass().getName().equals("org.apache.catalina.connector.ClientAbortException")){
                    logger.info("客户端终止操作");
                    return null;
                }
                throw e;
            }
        } catch (Exception e) {
            String errMsg = "异常";
            logger.error(errMsg,e);
            ServiceException se = new ServiceException(e.getMessage());
            se.setErrorCode(-502);
            se.setErrorMessage(errMsg);
            throw se;
        }         
        return null;
    }
    
    
    private void onError(HttpServletResponse resp, String msg) throws Exception {
        resp.setHeader("Content-type", "text/html;charset=UTF-8");
        resp.setCharacterEncoding("UTF-8");
        resp.getWriter().print(msg);
    }
    
    private void setResponseHeader(HttpServletResponse resp, String fileName, String fileType) throws Exception {

        String fileNameEncode = java.net.URLEncoder.encode(fileName, "UTF-8");
        String[][] contentType = 
            { 
                {".xls","application/x-xls"}
                 };
        for (int i = 0; i < contentType.length; i++) {
            if (contentType[i][0].equals(fileType)) {
                resp.setContentType(contentType[i][1]);
                break;
            }
        }
        resp.addHeader("Content-Disposition", "attachment;filename=" + fileNameEncode);
        return;

    }

第三步：读取模板文件，并输出文件流

	private byte[]  fileExport(JConnection conn, JParamObject PO) {
		InputStream io;
		String  path=EAI.LocalUserHome;
		String modelfilepath=path+"importTemp"+File.separator+"Glfftmodel.xls";
		String filename=TempleNameUtils.createTempTableName("glfmx_");
		String outputPath=path+"exporttemp"+File.separator+filename+".xls";
		ByteArrayOutputStream outputStream = null;
		byte[] bytes = new byte[0];
		deleteExcelBeforeExport(path+"exporttemp","glfmx");
		try{
		//这里读出来的workbook作为模版
		io = new FileInputStream(new File(modelfilepath));
		HSSFWorkbook workbook = new HSSFWorkbook(io);
		HSSFSheet sheet=workbook.getSheetAt(0);
		List<Glfftbean> listg=QueryGlfftList(conn);

		HSSFCellStyle cellStyle = workbook.createCellStyle(); // 单元格样式
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		 
		for(int i=0;i<listg.size();i++){
			Glfftbean glfftbean=listg.get(i);
			System.out.println(i);
			String xmbh= glfftbean.getXmbh();
			HSSFRow row=sheet.getRow(i+9);//第10行开始
			if (row == null) {
				row = sheet.createRow(i+9);
				row.createCell(0).setCellValue(xmbh);
				row.createCell(1).setCellValue(glfftbean.getXmmc());
				row.createCell(2).setCellValue(glfftbean.getQmzjgc());
				row.createCell(3).setCellValue(glfftbean.getZcbh());
				row.createCell(4).setCellValue(glfftbean.getZcmc());
				row.createCell(5).setCellValue(glfftbean.getCbzxbh());
				row.createCell(6).setCellValue(glfftbean.getCbzxmc());
				row.createCell(7).setCellValue(glfftbean.getFtje());
				row.createCell(8).setCellValue(glfftbean.getQzfzcbh());
			}else{
				row.getCell(0).setCellValue(xmbh);
				row.getCell(1).setCellValue(glfftbean.getXmmc());
				row.getCell(2).setCellValue(glfftbean.getQmzjgc());
				row.getCell(3).setCellValue(glfftbean.getZcbh());
				row.getCell(4).setCellValue(glfftbean.getZcmc());
				row.getCell(5).setCellValue(glfftbean.getCbzxbh());
				row.getCell(6).setCellValue(glfftbean.getCbzxmc());
				row.getCell(7).setCellValue(glfftbean.getFtje());
				row.getCell(8).setCellValue(glfftbean.getQzfzcbh());
			}
			for (Cell cell : row) {
				 cell.setCellStyle(cellStyle);
				}
			
		}
			File file= new File(outputPath);
			FileOutputStream fo=new FileOutputStream(file);
			workbook.write(fo);
			workbook.close();
			long fileSize = file.length();
			FileInputStream in = new FileInputStream(file);
			byte[] buffer = new byte[(int) fileSize];
			while ((fileSize = in.read(buffer)) != -1) {
				return buffer;
			}
			in.close();
			return buffer;
		}catch (Exception e){
			String errMsg = "创建文件流失败";
			logger.error(errMsg, e);
			ServiceException se = new ServiceException(e.getMessage());
			se.setErrorCode(-1);
			se.setErrorMessage(errMsg);
		}
		return bytes;
	}

删除相关文件：

public static void deleteExcelBeforeExport(String path, String username) {
        File file = new File(path);
        File[] fileList = file.listFiles();
        if (fileList != null) {
            for (int i = 0; i < fileList.length; i++) {
                File fs = fileList[i];
                if (!fs.isDirectory()) {
                    String[] fileNames = fs.getName().split("_");
                    if (fileNames.length > 1) {
                        String fileUserName = fileNames[0];
                            // --删除包含该用户名的并且后缀的开头为
                            if (fileUserName.equals(username)) {
                                String deleteFilePath = path + File.separator + fs.getName();
                                File deleteFile = new File(deleteFilePath);
                                deleteFile.delete();
                            }
                    }
                }
            }
        }

    }

结束

思路总结：需要生成文件 ->读取文件 -> 输出文件流 -> 删除文件

属作者原创，如有转载，请表明出处：https://www.cnblogs.com/mobeisanghai/p/12191374.html