1.首先写一个导出Excel的工具类和一个Resonse工具类
package util; import java.io.InputStream; import java.sql.ResultSet; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; public class ExcelUtil { public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{ int rowIndex=0; Sheet sheet=wb.createSheet(); Row row=sheet.createRow(rowIndex++); for(int i=0;i<headers.length;i++){ row.createCell(i).setCellValue(headers[i]); } while(rs.next()){ row=sheet.createRow(rowIndex++); for(int i=0;i<headers.length;i++){ row.createCell(i).setCellValue(rs.getObject(i+1).toString()); } } } public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{ InputStream inp=ExcelUtil.class.getResourceAsStream("/template/"+templateFileName); POIFSFileSystem fs=new POIFSFileSystem(inp); Workbook wb=new HSSFWorkbook(fs); Sheet sheet=wb.getSheetAt(0); // 获取列数 int cellNums=sheet.getRow(0).getLastCellNum(); int rowIndex=1; while(rs.next()){ Row row=sheet.createRow(rowIndex++); for(int i=0;i<cellNums;i++){ row.createCell(i).setCellValue(rs.getObject(i+1).toString()); } } return wb; } public static String formatCell(HSSFCell hssfCell){ if(hssfCell==null){ return ""; }else{ if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){ return String.valueOf(hssfCell.getBooleanCellValue()); }else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){ return String.valueOf(hssfCell.getNumericCellValue()); }else{ return String.valueOf(hssfCell.getStringCellValue()); } } } public static String getCell(HSSFCell cell) { DecimalFormat df = new DecimalFormat("#"); if (cell == null) return ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if(HSSFDateUtil.isCellDateFormatted(cell)){ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString(); } return df.format(cell.getNumericCellValue()); case HSSFCell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); return cell.getStringCellValue(); case HSSFCell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case HSSFCell.CELL_TYPE_BLANK: return ""; case HSSFCell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() + ""; case HSSFCell.CELL_TYPE_ERROR: return cell.getErrorCellValue() + ""; } return ""; } /*public static String formateDate(HSSFWorkbook wb,HSSFCell hssfCell){ //HSSFWorkbook wb = new HSSFWorkbook(); CreationHelper createHelper=wb.getCreationHelper(); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss")); //HSSFSheet sheet = wb.createSheet("format sheet"); //HSSFDataFormat format = wb.createDataFormat(); //HSSFRow row = sheet.createRow(0); //HSSFCell cell = row.createCell(0); //cell = row.getCell(2); hssfCell.setCellValue(formatCell(hssfCell)); hssfCell.setCellStyle(cellStyle); return hssfCell.getStringCellValue(); } */ }
-------------------------------------------------------------------------------------
package util; import java.io.OutputStream; import java.io.PrintWriter; import javax.servlet.http.HttpServletResponse; import org.apache.poi.ss.usermodel.Workbook; public class ResponseUtil { public static void write(HttpServletResponse response,Object o) throws Exception{ response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter(); out.println(o.toString()); out.flush(); out.close(); } public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{ response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1")); response.setContentType("application/ynd.ms-excel;charset=UTF-8"); OutputStream out=response.getOutputStream(); wb.write(out); out.flush(); out.close(); } }
2.调用其中的方法
public String export() throws Exception{ Connection con = null; try { con = dbUtil.getCon(); Workbook wb=ExcelUtil.fillExcelDataWithTemplate(exportDao.exportData(con), "exportTemp.xls"); ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls"); } catch (Exception e) { e.printStackTrace(); } return null; }
