/** 从Excel导入到TableBean */
public void doImport() {
int rowNum = sheet.getLastRowNum() + 1;
int columnNum = 0;
for (int i = 0; i < rowNum; i++) {
if (sheet.getRow(i) != null) {
int last = sheet.getRow(i).getLastCellNum();
columnNum = last > columnNum ? last : columnNum;
}
}
tableBean = new TableBean(rowNum, columnNum);
Collection<CellBean> cellBeans = new ArrayList<CellBean>();
for (int r = startRow; r < rowNum; r++) {
Row row = sheet.getRow(r);
if (row != null) {
for (int c = 0; c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
Integer type = forceCellType.get(c);
if (type != null) {
cell.setCellType(type);
}
String cellValue = null;
if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
cellValue = cell.getBooleanCellValue() + "";
} else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
try {
cellValue = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
cellValue = String.valueOf(cell.getRichStringCellValue()).trim();
}
} else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (DateUtil.isCellDateFormatted(cell)) {
Date date2 = cell.getDateCellValue();
SimpleDateFormat dff = new SimpleDateFormat(dateFormat);
cellValue = dff.format(date2); // 日期转化
} else cellValue = String.valueOf(cell.getNumericCellValue());
} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
cellValue = cell.getStringCellValue();
}
if (cellValue != null && cellValue instanceof String) {
cellValue = cellValue.toString().trim();
}
CellBean cellBean = new CellBean(cellValue, r, c);
cellBean.setCell(cell);
cellBeans.add(cellBean);
}
}
}
}
tableBean.setCellBeans(cellBeans);
}
/**
* 读取内容到List,可以是List<Map>也可以是List<Object>
*
* @param sortedColumns 从第0列开始,数组中每个元素对应type中的一个字段,元素为空则忽略
* @param type 读取数据存放的类型
* @param <T> 泛型
* @return
*/
public <T> List<T> read(String[] sortedColumns, Class<? extends T> type) {
List<T> list = new ArrayList();
for (int i = startRow; i < tableBean.getRowCount(); i++) {
Object bean =
Map.class.isAssignableFrom(type) ? new HashMap() : Reflections.instance(type.getName());
for (int j = 0; j < sortedColumns.length; j++) {
String column = sortedColumns[j];
if (ObjectHelper.isNotEmpty(column)) {
CellBean cellBean = tableBean.getCellBean(i, j);
if (cellBean != null) {
Cell cell = cellBean.getCell();
if (cell == null) {
cell = sheet.getRow(i).createCell(j);
}
if (bean instanceof Map) {
FieldUtils.setFieldValue(bean, "cell" + column, cell);
if (ObjectHelper.isNotEmpty(cellBean.getContent())) {
FieldUtils.setFieldValue(bean, column, cellBean.getContent());
}
} else {
if (bean instanceof BaseExcelVo) {
BaseExcelVo baseExcelVo = ((BaseExcelVo) bean);
baseExcelVo.setCell(column, cell);
baseExcelVo.setRow(i);
baseExcelVo.setCol(j);
}
Field accessibleField = Reflections.getAccessibleField(bean, column);
Class<?> fieldType = accessibleField.getType();
Object value = cellBean.getContent();
if (!fieldType.equals(String.class)) {
value = ConverString.asType(fieldType, cellBean.getContent());
}
if (ObjectHelper.isNotEmpty(cellBean.getContent())) {
FieldUtils.setFieldValue(bean, column, value);
}
}
}
}
}
list.add((T) bean);
}
return list;
}