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