步骤1.项目中的pom.xml文件引入hutool的jar
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.0.M3</version>
</dependency>
步骤2.创建测试对象【你的项目需要引入Lombok插件,否则注解报错,具体怎么引入可以百度】,如下:
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.Date;
/**
* <p>
* 设备基本信息表
* </p>
*
* @author wcl
* @since 2021-12-25
*/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class BasTDevice implements Serializable {
private static final long serialVersionUID = -349318113259997487L;
private String id;
private String planId;
private String devNo;
private String devModel;
private String productNo;
private Date buyDate;
private String qrCodeUrl;
private String remark;
}
步骤3.直接上代码,【请在你的代码逻辑中直接调用该方法即可】
/**
* desc
* @author wcl
* @date 2022/4/21 16:03
* @Param mulSheet true代表将数据写入到多个sheet中,否则只写入到一个sheet中
* @return
**/
public void importDataToExcel(HttpServletResponse response,boolean mulSheet) {
ExcelWriter writer=null;
ServletOutputStream out=null;
try{
List<BasDevice> rows=new ArrayList<>();
for(int i=0;i<81000;i++){
rows.add(BasDevice.builder()
.id((i+1)+"")
.qrCodeUrl(RandomUtil.randomString(30))
.buyDate(new Date())
.devNo(RandomUtil.randomString(30))
.planId("12345")
.devModel(RandomUtil.randomNumbers(10)+"")
.productNo("ABC_"+RandomUtil.randomNumbers(8))
.remark("导出OK")
.build()
);
}
writer= ExcelUtil.getBigWriter();
writer.renameSheet("数据导出万条");
LinkedMap<String, String> headerAlias=new LinkedMap<>();
headerAlias.put("id","编号");
headerAlias.put("qrCodeUrl","地址");
headerAlias.put("buyDate","购买日期");
headerAlias.put("devNo","设备编码");
headerAlias.put("planId","计划id");
headerAlias.put("devModel","设备型号");
headerAlias.put("productNo","产品编码");
headerAlias.put("remark","备注");
writer.setHeaderAlias(headerAlias);
for(int i=0;i<headerAlias.size();i++){
writer.setColumnWidth(i,25);
}
// 设置只导出有别名的字段
writer.setOnlyAlias(true);
// 设置冻结行
writer.setFreezePane(1);
if(mulSheet){
int page=((int)rows.size()/20000)+(rows.size()%20000>0?1:0);
writer.renameSheet("导出万条数据1");
List<BasDevice> list=new ArrayList<>();
for(int i=1;i<=page;i++){
//每个sheet20000条数据
writer.setSheet("导出万条数据"+i);
for(int j=0;j<headerAlias.size();j++){
writer.setColumnWidth(j,25);
}
// 设置只导出有别名的字段
writer.setOnlyAlias(true);
// 设置冻结行
writer.setFreezePane(1);
int finalI = i;
list=rows.parallelStream().filter(x->Integer.parseInt(x.getId())>(finalI -1)*20000 && Integer.parseInt(x.getId())<20000*finalI+1).collect(Collectors.toList());
writer.write(list,true);
}
}else{
writer.write(rows,true);
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition","attachment;filename=test.xlsx");
out=response.getOutputStream();
writer.flush(out, true);
}catch (Exception e){
e.printStackTrace();
}finally {
if(writer!=null){
writer.close();
}
if(out!=null){
IoUtil.close(out);
}
}
}
4.导出结果示例
导出到一个sheet
导出到多个sheet,我的是每个sheet设置导入2W条