package com.dsh.utils;
|
|
|
import org.apache.commons.beanutils.BeanUtils;
|
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.xssf.usermodel.XSSFWorkbook;
|
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.*;
|
|
/**
|
* Created by Roy.xiao on 2017/7/20.
|
*/
|
public class ExcelParser {
|
|
/**
|
* 数据源
|
*/
|
private InputStream in;
|
/**
|
* 扩展名
|
*/
|
private String ext;
|
|
/**
|
* 常量： 扩展名
|
*/
|
public static final String EXT_XLSX = "xlsx";
|
public static final String EXT_XLS = "xls";
|
|
/**
|
* 解析方式
|
*/
|
private int SIMPLE_DATASET = 0;
|
private int SPECIFIC_DATASET = 1;
|
|
public ExcelParser(InputStream inputStream, String ext) {
|
this.in = inputStream;
|
this.ext = ext;
|
}
|
|
/**
|
* 返还简单数据集
|
* @return
|
*/
|
public List<Map<String, Object>> dataset() {
|
return parseParams(SIMPLE_DATASET);
|
}
|
|
/**
|
* 返还指定类型集合
|
* @param tClass
|
* @param <T>
|
* @return 返还指定类型集合
|
*/
|
public <T>List<T> specificList(Class<T> tClass) {
|
List<T> targetList = new ArrayList<T>();
|
List<Map<String, Object>> speDatas = parseParams(SPECIFIC_DATASET);
|
for (Map<String, Object> map: speDatas) {
|
T obj = null;
|
try {
|
obj = tClass.newInstance();
|
} catch (Exception e) {
|
throw new RuntimeException(e);
|
}
|
Set<Map.Entry<String, Object>> entrySet =map.entrySet();
|
Iterator it = entrySet.iterator();
|
while (it.hasNext()) {
|
Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it.next();
|
try {
|
BeanUtils.setProperty(obj, entry.getKey(), entry.getValue());
|
}catch (Exception e) {
|
throw new RuntimeException(e);
|
}
|
}
|
targetList.add(obj);
|
}
|
return targetList;
|
}
|
|
/**
|
* 模板解析
|
* @param type
|
* @return
|
*/
|
private List<Map<String, Object>> parseParams(int type) {
|
List<Map<String, Object>> dataset = new LinkedList<Map<String, Object>>();
|
Workbook workbook = null;
|
|
try {
|
if (ext.equals(EXT_XLS)) {
|
workbook = new HSSFWorkbook(in);
|
} else {
|
workbook = new XSSFWorkbook(in);
|
}
|
} catch (IOException e) {
|
throw new RuntimeException(e);
|
}
|
Iterator<Sheet> sheetIterator = workbook.sheetIterator();
|
List<String> titleGroup = new ArrayList<String>();
|
|
boolean isFirstRowDes = true;
|
boolean isFirstRowTitle = true;
|
|
while (sheetIterator.hasNext()) {
|
Sheet sheet = sheetIterator.next();
|
Iterator<Row> rowIterator = sheet.rowIterator();
|
|
while (rowIterator.hasNext()) {
|
Row row = rowIterator.next();
|
if (row == null) {
|
continue;
|
}
|
if (isFirstRowDes){
|
isFirstRowDes = false;
|
continue;
|
}
|
Iterator<Cell> cellIterator = row.cellIterator();
|
int cellNum = 0;
|
Map<String, Object> cells = new TreeMap<String, Object>();
|
while (cellIterator.hasNext()) {
|
Cell cell = cellIterator.next();
|
// if (isFirstRowTitle) {
|
// titleGroup.add(cell.getStringCellValue());
|
// continue;
|
// }
|
String dataKey = null;
|
if (type == SIMPLE_DATASET) {
|
dataKey = String.valueOf(cell.getColumnIndex());
|
} else {
|
dataKey = titleGroup.get(cellNum);
|
}
|
switch (cell.getCellType()) {
|
case Cell.CELL_TYPE_BOOLEAN:
|
cells.put(dataKey, cell.getBooleanCellValue());
|
break;
|
case Cell.CELL_TYPE_NUMERIC:
|
cells.put(dataKey, cell.getNumericCellValue());
|
break;
|
case Cell.CELL_TYPE_STRING:
|
cells.put(dataKey, cell.getStringCellValue());
|
break;
|
case Cell.CELL_TYPE_BLANK:
|
cells.put(dataKey, "");
|
break;
|
default:
|
cells.put(dataKey, "");
|
break;
|
}
|
cellNum ++;
|
}
|
// if (!isFirstRowTitle) {
|
dataset.add(cells);
|
// }
|
if (isFirstRowTitle) {
|
if (titleGroup.size() != 0) {
|
isFirstRowTitle = false;
|
}
|
}
|
}
|
}
|
return dataset;
|
}
|
|
}
|
