从个人体验来讲，导入比导出简单。
导入之前需要申明一点，导入数据必须为文本格式。
ExcelUtil类
package me.cf81.onestep.util;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.io.FilenameUtils;
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.CellType;
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.InvocationTargetException;
import java.util.*;
/**
* @Author: yh
* @Description: Excel导入轮子
* @Date: Created in 9:44 2018/6/29.
*/
public class ExcelUtils {
public static <T> List<T> toList(String path, Class<T> clazz, ExcelFieldMap[] fields, int headerRows) throws IOException, InvocationTargetException, InstantiationException, IllegalAccessException {
if (StringUtils.equals(FilenameUtils.getExtension(path), "xls")) {
return xlsToList(new FileInputStream(path), clazz, fields, headerRows);
} else {
return xlsxToList(new FileInputStream(path), clazz, fields, headerRows);
}
}
public static class ExcelFieldMap {
private int excelColumn;
private String fieldName;
public ExcelFieldMap(int excelColumn, String fieldName) {
this.excelColumn = excelColumn;
this.fieldName = fieldName;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public int getExcelColumn() {
return excelColumn;
}
public void setExcelColumn(int excelColumn) {
this.excelColumn = excelColumn;
}
}
private static <T> List<T> xlsToList(InputStream inputStream, Class<T> clazz, ExcelFieldMap[] fields, int headerRows) throws IOException, IllegalAccessException, InstantiationException, InvocationTargetException {
HSSFWorkbook wb = new HSSFWorkbook(inputStream);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
Iterator rows = sheet.rowIterator();
List<T> list = new ArrayList<>();
for (int i = 0; rows.hasNext(); i++) {
row = (HSSFRow) rows.next();
if (i <= headerRows) {
continue;
}
T obj = clazz.newInstance();
for (ExcelFieldMap excelFieldMap : fields) {
cell = row.getCell(excelFieldMap.excelColumn);
if (cell == null) {
continue;
}
if (cell.getCellTypeEnum() == CellType.STRING) {
BeanUtils.setProperty(obj, excelFieldMap.fieldName, cell.getStringCellValue());
} else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
BeanUtils.setProperty(obj, excelFieldMap.fieldName, (int)cell.getNumericCellValue());
} else {
}
}
list.add(obj);
}
return list;
}
private static <T> List<T> xlsxToList(InputStream inputStream, Class<T> clazz, ExcelFieldMap[] fields, int headerRows) throws IOException, IllegalAccessException, InstantiationException, InvocationTargetException {
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row;
XSSFCell cell;
Iterator rows = sheet.rowIterator();
List<T> list = new ArrayList<>();
for (int i = 0; rows.hasNext(); i++) {
row = (XSSFRow) rows.next();
if (i <= headerRows) {
continue;
}
T obj = clazz.newInstance();
for (ExcelFieldMap excelFieldMap : fields) {
cell = row.getCell(excelFieldMap.excelColumn);
if (cell == null) {
continue;
}
if (cell.getCellTypeEnum() == CellType.STRING) {
BeanUtils.setProperty(obj, excelFieldMap.fieldName, cell.getStringCellValue());
} else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
BeanUtils.setProperty(obj, excelFieldMap.fieldName, (int) cell.getNumericCellValue());
} else {
//U Can Handel Boolean, Formula, Errors
}
}
list.add(obj);
}
return list;
}
}
impl实现类
/**
* 导入excel
*
* @param file
* @throws Exception
*/
@Transactional(rollbackFor = Exception.class)
@Override
public void importExcel(MultipartFile file,WebSessionUser webSessionUser) throws Exception {
filePath = FiledExcelPath + UpLoad.upLoadFile(file, FiledExcelPath);
Long companyId = webSessionUser.getCompanyId();
List<CarseriesConfiguration> configurations =
ExcelUtils.toList(new File(filePath).getCanonicalPath(),
CarseriesConfiguration.class,
new ExcelUtils.ExcelFieldMap[]{
new ExcelUtils.ExcelFieldMap(0, "carMaterialCode"),
new ExcelUtils.ExcelFieldMap(1, "carMaterialName"),
new ExcelUtils.ExcelFieldMap(2, "countryArea"),
new ExcelUtils.ExcelFieldMap(3, "configName"),
}, 0);
//插入数据
Util.sublist(configurations, l -> carseriesConfigurationMapper.insertAll(l, companyId));
}
Util.sublist是分批插入数据库的轮子
package me.cf81.onestep.util;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.List;
import java.util.function.Consumer;
/**
* @Author: yh
* @Description:
* @Date: Created in 9:44 2018/6/29.
* @Modified By:
*/
public class Util {
/**
* 分批操作list，无返回值
*
* @param list 需要插入数据库的数据
* @param consumer jav8函数接口
* @param <T>
*/
public static <T> Integer sublist(List<T> list, Consumer<List<T>> consumer) {
//多少分多少条插入
return sublist(list, consumer, 5000);
}
public static <T> Integer sublist(List<T> list, Consumer<List<T>> consumer, int pageSize) {
Integer count = 0;
if (list.size() == 0) {
return count;
}
if (list.size() < pageSize) {
consumer.accept(list);
count = list.size();
} else {
int page = list.size() / pageSize;
int lave = list.size() % pageSize;
page = (lave != 0) ? page + 1 : page;
for (int i = 1; i <= page; i++) {
int start = (i-1) * (pageSize);
int end = (i == page) ? start+lave : pageSize+start;
List<T> current = list.subList(start, end);
consumer.accept(current);
count = end;
}
}
return count;
}
}
