excel文件拷贝

小鱼儿 2023-10-17 15:28 111阅读 0赞

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