import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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 java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
* 读取excel,并将其映射成pojo
*/
public class ExcelUtil {
public <T> List<T> excelToPOJO(String excelPath, Class<T> clazz, Map<Integer, String> mapping,int sheet,int start) {
try {
File excel = new File(excelPath);
if (excel.isFile() && excel.exists()) {
return excelToPOJO(excel, clazz, mapping, sheet,start);
}
} catch (Exception e) {
e.printStackTrace();
}
return new ArrayList();
}
/**
*
* @param excel
* @param clazz
* @param mapping 映射Map<列数,实例类的属性名>
* @param <T>
* @return
*/
public <T> List<T> excelToPOJO(File excel, Class<T> clazz, Map<Integer, String> mapping,int sheetIndex,int start) {
List<T> result = new ArrayList<>();
try {
if (excel.isFile() && excel.exists()) { //判断文件是否存在
String[] split = excel.getName().split("\\."); //.是特殊字符,需要转义!!!!!
Workbook wb;
//根据文件后缀(xls/xlsx)进行判断
if ("xls".equals(split[1])) {
FileInputStream fis = new FileInputStream(excel); //文件流对象
wb = new HSSFWorkbook(fis);
} else if ("xlsx".equals(split[1])) {
FileInputStream fis = new FileInputStream(excel);
wb = new XSSFWorkbook(fis);
} else {
System.out.println("文件类型错误!");
return null;
}
//开始解析
Sheet sheet = wb.getSheetAt(sheetIndex); //读取sheet 0
int firstRowIndex = sheet.getFirstRowNum() + start; //第一行是列名,所以不读
int lastRowIndex = sheet.getLastRowNum();
System.out.println("firstRowIndex: " + firstRowIndex);
System.out.println("lastRowIndex: " + lastRowIndex);
for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //遍历行
System.out.println("rIndex: " + rIndex);
Row row = sheet.getRow(rIndex);
if (row != null) {
T obj = clazz.newInstance();
Set<Integer> keySet = mapping.keySet();
for (Integer key : keySet) {
String field = mapping.get(key);
String cell = row.getCell(key)==null ? "":row.getCell(key).toString();
ReflectUtil.setFileValue(obj, field, cell);
}
result.add(obj);
}
}
} else {
System.out.println("找不到指定的文件");
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 将excel读取成List
* @param excelPath
* @param start
* @return
*/
public List<List<String>> excelToList(String excelPath,int start) {
try {
File excel = new File(excelPath);
if (excel.isFile() && excel.exists()) {
return excelToList(excel,start);
}
} catch (Exception e) {
e.printStackTrace();
}
return new ArrayList();
}
public List<List<String>> excelToList(File excel,int startrow) {
List<List<String>> result = new ArrayList<List<String>>();
try {
if (excel.isFile() && excel.exists()) { //判断文件是否存在
String[] split = excel.getName().split("\\."); //.是特殊字符,需要转义!!!!!
Workbook wb;
//根据文件后缀(xls/xlsx)进行判断
if ("xls".equals(split[1])) {
FileInputStream fis = new FileInputStream(excel); //文件流对象
wb = new HSSFWorkbook(fis);
} else if ("xlsx".equals(split[1])) {
wb = new XSSFWorkbook(excel);
} else {
System.out.println("文件类型错误!");
return null;
}
//开始解析
Sheet sheet = wb.getSheetAt(0); //读取sheet 0
int firstRowIndex = startrow-1; //第一行是列名,所以不读
int lastRowIndex = sheet.getLastRowNum();
for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //遍历行
ArrayList<String> oneRow = new ArrayList<>();
Row row = sheet.getRow(rIndex);
if (row != null) {
int start = row.getFirstCellNum();
int end = row.getLastCellNum();
for (int i = start; i < end; i++) {
Cell cell = row.getCell(i);
oneRow.add(cell.toString());
}
result.add(oneRow);
}
}
} else {
System.out.println("找不到指定的文件");
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
}
gradle依赖
compile ("org.apache.poi:poi:3.17-beta1")
compile ("org.apache.poi:poi-ooxml:3.9")
compile ("org.apache.poi:poi-ooxml-schemas:3.9")