import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import java.io.InputStream;
import java.util.List;
import java.util.NoSuchElementException;
/**
* EXCEL解析
**/
public class EasyPoiUtils {
/**
* 功能描述:根据接收的Excel文件来导入多个sheet,根据索引可返回一个集合
*
* @param inputStream excel输入流
* @param sheetIndex 导入sheet索引
* @param titleRows 表标题的行数
* @param headerRows 表头行数
* @param pojoClass Excel实体类
*/
public static <T> List<T> importExcel(InputStream inputStream, int sheetIndex, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
// 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
ImportParams params = new ImportParams();
// 第几个sheet表页
params.setStartSheetIndex(sheetIndex);
//设置表标题行数
params.setTitleRows(titleRows);
//设置表头行数
params.setHeadRows(headerRows);
List<T> list = null;
try {
//读取的excel数据集合
list = ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new RuntimeException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
}
调用例子
public AjaxResult uploadStatisticsExcel(MultipartFile file) {
//分批进行读取excel的sheet工作表
List<HashMap<String, Object>> list = mapMapper.getChinaGridMap();
List<StatisticsDesc> statisticsDesc = null;
List<GridStatistics> gridStatistics = null;
List<StatisticalChart> statisticalChart = null;
try {
//读取第一个sheet表
statisticsDesc = EasyPoiUtils.importExcel(file.getInputStream(), 0, 0, 1, StatisticsDesc.class);
//读取第二个sheet表
gridStatistics = EasyPoiUtils.importExcel(file.getInputStream(), 1, 0, 1, GridStatistics.class);
//读取第三个sheet表
statisticalChart = EasyPoiUtils.importExcel(file.getInputStream(), 2, 0, 1, StatisticalChart.class);
} catch (IOException e) {
e.printStackTrace();
return AjaxResult.error("500", "数据有误!无法插入!");
}
String statisticsId = UUID.getUUID();
for (StatisticsDesc o : statisticsDesc) {
o.setId(statisticsId);
o.setCreateTime(new Date());
o.setCreateBy(SecurityUtils.getUsername());
if (o.getTitle() != null && !o.getTitle().equals("")) {
mapMapper.addStatisticsDesc(o);
}
}
List<GridStatistics> listGridStatistics = new ArrayList<>();
for (GridStatistics o : gridStatistics) {
for (HashMap map : list) {
boolean surface = GeoJSONUtils.surface(Double.parseDouble(o.getLon()), Double.parseDouble(o.getLat()), map.get("geojson").toString());
if (surface) {
o.setGridCode(map.get("code").toString());
break;
}
}
o.setCreateTime(new Date());
o.setCreateBy(SecurityUtils.getUsername());
o.setId(UUID.getUUID());
o.setStatisticsId(statisticsId);
listGridStatistics.add(o);
}
if (listGridStatistics.size() > 0) mapMapper.addGridStatistics(listGridStatistics);
List<StatisticalChart> listStatisticalChart = new ArrayList<>();
for (StatisticalChart o : statisticalChart) {
o.setId(UUID.getUUID());
o.setCreateTime(new Date());
o.setCreateBy(SecurityUtils.getUsername());
o.setStatisticsId(statisticsId);
if (o.getName() != null && o.getValue() != null) {
listStatisticalChart.add(o);
}
}
if (listStatisticalChart.size() > 0) mapMapper.addStatisticalChart(listStatisticalChart);
return AjaxResult.success("200", "操作成功");
}