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

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 CopyExcelSheetSuppressWarnings(deprecation)public static void main(String args) throws FileNotFoundException,IOException String fromPath = C:UsersAdministratorDesktopquinn1.xls;/ excel存放路径String toPath = C:UsersAdministratorDesktopquinn5.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 / 创建新的excelFile 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 = 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 / 创建新的excelHSSFWorkbook 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 = 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 =(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 =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 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(fon