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();
}
}