Java Excel 复制单元格 poi Excel 复制单元格 Java Excel 复制行 Java Excel 复制 sheet 页
一、前言
1、本文记录 poi excel 实现 单元格cell的复制 、 row行复制 、 sheet 页复制 ；一般应用的场景：excel 模板中 可能需要进行的复制操作。
2、poi 版本：3.16
3、本文参考代码：poi操作excel，复制sheet，复制行，复制单元格_PlayAround
二、示例代码
1、创建类：ExcelCellCopy ，主要有 copyCellStyle 、 copyCell 、copyRow 、copySheet ，分别对应作用是： 复制单元格样式、复制单元格、复制行 、 复制 sheet 页面 等功能。
2、具体代码如下：
package poi.test;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import java.util.Iterator;
/**
* Description: Excel 单元格 测试
* @author w
* @version 1.0
* @date 2022/8/23 14:14
* @see javascript:void(0) 【源码这里】
*
*/
public class ExcelCellCopy {
/**
* @Description: 复制单元格 cell 样式
* @param workbook
* @param fromStyle
* @param toStyle
* @return void
* @version v1.0
* @author wu
* @date 2022/8/24 22:39
*/
public static void copyCellStyle(Workbook workbook, CellStyle fromStyle, CellStyle toStyle) {
// 水平垂直对齐方式
toStyle.setAlignment(fromStyle.getAlignment());
toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
//边框和边框颜色
toStyle.setBorderBottom(fromStyle.getBorderBottom());
toStyle.setBorderLeft(fromStyle.getBorderLeft());
toStyle.setBorderRight(fromStyle.getBorderRight());
toStyle.setBorderTop(fromStyle.getBorderTop());
toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
//背景和前景
if(fromStyle instanceof XSSFCellStyle){
XSSFCellStyle xssfToStyle = (XSSFCellStyle) toStyle;
xssfToStyle.setFillBackgroundColor(((XSSFCellStyle) fromStyle).getFillBackgroundColorColor());
xssfToStyle.setFillForegroundColor(((XSSFCellStyle) fromStyle).getFillForegroundColorColor());
}else {
toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
}
toStyle.setDataFormat(fromStyle.getDataFormat());
toStyle.setFillPattern(fromStyle.getFillPattern());
// toStyle.setFont(fromStyle.getFont(null)); // 没有提供get 方法
if (fromStyle instanceof HSSFCellStyle) {
// 处理字体获取：03版 xls
HSSFCellStyle style = (HSSFCellStyle) fromStyle;
toStyle.setFont(style.getFont(workbook));
} else if (fromStyle instanceof XSSFCellStyle) {
// 处理字体获取：07版以及之后 xlsx
XSSFCellStyle style = (XSSFCellStyle) fromStyle;
toStyle.setFont(style.getFont());
}
toStyle.setHidden(fromStyle.getHidden());
toStyle.setIndention(fromStyle.getIndention());//首行缩进
toStyle.setLocked(fromStyle.getLocked());
toStyle.setRotation(fromStyle.getRotation());//旋转
toStyle.setWrapText(fromStyle.getWrapText());
}
/**
* @Description: 复制 sheet 页
* @param wb
* @param fromSheet
* @param toSheet
* @param copyValueFlag
* @return void
* @version v1.0
* @author wu
* @date 2022/8/24 22:40
*/
public static void copySheet(Workbook wb, Sheet fromSheet, Sheet toSheet, boolean copyValueFlag) {
//合并区域处理
mergerRegion(fromSheet, toSheet);
Iterator<Row> rowIterator = fromSheet.rowIterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Row newRow = toSheet.createRow(row.getRowNum());
copyRow(wb, row, newRow, copyValueFlag);
}
}
/**
* @Description: 复制 行row 数据
* @return void
* @version v1.0
* @author wu
* @date 2022/8/24 22:40
*/
public static void copyRow(Workbook wb, Row fromRow, Row toRow, boolean copyValueFlag) {
short lastCellNum = fromRow.getLastCellNum();
for (int i = 0; i < lastCellNum; i++) {
Cell fromCel = fromRow.getCell(i);
Cell destCell = toRow.createCell(i);
copyCell(wb, fromCel, destCell, copyValueFlag);
}
}
/**
* @Description: 复制原有sheet的合并单元格到新创建的sheet
* <br> 处理 sheet 页的合并区域
* @param fromSheet
* @param toSheet
* @return void
* @version v1.0
* @author wu
* @date 2022/8/24 22:43
*/
public static void mergerRegion(Sheet fromSheet, Sheet toSheet) {
int sheetMergerCount = fromSheet.getNumMergedRegions();
for (int i = 0; i < sheetMergerCount; i++) {
CellRangeAddress mergedRegion = fromSheet.getMergedRegion(i);
toSheet.addMergedRegion(mergedRegion);
}
}
/**
* @Description: 复制 单元格 cell
* @param wb
* @param srcCell
* @param distCell
* @param copyValueFlag 是否包含内容 - true 包含内容复制
* @return void
* @version v1.0
* @author wu
* @date 2022/8/24 22:43
*/
public static void copyCell(Workbook wb, Cell srcCell, Cell distCell, boolean copyValueFlag) {
CellStyle newstyle= wb.createCellStyle();
copyCellStyle(wb,srcCell.getCellStyle(), newstyle);
// distCell.setEncoding(srcCell.getEncoding());
// 复制样式
distCell.setCellStyle(newstyle);
//评论
if (srcCell.getCellComment() != null) {
distCell.setCellComment(srcCell.getCellComment());
}
// 不同数据类型处理
int srcCellType = srcCell.getCellType();
distCell.setCellType(srcCellType);
if (copyValueFlag) {
if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
distCell.setCellValue(srcCell.getDateCellValue());
} else {
distCell.setCellValue(srcCell.getNumericCellValue());
}
} else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
distCell.setCellValue(srcCell.getRichStringCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
// nothing21
} else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
} else { // nothing29
}
}
}
}3、测试代码如下：
package poi.test;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
/**
* Description: excel 单元复制
* @author wu
* @version 1.0
* @date 2022/8/24 22:48
* @see javascript:void(0)
*/
public class ExcelCopyTest {
public static void main(String[] args) throws Exception {
excelCellCopy(); // excel 单元格复制
// excelRowCopy(); // excel 行数据复制
// excelSheetCopy(); // excel sheet 页数据复制
}
/**
* @Description: excel 单元格复制
* @return void
* @version v1.0
* @author wu
* @date 2022/8/24 22:53
*/
private static void excelCellCopy() throws IOException {
Workbook workbook = readTemplate();
Sheet sheet = workbook.getSheet("template");
Sheet sheet2 = workbook.getSheet("第一页");
// 复制 普通单元格
Row srcRow = sheet.getRow(0);
Cell srcCell = srcRow.getCell(0);
Row destRow = sheet2.createRow(10);
Cell destCell = destRow.createCell(0);
ExcelCellCopy.copyCell(workbook,srcCell,destCell,true);
// 复制 合并的单元格
Row srcRow2 = sheet.getRow(2);
Cell srcCell2 = srcRow2.getCell(0);
sheet2.addMergedRegion(new CellRangeAddress(12,12,0,2));
Row destRow2 = sheet2.createRow(12);
Cell destCell2 = destRow2.createCell(0);
// remark: 合并 背景色的单元格 有问题
ExcelCellCopy.copyCell(workbook,srcCell2,destCell2,true);
// 写出到文件
String fileName = "D:\\文件导出-cell单元格复制 " +System.currentTimeMillis()+".xlsx" ;
FileOutputStream out = new FileOutputStream(new File(fileName));
workbook.write(out);
}
private static Workbook readTemplate() {
// 读取模板
InputStream inputStream = ExcelCopyTest.class.getResourceAsStream("/excel模板.xlsx");
// System.out.println("inputStream :" + inputStream);
Workbook workbook = null ;
try {
workbook= WorkbookFactory.create(inputStream);
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
return workbook ;
}
/**
* @Description: excel row 行数据复制
* @return void
* @version v1.0
* @author wu
* @date 2022/8/24 22:54
*/
private static void excelRowCopy() throws IOException {
Workbook workbook = readTemplate();
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
int start = 4 ;
for (int i = 0; i < 5; i++) {
Row destRow = sheet.createRow(start + i);
ExcelCellCopy.copyRow(workbook,row,destRow ,true);
}
// 写出到文件
String fileName = "D:\\文件导出-row 行复制 " +System.currentTimeMillis()+".xlsx" ;
FileOutputStream out = new FileOutputStream(new File(fileName));
workbook.write(out);
}
/**
* @Description: excel sheet 页复制
* @return void
* @version v1.0
* @author wu
* @date 2022/8/24 22:54
*/
private static void excelSheetCopy() throws IOException {
Workbook workbook = readTemplate();
Sheet srcSheet = workbook.getSheet("第一页");
Sheet destSheet = workbook.createSheet("第一个复制");
// 复制普通 sheet 页
ExcelCellCopy.copySheet(workbook,srcSheet,destSheet,true);
// 复制带有合并单元格个sheet页
Sheet srcSheet2 = workbook.getSheet("template");
Sheet destSheet2 = workbook.createSheet("template-copy");
ExcelCellCopy.copySheet(workbook,srcSheet2,destSheet2,true);
// 写出到文件
String fileName = "D:\\文件导出- sheet 页复制 " +System.currentTimeMillis()+".xlsx" ;
FileOutputStream out = new FileOutputStream(new File(fileName));
workbook.write(out);
}
}
4、示例中 excel模板.xlsx 文件，参考如下图：
三、总结
1、本文使用poi的相关api，实现 excel 单元格的复制功能，应用场景在 excel 模板相关的操作中 会使用到。
2、示例中是全部代码，直接复制粘贴即可使用~ excel 模板，根据实际需求进行创建即可。
3、复制后的效果如下 ---- 几乎一模一样~
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
