poi复制行
package vip.xiaonuo.modular.utiles;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.List;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
public class ExcelCopy {
public static void main(String[] args) throws Exception {
File newFile = new File("C:\\Users\\Desktop\\工作簿");
File newFile1 = new File("C:\\Users\\Desktop\\工作簿");
// 新文件写入数据，并下载
InputStream is = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFSheet sheet1 = null;
HSSFSheet sheet2 = null;
POIFSFileSystem ps = null;
try {
is = new FileInputStream(newFile);// 将excel文件转为输入流
ps = new POIFSFileSystem(is);
workbook = new HSSFWorkbook(ps);// 创建个workbook，
// 获取第一个sheet
sheet = workbook.getSheetAt(0);
sheet1 = workbook.cloneSheet(1);
sheet2 = workbook.cloneSheet(2);
} catch (Exception e1) {
e1.printStackTrace();
}
ExcelUtil.copyRows(workbook, sheet, sheet1, 1, 40, 1);
// 写数据
FileOutputStream fos = new FileOutputStream(newFile1);
workbook.write(fos);
fos.flush();
fos.close();
workbook.close();
}
/**
*
* @param startRow 插入行的行标,即在哪一行下插入
* @param rows 插入多少行
* @param sheet XSSFSheet
* @param copyvalue 新行复制(startRow-1)行的样式,而且在拷贝行的时候可以指定是否需要拷贝值
* @Author Wsong qzsoft
*/
public static void InsertRow(int startRow, int rows, XSSFSheet sheet, Boolean copyvalue) {
if (rows == 0) {
rows = 1;
}
//先获取原始的合并单元格address集合
List<CellRangeAddress> originMerged = sheet.getMergedRegions();
for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
CellRangeAddress region = sheet.getMergedRegion(i);
//判断移动的行数后重新拆分
if(region.getFirstRow()>startRow){
sheet.removeMergedRegion(i);
}
}
sheet.shiftRows(startRow,sheet.getLastRowNum(),rows,true,false);
sheet.createRow(startRow);
for(CellRangeAddress cellRangeAddress : originMerged) {
//这里的8是插入行的index，表示这行之后才重新合并
if(cellRangeAddress.getFirstRow() > startRow) {
//你插入了几行就加几，我这里插入了一行，加1
int firstRow = cellRangeAddress.getFirstRow() + rows;
CellRangeAddress newCellRangeAddress = new CellRangeAddress(firstRow, (firstRow + (cellRangeAddress
.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(),
cellRangeAddress.getLastColumn());
sheet.addMergedRegion(newCellRangeAddress);
}
}
CellCopyPolicy cellCopyPolicy = new CellCopyPolicy();
cellCopyPolicy.setCopyCellValue(copyvalue);
cellCopyPolicy.isCopyCellValue();
for (int i = 0; i < rows; i++) {
sheet.copyRows(startRow-1,startRow-1,startRow+i,cellCopyPolicy);
}
}
/**
* 复制行
*
* @param startCol
* 起始列
* @param endCol
* 结束列
* @param pPosition
* 目标起始行位置
*/
public static HSSFSheet copycols(HSSFSheet currentSheet,int startCol, int endCol, int pPosition) {
int pStartCol= startCol - 1;
int pEndCol = endCol - 1;
int targetColFrom;
int targetColTo;
int RowCount;
CellRangeAddress region = null;
int i;
int j;
if (pStartCol == -1 || pEndCol == -1) {
return null;
}
System.out.println(currentSheet.getNumMergedRegions());
for (i = 0; i < currentSheet.getNumMergedRegions(); i++) {
region = currentSheet.getMergedRegion(i);
if ((region.getFirstColumn() >= pStartCol)
&& (region.getLastColumn() <= pEndCol)) {
targetColFrom = region.getFirstColumn() - pStartCol + pPosition;
targetColTo = region.getLastColumn() - pStartCol + pPosition;
CellRangeAddress newRegion = region.copy();
newRegion.setFirstRow(region.getFirstRow());
newRegion.setFirstColumn(targetColFrom);
newRegion.setLastRow(region.getLastRow());
newRegion.setLastColumn(targetColTo);
currentSheet.addMergedRegion(newRegion);
}
}
for (i = 0; i <= 50; i++) {
HSSFRow sourceRow = currentSheet.getRow(i);
if (sourceRow != null) {
HSSFRow newRow = currentSheet.getRow(i);
for (j = 0; j < pEndCol; j++) {
HSSFCell templateCell = sourceRow.getCell(j);
if(i == 0){
currentSheet.setColumnWidth(pPosition+j, currentSheet.getColumnWidth(j));
}
if (templateCell != null) {
HSSFCell newCell = newRow.createCell(pPosition+j);
copyCell(templateCell, newCell);
}
}
}
}
return currentSheet;
}
public static void copyRows(HSSFSheet currentSheet,int startRow, int endRow, int pPosition) {
int pStartRow = startRow - 1;
int pEndRow = endRow - 1;
int targetRowFrom;
int targetRowTo;
int columnCount;
CellRangeAddress region = null;
int i;
int j;
if (pStartRow == -1 || pEndRow == -1) {
return;
}
for (i = 0; i < currentSheet.getNumMergedRegions(); i++) {
region = currentSheet.getMergedRegion(i);
if ((region.getFirstRow() >= pStartRow)
&& (region.getLastRow() <= pEndRow)) {
targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
targetRowTo = region.getLastRow() - pStartRow + pPosition;
CellRangeAddress newRegion = region.copy();
newRegion.setFirstRow(targetRowFrom);
newRegion.setFirstColumn(region.getFirstColumn());
newRegion.setLastRow(targetRowTo);
newRegion.setLastColumn(region.getLastColumn());
currentSheet.addMergedRegion(newRegion);
}
}
for (i = pStartRow; i <= pEndRow; i++) {
HSSFRow sourceRow = currentSheet.getRow(i);
columnCount = sourceRow.getLastCellNum();
if (sourceRow != null) {
HSSFRow newRow = currentSheet.createRow(pPosition - pStartRow
+ i);
newRow.setHeight(sourceRow.getHeight());
for (j = 0; j < columnCount; j++) {
HSSFCell templateCell = sourceRow.getCell(j);
if (templateCell != null) {
HSSFCell newCell = newRow.createCell(j);
copyCell(templateCell, newCell);
}
}
}
}
}
private static void copyCell(HSSFCell srcCell, HSSFCell distCell) {
distCell.setCellStyle(srcCell.getCellStyle());
if (srcCell.getCellComment() != null) {
distCell.setCellComment(srcCell.getCellComment());
}
CellType srcCellType = srcCell.getCellType();
distCell.setCellType(srcCellType);
Object xssfValue = getXSSFValue(srcCell);
switch (srcCell.getCellType()) {
case NUMERIC: //数字
distCell.setCellValue(srcCell.getDateCellValue());
break;
case BOOLEAN: //Boolean
distCell.setCellValue(srcCell.getBooleanCellValue());
break;
case ERROR: //故障
distCell.setCellErrorValue(srcCell.getErrorCellValue());
break;
case FORMULA: //公式
distCell.setCellFormula(srcCell.getCellFormula());
break;
case BLANK: //空值
break;
default: //字符串
distCell.setCellValue(srcCell.getRichStringCellValue());
}
}
/**
* excel值处理
*
* @param hssfCell
* @return
*/
public static Object getXSSFValue(HSSFCell hssfCell) {
Object result = null;
CellType cellType = hssfCell.getCellType();
switch (hssfCell.getCellType()) {
case NUMERIC: //数字
result = hssfCell.getNumericCellValue();
break;
case BOOLEAN: //Boolean
result = hssfCell.getBooleanCellValue();
break;
case ERROR: //故障
result = hssfCell.getErrorCellValue();
break;
case FORMULA: //公式
result = hssfCell.getCellFormula();
break;
case BLANK: //空值
result = "";
break;
default: //字符串
result = hssfCell.getStringCellValue();
}
return result;
}
}