maven 导入
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.9</version>
</dependency>
Excel 导入工具类
package com.qf58.supplier.util;
import com.qf58.supplier.entity.excel.SupplierExcel;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.Cell;
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 java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* Created with IntelliJ IDEA.
* Description:
* User: zhubo
* Date: 2017-11-30
* Time: 19:24
*/
public class ExcelReaderUtils {
/**
* suffix of excel 2003
*/
public static final String OFFICE_EXCEL_V2003_SUFFIX = "xls";
/**
* suffix of excel 2007
*/
public static final String OFFICE_EXCEL_V2007_SUFFIX = "xlsx";
/**
* suffix of excel 2010
*/
public static final String OFFICE_EXCEL_V2010_SUFFIX = "xlsx";
public static final String EMPTY = "";
public static final String DOT = ".";
public static final String NOT_EXCEL_FILE = " is Not a Excel file!";
public static final String PROCESSING = "Processing...";
public static void main(String[] args) throws IOException {
try {
List<SupplierExcel> excels = readExcel("D:\\SRM数据导入1201更新.xlsx",new SupplierExcel(),null);
System.out.println(excels.size());
System.out.println(excels);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Check which version of The Excel file is. Throw exception if Excel file path is illegal.
* @param path the Excel file
* @param obj obj of convert
* @param num column index array of Excel
* @param <T> Type of obj
* @return a list that contains T from Excel.
* @throws Exception
*/
public static <T> List<T> readExcel(String path,T obj,Integer ... num) throws Exception {
if (StringUtils.isBlank(path)) {
throw new IllegalArgumentException(path + " excel file path is either null or empty");
} else {
String suffiex = getSuffiex(path);
if(StringUtils.isBlank(suffiex)){
throw new IllegalArgumentException(path + " suffiex is either null or empty");
}
if(num == null || (num != null && num.length <= 0)){
num = new Integer[obj.getClass().getDeclaredFields().length];
for(int i = 0 ; i < num.length ; i++){
num[i] = i;
}
}
if (OFFICE_EXCEL_V2003_SUFFIX.equals(suffiex)) {
return readXls2003(path,obj,num);
} else if (OFFICE_EXCEL_V2007_SUFFIX.equals(suffiex)) {
return readXlsx2007_2010(path,obj,num);
} else if (OFFICE_EXCEL_V2010_SUFFIX.equals(suffiex)) {
return readXlsx2007_2010(path,obj,num);
} else {
throw new IllegalArgumentException(path + NOT_EXCEL_FILE);
}
}
}
public static <T> List<T> readExcelIO(String realName,InputStream is,T obj,Integer ... num) throws Exception {
if (StringUtils.isBlank(realName)) {
throw new IllegalArgumentException(realName + " excel file path is either null or empty");
} else {
String suffiex = getSuffiex(realName);
if(StringUtils.isBlank(suffiex)){
throw new IllegalArgumentException(realName + " suffiex is either null or empty");
}
if(num == null || (num != null && num.length <= 0)){
num = new Integer[obj.getClass().getDeclaredFields().length];
for(int i = 0 ; i < num.length ; i++){
num[i] = i;
}
}
if (OFFICE_EXCEL_V2003_SUFFIX.equals(suffiex)) {
return readXlsIO2003(is,obj,num);
} else if (OFFICE_EXCEL_V2007_SUFFIX.equals(suffiex)) {
return readXlsxIO2007_2010(is,obj,num);
} else if (OFFICE_EXCEL_V2010_SUFFIX.equals(suffiex)) {
return readXlsxIO2007_2010(is,obj,num);
} else {
throw new IllegalArgumentException(realName + NOT_EXCEL_FILE);
}
}
}
/**
*
* @param path the path of the Excel
* @param obj 文件实体类
* @param num 对应Excel中列对应标识 计数从0开始
* @param <T> 泛型类型
* @return
* @throws Exception
*/
public static <T> List<T> readXlsx2007_2010(String path,T obj,Integer... num) throws Exception{
System.out.println(PROCESSING + path);
InputStream is = new FileInputStream(path);
return readXlsxIO2007_2010(is,obj,num);
}
/**
*
* @param is the path of the Excel
* @param obj 文件实体类
* @param num 对应Excel中列对应标识 计数从0开始
* @param <T> 泛型类型
* @return
* @throws Exception
*/
public static <T> List<T> readXlsxIO2007_2010(InputStream is,T obj,Integer... num) throws Exception{
System.out.println(PROCESSING);
List<T> list = new ArrayList<T>();
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
T o = (T)obj.getClass().newInstance();
Field[] fields = obj.getClass().getDeclaredFields();
for(int i = 0 ; i < fields.length ; i++){
XSSFCell cell = xssfRow.getCell(num[i]);
setFieldData(o,fields[i] ,cell);
}
list.add(o);
}
}
return list;
}
return null;
}
/**
*
* @param path
* @param obj
* @param num
* @param <T>
* @return
* @throws Exception
*/
public static <T> List<T> readXls2003(String path,T obj,Integer ... num) throws Exception {
System.out.println(PROCESSING + path);
List<T> list = new ArrayList<T>();
InputStream is = new FileInputStream(path);
return readXlsIO2003(is,obj,num);
}
public static <T> List<T> readXlsIO2003(InputStream is,T obj,Integer ... num) throws Exception {
System.out.println(PROCESSING);
List<T> list = new ArrayList<T>();
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
T o = (T)obj.getClass().newInstance();
Field[] fields = obj.getClass().getDeclaredFields();
for(int i = 0 ; i < fields.length ; i++){
HSSFCell cell = hssfRow.getCell(num[i]);
setFieldData(o,fields[i] ,cell);
}
list.add(o);
}
}
return list;
}
return null;
}
public static <T> void setFieldData(T model,Field field ,Cell cell) throws Exception{
String type = field.getGenericType().toString();
String name = getFieldName(field.getName());
if (type.equals("class java.lang.String")) { // 如果type是类类型,则前面包含"class ",后面跟类名
Method m = model.getClass().getMethod("set"+name,String.class);
m.invoke(model, getValue(cell)); // 调用setter方法获取属性值
}
if (type.equals("class java.lang.Byte")) {
Method m = model.getClass().getMethod("set"+name,Byte.class);
m.invoke(model, new Byte(getValue(cell)));
}
if (type.equals("class java.lang.Integer")) {
Method m = model.getClass().getMethod("set"+name,Integer.class);
m.invoke(model, new Integer(getValue(cell)));
}
if (type.equals("class java.lang.Long")) {
Method m = model.getClass().getMethod("set"+name,Long.class);
m.invoke(model, new Long(getValue(cell)));
}
if (type.equals("class java.lang.Double")) {
Method m = model.getClass().getMethod("set"+name,Double.class);
m.invoke(model, new Double(getValue(cell)));
}
if (type.equals("class java.lang.Boolean")) {
Method m = model.getClass().getMethod("set"+name,Boolean.class);
m.invoke(model, new Boolean(getValue(cell)));
}
if (type.equals("class java.util.Date")) {
Method m = model.getClass().getMethod("set"+name,Date.class);
m.invoke(model, new Date());
}
}
public static String getFieldName(String name){
String prefix = name.substring(0,1).toUpperCase();
return prefix + name.substring(1,name.length());
}
@SuppressWarnings("static-access")
private static String getValue(Cell cell) {
if(cell instanceof XSSFCell){
if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}else if(cell instanceof HSSFCell){
if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
return "";
}
public static String getSuffiex(String path) {
if(StringUtils.isBlank(path)){
return EMPTY;
}
int index = path.lastIndexOf(DOT);
if (index == -1) {
return EMPTY;
}
return path.substring(index + 1, path.length());
}
}
package com.qf58.supplier.entity.others;
/**
* Created with IntelliJ IDEA.
* Description:
* User: zhubo
* Date: 2017-11-30
* Time: 21:34
*/
public class Std {
private String name ;
private String simpleName;
private Long number;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSimpleName() {
return simpleName;
}
public void setSimpleName(String simpleName) {
this.simpleName = simpleName;
}
public Long getNumber() {
return number;
}
public void setNumber(Long number) {
this.number = number;
}
@Override
public String toString() {
return "Std{" +
"name='" + name + '\'' +
", simpleName='" + simpleName + '\'' +
", number=" + number +
'}';
}
}