excel文件拷贝

package cn.sos.until;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

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.ss.util.CellRangeAddress;

poi.jar免费下载地址→http://download.csdn.net/download/cjava_math/9950387

/**

• 拷贝Excel通用工具类,(给一个源文件的路径,拿去直接用)
• @author lannan

*/
public class CopyExcel {

/**
 * 拷贝Excel到新的Excel中
 * 
 * @param fromPath:源excel的存放路径
 * @throws IOException 
 */
public static void CopyExcelToAntherExcel(String fromPath) throws IOException{
	
	//以当前拷贝时间为新excel文件名的后半部
	DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
	Calendar calendar = Calendar.getInstance();
	String dateName = df.format(calendar.getTime());
	
	
	String toPath = "D:\\";//新excel保存路径
	
	String excelName = "数据"+dateName;//新excel文件名
	
	//创建新的excel
	HSSFWorkbook wbCreat = new HSSFWorkbook();
	
	File file = new File(fromPath);
	//打开excel
	InputStream in = new FileInputStream(file);
	
	HSSFWorkbook wb = new HSSFWorkbook(in);
	
	for (int i = 0; i < wb.getNumberOfSheets(); i++) {
		
		HSSFSheet sheet = wb.getSheetAt(i);
		
		HSSFSheet sheetCreat = wbCreat.createSheet(sheet.getSheetName());
		
		//复制源表中的合并单元格
		MergerRegion(sheetCreat, sheet);
		int firstRow = sheet.getFirstRowNum();
		int lastRow = sheet.getLastRowNum();
		for (int j = firstRow; j <= lastRow; j++) {
			
			//创建新建excel sheet的行
			HSSFRow rowCreat = sheetCreat.createRow(j);
			
			//取的原有excel sheet的行
			HSSFRow row = sheet.getRow(j);
			
			//单元格样式
			int firstCell = row.getFirstCellNum();
			int lastCell = row.getLastCellNum();
			for (int k = firstCell; k <= lastCell; k++) {
				
				rowCreat.createCell(k);
				String strVal = "";
				if (row.getCell(k)==null) {
					
				}else {
					strVal = removeInternalBlank(row.getCell(k).getStringCellValue());
				}
				rowCreat.getCell(k).setCellValue(strVal);
				
			}
		}
	}
	
	FileOutputStream fileOut = new FileOutputStream(toPath+excelName+".xls");
	wbCreat.write(fileOut);
	fileOut.close();
	wbCreat.close();
	wb.close();
}





/**
 * 复制原有的sheet的合并单元格到新创建的sheet
 * @param sheetCreat : 新创建的sheet
 * @param sheet : 原有的sheet
 */
public static void MergerRegion(HSSFSheet sheetCreat,HSSFSheet sheet){
	
	int sheetMergerCount = sheet.getNumMergedRegions();
	
	for (int i = 0; i < sheetMergerCount; i++) {
		
		CellRangeAddress mergedRegionAt = sheet.getMergedRegion(i);
		
		sheetCreat.addMergedRegion(mergedRegionAt);
	}
}


/**
 * 去除字符串内部空格
 * @param s
 * @return
 */
public static String removeInternalBlank(String s){
	
	Pattern p = Pattern.compile("\\s*|\t|\r|\n");
	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;
}

}