企业网站建设规划,现代简约设计风格说明,怎么用自己的网站做网页,ppt汇报模板免费目录
1.源码下载
2、先上结论
3、代码
3.1、实体、接口
3.2、读CSV
3.3、读Excel
3.4、写CSV
3.5、写Excel
3.6、业务示例 1.源码下载
示例源码下载#xff08;含模板#xff09;
2、先上结论
注意注意#xff1a;#xff1a;#xff1a;#xff1a;导出大…目录
1.源码下载
2、先上结论
3、代码
3.1、实体、接口
3.2、读CSV
3.3、读Excel
3.4、写CSV
3.5、写Excel
3.6、业务示例 1.源码下载
示例源码下载含模板
2、先上结论
注意注意导出大文件的时候可能会有临时存储
我的是在C:\Users\xbx\AppData\Local\Temp\poifiles读csv文件
行数1048575
hutool 5205
esayPoi 3929
easyExcel1 1917
easyExcel2 2832读excel文件
行数1048575
hutool 58207
esayPoi 25513
easyExce1 10646
easyExcel2 10123写csv文件
行数1048575
hutool 6136
esayPoi 828
easyExcel1 6943
easyExcel2 5212写excel文件
行数1048575
hutool1 146655
hutool2 23570
esayPoi1 21805
esayPoi2 93034
easyExcel120762
easyExcel220523
easyExcel315386
easyExcel414586
3、代码
3.1、实体、接口
package cn.sdjtky.ltpon.FileTest;import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;/*** version v1.0* ProjectName: ltpon* ClassName: Strainentey* Description:* Author: xbx* Date: 2023/12/8 13:55*/
Data
public class DeviceDto {//snchannelString sn;//与地表距离mDouble surfaceDistance;//结构层位String structuralLayer;//就写电信号String channel;//这个值就是StressStrainDataDto里面的key 如t.v1,t.v2String dataKey;
}package cn.sdjtky.ltpon.FileTest;import cn.hutool.core.annotation.Alias;
import lombok.Data;/*** version v1.0* ProjectName: ltpon* ClassName: Strainentey* Description:* Author: xbx* Date: 2023/12/8 13:55*/
Data
public class StrainEntity {Alias(时间)String time;Alias(通道1)String v1;Alias(通道2)String v2;Alias(通道3)String v3;Alias(通道4)String v4;Double v5;Double v6;Double v7;Double v8;
}package cn.sdjtky.ltpon.FileTest;import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;/*** version v1.0* ProjectName: ltpon* ClassName: Strainentey* Description:* Author: xbx* Date: 2023/12/8 13:55*/
Data
public class StrainEntity2 {Excel(name 时间)String time;Excel(name 通道1)String v1;Excel(name 通道2)String v2;Excel(name 通道3)String v3;Excel(name 通道4)String v4;Double v5;Double v6;Double v7;Double v8;
}package cn.sdjtky.ltpon.FileTest;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;/*** version v1.0* ProjectName: ltpon* ClassName: Strainentey* Description:* Author: xbx* Date: 2023/12/8 13:55*/
Data
public class StrainEntity3 {ExcelProperty(时间)String time;ExcelProperty(通道1)String v1;ExcelProperty(通道2)String v2;ExcelProperty(通道3)String v3;ExcelProperty(通道4)String v4;Double v5;Double v6;Double v7;Double v8;
}package cn.sdjtky.ltpon.FileTest;import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;import java.util.Date;/*** version v1.0* ProjectName: ltpon* ClassName: Strainentey* Description:* Author: xbx* Date: 2023/12/8 13:55*/
Data
public class StressStrainDataDto {
// 年/月/日String date;
// 时/分/秒/毫秒String time;
// 时/分/秒/毫秒String dateTime;Double v1;Double v2;Double v3;Double v4;Double v5;Double v6;Double v7;Double v8;Double v9;Double v10;Double v11;Double v12;Double v13;
}package cn.sdjtky.ltpon.FileTest;import cn.sdjtky.ltpon.FileTest.StrainEntity3;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson2.JSON;
import org.apache.poi.ss.formula.functions.T;import java.util.ArrayList;
import java.util.List;// 有个很重要的点 DemoDataListener 不能被spring管理要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class Strain3ReadListenerT implements ReadListenerT {/*** 每隔5条存储数据库实际使用中可以100条然后清理list 方便内存回收*//*** 缓存的数据*/private ListT cachedDataList new ArrayListT();/*** 这个每一条数据解析都会来调用** param data one row value. Is is same as {link AnalysisContext#readRowHolder()}* param context*/Overridepublic void invoke(T data, AnalysisContext context) {cachedDataList.add(data);
// // 达到BATCH_COUNT了需要去存储一次数据库防止数据几万条数据在内存容易OOM
// if (cachedDataList.size() BATCH_COUNT) {
// // 存储完成清理 list
// cachedDataList ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
// }}/*** 所有数据解析完成了 都会来调用** param context*/Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 这里也要保存数据确保最后遗留的数据也存储到数据库}public ListT getCachedDataList() {return cachedDataList;}
}
3.2、读CSV
package cn.sdjtky.ltpon.FileTest;import cn.afterturn.easypoi.csv.CsvImportUtil;
import cn.afterturn.easypoi.csv.entity.CsvImportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.text.csv.CsvReader;
import cn.hutool.core.text.csv.CsvUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;public class ReadCsvTest {private static final Logger LOGGER LoggerFactory.getLogger(ReadCsvTest.class);Testpublic void test() {String filePath G:/test/1048575.csv;hutool(filePath);
// esayPoi(filePath);
// easyExcel1(filePath);
// easyExcel2(filePath);}public void hutool(String filePath) {try {long start System.currentTimeMillis();CsvReader reader CsvUtil.getReader();ListStrainEntity result reader.read(ResourceUtil.getUtf8Reader(filePath), StrainEntity.class);long end System.currentTimeMillis();System.out.println(hutool (end-start));System.out.println(行数result.size());} catch (Exception e) {e.printStackTrace();}}public void esayPoi(String filePath) {try {CsvImportParams params new CsvImportParams(CsvImportParams.UTF8);long start System.currentTimeMillis();ListStrainEntity2 result CsvImportUtil.importCsv(new FileInputStream(new File(filePath)),StrainEntity2.class,params);long end System.currentTimeMillis();System.out.println(esayPoi (end-start));System.out.println(行数result.size());} catch (Exception e) {e.printStackTrace();}}public void easyExcel1(String filePath) {try {ImportParams params new ImportParams();ListStrainEntity3 result new ArrayListStrainEntity3();long start System.currentTimeMillis();EasyExcel.read(filePath, StrainEntity3.class, new AnalysisEventListenerStrainEntity3() {Overridepublic void invoke(StrainEntity3 reportExcel, AnalysisContext analysisContext) {// 将读取到的每一行存入reportDetails集合中result.add(reportExcel);}Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}).excelType(ExcelTypeEnum.CSV).sheet().doRead();long end System.currentTimeMillis();System.out.println(easyExcel1 (end-start));System.out.println(行数result.size());} catch (Exception e) {e.printStackTrace();}}public void easyExcel2(String filePath) {final Strain3ReadListenerStrainEntity3 strain3ReadListener new Strain3ReadListenerStrainEntity3() ;try {long start System.currentTimeMillis();EasyExcel.read(filePath, StrainEntity3.class, strain3ReadListener).sheet().doRead();
// EasyExcel.read(filePath)
// .excelType(ExcelTypeEnum.CSV)
// .registerReadListener(strain3ReadListener)
// .sheet()
// .doRead();ListStrainEntity3 result strain3ReadListener.getCachedDataList();long end System.currentTimeMillis();System.out.println(easyExcel2 (end-start));System.out.println(行数result.size());} catch (Exception e) {e.printStackTrace();}}}3.3、读Excel
package cn.sdjtky.ltpon.FileTest;import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.handler.inter.IReadHandler;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.sdjtky.ltpon.easy.excel.listener.MergeCellsReadListener;
import cn.sdjtky.ltpon.poi.DemoDataListener;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.fastjson2.JSON;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;public class ReadExcelTest {private static final Logger LOGGER LoggerFactory.getLogger(ReadExcelTest.class);Testpublic void test() {String filePath G:/test/1048575.xlsx;hutool(filePath);
// esayPoi(filePath);
// easyExcel1(filePath);
// easyExcel2(filePath);}public void hutool(String filePath) {try {long start System.currentTimeMillis();ExcelReader reader ExcelUtil.getReader(filePath);ListStrainEntity result reader.readAll(StrainEntity.class);long end System.currentTimeMillis();System.out.println(hutool (end-start));System.out.println(行数result.size());} catch (Exception e) {e.printStackTrace();}}public void esayPoi(String filePath) {try {ImportParams params new ImportParams();ListStrainEntity2 result new ArrayListStrainEntity2();long start System.currentTimeMillis();ExcelImportUtil.importExcelBySax(new FileInputStream(new File(filePath)),StrainEntity2.class,params,new IReadHandlerStrainEntity2() {Overridepublic void handler(StrainEntity2 o) {result.add(o);}Overridepublic void doAfterAll() {System.out.println(全部执行完毕了--------------------------------);}});long end System.currentTimeMillis();System.out.println(esayPoi (end-start));System.out.println(行数result.size());} catch (Exception e) {e.printStackTrace();}}public void easyExcel1(String filePath) {try {ImportParams params new ImportParams();ListStrainEntity3 result new ArrayListStrainEntity3();long start System.currentTimeMillis();EasyExcel.read(filePath, StrainEntity3.class, new AnalysisEventListenerStrainEntity3() {Overridepublic void invoke(StrainEntity3 reportExcel, AnalysisContext analysisContext) {// 将读取到的每一行存入reportDetails集合中result.add(reportExcel);}Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}).sheet().doRead();long end System.currentTimeMillis();System.out.println(easyExcel1 (end-start));System.out.println(行数result.size());} catch (Exception e) {e.printStackTrace();}}public void easyExcel2(String filePath) {final Strain3ReadListenerStrainEntity3 strain3ReadListener new Strain3ReadListenerStrainEntity3() ;try {long start System.currentTimeMillis();EasyExcel.read(filePath, StrainEntity3.class, strain3ReadListener).sheet().doRead();
// EasyExcel.read(filePath)
// .registerReadListener(strain3ReadListener)
// .sheet()
// .doRead();ListStrainEntity3 result strain3ReadListener.getCachedDataList();long end System.currentTimeMillis();System.out.println(easyExcel2 (end-start));System.out.println(行数result.size());} catch (Exception e) {e.printStackTrace();}}}3.4、写CSV
package cn.sdjtky.ltpon.FileTest;import cn.afterturn.easypoi.csv.CsvExportUtil;
import cn.afterturn.easypoi.csv.CsvImportUtil;
import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.csv.entity.CsvImportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.hutool.core.io.FastByteArrayOutputStream;
import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.text.csv.CsvReader;
import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.core.text.csv.CsvWriter;
import cn.hutool.core.util.CharsetUtil;
import cn.sdjtky.ltpon.query.HumidityQuery;
import cn.sdjtky.ltpon.utils.BeanCopierUtils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.ruoyi.common.utils.uuid.IdUtils;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;public class WriteCsvTest {private static final Logger LOGGER LoggerFactory.getLogger(WriteCsvTest.class);Testpublic void test() {String filePath G:/test/1048575.csv;ListStrainEntity3 strainEntity3List Rerad_easyExcel1(filePath);ListStrainEntity strainEntityList BeanCopierUtils.list(strainEntity3List, StrainEntity::new);ListStrainEntity2 strainEntity2List BeanCopierUtils.list(strainEntity3List, StrainEntity2::new);
// ListStrainEntity3 strainEntity3List BeanCopierUtils.list(strainEntity3List, StrainEntity3::new);
// hutool(strainEntityList);
// esayPoi(strainEntity2List);
// easyExcel1(strainEntity3List);easyExcel2(strainEntity3List);}public ListStrainEntity3 Rerad_easyExcel1(String filePath) {ListStrainEntity3 result new ArrayListStrainEntity3();try {ImportParams params new ImportParams();EasyExcel.read(filePath, StrainEntity3.class, new AnalysisEventListenerStrainEntity3() {Overridepublic void invoke(StrainEntity3 reportExcel, AnalysisContext analysisContext) {// 将读取到的每一行存入reportDetails集合中result.add(reportExcel);}Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}).excelType(ExcelTypeEnum.CSV).sheet().doRead();} catch (Exception e) {e.printStackTrace();}return result;}public void hutool(ListStrainEntity list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();CsvWriter writer CsvUtil.getWriter(G:/test/write/hutool.csv, CharsetUtil.CHARSET_UTF_8);
// writer.write(list);writer.writeBeans(list);long end System.currentTimeMillis();long cost end-start;System.out.println(hutool cost);} catch (Exception e) {e.printStackTrace();}}public void esayPoi(ListStrainEntity2 list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();CsvExportParams params new CsvExportParams(CsvImportParams.UTF8);FileOutputStream fos new FileOutputStream(G:/test/write/esayPoi.csv);CsvExportUtil.exportCsv(params, StrainEntity2.class, list, fos);fos.flush();fos.close();long end System.currentTimeMillis();long cost end-start;System.out.println(esayPoi cost);} catch (Exception e) {e.printStackTrace();}}public void easyExcel1(ListStrainEntity3 list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();FastByteArrayOutputStream fastByteArrayOutputStream new FastByteArrayOutputStream();try {EasyExcel.write(fastByteArrayOutputStream, StrainEntity3.class).autoCloseStream(false).excelType(ExcelTypeEnum.CSV).sheet(sheet页).doWrite(list);FileOutputStream fileOutputStream new FileOutputStream(G:/test/write/easyExcel.csv);fastByteArrayOutputStream.writeTo(fileOutputStream);fileOutputStream.flush();fileOutputStream.close();} finally {}long end System.currentTimeMillis();long cost end-start;System.out.println(easyExcel1 cost);} catch (Exception e) {e.printStackTrace();}}public void easyExcel2(ListStrainEntity3 list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();try {EasyExcel.write(G:/test/write/easyExcel.csv, StrainEntity3.class).excelType(ExcelTypeEnum.CSV).sheet(sss).doWrite(list);} finally {}long end System.currentTimeMillis();long cost end-start;System.out.println(easyExcel2 cost);} catch (Exception e) {e.printStackTrace();}}}3.5、写Excel
package cn.sdjtky.ltpon.FileTest;import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.handler.inter.IExcelExportServer;
import cn.afterturn.easypoi.handler.inter.IReadHandler;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FastByteArrayOutputStream;
import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.core.text.csv.CsvWriter;
import cn.hutool.core.util.CharsetUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.sdjtky.ltpon.utils.BeanCopierUtils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.ruoyi.common.utils.uuid.IdUtils;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;public class WriteExcelTest {private static final Logger LOGGER LoggerFactory.getLogger(WriteExcelTest.class);Testpublic void test() {String filePath G:/test/1048575.csv;ListStrainEntity3 strainEntity3List Rerad_easyExcel1(filePath);ListStrainEntity strainEntityList BeanCopierUtils.list(strainEntity3List, StrainEntity::new);ListStrainEntity2 strainEntity2List BeanCopierUtils.list(strainEntity3List, StrainEntity2::new);
// hutool1(strainEntityList);
// hutool2(strainEntityList);
// esayPoi1(strainEntity2List);
// esayPoi2(strainEntity2List);
// easyExcel1(strainEntity3List);
// easyExcel2(strainEntity3List);
// easyExcel3(strainEntity3List);easyExcel4(strainEntity3List);}public ListStrainEntity3 Rerad_easyExcel1(String filePath) {ListStrainEntity3 result new ArrayListStrainEntity3();try {ImportParams params new ImportParams();EasyExcel.read(filePath, StrainEntity3.class, new AnalysisEventListenerStrainEntity3() {Overridepublic void invoke(StrainEntity3 reportExcel, AnalysisContext analysisContext) {// 将读取到的每一行存入reportDetails集合中result.add(reportExcel);}Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}).excelType(ExcelTypeEnum.CSV).sheet().doRead();} catch (Exception e) {e.printStackTrace();}return result;}public void hutool1(ListStrainEntity list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();ExcelWriter writer ExcelUtil.getWriter(G:/test/write/hutool.xlsx);writer.write(list, true);// 关闭writer释放内存writer.close();long end System.currentTimeMillis();long cost end-start;System.out.println(hutool cost);} catch (Exception e) {e.printStackTrace();}}public void hutool2(ListStrainEntity list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();BigExcelWriter writer ExcelUtil.getBigWriter(G:/test/write/hutool2 IdUtils.getSequenceID().xlsx);writer.write(list);writer.close();long end System.currentTimeMillis();long cost end-start;System.out.println(hutool2 cost);} catch (Exception e) {e.printStackTrace();}}public void esayPoi1(ListStrainEntity2 list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();ExportParams params new ExportParams();params.setMaxNum(1048576);Workbook workbook null;workbook ExcelExportUtil.exportBigExcel(params, StrainEntity2.class, new IExcelExportServer() {Overridepublic ListObject selectListForExcelExport(Object obj, int page) {if (list.size()0){return null;}ListObject listObj new ArrayListObject();listObj.addAll(list);list.clear();return listObj;}}, 1);// workbook ExcelExportUtil.exportExcel(params,StrainEntity2.class,list);FileOutputStream fos new FileOutputStream(G:/test/write/esayPoi.xlsx);workbook.write(fos);fos.close();long end System.currentTimeMillis();long cost end-start;System.out.println(esayPoi cost);} catch (Exception e) {e.printStackTrace();}}public void esayPoi2(ListStrainEntity2 list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();MapString, Object map new HashMapString, Object();map.put(list, list);TemplateExportParams params new TemplateExportParams(G:/test/t/StrainEntity2.xlsx);Workbook workbook ExcelExportUtil.exportExcel(params, map);FileOutputStream fos new FileOutputStream(G:/test/write/esayPoi2.xlsx);workbook.write(fos);fos.close();long end System.currentTimeMillis();long cost end-start;System.out.println(esayPoi2 cost);} catch (Exception e) {e.printStackTrace();}}public void easyExcel1(ListStrainEntity3 list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();FastByteArrayOutputStream fastByteArrayOutputStream new FastByteArrayOutputStream();try {EasyExcel.write(fastByteArrayOutputStream, StrainEntity3.class).autoCloseStream(false).excelType(ExcelTypeEnum.XLSX).sheet(sheet页).doWrite(list);FileOutputStream fileOutputStream new FileOutputStream(G:/test/write/easyExcel.xlsx);fastByteArrayOutputStream.writeTo(fileOutputStream);fileOutputStream.flush();fileOutputStream.close();} finally {}long end System.currentTimeMillis();long cost end-start;System.out.println(easyExcel1 cost);} catch (Exception e) {e.printStackTrace();}}public void easyExcel2(ListStrainEntity3 list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();try {EasyExcel.write(G:/test/write/easyExcel.xlsx, StrainEntity3.class).excelType(ExcelTypeEnum.XLSX).sheet(sss).doWrite(list);} finally {}long end System.currentTimeMillis();long cost end-start;System.out.println(easyExcel2 cost);} catch (Exception e) {e.printStackTrace();}}public void easyExcel3(ListStrainEntity3 list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();String templateFileName G:/test/t/StrainEntity3.xlsx;String fileName G:/test/write/easyExcel.xlsx;// 方案1 一下子全部放到内存里面 并填充// 这里 会填充到第一个sheet 然后文件流会自动关闭EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(list);long end System.currentTimeMillis();long cost end-start;System.out.println(easyExcel3 cost);} catch (Exception e) {e.printStackTrace();}}public void easyExcel4(ListStrainEntity3 list) {System.out.println(数据行数list.size());try {long start System.currentTimeMillis();FastByteArrayOutputStream fastByteArrayOutputStream new FastByteArrayOutputStream();// 方案2 分多次 填充 会使用文件缓存省内存String templateFileName G:/test/t/StrainEntity3.xlsx;String fileName G:/test/write/easyExcel.xlsx;try (com.alibaba.excel.ExcelWriter excelWriter EasyExcel.write(fastByteArrayOutputStream).withTemplate(templateFileName).build()) {WriteSheet writeSheet EasyExcel.writerSheet().build();excelWriter.fill(list, writeSheet);}// String templateFileName G:/test/t/StrainEntity3.1.xlsx;
// String fileName G:/test/write/easyExcel.xlsx;
// try (com.alibaba.excel.ExcelWriter excelWriter EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
// WriteSheet writeSheet EasyExcel.writerSheet().build();
// excelWriter.fill(new FillWrapper(sl, list), writeSheet);
// }// String templateFileName G:/test/t/StrainEntity3.1.xlsx;
// String fileName G:/test/write/easyExcel.xlsx;
// try (com.alibaba.excel.ExcelWriter excelWriter EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
// WriteSheet writeSheet EasyExcel.writerSheet().build();
// excelWriter.fill(new FillWrapper(sl, listX), writeSheet);
// excelWriter.fill(new FillWrapper(sl, listX), writeSheet);
// excelWriter.fill(new FillWrapper(sl, listX), writeSheet);
// excelWriter.fill(new FillWrapper(sl, listX), writeSheet);
// excelWriter.fill(new FillWrapper(sl, listX), writeSheet);
// }long end System.currentTimeMillis();long cost end-start;System.out.println(easyExcel4 cost);} catch (Exception e) {e.printStackTrace();}}
}3.6、业务示例
package cn.sdjtky.ltpon.FileTest;import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.handler.inter.IExcelExportServer;
import cn.hutool.core.io.FastByteArrayOutputStream;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.sdjtky.ltpon.utils.BeanCopierUtils;
import cn.sdjtky.ltpon.utils.LocalDateTimeUtils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.uuid.IdUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.time.LocalDateTime;
import java.util.*;public class StressStrainTest {private static final Logger LOGGER LoggerFactory.getLogger(StressStrainTest.class);private static final String stationName 临沂G205观测点;private static final String pileNumber K103--K113;private static final String observer 徐本锡;private static final String observationDate LocalDateTimeUtils.format(LocalDateTimeUtils.DEFAULT_PATTERN,LocalDateTime.now());private static final String reviewer 金小少;private static final String reviewDate LocalDateTimeUtils.format(LocalDateTimeUtils.DEFAULT_PATTERN,LocalDateTime.now());Testpublic void test() {
// esayPoi2();easyExcel4();
// easyExcelCSV2();}public MapString, Object getData(){MapString, Object map new HashMapString, Object();ListDeviceDto deviceList new ArrayListDeviceDto();for (int i1; i8; i){DeviceDto deviceDto new DeviceDto();deviceDto.setSn(传感器x_i);deviceDto.setSurfaceDistance(Double.valueOf(i));deviceDto.setStructuralLayer(层位i);deviceDto.setChannel(电信号);deviceDto.setDataKey(t.vi);deviceList.add(deviceDto);}ListMapString, Object dataList new ArrayListMapString, Object();ListStressStrainDataDto dataList2 new ArrayListStressStrainDataDto();for(int i0; i100; i){MapString, Object m new HashMapString, Object();StressStrainDataDto dto new StressStrainDataDto();for (int j1; j8; j){m.put(date, DateUtils.datePath());m.put(time, DateUtils.dateTimeNow(DateUtils.HHMMssSSS_1));m.put(vj, Math.random()1);dto.setDate(DateUtils.datePath());dto.setTime(DateUtils.dateTimeNow(DateUtils.HHMMssSSS_1));dto.setDateTime(DateUtils.dateTimeNow(DateUtils.YYYYMMDDHHMMssSSS_1));dto.setV1( Math.random()1);dto.setV2( Math.random()1);dto.setV3( Math.random()1);dto.setV4( Math.random()1);dto.setV5( Math.random()1);dto.setV6( Math.random()1);dto.setV7( Math.random()1);dto.setV8( Math.random()1);}dataList.add(m);dataList2.add(dto);}map.put(stationName, stationName);map.put(pileNumber, pileNumber);map.put(observer, observer);map.put(observationDate, observationDate);map.put(reviewer, reviewer);map.put(reviewDate, reviewDate);map.put(deviceList, deviceList);map.put(dataList, dataList2);return map;}public void esayPoi2() {MapString, Object data getData();System.out.println(数据行数((List)data.get(dataList)).size());try {long start System.currentTimeMillis();TemplateExportParams params new TemplateExportParams(G:/test/t/stressStrainTemplateDongHua.xlsx);params.setColForEach(true);Workbook workbook ExcelExportUtil.exportExcel(params, data);FileOutputStream fos new FileOutputStream(G:/test/write/stressStrainTemplateDongHua.xlsx);workbook.write(fos);fos.close();long end System.currentTimeMillis();long cost end-start;System.out.println(esayPoi2 cost);} catch (Exception e) {e.printStackTrace();}}public void easyExcel4() {MapString, Object data getData();MapString, Object data1 new HashMapString, Object();data1.put(stationName, data.get(stationName));data1.put(pileNumber, data.get(pileNumber));data1.put(observer, data.get(observer));data1.put(observationDate, data.get(observationDate));data1.put(reviewer, data.get(reviewer));data1.put(reviewDate, data.get(reviewDate));ListDeviceDto deviceList (ListDeviceDto) data.get(deviceList);ListStressStrainDataDto dataList (ListStressStrainDataDto) data.get(dataList);// 根据用户传入字段 导出数据SetString includeColumnFiledNames new HashSetString();includeColumnFiledNames.add(date);
// includeColumnFiledNames.add(time);for(int i0; ideviceList.size(); i){includeColumnFiledNames.add(v(i1));}System.out.println(数据行数((List)data.get(dataList)).size());try {long start System.currentTimeMillis();Resource resource new DefaultResourceLoader().getResource(classpath:templates/stressStrain/stressStrainTemplateDongHua.xlsx);FastByteArrayOutputStream outputStream new FastByteArrayOutputStream();// 方案2 分多次 填充 会使用文件缓存省内存
// String templateFileName G:/test/t/stressStrainTemplateDongHua2.xlsx;
// try (com.alibaba.excel.ExcelWriter excelWriter EasyExcel.write(fastByteArrayOutputStream).withTemplate(templateFileName).build()) {
// WriteSheet writeSheet EasyExcel.writerSheet().build();
// FillConfig fillConfig FillConfig.builder().autoStyle(true).direction(WriteDirectionEnum.HORIZONTAL).build();
// excelWriter.fill(data1, writeSheet);
// excelWriter.fill(new FillWrapper(deviceList, deviceList), fillConfig, writeSheet);writeSheet.setIncludeColumnFieldNames(includeColumnFiledNames);
// excelWriter.fill(new FillWrapper(dataList, dataList), writeSheet);
// }try (com.alibaba.excel.ExcelWriter excelWriter EasyExcel.write(outputStream).withTemplate(resource.getInputStream()).build()) {WriteSheet writeSheet EasyExcel.writerSheet().build();FillConfig fillConfig FillConfig.builder().autoStyle(true).direction(WriteDirectionEnum.HORIZONTAL).build();excelWriter.fill(data1, writeSheet);excelWriter.fill(new FillWrapper(deviceList, deviceList), fillConfig, writeSheet);excelWriter.fill(new FillWrapper(dataList, dataList), writeSheet);excelWriter.close();outputStream.flush();outputStream.close();}
// FileOutputStream fileOutputStream new FileOutputStream(G:/test/write/123.xlsx);
// outputStream.writeTo(fileOutputStream);
// fileOutputStream.flush();
// fileOutputStream.close();long end System.currentTimeMillis();
// String templateFileName G:/test/t/stressStrainTemplateDongHua2.xlsx;
// String fileName G:/test/write/stressStrainTemplateDongHua2.xlsx;
// try (com.alibaba.excel.ExcelWriter excelWriter EasyExcel.write(fileName).withTemplate(resource.getInputStream()).build()) {
// WriteSheet writeSheet EasyExcel.writerSheet().build();
// FillConfig fillConfig FillConfig.builder().autoStyle(true).direction(WriteDirectionEnum.HORIZONTAL).build();
// excelWriter.fill(data1, writeSheet);
// excelWriter.fill(new FillWrapper(deviceList, deviceList), fillConfig, writeSheet);writeSheet.setIncludeColumnFieldNames(includeColumnFiledNames);
// excelWriter.fill(new FillWrapper(dataList, dataList), writeSheet);
// }long cost end-start;System.out.println(easyExcel4 cost);} catch (Exception e) {e.printStackTrace();}}public void easyExcelCSV2() {MapString, Object data getData();ListDeviceDto deviceList (ListDeviceDto) data.get(deviceList);ListStressStrainDataDto dataList (ListStressStrainDataDto) data.get(dataList);// 根据用户传入字段 导出数据SetString includeColumnFiledNames new HashSetString();ListListString headList new ArrayListListString();ListString head0 new ArrayListString();head0.add(时间);head0.add(与路表距离);head0.add(结构层位);headList.add(head0);includeColumnFiledNames.add(dateTime);for(int i0; ideviceList.size(); i){includeColumnFiledNames.add(v(i1));ListString head new ArrayListString();head.add(deviceList.get(i).getSn());head.add(deviceList.get(i).getSurfaceDistance().toString());head.add(deviceList.get(i).getStructuralLayer());headList.add(head);}System.out.println(数据行数((List)data.get(dataList)).size());try {long start System.currentTimeMillis();try {EasyExcel.write(G:/test/write/stressStrainTemplateDongHua2.csv, StressStrainDataDto.class).head(headList).includeColumnFieldNames(includeColumnFiledNames).excelType(ExcelTypeEnum.CSV).sheet(sss).doWrite(dataList);} finally {}long end System.currentTimeMillis();long cost end-start;System.out.println(easyExcel2 cost);} catch (Exception e) {e.printStackTrace();}}}
