摘要 Excel文件转换为javabean一般用Apache POI工具，这个工具是专门操作微软办办公软件的。虽然比较简单，但是比较费时费力，没有提供xlsToBean这样给力的方法。这次正好有这个需求，看了同事们的方法~~Orz给跪。既然是程序猿，那么就不要用人力解决问题，而是用算法解决它，所以还是直接写工具，方便自己，也方便他人。
先看看同事的方法
public static List<JavaBean> getListByExcel(String file) throws BiffException, IOException, MyException{
List<JavaBean> list=new ArrayList<JavaBean>();
Workbook rwb=Workbook.getWorkbook(new File(file));
Sheet rs=rwb.getSheet("商品");
if(rs==null) {
throw new MyException("行数为零");
}
int rows=rs.getRows();
for (int i = 1; i < rows; i++) {
if(rs.getCell(0, i).getContents()==null||rs.getCell(0, i).getContents().equals("")) {
break;
}
String name=rs.getCell(1, i).getContents();
String format=rs.getCell(2, i).getContents();
String unit=rs.getCell(3, i).getContents();
String factory=rs.getCell(4, i).getContents();
String price=rs.getCell(5, i).getContents();
String conversion=rs.getCell(6, i).getContents();
String tname=rs.getCell(7, i).getContents();
String tformat=rs.getCell(8, i).getContents();
String tunit=rs.getCell(9, i).getContents();
String tfactory=rs.getCell(10, i).getContents();
String tprice=rs.getCell(11, i).getContents();
String tendGoodsId=rs.getCell(12, i).getContents();
String goodsId=rs.getCell(13, i).getContents();
String factoryId=(rs.getCell(14, i).getContents()==null||rs.getCell(14, i).getContents().equals(""))?"0":rs.getCell(14, i).getContents();
GoodsEntity a = new GoodsEntity();
a.setName(name);
a.setFormat(format);
a.setUnit(unit);
a.setFactory(factory);
a.setPrice(price);
a.setTend_goodsId(tendGoodsId);
a.setGoodsId(goodsId);
a.setFactoryId(factoryId);
a.setUuId(UUID.randomUUID().toString());
a.setTfactory(tfactory);
a.setTformat(tformat);
a.setTname(tname);
a.setTunit(tunit);
a.setConversion(conversion);
a.setTprice(tprice);
list.add(a);
}
return list;
}
这只是其中一个javabean的转换方法。所在类里面目前有10个左右的转换方法，但是代码已经有484行。这个项目才刚起步，到后期岂不是要几千行上万行？
首先说说我的想法，直接看伪代码比较清楚
File file = new File(excel文件);
WorkBook wb = new Workbook(file); //excel文件转换文档对象
Row row = wb.getSheet(0).getRow(行号);//读取excel一行数据
//其实到这里，一行数据就是一个JavaBean
int cols = row.getLastCellNum();//获取最后一列的列号
Object[] arr = new Object[cols];
//遍历这行的每一个单元格，然后把单元格取到的数据添加到数组arr
for(int i = 0 ;i<cols;i++){
Cell cell = row.getCell(i);
arr[i] = cell.getCellValue();
}
//ok~到此为止我们把一行数据插入到了数组。那么接下来如何把数组填充到一个javabean呢？
//我的想法：数组的下标与javabean属性对应起来。就可以利用反射将数组的元素一一设置到javabean。
看我的代码，不BB
ExcelCell.java 注解
/**
* @ClassName: ExcelCell
* @Description: 实体字段与excel列号关联的注解
* @author albert
*
*/
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelCell {
int col();
Class<?> Type() default String.class;
}
Excel 文件
| 商品名 | 单位 | 规格 | 生产厂家 |
|---|---|---|---|
| 感冒灵颗粒 | 盒 | 3片/盒 | 贵州百灵 |
excel文件总共有4列，那么序号0-3；
Goods.java 实体类
这里要给实体类属性添加注解，以便与Excel 列号对应
public class Goods{
@ExcelCell(col=0)
private String name; //商品名
@ExcelCell(col=1)
private String unit; //单位
@ExcelCell(col=2)
private String format; //规格
@ExcelCell(col=3)
private String factory;//生产厂家
}
最重要的工具类：ExcelConvert.java
/**
*
*/
package com.sy.utils;
/**
* @ClassName: ExcelConveter
* @Description:
* @author albert
* @date 2017年5月5日 下午1:19:56
*
*/
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.xssf.usermodel.XSSFWorkbook;
import com.sy.exceptions.MyException;
public class ExcelConveter {
public static Workbook readFile(File file) throws MyException {
try {
//xls和xlsx必须不同的处理类，POI就这么规定的
if (file.getName().toLowerCase().endsWith(".xls")) {
return readFileHSSF(new FileInputStream(file));
} else {
return readFileXSSF(new FileInputStream(file));
}
} catch (Exception e) {
e.printStackTrace();
throw new MyException(e.getMessage());
}
}
//HSSF*是处理xls格式的，XSSF是处理xlsx格式文件
private static Workbook readFileHSSF(InputStream stream) throws MyException, IOException {
try {
return new HSSFWorkbook(stream);
} catch (IOException e) {
e.printStackTrace();
throw new MyException(e.getMessage());
} finally {
stream.close();
}
}
private static Workbook readFileXSSF(InputStream stream) throws MyException, IOException {
try {
return new XSSFWorkbook(stream);
} catch (IOException e) {
e.printStackTrace();
throw new MyException(e.getMessage());
} finally {
stream.close();
}
}
public static Workbook readFile(String path) throws MyException {
File file = new File(path);
if (!file.exists())
throw new MyException("文件不存在");
if (!file.isFile())
throw new MyException("不是合法的文件");
return readFile(file);
}
public static Sheet readSheet(HSSFWorkbook workbook, Integer index) {
return workbook.getSheetAt(index);
}
public static Object[] convertArrayByRow(Row row) {
int cols = row.getLastCellNum();
Object[] arr = new Object[cols];
for (int i = 0; i < cols; i++) {
Cell cell = row.getCell(i);
if (cell == null)
continue;
if (cell.getCellTypeEnum() == CellType.STRING) {
arr[i] = cell.getStringCellValue();
} else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
arr[i] = cell.getNumericCellValue();
} else {
}
}
return arr;
}
public static <T extends Object> T convertBeanFromArray(Object[] arr, Class<T> clazz) throws MyException {
T entity;
try {
entity = clazz.newInstance();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (!field.isAnnotationPresent(ExcelCell.class))
continue;
field.setAccessible(true);
ExcelCell anno = field.getAnnotation(ExcelCell.class);
Class<?> cellType = anno.Type();
Integer col = anno.col();
if (col >= arr.length)
continue;
if (arr[col] == null)
continue;
if (cellType == null) {
field.set(entity, arr[col]);
} else {
field.set(entity, numericByStr(cellType, arr[col]));
}
}
return entity;
} catch (Exception e) {
e.printStackTrace();
throw new MyException(e.getMessage());
}
}
public static <T extends Object> Object numericByStr(Class<T> clazz, Object param) {
if (param == null)
return null;
String arg = String.valueOf(param);
if (clazz.isAssignableFrom(Double.class)) {
return Double.valueOf(arg);
} else if (clazz.isAssignableFrom(Long.class)) {
Double d = Double.valueOf(arg);
return d.longValue();
} else if (clazz.isAssignableFrom(Integer.class)) {
return Integer.valueOf(arg);
} else {
return arg;
}
}
public static <T> List<T> getBean(String path, Class<T> clazz) throws MyException {
List<T> list = new ArrayList<T>();
Workbook book = readFile(path);
for (int i = 1; i <= book.getSheetAt(0).getLastRowNum(); i++) {
Object[] arr = convertArrayByRow(book.getSheetAt(0).getRow(i));
T t = convertBeanFromArray(arr, clazz);
list.add(t);
}
return list;
}
public static <T> List<T> getBean(File file, Class<T> clazz) throws MyException {
List<T> list = new ArrayList<T>();
Workbook book = readFile(file);
for (int i = 1; i <= book.getSheetAt(0).getLastRowNum(); i++) {
Object[] arr = convertArrayByRow(book.getSheetAt(0).getRow(i));
T t = convertBeanFromArray(arr, clazz);
list.add(t);
}
return list;
}
public static <T> List<T> getBean(InputStream stream, String excelType, Class<T> clazz)
throws MyException, IOException {
Workbook book;
if (excelType.equals("xls")) {
book = readFileHSSF(stream);
} else {
book = readFileXSSF(stream);
}
List<T> list = new ArrayList<T>();
for (int i = 1; i <= book.getSheetAt(0).getLastRowNum(); i++) {
Object[] arr = convertArrayByRow(book.getSheetAt(0).getRow(i));
T t = convertBeanFromArray(arr, clazz);
list.add(t);
}
return list;
}
}
使用方法：就这么一行，有没有很简单？
List<Goods> list = ExcelConveter.getBean('excel文件路径', Barcode.class);
所需jar包在apache 官网就可以下载 http://poi.apache.org/download.html#POI-3.16
