poi操作excel，複製sheet，複製行，複製單元格，複製style

李觀華發表於2019-01-19

Excel

緣起

最近寫一個程式，需要操作excel，遂稍微深入瞭解下poi從操作，

在需要複製一個sheet的時候，找到三份資料

問題

如果在copy一個cell的時候，需要把樣式也copy過去，怎麼辦，會怎樣呢？

如果像第三份資料中所做，會建立太多的style,而這些style又是重複的，毫無意義，資料多了還報錯

如果像第二份資料中所做，前提是同一個workbook，也不存在copy style 的問題，只不過是不同的cell引用相同的style而已

如果像第一份資料所做，他自己都註釋掉了，因為報錯

如何解決

思路

相同的book中，不需要copy
不同的book中，styleTable應該不一致，copy的時候，在目標book中建立，最後返回兩個book中的style index的對映關係

程式碼

暫時沒測試過，寫出來就迫不及待要分享了。

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 提供常用excel操作<br>
 *     <ul>
 *         <li></li>
 *     </ul>
 */
public class ExcelOperationUtil {
    private static Logger logger = LoggerFactory.getLogger(ExcelOperationUtil.class);
    
    /**
     * 把一個excel中的styleTable複製到另一個excel中
     * @return StyleMapping 兩個檔案中styleTable的對映關係
     * @see StyleMapping
     */
    public static StyleMapping copyCellStyle(Workbook srcBook, Workbook desBook){
        if (null == srcBook || null == desBook) {
            throw new ExcelExecption("源excel 或 目標excel 不存在");
        }
        if (srcBook.equals(desBook)) {
            throw new ExcelExecption("不要使用此方法在同一個檔案中copy style");
        }

        logger.debug("src中style number:{}, des中style number:{}", srcBook.getNumCellStyles(), desBook.getNumCellStyles());
        short[] src2des = new short[srcBook.getNumCellStyles()];
        short[] des2src = new short[desBook.getNumCellStyles() + srcBook.getNumCellStyles()];

        for(short i=0;i<srcBook.getNumCellStyles();i++){
            //建立雙向對映
            CellStyle srcStyle = srcBook.getCellStyleAt(i);
            CellStyle desStyle = desBook.createCellStyle();
            src2des[srcStyle.getIndex()] = desStyle.getIndex();
            des2src[desStyle.getIndex()] = srcStyle.getIndex();

            //複製樣式
            desStyle.cloneStyleFrom(srcStyle);
        }

        return new StyleMapping(des2src, src2des);
    }

    /**
     * 存放兩個excel檔案中的styleTable的對映關係，以便於在複製表格時，在目標檔案中獲取到對應的樣式
     */
    public static class StyleMapping {
        /**
         *
         */
        private short[] des2srcIndexMapping;
        /**
         *
         */
        private short[] src2desIndexMapping;

        /**
         * 不允許其他類建立此型別物件
         */
        private StyleMapping() {
        }

        private StyleMapping(short[] des2srcIndexMapping, short[] src2desIndexMapping) {
            this.des2srcIndexMapping = des2srcIndexMapping;
            this.src2desIndexMapping = src2desIndexMapping;
        }

        public short srcIndex(short desIndex) {
            if (desIndex < 0 || desIndex >= this.des2srcIndexMapping.length) {
                throw new ExcelExecption("索引越界：原始檔styleNum=" + this.des2srcIndexMapping.length + " 訪問位置=" + desIndex);
            }
            return this.des2srcIndexMapping[desIndex];
        }

        /**
         * 根據原始檔的style的index,獲取目標檔案的style的index
         * @param srcIndex 源excel中style的index
         * @return desIndex 目標excel中style的index
         */
        public short desIndex(short srcIndex) {
            if (srcIndex < 0 || srcIndex >= this.src2desIndexMapping.length) {
                throw new ExcelExecption("索引越界：原始檔styleNum=" + this.src2desIndexMapping.length + " 訪問位置=" + srcIndex);
            }

            return this.src2desIndexMapping[srcIndex];
        }
    }

}

完善程式碼

工作忙了幾天，終於可以回頭把這件事結束掉了

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>

為啥貼出依賴？因為我一開始用3.8版本，如果是xls就可以，如果是xlsx死活不行，最後，換掉版本就可以了。

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.Iterator;

/**
 * 提供常用excel操作<br>
 *     <ul>
 *         <li></li>
 *     </ul>
 */
public class ExcelOperationUtil {
    private static Logger logger = LoggerFactory.getLogger(ExcelOperationUtil.class);

    /**
     * sheet 複製，複製資料、如果同一個檔案，複製樣式，不同檔案則只複製資料<br/>
     * 如果是同book中複製，建議使用workbook中的cloneSheet()方法<br/>
     *
     * <br/>建議用於 不同book間只複製資料
     *
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet) {
        copySheet(srcSheet, desSheet, true, true, null);
    }

    /**
     * sheet 複製，如果同一個檔案，複製樣式，不同檔案則不復制<br/>
     *
     * <br/>建議用於 同book中，只複製樣式，不復制資料<br/>
     * eg: copySheet(srcSheet, desSheet, false)
     *
     * @param copyValueFlag 控制是否複製資料
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, boolean copyValueFlag) {
        copySheet(srcSheet, desSheet, copyValueFlag, true, null);
    }

    /**
     * sheet 複製，複製資料、樣式<br/>
     *
     * <br/>建議用於 不同book間複製，同時複製資料和樣式<br/>
     * eg: copySheet(srcSheet, desSheet, mapping)
     *
     * @param mapping 不同檔案間複製時，如果要複製樣式，必傳，否則不復制樣式
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, StyleMapping mapping) {
        copySheet(srcSheet, desSheet, true, true, mapping);
    }

    /**
     * sheet 複製,複製資料<br/>
     *
     *  <br/>建議用於 同book中，只複製資料，不復制樣式<br/>
     *  eg: copySheet(srcSheet, desSheet, false, null)
     *
     * @param srcSheet
     * @param desSheet
     * @param copyStyleFlag
     * @param mapping
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, boolean copyStyleFlag, StyleMapping mapping) {
        copySheet(srcSheet, desSheet, true, copyStyleFlag, mapping);
    }

    /**
     * sheet 複製, 靈活控制是否控制資料、樣式<br/>
     *
     * <br/>不建議直接使用
     *
     * @param copyValueFlag 控制是否複製資料
     * @param copyStyleFlag 控制是否複製樣式
     * @param mapping       不同book中複製樣式時，必傳
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, boolean copyValueFlag, boolean copyStyleFlag, StyleMapping mapping) {
        if (srcSheet.getWorkbook() == desSheet.getWorkbook()) {
            logger.warn("統一workbook內複製sheet建議使用 workbook的cloneSheet方法");
        }

        //合併區域處理
        copyMergedRegion(srcSheet, desSheet);

        //行復制
        Iterator<Row> rowIterator = srcSheet.rowIterator();

        int areadlyColunm = 0;
        while (rowIterator.hasNext()) {
            Row srcRow = rowIterator.next();
            Row desRow = desSheet.createRow(srcRow.getRowNum());
            copyRow(srcRow, desRow, copyValueFlag, copyStyleFlag, mapping);

            //調整列寬(增量調整)
            if (srcRow.getPhysicalNumberOfCells() > areadlyColunm) {
                for (int i = areadlyColunm; i < srcRow.getPhysicalNumberOfCells(); i++) {
                    desSheet.setColumnWidth(i, srcSheet.getColumnWidth(i));
                }
                areadlyColunm = srcRow.getPhysicalNumberOfCells();
            }
        }
    }

    /**
     * 複製行
     */
    public static void copyRow(Row srcRow, Row desRow) {
        copyRow(srcRow, desRow, true, true, null);
    }

    /**
     * 複製行
     */
    public static void copyRow(Row srcRow, Row desRow, boolean copyValueFlag) {
        copyRow(srcRow, desRow, copyValueFlag, true, null);
    }

    /**
     * 複製行
     */
    public static void copyRow(Row srcRow, Row desRow, StyleMapping mapping) {
        copyRow(srcRow, desRow, true, true, mapping);
    }

    /**
     * 複製行
     */
    public static void copyRow(Row srcRow, Row desRow, boolean copyStyleFlag, StyleMapping mapping) {
        copyRow(srcRow, desRow, true, copyStyleFlag, mapping);
    }

    /**
     * 複製行
     */
    public static void copyRow(Row srcRow, Row desRow,boolean copyValueFlag, boolean copyStyleFlag, StyleMapping mapping) {
        Iterator<Cell> it = srcRow.cellIterator();
        while (it.hasNext()) {
            Cell srcCell = it.next();
            Cell desCell = desRow.createCell(srcCell.getColumnIndex());
            copyCell(srcCell, desCell, copyValueFlag, copyStyleFlag, mapping);
        }
    }

    /**
     * 複製區域（合併單元格）
     */
    public static void copyMergedRegion(Sheet srcSheet, Sheet desSheet) {
        int sheetMergerCount = srcSheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            desSheet.addMergedRegion(srcSheet.getMergedRegion(i));
            CellRangeAddress cellRangeAddress = srcSheet.getMergedRegion(i);
        }
    }

    /**
     * 複製單元格，複製資料，如果同檔案，複製樣式，不同檔案則不復制樣式
     */
    public static void copyCell(Cell srcCell, Cell desCell) {
        copyCell(srcCell, desCell, true, true,null);
    }

    /**
     * 複製單元格， 如果同檔案，複製樣式，不同檔案則不復制樣式
     * @param copyValueFlag 控制是否複製資料
     */
    public static void copyCell(Cell srcCell, Cell desCell, boolean copyValueFlag) {
        copyCell(srcCell, desCell, copyValueFlag, true, null);
    }

    /**
     * 複製單元格，複製資料,複製樣式
     * @param mapping       不同檔案間複製時，如果要複製樣式，必傳，否則不復制樣式
     */
    public static void copyCell(Cell srcCell, Cell desCell,  StyleMapping mapping) {
        copyCell(srcCell, desCell, true, true, mapping);
    }

    /**
     * 複製單元格，複製資料
     * @param copyStyleFlag 控制是否複製樣式
     * @param mapping       不同檔案間複製時，如果要複製樣式，必傳，否則不復制樣式
     */
    public static void copyCell(Cell srcCell, Cell desCell, boolean copyStyleFlag, StyleMapping mapping) {
        copyCell(srcCell, desCell, true, copyStyleFlag, mapping);
    }

    /**
     * 複製單元格
     * @param copyValueFlag 控制是否複製單元格的內容
     * @param copyStyleFlag 控制是否複製樣式
     * @param mapping 不同檔案間複製時，如果需要連帶樣式複製，必傳，否則不復制樣式
     */
    public static void copyCell(Cell srcCell, Cell desCell, boolean copyValueFlag, boolean copyStyleFlag, StyleMapping mapping) {
        Workbook srcBook = srcCell.getSheet().getWorkbook();
        Workbook desBook = desCell.getSheet().getWorkbook();

        //複製樣式
        //如果是同一個excel檔案內，連帶樣式一起復制
        if (srcBook == desBook && copyStyleFlag) {
            //同檔案，複製引用
            desCell.setCellStyle(srcCell.getCellStyle());
        } else if (copyStyleFlag) {
           //不同檔案，通過對映關係複製
            if (null != mapping) {
                short desIndex = mapping.desIndex(srcCell.getCellStyle().getIndex());
                desCell.setCellStyle(desBook.getCellStyleAt(desIndex));
            }
        }

        //複製評論
        if (srcCell.getCellComment() != null) {
            desCell.setCellComment(srcCell.getCellComment());
        }

        //複製內容
        desCell.setCellType(srcCell.getCellTypeEnum());

        if (copyValueFlag) {
            switch (srcCell.getCellTypeEnum()) {
                case STRING:
                    desCell.setCellValue(srcCell.getStringCellValue());
                    break;
                case NUMERIC:
                    desCell.setCellValue(srcCell.getNumericCellValue());
                    break;
                case FORMULA:
                    desCell.setCellFormula(srcCell.getCellFormula());
                    break;
                case BOOLEAN:
                    desCell.setCellValue(srcCell.getBooleanCellValue());
                    break;
                case ERROR:
                    desCell.setCellValue(srcCell.getErrorCellValue());
                    break;
                case BLANK:
                    //nothing to do
                    break;
                default:
                    break;
            }
        }

    }


    /**
     * 把一個excel中的styleTable複製到另一個excel中<br>
     * 如果是同一個excel檔案，就不用複製styleTable了
     * @return StyleMapping 兩個檔案中styleTable的對映關係
     * @see StyleMapping
     */
    public static StyleMapping copyCellStyle(Workbook srcBook, Workbook desBook){
        if (null == srcBook || null == desBook) {
            throw new ExcelException("源excel 或 目標excel 不存在");
        }
        if (srcBook.equals(desBook)) {
            throw new ExcelException("不要使用此方法在同一個檔案中copy style，同一個excel中複製sheet不需要copy Style");
        }
        if ((srcBook instanceof HSSFWorkbook && desBook instanceof XSSFWorkbook) ||
                (srcBook instanceof XSSFWorkbook && desBook instanceof HSSFWorkbook)) {
            throw new ExcelException("不支援在不同的版本的excel中複製樣式）");
        }

        logger.debug("src中style number:{}, des中style number:{}", srcBook.getNumCellStyles(), desBook.getNumCellStyles());
        short[] src2des = new short[srcBook.getNumCellStyles()];
        short[] des2src = new short[desBook.getNumCellStyles() + srcBook.getNumCellStyles()];

        for(short i=0;i<srcBook.getNumCellStyles();i++){
            //建立雙向對映
            CellStyle srcStyle = srcBook.getCellStyleAt(i);
            CellStyle desStyle = desBook.createCellStyle();
            src2des[srcStyle.getIndex()] = desStyle.getIndex();
            des2src[desStyle.getIndex()] = srcStyle.getIndex();

            //複製樣式
            desStyle.cloneStyleFrom(srcStyle);
        }


        return new StyleMapping(des2src, src2des);
    }

    /**
     * 存放兩個excel檔案中的styleTable的對映關係，以便於在複製表格時，在目標檔案中獲取到對應的樣式
     */
    public static class StyleMapping {
        /**
         *
         */
        private short[] des2srcIndexMapping;
        /**
         *
         */
        private short[] src2desIndexMapping;

        /**
         * 不允許其他類建立此型別物件
         */
        private StyleMapping() {
        }

        public StyleMapping(short[] des2srcIndexMapping, short[] src2desIndexMapping) {
            this.des2srcIndexMapping = des2srcIndexMapping;
            this.src2desIndexMapping = src2desIndexMapping;
        }

        public short srcIndex(short desIndex) {
            if (desIndex < 0 || desIndex >= this.des2srcIndexMapping.length) {
                throw new ExcelException("索引越界：原始檔styleNum=" + this.des2srcIndexMapping.length + " 訪問位置=" + desIndex);
            }
            return this.des2srcIndexMapping[desIndex];
        }

        /**
         * 根據原始檔的style的index,獲取目標檔案的style的index
         * @param srcIndex 源excel中style的index
         * @return desIndex 目標excel中style的index
         */
        public short desIndex(short srcIndex) {
            if (srcIndex < 0 || srcIndex >= this.src2desIndexMapping.length) {
                throw new ExcelException("索引越界：原始檔styleNum=" + this.src2desIndexMapping.length + " 訪問位置=" + srcIndex);
            }

            return this.src2desIndexMapping[srcIndex];
        }
    }

}

一個自定義異常類


public class ExcelException extends RuntimeException {

    public ExcelException() {
    }

    public ExcelException(String message) {
        super(message);
    }

    public ExcelException(String message, Throwable cause) {
        super(message, cause);
    }

    public ExcelException(Throwable cause) {
        super(cause);
    }

    public ExcelException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
        super(message, cause, enableSuppression, writableStackTrace);
    }
}

淺複製和深複製的概念與值複製和指標複製（引用複製）有關淺複製 “指標複製深複製值複製
2024-10-11
指標
Java引用複製、淺複製、深複製
2024-04-24
Java
JS物件複製：深複製和淺複製
2021-08-18
JS物件
MySQL 8 複製（三）——延遲複製與部分複製
2020-02-12
MySql
Delphi_Excel例子:單元格複製貼上 (轉)
2007-08-15
Excel
主從複製、雙主複製及半同步複製、以及基於SSL的複製
2017-09-16
MySQL主從複製、半同步複製和主主複製
2016-02-22
MySql
GoldenGate單向複製配置(支援DDL複製)
2015-06-17
Go
JavaScript 淺複製和深複製
2023-01-16
JavaScript
mysql複製--主從複製配置
2018-04-12
MySql
MySQL主從複製、半同步複製和主主複製概述
2016-03-14
MySql
MySQL 8 複製（一）——非同步複製
2020-02-12
MySql非同步
MySQL 8 複製（二）——半同步複製
2020-02-12
MySql
go slice深複製和淺複製
2021-07-18
Go
MySQL主從複製_複製過濾
2017-12-26
MySql
c#淺複製與深複製
2012-06-08
C#
go的深複製跟淺複製
2024-06-12
Go
js 實現深複製/深複製
2024-03-11
JS
python 淺複製、深複製坑
2024-10-23
Python
python 深複製和淺複製
2024-07-25
Python
MySQL的主從複製、半同步複製、主主複製詳解
2016-03-11
MySql
配置mysql5.5主從複製、半同步複製、主主複製
2016-01-18
MySql
Day 7.5 資料型別總結 + 複製淺複製深複製
2024-10-08
資料型別
對於複製普通物件深複製和淺複製是否一樣
2024-03-30
物件
最簡單的螢幕複製程式（象素複製） (轉)
2007-08-15
禁止複製的網頁怎麼複製
2020-04-04
網頁
MySQL 8 複製（四）——GTID與複製
2020-02-12
MySql
MySQL 8 複製（五）——配置GTID複製
2020-02-12
MySql
MySQL主從複製之GTID複製
2019-07-23
MySql
MySQL主從複製之半同步複製
2019-07-23
MySql
MySQL主從複製之非同步複製
2019-07-23
MySql非同步
資料庫複製（一）–複製介紹
2018-09-04
資料庫
JavaScript中的淺複製與深複製
2023-09-22
JavaScript
MySQL主從複製與主主複製
2017-03-01
MySql
前端er怎樣操作剪下複製以及禁止複製+破解等
2018-08-09
前端
MySQL併發複製系列二：多執行緒複製
2016-01-16
MySql執行緒
MySQL複製
2020-07-16
MySql
MySQL 複製
2016-10-06
MySql

poi操作excel，複製sheet，複製行，複製單元格，複製style

緣起

問題

如何解決

思路

程式碼

完善程式碼

相關文章