Java實現(xiàn)excel動態(tài)列導(dǎo)出的示例代碼

更新時間：2024年03月05日 16:47:15 作者：warrah

這篇文章主要為大家詳細介紹了如何使用Java實現(xiàn)excel動態(tài)列導(dǎo)出,文中的示例代碼講解詳細,具有一定的借鑒價值,感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下

excel動態(tài)列，只好用poi來寫了，也并不復(fù)雜，一樣就這個件事情抽像為幾步，就是套路了，開發(fā)效率就上去了。

準備空模板

導(dǎo)出操作與excel模板的導(dǎo)出一樣，可以參考excel導(dǎo)出標準化

自定義SheetWriteHandler

要通過pos自己創(chuàng)建每一樣，像模板一樣創(chuàng)建即可.

WriteSheet sheet0 = EasyExcel.writerSheet(0)
                //標題
                .registerWriteHandler(new GoodsInvRdSumWriteHandler(goodsInvRdSumListDto.getHeader()))
                .build();

主要重寫afterSheetCreate,也就是一行行的創(chuàng)建excel模板

 @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        this.centerCellStyle = createCellContentStyle(workbook,HorizontalAlignment.CENTER,BorderStyle.THIN);
        this.leftCellStyle = createCellContentStyle(workbook,HorizontalAlignment.LEFT,BorderStyle.THIN);
        this.rightCellStyle = createCellContentStyle(workbook,HorizontalAlignment.RIGHT,BorderStyle.THIN);
        Sheet sheet = workbook.getSheetAt(0);
        row1(sheet,workbook);
        row2(sheet,workbook);
        row34(sheet);
        row5(sheet);
    }

第一行

  /**
     * 第一行是標題
     * @param sheet
     */
    private void row1(Sheet sheet,Workbook workbook){
        Row row = sheet.createRow(0);
        row.setHeight((short) (50 * 20));
        Cell cell = row.createCell(0);
        cell.setCellValue("商品收發(fā)匯總表");
        cell.setCellStyle(getHeadCellStyle(workbook, this.centerCellStyle));
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 9+this.dynamicHeader.size()*2-1);
        sheet.addMergedRegionUnsafe(cellRangeAddress);
        setMergedRegionStyleNoBorder(sheet, cellRangeAddress);
    }

第二行

/**
     * 第二行 公司名稱、日期
     * @param sheet
     */
    private void row2(Sheet sheet,Workbook workbook){
        Row row = sheet.createRow(1);
        CellStyle subHeaderStyle = createCellContentStyle(workbook, HorizontalAlignment.LEFT,BorderStyle.NONE);
        // 公司名稱
        Cell cell = row.createCell(0);
        cell.setCellStyle(subHeaderStyle);
        cell.setCellValue("公司：{companyName}　　　　　　　　                       日期：{startBillDate}至{endBillDate}");
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 9+this.dynamicHeader.size()*2-1));

???????    }

第三行,第四行涉及到動態(tài)列的創(chuàng)建和合并表頭

 private void row34(Sheet sheet){
        Row row3 = sheet.createRow(2);
        Row row4 = sheet.createRow(3);
        // 商品編碼
        Cell cell = row3.createCell(0);
        cell.setCellValue("商品編碼");
        cell.setCellStyle(this.centerCellStyle);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(2, 3, 0, 0);
        sheet.addMergedRegionUnsafe(cellRangeAddress);
        setMergedRegionStyle(sheet, cellRangeAddress);
        // 商品名稱
        cell = row3.createCell(1);
        cell.setCellValue("商品名稱");
        cell.setCellStyle(this.centerCellStyle);
        cellRangeAddress = new CellRangeAddress(2, 3, 1, 1);
        sheet.addMergedRegionUnsafe(cellRangeAddress);
        setMergedRegionStyle(sheet, cellRangeAddress);
        // 商品規(guī)格
        cell = row3.createCell(2);
        cell.setCellValue("商品規(guī)格");
        cell.setCellStyle(this.centerCellStyle);
        cellRangeAddress = new CellRangeAddress(2, 3, 2, 2);
        sheet.addMergedRegionUnsafe(cellRangeAddress);
        setMergedRegionStyle(sheet, cellRangeAddress);
        //動態(tài)列
        int dySize = this.dynamicHeader.size();
        if (dySize>0){
            for (int i=0; i<dySize; i++){
                Map<String,Object> colMap = this.dynamicHeader.get(i);
                String busiType = String.valueOf(colMap.get("prop")).replace("busi_", BaseConstant.Separate.NONE);
                BusinessTypeEnum businessTypeEnum = BusinessTypeEnum.getInvBusinessTypeEnum(busiType);
                // 第3行——合并表頭
                cell = row3.createCell(3+i*2);
                cell.setCellValue(businessTypeEnum.display());
                cell.setCellStyle(this.centerCellStyle);
                cellRangeAddress = new CellRangeAddress(2, 2, 3+i*2, 4+i*2);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
                setMergedRegionStyle(sheet, cellRangeAddress);
                // 第4行——成本
                cell = row4.createCell(3+i*2);
                cell.setCellStyle(this.centerCellStyle);
                cell.setCellValue("數(shù)量");
                // 第4行——數(shù)量
                cell = row4.createCell(4+i*2);
                cell.setCellStyle(this.centerCellStyle);
                cell.setCellValue("成本");

            }
        }
        // 入庫合計
        cell = row3.createCell(3+dySize*2);
        cell.setCellValue("入庫合計");
        cell.setCellStyle(this.centerCellStyle);
        cellRangeAddress = new CellRangeAddress(2, 2, 3+dySize*2, 4+dySize*2);
        sheet.addMergedRegionUnsafe(cellRangeAddress);
        setMergedRegionStyle(sheet, cellRangeAddress);
        // 入庫合計——成本
        cell = row4.createCell(3+dySize*2);
        cell.setCellStyle(this.centerCellStyle);
        cell.setCellValue("數(shù)量");
        // 入庫合計——數(shù)量
        cell = row4.createCell(4+dySize*2);
        cell.setCellStyle(this.centerCellStyle);
        cell.setCellValue("成本");
        // 出庫合計
        cell = row3.createCell(5+dySize*2);
        cell.setCellValue("出庫合計");
        cell.setCellStyle(this.centerCellStyle);
        cellRangeAddress = new CellRangeAddress(2, 2, 5+dySize*2, 6+dySize*2);
        sheet.addMergedRegionUnsafe(cellRangeAddress);
        setMergedRegionStyle(sheet, cellRangeAddress);
        // 出庫合計——成本
        cell = row4.createCell(5+dySize*2);
        cell.setCellStyle(this.centerCellStyle);
        cell.setCellValue("數(shù)量");
        // 出庫合計——數(shù)量
        cell = row4.createCell(6+dySize*2);
        cell.setCellStyle(this.centerCellStyle);
        cell.setCellValue("成本");
        // 結(jié)余
        cell = row3.createCell(7+dySize*2);
        cell.setCellValue("結(jié)余");
        cell.setCellStyle(this.centerCellStyle);
        cellRangeAddress = new CellRangeAddress(2, 2, 7+dySize*2, 8+dySize*2);
        sheet.addMergedRegionUnsafe(cellRangeAddress);
        setMergedRegionStyle(sheet, cellRangeAddress);
        // 結(jié)余——成本
        cell = row4.createCell(7+dySize*2);
        cell.setCellStyle(this.centerCellStyle);
        cell.setCellValue("數(shù)量");
        // 結(jié)余——數(shù)量
        cell = row4.createCell(8+dySize*2);
        cell.setCellStyle(this.centerCellStyle);
        cell.setCellValue("成本");
    }

第五行是數(shù)據(jù)域

/**
     * 第五行：數(shù)據(jù)域
     * @param sheet
     */
    private void row5(Sheet sheet){
        Row row = sheet.createRow(4);
        // 商品編碼
        Cell cell = row.createCell(0);
        cell.setCellStyle(this.leftCellStyle);
        cell.setCellValue("{.stockCode}");
        // 商品名稱
        cell = row.createCell(1);
        cell.setCellStyle(this.leftCellStyle);
        cell.setCellValue("{.stockName}");
        // 商品規(guī)格
        cell = row.createCell(2);
        cell.setCellStyle(this.leftCellStyle);
        cell.setCellValue("{.stockModel}");
        // 動態(tài)列
        int dySize = this.dynamicHeader.size();
        if (!CheckEmptyUtil.isEmpty(this.dynamicHeader)){
            for (int i=0; i<dySize; i++){
                Map<String,Object> colMap = this.dynamicHeader.get(i);
                List<Map<String,String>> chidren = (List<Map<String,String>>)colMap.get("children");
                // 數(shù)量
                Map<String,String> countMap = chidren.get(0);
                cell = row.createCell(3+i*2);
                cell.setCellStyle(this.rightCellStyle);
                cell.setCellValue(String.format("{.%s}", countMap.get("prop")));
                // 成本
                Map<String,String> costMap = chidren.get(1);
                cell = row.createCell(4+i*2);
                cell.setCellStyle(this.rightCellStyle);
                cell.setCellValue(String.format("{.%s}", costMap.get("prop")));
            }
        }
        // 入庫合計
        cell = row.createCell(3+dySize*2);
        cell.setCellStyle(this.rightCellStyle);
        cell.setCellValue("{.count_total_in}");
        cell = row.createCell(4+dySize*2);
        cell.setCellStyle(this.rightCellStyle);
        cell.setCellValue("{.cost_total_in}");
        // 出庫合計
        cell = row.createCell(5+dySize*2);
        cell.setCellStyle(this.rightCellStyle);
        cell.setCellValue("{.count_total_out}");
        cell = row.createCell(6+dySize*2);
        cell.setCellStyle(this.rightCellStyle);
        cell.setCellValue("{.cost_total_out}");
        // 結(jié)余
        cell = row.createCell(7+dySize*2);
        cell.setCellStyle(this.rightCellStyle);
        cell.setCellValue("{.final_count}");
        cell = row.createCell(8+dySize*2);
        cell.setCellStyle(this.rightCellStyle);
        cell.setCellValue("{.final_cost}");
    }

表格樣式這里只寫一個,其他的參考pos文檔即可,不要每一個單元都重新創(chuàng)建單元格樣式,那樣非常消耗性能.

 private CellStyle createCellContentStyle(Workbook workbook, HorizontalAlignment align,BorderStyle borderStyle) {
        CellStyle style = workbook.createCellStyle();
        // 設(shè)置對齊樣式
        style.setAlignment(align);
        //背景為白色
        style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 設(shè)置邊框樣式
        // 下邊框
        style.setBorderBottom(borderStyle);
        // 左邊框
        style.setBorderLeft(borderStyle);
        // 上邊框
        style.setBorderTop(borderStyle);
        // 右邊框
        style.setBorderRight(borderStyle);
        // 生成字體
        Font font = workbook.createFont();
        font.setFontName("宋體");
        // 設(shè)置字體大小
        font.setFontHeightInPoints((short) 10);
        // 粗體顯示
        font.setBold(false);
        // 選擇創(chuàng)建的字體格式
        style.setFont(font);
        return style;
    }

到此這篇關(guān)于Java實現(xiàn)excel動態(tài)列導(dǎo)出的示例代碼的文章就介紹到這了,更多相關(guān)Java excel動態(tài)列導(dǎo)出內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: