ofbiz——excel导出/加载工具类
org.apache.ofbiz.base.util.ExcelUtil类主要实现了excel导出/加载功能。
实验室预约系统中主要使用了加载功能。使用方法：
List
这样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 pelincellValue = "";}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 options=MapUtil.buildMap("title",title,"columns",columns,"dataset",dataset,"outputStream",out);exportExcel(options);}public static void exportExcel(Map 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 mergedRegions=(Collection)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); //生成一个指定名称的sheetsheet.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 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"));// 遍历集合数据，填充数据行到excelIterator 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 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*/
// @FunctionalInterfacepublic static interface Formatter {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 cellList = new ArrayList();public List getCellList(){return cellList;}@Overridepublic void doStart(Sheet sheet) {// TODO Auto-generated method stub}@Overridepublic void doRow(int index, String[] cells) {cellList.add(cells);}@Overridepublic void doEnd(Sheet sheet) {// TODO Auto-generated method stub}}public static List readExcel(File file){ImportFile importFile = new ImportFile();ExcelUtil.readExcel(file, importFile);return importFile.getCellList();}
}
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！