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,