1、ExportExcel.java package com.rjsoft.ccss.bbs.web.excel; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.Region; import com.rjsoft.ccss.bbs.model.SurveyQuestionV; import com.rjsoft.ccss.pubs.common.export.ExportExcelFile; import com.rjsoft.ccss.pubs.common.export.FileDownloader; public class ExportExcel { private static final Log log = LogFactory.getLog(ExportExcelFile.class); private static final int EXCEL_MAX_ROW = 65000; public void getExcelList(HttpServletRequest request, HttpServletResponse response, List list, String name) throws Exception { //Excel最大能导出的行数限制 String maxRowStr = "65000"; int maxRow = this.parseInt(maxRowStr); //如果导出的数据超过最大数,则给提示信息 if (list != null && list.size() > maxRow) { String str1 = "不能超过最大导出数"; StringBuffer sb = new StringBuffer( "<mce:script language=/"javascript/"><!-- alert('").append(str1) .append("'); // --></mce:script>"); request.setAttribute("alertRow", sb.toString()); } String fileName = null; fileName = name + ".xls"; //调用创建文件excel的方法 createExcel(list, fileName); // exprotExcel(request, response, fileName); } // 将配置中的字符串转成数字,并与默认的最大行数比较,取最小值 private int parseInt(String str) { int value = 0; try { value = Integer.parseInt(str); } catch (Exception e) { // log.error("将[" + str + "]转成数字出错!", e); } return value > EXCEL_MAX_ROW ? EXCEL_MAX_ROW : value; } // 创建Excel文件 private void createExcel(List list, String fileName) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row = null; HSSFCell cell = null; // 设置字符内容为文本的style HSSFCellStyle style = workbook.createCellStyle(); style.setDataFormat((short) 49); // 居中对齐的style HSSFCellStyle style2 = workbook.createCellStyle(); style2.setDataFormat((short) 49); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // sheet.addMergedRegion(getRegion(0, 0, 0, // ((String[]) list.get(0)).length - 1)); // sheet.addMergedRegion(getRegion(1, 0, 1, // ((String[]) list.get(0)).length - 1)); // 第一行 // row = sheet.createRow(0); // cell = row.createCell((short) 0); // cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16); // cell.setCellStyle(style2); // cell.setCellValue(date); // // 第二行 // row = sheet.createRow(1); // cell = row.createCell((short) 0); // cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16); // cell.setCellStyle(style2); // cell.setCellValue("操作员:" + userName); // 数据 int subjectId = 0; String subjectName = ""; short j = 0; short n = 0; for (int i = 0; i < list.size(); i++) { SurveyQuestionV sqv = (SurveyQuestionV) list.get(i); if(sqv.getSubjectId().intValue() == subjectId) { cell = row.createCell((short) j); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16); cell.setCellStyle(style); cell.setCellValue(sqv.getContentName()+"("+sqv.getSurContentCount()+" 票)"); j++; }else{ j = 0; row = sheet.createRow(n); n++; subjectId = sqv.getSubjectId(); subjectName = sqv.getSubjectName(); cell = row.createCell((short) j); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16); cell.setCellStyle(style); cell.setCellValue(subjectName); j++; cell = row.createCell((short) j); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16); cell.setCellStyle(style); cell.setCellValue(sqv.getContentName()+"("+sqv.getSurContentCount()+" 票)"); j++; } // row = sheet.createRow(i + 2); /*row = sheet.createRow(i); for (int j = 0; j < arr.length; j++) { cell = row.createCell((short) j); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16); cell.setCellStyle(style); cell.setCellValue(arr[j]); }*/ } FileOutputStream fOut = new FileOutputStream("../" + fileName); workbook.write(fOut); fOut.flush(); fOut.close(); } // 得到合并 private Region getRegion(int startRow, int startCol, int endRow, int endCol) { return new Region((short) startRow, (short) startCol, (short) endRow, (short) endCol); } /* * 导出Excel文件 * * @param request @param response @param fileName */ private void exprotExcel(HttpServletRequest request, HttpServletResponse response, String fileName) { response.addHeader("Content-Disposition", "attachment;filename=" + fileName); InputStream dataStream = null; String filePath = "../" + fileName; File file = new File(filePath); try { if (fileName != null) { dataStream = new FileInputStream(filePath); String contentType = "application/vnd.ms-excel"; FileDownloader fd = new FileDownloader(); fd.setIsDownload(false); fd.setFileName(fileName); fd.setContentType(contentType); fd.setInputStream(dataStream); fd.setRequest(request); fd.setResponse(response); fd.operate(); } else { response.flushBuffer(); } } catch (Exception e) { log.error("", e); } finally { try { file.delete(); if (fileName != null && fileName != "") { dataStream.close(); } } catch (Exception ignore) { log.error("删除文件出错", ignore); } finally { dataStream = null; } } } } 2、FileDownLoader.java /* * @(#)FileDownloader.java * * Copyright (c) 2003 DCIVision Ltd * All rights reserved. * * This software is the confidential and proprietary information of DCIVision * Ltd ("Confidential Information"). You shall not disclose such Confidential * Information and shall use it only in accordance with the terms of the license * agreement you entered into with DCIVision Ltd. */ package com.rjsoft.ccss.pubs.common.export; import java.io.ByteArrayInputStream; import java.io.IOException; import java.io.InputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; /** * <p>Class Name: FileDownloader.java </p> * <p>Description: FileDownloader handles the document downloading from DMS. </p> * * @author * @company DCIVision Limited * @creation date 01/08/2003 * @version $Revision: 1.12.8.7 $ */ public class FileDownloader { public static final String REVISION = "$Revision: 1.12.8.7 $"; public static int DATA_BLOCK_SIZE = 8192; protected Log log = LogFactory.getLog(this.getClass().getName()); protected HttpServletResponse response; protected HttpServletRequest request; protected String filename = ""; protected InputStream attachment; //InputStream for temporarily storing attachment protected InputStream testStream; protected String contentType; protected byte[] b; protected InputStream dataStream; protected boolean isDownload = false; public FileDownloader() { } public void setIsDownload(boolean flag) { this.isDownload = flag; } public void setResponse(HttpServletResponse response) { this.response = response; } public void setRequest(HttpServletRequest request) { this.request = request; } public void setFileName(String filename) { try { this.filename = filename; // this.filename = URLDecoder.decode(filename, "UTF-8"); } catch (Exception e) { e.printStackTrace(); } } public void setContentType(String contentType) { this.contentType = contentType; } public void setBytesContent(byte[] b) { this.b = b; } public void setInputStream(InputStream dataStream) { this.dataStream = dataStream; } public InputStream getInputStream() { if (b != null) { return new ByteArrayInputStream(b); } else { return dataStream; } } public InputStream getTestStream() { return testStream; } public void setTestStream(InputStream testStream) { this.testStream = testStream; } /** * Sets the HttpServletResponse for downloading and the javax.servlet.ServletOutputStream * for downloading. * * @throws ApplicationException * @see com.dcivision.framework.ErrorConstant */ public void operate() throws Exception { InputStream inputStream = null; try { //filename = TextUtility.replaceString(filename, "+", "%20"); response.addHeader("Content-Transfer-Encoding", "base64"); if (this.isDownload) { response.addHeader("Content-Disposition", "attachment; filename=/"" + new String(filename.getBytes("gbk"),"gbk") + "/""); //addheader so that filename can auto put into the download dialog box } else { response.addHeader("Content-Disposition", "inline; filename=/"" + new String(filename.getBytes("gbk"),"gbk") + "/""); //addheader so that filename can auto put into the download dialog box } System.out.println(new String(filename.getBytes("gbk"),"gbk")); /* * Update Code End */ response.setContentType(contentType);//set the file type default unrecognized so that other browser can download int bufferSize = 8192; byte[] buffer = new byte[bufferSize]; int length = -1; inputStream = this.getInputStream(); java.io.OutputStream outputStream = response.getOutputStream(); while ((length = inputStream.read(buffer, 0,bufferSize)) != -1) { outputStream.write(buffer,0,length); } outputStream.flush(); outputStream.close(); }catch (Exception e) { log.error(e, e); } finally { try { inputStream.close(); } catch (IOException ignore) {} } } }