package com.pingan.common.transfer;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
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.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.pingan.common.model.BaseEntity;
/**
* @author chinwer
* @created 2014-5-20
*/
public abstract class ExcelTransfer<T extends BaseEntity> {
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
public static final String PROCESSING = "Processing...";
public static final String EMPTY = "";
public static final String POINT = ".";
protected abstract T getModel();
private List<String> parseErrorList = new ArrayList<String>();
public String getParseErrorInfo() {
if (!parseErrorList.isEmpty()) {
return ArrayUtils.toString(parseErrorList, "\n");
}
return "";
}
/**
* @return the parseErrorList
*/
public List<String> getParseErrorList() {
return parseErrorList;
}
/**
* 读取xls或者xlsx文件
* @param is excel文件输入流
* @param startRow 开始行 传null值默认取1
* @param endRow 结束行 传null值默认取现有数据行
* @param startColumn 开始列
* @param endColumn 结束列
* @return 每行数据以list返回，每个元素对应一列
* @throws Exception
*/
public List<T> readExcel(String fileName, InputStream is, Integer startRow, Integer endRow, Integer startColumn,
Integer endColumn) throws Exception {
if (is == null) {
throw new Exception("is参数为必传");
}
if (startColumn == null || endColumn == null) {
throw new Exception("startColumn和endColumn参数为必传");
} else {
String postfix = getPostfix(fileName);
if (StringUtils.isNotBlank(postfix)) {
if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
return readXls(is, startRow, endRow, startColumn, endColumn);
} else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
return readXlsx(is, startRow, endRow, startColumn, endColumn);
}
} else {
System.out.println(fileName + NOT_EXCEL_FILE);
}
}
return null;
}
/**
* Read the Excel 2010
* @param is of the excel file
* @return
* @throws IOException
*/
public List<T> readXlsx(InputStream is, Integer startRow, Integer endRow, Integer startColumn, Integer endColumn)
throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
List<T> retlist = new ArrayList<T>();
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
if (startRow == null)
startRow = 1;
if (endRow == null)
endRow = xssfSheet.getLastRowNum() + 1;
for (int rowNum = startRow - 1; rowNum < endRow; rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
List<String> errorList = new ArrayList<String>();
T model = getModel();
for (int colNum = startColumn; colNum <= endColumn; colNum++) {
try {
setCellValue(model, xssfRow, colNum);
} catch (Exception e) {
errorList.add(String.valueOf(colNum));
}
}
if (!errorList.isEmpty()) {
parseErrorList.add("第" + String.valueOf(xssfRow.getRowNum() + 1) + "行的第"
+ ArrayUtils.toString(errorList, ",") + "列存在格式错误！");
} else if (retlist.contains(model)) {
parseErrorList.add("第" + String.valueOf(xssfRow.getRowNum() + 1) + "行的数据重复！");
} else {
model.setRowNum(String.valueOf(xssfRow.getRowNum() + 1));
retlist.add(model);
}
}
}
break;
}
return retlist;
}
/**
* Read the Excel 2003-2007
* @param is of the Excel
* @return
* @throws IOException
*/
public List<T> readXls(InputStream is, Integer startRow, Integer endRow, Integer startColumn, Integer endColumn)
throws IOException {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
List<T> retlist = new ArrayList<T>();
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
System.out.println(numSheet);
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
if (startRow == null)
startRow = 1;
if (endRow == null)
endRow = hssfSheet.getLastRowNum() + 1;
for (int rowNum = startRow - 1; rowNum < endRow; rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
List<String> errorList = new ArrayList<String>();
T model = getModel();
for (int colNum = startColumn; colNum <= endColumn; colNum++) {
try {
setCellValue(model, hssfRow, colNum);
} catch (Exception e) {
errorList.add(String.valueOf(colNum));
}
}
if (!errorList.isEmpty()) {
parseErrorList.add("第" + String.valueOf(hssfRow.getRowNum() + 1) + "行的第"
+ ArrayUtils.toString(errorList, ",") + "列存在格式错误！");
} else if (retlist.contains(model)) {
parseErrorList.add("第" + String.valueOf(hssfRow.getRowNum() + 1) + "行的数据重复！");
} else {
model.setRowNum(String.valueOf(hssfRow.getRowNum() + 1));
retlist.add(model);
}
}
}
break;
}
return retlist;
}
@SuppressWarnings("static-access")
protected String getValue(Cell cell, DecimalFormat df) {
if (cell == null) {
return EMPTY;
}
if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
return df.format(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
private static String getPostfix(String path) {
if (path == null || StringUtils.isEmpty(path.trim())) {
return EMPTY;
}
if (path.contains(POINT)) {
return path.substring(path.lastIndexOf(POINT) + 1, path.length());
}
return EMPTY;
}
/**
* 设置每个单元格对应模型的属性<br>
* （主要注意一下对存数字的单元格和即可能存字符又可能存数字的单元格的格式化）
* @param model
* @param row
* @param colNum
*/
protected abstract void setCellValue(T model, Row row, int colNum) throws Exception;
}
