java李sheet,java poi 復制excel中的sheet

package excel;

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 java.util.regex.Matcher;

import java.util.regex.Pattern;

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

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

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

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

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

import org.apache.poi.ss.util.CellRangeAddress;

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

public class CopyExcelSheetToAnotherExcelSheet {

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

IOException {

String strPath = "D:\\excelFrom";//excel存放路徑

File file = new File(strPath);

for (File excel : file.listFiles()) {

ReadExcel(strPath, excel.getName());

}

}

/**

* strPath 文件夾路徑名

* name　　 excel文件名

*/

private static void ReadExcel(String strPath, String name)

throws IOException {

// 打開已有的excel

String strExcelPath = strPath + "\\" + name;

InputStream in = new FileInputStream(strExcelPath);

HSSFWorkbook wb = new HSSFWorkbook(in);

for (int i = 0; i < wb.getNumberOfSheets(); i++) {

HSSFSheet sheet = wb.getSheetAt(i);

CreatNewExcel(sheet,wb);

}

}

/**

*

* @param sheet excel表sheet

* @throws IOException

*/

private static void CreatNewExcel(HSSFSheet sheet,HSSFWorkbook wb) throws IOException {

// 新的excel 文件名

String excelName = "新的excel 文件名";

// 創建新的excel

HSSFWorkbook wbCreat = new HSSFWorkbook();

HSSFSheet sheetCreat = wbCreat.createSheet("new sheet");

// 復制源表中的合並單元格

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

// 單元格式樣

HSSFCellStyle cellStyle = null;

int firstCell = row.getFirstCellNum();

int lastCell = row.getLastCellNum();

for (int j = firstCell; j < lastCell; j++) {

// 自動適應列寬 貌似不起作用

sheetCreat.autoSizeColumn(j);

// new一個式樣

cellStyle = wbCreat.createCellStyle();

// 設置邊框線型

cellStyle.setBorderTop(row.getCell(j).getCellStyle().getBorderTop());

cellStyle.setBorderBottom(row.getCell(j).getCellStyle().getBorderBottom());

cellStyle.setBorderLeft(row.getCell(j).getCellStyle().getBorderLeft());

cellStyle.setBorderRight(row.getCell(j).getCellStyle().getBorderRight());

// 設置內容位置:例水平居中,居右，居工

cellStyle.setAlignment(row.getCell(j).getCellStyle().getAlignment());

// 設置內容位置:例垂直居中,居上，居下

cellStyle.setVerticalAlignment(row.getCell(j).getCellStyle().getVerticalAlignment());

// 自動換行

cellStyle.setWrapText(row.getCell(j).getCellStyle().getWrapText());

rowCreat.createCell(j).setCellStyle(cellStyle);

// 設置單元格高度

rowCreat.getCell(j).getRow().setHeight(row.getCell(j).getRow().getHeight());

// 單元格類型

switch (row.getCell(j).getCellType()) {

case HSSFCell.CELL_TYPE_STRING:

String strVal = removeInternalBlank(row.getCell(j).getStringCellValue());

rowCreat.getCell(j).setCellValue(strVal);

break;

case HSSFCell.CELL_TYPE_NUMERIC:

rowCreat.getCell(j).setCellValue(row.getCell(j).getNumericCellValue());

break;

case HSSFCell.CELL_TYPE_FORMULA:

try {

rowCreat.getCell(j).setCellValue(String.valueOf(row.getCell(j).getNumericCellValue()));

} catch (IllegalStateException e) {

try {

rowCreat.getCell(j).setCellValue(String.valueOf(row.getCell(j).getRichStringCellValue()));

} catch (Exception ex) {

rowCreat.getCell(j).setCellValue("公式出錯");

}

}

break;

}

}

}

String strPath = "D:\\excelTo\\";//保存新EXCEL路徑

//檢查同名

excelName = checkFileName(strPath,excelName);

FileOutputStream fileOut = new FileOutputStream(strPath + excelName +".xls");

wbCreat.write(fileOut);

fileOut.close();

}

/**

* 檢查此文件夾下有無同名，若有返回新文件名“文件名_重名”

* @param strPath        "D:\\excelTo\\"

* @param checkFilename   文件名

* @return   文件名

*/

private static String checkFileName(String strPath ,String checkFilename){

File file = new File (strPath);

for(File f : file.listFiles()){

if(f.getName().equals(checkFilename)){

checkFilename+=checkFilename+"_重名";

checkFileName(strPath,checkFilename);

break;

}

}

return checkFilename;

}

/**

* 復制原有sheet的合並單元格到新創建的sheet

*

* @param sheetCreat 新創建sheet

* @param sheet      原有的sheet

*/

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

}

}

/**

* 判斷單元格在不在合並單元格范圍內

*

* @param sheet

* @param intCellRow    被判斷的單元格的行號

* @param intCellCol    被判斷的單元格的列號

* @return TRUE 表示在，反之不在

* @throws IOException

*/

private static boolean isInMergerCellRegion(HSSFSheet sheet,

int intCellRow, int intCellCol) throws IOException {

boolean retVal = false;

int sheetMergerCount = sheet.getNumMergedRegions();

for (int i = 0; i < sheetMergerCount; i++) {

CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);

// 合並單元格CELL起始行

int firstRow = cra.getFirstRow();

// 合並單元格CELL起始列

int firstCol = cra.getFirstColumn();

// 合並單元格CELL結束行

int lastRow = cra.getFirstColumn();

// 合並單元格CELL結束列

int lastCol = cra.getLastColumn();

if (intCellRow >= firstRow && intCellRow <= lastRow) {

if (intCellCol >= firstCol && intCellCol <= lastCol) {

retVal = true;

break;

}

}

}

return retVal;

}

/**

* 去除字符串內部空格

*/

public static String removeInternalBlank(String s) {

// System.out.println("bb:" + s);

Pattern p = Pattern.compile("");

Matcher m = p.matcher(s);

char str[] = s.toCharArray();

StringBuffer sb = new StringBuffer();

for (int i = 0; i < str.length; i++) {

if (str[i] == ' ') {

sb.append(' ');

} else {

break;

}

}

String after = m.replaceAll("");

return sb.toString() + after;

}

}