欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

java使用poi导出Excel

程序员文章站 2022-07-13 12:41:51
...

通过借鉴别人的文档,然后在结合自己的实践得到的结果(效率还有待测试)

1. 中间有两个导出Excel的方式

a. 单独导出一个Excel在一个页面单独展示

效果如下:

java使用poi导出Excel

b. 在一个页面中同时展示多个Excel

效果如下:

java使用poi导出Excel

代码如下:

package com.dmsoft.dmcounter.common;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hpsf.Date;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.struts2.ServletActionContext;
/**
 * Excel导出
 * @author User
 *
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public class PoiExcelExport {
	HttpServletResponse response;
	// 文件名
	private String fileName ;
	//文件保存路径
	private String fileDir;
	//sheet名
	private String sheetName;
	//表头字体
	private String titleFontType = "Arial Unicode MS";
	//表头背景色
	private String titleBackColor = "C1FBEE";
	//表头字号
	private short titleFontSize = 12;
	//添加自动筛选的列 如 A:M
	private String address = "";
	//正文字体
	private String contentFontType = "Arial Unicode MS";
	//正文字号
	private short contentFontSize = 12;
	//设置列的公式
	private String colFormula[] = null;
	
	private HSSFWorkbook workbook = null;
	
	public PoiExcelExport(String fileDir,String sheetName){
	     this.fileDir = fileDir;
	     this.sheetName = sheetName;
	     workbook = new HSSFWorkbook();
	}
	
	public PoiExcelExport(HttpServletResponse response,String fileName,String sheetName){
		 this.response = response;
		 this.fileName = fileName;
		 this.sheetName = sheetName;
	     workbook = new HSSFWorkbook();
	}
   /**
    * 设置表头字体.
    * @param titleFontType
    */
	public void setTitleFontType(String titleFontType) {
		this.titleFontType = titleFontType;
	}
   /**
    * 设置表头背景色.
    * @param titleBackColor 十六进制
    */
	public void setTitleBackColor(String titleBackColor) {
		this.titleBackColor = titleBackColor;
	}
   /**
    * 设置表头字体大小.
    * @param titleFontSize
    */
	public void setTitleFontSize(short titleFontSize) {
		this.titleFontSize = titleFontSize;
	}
   /**
    * 设置表头自动筛选栏位,如A:AC.
    * @param address
    */
	public void setAddress(String address) {
		this.address = address;
	}
   /**
    * 设置正文字体.
    * @param contentFontType
    */
	public void setContentFontType(String contentFontType) {
		this.contentFontType = contentFontType;
	}
   /**
    * 设置正文字号.
    * @param contentFontSize
    */
	public void setContentFontSize(short contentFontSize) {
		this.contentFontSize = contentFontSize;
	}
	/**
	 * 设置列的公式 
	 * @param colFormula  存储i-1列的公式 涉及到的行号使用@替换 如[email protected][email protected]
	 */
	public void setColFormula(String[] colFormula) {
		this.colFormula = colFormula;
	}
	/**
	 * 导出excel.在一个页面中单独导出Excel
	 * @param titleColumn  对应bean的属性名
	 * @param titleName   excel要导出的表名
	 * @param titleSize   列宽
	 * @param dataList  数据
	 */
	public void wirteExcel(String titleColumn[],String titleName[],int titleSize[],List<?> dataList){
		HttpServletRequest request = ServletActionContext.getRequest();
		//添加Worksheet(不添加sheet时生成的xls文件打开时会报错)
		Sheet sheet = workbook.createSheet(this.sheetName);  
		//新建文件
		OutputStream out = null;
		try {	 
			if(fileDir!=null){
				deleteExcel(fileDir);
				//有文件路径
				out = new FileOutputStream(fileDir);    			
			}else{
				//否则,直接写到输出流中
				out = response.getOutputStream();
				fileName = fileName+".xls";
				response.setContentType("application/x-msdownload");
				final String userAgent = request.getHeader("USER-AGENT");  //获取浏览器的代理
				//下面主要是让文件名适应不同浏览器的编码格式
				String finalFileName = null;
				if(StringUtils.contains(userAgent, "MSIE")) {
					finalFileName = URLEncoder.encode(fileName,"UTF8");
				}else if(StringUtils.contains(userAgent, "Mozilla")){//google,火狐浏览器
					finalFileName = new String(fileName.getBytes(), "ISO8859-1");
				}else{
					finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器
				}
				response.setHeader("Content-Disposition", "attachment; filename=\"" + 
						finalFileName + "\"");
			}
			
			//写入excel的表头
			Row titleNameRow = workbook.getSheet(sheetName).createRow(0); 
			
			//设置样式
			HSSFCellStyle titleStyle = workbook.createCellStyle();  
			titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, titleFontType, (short) titleFontSize);
			titleStyle = (HSSFCellStyle) setColor(titleStyle, titleBackColor, (short)10);
			titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			
			for(int i = 0;i < titleName.length;i++){
				sheet.setColumnWidth(i, titleSize[i]*256);    //设置宽度   		
				Cell cell = titleNameRow.createCell(i);
				cell.setCellStyle(titleStyle);
				cell.setCellValue(titleName[i].toString());
			}
			
			//为表头添加自动筛选
			if(!"".equals(address)){
				CellRangeAddress c = (CellRangeAddress) CellRangeAddress.valueOf(address);
				sheet.setAutoFilter(c);
			}
			
				//设置样式
				titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, contentFontType, (short) contentFontSize);
				//通过反射获取数据并写入到excel中
				if(dataList!=null&&dataList.size()>0){
				if(titleColumn.length>0){
					for(int rowIndex = 1;rowIndex<=dataList.size();rowIndex++){
						Object obj = dataList.get(rowIndex-1);     //获得该对象
						Class clsss = obj.getClass();     //获得该对对象的class实例
						HSSFSheet sheet2 = workbook.getSheet(sheetName);
						Row dataRow = sheet2.createRow(rowIndex);  
						
						for(int columnIndex = 0;columnIndex<titleColumn.length;columnIndex++){
							String title = titleColumn[columnIndex].toString().trim();
							if(!"".equals(title)){  //字段不为空
								//使首字母大写
								String UTitle = Character.toUpperCase(title.charAt(0))+ title.substring(1, title.length()); // 使其首字母大写;
								String methodName  = "get"+UTitle;
								
								// 设置要执行的方法
								Method method = clsss.getDeclaredMethod(methodName); 
								
								//获取返回类型
								Class<?> returnType2 = method.getReturnType();
								String returnType = returnType2.getName(); 
								String data = method.invoke(obj)==null?"":method.invoke(obj).toString();
								Cell cell = dataRow.createCell(columnIndex);
								cell.setCellStyle(createCellContentStyle(workbook));
								if(data!=null&&!"".equals(data)){
									if("int".equals(returnType)){
										cell.setCellValue(Integer.parseInt(data));
										cell.setCellStyle(createCellContent4IntegerStyle(workbook));
									}else if("long".equals(returnType)){
										cell.setCellValue(Long.parseLong(data));
										cell.setCellStyle(createCellContent4IntegerStyle(workbook));
									}else if("float".equals(returnType)){
										cell.setCellValue(Float.parseFloat(data));
										cell.setCellStyle(createCellContent4DoubleStyle(workbook));
									}else if("double".equals(returnType)){
										cell.setCellValue(Double.parseDouble(data));
										cell.setCellStyle(createCellContent4DoubleStyle(workbook));
									}else{
										if(data.matches("\\d+")==true) {//判断能否转成数字
//											if(Long.parseLong(data)<2147483647) {//可转int类型
//												cell.setCellValue(Integer.parseInt(data));
//											}else {  //大于int类型
//												cell.setCellValue(data);  //cell.setCellValue(Long.parseLong(data));
//											}
											cell.setCellValue("'"+data);//转换成文本形式 避免 0001导出变成1
										}else {
											cell.setCellValue(data);
										}
									}
								}
							}else{   //字段为空 检查该列是否是公式
								if(colFormula!=null){
									String sixBuf = colFormula[columnIndex].replace("@", (rowIndex+1)+"");
									Cell cell = dataRow.createCell(columnIndex);
									cell.setCellFormula(sixBuf.toString());
								}
							}
						}
					}
					
				}
			}
			
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {  
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}  
	}
	
	/**
	 * 导出excel.在一个页面中同时展示多个Excel
	 * @param titleColumn  对应bean的属性名
	 * @param titleName   excel要导出的表名
	 * @param titleSize   列宽
	 * @param dataList  数据
	 */
	
	public void wirteMultiExcel (List<ArrayList<?>> titleColumn,List<ArrayList<?>> titleName,List<ArrayList<?>> dataList,int[] ...titleSize){
		HttpServletRequest request = ServletActionContext.getRequest();
		//添加Worksheet(不添加sheet时生成的xls文件打开时会报错)
		Sheet sheet = workbook.createSheet(this.sheetName);  
		//新建文件
		OutputStream out = null;
		try {	 
			if(fileDir!=null){
				//有文件路径
				out = new FileOutputStream(fileDir);    			
			}else{
				//否则,直接写到输出流中
				out = response.getOutputStream();
				fileName = fileName+".xls";
				response.setContentType("application/x-msdownload");
				final String userAgent = request.getHeader("USER-AGENT");  //获取浏览器的代理
				//下面主要是让文件名适应不同浏览器的编码格式
				String finalFileName = null;
				if(StringUtils.contains(userAgent, "MSIE")) {
					finalFileName = URLEncoder.encode(fileName,"UTF8");
				}else if(StringUtils.contains(userAgent, "Mozilla")){//google,火狐浏览器
					finalFileName = new String(fileName.getBytes(), "ISO8859-1");
				}else{
					finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器
				}
				response.setHeader("Content-Disposition", "attachment; filename=\"" + 
						finalFileName + "\"");
			}
			int titleRow = 0;//列头位置
			
			ArrayList<?> arrayList = null;
			for (int i = 0; i < titleName.size(); i++) {//遍历所有的标题name
				arrayList = titleName.get(i);
			}
			ArrayList<?> arrayList2 = null;
			for (int i = 0; i < titleColumn.size(); i++) {//获取所有的列头表明
				arrayList2 = titleColumn.get(i);
			}
			
			if(dataList != null && dataList.size() > 0) {
				for (int i = 0; i < dataList.size(); i++) {
					if(dataList.get(0) == null) {
						workbook.write(out);
					}
					if(i != 0 && dataList.get(i) != null) {
						titleRow += dataList.get(i-1).size()+2;
					}
					Row titleNameRow = workbook.getSheet(sheetName).createRow(titleRow); //列头
					//设置样式
					HSSFCellStyle titleStyle = workbook.createCellStyle();  
					titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, titleFontType, (short) titleFontSize);
					titleStyle = (HSSFCellStyle) setColor(titleStyle, titleBackColor, (short)10);
					
					Object[] titleNameArray = null;
					for (int i1 = 0; i1 < arrayList.size(); i1++) {//将遍历name转换成array
						List<String> list = (List<String>)arrayList.get(i);
						titleNameArray = list.toArray();
					}
					int[] titleSizearr = null;
					for (int i1 = 0; i1 < titleSize.length; i1++) {	//遍历得到的所有列头宽
						titleSizearr = titleSize[i];
					}
					for (int i1 = 0; i1 < titleNameArray.length; i1++) {//设置列头的样式与宽度
						sheet.setColumnWidth(i1, titleSizearr[i1]*256);    //设置宽度   		
						Cell cell = titleNameRow.createCell(i1);
						cell.setCellStyle(titleStyle);
						cell.setCellValue(titleNameArray[i1].toString());
					}
					Object[]  titleColumnArray = null;
					for (int i1 = 0; i1 < arrayList2.size(); i1++) {//将所有的列头表明转换为数组
						List<String> titleColumns = (List<String>)arrayList2.get(i);
						titleColumnArray = titleColumns.toArray();
					}
					ArrayList<?> dataListArray = null;
					for (int i1 = 0; i1 < dataList.size(); i1++) {
						dataListArray = dataList.get(i);
					}
					//为表头添加自动筛选
					if(!"".equals(address)){
						CellRangeAddress c = (CellRangeAddress) CellRangeAddress.valueOf(address);
						sheet.setAutoFilter(c);
					}		
					if(dataListArray != null && dataListArray.size() != 0) {
						//设置样式
//						HSSFCellStyle dataStyle = workbook.createCellStyle();  
						titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, contentFontType, (short) contentFontSize);
						
						if(titleColumnArray.length>0){
							for(int rowIndex = 1;rowIndex<=dataListArray.size();rowIndex++){
		//						titleRow += dataListArray.size() + 2 ;
								//写入excel的表头
								Object obj = dataListArray.get(rowIndex-1);     //获得该对象
								Class clsss = obj.getClass();     //获得该对对象的class实例
								Row dataRow = workbook.getSheet(sheetName).createRow(rowIndex+titleRow); 
								
								for(int columnIndex = 0;columnIndex<titleColumnArray.length;columnIndex++){
									String title = titleColumnArray[columnIndex].toString().trim();
									if(!"".equals(title)){  //字段不为空
										//使首字母大写
										String UTitle = Character.toUpperCase(title.charAt(0))+ title.substring(1, title.length()); // 使其首字母大写;
										String methodName  = "get"+UTitle;
										
										// 设置要执行的方法
										Method method = clsss.getDeclaredMethod(methodName); 
										
										//获取返回类型
										String returnType = method.getReturnType().getName(); 
										
										Object object = dataListArray.get(rowIndex);
										
										String data = method.invoke(obj)==null?"":method.invoke(obj).toString();
										Cell cell = dataRow.createCell(columnIndex);
										cell.setCellStyle(createCellContentStyle(workbook));
										if(data!=null&&!"".equals(data)){
											if("int".equals(returnType)){
												cell.setCellValue(Integer.parseInt(data));
												cell.setCellStyle(createCellContent4IntegerStyle(workbook));
											}else if("long".equals(returnType)){
												cell.setCellValue(Long.parseLong(data));
												cell.setCellStyle(createCellContent4IntegerStyle(workbook));
											}else if("float".equals(returnType)){
												cell.setCellValue(/*floatDecimalFormat.format(*/Float.parseFloat(data)/*)*/);
												cell.setCellStyle(createCellContent4DoubleStyle(workbook));
											}else if("double".equals(returnType)){
												cell.setCellValue(/*doubleDecimalFormat.format(*/Double.parseDouble(data)/*)*/);
												cell.setCellStyle(createCellContent4DoubleStyle(workbook));
											}else if (object instanceof Date) {
												cell.setCellStyle(createCellContentStyle(workbook));
												cell.setCellValue(getCnDate((Date) object));
						                    } 
											
											else{
												if(data.matches("\\d+")==true) {//判断能否转成数字
//													if(Long.parseLong(data)<2147483647) {//可转int类型
//														cell.setCellValue(Integer.parseInt(data));
//													}else {  //大于int类型
//														cell.setCellValue(data);  //cell.setCellValue(Long.parseLong(data));
//													}
													cell.setCellValue("'"+data);//以文本形式导出  避免 0001导出变成1
												}else {
													cell.setCellValue(data);
												}
											}
										}
									}else{   //字段为空 检查该列是否是公式
										if(colFormula!=null){
											String sixBuf = colFormula[columnIndex].replace("@", (rowIndex+1)+"");
											Cell cell = dataRow.createCell(columnIndex);
											cell.setCellFormula(sixBuf.toString());
										}
									}
								}
							}
						}
					}
				}
			}
			workbook.write(out);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {  
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}  
	}
   /**
    * 将16进制的颜色代码写入样式中来设置颜色
    * @param style  保证style统一
    * @param color 颜色:66FFDD
    * @param index 索引 8-64 使用时不可重复
    * @return
    */
   public CellStyle setColor(CellStyle style,String color,short index){
   	if(color!=""&&color!=null){
			//转为RGB码
   		int r = Integer.parseInt((color.substring(0,2)),16);   //转为16进制
   		int g = Integer.parseInt((color.substring(2,4)),16);
   		int b = Integer.parseInt((color.substring(4,6)),16);
   		//自定义cell颜色
   		HSSFPalette palette = workbook.getCustomPalette(); 
   		palette.setColorAtIndex((short)index, (byte) r, (byte) g, (byte) b);
   	
   		style.setFillPattern(CellStyle.SOLID_FOREGROUND); 
   		style.setFillForegroundColor(index);
		}
       return style;	
   }
  
   /**
    * 设置字体并加外边框
    * @param style  样式
    * @param style  字体名
    * @param style  大小
    * @return
    */
   public CellStyle setFontAndBorder(CellStyle style,String fontName,short size){
   	HSSFFont font = workbook.createFont();  
       font.setFontHeightInPoints(size);    
       font.setFontName(fontName); 
//       font.setBold(true);
       style.setFont(font);
       style.setBorderBottom(CellStyle.BORDER_THIN); //下边框    
       style.setBorderLeft(CellStyle.BORDER_THIN);//左边框    
       style.setBorderTop(CellStyle.BORDER_THIN);//上边框    
       style.setBorderRight(CellStyle.BORDER_THIN);//右边框   
       return style;
   }
   /**
	 * 删除文件,当文件名出现重复的时候调用
	 * @param fileDir
	 * @return
	 */
   public boolean deleteExcel(String path){
   	boolean flag = false;
   	File file = new File(path);
   	// 判断目录或文件是否存在  
       if (!file.exists()) {  // 不存在返回 false  
           return flag;  
       } else {  
           // 判断是否为文件  
           if (file.isFile()) {  // 为文件时调用删除文件方法  
               file.delete();
               flag = true;
           } 
       }
       return flag;
   }
   
   /**
    * 单元格样式(Double)列表
    */
   private  CellStyle createCellContent4DoubleStyle(Workbook workbook) {
       CellStyle style = workbook.createCellStyle();
       // 设置边框样式
       style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
       style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
       style.setBorderRight(XSSFCellStyle.BORDER_THIN);
       style.setBorderTop(XSSFCellStyle.BORDER_THIN);
       //设置对齐样式
       style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       // 生成字体
       Font font = workbook.createFont();
       // 正文样式
       style.setFillPattern(XSSFCellStyle.NO_FILL);
       style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
       font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
       // 把字体应用到当前的样式
       style.setFont(font);
       style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
       return style;
   }
   /**
    * 单元格样式(Integer)列表
    */
   private static CellStyle createCellContent4IntegerStyle(Workbook workbook) {
       CellStyle style = workbook.createCellStyle();
       // 设置边框样式
       style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
       style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
       style.setBorderRight(XSSFCellStyle.BORDER_THIN);
       style.setBorderTop(XSSFCellStyle.BORDER_THIN);
       //设置对齐样式
       style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       // 生成字体
       Font font = workbook.createFont();
       // 正文样式
       style.setFillPattern(XSSFCellStyle.NO_FILL);
       style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
       font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
       // 把字体应用到当前的样式
       style.setFont(font);
//       style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));//数据格式只显示整数
       return style;
   }
   /**
    * 创建单元格表头样式
    *
    * @param workbook 工作薄
    */
   private CellStyle createCellHeadStyle(Workbook workbook) {
       CellStyle style = workbook.createCellStyle();
       // 设置边框样式
       style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
       style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
       style.setBorderRight(XSSFCellStyle.BORDER_THIN);
       style.setBorderTop(XSSFCellStyle.BORDER_THIN);
       //设置对齐样式
       style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       // 生成字体
       Font font = workbook.createFont();
       // 表头样式
       style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
       style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
       font.setFontHeightInPoints((short) 12);
       font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
       // 把字体应用到当前的样式
       style.setFont(font);
       return style;
   }
   /**
    * 创建单元格正文样式
    *
    * @param workbook 工作薄
    */
   private static CellStyle createCellContentStyle(Workbook workbook) {
       CellStyle style = workbook.createCellStyle();
       // 设置边框样式
       style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
       style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
       style.setBorderRight(XSSFCellStyle.BORDER_THIN);
       style.setBorderTop(XSSFCellStyle.BORDER_THIN);
       //设置对齐样式
       style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       // 生成字体
       Font font = workbook.createFont();
       // 正文样式
       style.setFillPattern(XSSFCellStyle.NO_FILL);
       style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
       font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
       // 把字体应用到当前的样式
       style.setFont(font);
       return style;
   }
   
   /**
    * 单元格样式列表,方便调用全部
    */
   private Map<String, CellStyle> styleMap(Workbook workbook) {
       Map<String, CellStyle> styleMap = new LinkedHashMap<>();
       styleMap.put("head", createCellHeadStyle(workbook));
       styleMap.put("content", createCellContentStyle(workbook));
       styleMap.put("integer", createCellContent4IntegerStyle(workbook));
       styleMap.put("double", createCellContent4DoubleStyle(workbook));
       return styleMap;
   }
   /**
    *	日期转化为字符串,格式为yyyy-MM-dd HH:mm:ss
    */
   private String getCnDate(Date date) {
       String format = "yyyy-MM-dd HH:mm:ss";
       SimpleDateFormat sdf = new SimpleDateFormat(format);
       return sdf.format(date);
   }
}

a.代码调用

		//	导出Excel
		String titleColumn[] = {"FClientNo","FCurrencyNo","FIsBase","FRate","FBanance","FCanUse","FCanOut","FDeposit","FFee","FFrozen","FFisrtMoney","FCloseMoney","FSelfMoney","FUnexpiredProfit","FKeepDeposit"};
	    String titleName[] = {"资金账号","货币代码","是否基币","与基币的汇率","今权益","今可用 ","今可出","保证金","手续费","冻结资金","优先资金","自有资金","平仓盈亏","未到期平盈","维持保证金"};
	    int titleSize[] = {13,13,10,15,15,15,15,15,15,15,15,15,15,15,15};
	    pee.wirteExcel(titleColumn, titleName, titleSize, searchHistoryGroup);

b.代码调用

		List<ArrayList<?>> titleColumns = new ArrayList<ArrayList<?>>();//对应bean的属性名
		List<ArrayList<?>> titleNames = new ArrayList<ArrayList<?>>();//excel要导出的表名
		List<ArrayList<?>> dataLists = new ArrayList<ArrayList<?>>();//数据
		
//		导出
		String titleColumn[] = {"FClientNo","FCurrencyNo","FIsBase","FRate","FBanance","FCanUse","FCanOut","FDeposit","FFee","FFrozen","FFisrtMoney","FCloseMoney","FSelfMoney","FUnexpiredProfit","FKeepDeposit"};
	    String titleName[] = {"资金账号","货币代码","是否基币","与基币的汇率","今权益","今可用 ","今可出","保证金","手续费","冻结资金","优先资金","自有资金","平仓盈亏","未到期平盈","维持保证金"};
	    int titleSize[] = {13,13,25,25,13,13,13,13,13,13,13,25,13,13,25};
		
//		//导出
		String titleColumn2[] = {"FOrgCode","FOrgName","FClientNo","FName","FCurrencyNo","FStatus","FMoneyType","FMoney","FMoneyBase","FInsertTime","FInsertUserCode","FAuthUserCode","FStatus","FReason"};
		String titleName2[] = {"机构代码","机构名称","客户账号","客户名称","币种","状态 ","资金类型","调整金额","对应基币金额","调整日期","添加人","审核人","审核日期","调整原因"};
		int titleSize2[] = {13,13,25,25,13,13,13,13,13,13,13,25,13,13};
		
		List<?> titleColumnAsList = Arrays.asList(titleColumn);
		List<?> titleColumnAsList2 = Arrays.asList(titleColumn2);
		
		@SuppressWarnings("rawtypes")
		ArrayList titleColumnAsListArr = new ArrayList<String>(); 
		titleColumnAsListArr.add(titleColumnAsList);
		titleColumnAsListArr.add(titleColumnAsList2);
		
		titleColumns.add(titleColumnAsListArr);
		
		List<String> titleNameAsList = Arrays.asList(titleName);
		List<String> titleNameAsList2 = Arrays.asList(titleName2);
		
		@SuppressWarnings("rawtypes")
		ArrayList titleNameAsListListArr = new ArrayList<String>(); 
		
		titleNameAsListListArr.add(titleNameAsList);
		titleNameAsListListArr.add(titleNameAsList2);
		
		titleNames.add(titleNameAsListListArr);
		
		
		dataLists.add(searchHistoryGroup);
		dataLists.add(searchClientChange);
		
		pee.wirteMultiExcel(titleColumns, titleNames, dataLists, titleSize,titleSize2);

3. poi jar下载,点击下载二字

下载二进制的就够了