據(jù)導(dǎo)出)
1.pom文件dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.0.5/version /dependency2.自定義字典注解import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * 數(shù)據(jù)字段類型識(shí)別注解 * 需要傳入數(shù)據(jù)字典的type */ Target(ElementType.FIELD) Retention(RetentionPolicy.RUNTIME) public interface DictTypeProperty { // 傳入的type接收 String value() default ; }3.數(shù)據(jù)轉(zhuǎn)化import com.alibaba.excel.converters.Converter; import com.alibaba.excel.metadata.GlobalConfiguration; import com.alibaba.excel.metadata.data.WriteCellData; import com.alibaba.excel.metadata.property.ExcelContentProperty; import java.lang.reflect.Field; public class DictTypeConvert implements ConverterString { Override public Class? supportJavaTypeKey() { return String.class; } Override public WriteCellData? convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { //獲取當(dāng)前字段 Field field contentProperty.getField(); //獲取字段上的注解 DictTypeProperty declaredAnnotation field.getDeclaredAnnotation(DictTypeProperty.class); if (BlankUtil.isBlank(declaredAnnotation)) { throw new Exception(沒(méi)有這個(gè)注解); } //獲取DictTypeProperty的value String dictValue declaredAnnotation.value(); String label DictUtils.getDictLabel(dictValue, value);//若依dict工具 //將結(jié)果覆蓋到字段上 return new WriteCellData(label); } }4.實(shí)體類import com.alibaba.excel.annotation.ExcelIgnoreUnannotated; import com.alibaba.excel.annotation.ExcelProperty; /** * author author * since 2024-11-12 */ ExcelIgnoreUnannotated public class TestPO extends BaseEntity { private static final long serialVersionUID 1L; ExcelProperty(value 狀態(tài),converter DictTypeConvert.class) DictTypeProperty(value status_type) private String status;//Integer類型轉(zhuǎn)義后為String類型 }5.工具類import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; Service public class ExportUtilT { private static final Logger log LoggerFactory.getLogger(ExportUtil.class); //一個(gè)sheet裝100w數(shù)據(jù) public static final Integer PER_SHEET_ROW_COUNT 1000000; //每次查詢20w數(shù)據(jù)每次寫入20w數(shù)據(jù) public static final Integer PER_WRITE_ROW_COUNT 200000; /** * 對(duì)list數(shù)據(jù)源將其里面的數(shù)據(jù)導(dǎo)入到excel表單 * * param response 返回?cái)?shù)據(jù) * param list 導(dǎo)出數(shù)據(jù)集合 * param totalCount 集合總條數(shù) * param sheetName 工作表的名稱 * param clazz 導(dǎo)出實(shí)體類 * return 結(jié)果 */ public void exportExcel(HttpServletResponse response, ListT list,int totalCount, String sheetName,ClassT clazz) throws Exception{ String fileName String.valueOf(System.currentTimeMillis()); OutputStream outputStream null; try { //必須放到循環(huán)外否則會(huì)刷新流 outputStream response.getOutputStream(); ExcelWriter excelWriter EasyExcel.write(outputStream).build(); ListListT returnList exportSurfList(list,totalCount); int i 0; for (ListT items : returnList) { i; //不做設(shè)置全部導(dǎo)出 WriteSheet writeSheet EasyExcel.writerSheet(i, sheetName i).head(clazz) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build(); excelWriter.write(items, writeSheet); } // 下載EXCEL返回給前端stream流 response.setContentType(application/octet-stream); response.setCharacterEncoding(utf-8); response.setHeader(Content-disposition, attachment;filename*utf-8 fileName .xlsx); excelWriter.finish(); outputStream.flush(); outputStream.close(); log.info(*********列表導(dǎo)出結(jié)束**************); } catch (Exception e) { e.printStackTrace(); } finally { if (outputStream ! null) { outputStream.close(); } } } public ListListT exportSurfList(ListT list,int totalCount) throws Exception{ ListListT returnList new ArrayList(); //記錄總數(shù):實(shí)際中需要根據(jù)查詢條件進(jìn)行統(tǒng)計(jì)即可:一共多少條 //每一個(gè)Sheet存放100w條數(shù)據(jù) Integer sheetDataRows PER_SHEET_ROW_COUNT; //每次寫入的數(shù)據(jù)量20w,每頁(yè)查詢20W Integer writeDataRows PER_WRITE_ROW_COUNT; //計(jì)算需要的Sheet數(shù)量 Integer sheetNum totalCount % sheetDataRows 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows 1); //計(jì)算一般情況下每一個(gè)Sheet需要寫入的次數(shù)(一般情況不包含最后一個(gè)sheet,因?yàn)樽詈笠粋€(gè)sheet不確定會(huì)寫入多少條數(shù)據(jù)) Integer oneSheetWriteCount sheetDataRows / writeDataRows; //計(jì)算最后一個(gè)sheet需要寫入的次數(shù) Integer lastSheetWriteCount totalCount % sheetDataRows 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount % sheetDataRows / writeDataRows 1)); //開始分批查詢分次寫入 //注意這次的循環(huán)就需要進(jìn)行嵌套循環(huán)了,外層循環(huán)是Sheet數(shù)目,內(nèi)層循環(huán)是寫入次數(shù) for (int i 0; i sheetNum; i) { //循環(huán)寫入次數(shù): j的自增條件是當(dāng)不是最后一個(gè)Sheet的時(shí)候?qū)懭氪螖?shù)為正常的每個(gè)Sheet寫入的次數(shù),如果是最后一個(gè)就需要使用計(jì)算的次數(shù)lastSheetWriteCount for (int j 0; j (i ! sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j) { //數(shù)據(jù)分頁(yè)writeDataRows //開始分批查詢分次寫入 ListT newList listSplit(list,j 1 oneSheetWriteCount * i, writeDataRows); returnList.add(newList); } } return returnList; } public ListT listSplit(ListT list, int pageNum, int pageSize) { ListT newListnull; int totallist.size(); newListlist.subList(pageSize*(pageNum-1), ((pageSize*pageNum)total?total:(pageSize*pageNum))); return newList; } }6.使用示例public void export(HttpServletResponse response, TestDTO testDTO) throws Exception { ListTestPO list mapper.getList(testDTO); exportUtil.exportExcel(response,list,list.size(),導(dǎo)出結(jié)果,TestPO.class); }7.問(wèn)題實(shí)體類中使用的字典個(gè)數(shù)多可能會(huì)報(bào)錯(cuò)8.參考鏈接使用EasyExcel實(shí)現(xiàn)excel導(dǎo)出支持百萬(wàn)大數(shù)據(jù)量導(dǎo)出-----超簡(jiǎn)單_easyexcel導(dǎo)出大量數(shù)據(jù)-CSDN博客Java中easyExcel框架字段轉(zhuǎn)換具體實(shí)現(xiàn)根據(jù)數(shù)據(jù)字典轉(zhuǎn)換對(duì)注解ExcelProperty中converter的具體使用_java_weixin_54566947-開放原子開發(fā)者工作坊java中把查詢出來(lái)的結(jié)果進(jìn)行分頁(yè)_java 對(duì)于查詢兩次的結(jié)果進(jìn)行合并分頁(yè)-CSDN博客 ExportUtil——若依集成easyExcel大數(shù)據(jù)導(dǎo)出
1.pom文件dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.0.5/version /dependency2.自定義字典注解import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * 數(shù)據(jù)字段類型識(shí)別注解 * 需要傳入數(shù)據(jù)字典的type */ Target(ElementType.FIELD) Retention(RetentionPolicy.RUNTIME) public interface DictTypeProperty { // 傳入的type接收 String value() default ; }3.數(shù)據(jù)轉(zhuǎn)化import com.alibaba.excel.converters.Converter; import com.alibaba.excel.metadata.GlobalConfiguration; import com.alibaba.excel.metadata.data.WriteCellData; import com.alibaba.excel.metadata.property.ExcelContentProperty; import java.lang.reflect.Field; public class DictTypeConvert implements ConverterString { Override public Class? supportJavaTypeKey() { return String.class; } Override public WriteCellData? convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { //獲取當(dāng)前字段 Field field contentProperty.getField(); //獲取字段上的注解 DictTypeProperty declaredAnnotation field.getDeclaredAnnotation(DictTypeProperty.class); if (BlankUtil.isBlank(declaredAnnotation)) { throw new Exception(沒(méi)有這個(gè)注解); } //獲取DictTypeProperty的value String dictValue declaredAnnotation.value(); String label DictUtils.getDictLabel(dictValue, value);//若依dict工具 //將結(jié)果覆蓋到字段上 return new WriteCellData(label); } }4.實(shí)體類import com.alibaba.excel.annotation.ExcelIgnoreUnannotated; import com.alibaba.excel.annotation.ExcelProperty; /** * author author * since 2024-11-12 */ ExcelIgnoreUnannotated public class TestPO extends BaseEntity { private static final long serialVersionUID 1L; ExcelProperty(value 狀態(tài),converter DictTypeConvert.class) DictTypeProperty(value status_type) private String status;//Integer類型轉(zhuǎn)義后為String類型 }5.工具類import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; Service public class ExportUtilT { private static final Logger log LoggerFactory.getLogger(ExportUtil.class); //一個(gè)sheet裝100w數(shù)據(jù) public static final Integer PER_SHEET_ROW_COUNT 1000000; //每次查詢20w數(shù)據(jù)每次寫入20w數(shù)據(jù) public static final Integer PER_WRITE_ROW_COUNT 200000; /** * 對(duì)list數(shù)據(jù)源將其里面的數(shù)據(jù)導(dǎo)入到excel表單 * * param response 返回?cái)?shù)據(jù) * param list 導(dǎo)出數(shù)據(jù)集合 * param totalCount 集合總條數(shù) * param sheetName 工作表的名稱 * param clazz 導(dǎo)出實(shí)體類 * return 結(jié)果 */ public void exportExcel(HttpServletResponse response, ListT list,int totalCount, String sheetName,ClassT clazz) throws Exception{ String fileName String.valueOf(System.currentTimeMillis()); OutputStream outputStream null; try { //必須放到循環(huán)外否則會(huì)刷新流 outputStream response.getOutputStream(); ExcelWriter excelWriter EasyExcel.write(outputStream).build(); ListListT returnList exportSurfList(list,totalCount); int i 0; for (ListT items : returnList) { i; //不做設(shè)置全部導(dǎo)出 WriteSheet writeSheet EasyExcel.writerSheet(i, sheetName i).head(clazz) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build(); excelWriter.write(items, writeSheet); } // 下載EXCEL返回給前端stream流 response.setContentType(application/octet-stream); response.setCharacterEncoding(utf-8); response.setHeader(Content-disposition, attachment;filename*utf-8 fileName .xlsx); excelWriter.finish(); outputStream.flush(); outputStream.close(); log.info(*********列表導(dǎo)出結(jié)束**************); } catch (Exception e) { e.printStackTrace(); } finally { if (outputStream ! null) { outputStream.close(); } } } public ListListT exportSurfList(ListT list,int totalCount) throws Exception{ ListListT returnList new ArrayList(); //記錄總數(shù):實(shí)際中需要根據(jù)查詢條件進(jìn)行統(tǒng)計(jì)即可:一共多少條 //每一個(gè)Sheet存放100w條數(shù)據(jù) Integer sheetDataRows PER_SHEET_ROW_COUNT; //每次寫入的數(shù)據(jù)量20w,每頁(yè)查詢20W Integer writeDataRows PER_WRITE_ROW_COUNT; //計(jì)算需要的Sheet數(shù)量 Integer sheetNum totalCount % sheetDataRows 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows 1); //計(jì)算一般情況下每一個(gè)Sheet需要寫入的次數(shù)(一般情況不包含最后一個(gè)sheet,因?yàn)樽詈笠粋€(gè)sheet不確定會(huì)寫入多少條數(shù)據(jù)) Integer oneSheetWriteCount sheetDataRows / writeDataRows; //計(jì)算最后一個(gè)sheet需要寫入的次數(shù) Integer lastSheetWriteCount totalCount % sheetDataRows 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount % sheetDataRows / writeDataRows 1)); //開始分批查詢分次寫入 //注意這次的循環(huán)就需要進(jìn)行嵌套循環(huán)了,外層循環(huán)是Sheet數(shù)目,內(nèi)層循環(huán)是寫入次數(shù) for (int i 0; i sheetNum; i) { //循環(huán)寫入次數(shù): j的自增條件是當(dāng)不是最后一個(gè)Sheet的時(shí)候?qū)懭氪螖?shù)為正常的每個(gè)Sheet寫入的次數(shù),如果是最后一個(gè)就需要使用計(jì)算的次數(shù)lastSheetWriteCount for (int j 0; j (i ! sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j) { //數(shù)據(jù)分頁(yè)writeDataRows //開始分批查詢分次寫入 ListT newList listSplit(list,j 1 oneSheetWriteCount * i, writeDataRows); returnList.add(newList); } } return returnList; } public ListT listSplit(ListT list, int pageNum, int pageSize) { ListT newListnull; int totallist.size(); newListlist.subList(pageSize*(pageNum-1), ((pageSize*pageNum)total?total:(pageSize*pageNum))); return newList; } }6.使用示例public void export(HttpServletResponse response, TestDTO testDTO) throws Exception { ListTestPO list mapper.getList(testDTO); exportUtil.exportExcel(response,list,list.size(),導(dǎo)出結(jié)果,TestPO.class); }7.問(wèn)題實(shí)體類中使用的字典個(gè)數(shù)多可能會(huì)報(bào)錯(cuò)8.參考鏈接使用EasyExcel實(shí)現(xiàn)excel導(dǎo)出支持百萬(wàn)大數(shù)據(jù)量導(dǎo)出-----超簡(jiǎn)單_easyexcel導(dǎo)出大量數(shù)據(jù)-CSDN博客Java中easyExcel框架字段轉(zhuǎn)換具體實(shí)現(xiàn)根據(jù)數(shù)據(jù)字典轉(zhuǎn)換對(duì)注解ExcelProperty中converter的具體使用_java_weixin_54566947-開放原子開發(fā)者工作坊java中把查詢出來(lái)的結(jié)果進(jìn)行分頁(yè)_java 對(duì)于查詢兩次的結(jié)果進(jìn)行合并分頁(yè)-CSDN博客