package
com.berchina.iec.agency.util.execl;
import
java.io.FileInputStream;
import
java.io.InputStream;
import
java.lang.reflect.Method;
import
java.math.BigDecimal;
import
java.text.DecimalFormat;
import
java.text.SimpleDateFormat;
import
java.util.ArrayList;
import
java.util.Date;
import
java.util.HashMap;
import
java.util.List;
import
java.util.Map;
import
org.apache.log4j.LogManager;
import
org.apache.log4j.Logger;
import
org.apache.poi.ss.usermodel.Cell;
import
org.apache.poi.ss.usermodel.CellStyle;
import
org.apache.poi.ss.usermodel.DateUtil;
import
org.apache.poi.ss.usermodel.Row;
import
org.apache.poi.ss.usermodel.Sheet;
import
org.apache.poi.ss.usermodel.Workbook;
import
com.berchina.iec.agency.util.ConvertUtil;
import
com.berchina.iec.agency.util.StringUtils;
public
class
T123 {
private
static
Logger logger = LogManager.getLogger(ReaderFileUtil.
class
);
public
static
final
String READER_BASE_ROOT =
"io"
;
public
static
final
String START_INDEX =
"startRow"
;
public
static
final
String TITLES =
"titles"
;
/**
* fullFilePath 目标excel的磁盘路径
* clz 需要转换对象的class
* @param fullFilePath
* @param clz
* @return
* @throws Exception
*/
@SuppressWarnings
(
"unchecked"
)
public
static
<T> List<T> transToObject(String fullFilePath,Class<T> clz)
throws
Exception{
InputStream is =
null
;
try
{
is =
new
FileInputStream(fullFilePath);
Workbook wb = ReaderFileUtil.loadWorkBookByPath(fullFilePath,is);
Sheet sheet = wb.getSheetAt(
0
);
Map<String,Object> map = readExcelTitle(sheet);
int
start = Integer.parseInt(map.get(START_INDEX).toString());
List<String> titles = (List<String>)map.get(TITLES);
int
last = sheet.getLastRowNum();
List<T> lst =
new
ArrayList<T>();
for
(
int
i = (start+
1
);i<=last;i++){
List<String> values = getRowValues(sheet, i);
T t = transToObject(titles, values, clz);
lst.add(t);
}
return
lst;
}
catch
(Exception e) {
throw
e;
}
finally
{
if
(is!=
null
){
is.close();
}
}
}
private
static
<T> T transToObject(List<String> titles,List<String> values,Class<T> clz)
throws
Exception{
T t = clz.newInstance();
int
size = titles.size();
for
(
int
i =
0
;i<size;i++){
if
(values.size()<=i){
break
;
}
String title = titles.get(i);
String value = values.get(i);
setValue(t,clz,title,value);
}
return
t;
}
private
static
void
setValue(Object o,Class<?>clz,String title,String value)
throws
Exception{
Method m =
null
;
if
(title.indexOf(
"."
)!=-
1
){
String[] titleSplit = title.split(
"\\."
);
m = getSetMethod(titleSplit<img src=
"http://www.oschina.net/js/ke/plugins/emoticons/images/0.gif"
alt=
""
>, clz);
}
else
{
m = getSetMethod(title, clz);
}
if
(m ==
null
){
logger.info(title+
"在"
+clz.getName()+
"中不存在"
);
return
;
}
setValue(o, m, title,value);
}
private
static
void
setValue(Object o,Method method,String title,String value)
throws
Exception{
Class<?>[] clazz = method.getParameterTypes();
String type = clazz<img src=
"http://www.oschina.net/js/ke/plugins/emoticons/images/0.gif"
alt=
""
>.getName();
if
(StringUtils.isEmpty(value)){
return
;
}
if
(
"java.lang.String"
.equals(type)){
method.invoke(o, value);
}
else
if
(
"java.util.Date"
.equals(type)){
Date d =
null
;
if
(value.length()>
10
){
d = ConvertUtil.convertObj2Time(value);
}
else
{
d = ConvertUtil.convertObj2Date(value);
}
method.invoke(o, d);
}
else
if
(
"java.lang.Integer"
.equals(type)||
"int"
.equals(type)){
Integer i = ConvertUtil.convertObj2Int(value);
method.invoke(o, i);
}
else
if
(
"java.lang.Long"
.equals(type)||
"long"
.equals(type)){
Long l = ConvertUtil.convertObj2Long(value);
method.invoke(o, l);
}
else
if
(
"java.lang.Short"
.equals(type)||
"short"
.equals(type)){
Short s = ConvertUtil.convertObj2Short(value);
method.invoke(o, s);
}
else
if
(
"java.lang.Boolean"
.equals(type)||
"boolean"
.equals(type)){
Boolean b = ConvertUtil.convertObj2Boolean(value);
method.invoke(o, b);
}
else
if
(
"java.math.BigDecimal"
.equals(type)){
BigDecimal b = ConvertUtil.convertObj2BigDecimal(value);
method.invoke(o, b);
}
else
{
Method getMethodName = o.getClass().getMethod(method.getName().replace(
"set"
,
"get"
));
Object returnValue = getMethodName.invoke(o);
Class<?> returnClass = Class.forName(type);
if
(returnValue ==
null
){
returnValue = returnClass.newInstance();
method.invoke(o, returnValue);
}
title = title.substring(title.indexOf(
"."
)+
1
);
setValue(returnValue, returnClass, title, value);
}
}
private
static
Method getSetMethod(String propName,Class<?> clz){
Method[]methods = clz.getMethods();
for
(Method method : methods){
if
(method.getName().toLowerCase().equals(
"set"
+propName.toLowerCase())){
Class<?>[] clazz = method.getParameterTypes();
if
(clazz.length ==
1
){
return
method;
}
}
}
return
null
;
}
private
static
Map<String,Object> readExcelTitle(Sheet sheet)
throws
Exception{
int
m =
0
;
Map<String,Object> map =
new
HashMap<String,Object>();
int
last = sheet.getLastRowNum();
while
(m<=last){
Cell cell = sheet.getRow(m).getCell(
0
);
if
(cell!=
null
){
String cellValue = cell.getStringCellValue();
if
(!cellValue.startsWith(
"#"
)){
List<String> lstStr = getRowValues(sheet, m);
map.put(START_INDEX, m);
map.put(TITLES, lstStr);
return
map;
}
}
m++;
}
throw
new
Exception(
"Excel格式不正确"
);
}
/**
* 获得行数据
* @param sheet
* @param rowIndex
* @return
*/
private
static
List<String> getRowValues(Sheet sheet,
int
rowIndex){
List<String> lstStr =
new
ArrayList<String>();
Row row = sheet.getRow(rowIndex);
int
last = row.getLastCellNum();
for
(
int
i =
0
;i<last;i++){
lstStr.add(getCellValue(row, i));
}
return
lstStr;
}
/**
* 获得列数据
* @param row
* @param colIndex
* @return
*/
private
static
String getCellValue(Row row,
int
colIndex){
String cellValue =
""
;
if
(colIndex < row.getFirstCellNum()){
cellValue =
""
;
}
else
{
Cell cell = row.getCell(colIndex);
if
(cell ==
null
){
cellValue =
""
;
}
else
{
int
cellType = cell.getCellType();
if
(Cell.CELL_TYPE_FORMULA == cellType){
cellType = cell.getCachedFormulaResultType();
}
if
(Cell.CELL_TYPE_BLANK ==cellType){
cellValue =
""
;
}
else
if
(Cell.CELL_TYPE_BOOLEAN == cellType){
Boolean b = cell.getBooleanCellValue();
cellValue = b.toString();
}
else
if
(Cell.CELL_TYPE_ERROR == cellType){
cellValue =
""
;
}
else
if
(Cell.CELL_TYPE_NUMERIC == cellType){
if
(isCellDateFormatted(cell)){
SimpleDateFormat sdf =
new
SimpleDateFormat(
"yyyy-MM-dd"
);
if
(cell.getDateCellValue()!=
null
){
cellValue = sdf.format(cell.getDateCellValue());
}
}
else
if
(isCellTimeFormatted(cell)){
SimpleDateFormat sdf =
new
SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss"
);
if
(cell.getDateCellValue()!=
null
){
cellValue = sdf.format(cell.getDateCellValue());
}
}
else
{
double
d = cell.getNumericCellValue();
cellValue = String.format(
"%.2f"
, d);
if
(cellValue.matches(
"^\\d+\\.0+$"
)){
DecimalFormat df =
new
DecimalFormat(
"#"
);
cellValue = df.format(d);
}
}
}
else
if
(Cell.CELL_TYPE_STRING == cellType){
cellValue = cell.getStringCellValue();
}
}
}
if
(cellValue!=
null
){
cellValue = cellValue.trim();
}
return
cellValue;
}
public
static
boolean
isCellDateFormatted(Cell cell) {
if
(cell ==
null
)
return
false
;
boolean
bDate =
false
;
double
d = cell.getNumericCellValue();
if
(DateUtil.isValidExcelDate(d)) {
CellStyle style = cell.getCellStyle();
if
(style ==
null
)
return
false
;
int
i = style.getDataFormat();
String f = style.getDataFormatString();
if
(f.indexOf(
"y"
) > -
1
&& f.indexOf(
"m"
) > -
1
&& f.indexOf(
"d"
) > -
1
) {
return
true
;
}
}
return
bDate;
}
public
static
boolean
isCellTimeFormatted(Cell cell) {
if
(cell ==
null
)
return
false
;
boolean
bDate =
false
;
double
d = cell.getNumericCellValue();
if
(DateUtil.isValidExcelDate(d)) {
CellStyle style = cell.getCellStyle();
if
(style ==
null
)
return
false
;
int
i = style.getDataFormat();
String f = style.getDataFormatString();
if
(f.indexOf(
"mm"
) > -
1
&& f.indexOf(
"ss"
) > -
1
&& f.indexOf(
"h"
) > -
1
) {
return
true
;
}
}
return
bDate;
}
}