用途:当导出数据超过6.5w是excel显示异常,所以想到了写这个工具类
实现逻辑:先用原生poi复制原有工作簿,并将原来list,拆分为list、list1、list2,在使用easypoi导入到新的模板中
原模板:
生成的临时模板(实际看不到):
代码如下:
maven引包:
<!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-base -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.4.0</version>
</dependency>
代码:
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.jupiter.api.Test;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* EasyPoi使用模板导出大量数据自动拆分Sheet
*
* @author LaoNa
* @since 2020-12-25
*/
public class T1 {
@Test
public void t3() throws Exception {
List<Map<String, String>> list = new ArrayList<>();
Map<String, Object> map = new HashMap<>();
for (int i = 0; i < 100; i++) {
Map<String, String> m = new HashMap<>();
m.put("xh", "" + i);
m.put("propertyName", "a" + i);
list.add(m);
}
map.put("list", list);
create(map, "G:\\1.poi\\wjsdjrk.xls", 0, 1);
}
/**
* @param data 传入的数据
* @param templatePath 传入的模板路径
* @param cell list 所在列
* @param row list 所在行
* @return
*/
public static ByteArrayOutputStream create(Map data, String templatePath, int cell, int row) {
try {
//拆分数据
int len = 19;
Map<String, Object> result = new HashMap<>();
List<Object> list = (List<Object>) data.get("list");
int size = list.size();
int count = (size + len - 1) / len;
for (int i = 0; i < count; i++) {
List<Object> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
if (i == 0) {
result.put("list", subList); //list
} else {
result.put("list" + i, subList); //list1 list2
}
}
//创建新模板
//Sprongboot用
ClassPathResource cpr = new ClassPathResource(templatePath);
InputStream fis = cpr.getInputStream();
//直接写路径用,测试用
// File file = new File(templatePath);
// FileInputStream fis = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(fis);
String listName = wb.getSheetAt(0).getRow(row).getCell(cell).getStringCellValue();
for (int i = 0; i < count - 1; i++) {
HSSFSheet sheet = wb.cloneSheet(0);
sheet.getRow(row).getCell(cell).setCellValue(listName.replaceAll("list", "list" + (i + 1)));
}
//把新模板导出为文件,测试用
// FileOutputStream fos = new FileOutputStream("G:\\1.poi\\wjsdjrk22.xls");
// wb.write(fos);
// fis.close();
// fos.close();
// wb.close();
//导出文件
TemplateExportParams params = new TemplateExportParams();
params.setTemplateWb(wb); //设置模板
params.setScanAllsheet(true); //所有工作簿都操作
String[] sheetNameArray = new String[count]; //给工作簿起名
for (int i = 0; i < count; i++) {
sheetNameArray[i] = "Sheet" + (i + 1);
}
params.setSheetName(sheetNameArray);
Workbook book = ExcelExportUtil.exportExcel(params, result); //导出
//把数据导出为文件,测试用
// FileOutputStream fos1 = new FileOutputStream("G:\\1.poi\\zhcx11.xls");
// book.write(fos1);
// fis.close();
// fos1.close();
// book.close();
// return null;
//返回用,实际用
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
Writer writer = new BufferedWriter(new OutputStreamWriter(outputStream, "utf-8"));
book.write(outputStream);
writer.close();
return outputStream;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}