JAVA生成Excel代码实现

package com.woniu.utils;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class Xls {
	public static <T extends Object> Workbook createExcel(Map<String,String> columnMap,List<T> dataList,String sheelName,String fileName) {
		int sheetRow=500;
		sheetRow=sheetRow<0?200:sheetRow;
		//第一步，创建一个webbook，对应一个excel文件，内存中sheetRow条记录后提交。
		Workbook wb=new SXSSFWorkbook(sheetRow);
		if(columnMap!=null&&dataList!=null) {
			//获取需要生成的sheet数，以防数据量过大报错
			int sheetCount=getSheetCount(sheetRow,dataList.size());
			//循环生成sheet
			for(int i=0;i<sheetCount;i++) {
				//第二步，在webbook中添加一个sheet，对应Excel文件中的sheet
				Sheet sheet=wb.createSheet(sheelName);
				//第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数有限制short
				Row titleRow=sheet.createRow(0);
				//第四步，创建单元格，并设置表头，设置表头居中
				CellStyle style=wb.createCellStyle();
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				Set<String> keys=columnMap.keySet();
				//第一行（标题行）合并并设置标题
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, keys!=null&&keys.size()>0?keys.size()-1:0));
				Cell titleCell=titleRow.createCell(0);
				titleCell.setCellStyle(style);
				titleCell.setCellValue(fileName);
				
				//创建表头行
				Row headRow=sheet.createRow(1);
				//生成表头行
				int j=0;
				for(String key:keys) {
					Cell hCell=headRow.createCell(j++);
					hCell.setCellStyle(style);
					hCell.setCellValue(columnMap.get(key));
				}
				//从第三行开始填充数据
				int c=2;
				for(int k=i*sheetRow;k<dataList.size()&&k<(i+1)*sheetRow;k++) {
					T t=dataList.get(k);
					Row dataRow=sheet.createRow(c++);
					//循环取值进行数据填充
					int v=0;
					for(String key:keys) {
						try {
							Object val=BeanUtils.getProperty(t, key);
							if(val instanceof Long) {
								dataRow.createCell(v++).setCellValue((Long)val);
							}else if(val instanceof Double){
								dataRow.createCell(v++).setCellValue((Double)val);
							}else if(val instanceof Date) {
								dataRow.createCell(v++).setCellValue((Date)val);
							}else if(val instanceof Calendar) {
								dataRow.createCell(v++).setCellValue((Calendar)val);
							}else if(val instanceof Boolean) {
								dataRow.createCell(v++).setCellValue((Boolean)val);
							}else if(val==null) {
								dataRow.createCell(v++).setCellValue("");
							}else {
								dataRow.createCell(v++).setCellValue((String)val);
							}
						} catch (Exception e) {
							System.out.println("导出Excel-表格赋值异常");
						}
					}
				}
				//设置列宽
				setColumnWidth(sheet,columnMap);
			}
		}
		return wb;
	}
	public static void setColumnWidth(Sheet sheet,Map<String,String>columnMap) {
		if(sheet!=null&&columnMap!=null) {
			List<String> list=new ArrayList<String>(columnMap.keySet());
			for(int i=0;i<list.size();i++) {
				sheet.autoSizeColumn(i,true);				
			}
		}
	}
	public static int getSheetCount(int sheelRow,int total) {
		if(total==0||total<sheelRow)return 1;
		return total%sheelRow>0?total/sheelRow+1:total/sheelRow;
	}
}