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;

    }

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

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