我们使用的导出并下载功能是利用一个插件叫POI的插件提供的导出功能，很实用；首先先导入Jar包：
Jar包下载地址： 官方文档地址：
Action代码：
public void exportToExcel(Listdata) throws Exception { this.setEnableAccessRequest(true); this.setEnableAccessResponse(true); HttpServletRequest request = this.getRequest(); HttpServletResponse response = this.getResponse(); String randomNumber = request.getParameter("randomNumber");// session名称 try { session = request.getSession(); session.setAttribute(randomNumber, new Double(1)); // 导出的EXCEL文件名 String exportFileName = "addressBook.xlsx"; response.reset(); response.setContentType("octets/stream"); // response.setHeader("Content-Disposition","attachment;filename="+exportFileName); response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(exportFileName.getBytes("UTF-8"), "iso8859-1") + "\""); // 导出的EXCEL列属性 List columnListName = new ArrayList (); columnListName.add("userName&姓名"); columnListName.add("mobile&手机"); columnListName.add("shopTel&分店电话"); columnListName.add("postName&职位"); columnListName.add("email&邮箱"); columnListName.add("shopAddress&分店地址"); Bean2ExcelConversionUtils.beans2excelFile07(columnListName, data, response.getOutputStream()); session.setAttribute(randomNumber, new Double(100)); } catch (Exception e) { e.printStackTrace(); session.setAttribute(randomNumber, new Double(100)); } catch (Throwable e) { e.printStackTrace(); session.setAttribute(randomNumber, new Double(100)); } }
JSP代码：
function exportToExcel() { var randomNumber=new Date().getTime(); top.$.jBox.tip("正在导出...", 'loading'); var exportDate = "${ctx}/xxxAction.do?method=export&randomNumber="+randomNumber; $("#exportForm").attr("action", exportDate); $("#exportForm").attr("method","post"); $("#exportForm").submit(); }
因为是使用的插件，所以需要引入一个工具类（下面的工具类直接复制到新建的类文件里面即可）
也可以通过： 进行下载
import java.beans.Introspector;import java.beans.PropertyDescriptor;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCell;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.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;/** * @author Tan Jiangyong * @date 2013-9-3 下午3:36:43 * @version V1.0 */@SuppressWarnings("all")public class Bean2ExcelConversionUtils { private static final String PATTERN="yyyy-MM-dd HH:mm:ss"; //excel日期格式，默认配置 private static final String DATE_PATTERN="yyyy-MM-dd"; //excel日期格式 private static final String DATE_HH_PATTERN="HH:mm:ss"; //excel时间格式 private static final int TOTAL_SIZE=40000; //每个excel写入多少数据（默认配置） private static final int MAX_SHEET_SIZE=10000; //每一个sheet的大小（默认配置） private static final int COLUMN_WIDTH_WORD=25; //列宽，默认汉字个数为25个 private static final int FLUSH_ROWS=100; //每生成excel行数，内存中缓存记录数清空（目的，避免零时文件过大） /** * 07、10办公版EXCEL导出（数据直接写到服务器的EXCEL里，以下载的形式，下载导出的数据） * @param listName 列表头名称 * @param beans 实体集合 * @param result 数字字典Map集 * @param filePath 服务器存放文件路径 * @param fileName 文件名称 * @param totalSize EXCEL条数量 * @param maxSheetSize sheet页条数量 * @return 文件集合 * @throws Exception */ public static List beans2excelFile07(List listName,List beans,HashMap > result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throws Exception{ if(totalSize==null || totalSize<=0) totalSize=TOTAL_SIZE; if(maxSheetSize==null || maxSheetSize<=0) maxSheetSize=MAX_SHEET_SIZE; if(fileName==null) fileName=""; return beans2excelFile2007(listName, beans, result, filePath, fileName,totalSize,maxSheetSize); } /** * 07、10办公版EXCEL导出（数据直接写到服务器的EXCEL里，以下载的形式，下载导出的数据） * @param listName 列表头名称 * @param beans 实体集合 * @param result 数字字典Map集 * @param filePath 服务器存放文件路径 * @param fileName 文件名称 * @param totalSize EXCEL条数量 * @param maxSheetSize sheet页条数量 * @param request 客户端请求对象 * @param response 客户端响应对象 * @throws Exception */ public static void beans2excelFile07(List listName,List beans,HashMap > result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize,HttpServletRequest request,HttpServletResponse response) throws Exception{ if(totalSize==null || totalSize<=0) totalSize=TOTAL_SIZE; if(maxSheetSize==null || maxSheetSize<=0) maxSheetSize=MAX_SHEET_SIZE; if(fileName==null) fileName=""; List files = beans2excelFile2007(listName, beans, result, filePath, fileName,totalSize,maxSheetSize); DownLoadUtils.downLoadFiles(files, filePath, request, response); } /** * 07、10办公版EXCEL导出，每个EXCEL组织数据 * @param listName 列表头名称 * @param beans 实体集合 * @param result 数字字典Map集 * @param filePath 服务器存放文件路径 * @param fileName 文件名称 * @param totalSize EXCEL条数量 * @param maxSheetSize sheet页条数量 * @return 文件集合 * @throws Exception */ private static List beans2excelFile2007(List listName,List beans,HashMap > result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throws Exception{ if ((listName == null) || (listName.size() == 0)) { throw new Exception("listName is null when create excel document"); } List listFile=new ArrayList ();//返回的文件集合 int size=beans==null?0:beans.size(); String fileSuffixName=".xlsx";//后缀 String path="";//文件路径 Integer startIdx=0;//数据读取的起始行 Integer endIdx=0;//数据读取的结束行 (new File(filePath)).mkdirs(); //没有该目录创建目录 if(size==0){ startIdx=0; endIdx=(totalSize)>size?size:(totalSize); String name=fileName+"_第0-0条数据"; path=filePath+File.separatorChar+name+fileSuffixName; Workbook wb =new SXSSFWorkbook(); buildExcelDocument2007(wb, listName, beans,result,startIdx,endIdx,maxSheetSize); //没有文件，创建文件 File file = new File(path); if (!file.exists()){ file.createNewFile(); } FileOutputStream out=new FileOutputStream(file); wb.write(out); out.close(); return listFile; } for (int i = 0; i < size;i++) { int remainder=i%totalSize; if(size==0 || remainder==0){ startIdx=i; endIdx=(i+totalSize)>size?size:(i+totalSize); String name=fileName+"_第"+(startIdx+1)+"-"+(endIdx)+"条数据"; path=filePath+"/"+name+fileSuffixName; Workbook wb =new SXSSFWorkbook(); buildExcelDocument2007(wb, listName, beans,result,startIdx,endIdx,maxSheetSize); //没有文件，创建文件 File file = new File(path); if (!file.exists()){ file.createNewFile(); } FileOutputStream out=new FileOutputStream(file); wb.write(out); out.close(); listFile.add(file); }else if((size-i) endIdx){ //最后，不满一万条 startIdx=i; endIdx=i+totalSize; String name=fileName+"_第"+(startIdx+1)+"-"+(endIdx)+"条数据"; path=filePath+name+"."+fileSuffixName;//没有文件，创建文件 Workbook wb =new SXSSFWorkbook(); buildExcelDocument2007(wb, listName, beans, result,startIdx,endIdx,maxSheetSize); //没有文件，创建文件 File file = new File(path); if (!file.exists()){ file.createNewFile(); } FileOutputStream out=new FileOutputStream(file); wb.write(out); out.close(); listFile.add(file); } } return listFile; } /** * 07、10办公版EXCEL导出，每个EXCEL写入数据 * @param wb EXCEL工作薄 * @param listName 列表头名称 * @param beans 实体集合 * @param result 数字字典Map集 * @param startIdx 数据集合，开始行 * @param endIdx 数据集合，结束始行 * @param maxSheetSize SHEET页条数 * @throws Exception */ private static void buildExcelDocument2007(Workbook wb, List listName, List beans,HashMap > result,Integer startIdx,Integer endIdx,Integer maxSheetSize) throws Exception { int totalSize=endIdx-startIdx;//总共条数 try { CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null); List titles = new ArrayList(); List beanAttrNames = new ArrayList(); boolean flagListExists=false; List flagList=new ArrayList(); List widthList=new ArrayList(); HashMap dateMap=new HashMap (); String[] header = new String[listName.size()]; int rows_max = 0;//标题占多少列 for (int i=0;i rows_max) { rows_max = zh_name.split("_").length; } if(str.length>2){ String flag=str[2]; flagList.add(i,flag); if(!flagListExists) flagListExists=true; } if(str.length>3){ widthList.add(str[3]); } if(str.length>4){ dateMap.put(en_name, str[4]); } } PropertyDescriptor[] props = null; int size=endIdx-startIdx; Sheet sheet=null; //如果没有数据，导出表头 if(size==0){ sheet=ExcelHeadUtils.getExcelHead2007(wb, header, "Sheet1"); sheet.setDefaultRowHeight((short)350);//高度 setColumnWidth2007(widthList, sheet,beanAttrNames.size()); return ; } int u=1;//用来创建每个sheet的行 int h=0;//用来标注每个sheet也得名字：多少行-多少行 for (int i = startIdx; i < endIdx ; i++) { int remainder=h%maxSheetSize; if(size==0 || i==startIdx || remainder==0){ u=1; int section=(h+maxSheetSize)>totalSize?totalSize:(h+maxSheetSize); sheet=ExcelHeadUtils.getExcelHead2007(wb, header,"第"+(h+1)+"-"+section+"条"); sheet.createFreezePane( 1, rows_max, 1, rows_max); sheet.setDefaultRowHeight((short)350);//高度 setColumnWidth2007(widthList, sheet,beanAttrNames.size()); } if(props==null) props=Introspector.getBeanInfo(beans.get(0).getClass()).getPropertyDescriptors(); Object bean = beans.get(i); Row row = sheet.createRow(u+rows_max-1); u++; h++; for (int j = 0; j < beanAttrNames.size(); j++) { String beanAttrName = (String)beanAttrNames.get(j); String flag=""; if(flagListExists) flag=(String)flagList.get(j); for (int k = 0; k < props.length; k++) { String propName = props[k].getName(); if (propName.equals(beanAttrName)) { String pattern=dateMap.get(beanAttrName); Cell cell = row.createCell((short)j); Object cellValue = callGetter(bean, props[k],pattern); if("true".equalsIgnoreCase(flag)){ if(result!=null){ HashMap hash=result.get(beanAttrName); if(hash!=null) cellValue=hash.get(cellValue); } } if (cellValue == null) { cellValue = ""; } setExcelCellText2007(cell, cellValue.toString(),cellStyle); } } } //每当行数达到设置的值就刷新数据到硬盘,以清理内存 if(i%FLUSH_ROWS==0){ ((SXSSFSheet)sheet).flushRows(); } } } catch (Exception e) { throw new Exception(e); } } /** * 07、10办公版EXCEL导出（直接以流的方式，写到客户端，导出的EXCEL文件只有一个） * @param listName 列表头名称 * @param beans 实体集合 * @param maxSheetSize SHEET页的条数 * @param outputStream 客户端输出流 * @throws Exception */ public static void beans2excelFile07(List listName,List beans, OutputStream outputStream) throws Exception{ if ((listName == null) || (listName.size() == 0)) { throw new Exception("listName is null when create excel document"); } if (outputStream == null) { throw new Exception("outputStream is null when create excel document"); } Workbook wb =new SXSSFWorkbook(); beans2excelFile07(listName, beans, null, null, MAX_SHEET_SIZE, outputStream); try { wb.write(outputStream); outputStream.close(); } catch (IOException e) { throw new Exception(e); } } /** * 07、10办公版EXCEL导出（直接以流的方式，写到客户端，导出的EXCEL文件只有一个） * @param listName 列表头名称 * @param beans 实体集合 * @param maxSheetSize SHEET页的条数 * @param outputStream 客户端输出流 * @throws Exception */ public static void beans2excelFile07(List listName,List beans,HashMap > result,String sheetName,Integer maxSheetSize, OutputStream outputStream) throws Exception{ if ((listName == null) || (listName.size() == 0)) { throw new Exception("listName is null when create excel document"); } if (outputStream == null) { throw new Exception("outputStream is null when create excel document"); } if(maxSheetSize==null || maxSheetSize<=0){ maxSheetSize=MAX_SHEET_SIZE; } if(sheetName==null || "".equals(sheetName.trim())){ sheetName="Sheet"; } Workbook wb =new SXSSFWorkbook(); if(maxSheetSize==null || maxSheetSize<=0){ maxSheetSize=MAX_SHEET_SIZE; } buildExcelDocument2007(wb, listName, beans,result,sheetName,maxSheetSize); try { wb.write(outputStream); outputStream.close(); } catch (IOException e) { throw new Exception(e); } } /** * * @param listName * @param beans * @param response * @param fileName 导出的文件名称 * @throws Exception */ public static void beans2excelFile07(List listName, List beans, HttpServletResponse response,String fileName) throws Exception { response.reset(); response.setContentType("octets/stream"); response.setHeader("Content-Disposition", "attachment;filename="+java.net.URLEncoder.encode(fileName, "UTF-8")); if ((listName == null) || (listName.size() == 0)) { throw new Exception("listName is null when create excel document"); } if (response.getOutputStream() == null) { throw new Exception("outputStream is null when create excel document"); } beans2excelFile07(listName, beans, null, null, MAX_SHEET_SIZE, response.getOutputStream()); } /** * 07、10办公版EXCEL导出，EXCEL写入数据 * @param wb EXCEL工作薄 * @param listName 列表头名称 * @param beans 实体集合 * @param maxSheetSize SHEET页的条数 * @throws Exception */ private static void buildExcelDocument2007(Workbook wb, List listName, List beans,HashMap > result,String sheetName,Integer maxSheetSize) throws Exception { try { CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null); List titles = new ArrayList(); List beanAttrNames = new ArrayList(); List widthList = new ArrayList(); HashMap dateMap=new HashMap (); String[] header = new String[listName.size()]; int rows_max = 0;//标题占多少列 List flagList=new ArrayList(); boolean flagListExists=false; for (int i=0;i rows_max) { rows_max = zh_name.split("_").length; } if(str.length>2){ String flag=str[2]; flagList.add(i,flag); if(!flagListExists) flagListExists=true; } if(str.length>3){ widthList.add(str[3]); } if(str.length>4){ dateMap.put(en_name, str[4]); } } PropertyDescriptor[] props = null; int size=beans==null?0:beans.size(); Sheet sheet=null; //如果没有数据，导出表头 if(size==0){ sheet=ExcelHeadUtils.getExcelHead2007(wb, header, sheetName); sheet.setDefaultRowHeight((short)350);//高度 setColumnWidth2007(widthList, sheet,beanAttrNames.size()); return ; } for (int i = 0; i < size ; i++) { int remainder=i%maxSheetSize; if(size==0 || i==0 || remainder==0){ sheet=ExcelHeadUtils.getExcelHead2007(wb, header,sheetName+(i/maxSheetSize)); sheet.createFreezePane( 1, rows_max, 1, rows_max); sheet.setDefaultRowHeight((short)350);//高度 setColumnWidth2007(widthList, sheet,beanAttrNames.size()); } if(props==null) props=Introspector.getBeanInfo(beans.get(0).getClass()).getPropertyDescriptors(); Object bean = beans.get(i); Row row = sheet.createRow(remainder+rows_max); for (int j = 0; j < beanAttrNames.size(); j++) { String beanAttrName = (String)beanAttrNames.get(j); String flag=""; if(flagListExists) flag=(String)flagList.get(j); for (int k = 0; k < props.length; k++) { String propName = props[k].getName(); if (propName.equals(beanAttrName)) { String pattern=dateMap.get(beanAttrName); Cell cell = row.createCell((short)j); Object cellValue = callGetter(bean, props[k],pattern); if("true".equalsIgnoreCase(flag)){ if(result!=null){ HashMap hash=result.get(beanAttrName); if(hash!=null) cellValue=hash.get(cellValue); } } if (cellValue == null) { cellValue = ""; } setExcelCellText2007(cell, cellValue.toString(),cellStyle); } } } //每当行数达到设置的值就刷新数据到硬盘,以清理内存 if(i%FLUSH_ROWS==0){ ((SXSSFSheet)sheet).flushRows(); } } } catch (Exception e) { throw new Exception(e); } } /** * 07、10办公版EXCEL导出（直接以流的方式，写到客户端，导出的EXCEL文件只有一个） * @param listName 列表头名称 * @param beans 实体集合 * @param maxSheetSize SHEET页的条数 * @param outputStream 客户端输出流 * @throws Exception */ public static void beans2excelFile07List(List > listColumnName,List list2beans,HashMap >> result,List listSheetName, OutputStream outputStream) throws Exception{ if ((listColumnName == null) || (listColumnName.size() == 0)) { throw new Exception("listColumnName is null when create excel document"); } if (list2beans.size() != listColumnName.size()) { throw new Exception("list2beans and listColumnName size Unequal"); } if (outputStream == null) { throw new Exception("outputStream is null when create excel document"); } Workbook wb =new SXSSFWorkbook(); buildExcelDocument2007List(wb, listColumnName, list2beans, result, listSheetName); try { wb.write(outputStream); outputStream.close(); } catch (IOException e) { throw new Exception(e); } } /** * 07、10办公版EXCEL导出，EXCEL写入数据 * @param wb EXCEL工作薄 * @param listName 列表头名称 * @param beans 实体集合 * @param maxSheetSize SHEET页的条数 * @throws Exception */ private static void buildExcelDocument2007List(Workbook wb, List > listColumnName,List list2beans,HashMap >> resultMap,List listSheetName) throws Exception { try { int sheets=listColumnName.size(); boolean sheetNameIsNullFlag=false; if(listSheetName==null || listSheetName.size()!=sheets){ sheetNameIsNullFlag=true; } for (int s = 0; s < sheets; s++) { String sheetName="Sheet"+s; if(!sheetNameIsNullFlag){ sheetName=listSheetName.get(s); } List listName=listColumnName.get(s); CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null); List titles = new ArrayList(); List beanAttrNames = new ArrayList(); List widthList = new ArrayList(); HashMap dateMap=new HashMap (); String[] header = new String[listName.size()]; int rows_max = 0;//标题占多少列 List flagList=new ArrayList(); boolean flagListExists=false; for (int i=0;i rows_max) { rows_max = zh_name.split("_").length; } if(str.length>2){ String flag=str[2]; flagList.add(i,flag); if(!flagListExists) flagListExists=true; } if(str.length>3){ widthList.add(str[3]); } if(str.length>4){ dateMap.put(en_name, str[4]); } } PropertyDescriptor[] props = null; ArrayList beans=(ArrayList )list2beans.get(s); int size=beans==null?0:beans.size(); Sheet sheet=null; //如果没有数据，导出表头 if(size==0){ sheet=ExcelHeadUtils.getExcelHead2007(wb, header, sheetName); sheet.setDefaultRowHeight((short)350);//高度 setColumnWidth2007(widthList, sheet,beanAttrNames.size()); return ; } HashMap > result=null; if(resultMap!=null){ result=resultMap.get(sheetName); } sheet=ExcelHeadUtils.getExcelHead2007(wb, header,sheetName); sheet.createFreezePane( 1, rows_max, 1, rows_max); sheet.setDefaultRowHeight((short)350);//高度 setColumnWidth2007(widthList, sheet,beanAttrNames.size()); for (int i = 0; i < size ; i++) { if(props==null) props=Introspector.getBeanInfo(beans.get(0).getClass()).getPropertyDescriptors(); Object bean = beans.get(i); Row row = sheet.createRow(rows_max+i); for (int j = 0; j < beanAttrNames.size(); j++) { String beanAttrName = (String)beanAttrNames.get(j); String flag=""; if(flagListExists) flag=(String)flagList.get(j); for (int k = 0; k < props.length; k++) { String propName = props[k].getName(); if (propName.equals(beanAttrName)) { String pattern=dateMap.get(beanAttrName); Cell cell = row.createCell((short)j); Object cellValue = callGetter(bean, props[k],pattern); if("true".equalsIgnoreCase(flag)){ if(result!=null){ HashMap hash=result.get(beanAttrName); if(hash!=null) cellValue=hash.get(cellValue); } } if (cellValue == null) { cellValue = ""; } setExcelCellText2007(cell, cellValue.toString(),cellStyle); } } } //每当行数达到设置的值就刷新数据到硬盘,以清理内存 if(i%FLUSH_ROWS==0){ ((SXSSFSheet)sheet).flushRows(); } } } } catch (Exception e) { throw new Exception(e); } } /** * 07、10办公版EXCEL导出，单元格设置 * @param cell 单元格对象 * @param text 单元格文本内容 * @param cellStyle 单元格格式 */ private static void setExcelCellText2007(Cell cell, Object text,CellStyle cellStyle) { cell.setCellValue(text.toString()); cell.setCellType(1);//单元格类型 cell.setCellStyle(cellStyle); } /** * 07、10办公版EXCEL导出，单元格宽度设置 * @param widthList 列宽集合 * @param sheet sheet对象 * @param allSize 总列数 */ private static void setColumnWidth2007(List widthList,Sheet sheet,int allSize){ if(widthList!=null && widthList.size()>0){ int size=widthList.size(); for (int i = 0; i < size; i++) { try { Integer width=Integer.parseInt((String) widthList.get(i)); sheet.setColumnWidth((short) i,width*256); } catch (NumberFormatException e) { continue; } } }else{ for (int i = 0; i < allSize; i++) { try { sheet.setColumnWidth((short) i,COLUMN_WIDTH_WORD*256); } catch (NumberFormatException e) { continue; } } } } /** * 03、WPS：EXCEL导出（数据直接写到服务器的EXCEL里，以下载的形式，下载导出的数据） * @param listName 列表头名称 * @param beans 实体集合 * @param result 数字字典Map集 * @param filePath 服务器存放文件路径 * @param fileName 文件名称 * @param totalSize EXCEL条数量 * @param maxSheetSize sheet页条数量 * @return List 文件集合 * @throws Exception */ public static List beans2excelFile03(List listName,List beans,HashMap > result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throws Exception{ if(totalSize==null || totalSize<=0) totalSize=TOTAL_SIZE; if(maxSheetSize==null || maxSheetSize<=0) maxSheetSize=MAX_SHEET_SIZE; if(fileName==null) fileName=""; return beans2excelFile2003(listName, beans, result, filePath, fileName,totalSize,maxSheetSize); } /** * 03、WPS：EXCEL导出（数据直接写到服务器的EXCEL里，以下载的形式，下载导出的数据） * @param listName 列表头名称 * @param beans 实体集合 * @param result 数字字典Map集 * @param filePath 服务器存放文件路径 * @param fileName 文件名称 * @param totalSize EXCEL条数量 * @param maxSheetSize sheet页条数量 * @param request 客户端请求对象 * @param response 客户端响应对象 * @throws Exception */ public static void beans2excelFile03(List listName,List beans,HashMap > result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize,HttpServletRequest request,HttpServletResponse response) throws Exception{ if(totalSize==null || totalSize<=0) totalSize=TOTAL_SIZE; if(maxSheetSize==null || maxSheetSize<=0) maxSheetSize=MAX_SHEET_SIZE; if(fileName==null) fileName=""; List files=beans2excelFile2003(listName, beans, result, filePath, fileName,totalSize,maxSheetSize); DownLoadUtils.downLoadFiles(files, filePath, request, response); } /** * 03、WPS：EXCEL导出，每个EXCEL组织数据 * @param listName 列表头名称 * @param beans 实体集合 * @param result 数字字典Map集 * @param filePath 服务器存放文件路径 * @param fileName 文件名称 * @param totalSize EXCEL条数量 * @param maxSheetSize sheet页条数量 * @return 文件集合 * @throws Exception */ private static List beans2excelFile2003(List listName,List beans,HashMap > result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throws Exception{ if ((listName == null) || (listName.size() == 0)) { throw new Exception("listName is null when create excel document"); } List listFile=new ArrayList ();//返回的文件集合 int size=beans==null?0:beans.size(); String fileSuffixName=".xls";//后缀 String path="";//文件路径 Integer startIdx=0;//数据读取的起始行 Integer endIdx=0;//数据读取的结束行 (new File(filePath)).mkdirs(); //没有该目录创建目录 if(size==0){ startIdx=0; endIdx=(totalSize)>size?size:(totalSize); String name=fileName+"_第0-0条数据"; path=filePath+File.separatorChar+name+fileSuffixName; HSSFWorkbook wb =new HSSFWorkbook(); buildExcelDocument2003(wb, listName, beans,result,startIdx,endIdx,maxSheetSize); //没有文件，创建文件 File file = new File(path); if (!file.exists()){ file.createNewFile(); } FileOutputStream out=new FileOutputStream(file); wb.write(out); out.close(); return listFile; } for (int i = 0; i < size;i++) { int remainder=i%totalSize; if(size==0 || remainder==0){ startIdx=i; endIdx=(i+totalSize)>size?size:(i+totalSize); String name=fileName+"_第"+(startIdx+1)+"-"+(endIdx)+"条数据"; path=filePath+"/"+name+fileSuffixName; HSSFWorkbook wb =new HSSFWorkbook(); buildExcelDocument2003(wb, listName, beans,result,startIdx,endIdx,maxSheetSize); //没有文件，创建文件 File file = new File(path); if (!file.exists()){ file.createNewFile(); } FileOutputStream out=new FileOutputStream(file); wb.write(out); out.close(); listFile.add(file); }else if((size-i) endIdx){ //最后，不满一万条 startIdx=i; endIdx=i+totalSize; String name=fileName+"_第"+(startIdx+1)+"-"+(endIdx)+"条数据"; path=filePath+name+"."+fileSuffixName;//没有文件，创建文件 HSSFWorkbook wb =new HSSFWorkbook(); buildExcelDocument2003(wb, listName, beans, result,startIdx,endIdx,maxSheetSize); //没有文件，创建文件 File file = new File(path); if (!file.exists()){ file.createNewFile(); } FileOutputStream out=new FileOutputStream(file); wb.write(out); out.close(); listFile.add(file); } } return listFile; } /** * 03,WPS：EXCEL导出，每个EXCEL写入数据 * @param wb EXCEL工作薄 * @param listName 列表头名称 * @param beans 实体集合 * @param result 数字字典Map集 * @param startIdx 数据集合，开始行 * @param endIdx 数据集合，结束始行 * @param maxSheetSize SHEET页条数 * @throws Exception */ private static void buildExcelDocument2003(HSSFWorkbook wb, List listName, List beans,HashMap > result,Integer startIdx,Integer endIdx,Integer maxSheetSize) throws Exception { int totalSize=endIdx-startIdx;//总共条数 try { CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null); List titles = new ArrayList(); List beanAttrNames = new ArrayList(); List widthList=new ArrayList(); String[] header = new String[listName.size()]; List flagList=new ArrayList(); boolean flagListExists=false; int rows_max = 0;//标题占多少列 HashMap dateMap=new HashMap (); for (int i=0;i rows_max) { rows_max = zh_name.split("_").length; } if(str.length>2){ String flag=str[2]; flagList.add(i,flag); if(!flagListExists) flagListExists=true; } if(str.length>3){ widthList.add(str[3]); } if(str.length>4){ dateMap.put(en_name, str[4]); } } PropertyDescriptor[] props = null; int size=endIdx-startIdx; HSSFSheet sheet=null; //如果没有数据，导出表头 if(size==0){ sheet=ExcelHeadUtils.getExcelHead2003(wb, header, "Sheet1"); sheet.setDefaultRowHeight((short)350);//高度 setColumnWidth2003(widthList, sheet,beanAttrNames.size()); return ; } int u=1;//用来创建每个sheet的行 int h=0;//用来标注每个sheet也得名字：多少行-多少行 for (int i = startIdx; i < endIdx ; i++) { int remainder=h%maxSheetSize; if(size==0 || i==startIdx || remainder==0){ u=1; int section=(h+maxSheetSize)>totalSize?totalSize:(h+maxSheetSize); sheet=ExcelHeadUtils.getExcelHead2003(wb, header, "第"+(h+1)+"-"+section+"条"); sheet.createFreezePane( 1, rows_max, 1, rows_max); sheet.setDefaultRowHeight((short)350);//高度 setColumnWidth2003(widthList, sheet,beanAttrNames.size()); } if(props==null) props=Introspector.getBeanInfo(beans.get(0).getClass()).getPropertyDescriptors(); Object bean = beans.get(i); HSSFRow row = sheet.createRow(u+rows_max-1); u++; h++; for (int j = 0; j < beanAttrNames.size(); j++) { String beanAttrName = (String)beanAttrNames.get(j); String flag=null; if(flagListExists) flag=(String)flagList.get(j); for (int k = 0; k < props.length; k++) { String propName = props[k].getName(); if (propName.equals(beanAttrName)) { String pattern=dateMap.get(beanAttrName); HSSFCell cell = row.createCell((short)j); Object cellValue = callGetter(bean, props[k],pattern); if("true".equalsIgnoreCase(flag)){ if(result!=null){ HashMap hash=result.get(beanAttrName); if(hash!=null) cellValue=hash.get(cellValue); } } if (cellValue == null) { cellValue = ""; } setExcelCellText2003(cell, cellValue.toString(),cellStyle); } } } } } catch (Exception e) { throw new Exception(e); } } /** * 03,WPS：EXCEL导出（直接以流的方式，写到客户端，导出的EXCEL文件只有一个） * @param listName 列表头名称 * @param beans 实体集合 * @param maxSheetSize sheet页条数量 * @param outputStream 客户端输出流 * @throws Exception */ public static void beans2excelFile03(List listName,List beans,HashMap > result,String sheetName,Integer maxSheetSize, OutputStream outputStream) throws Exception{ if ((listName == null) || (listName.size() == 0)) { throw new Exception("listName is null when create excel document"); } if(maxSheetSize==null || maxSheetSize<=0){ maxSheetSize=MAX_SHEET_SIZE; } if(sheetName==null || "".equals(sheetName.trim())){ sheetName="Sheet"; } HSSFWorkbook wb =new HSSFWorkbook(); if(maxSheetSize==null || maxSheetSize<=0) maxSheetSize=MAX_SHEET_SIZE; buildExcelDocument2003(wb, listName, beans,result,sheetName,maxSheetSize); try { wb.write(outputStream); outputStream.close(); } catch (IOException e) { throw new Exception(e); } } /** * 03,WPS：EXCEL导出，EXCEL写入数据 * @param wb EXCEL工作薄 * @param listName 列表头名称 * @param beans 实体集合 * @param maxSheetSize sheet页条数量 * @throws Exception */ private static void buildExcelDocument2003(HSSFWorkbook wb, List listName, List beans,HashMap > result,String sheetName,Integer maxSheetSize) throws Exception { try { CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null); List titles = new ArrayList(); List beanAttrNames = new ArrayList(); List widthList = new ArrayList(); HashMap dateMap=new HashMap (); String[] header = new String[listName.size()]; int rows_max = 0;//标题占多少列 List flagList=new ArrayList(); boolean flagListExists=false; for (int i=0;i rows_max) { rows_max = zh_name.split("_").length; } if(str.length>2){ String flag=str[2]; flagList.add(i,flag); if(!flagListExists) flagListExists=true; } if(str.length>3){ widthList.add(str[3]); } if(str.length>4){ dateMap.put(en_name, str[4]); } } PropertyDescriptor[] props =null; int size=beans==null?0:beans.size(); HSSFSheet sheet=null; //如果没有数据，导出表头 if(size==0){ sheet=ExcelHeadUtils.getExcelHead2003(wb, header, sheetName); setColumnWidth2003(widthList, sheet,beanAttrNames.size()); sheet.setDefaultRowHeight((short)350);//高度 return ; } for (int i = 0; i < size ; i++) { int remainder=i%maxSheetSize; if(size==0 || i==0 || remainder==0){ sheet=ExcelHeadUtils.getExcelHead2003(wb, header, sheetName+(i/maxSheetSize)); sheet.createFreezePane( 1, rows_max, 1, rows_max); setColumnWidth2003(widthList, sheet,beanAttrNames.size()); sheet.setDefaultRowHeight((short)350);//高度 } if(props==null) props= Introspector.getBeanInfo(beans.get(0).getClass()).getPropertyDescriptors(); Object bean = beans.get(i); HSSFRow row = sheet.createRow(remainder+rows_max); for (int j = 0; j < beanAttrNames.size(); j++) { String beanAttrName = (String)beanAttrNames.get(j); String flag=null; if(flagListExists) flag=(String)flagList.get(j); for (int k = 0; k < props.length; k++) { String propName = props[k].getName(); if (propName.equals(beanAttrName)) { String pattern=dateMap.get(beanAttrName); HSSFCell cell = row.createCell((short)j); Object cellValue = callGetter(bean, props[k],pattern); if("true".equalsIgnoreCase(flag)){ if(result!=null){ HashMap hash=result.get(beanAttrName); if(hash!=null) cellValue=hash.get(cellValue); } } if (cellValue == null) { cellValue = ""; } setExcelCellText2003(cell, cellValue.toString(),cellStyle); } } } } } catch (Exception e) { throw new Exception(e); } } /** * 03,WPS：EXCEL导出，单元格设置 * @param cell 单元格对象 * @param text 单元格文本内容 * @param cellStyle 单元格格式 */ private static void setExcelCellText2003(HSSFCell cell, Object text,CellStyle cellStyle) { cell.setCellValue(text.toString()); cell.setCellType(1);//单元格类型 cell.setCellStyle(cellStyle); } /** * 03,WPS：EXCEL导出，单元格宽度设置 * @param widthList 列宽集合 * @param sheet sheet对象 * @param allSize 总列数 */ private static void setColumnWidth2003(List widthList,HSSFSheet sheet,int allSize){ if(widthList!=null && widthList.size()>0){ int size=widthList.size(); for (int i = 0; i < size; i++) { try { Integer width=Integer.parseInt((String) widthList.get(i)); sheet.setColumnWidth((short) i,width*256); } catch (NumberFormatException e) { continue; } } }else{ for (int i = 0; i < allSize; i++) { try { sheet.setColumnWidth((short) i,COLUMN_WIDTH_WORD*256); } catch (NumberFormatException e) { continue; } } } } /** * 根据反射，获取实体属性的值 * @param target 实体属性 * @param prop 反射调用类 * @param pattern 日期格式 * @return */ private static Object callGetter(Object target, PropertyDescriptor prop,String pattern) { Object o = null; if (prop.getReadMethod() != null) { try { o = prop.getReadMethod().invoke(target, null); if (Date.class.equals(prop.getPropertyType())) { if(pattern!=null && !"".equals(pattern)){ try { o = new SimpleDateFormat(pattern).format(o); } catch (Exception e) { o = new SimpleDateFormat(PATTERN).format(o); } }else{ o = formatDate(o); } } } catch (Exception e) { o = null; } } return o; } /** * 日期转换 * @param date * @return 字符串的日期 */ private static String formatDate(Object date) { if(date==null) return ""; String dateStr = new SimpleDateFormat(DATE_HH_PATTERN).format(date); if("00:00:00".equals(dateStr)){ return new SimpleDateFormat(DATE_PATTERN).format(date); } return new SimpleDateFormat(PATTERN).format(date); }}
然后看看效果吧：
