package com.houbank.app.util;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
public abstract class CellSpace<V> {
/** 工作空间 **/
protected XSSFWorkbook wb;
/** 第一张工作簿 **/
protected XSSFSheet sheet;
/** 列数--以 标头 行为基准 **/
private int colCount;
/** 总行数 **/
private int rowCount;
/** 第几行开始读 */
private Integer rowStart;
/** 第几列开始读 */
private Integer colStart;
private SimpleDateFormat dateUtil = new SimpleDateFormat("yyyy-MM-dd");
DecimalFormat decimalFormat = new DecimalFormat("0");
private List<V> ccs = new ArrayList<V>();
private static NumberFormat nt = NumberFormat.getInstance();
static {
nt.setGroupingUsed(false);
nt.setMaximumFractionDigits(10);// 最多10位
}
protected CellSpace() {
}
Map<Integer, String> cellFieldNames = null;
// 能为空的字段
List<String> canBeNullFields = null;
/**
* 初始化模板基础数据
*
* @param in
* 数据
* @param rowStart
* 第几行开始读
* @param colStart
* 第几列开始读
* @throws Exception
*/
public CellSpace(InputStream in, Integer rowStart, Integer colStart) throws Exception {
this.rowStart = rowStart;
this.colStart = colStart;
wb = new XSSFWorkbook(in);
sheet = wb.getSheetAt(0);
rowCount = sheet.getPhysicalNumberOfRows();
if (rowCount < 2) {
throw new Exception("模板文件数据不完整");
}
cellFieldNames = getCellFildsName();
canBeNullFields = getCanBeNullFields();
colCount = this.colStart + cellFieldNames.size();
}
/**
* 加载详情数据
*
* @param title
* 文件头
*/
public List<V> loadSheetFile(StringBuffer title) throws Exception {
if(title!=null)
title.append(sheet.getRow(0).getCell(0).getStringCellValue());
for (int j = rowStart; j < rowCount; j++) {
Row cells = sheet.getRow(j);
V c = this.findSimpleBean();
BeanWrapper wrapper = new BeanWrapperImpl(c);
Object obj = getCellValue(cells.getCell(0), wrapper.getPropertyType(cellFieldNames.get(0)), true);
String endToken = getEndToken();
if (obj == endToken || obj.equals(endToken) ) {// 每一行第一个单元格为空则不向下进行
break;
}
for (int i = colStart; i < colCount; i++) {
String fieldName = cellFieldNames.get(i - colStart);
Cell cell = null;
try {
cell = cells.getCell(i);
} catch (Exception e) {
}
try {
wrapper.setPropertyValue(fieldName,
getCellValue(cell, wrapper.getPropertyType(fieldName), canBeNullFields.contains(fieldName)));
} catch (Exception e) {
// 第几列 第几行 失败原因
throw e;
}
}
ccs.add(c);
}
return ccs;
}
public List<V> loadSheetFile() throws Exception {
return loadSheetFile(null);
}
/**
* 关闭工作空间
*/
public void close(){
if(wb != null){
try {
wb.close();
} catch (IOException e) {
}
}
}
/**
* 读单元格内容 只认识字符串和数字类型
*/
@SuppressWarnings({ "unchecked" })
private <T> T getCellValue(Cell cell, Class<T> clazz, Boolean canbeNull) throws Exception {
String ret = "";
if (cell != null) {
ret = getStringValue(cell);
}
ret = StringUtils.trimToEmpty(ret);
if (ret.equals("")) {
if (canbeNull)
return null;
else
throw new Exception("模板第" + (cell.getRow()) + "行,第" + (cell.getColumnIndex() + 1) + "列不能是空");
}
try {
if (clazz == String.class) {
return (T) ret;
} else if (clazz == Double.class) {
return (T) Double.valueOf(ret.replaceAll(",", "").replaceAll(",", ""));
} else if (clazz == Integer.class) {
return (T) Integer.valueOf(Double.valueOf(ret.replaceAll(",", "").replaceAll(",", "")).intValue());
} else if (clazz == Boolean.class) {
return (T) Boolean.valueOf(ret.equals("是") ? "true" : ret.equals("否") ? "false" : ret);
} else if (clazz == Date.class) {
return (T) dateUtil.parse(ret);
} else {
throw new Exception("暂未支持的类型");
}
} catch (Exception e) {
throw new Exception("模板第" + (cell.getRowIndex() + 1) + "行,第" + (cell.getColumnIndex() + 1) + "列输入有误:"
+ e.getMessage());
}
}
private String getStringValue(Cell cell) {
if (cell == null) {
return "";
}
String strCell = "";
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
strCell = decimalFormat.format((cell.getNumericCellValue()));
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
return strCell;
}
public int getRowCount(){
return rowCount;
}
public abstract Map<Integer, String> getCellFildsName();
public abstract List<String> getCanBeNullFields();
public abstract V findSimpleBean() throws Exception;
public abstract String getEndToken() throws Exception;
}
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
public abstract class CellSpace<V> {
/** 工作空间 **/
protected XSSFWorkbook wb;
/** 第一张工作簿 **/
protected XSSFSheet sheet;
/** 列数--以 标头 行为基准 **/
private int colCount;
/** 总行数 **/
private int rowCount;
/** 第几行开始读 */
private Integer rowStart;
/** 第几列开始读 */
private Integer colStart;
private SimpleDateFormat dateUtil = new SimpleDateFormat("yyyy-MM-dd");
DecimalFormat decimalFormat = new DecimalFormat("0");
private List<V> ccs = new ArrayList<V>();
private static NumberFormat nt = NumberFormat.getInstance();
static {
nt.setGroupingUsed(false);
nt.setMaximumFractionDigits(10);// 最多10位
}
protected CellSpace() {
}
Map<Integer, String> cellFieldNames = null;
// 能为空的字段
List<String> canBeNullFields = null;
/**
* 初始化模板基础数据
*
* @param in
* 数据
* @param rowStart
* 第几行开始读
* @param colStart
* 第几列开始读
* @throws Exception
*/
public CellSpace(InputStream in, Integer rowStart, Integer colStart) throws Exception {
this.rowStart = rowStart;
this.colStart = colStart;
wb = new XSSFWorkbook(in);
sheet = wb.getSheetAt(0);
rowCount = sheet.getPhysicalNumberOfRows();
if (rowCount < 2) {
throw new Exception("模板文件数据不完整");
}
cellFieldNames = getCellFildsName();
canBeNullFields = getCanBeNullFields();
colCount = this.colStart + cellFieldNames.size();
}
/**
* 加载详情数据
*
* @param title
* 文件头
*/
public List<V> loadSheetFile(StringBuffer title) throws Exception {
if(title!=null)
title.append(sheet.getRow(0).getCell(0).getStringCellValue());
for (int j = rowStart; j < rowCount; j++) {
Row cells = sheet.getRow(j);
V c = this.findSimpleBean();
BeanWrapper wrapper = new BeanWrapperImpl(c);
Object obj = getCellValue(cells.getCell(0), wrapper.getPropertyType(cellFieldNames.get(0)), true);
String endToken = getEndToken();
if (obj == endToken || obj.equals(endToken) ) {// 每一行第一个单元格为空则不向下进行
break;
}
for (int i = colStart; i < colCount; i++) {
String fieldName = cellFieldNames.get(i - colStart);
Cell cell = null;
try {
cell = cells.getCell(i);
} catch (Exception e) {
}
try {
wrapper.setPropertyValue(fieldName,
getCellValue(cell, wrapper.getPropertyType(fieldName), canBeNullFields.contains(fieldName)));
} catch (Exception e) {
// 第几列 第几行 失败原因
throw e;
}
}
ccs.add(c);
}
return ccs;
}
public List<V> loadSheetFile() throws Exception {
return loadSheetFile(null);
}
/**
* 关闭工作空间
*/
public void close(){
if(wb != null){
try {
wb.close();
} catch (IOException e) {
}
}
}
/**
* 读单元格内容 只认识字符串和数字类型
*/
@SuppressWarnings({ "unchecked" })
private <T> T getCellValue(Cell cell, Class<T> clazz, Boolean canbeNull) throws Exception {
String ret = "";
if (cell != null) {
ret = getStringValue(cell);
}
ret = StringUtils.trimToEmpty(ret);
if (ret.equals("")) {
if (canbeNull)
return null;
else
throw new Exception("模板第" + (cell.getRow()) + "行,第" + (cell.getColumnIndex() + 1) + "列不能是空");
}
try {
if (clazz == String.class) {
return (T) ret;
} else if (clazz == Double.class) {
return (T) Double.valueOf(ret.replaceAll(",", "").replaceAll(",", ""));
} else if (clazz == Integer.class) {
return (T) Integer.valueOf(Double.valueOf(ret.replaceAll(",", "").replaceAll(",", "")).intValue());
} else if (clazz == Boolean.class) {
return (T) Boolean.valueOf(ret.equals("是") ? "true" : ret.equals("否") ? "false" : ret);
} else if (clazz == Date.class) {
return (T) dateUtil.parse(ret);
} else {
throw new Exception("暂未支持的类型");
}
} catch (Exception e) {
throw new Exception("模板第" + (cell.getRowIndex() + 1) + "行,第" + (cell.getColumnIndex() + 1) + "列输入有误:"
+ e.getMessage());
}
}
private String getStringValue(Cell cell) {
if (cell == null) {
return "";
}
String strCell = "";
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
strCell = decimalFormat.format((cell.getNumericCellValue()));
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
return strCell;
}
public int getRowCount(){
return rowCount;
}
public abstract Map<Integer, String> getCellFildsName();
public abstract List<String> getCanBeNullFields();
public abstract V findSimpleBean() throws Exception;
public abstract String getEndToken() throws Exception;
}