poi操作Excel中sheet的复制（java版）

package cn.labsoft.labos.utils.exportexcel;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream;

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

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

import org.apache.poi.hssf.usermodel.HSSFRichTextString; 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.hssf.util.Region; import org.apache.poi.ss.usermodel.Font;

public class CopyExcelSheet{ @SuppressWarnings(\ public static void main(String[] args) throws FileNotFoundException, IOException { String fromPath = \存放路径 String toPath = \保存新EXCEL路径 copyExcelSheet(fromPath, toPath); } /** * @Title: 根据文件路径复制sheet * @Description: 将fromPath文件中的sheet全部复制到toPath中 * @param @param fromPath 源文件全路径 * @param @param toPath 新文件全路径 * @param @throws IOException * @return 返回类型 * @throws */ @SuppressWarnings(\ public static void copyExcelSheet(String fromPath, String toPath) throws IOException { // 创建新的excel File toFile = new File(toPath); HSSFWorkbook wbCreat = null;

if (!toFile.isFile()) {

wbCreat = new HSSFWorkbook();//建立新HSSFWorkbook对象 } else { InputStream out = new FileInputStream(toFile); wbCreat = new HSSFWorkbook(out); }

File fromFile = new File(fromPath); if (fromFile.exists()) { try { InputStream in = new FileInputStream(fromFile); HSSFWorkbook wb = new HSSFWorkbook(in); for (int ii = 0; ii < wb.getNumberOfSheets(); ii++) { HSSFSheet sheet = wb.getSheetAt(ii); if (wbCreat.getSheetIndex(sheet.getSheetName()) >= 0) { continue; } HSSFSheet sheetCreat = wbCreat.createSheet(sheet .getSheetName()); // 复制源表中的合并单元格 MergerRegion(sheetCreat, sheet); int firstRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); for (int i = firstRow; i <= lastRow; i++) { // 创建新建excel Sheet的行 HSSFRow rowCreat = sheetCreat.createRow(i); // 取得源有excel Sheet的行 HSSFRow row = sheet.getRow(i); if (row == null) continue; // 单元格式样 int firstCell = row.getFirstCellNum(); int lastCell = row.getLastCellNum(); for (int j = firstCell; j < lastCell; j++) { if (row.getCell(j) == null) continue; if (i == 0) { short width = sheet.getColumnWidth((short) j); sheetCreat.setColumnWidth((short) j,(short) (1.15 width)); boolean isHidden=sheet.isColumnHidden(j); sheetCreat.setColumnHidden(j,isHidden); }

rowCreat.createCell(j);

* // 设置高度 rowCreat.getCell(j).getRow().setHeight( row.getCell(j).getRow().getHeight()); //复制样式 HSSFCellStyle newStyle = wbCreat.createCellStyle(); HSSFCellStyle oldStely = row.getCell(j).getCellStyle(); newStyle=copyStyle(newStyle, wbCreat, oldStely, wb); rowCreat.getCell(j).setCellStyle(newStyle); //复制内容 rowCreat.getCell(j).setCellValue(copyfont(wbCreat, row.getCell(j))); } } } FileOutputStream fileOut = new FileOutputStream(toFile); wbCreat.write(fileOut); fileOut.flush(); fileOut.close(); in.close(); } catch (RuntimeException e) { e.printStackTrace(); } } }; /** * @Title: 复制sheet * @Description: 将fromFile文件中的sheet全部复制到toFile中 * @param @param fromFile .xls源文件 * @param @param toFile .xls新文件 * @param @throws IOException * @return 返回类型 * @throws */ @SuppressWarnings(\ public static void copyExcelSheet(File fromFile, File toFile) throws IOException { // 创建新的excel HSSFWorkbook wbCreat = null; if (!toFile.isFile()) { wbCreat = new HSSFWorkbook();//建立新HSSFWorkbook对象 } else { InputStream out = new FileInputStream(toFile); wbCreat = new HSSFWorkbook(out); }

wb,

if (fromFile.exists()) {

try { InputStream in = new FileInputStream(fromFile); HSSFWorkbook wb = new HSSFWorkbook(in); for (int ii = 0; ii < wb.getNumberOfSheets(); ii++) { width));

HSSFSheet sheet = wb.getSheetAt(ii); if (wbCreat.getSheetIndex(sheet.getSheetName()) >= 0) { continue; } HSSFSheet sheetCreat = wbCreat.createSheet(sheet .getSheetName()); // 复制源表中的合并单元格 MergerRegion(sheetCreat, sheet); int firstRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); for (int i = firstRow; i <= lastRow; i++) { // 创建新建excel Sheet的行 HSSFRow rowCreat = sheetCreat.createRow(i); // 取得源有excel Sheet的行 HSSFRow row = sheet.getRow(i); if (row == null) continue; // 单元格式样 int firstCell = row.getFirstCellNum(); int lastCell = row.getLastCellNum(); for (int j = firstCell; j < lastCell; j++) { if (row.getCell(j) == null) continue; if (i == 0) { short width = sheet.getColumnWidth((short) j); sheetCreat.setColumnWidth((short) j,(short) (1.15 boolean isHidden=sheet.isColumnHidden(j); sheetCreat.setColumnHidden(j,isHidden); }

rowCreat.createCell(j); // 设置高度

rowCreat.getCell(j).getRow().setHeight( row.getCell(j).getRow().getHeight()); //复制样式

HSSFCellStyle newStyle = wbCreat.createCellStyle(); HSSFCellStyle oldStely = row.getCell(j).getCellStyle();

newStyle=copyStyle(newStyle, wbCreat, oldStely, wb);

* rowCreat.getCell(j).setCellStyle(newStyle); //复制内容 rowCreat.getCell(j).setCellValue(copyfont(wbCreat, row.getCell(j))); } } } FileOutputStream fileOut = new FileOutputStream(toFile); wbCreat.write(fileOut); fileOut.flush(); fileOut.close(); in.close(); } catch (RuntimeException e) { e.printStackTrace(); } } }; /** * @Title: 复制源文件中的第n个sheet * @Description: 将fromFile文件中的第n个sheet复制到toFile中 * @param @param fromFile .xls源文件 * @param @param n 源文件中要复制的sheet的序号（从0开始） * @param @param toFile .xls新文件 * @param @throws IOException * @return 返回类型 * @throws */ @SuppressWarnings(\ public static void copyExcelSheet(File fromFile,int n, File toFile) throws IOException { // 创建新的excel HSSFWorkbook wbCreat = null; if (!toFile.isFile()) { wbCreat = new HSSFWorkbook();//建立新HSSFWorkbook对象 } else { InputStream out = new FileInputStream(toFile); wbCreat = new HSSFWorkbook(out); } if (fromFile.exists()) { try { InputStream in = new FileInputStream(fromFile); HSSFWorkbook wb = new HSSFWorkbook(in); if(wb.getNumberOfSheets()>=(n+1)){ HSSFSheet sheet = wb.getSheetAt(n);

wb,

width)); row.getCell(j)));

String sheetName=sheet.getSheetName(); int x=1;

while(wbCreat.getSheetIndex(sheetName) >= 0){ sheetName+=\}

HSSFSheet sheetCreat = wbCreat.createSheet(sheetName); // 复制源表中的合并单元格 MergerRegion(sheetCreat, sheet);

int firstRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); for (int i = firstRow; i <= lastRow; i++) { // 创建新建excel Sheet的行 HSSFRow rowCreat = sheetCreat.createRow(i); // 取得源有excel Sheet的行 HSSFRow row = sheet.getRow(i); if (row == null) continue; // 单元格式样 int firstCell = row.getFirstCellNum(); int lastCell = row.getLastCellNum(); for (int j = firstCell; j < lastCell; j++) { if (row.getCell(j) == null) continue; if (i == 0) { short width = sheet.getColumnWidth((short) j); sheetCreat.setColumnWidth((short) j,(short) (1.15 * boolean isHidden=sheet.isColumnHidden(j); sheetCreat.setColumnHidden(j,isHidden); }

rowCreat.createCell(j); // 设置高度

rowCreat.getCell(j).getRow().setHeight( row.getCell(j).getRow().getHeight()); //复制样式

HSSFCellStyle newStyle = wbCreat.createCellStyle(); HSSFCellStyle oldStely = row.getCell(j).getCellStyle(); newStyle=copyStyle(newStyle, wbCreat, oldStely, wb); rowCreat.getCell(j).setCellStyle(newStyle); //复制内容

rowCreat.getCell(j).setCellValue(copyfont(wbCreat,

wb,

}

} } FileOutputStream fileOut = new FileOutputStream(toFile); wbCreat.write(fileOut); fileOut.flush(); fileOut.close(); in.close(); } catch (RuntimeException e) { e.printStackTrace(); } } }; /**

* @Title: 复制源文件中的名称为sheetName的sheet

* @Description: 将fromFile文件中的名称为sheetName的sheet复制到toFile中 * @param @param fromFile .xls源文件

* @param @param sheetName 源文件中要复制的sheet的名称 * @param @param toFile .xls新文件 * @param @throws IOException * @return 返回类型 * @throws */

@SuppressWarnings(\

public static void copyExcelSheet(File fromFile,String sheetName, File toFile) throws IOException { // 创建新的excel HSSFWorkbook wbCreat = null; if (!toFile.isFile()) { wbCreat = new HSSFWorkbook();//建立新HSSFWorkbook对象 } else { InputStream out = new FileInputStream(toFile); wbCreat = new HSSFWorkbook(out); } if (fromFile.exists()) { try { InputStream in = new FileInputStream(fromFile); HSSFWorkbook wb = new HSSFWorkbook(in); if(wb.getSheetIndex(sheetName)>=0){ HSSFSheet sheet = wb.getSheetAt(wb.getSheetIndex(sheetName)); int x=1; while(wbCreat.getSheetIndex(sheetName) >= 0){ sheetName+=\ } HSSFSheet sheetCreat = wbCreat.createSheet(sheetName);

width)); row.getCell(j)));

// 复制源表中的合并单元格 MergerRegion(sheetCreat, sheet);

int firstRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); for (int i = firstRow; i <= lastRow; i++) { // 创建新建excel Sheet的行 HSSFRow rowCreat = sheetCreat.createRow(i); // 取得源有excel Sheet的行 HSSFRow row = sheet.getRow(i); if (row == null) continue; // 单元格式样 int firstCell = row.getFirstCellNum(); int lastCell = row.getLastCellNum(); for (int j = firstCell; j < lastCell; j++) { if (row.getCell(j) == null) continue; if (i == 0) { short width = sheet.getColumnWidth((short) j); sheetCreat.setColumnWidth((short) j,(short) (1.15 * boolean isHidden=sheet.isColumnHidden(j); sheetCreat.setColumnHidden(j,isHidden); }

rowCreat.createCell(j); // 设置高度

rowCreat.getCell(j).getRow().setHeight( row.getCell(j).getRow().getHeight()); //复制样式

HSSFCellStyle newStyle = wbCreat.createCellStyle(); HSSFCellStyle oldStely = row.getCell(j).getCellStyle(); newStyle=copyStyle(newStyle, wbCreat, oldStely, wb); rowCreat.getCell(j).setCellStyle(newStyle); //复制内容

rowCreat.getCell(j).setCellValue(copyfont(wbCreat,

wb,

} } }

FileOutputStream fileOut = new FileOutputStream(toFile); wbCreat.write(fileOut); fileOut.flush(); fileOut.close();

in.close(); } catch (RuntimeException e) { e.printStackTrace(); } } }; private static HSSFRichTextString copyfont(HSSFWorkbook wbCreat,HSSFWorkbook wb,HSSFCell oldCell){ int fromIndex = 0; int toIndex = 0; String value = \ //获取单元格中的数据 HSSFRichTextString rts = (HSSFRichTextString) oldCell.getRichStringCellValue(); //获取每个单元格数据的style属性 HSSFFont fontTemp =oldCell.getCellStyle().getFont(wb); //处理上下标 if (rts.numFormattingRuns() > 0) { for (int k = 0; k < rts.numFormattingRuns(); k++) { toIndex = rts.getIndexOfFormattingRun(k); String temp = rts.toString().substring(fromIndex, toIndex); //判断下标 if (fontTemp.getTypeOffset() == HSSFFont.SS_SUB) { Font font1 = wbCreat.createFont(); font1.setTypeOffset(HSSFFont.SS_SUB); rts.applyFont(fromIndex, toIndex,font1); } //判断上标 if (fontTemp.getTypeOffset() == HSSFFont.SS_SUPER) { Font font2 = wbCreat.createFont(); font2.setTypeOffset(HSSFFont.SS_SUPER); rts.applyFont(fromIndex, toIndex,font2); } value += temp; if (!value.equals(\ fontTemp = wb.getFontAt(rts.getFontOfFormattingRun(k)); } fromIndex = toIndex; } toIndex = rts.length(); String temp1 = rts.toString().substring(fromIndex, toIndex); if (fontTemp.getTypeOffset() == HSSFFont.SS_SUB) { Font font1 = wbCreat.createFont(); font1.setTypeOffset(HSSFFont.SS_SUB); rts.applyFont(fromIndex, toIndex,font1);

} if (fontTemp.getTypeOffset() == HSSFFont.SS_SUPER) { Font font2 = wbCreat.createFont(); font2.setTypeOffset(HSSFFont.SS_SUPER); rts.applyFont(fromIndex, toIndex,font2); } value += temp1; } return rts; } private static HSSFCellStyle copyStyle(HSSFCellStyle newStyle,HSSFWorkbook wbCreat,HSSFCellStyle oldStely,HSSFWorkbook wb){ newStyle.setAlignment(oldStely.getAlignment()); newStyle.setBorderTop(oldStely.getBorderTop()); newStyle.setBorderBottom(oldStely.getBorderBottom()); newStyle.setBorderLeft(oldStely.getBorderLeft()); newStyle.setBorderRight(oldStely.getBorderRight()); newStyle.setBottomBorderColor(oldStely.getBottomBorderColor()); newStyle.setFillBackgroundColor(oldStely.getFillBackgroundColor()); newStyle.setDataFormat(oldStely.getDataFormat()); newStyle.setFillForegroundColor(oldStely.getFillForegroundColor()); newStyle.setFillPattern(oldStely.getFillPattern()); newStyle.setHidden(oldStely.getHidden()); newStyle.setIndention(oldStely.getIndention()); newStyle.setLeftBorderColor(oldStely.getLeftBorderColor()); newStyle.setLocked(oldStely.getLocked()); newStyle.setRightBorderColor(oldStely.getRightBorderColor()); newStyle.setRotation(oldStely.getRotation()); newStyle.setTopBorderColor(oldStely.getTopBorderColor()); newStyle.setVerticalAlignment(oldStely.getVerticalAlignment()); newStyle.setWrapText(oldStely.getWrapText()); //设置字体 Font newfont = wbCreat.createFont(); HSSFFont font = oldStely.getFont(wb); newfont.setFontHeightInPoints(font.getFontHeightInPoints());//设置字号 newfont.setFontName(font.getFontName());//设置字体 newfont.setItalic(font.getItalic());//设置是否斜体 newfont.setStrikeout(font.getStrikeout());//设置是否强调 newfont.setBoldweight(font.getBoldweight()); newfont.setFontHeight(font.getFontHeight()); newfont.setCharSet(font.getCharSet()); newfont.setColor(font.getColor()); newfont.setTypeOffset(font.getTypeOffset()); newfont.setUnderline(font.getUnderline());

}

newStyle.setFont(newfont); return newStyle; } /**

* 复制原有sheet的合并单元格到新创建的sheet *

* @param sheetCreat

* 新创建sheet * @param sheet

* 原有的sheet */

@SuppressWarnings(\

private static void MergerRegion(HSSFSheet sheetCreat, HSSFSheet sheet) { int sheetMergerCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergerCount; i++) { Region mergedRegionAt = sheet.getMergedRegionAt(i); sheetCreat.addMergedRegion(mergedRegionAt); } }

}

newStyle.setFont(newfont); return newStyle; } /**

* 复制原有sheet的合并单元格到新创建的sheet *

* @param sheetCreat

* 新创建sheet * @param sheet

* 原有的sheet */

@SuppressWarnings(\

private static void MergerRegion(HSSFSheet sheetCreat, HSSFSheet sheet) { int sheetMergerCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergerCount; i++) { Region mergedRegionAt = sheet.getMergedRegionAt(i); sheetCreat.addMergedRegion(mergedRegionAt); } }

本文来源：https://www.bwwdw.com/article/n946.html