package com.eigpay.framework.util.ExcelUtil;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
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;
/**
* <p>Title: ExcelUtil</p>
* <p>Description: Excel数据转换为JavaBean对象</p>
* 支持：Excel Date、Integer、Double、Float、Boolean类型
* 支持：Excel2003-2007，再高版本未做测试
* <p>Email: yuyue_qty@163.com</p>
* @author 晴天雨
* @date 2016年3月26日下午5:31:54
* @version 1.0
*/
public class ExcelUtil<T> {
private static final Logger logger = Logger.getLogger(ExcelUtil.class);
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
/**
* Excel数据转换为对象
* <p>Description: </p>
* @param file
* @param t
* @return
* @throws Exception
* @date 2016年3月26日下午4:18:20
*/
public List<T> excel2OJavaBean(File file, Class<T> t) throws Exception{
if(!file.exists()){
throw new FileNotFoundException();
}
List<T> result = new ArrayList<T>();
int beginRowNum = 1;
String[][] data = getData(file, beginRowNum);
int rowNum = data.length;
if(rowNum < 2){
throw new RuntimeException("Excel is not data.");
}
int cellNum = data[beginRowNum].length-1;
if(rowNum < 1){
throw new RuntimeException("Excel is not data.");
}
Field[] fields = t.getDeclaredFields();
int propertyNum = fields.length;
if(propertyNum < cellNum){
logger.info("cellNum=" + cellNum + " propertyNum=" + propertyNum);
throw new RuntimeException("Excel column number is greater than the number of object properties.");
}
for(int x=0; x<rowNum; x++){
T obj = t.newInstance();
for(int y=0; y<cellNum; y++){
Field field = fields[y];
String fieldType = field.getGenericType().toString();
Object value = data[x][y];
if(fieldType.equals("class java.util.Date")){
value = strToDate(value);
}else if(fieldType.equals("class java.lang.Integer")){
value = Integer.parseInt(value.toString());
}else if(fieldType.equals("class java.lang.Double")){
value = Double.parseDouble(value.toString());
}else if(fieldType.equals("class java.lang.Float")){
value = Float.parseFloat(value.toString());
}else if(fieldType.equals("class java.lang.Boolean")){
value = Boolean.parseBoolean(value.toString());
}
field.setAccessible(true);
field.set(obj, value);
field.setAccessible(false);
}
result.add(obj);
}
return result;
}
public static String[][] getData(File file, int ignoreRows) {
int rowSize = 0;
List<String[]> result = new ArrayList<String[]>();
InputStream in = null;
try {
in = new FileInputStream(file);
Workbook wb = WorkbookFactory.create(in);
if (wb instanceof XSSFWorkbook) {
result = getXSSFResult(wb, ignoreRows);
} else if (wb instanceof HSSFWorkbook) {
result = getHSSFResult(wb, ignoreRows);
}
String[][] returnArray = new String[result.size()][rowSize];
for (int i = 0; i < returnArray.length; i++) {
returnArray[i] = (String[]) result.get(i);
}
return returnArray;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (in != null)
in.close();
} catch (Exception ex) {
}
}
return new String[0][0];
}
public static List<String[]> getXSSFResult(Workbook wb, int ignoreRows) {
List<String[]> result = new ArrayList<String[]>();
XSSFCell cell = null;
int rowSize = 0;
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
XSSFSheet st = (XSSFSheet) wb.getSheetAt(sheetIndex);
for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {// 第一行为标题，不取
XSSFRow row = st.getRow(rowIndex);
if (row == null) {
continue;
}
int tempRowSize = row.getLastCellNum() + 1;
if (tempRowSize > rowSize) {
rowSize = tempRowSize;
}
String[] values = new String[rowSize];
Arrays.fill(values, "");
boolean hasValue = false;
for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
String value = "";
cell = row.getCell(columnIndex);
if (cell != null) {
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);//
// 注意:一定要设成这个，否则可能会出现乱码
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_FORMULA: // 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = sdf.format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0.00").format(cell
.getNumericCellValue());
if (value.endsWith(".00")) {
value = value.substring(0,
value.length() - 3);
}
}
break;
case XSSFCell.CELL_TYPE_BLANK:
break;
case XSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
default:
value = "";
}
}
if (columnIndex == 0 && value.trim().equals("")) {
break;
}
values[columnIndex] = rightTrim(value);
hasValue = true;
}
if (hasValue) {
result.add(values);
}
}
}
return result;
}
@SuppressWarnings("deprecation")
public static List<String[]> getHSSFResult(Workbook wb, int ignoreRows) {
List<String[]> result = new ArrayList<String[]>();
HSSFCell cell = null;
int rowSize = 0;
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
HSSFSheet st = (HSSFSheet) wb.getSheetAt(sheetIndex);
for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {// 第一行为标题，不取
HSSFRow row = st.getRow(rowIndex);
if (row == null) {
continue;
}
int tempRowSize = row.getLastCellNum() + 1;
if (tempRowSize > rowSize) {
rowSize = tempRowSize;
}
String[] values = new String[rowSize];
Arrays.fill(values, "");
boolean hasValue = false;
for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
String value = "";
cell = row.getCell(columnIndex);
if (cell != null) {
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);//注意:一定要设成这个，否则可能会出现乱码
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = sdf.format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0.00").format(cell.getNumericCellValue());
if (value.endsWith(".00")) {
value = value.substring(0,value.length() - 3);
}
}
break;
case HSSFCell.CELL_TYPE_FORMULA:// 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
default:
value = "";
}
}
if (columnIndex == 0 && value.trim().equals("")) {
break;
}
values[columnIndex] = rightTrim(value);
hasValue = true;
}
if (hasValue) {
result.add(values);
}
}
}
return result;
}
/**
* 去掉字符串右边的空格
*
* @param str要处理的字符串
* @return 处理后的字符串
*/
public static String rightTrim(String str) {
if (str == null) {
return "";
}
int length = str.length();
for (int i = length - 1; i >= 0; i--) {
if (str.charAt(i) != 0x20) {
break;
}
length--;
}
return str.substring(0, length);
}
/**
* 字符串转Date
* 字符串格式为:2016/03/26
* <p>Description: </p>
* @param str
* @return
* @date 2016年3月26日下午4:20:46
*/
public static Date strToDate(Object str) {
Date date = null;
try {
date = sdf.parse(str != null ? str
.toString().replaceAll("/", "-") : null);
} catch (ParseException e) {
logger.error(e.getMessage(), e);
}
return date;
}
}调用方法：
File file = new File("F:/upload/a.xlsx");
ExcelUtil<Payable> excelUtil = new ExcelUtil<Payable>();
List<Payable> payables = excelUtil.excel2OJavaBean(file, Payable.class);
for(Payable payable : payables){
System.out.println(JSON.toJSON(payable));
}打印结果：
{"acceptDate":1454085600000,"amount":55555,"buyerMsg":"aaaaaaa","buyerPayableId":"AAAAAAAAAA","isOK":false,"payableDate":1453737600000,"supplierMsg":"ddddddddd","supplierName":"A供应商","terraceProductId":"001"}
{"acceptDate":1454086200000,"amount":66666,"buyerMsg":"bbbbbbb","buyerPayableId":"BBBBBBBBBB","isOK":true,"payableDate":1453737600000,"supplierMsg":"ddddddddd","supplierName":"B供应商","terraceProductId":"001"}
{"acceptDate":1454084400000,"amount":77777,"buyerMsg":"ccccccc","buyerPayableId":"CCCCCCCCCC","isOK":false,"payableDate":1453737600000,"supplierMsg":"ddddddddd","supplierName":"C供应商","terraceProductId":"001"}技术点：
使用到POI读取Excel文件，利用反射技术将Excel文件列数据赋给对象属性（要求Excel列字段顺序与对象属性顺序一致），将对象封装成List<T>返回。
Excel支持2003和2007，其他版本未测试，支持 Date、Integer、Double、Float、Boolean类型
扫一扫
2376
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
