據(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ù)據(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(沒有這個注解); } //獲取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); //一個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; /** * 對list數(shù)據(jù)源將其里面的數(shù)據(jù)導(dǎo)入到excel表單 * * param response 返回數(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)外否則會刷新流 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)計即可:一共多少條 //每一個Sheet存放100w條數(shù)據(jù) Integer sheetDataRows PER_SHEET_ROW_COUNT; //每次寫入的數(shù)據(jù)量20w,每頁查詢20W Integer writeDataRows PER_WRITE_ROW_COUNT; //計算需要的Sheet數(shù)量 Integer sheetNum totalCount % sheetDataRows 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows 1); //計算一般情況下每一個Sheet需要寫入的次數(shù)(一般情況不包含最后一個sheet,因?yàn)樽詈笠粋€sheet不確定會寫入多少條數(shù)據(jù)) Integer oneSheetWriteCount sheetDataRows / writeDataRows; //計算最后一個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)不是最后一個Sheet的時候?qū)懭氪螖?shù)為正常的每個Sheet寫入的次數(shù),如果是最后一個就需要使用計算的次數(shù)lastSheetWriteCount for (int j 0; j (i ! sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j) { //數(shù)據(jù)分頁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.問題實(shí)體類中使用的字典個數(shù)多可能會報錯8.參考鏈接使用EasyExcel實(shí)現(xiàn)excel導(dǎo)出支持百萬大數(shù)據(jù)量導(dǎo)出-----超簡單_easyexcel導(dǎo)出大量數(shù)據(jù)-CSDN博客Java中easyExcel框架字段轉(zhuǎn)換具體實(shí)現(xiàn)根據(jù)數(shù)據(jù)字典轉(zhuǎn)換對注解ExcelProperty中converter的具體使用_java_weixin_54566947-開放原子開發(fā)者工作坊java中把查詢出來的結(jié)果進(jìn)行分頁_java 對于查詢兩次的結(jié)果進(jìn)行合并分頁-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ù)據(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(沒有這個注解); } //獲取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); //一個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; /** * 對list數(shù)據(jù)源將其里面的數(shù)據(jù)導(dǎo)入到excel表單 * * param response 返回數(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)外否則會刷新流 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)計即可:一共多少條 //每一個Sheet存放100w條數(shù)據(jù) Integer sheetDataRows PER_SHEET_ROW_COUNT; //每次寫入的數(shù)據(jù)量20w,每頁查詢20W Integer writeDataRows PER_WRITE_ROW_COUNT; //計算需要的Sheet數(shù)量 Integer sheetNum totalCount % sheetDataRows 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows 1); //計算一般情況下每一個Sheet需要寫入的次數(shù)(一般情況不包含最后一個sheet,因?yàn)樽詈笠粋€sheet不確定會寫入多少條數(shù)據(jù)) Integer oneSheetWriteCount sheetDataRows / writeDataRows; //計算最后一個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)不是最后一個Sheet的時候?qū)懭氪螖?shù)為正常的每個Sheet寫入的次數(shù),如果是最后一個就需要使用計算的次數(shù)lastSheetWriteCount for (int j 0; j (i ! sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j) { //數(shù)據(jù)分頁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.問題實(shí)體類中使用的字典個數(shù)多可能會報錯8.參考鏈接使用EasyExcel實(shí)現(xiàn)excel導(dǎo)出支持百萬大數(shù)據(jù)量導(dǎo)出-----超簡單_easyexcel導(dǎo)出大量數(shù)據(jù)-CSDN博客Java中easyExcel框架字段轉(zhuǎn)換具體實(shí)現(xiàn)根據(jù)數(shù)據(jù)字典轉(zhuǎn)換對注解ExcelProperty中converter的具體使用_java_weixin_54566947-開放原子開發(fā)者工作坊java中把查詢出來的結(jié)果進(jìn)行分頁_java 對于查詢兩次的結(jié)果進(jìn)行合并分頁-CSDN博客