poi操作Excel中sheet的复制(java版)

package https://www.360docs.net/doc/2c13889745.html,bos.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 https://www.360docs.net/doc/2c13889745.html,ermodel.HSSFCell;

import https://www.360docs.net/doc/2c13889745.html,ermodel.HSSFCellStyle;

import https://www.360docs.net/doc/2c13889745.html,ermodel.HSSFFont;

import https://www.360docs.net/doc/2c13889745.html,ermodel.HSSFRichTextString;

import https://www.360docs.net/doc/2c13889745.html,ermodel.HSSFRow;

import https://www.360docs.net/doc/2c13889745.html,ermodel.HSSFSheet;

import https://www.360docs.net/doc/2c13889745.html,ermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.Region;

import https://www.360docs.net/doc/2c13889745.html,ermodel.Font;

public class CopyExcelSheet{

@SuppressWarnings("deprecation")

public static void main(String[] args) throws FileNotFoundException,

IOException {

String fromPath = "C:\\Users\\Administrator\\Desktop\\quinn\\1.xls";// excel存放路径

String toPath = "C:\\Users\\Administrator\\Desktop\\quinn\\5.xls";// 保存新EXCEL路径

copyExcelSheet(fromPath, toPath);

}

/**

* @Title: 根据文件路径复制sheet

* @Description: 将fromPath文件中的sheet全部复制到toPath中

* @param @param fromPath 源文件全路径

* @param @param toPath 新文件全路径

* @param @throws IOException

* @return 返回类型

* @throws

*/

@SuppressWarnings("deprecation")

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, wb, 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("deprecation")

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);

}

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, wb, 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("deprecation")

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);

String sheetName=sheet.getSheetName();

int x=1;

while(wbCreat.getSheetIndex(sheetName) >= 0){

sheetName+="("+x+")";

}

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 * 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, wb, row.getCell(j)));

}

}

}

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("deprecation")

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+="("+x+")";

}

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 * 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, wb, row.getCell(j)));

}

}

}

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("deprecation")

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);

}

}

}