ArrayList/MySQL数据批量写入Excel表格 - ToB企服应用市场:ToB评测及商务社交产业平台

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.2</version>
</dependency>

复制代码

package com.ccc.bean.tools_enty;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
@NoArgsConstructor
@Data
public class WriteDataBean implements Comparable {
　　 // Excel标头名称
@ExcelProperty("a")
private String a;
@ExcelProperty("b")
private String b;
public String getA() {
return a;
}
public void setA(String a) {
this.a = a;
}
public String getB() {
return b;
}
public void setB(String b) {
this.b = b;
}
@Override
public int compareTo(Object o) {
// //可设置排序
// if (o instance of WriteDataBean){ // WechatOfficialAccountBean wechatOfficialAccountBean= (WechatOfficialAccountBean) o; // return this.getUpload_time().hashCode()-wechatOfficialAccountBean.getUpload_time().hashCode(); // } throw new ClassCastException("不能转换 WriteDataBean ");

复制代码

} }

复制代码

package com.ccc.tools;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.felephantst.bean.tools_enty.WriteDataBean;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName: FileTool
* @Description TODO 操作文件工具类
* @Author: 东霖
* @Date: 2023/9/12 13:19
* @Version 1.0
**/
public class FileTool {
/**
* 读取指定文件
*
* @param filePath
* @return 返回一个字符串集合
*/
public static List<String> readFile(String filePath) {
List<String> stringList = new ArrayList<>();
try {
FileReader fr = new FileReader(filePath);
BufferedReader bf = new BufferedReader(fr);
String str;
// 按行读取字符串
while ((str = bf.readLine()) != null) {
stringList.add(str);
}
bf.close();
fr.close();
} catch (IOException e) {
e.printStackTrace();
}
return stringList;
}
/**
* TODO：小数据量写出数据到 Excel（2000条左右用这个）
*
* @param bean 模板类
* @param outFilePath 输出路径全称：test.xlsx
* @param sheetName excel Sheet 名称
* @param collect 数据集
*/
public static void writeMinDataExcel(Class<?> bean, String outFilePath, String sheetName, ArrayList collect) {
OutputStream outputStream = null;
try {
outputStream = new FileOutputStream(outFilePath);
EasyExcel.write(outputStream, bean).sheet(sheetName).doWrite(collect);
//写出到文件
outputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* TODO：大数据量写出数据到 Excel（2000条左右用这个）
*
* @param bean 模板类
* @param outFilePath 输出路径全称：test.xlsx
* @param sheetName excel Sheet 名称
* @param sizeLimitDataList 数据集
* @param sizeLimit 设置多少条数据为一个Sheet
*/
public static void writeMaxDataExcel(Class<?> bean, String outFilePath, String sheetName, ArrayList sizeLimitDataList, Integer sizeLimit) {
// 总Sheet数
int num = sizeLimitDataList.size() / sizeLimit + (sizeLimitDataList.size() % sizeLimit > 0 ? 1 : 0);
// 输出流
OutputStream outputStream = null;
try {
// 以字节流的形式输出响应正文
outputStream = new FileOutputStream(outFilePath);
try (ExcelWriter excelWriter = EasyExcel.write(outputStream, bean).build()) {
// 中间list调用写入
List<?> partList = null;
WriteSheet writeSheet =null;
for (int i = 0; i < num; i++) {
// 每次写入都要创建WriteSheet, 这里注意必须指定sheetNo, 并且sheetName必须不一样
writeSheet = EasyExcel.writerSheet(i, sheetName + (i + 1)).build();
// 截取批次长度的list
partList = sizeLimitDataList.subList(0, sizeLimit);
// 分批业务逻辑处理- 打印替代
excelWriter.write(partList, writeSheet);
// 去除已经处理的部分 (Arrays.asList()方式生成的数据不能进行此修改操作，会报错)
partList.clear();
}
// 获取最后一次截取后的剩余列表数据
if (!sizeLimitDataList.isEmpty()) {
// 业务逻辑数据处理
excelWriter.write(sizeLimitDataList, writeSheet);
}
}
} catch (IOException ex) {
throw new RuntimeException(ex);
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException ex) {
throw new RuntimeException(ex);
}
}
}
}
public static void main (String[]args){
WriteDataBean writeDataBean = new WriteDataBean();
writeDataBean.setA("a");
writeDataBean.setB("b");
WriteDataBean writeDataBean1 = new WriteDataBean();
writeDataBean1.setA("ad");
writeDataBean1.setB("bd");
objects.add(writeDataBean1);
objects.add(writeDataBean);
writeMaxDataExcel(WriteDataBean.class, "D://cdc.xlsx", "test", objects, 1);
writeMinDataExcel(WriteDataBean.class, "D://cddc.xlsx", "test", objects);
}
}

复制代码