Excel读写
对于读Excel,因为场景是无固定模板即每次读都不确定表头,找了很久没有找到EasyExcel的无固定模板读写逐行逐列获取值的方法,所以使用HuTool的ExcelUtil实现逐行逐列的读取。
EasyExcel的读写
EasyExcel的同步读
配置
public class ReadTableListener extends AnalysisEventListener<Map<Integer, String>> {
private static final Logger LOGGER = LoggerFactory.getLogger(ReadTableListener.class);
@Override
public void invoke(Map<Integer, String> data, AnalysisContext analysisContext) {
LOGGER.info("解析到一条数据:{}", data.toString());
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
LOGGER.info("所有数据解析完成!");
}
}
使用
List<Object> objects = EasyExcel.read(file.getInputStream(), new ReadTableListener()).sheet().doReadSync();
EasyExcel的异步读
配置
public class ReadDataListener extends AnalysisEventListener<Map<Integer, String>> {
private static final Logger LOGGER = LoggerFactory.getLogger(ReadDataListener.class);
Map<Integer, String> reflect = new HashMap<>();
List<Map<String, String>> dataList = new ArrayList<>();
boolean isFirst = true;
@Override
public void invoke(Map<Integer, String> data, AnalysisContext analysisContext) {
String s = data.toString();
LOGGER.info("解析到一条数据:{}", s);
s = s.substring(1, s.length() - 1);
String[] split = s.replaceAll(" ", "").split(",");
Map<String, String> value = null;
if (!isFirst) {
value = new HashMap<>();
}
for (int i = 0; i < split.length; i++) {
String[] dataString = split[i].split("=");
if (isFirst) {
reflect.put(Integer.parseInt(dataString[0]), dataString[1]);
continue;
}
value.put(reflect.get(Integer.parseInt(dataString[0])), dataString[1]);
}
if (null != value) {
dataList.add(value);
}
isFirst = false;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
LOGGER.info("所有数据解析完成!" + dataList.toString());
}
}
使用
EasyExcel.read(file.getInputStream(), new ReadDataListener()).sheet().doRead();
EasyExcel的写
// List<List<String>> head
EasyExcel.write(response.getOutputStream()).head(head).sheet("文件模板").doWrite(Collections.emptyList());
ExcelUtil的读
注意:poi4.12及以上
优点:逐行逐列的读取
ExcelReader excelReader = ExcelUtil.getReader(file.getInputStream());
// 读取第一行到最后一行数据
List<List<Object>> read = excelReader.read(1, excelReader.getRowCount());