java easyPOI實現(xiàn)導出一對多數(shù)據(jù)
java easyPOI導出一對多數(shù)據(jù),設(shè)置邊框,字體,字體大小
需求總是千奇百怪,解決的方式也可以是多種多樣。
今天碰到導出excel是一對多結(jié)構(gòu)的,以往導出的數(shù)據(jù)都是一條一條的,所以采用的是比較方便簡單的方法easyExcel,今天猛然碰到一對多導出雖然用easyExcel也可以,但是相對比較麻煩,沒有easyPOI快捷,之前有自己寫過導出excel一個表格一個表格畫,但是太麻煩。今天正好需求不急,就慢慢研究用easypoi導出,寫出通用方法,以后遇到類似的就好辦多了,直接調(diào)方法。廢話不多說,先上效果圖:
首先是實體中設(shè)置:
public class YjwzStockInVO extends TYjwzStockIn implements Serializable { //注意:needMerge = true 必加,不然合并的單元格,邊框不會合并,需要導出的實體加@Excel注解,不需要導出的字段加@ExcelIgnore注解排除 @Excel(needMerge = true,name = "狀態(tài)名稱") @ApiModelProperty(value = "狀態(tài)名稱") private String ztmc; @Excel(needMerge = true,name = "一級品類") @ApiModelProperty(value = "一級物資名稱") private String wzOnemc; @Excel(needMerge = true,name = "二級品類") @ApiModelProperty(value = "二級物資名稱") private String wzTwomc; @Excel(needMerge = true,name = "三級品類") @ApiModelProperty(value = "三級物資名稱") private String wzThreemc; @Excel(needMerge = true,name = "四級品類") @ApiModelProperty(value = "四級物資名稱") private String wzRourmc; @Excel(needMerge = true,name = "數(shù)據(jù)來源") @ApiModelProperty(value = "數(shù)據(jù)來源:1:市應(yīng)急平臺,2:人工新增") @DataBindDict(sourceField = "#sjly", sourceFieldCombination = "sjly") private String sjlymc; @ExcelCollection(name = "應(yīng)急物資明細") @ApiModelProperty(value = "應(yīng)急物資詳情") private List<YjwzStockInDtlVO> dtlList; }
子類:
public class YjwzStockInDtlVO extends TYjwzStockInDtl implements Serializable { @Excel(name = "單據(jù)類型") @ApiModelProperty(value = "單據(jù)類型:1:采購入庫,2:調(diào)撥入庫") @DataBindDict(sourceField = "#djlx", sourceFieldCombination = "lx") private String djlxmc; @Excel(name = "入庫質(zhì)檢") @ApiModelProperty(value = "入庫質(zhì)檢:1:已檢驗入庫,2:未檢驗入庫") @DataBindDict(sourceField = "#rkzj", sourceFieldCombination = "zjlx") private String rkzjmc; @Excel(name = "質(zhì)檢結(jié)果") @ApiModelProperty(value = "質(zhì)檢結(jié)果:1:合格,2:不合格") @DataBindDict(sourceField = "#zjjg", sourceFieldCombination = "zjjg") private String zjjgmc; @Excel(name = "存儲期單位") @ApiModelProperty(value = "存儲期單位:1:年,2:月") @DataBindDict(sourceField = "#ccqdw", sourceFieldCombination = "ccqdw") private String ccqdwmc; }
然后是工具類準備:
1)首先是設(shè)置字體樣式的工具類:
package com.sydata.zt.common.excel; import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity; import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams; import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler; import org.apache.poi.ss.usermodel.*; /** * @Author xx * @Date 2023/12/5 17:37 * @Description: poi導出excel樣式設(shè)置工具 * @Version 1.0 */ public class ExcelStyleUtil implements IExcelExportStyler { private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT"); private static final short FONT_SIZE_TEN = 9; private static final short FONT_SIZE_ELEVEN = 10; private static final short FONT_SIZE_TWELVE = 10; /** * 大標題樣式 */ private CellStyle headerStyle; /** * 每列標題樣式 */ private CellStyle titleStyle; /** * 數(shù)據(jù)行樣式 */ private CellStyle styles; public ExcelStyleUtil(Workbook workbook){ this.init(workbook); } /** * 初始化樣式 * @param workbook */ private void init(Workbook workbook) { this.headerStyle = initHeaderStyle(workbook); this.titleStyle = initTitleStyle(workbook); this.styles = initStyles(workbook); } /** * 初始化大標題樣式 * * @param workbook * @return */ private CellStyle initHeaderStyle(Workbook workbook) { CellStyle style = getBaseCellStyle(workbook); style.setFont(getFont(workbook,FONT_SIZE_ELEVEN,Boolean.TRUE)); return style; } /** * 初始化小標題樣式 * @param workbook * @return */ private CellStyle initTitleStyle(Workbook workbook) { CellStyle style = getBaseCellStyle(workbook); style.setFont(getFont(workbook,FONT_SIZE_ELEVEN,Boolean.TRUE)); return style; } /** * 數(shù)據(jù)行樣式 * @param workbook * @return */ private CellStyle initStyles(Workbook workbook) { CellStyle style = getBaseCellStyle(workbook); style.setDataFormat(STRING_FORMAT); return style; } /** * 大標題樣式 * @param color * @return */ @Override public CellStyle getHeaderStyle(short color) { return headerStyle; } /** * 每列標題樣式 * @param color * @return */ @Override public CellStyle getTitleStyle(short color) { return titleStyle; } /** * 數(shù)據(jù)行樣式 * @param b * @param excelExportEntity * @return */ @Override public CellStyle getStyles(boolean b, ExcelExportEntity excelExportEntity) { return styles; } /** * 獲取行樣式方法 * @param cell * @param i * @param entity * @param o * @param o1 * @return */ @Override public CellStyle getStyles(Cell cell, int i, ExcelExportEntity entity, Object o, Object o1) { return getStyles(true,entity); } @Override public CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) { return null; } /** * 基礎(chǔ)樣式 * @param workbook * @return */ private CellStyle getBaseCellStyle(Workbook workbook) { CellStyle style = workbook.createCellStyle(); //下邊框 style.setBorderBottom(BorderStyle.THIN); //左邊框 style.setBorderLeft(BorderStyle.THIN); //右邊框 style.setBorderRight(BorderStyle.THIN); //上邊框 style.setBorderTop(BorderStyle.THIN); //水平居中 style.setAlignment(HorizontalAlignment.CENTER); //上下居中 style.setVerticalAlignment(VerticalAlignment.CENTER); //設(shè)置自動換行 style.setWrapText(Boolean.TRUE); return style; } /** * 字體樣式 * @param workbook * @param size * @param isBold * @return */ private Font getFont(Workbook workbook,short size,boolean isBold){ Font font = workbook.createFont(); //字體大小 font.setFontHeightInPoints(size); //字體是否加粗 font.setBold(isBold); //設(shè)置字體 // font.setFontName(""); return font; } }
2)然后導出excel工具類
package com.sydata.zt.common.excel; import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import com.alibaba.excel.util.ListUtils; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.formula.functions.T; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.util.CollectionUtils; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.lang.reflect.Method; import java.net.URLEncoder; import java.util.*; /** * @author hm * @date 2023/3/14 17:33 */ public class EasyExcelGeneralUtil { /** * 設(shè)置response編碼 */ public static void setResponseContentType(HttpServletResponse response, String fileName) throws UnsupportedEncodingException { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8") + ".xlsx"); response.setHeader("Access-Control-Expose-Headers", "Content-Disposition"); } public static void exportExcelByEasyPoi(HttpServletResponse response, String fileName, List<Object> vos, Class<?> classType) throws IOException { ExportParams exportParams = new ExportParams(); // 設(shè)置sheet得名稱 exportParams.setSheetName(fileName); //設(shè)置邊框,字體,字體大小 exportParams.setStyle(ExcelStyleUtil.class); Map<String, Object> map = new HashMap<>(); // title的參數(shù)為ExportParams類型,目前僅僅在ExportParams中設(shè)置了sheetName map.put("title", exportParams); // 模版導出對應(yīng)得實體類型的class文件 map.put("entity", classType); // sheet中要填充得數(shù)據(jù) map.put("data", vos); List<Map<String, Object>> sheetsList = new ArrayList<>(); sheetsList.add(map); //創(chuàng)建excel文件的方法 Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF); //通過response輸出流直接輸入給客戶端 ServletOutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } }
準備工作已完成,接下來就可以愉快的導出了:
@SneakyThrows @PostMapping("/export") @ApiOperation(value = "導出") public void export(HttpServletRequest request, HttpServletResponse response,@RequestBody YjwzStockInDTO yjwzStockInDTO){ request.getSession(); String fileName = "文件名稱"; EasyExcelGeneralUtil.setResponseContentType(response,fileName); //設(shè)置的分頁最多能導出10000條數(shù)據(jù) yjwzStockInDTO.setPageNum(1); yjwzStockInDTO.setPageSize(10000); //查庫得到需要導出的數(shù)據(jù) List<Object> vos = (List<Object>) stockInService.page(yjwzStockInDTO).getRows(); EasyExcelGeneralUtil.exportExcelByEasyPoi(response,fileName,vos,YjwzStockInVO.class); }
好了,完整的過程就是這樣了,直接掉接口導出就可以了。
以上就是java easyPOI實現(xiàn)導出一對多數(shù)據(jù)的詳細內(nèi)容,更多關(guān)于java easyPOI導出數(shù)據(jù)的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
Java使用@Retryable注解實現(xiàn)HTTP請求重試
HTTP調(diào)用是Java應(yīng)用與外部API進行交互時重要的訪問方式之一,為了確保在遇到臨時性問題時能自動重試,我們可以設(shè)計一個靈活的重試機制,在Java中,我們可以通過注解來實現(xiàn)這一功能,文將介紹如何使用注解@Retryable來實現(xiàn)HTTP調(diào)用的重試機制,需要的朋友可以參考下2024-10-10spring cloud Feign使用@RequestLine遇到的坑
這篇文章主要介紹了spring cloud Feign使用@RequestLine遇到的坑,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2021-06-06Java存儲過程調(diào)用CallableStatement的方法
這篇文章主要介紹了Java存儲過程調(diào)用CallableStatement的方法,幫助大家更好的理解和學習Java,感興趣的朋友可以了解下2020-11-11MyEclipse 2016 CI 4新增BootStrap模板
MyEclipse2016是一款全球使用最為廣泛的企業(yè)級開發(fā)環(huán)境程序,這篇文章主要介紹了MyEclipse 2016 CI 4新增BootStrap模板的相關(guān)資料,非常不錯,具有參考借鑒價值,需要的朋友可以參考下2016-06-06Java中語音url轉(zhuǎn)換成InputStream的示例代碼
在Java中,可以使用java.net.URL和java.net.URLConnection類來將語音URL轉(zhuǎn)換為InputStream,本文通過示例代碼介紹Java中語音url轉(zhuǎn)換成InputStream的相關(guān)知識,感興趣的朋友一起看看吧2024-01-01