緣起
最近寫一個程式，需要操作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);
}
}