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

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

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

var url=productPath + "/CtrlService"+"?action=ZcjqrGlfExport&UserName="+ username+"&FILENAME="+filePath;

window.open(url);

第二步：

publicObject doCtrlService(StubObject arg0, JParamObject arg1, HttpServletRequest req,

HttpServletResponse resp)throwsException {

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;

}throwe;

}

}catch(Exception e) {

String errMsg= "异常";

logger.error(errMsg,e);

ServiceException se= newServiceException(e.getMessage());

se.setErrorCode(-502);

se.setErrorMessage(errMsg);throwse;

}return null;

}private void onError(HttpServletResponse resp, String msg) throwsException {

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) throwsException {

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 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

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 voiddeleteExcelBeforeExport(String path, String username) {

File file= newFile(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= newFile(deleteFilePath);

deleteFile.delete();

}

}

}

}

}

}

结束

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