java复制sheet_Java 操作POI 之复制sheet页

最新推荐文章于 2025-06-13 10:43:30 发布

原创最新推荐文章于 2025-06-13 10:43:30 发布 · 2k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#java复制sheet

该代码段展示了如何使用Apache POI库在Java中复制HSSFSheet（Excel工作表）的内容，包括行、列及合并单元格，并在目标工作表中保持原有的样式和数据类型。它遍历源工作表的所有行和列，复制单元格数据，处理合并区域，并调整列宽。此功能适用于需要批量复制Excel工作表内容的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

来点干货直接上代码，就不细说了

package com.qs.web.tools.core.excel;

import java.util.HashMap;

import java.util.Map;

import java.util.Set;

import java.util.TreeSet;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelCopySheetUtil {

public ExcelCopySheetUtil() {

}

public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet) {

copySheets(newSheet, sheet, true);

}

public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet,

boolean copyStyle) {

int maxColumnNum = 0;

Map styleMap = (copyStyle) ? new HashMap()

: null;

for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {

HSSFRow srcRow = sheet.getRow(i);

HSSFRow destRow = newSheet.createRow(i);

if (srcRow != null) {

ExcelCopySheetUtil.copyRow(sheet, newSheet, srcRow, destRow,

styleMap);

if (srcRow.getLastCellNum() > maxColumnNum) {

maxColumnNum = srcRow.getLastCellNum();

}

for (int i = 0; i <= maxColumnNum; i++) { //设置列宽

newSheet.setColumnWidth(i, sheet.getColumnWidth(i));

}

/**

* 复制并合并单元格

* @param newSheet

* @param sheet

* @param copyStyle

public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet,

HSSFRow srcRow, HSSFRow destRow,

Map styleMap) {

Set mergedRegions = new TreeSet();

destRow.setHeight(srcRow.getHeight());

int deltaRows = destRow.getRowNum() - srcRow.getRowNum(); //如果copy到另一个sheet的起始行数不同

for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {

HSSFCell oldCell = srcRow.getCell(j); // old cell

HSSFCell newCell = destRow.getCell(j); // new cell

if (oldCell != null) {

if (newCell == null) {

newCell = destRow.createCell(j);

}

copyCell(oldCell, newCell, styleMap);

CellRangeAddress mergedRegion = getMergedRegion(srcSheet,

srcRow.getRowNum(), (short) oldCell.getColumnIndex());

if (mergedRegion != null) {

CellRangeAddress newMergedRegion = new CellRangeAddress(

mergedRegion.getFirstRow() + deltaRows,

mergedRegion.getLastRow() + deltaRows, mergedRegion

.getFirstColumn(), mergedRegion

.getLastColumn());

CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(

newMergedRegion);

if (isNewMergedRegion(wrapper, mergedRegions)) {

mergedRegions.add(wrapper);

destSheet.addMergedRegion(wrapper.range);

}

/**

* 把原来的Sheet中cell(列)的样式和数据类型复制到新的sheet的cell(列)中 *

* @param oldCell

* @param newCell

* @param styleMap

public static void copyCell(HSSFCell oldCell, HSSFCell newCell,

Map styleMap) {

if (styleMap != null) {

if (oldCell.getSheet().getWorkbook() == newCell.getSheet()

.getWorkbook()) {

newCell.setCellStyle(oldCell.getCellStyle());

} else {

int stHashCode = oldCell.getCellStyle().hashCode();

HSSFCellStyle newCellStyle = styleMap.get(stHashCode);

if (newCellStyle == null) {

newCellStyle = newCell.getSheet().getWorkbook()

.createCellStyle();

newCellStyle.cloneStyleFrom(oldCell.getCellStyle());

styleMap.put(stHashCode, newCellStyle);

}

newCellStyle.setLocked(false);

newCell.setCellStyle(newCellStyle);

}

switch (oldCell.getCellType()) {

case HSSFCell.CELL_TYPE_STRING:

newCell.setCellValue(oldCell.getStringCellValue());

break;

case HSSFCell.CELL_TYPE_NUMERIC:

newCell.setCellValue(oldCell.getNumericCellValue());

break;

case HSSFCell.CELL_TYPE_BLANK:

newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);

break;

case HSSFCell.CELL_TYPE_BOOLEAN:

newCell.setCellValue(oldCell.getBooleanCellValue());

break;

case HSSFCell.CELL_TYPE_ERROR:

newCell.setCellErrorValue(oldCell.getErrorCellValue());

break;

case HSSFCell.CELL_TYPE_FORMULA:

newCell.setCellFormula(oldCell.getCellFormula());

break;

default:

break;

}

// 获取merge对象

public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum,

short cellNum) {

for (int i = 0; i < sheet.getNumMergedRegions(); i++) {

CellRangeAddress merged = sheet.getMergedRegion(i);

if (merged.isInRange(rowNum, cellNum)) {

return merged;

}

return null;

}

private static boolean isNewMergedRegion(

CellRangeAddressWrapper newMergedRegion,

Set mergedRegions) {

boolean bool = mergedRegions.contains(newMergedRegion);

return !bool;

}