java poi 注解_Java POI注解方式---Excel文件导入工具类实现

package com.oa.manager.vcms.util;

import org.apache.poi.ss.usermodel.Cell;

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 org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.FileInputStream;

import java.io.InputStream;

import java.lang.reflect.Field;

import java.math.BigDecimal;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

public class ExcelImportUtil {

private Class clazz;

private Field fields[];

public ExcelImportUtil(Class clazz) {

this.clazz = clazz;

fields = clazz.getDeclaredFields();

}

/**

* 读取excel

* @param is

* @param rowIndex

* @param cellIndex

* @return

*/

@SuppressWarnings({ "unused" })

public List readExcel(String filePath, int rowIndex,int cellIndex) {

InputStream is = null;

try {

is = new FileInputStream(filePath);

//WorkbookFactory工厂类生产出对应版本的Excel的workbook具体对象

Workbook workbook = WorkbookFactory.create(is);

Sheet sheet = workbook.getSheetAt(0);

// 不准确

int rowLength = sheet.getLastRowNum()+1;

System.out.println("sheet总行数："+(sheet.getLastRowNum()+1));

return this.readExcelData(sheet, rowIndex, cellIndex);

} catch (Exception e) {

e.printStackTrace();

}

return null;

}

/**

* 收集Excel中的数据

*/

public List readExcelData(Sheet sheet, int rowIndex,int cellIndex) {

List list = new ArrayList();

for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {

Row row = sheet.getRow(rowNum);

T entity = null;

try {

entity = this.covertObject(row, cellIndex);

list.add(entity);

} catch (Exception e) {

}

}

return list;

}

/**

* Excel每一行数据转换为对象

* @param row

* @param cellIndex

* @return

* @throws Exception

*/

public T covertObject(Row row,int cellIndex) throws Exception {

T entity = (T) clazz.newInstance();

for (int cellNum = cellIndex; cellNum < row.getLastCellNum(); cellNum++) {

Cell cell = row.getCell(cellNum);

for (Field field : fields) {

if(field.isAnnotationPresent(ExcelAttribute.class)){

field.setAccessible(true);//设置为可以访问私有变量

ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);

if(cellNum == ea.sort()) {

field.set(entity, covertAttrType(field, cell));

}

}

}

}

return entity;

}

/**

* 类型转换 将cell 单元格格式转为 字段类型

* @throws ParseException

*/

private Object covertAttrType(Field field, Cell cell) throws NumberFormatException, ParseException {

String fieldType = field.getType().getSimpleName();

if ("String".equals(fieldType)) {

return getValue(cell);

}else if ("Date".equals(fieldType)) {

return new SimpleDateFormat("yyyy-MM-dd").parse(getValue(cell)) ;

}else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {

return Integer.parseInt(getValue(cell));

}else if ("long".equals(fieldType) || "Long".equals(fieldType)) {

return Long.parseLong(getValue(cell));

}else if ("double".equals(fieldType) || "Double".equals(fieldType)) {

return Double.parseDouble(getValue(cell));

}else {

return null;

}

}

/**

* 格式转为String

* @param cell

* @return

*/

public String getValue(Cell cell) {

if (cell == null) {

return "";

}

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

return cell.getRichStringCellValue().getString().trim();

// 数字类型 小数,整数,日期

case Cell.CELL_TYPE_NUMERIC:

if (DateUtil.isCellDateFormatted(cell)) {

Date dt = cell.getDateCellValue();

//DateUtil.getJavaDate(cell.getNumericCellValue());

System.out.println("日期："+new SimpleDateFormat("yyyy-MM-dd").format(dt));

return new SimpleDateFormat("yyyy-MM-dd").format(dt);

} else {

// 防止数值变成科学计数法

String strCell = "";

Double num = cell.getNumericCellValue();

BigDecimal bd = new BigDecimal(num.toString());

if (bd != null) {

strCell = bd.toPlainString();

}

// 去除 浮点型 自动加的 .0

if (strCell.endsWith(".0")) {

strCell = strCell.substring(0, strCell.indexOf("."));

}

return strCell;

}

case Cell.CELL_TYPE_BOOLEAN:

return String.valueOf(cell.getBooleanCellValue());

case Cell.CELL_TYPE_FORMULA://公式类型

return cell.getCellFormula();

//其余的单元格类型统一返回""

default:

return "";

}

}

}