importjava.beans.Introspector;importjava.beans.PropertyDescriptor;importjava.io.File;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.OutputStream;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Date;importjava.util.HashMap;importjava.util.List;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.streaming.SXSSFSheet;importorg.apache.poi.xssf.streaming.SXSSFWorkbook;/***@authorTan Jiangyong
* @date 2013-9-3 下午3:36:43
*@versionV1.0*/@SuppressWarnings("all")public classBean2ExcelConversionUtils {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里，以下载的形式，下载导出的数据)
*@paramlistName 列表头名称
*@parambeans 实体集合
*@paramresult 数字字典Map集
*@paramfilePath 服务器存放文件路径
*@paramfileName 文件名称
*@paramtotalSize EXCEL条数量
*@parammaxSheetSize sheet页条数量
*@return文件集合
*@throwsException*/
public static List beans2excelFile07(List listName,List beans,HashMap> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throwsException{if(totalSize==null || totalSize<=0)
totalSize=TOTAL_SIZE;if(maxSheetSize==null || maxSheetSize<=0)
maxSheetSize=MAX_SHEET_SIZE;if(fileName==null)
fileName="";returnbeans2excelFile2007(listName, beans, result, filePath, fileName,totalSize,maxSheetSize);
}/*** 07、10办公版EXCEL导出(数据直接写到服务器的EXCEL里，以下载的形式，下载导出的数据)
*@paramlistName 列表头名称
*@parambeans 实体集合
*@paramresult 数字字典Map集
*@paramfilePath 服务器存放文件路径
*@paramfileName 文件名称
*@paramtotalSize EXCEL条数量
*@parammaxSheetSize sheet页条数量
*@paramrequest 客户端请求对象
*@paramresponse 客户端响应对象
*@throwsException*/
public static void beans2excelFile07(List listName,List beans,HashMap> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize,HttpServletRequest request,HttpServletResponse response) throwsException{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组织数据
*@paramlistName 列表头名称
*@parambeans 实体集合
*@paramresult 数字字典Map集
*@paramfilePath 服务器存放文件路径
*@paramfileName 文件名称
*@paramtotalSize EXCEL条数量
*@parammaxSheetSize sheet页条数量
*@return文件集合
*@throwsException*/
private static List beans2excelFile2007(List listName,List beans,HashMap> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throwsException{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=newSXSSFWorkbook();
buildExcelDocument2007(wb, listName, beans,result,startIdx,endIdx,maxSheetSize);//没有文件，创建文件
File file = newFile(path);if (!file.exists()){
file.createNewFile();
}
FileOutputStream out=newFileOutputStream(file);
wb.write(out);
out.close();returnlistFile;
}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=newSXSSFWorkbook();
buildExcelDocument2007(wb, listName, beans,result,startIdx,endIdx,maxSheetSize);//没有文件，创建文件
File file = newFile(path);if (!file.exists()){
file.createNewFile();
}
FileOutputStream out=newFileOutputStream(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 =newSXSSFWorkbook();
buildExcelDocument2007(wb, listName, beans, result,startIdx,endIdx,maxSheetSize);//没有文件，创建文件
File file = newFile(path);if (!file.exists()){
file.createNewFile();
}
FileOutputStream out=newFileOutputStream(file);
wb.write(out);
out.close();
listFile.add(file);
}
}returnlistFile;
}/*** 07、10办公版EXCEL导出，每个EXCEL写入数据
*@paramwb EXCEL工作薄
*@paramlistName 列表头名称
*@parambeans 实体集合
*@paramresult 数字字典Map集
*@paramstartIdx 数据集合，开始行
*@paramendIdx 数据集合，结束始行
*@parammaxSheetSize SHEET页条数
*@throwsException*/
private static void buildExcelDocument2007(Workbook wb, List listName, List beans,HashMap> result,Integer startIdx,Integer endIdx,Integer maxSheetSize) throwsException
{int totalSize=endIdx-startIdx;//总共条数
try{
CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null);
List titles= newArrayList();
List beanAttrNames= newArrayList();boolean flagListExists=false;
List flagList=newArrayList();
List widthList=newArrayList();
HashMap dateMap=new HashMap();
String[] header= newString[listName.size()];int rows_max = 0;//标题占多少列
for (int i=0;i
{
String[] str=listName.get(i).split("&");
String en_name=str[0];
String zh_name=str[1];
beanAttrNames.add(i,en_name);
titles.add(i, zh_name);
header[i]=zh_name;if (zh_name.split("_").length >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 newException(e);
}
}/*** 07、10办公版EXCEL导出(直接以流的方式，写到客户端，导出的EXCEL文件只有一个)
*@paramlistName 列表头名称
*@parambeans 实体集合
*@parammaxSheetSize SHEET页的条数
*@paramoutputStream 客户端输出流
*@throwsException*/
public static void beans2excelFile07(List listName,List beans, OutputStream outputStream) throwsException{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=newSXSSFWorkbook();
beans2excelFile07(listName, beans,null, null, MAX_SHEET_SIZE, outputStream);try{
wb.write(outputStream);
outputStream.close();
}catch(IOException e) {throw newException(e);
}
}/*** 07、10办公版EXCEL导出(直接以流的方式，写到客户端，导出的EXCEL文件只有一个)
*@paramlistName 列表头名称
*@parambeans 实体集合
*@parammaxSheetSize SHEET页的条数
*@paramoutputStream 客户端输出流
*@throwsException*/
public static void beans2excelFile07(List listName,List beans,HashMap> result,String sheetName,Integer maxSheetSize, OutputStream outputStream) throwsException{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=newSXSSFWorkbook();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 newException(e);
}
}/***
*@paramlistName
*@parambeans
*@paramresponse
*@paramfileName 导出的文件名称
*@throwsException*/
public static void beans2excelFile07(List listName, List beans, HttpServletResponse response,String fileName) throwsException {
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写入数据
*@paramwb EXCEL工作薄
*@paramlistName 列表头名称
*@parambeans 实体集合
*@parammaxSheetSize SHEET页的条数
*@throwsException*/
private static void buildExcelDocument2007(Workbook wb, List listName, List beans,HashMap> result,String sheetName,Integer maxSheetSize) throwsException
{try{
CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null);
List titles= newArrayList();
List beanAttrNames= newArrayList();
List widthList= newArrayList();
HashMap dateMap=new HashMap();
String[] header= newString[listName.size()];int rows_max = 0;//标题占多少列
List flagList=newArrayList();boolean flagListExists=false;for (int i=0;i
{
String[] str=listName.get(i).split("&");
String en_name=str[0];
String zh_name=str[1];
beanAttrNames.add(i,en_name);
titles.add(i, zh_name);
header[i]=zh_name;if (zh_name.split("_").length >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 newException(e);
}
}/*** 07、10办公版EXCEL导出(直接以流的方式，写到客户端，导出的EXCEL文件只有一个)
*@paramlistName 列表头名称
*@parambeans 实体集合
*@parammaxSheetSize SHEET页的条数
*@paramoutputStream 客户端输出流
*@throwsException*/
public static void beans2excelFile07List(List> listColumnName,List list2beans,HashMap>> result,List listSheetName, OutputStream outputStream) throwsException{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=newSXSSFWorkbook();
buildExcelDocument2007List(wb, listColumnName, list2beans, result, listSheetName);try{
wb.write(outputStream);
outputStream.close();
}catch(IOException e) {throw newException(e);
}
}/*** 07、10办公版EXCEL导出，EXCEL写入数据
*@paramwb EXCEL工作薄
*@paramlistName 列表头名称
*@parambeans 实体集合
*@parammaxSheetSize SHEET页的条数
*@throwsException*/
private static void buildExcelDocument2007List(Workbook wb, List> listColumnName,List list2beans,HashMap>> resultMap,List listSheetName) throwsException
{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= newArrayList();
List beanAttrNames= newArrayList();
List widthList= newArrayList();
HashMap dateMap=new HashMap();
String[] header= newString[listName.size()];int rows_max = 0;//标题占多少列
List flagList=newArrayList();boolean flagListExists=false;for (int i=0;i
{
String[] str=listName.get(i).split("&");
String en_name=str[0];
String zh_name=str[1];
beanAttrNames.add(i,en_name);
titles.add(i, zh_name);
header[i]=zh_name;if (zh_name.split("_").length >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 newException(e);
}
}/*** 07、10办公版EXCEL导出，单元格设置
*@paramcell 单元格对象
*@paramtext 单元格文本内容
*@paramcellStyle 单元格格式*/
private static voidsetExcelCellText2007(Cell cell, Object text,CellStyle cellStyle)
{
cell.setCellValue(text.toString());
cell.setCellType(1);//单元格类型
cell.setCellStyle(cellStyle);
}/*** 07、10办公版EXCEL导出，单元格宽度设置
*@paramwidthList 列宽集合
*@paramsheet sheet对象
*@paramallSize 总列数*/
private static void setColumnWidth2007(List widthList,Sheet sheet,intallSize){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里，以下载的形式，下载导出的数据)
*@paramlistName 列表头名称
*@parambeans 实体集合
*@paramresult 数字字典Map集
*@paramfilePath 服务器存放文件路径
*@paramfileName 文件名称
*@paramtotalSize EXCEL条数量
*@parammaxSheetSize sheet页条数量
*@returnList 文件集合
*@throwsException*/
public static List beans2excelFile03(List listName,List beans,HashMap> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throwsException{if(totalSize==null || totalSize<=0)
totalSize=TOTAL_SIZE;if(maxSheetSize==null || maxSheetSize<=0)
maxSheetSize=MAX_SHEET_SIZE;if(fileName==null)
fileName="";returnbeans2excelFile2003(listName, beans, result, filePath, fileName,totalSize,maxSheetSize);
}/*** 03、WPS：EXCEL导出(数据直接写到服务器的EXCEL里，以下载的形式，下载导出的数据)
*@paramlistName 列表头名称
*@parambeans 实体集合
*@paramresult 数字字典Map集
*@paramfilePath 服务器存放文件路径
*@paramfileName 文件名称
*@paramtotalSize EXCEL条数量
*@parammaxSheetSize sheet页条数量
*@paramrequest 客户端请求对象
*@paramresponse 客户端响应对象
*@throwsException*/
public static void beans2excelFile03(List listName,List beans,HashMap> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize,HttpServletRequest request,HttpServletResponse response) throwsException{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组织数据
*@paramlistName 列表头名称
*@parambeans 实体集合
*@paramresult 数字字典Map集
*@paramfilePath 服务器存放文件路径
*@paramfileName 文件名称
*@paramtotalSize EXCEL条数量
*@parammaxSheetSize sheet页条数量
*@return文件集合
*@throwsException*/
private static List beans2excelFile2003(List listName,List beans,HashMap> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throwsException{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=newHSSFWorkbook();
buildExcelDocument2003(wb, listName, beans,result,startIdx,endIdx,maxSheetSize);//没有文件，创建文件
File file = newFile(path);if (!file.exists()){
file.createNewFile();
}
FileOutputStream out=newFileOutputStream(file);
wb.write(out);
out.close();returnlistFile;
}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=newHSSFWorkbook();
buildExcelDocument2003(wb, listName, beans,result,startIdx,endIdx,maxSheetSize);//没有文件，创建文件
File file = newFile(path);if (!file.exists()){
file.createNewFile();
}
FileOutputStream out=newFileOutputStream(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 =newHSSFWorkbook();
buildExcelDocument2003(wb, listName, beans, result,startIdx,endIdx,maxSheetSize);//没有文件，创建文件
File file = newFile(path);if (!file.exists()){
file.createNewFile();
}
FileOutputStream out=newFileOutputStream(file);
wb.write(out);
out.close();
listFile.add(file);
}
}returnlistFile;
}/*** 03,WPS：EXCEL导出，每个EXCEL写入数据
*@paramwb EXCEL工作薄
*@paramlistName 列表头名称
*@parambeans 实体集合
*@paramresult 数字字典Map集
*@paramstartIdx 数据集合，开始行
*@paramendIdx 数据集合，结束始行
*@parammaxSheetSize SHEET页条数
*@throwsException*/
private static void buildExcelDocument2003(HSSFWorkbook wb, List listName, List beans,HashMap> result,Integer startIdx,Integer endIdx,Integer maxSheetSize) throwsException
{int totalSize=endIdx-startIdx;//总共条数
try{
CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null);
List titles= newArrayList();
List beanAttrNames= newArrayList();
List widthList=newArrayList();
String[] header= newString[listName.size()];
List flagList=newArrayList();boolean flagListExists=false;int rows_max = 0;//标题占多少列
HashMap dateMap=new HashMap();for (int i=0;i
{
String[] str=listName.get(i).split("&");
String en_name=str[0];
String zh_name=str[1];
beanAttrNames.add(i,en_name);
titles.add(i, zh_name);
header[i]=zh_name;if (zh_name.split("_").length >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 newException(e);
}
}/*** 03,WPS：EXCEL导出(直接以流的方式，写到客户端，导出的EXCEL文件只有一个)
*@paramlistName 列表头名称
*@parambeans 实体集合
*@parammaxSheetSize sheet页条数量
*@paramoutputStream 客户端输出流
*@throwsException*/
public static void beans2excelFile03(List listName,List beans,HashMap> result,String sheetName,Integer maxSheetSize, OutputStream outputStream) throwsException{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=newHSSFWorkbook();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 newException(e);
}
}/*** 03,WPS：EXCEL导出，EXCEL写入数据
*@paramwb EXCEL工作薄
*@paramlistName 列表头名称
*@parambeans 实体集合
*@parammaxSheetSize sheet页条数量
*@throwsException*/
private static void buildExcelDocument2003(HSSFWorkbook wb, List listName, List beans,HashMap> result,String sheetName,Integer maxSheetSize) throwsException
{try{
CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null);
List titles= newArrayList();
List beanAttrNames= newArrayList();
List widthList= newArrayList();
HashMap dateMap=new HashMap();
String[] header= newString[listName.size()];int rows_max = 0;//标题占多少列
List flagList=newArrayList();boolean flagListExists=false;for (int i=0;i
{
String[] str=listName.get(i).split("&");
String en_name=str[0];
String zh_name=str[1];
beanAttrNames.add(i,en_name);
titles.add(i, zh_name);
header[i]=zh_name;if (zh_name.split("_").length >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 newException(e);
}
}/*** 03,WPS：EXCEL导出，单元格设置
*@paramcell 单元格对象
*@paramtext 单元格文本内容
*@paramcellStyle 单元格格式*/
private static voidsetExcelCellText2003(HSSFCell cell, Object text,CellStyle cellStyle)
{
cell.setCellValue(text.toString());
cell.setCellType(1);//单元格类型
cell.setCellStyle(cellStyle);
}/*** 03,WPS：EXCEL导出，单元格宽度设置
*@paramwidthList 列宽集合
*@paramsheet sheet对象
*@paramallSize 总列数*/
private static void setColumnWidth2003(List widthList,HSSFSheet sheet,intallSize){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;
}
}
}
}/*** 根据反射，获取实体属性的值
*@paramtarget 实体属性
*@paramprop 反射调用类
*@parampattern 日期格式
*@return
*/
private staticObject 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= newSimpleDateFormat(pattern).format(o);
}catch(Exception e) {
o= newSimpleDateFormat(PATTERN).format(o);
}
}else{
o=formatDate(o);
}
}
}catch(Exception e) {
o= null;
}
}returno;
}/*** 日期转换
*@paramdate
*@return字符串的日期*/
private staticString formatDate(Object date) {if(date==null)return "";
String dateStr= newSimpleDateFormat(DATE_HH_PATTERN).format(date);if("00:00:00".equals(dateStr)){return newSimpleDateFormat(DATE_PATTERN).format(date);
}return newSimpleDateFormat(PATTERN).format(date);
}
}
2610
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
