ofbiz——excel导出/加载工具类

org.apache.ofbiz.base.util.ExcelUtil类主要实现了excel导出/加载功能。

实验室预约系统中主要使用了加载功能。使用方法：

List<String[]> list = ExcelUtil.readExcel(file);

这样excel表格里的数据就解析到了list对象里了。

需要依赖的jar包：commons-beanutils-1.9.1.jar，poi-3.13.jar，poi-ooxml-3.13.jar

ExcelUtil.java

package org.apache.ofbiz.base.util;


import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;

/**
 * 利用开源组件POI动态导出EXCEL文档,以及加载excel文档
 */
public class ExcelUtil {

	private static final float DEFAULT_ROW_HEIGHT=25.0f;
	public static boolean isEmpty(String format) {
		if (format == null || format.trim().equals("")) {
			return true;
		}
		return false;
	}
	public static void readExcel(File xlsfile,SheetHandler handler) {
		
		SimpleDateFormat dtf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    try(FileInputStream is = new FileInputStream(xlsfile)) {  
	        //同时支持Excel 2003、2007  
	        Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的  
            Sheet sheet = workbook.getSheetAt(0);  
            //int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
            int rowCount = sheet.getLastRowNum() + 1; //获取总行数
            handler.doStart(sheet);
            for (int i = 0; i < rowCount; i++) {  
                Row row = sheet.getRow(i);  
                int cellCount = 0;
                if (row != null) {//row  该行所有单元格为空时，row是null值    2017-01-05 pelin
                	//cellCount = row.getPhysicalNumberOfCells(); //获取总列数  
                	cellCount = row.getLastCellNum();//获取最后一个不为空的列是第几个。 
                	cellCount = cellCount < 0 ? 0 : cellCount;//getLastCellNum没有单元格时会返回负数
				}
                String[] cells=new String[cellCount];
                
                for (int c = 0; c < cellCount; c++) {  
                    Cell cell = row.getCell(c);  
                    String cellValue = null;  
                    if (cell == null) {//cell 单元格为空时，cell是null值    2017-01-05 pelin
                    	cellValue = "";
					}else{
						switch(cell.getCellType()) {  
	                        case Cell.CELL_TYPE_STRING: //文本  
	                            cellValue = cell.getStringCellValue();  
	                            break;  
	                        case Cell.CELL_TYPE_NUMERIC: //数字、日期  
	                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
	                            	Date theDate = cell.getDateCellValue();
	                            	cellValue = dtf.format(theDate);
	                            } else {
	                            	cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());  
	                            }
	                            break;  
	                        case Cell.CELL_TYPE_BOOLEAN: //布尔型  
	                            cellValue = String.valueOf(cell.getBooleanCellValue());  
	                            break;  
	                        case Cell.CELL_TYPE_BLANK: //空白  
	                            cellValue = cell.getStringCellValue();  
	                            break;  
	                        case Cell.CELL_TYPE_FORMULA: //公式  
	                            //cellValue = cell.getCellFormula();  
	                         // cell.getCellFormula();
								try {
									cellValue = String.valueOf(cell.getNumericCellValue());
								} catch (IllegalStateException e) {
									cellValue = String.valueOf(cell.getRichStringCellValue());
								}
	                            break;  
	                        default:  
	                            cellValue = "#ERROR#";  
						}  
					}
                    cells[c]=cellValue;
                }
                handler.doRow(i,cells);
            }  
            handler.doEnd(sheet);
	    } catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 导出xls
	 * @param title 表格标题名
	 * @param headers 表格属性列名数组
	 * @param dataset 需要显示的数据集合,dataset中可以是对象或数组
	 * @param mergedRegion 合并区别
	 * @param out xls输出流
	 */
	@SuppressWarnings("all")
	public static void exportExcel(String title, Column[] columns, Collection<?> dataset, OutputStream out) {
		Map options = new HashMap();
		options.put("title", title);
		options.put("columns", columns);
		options.put("dataset", dataset);
		options.put("outputStream", out);
//		Map<String,Object> options=MapUtil.buildMap("title",title,"columns",columns,"dataset",dataset,"outputStream",out);
		exportExcel(options);
	}
	
	public static void exportExcel(Map<String,Object> options) {
		
		String title=(String)options.get("title");
		Column[] columns=(Column[])options.get("columns");
		Column[][] nestedColumns=(Column[][])options.get("nestedColumns");
		Collection<?> dataset=(Collection<?>)options.get("dataset");
		Collection<MergedRegion> mergedRegions=(Collection<MergedRegion>)options.get("mergedRegions");
		OutputStream out=(OutputStream)options.get("outputStream");
		boolean showTitle=true;
		if(options.containsKey("showTitle")) {
			showTitle=(boolean)options.get("showTitle");
		}
		boolean showHeader=true;
		if(options.containsKey("showHeader")) {
			showHeader=(boolean)options.get("showHeader");
		}
		boolean forceFormula=false;
		if(options.containsKey("forceFormula")) {
			forceFormula=(boolean)options.get("forceFormula");
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();		// 声明一个工作薄
		HSSFSheet sheet=workbook.createSheet(title);	//生成一个指定名称的sheet
		sheet.setDefaultColumnWidth(256*15);			// 设置表格默认列宽度为15个字符
		sheet.setDefaultRowHeightInPoints(DEFAULT_ROW_HEIGHT);
		
		//设置列宽
		for (int i = 0; i < columns.length; i++) {
			if(columns[i].getWidth()!=null) {
				sheet.setColumnWidth(i, 256*columns[i].getWidth());
			}
		}
		
		//设置起始行
		int startrow=0;
		
		//构建标题头
		if(showTitle) {
			buildTitle(workbook,sheet,title,columns.length);
			startrow++;
		}
		
		//构建表格列头
		if(showHeader) {
			if(nestedColumns!=null) {
				buildNestHeader(workbook, sheet, nestedColumns, startrow);
				startrow+=nestedColumns.length;
			}
			buildHeader(workbook, sheet, columns, startrow);
			startrow++;
		}
		
		//构建表格体
		if(dataset!=null && dataset.size()>0) {
			buildBody(workbook, sheet, columns, dataset, startrow);
		}
		
		//构建合并区域
		if(mergedRegions!=null && mergedRegions.size()>0) {
			buildMergedRegion(workbook, sheet, mergedRegions);
		}
		
		//强制重新计算公式
		if(forceFormula) {
			sheet.setForceFormulaRecalculation(true);
		}
		
		try {
			workbook.write(out);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}
	
	
	private static void buildTitle(HSSFWorkbook workbook, HSSFSheet sheet, String title,int colspan) {
		
		HSSFCellStyle titleStyle = createTitleStyle(workbook);
		
		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		row.setHeightInPoints(40.0f);
		
		HSSFCell cell = row.createCell(0);
		cell.setCellStyle(titleStyle);
		HSSFRichTextString text = new HSSFRichTextString(title);
		cell.setCellValue(text);
		
		for(int i=1;i<colspan;i++) {
			row.createCell(i);
		}
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colspan-1));
	}

	/**
	 * 生成标题单元格样式
	 * @param workbook
	 * @return
	 */
	private static HSSFCellStyle createTitleStyle(HSSFWorkbook workbook) {
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 18);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		titleStyle.setFont(font);
		return titleStyle;
	}

	private static void buildNestHeader(HSSFWorkbook workbook, HSSFSheet sheet, Column[][] nestedColumns,int startrow) {
		
		HSSFCellStyle headerStyle = createHeaderStyle(workbook);
		
		for (int i = 0; i < nestedColumns.length; i++) {
			//产生表格嵌套列头
			HSSFRow row = sheet.createRow(startrow+i);
			row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
			
			for (int j = 0; j < nestedColumns[i].length; j++) {
				HSSFCell cell = row.createCell(j);
				cell.setCellStyle(headerStyle);
				HSSFRichTextString text = new HSSFRichTextString(nestedColumns[i][j].getName());
				cell.setCellValue(text);
			}
		}
	}

	private static void buildHeader(HSSFWorkbook workbook, HSSFSheet sheet, Column[] columns,int startrow) {
		
		HSSFCellStyle headerStyle = createHeaderStyle(workbook);
		
		//产生表格列头
		HSSFRow row = sheet.createRow(startrow);
		row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
		for (int i = 0; i < columns.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(headerStyle);
			HSSFRichTextString text = new HSSFRichTextString(columns[i].getName());
			cell.setCellValue(text);
		}
	}

	/**
	 * 生成列表头单元格样式
	 * @param workbook
	 * @return
	 */
	private static HSSFCellStyle createHeaderStyle(HSSFWorkbook workbook) {
		
		HSSFCellStyle headerStyle = workbook.createCellStyle();
//		headerStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
//		headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		//font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		headerStyle.setFont(font);
		return headerStyle;
	}

	private static void buildBody(HSSFWorkbook workbook, HSSFSheet sheet, Column[] columns, Collection<?> dataset,int startrow) {
		
		//创建各类型单元格样式
		HSSFCellStyle dataStyle = createDataStyle(workbook);
		
		HSSFCellStyle integerStyle = workbook.createCellStyle();
		integerStyle.cloneStyleFrom(dataStyle);
		integerStyle.setDataFormat(workbook.createDataFormat().getFormat("#,###"));
		
		HSSFCellStyle decmalStyle = workbook.createCellStyle();
		decmalStyle.cloneStyleFrom(dataStyle);
		decmalStyle.setDataFormat(workbook.createDataFormat().getFormat("#,###.00"));
		
		HSSFCellStyle dateStyle = workbook.createCellStyle();
		dateStyle.cloneStyleFrom(dataStyle);
		dateStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy/m/d"));
		
		HSSFCellStyle datetimeStyle = workbook.createCellStyle();
		datetimeStyle.cloneStyleFrom(dataStyle);
		datetimeStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy/m/d h:mm:ss"));
		
		
		// 遍历集合数据，填充数据行到excel
		Iterator<?> it = dataset.iterator();
		for (int index = startrow;it.hasNext();index++) {
			
			Object rowdata = it.next();
			Object[] rowval = rowdata.getClass().isArray()?(Object[])rowdata:null;
			
			HSSFRow row = sheet.createRow(index);
			row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
			
			//遍历每行的各列数据
			for(int j=0;j<columns.length;j++) {
				HSSFCell cell = row.createCell(j);
				cell.setCellStyle(dataStyle);
				
				Column column = columns[j];	//当前列定义
				Object value = null;	//当前列值
				
				//判断是否有列格式化器
				if(column.getFormatter()==null) {
					if(rowval!=null) {		//如果源数据是数组,那么直接从数组中取
						value = rowval[j];
					} else {				//否则源数据是pojo类型
						//如果是自增类型字段,那么自动计算自增值,否则从对象属性中取值
						if("increment".equals(column.getType().toLowerCase())) {
							value=index-1;
						} else {
							try {
								value=PropertyUtils.getProperty(rowdata, column.getField());
							} catch (Exception e) {
								throw new RuntimeException(e);
							}
						}
					}
				} else {
					value=column.getFormatter().format(rowdata);
				}
				
				//判断列类型,决定如何显示数据
				switch(column.getType().toLowerCase()) {
				case "string":		//字符类型
					if(value!=null) {
						cell.setCellValue(value.toString());
					}
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					break;
				case "integer":		//整数类型
					if(isEmpty(column.getFormat()) || "#,###".equals(column.getFormat())) {
						cell.setCellStyle(integerStyle);
					} else {
						HSSFCellStyle customStyle = workbook.createCellStyle();
						customStyle.cloneStyleFrom(dataStyle);
						customStyle.setDataFormat(workbook.createDataFormat().getFormat(column.getFormat()));
						cell.setCellStyle(customStyle);
					}
					if(value!=null) {
						cell.setCellValue(new Double(value.toString()));
					}
					cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					break;
				case "decimal":		//小数类型
					if(isEmpty(column.getFormat()) || "#,###.00".equals(column.getFormat())) {
						cell.setCellStyle(decmalStyle);
					} else {
						HSSFCellStyle customStyle = workbook.createCellStyle();
						customStyle.cloneStyleFrom(dataStyle);
						customStyle.setDataFormat(workbook.createDataFormat().getFormat(column.getFormat()));
						cell.setCellStyle(customStyle);
					}
					if(value!=null) {
						cell.setCellValue(new Double(value.toString()));
					}
					cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					break;
				case "date":	//日期类型
					if(isEmpty(column.getFormat()) || "yyyy/m/d".equals(column.getFormat())) {
						cell.setCellStyle(dateStyle);
					} else {
						HSSFCellStyle customStyle = workbook.createCellStyle();
						customStyle.cloneStyleFrom(dataStyle);
						customStyle.setDataFormat(workbook.createDataFormat().getFormat(column.getFormat()));
						cell.setCellStyle(customStyle);
					}
					if(value!=null) {
						cell.setCellValue((Date)value);
					}
					break;
				case "datetime":	//日期时间类型
					if(isEmpty(column.getFormat()) || "yyyy/m/d h:mm:ss".equals(column.getFormat())) {
						cell.setCellStyle(datetimeStyle);
					} else {
						HSSFCellStyle customStyle = workbook.createCellStyle();
						customStyle.cloneStyleFrom(dataStyle);
						customStyle.setDataFormat(workbook.createDataFormat().getFormat(column.getFormat()));
						cell.setCellStyle(customStyle);
					}
					if(value!=null) {
						cell.setCellValue((Date)value);
					}
					break;
				case "increment":	//自增类型
					cell.setCellValue(new Double(value.toString()));
					cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					break;
				case "formula":		//公式类型
					if(value!=null) {
						if(value.toString().startsWith("=")) {
							cell.setCellFormula(value.toString().substring(1));
						} else {
							cell.setCellFormula(value.toString());
						}
						cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
					}
					break;
				default:		//默认自动判断决定什么类型
					
					if(value==null) break;		//如果值为null,直接退出switch
					
					if(value instanceof Date) {
						cell.setCellValue((Date)value);
					} else if(value instanceof Integer || value instanceof Short || value instanceof Long || value instanceof BigInteger) {
						cell.setCellValue(new Double(value.toString()));
						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					} else if(value instanceof Float || value instanceof Double || value instanceof BigDecimal) {
						cell.setCellValue(new Double(value.toString()));
						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					} else {	//否则是字符类型,还要更深层的判断,是数字字符,还是公式
						
						String cellval=value.toString();
						try {
							Double dec=Double.parseDouble(cellval);
							cell.setCellValue(dec);
							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
						} catch(Exception e) {
							if(cellval.startsWith("=")) {
								cell.setCellFormula(cellval.substring(1));
								cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
							} else {
								cell.setCellValue(cellval);
								cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							}
						}
					}
				}
			}
		}
	}

	/**
	 * 构建合并区域
	 * @param workbook
	 * @param sheet
	 * @param mergedRegion
	 */
	private static void buildMergedRegion(HSSFWorkbook workbook, HSSFSheet sheet, Collection<MergedRegion> mergedRegion) {
		
		for(MergedRegion merge:mergedRegion) {
			sheet.addMergedRegion(new CellRangeAddress(merge.getFirstRow(), merge.getLastRow(), merge.getFirstCol(), merge.getLastCol()));
		}
	}

	/**
	 * 生成列表体单元格样式
	 * @param workbook
	 * @return
	 */
	private static HSSFCellStyle createDataStyle(HSSFWorkbook workbook) {
		
		HSSFCellStyle defaultDataStyle = workbook.createCellStyle();
//		cellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
//		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		defaultDataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		defaultDataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		defaultDataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		defaultDataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		defaultDataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		defaultDataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		HSSFFont font = workbook.createFont();
		//font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		font.setFontHeightInPoints((short) 12);
		defaultDataStyle.setFont(font);
		return defaultDataStyle;
	}
	
	/**
	 * xls列定义
	 * @author zhangle
	 */
	public static class Column {
		
		private String name;		//列头名
		private String type;		//列类型
		private String format;		//列的excel格式
		private Integer width;		//列宽度
		private String field;		//用于填充此列的对象属性,配置此属性表示数据源是对象,否则数据源是数组
		private Formatter<?> formatter;//格式化器
		
		public Column(String name,String type) {
			this.name=name;
			this.type=type;
		}
		
		public Column(String name,String type,String format,Integer width) {
			this.name=name;
			this.type=type;
			this.format=format;
			this.width=width;
		}
		public Column(String name,String type,String format,Integer width,String field) {
			this(name,type,format,width);
			this.field=field;
		}
		public Column(String name,String type,String format,Integer width,String field,Formatter<?> formatter) {
			this(name,type,format,width,field);
			this.formatter=formatter;
		}
		
		public String getName() {
			return name;
		}
		public String getType() {
			return type;
		}
		public String getFormat() {
			return format;
		}
		public Integer getWidth() {
			return width;
		}
		public void setName(String name) {
			this.name = name;
		}
		public void setType(String type) {
			this.type = type;
		}
		public void setFormat(String format) {
			this.format = format;
		}
		public void setWidth(Integer width) {
			this.width = width;
		}
		public String getField() {
			return field;
		}
		public void setField(String field) {
			this.field = field;
		}
		public Formatter getFormatter() {
			return formatter;
		}
		public void setFormatter(Formatter formatter) {
			this.formatter = formatter;
		}
	}
	
	/**
	 * 列格式化器
	 * @author zhangle
	 */
//	@FunctionalInterface
	public static interface Formatter<T> {
		public Object format(T data);
	}
	
	/**
	 * excel行处理器
	 * @author zhangle
	 */
	public static interface SheetHandler {
		public void doStart(Sheet sheet);
		public void doRow(int index,String[] cells);
		public void doEnd(Sheet sheet);
	}
	
	/**
	 * 合并区域定义
	 * @author zhangle
	 */
	public static class MergedRegion {
		private int firstRow;
		private int lastRow;
		private int firstCol;
		private int lastCol;
		
		public MergedRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
			this.firstRow=firstRow;
			this.lastRow=lastRow;
			this.firstCol=firstCol;
			this.lastCol=lastCol;
		}

		public int getFirstRow() {
			return firstRow;
		}

		public int getLastRow() {
			return lastRow;
		}

		public int getFirstCol() {
			return firstCol;
		}

		public int getLastCol() {
			return lastCol;
		}

		public void setFirstRow(int firstRow) {
			this.firstRow = firstRow;
		}

		public void setLastRow(int lastRow) {
			this.lastRow = lastRow;
		}

		public void setFirstCol(int firstCol) {
			this.firstCol = firstCol;
		}

		public void setLastCol(int lastCol) {
			this.lastCol = lastCol;
		}
	}
	
	static class ImportFile implements ExcelUtil.SheetHandler {
		private List<String[]> cellList = new ArrayList<String[]>();
		
		public List<String[]> getCellList(){
			return cellList;
		}
				
		@Override
		public void doStart(Sheet sheet) {
			// TODO Auto-generated method stub
		}
		@Override
		public void doRow(int index, String[] cells) {
			cellList.add(cells);
		}
		
		@Override
		public void doEnd(Sheet sheet) {
			// TODO Auto-generated method stub

		}
	}
	public static List<String[]> readExcel(File file){
		ImportFile importFile = new ImportFile();
		ExcelUtil.readExcel(file, importFile);
		return importFile.getCellList();
	}
}