项目需要读取类似的Excel表:
。
1.建立Excel表整体对象模型:
/*******************************************************************************
******************************************************************************/
package com.hirain.chassis.core.model;
import java.util.ArrayList;
import java.util.List;
/**
* @Version 1.0
* @Created Oct 13, 2015 5:14:31 PM
* @Description
* <p>
* 读取采集的硬件信号与配电柜的关系Excel表(配电柜变量表)
* @Modification
* <p>
* Date Author Version Description
* <p>
*/
public class ExcelData {
private final List<ChassisDevice> chassisDevices = new ArrayList<ChassisDevice>();
/**
* @param chassisDevice
* 配电柜中的变量类别
*/
public void addChassisDevice(final ChassisDevice chassisDevice) {
chassisDevices.add(chassisDevice);
}
/**
* @return 配电柜中的变量的所有种类
*/
public List<ChassisDevice> getChassisDevices() {
return chassisDevices;
}
}
2.父类别模型:
/*******************************************************************************
******************************************************************************/
package com.hirain.chassis.core.model;
import java.util.ArrayList;
import java.util.List;
/**
* @Version 1.0
* @Created Oct 13, 2015 5:41:55 PM
* @Description
* <p>
* 机柜变量的类:继电器、继电器盘。。(配电柜可能有多个相同配电柜类型的配电柜变量)
* @Modification
* <p>
* Date Author Version Description
* <p>
*/
public class ChassisDevice {
private final List<ChassisVariable> chassisVariables = new ArrayList<ChassisVariable>();
private String name;
public String getName() {
return name;
}
public void setName(final String name) {
this.name = name;
}
/**
* @param chassisVariable
* 配电柜变量(配电柜可能有多个相同配电柜类型的配电柜变量)
*/
public void addChassisVariable(final ChassisVariable chassisVariable) {
chassisVariables.add(chassisVariable);
}
public List<ChassisVariable> getChassisVariables() {
return chassisVariables;
}
}
3.子类别模型:
/*******************************************************************************
******************************************************************************/
package com.hirain.chassis.core.model;
/**
* @Version 1.0
* @Created Oct 10, 2015 5:55:37 PM
* @Description
* <p>
* 配电柜的变量,一个配电柜变量由N个信号逻辑组合
* @Modification
* <p>
* Date Author Version Description
* <p>
*/
public class ChassisVariable {
private String id;
private String name;
private String value;
public String getId() {
return id;
}
public void setId(final String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(final String name) {
this.name = name;
}
public String getValue() {
return value;
}
public void setValue(final String value) {
this.value = value;
}
}
4.开始读取Excel:Sheet页加载器
/*******************************************************************************
******************************************************************************/
package com.hirain.chassis.core.excel;
import java.util.List;
import org.apache.poi.ss.usermodel.Sheet;
/**
* @Version 1.0
* @Description
* <p>
* Excel的Sheet页数据加载器
* @Modification
* <p>
* Date Author Version Description
* <p>
*/
public class ExcelSheetLoader {
/**
* Excel中的sheet页(api定义类型)
*/
private Sheet sheet;
private String[] headers;
/**
* Excel中的行数据加载器
*/
private AbstractExcelRowLoader rowLoader;
/**
* Excel中的sheet页数据模型,用来保存读进来的数据
*/
private Object sheetData;
/**
* 把sheet页中数据加载到sheet数据对象中
*/
public void loadSheetData() {
/* 收集所有行(表头)列数据 */
final List<List<String>> datas = ExcelUtil.getSheetDatas(sheet, headers);
for (final List<String> data : datas) {
/* 把这一行的数据赋给RowData数据模型,然后把RowData赋给sheetData对象 */
rowLoader.loadRowData(sheetData, data);
}
}
public Sheet getSheet() {
return sheet;
}
public void setSheet(final Sheet sheet) {
this.sheet = sheet;
}
public String[] getHeaders() {
return headers;
}
public void setHeaders(final String[] headers) {
this.headers = headers;
}
public AbstractExcelRowLoader getRowLoader() {
return rowLoader;
}
/**
* sheet数据收集器_设置_行数据收集器
*/
public void setRowLoader(final AbstractExcelRowLoader rowLoader) {
this.rowLoader = rowLoader;
}
public Object getSheetData() {
return sheetData;
}
public void setSheetData(final Object sheetData) {
this.sheetData = sheetData;
}
}
5.Excel行Row加载器
/*******************************************************************************
******************************************************************************/
package com.hirain.chassis.core.excel;
import java.util.List;
/**
* @Version 1.0
* @Created Oct 13, 2015 4:19:47 PM
* @Description
* <p>
* Excel的行数据加载器
* @Modification
* <p>
* Date Author Version Description
* <p>
*/
public abstract class AbstractExcelRowLoader {
/**
* 把这一行的数据赋给RowData数据模型,然后把RowData赋给sheetData对象
*
* @param sheetData
* Sheet页数据模型对象
* @param cellValues
* 这一行的若干列(表头列)的单元格cell中的数据list
* @return 行rowData数据模型对象
*/
public Object loadRowData(final Object sheetData, final List<String> cellValues) {
Object rowData = getRowData();
for (int i = 0; i < cellValues.size(); i++) {
rowData = setCellValueToRow(rowData, i, cellValues.get(i));
}
setRowValueToSheet(sheetData, rowData);
return rowData;
}
/**
* @return 行rowData数据模型对象
*/
public abstract Object getRowData();
/**
* 把某个单元格cellValue数据赋给行rowData数据对象
*
* @param rowData
* 行rowData数据对象
* @param headerIndex
* 这个单元格所在的列对应表头中的列
* @param cellValue
* 单元格的数据内容
* @return 行rowData数据对象
*/
public abstract Object setCellValueToRow(final Object rowData, final int headerIndex, final String cellValue);
/**
* 行数据对象赋给sheet数据对象
*
* @param sheetData
* sheet页数据对象
* @param rowData
* 行数据对象
*/
public abstract void setRowValueToSheet(Object sheetData, Object rowData);
}
6.Excel操作实现方法工具
/*******************************************************************************
******************************************************************************/
package com.hirain.chassis.core.excel;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
/**
* @Version 1.0
* @Description
* <p>
* 操作Excel表的基本API方法封装
* @Modification
* <p>
* Date Author Version Description
* <p>
*/
public class ExcelUtil {
/**
* 获取单元格Cell数据(支持公式计算值)
*/
public static String getCellValue(final Cell cell, final FormulaEvaluator evaluator) {
String value = null;
if (cell == null) {
return null;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
break;
case Cell.CELL_TYPE_FORMULA:// Excel公式计算所得值
final CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
value = String.valueOf(cellValue.getNumberValue());
break;
case Cell.CELL_TYPE_STRING:
value = cellValue.getStringValue().trim();
if (value.equals("")) {
value = null;// 如果excel中什么也不显示,认为是null,但是公式api转成空字符串
}
break;
}
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
final DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
return sdf.format(cell.getDateCellValue());
}
value = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue().trim();
if (value.equals("")) {
value = null;// 如果excel中填写了空格,我就认为啥都没填。实际Excel中是不会填写空字符串的。
}
break;
}
return value;
}
/**
* 获取单元格Cell数据(不支持公式计算值)
*/
public static String getCellValue(final Cell cell) {
String value = null;
if (cell == null) {
return null;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
final DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
return sdf.format(cell.getDateCellValue());
}
value = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
}
return value;
}
/**
* 获取所有的表头行list
*
* @param sheet
* excel的sheet页
* @param headers
* 表头
* @return 获取所有的表头行list
*/
public static List<Row> getHeaderRows(final Sheet sheet, final String[] headers) {
final List<Row> rows = new ArrayList<Row>();
if (sheet == null || headers == null || headers.length == 0) {
return rows;
}
final Iterator<Row> rowIter = sheet.rowIterator();
while (rowIter.hasNext()) {
final Row row = rowIter.next();
if (isHeaderRow(row, headers)) {
rows.add(row);
}
}
return rows;
}
/**
* 判断行是否是表头行
*
* @param row
* 行
* @param headers
* 表头
* @return 是否是表头行
*/
public static boolean isHeaderRow(final Row row, final String[] headers) {
if (row == null || headers == null || headers.length == 0) {
return false;
}
final List<String> values = getRowValues(row);
if (values.containsAll(Arrays.asList(headers))) {
return true;
}
return false;
}
/**
* 获取表头中某列 和 实际excel表中某列 的对应关系(表头中列的顺序和实际excel中列的顺序没有关系)
*
* @param row
* @param headers
* (header_row3,header_row1,header_row2)
* @return (3,1,2)
*/
public static List<Integer> getColumnIndexs(final Row row, final String[] headers) {
final List<Integer> indexs = new ArrayList<Integer>();
if (row == null || headers == null || headers.length == 0) {
return indexs;
}
final List<String> values = getRowValues(row);
if (values.containsAll(Arrays.asList(headers))) {
for (final String header : headers) {
indexs.add(values.indexOf(header));
}
}
return indexs;
}
/**
* @param row
* 行
* @return 这一行的所有单元格数据List(cellValues)
*/
public static List<String> getRowValues(final Row row) {
final List<String> values = new ArrayList<String>();
if (row == null) {
return values;
}
final Iterator<Cell> cellIter = row.cellIterator();
while (cellIter.hasNext()) {
final Cell cell = cellIter.next();
values.add(getCellValue(cell));
}
return values;
}
/**
* 把此sheet中所有表头列下的cell单元格数据收集到List<List<String>>(所有行(每行表头单元格))中
*
* @param sheet
* sheet页
* @param headers
* 需要读取的sheet页的表头
* @return 所有行(除去表头)的数据,每行的数据包括每行表头中包含的列的单元格数据
*/
public static List<List<String>> getSheetDatas(final Sheet sheet, final String[] headers) {
final List<List<String>> datas = new ArrayList<List<String>>();
if (sheet == null || headers == null || headers.length == 0) {
return datas;
}
final List<Row> headerRows = getHeaderRows(sheet, headers);
for (final Row rowHeader : headerRows) {
final List<Integer> columnIndexs = getColumnIndexs(rowHeader, headers);
final int startRow = rowHeader.getRowNum();
for (int i = startRow + 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (isHeaderRow(row, headers)) {
/* 如果读到表头行,就从跨过这个表头行重新开始读 */
i = sheet.getLastRowNum();
row = null;
}
if (row == null) {
continue;
}
final List<String> data = new ArrayList<String>();// 一行的单元格数据
for (int j = 0; j < columnIndexs.size(); j++) {// 遍历此行中所有表头中包含的列
data.add(ExcelUtil.getCellValue(row.getCell(columnIndexs.get(j))));
}
datas.add(data);
}
}
return datas;
}
}
7.接口:导入Excel表
/*******************************************************************************
******************************************************************************/
package com.hirain.chassis.core.excel;
/**
* @Version 1.0
* @Created Oct 13, 2015 4:16:17 PM
* @Description
* <p>
* 加载excel文件接口
* @Modification
* <p>
* Date Author Version Description
* <p>
*/
public interface IImporter {
/**
* @param excelPath
* excel文件路径
* @return excel的数据模型对象excelData
* @throws Exception
* 文件找不到异常
*/
Object doImport(String excelPath) throws Exception;
}
8.具体导入Excel方法并测试
/*******************************************************************************
******************************************************************************/
package com.hirain.chassis.core.excel;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import com.hirain.chassis.core.model.ChassisDevice;
import com.hirain.chassis.core.model.ChassisVariable;
import com.hirain.chassis.core.model.ExcelData;
/**
* @Version 1.0
* @Created Nov 18, 2014 10:29:47 AM
* @Description
* <p>
* Excel具体导入、模型赋值方法逻辑
* @Modification
* <p>
* Date Author Version Description
* <p>
*/
public class ExcelImporter implements IImporter {
public static void main(final String[] args) {
try {
final ExcelImporter importer = new ExcelImporter();
final ExcelData data = (ExcelData) importer.doImport(ConstantsExcel.EXCEL_PATH);
final List<ChassisDevice> chassisDevices = data.getChassisDevices();
for (final ChassisDevice chassisDevice : chassisDevices) {
final List<ChassisVariable> chassisVariables = chassisDevice.getChassisVariables();
for (final ChassisVariable chassisVariable : chassisVariables) {
System.out.println(chassisDevice.getName() + " : " + chassisVariable.getName());
}
System.out.println("----------------------------");
}
} catch (final Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public Object doImport(final String excelPath) throws Exception {
final ExcelData data = loadExcel(excelPath);
return data;
}
/**
* 加载Excel数据到excel数据模型(根据业务需求自定义),并返回模型
*/
public ExcelData loadExcel(final String excelPath) throws IOException {
final ExcelData excelData = new ExcelData();
loadSheet(excelPath, excelData);
return excelData;
}
/**
* 加载指定的sheet页数据到excel数据模型
*/
private void loadSheet(final String excelPath, final ExcelData excelData) throws IOException {
final String sheetName = ConstantsExcel.SHEET_NAME;
final String[] headers = ConstantsExcel.HEADERS;
final InputStream inputExcelFile = new FileInputStream(excelPath);
final HSSFWorkbook workbook = new HSSFWorkbook(inputExcelFile);
final Sheet sheet = workbook.getSheet(sheetName);
final ExcelSheetLoader sheetLoader = new ExcelSheetLoader();
sheetLoader.setSheet(sheet);
sheetLoader.setHeaders(headers);
/* 只读一个sheet页时候SheetData就是excelData,一个excelData可有多个不同SheetData */
final Object sheetData = excelData;
sheetLoader.setSheetData(sheetData);
sheetLoader.setRowLoader(new AbstractExcelRowLoader() {
ChassisDevice chassisDeviceLast;
@Override
public Object getRowData() {
return new ChassisDevice();
}
@Override
public Object setCellValueToRow(final Object rowData, final int headerIndex, final String cellValue) {
if (rowData instanceof ChassisDevice) {
final ChassisDevice device = (ChassisDevice) rowData;
switch (headerIndex) {
case 0:
if (cellValue == null) {
return chassisDeviceLast;
} else {
chassisDeviceLast = device;
device.setName(cellValue);
}
break;
case 1:
if (cellValue == null) {
return null;
} else {
final ChassisVariable v = new ChassisVariable();
v.setName(cellValue);
device.addChassisVariable(v);
}
break;
default:
break;
}
}
return rowData;
}
@Override
public void setRowValueToSheet(final Object sheetData, final Object rowData) {
if (rowData instanceof ChassisDevice) {
final ChassisDevice chassisDevice = (ChassisDevice) rowData;
if (!excelData.getChassisDevices().contains(chassisDevice)) {
excelData.addChassisDevice(chassisDevice);
}
}
}
});
sheetLoader.loadSheetData();
}
}
9.常量
/*******************************************************************************
******************************************************************************/
package com.hirain.chassis.core.excel;
/**
* @Version 1.0
* @Created Oct 13, 2015 7:17:49 PM
* @Description
* <p>
* @Modification
* <p>
* Date Author Version Description
* <p>
*/
public class ConstantsExcel {
public static final String EXCEL_PATH = "D:\\testExcel.xls";
public static final String SHEET_NAME = "testSheet";
public static final String HEADERS[] = new String[] { "父类别", "子类别" };
}
10.over,附上源代码工程,自己去找poi的jar包去依赖。(下面评论中附上地址,在这不会传附件)
。