package com.utils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* Excel 导入实现
*
* @author
*
*/
public class ExcelReader {
public static final String SUFFIX_EXCEL_2003 = ".xls";
public static final String SUFFIX_EXCEL_2007 = ".xlsx";
private InputStream inputStream;
public ExcelReader() {
}
public ExcelReader(InputStream inputStream) {
this.inputStream = inputStream;
}
public List<List<Object>> readSheet(int sheetIndex, String fileName) throws Exception {
if (fileName == null || fileName.length() < 1) {
return null;
}
fileName = fileName.trim().toLowerCase();
if (fileName.endsWith(SUFFIX_EXCEL_2003)) {
return this.readSheet2003(sheetIndex);
}
if (fileName.endsWith(SUFFIX_EXCEL_2007)) {
return this.readSheet2007(sheetIndex);
}
return null;
}
public List<List<Object>> readSheet2003(int sheetIndex) throws Exception {
Workbook workbook = new HSSFWorkbook(this.getInputStream());
Sheet sheet = workbook.getSheetAt(sheetIndex);
return this.readSheet(sheet);
}
public List<List<Object>> readSheet2007(int sheetIndex) throws Exception {
Workbook workbook = new XSSFWorkbook(this.getInputStream());
Sheet sheet = workbook.getSheetAt(sheetIndex);
return this.readSheet(sheet);
}
protected List<List<Object>> readSheet(Sheet sheet) throws Exception {
if(sheet == null){
return null;
}
List<List<Object>> sheet_data = new ArrayList<List<Object>>();
List<Object> row_data = null;
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
row_data = this.readRow(row);
if (row_data != null) {
sheet_data.add(row_data);
}
}
return sheet_data;
}
protected List<Object> readRow(Row row) throws Exception {
if (row == null) {
return null;
}
List<Object> list = new ArrayList<Object>();
int lastCellNum = row.getLastCellNum();
boolean emptyRow = true;
for (int i = 0; i < lastCellNum; i++) {
Cell cell = row.getCell(i);
Object cellValue = this.readCell(cell);
if (cellValue == null || cellValue instanceof String && StringUtils.isBlank((String) cellValue)) {
// do nothing
} else if (emptyRow) {
emptyRow = false;
}
list.add(cellValue);
}
if (emptyRow) {
return null;
}
return list;
}
public static Object readCell(List<Object> cells, int idx) {
if (CollectionUtils.isEmpty(cells) || idx + 1 > cells.size()) {
return null;
} else {
return cells.get(idx);
}
}
public static <E> E transData(Class<E> e, List<Object> vals, int cellIndex) {
if (cellIndex + 1 > vals.size()) {
return null;
}
Object val = readCell(vals, cellIndex);
if (val == null) {
return null;
}
if (e == String.class) {
String s = val.toString();
if (StringUtils.endsWith(s, ".0")) {
val = StringUtils.substringBefore(s, ".0");
} else {
val = s;
}
} else if (e == Integer.class) {
if (StringUtils.isBlank(val.toString())) {
val = null;
} else {
val = Double.valueOf(val.toString()).intValue();
}
} else if (e == Long.class) {
if (StringUtils.isBlank(val.toString())) {
val = null;
} else {
val = Double.valueOf(val.toString()).longValue();
}
} else if (e == BigDecimal.class) {
if (StringUtils.isBlank(val.toString())) {
val = null;
} else {
val = BigDecimal.valueOf(Double.valueOf(val.toString()));
}
} else if (e == Double.class) {
if (StringUtils.isBlank(val.toString())) {
val = null;
} else {
val = Double.valueOf(val.toString());
}
} else if (e == Float.class) {
if (StringUtils.isBlank(val.toString())) {
val = null;
} else {
val = Float.valueOf(val.toString());
}
} else if (e == Date.class) {
if (!(val instanceof Date)) {
val = null;
}
} else {
val = null;
}
return (E) val;
}
protected Object readCell(Cell cell) {
if (cell == null) {
return null;
}
Object cellValue;
if(CellType.NUMERIC.equals(cell.getCellType())) {
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
cellValue = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(cell.getNumericCellValue());
} else {
BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue());
cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString,可以防止获取到科学计数值
}
} else if(CellType.STRING.equals(cell.getCellType())){
cellValue = cell.getStringCellValue().trim();
} else if(CellType.BLANK.equals(cell.getCellType())){
cellValue = "";
} else if(CellType.BOOLEAN.equals(cell.getCellType())){
cellValue = cell.getBooleanCellValue();
} else {
cellValue = "";
}
return cellValue;
}
// ---------- getter and setter ----------//
public InputStream getInputStream() {
return inputStream;
}
public void setInputStream(InputStream inputStream) {
this.inputStream = inputStream;
}
}
09-06
519