注意headArr 和fieldArr 数量和顺序都需保持一致即可
//不创建对象的导出
public void write(OutputStream out, String[] headArr, String sheetName, List<Map> dataList, String[] fieldArr) throws IOException {
EasyExcel.write(out).head(head(headArr)).sheet(sheetName).doWrite(dataList(dataList, fieldArr));
}
//设置表头
private List<List<String>> head(String[] headMap) {
List<List<String>> list = new ArrayList<List<String>>();
for (String head : headMap) {
List<String> headList = new ArrayList<String>();
headList.add(head);
list.add(headList);
}
return list;
}
//设置导出的数据内容
private List<List<Object>> dataList(List<Map> dataList, String[] dataStrMap) {
List<List<Object>> list = new ArrayList<>();
dataList.stream().forEach(map->{
List<Object> data = new ArrayList<Object>();
for (int i = 0; i < dataStrMap.length; i++) {
data.add(getString(map.get(dataStrMap[i])));
}
list.add(data);
});
return list;
}
使用模板导出List<Map>数据
//模板文件
File fileTemp = new File("D:/template.xlsx");
File targetFile = new File("D:/test.xlsx");
//模板文件不能和目标导出文件是一个文件,使用模板文件其实是追加内容写,可能需提前处理一下模板文件,将表头下得行删除掉,不然数据可能不是从你想要得行数开始写入得
ExcelWriterSheetBuilder sheetBuilder = EasyExcel.write().withTemplate(fileTemp).file(targetFile).head(head(headArr)).sheet(sheetName);
sheetBuilder.needHead(false).doWrite(dataList(dataList, fieldArr));
out.flush();
out.close();
删除excel指定行以下所有行
//从 dataRow行开始,删除这一行下面所有行,包括dataRow行
private void delXlsxByRowNum(File fileTemp, Integer dataRow,File targetFile) {
FileUtils.copyFile(fileTemp, targetFile);
XSSFWorkbook wb = null;
FileOutputStream fileOutputStream=null;
try {
fileOutputStream = new FileOutputStream(targetFile);
wb = new XSSFWorkbook(new FileInputStream(fileTemp));
XSSFSheet sheet = wb.getSheetAt(0);
if(dataRow<sheet.getLastRowNum()){
for (int i = dataRow; i < sheet.getLastRowNum()+1; i++) {
Row row = sheet.getRow(i);
int lastRowNum = sheet.getLastRowNum();
if (i >= 0 && i < lastRowNum) {
sheet.shiftRows(i + 1, lastRowNum, -1);// 将行号为i+1一直到行号为lastRowNum的单元格全部上移一行,以便删除i行
}
if (i == lastRowNum) {
if (row != null) {
sheet.removeRow(row);
}
}
i--;
}
}
wb.write(fileOutputStream);
fileOutputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}finally {
if(wb!=null){
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(fileOutputStream!=null){
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}