combinationfileexcel.java

利用poi插件和Java语言在web中实现excel的合并功能

package com.dao.join;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;


import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;

/**
 * <p/> Title: 经过验证有效性后,合并多个经验证的excel文件
 * </p>
 * <p/> Description:
 * </p>
 * <p/> Date:2006-7-17 10:19:29
 * </p>
 * 
 * @author TEAM3 曲静波
 * @version 1.0
 */

public class CombinationFileExcel {

	private ValidatorTemplate vt = new ValidatorTemplate();

	/**
	 * 合并excel数据
	 * 
	 * @param templatePath
	 *            模板路径
	 * @param filePath
	 *            要合并的多个文件路径,为一个数组
	 * @param templFilePath
	 *            需要一临时文件路径
	 * @param newFilePath
	 *            新建文件的路径
	 * @param rowSize
	 *            在读rowCount时要用到的,建议设为20-50.设的越大精度越高,
	 *            但效率低.可以使用ValidatorTemplate.VALIDATOR_TEMPLATE_ROW_SIZE
	 * @throws Exception
	 *             出问题则抛出异常
	 */
	public void combination(String templatePath, String filePath[],
			String templFilePath, String newFilePath, int rowSize)
			throws Exception {
		// 若任一路径为空则抛异常
		System.out.println("entered C combination");
		if (null == templatePath || null == filePath || null == newFilePath)
			throw new ExcelException(
					ExcelExceptionMsg.TEMPLATE_FILE_NEWFILE_PATH_NULL_ERROR_MSG);
		// 目的相关
		// FileOutputStream targetFs = null;
		FileOutputStream fileOut = null;
		POIFSFileSystem targetFs = null;
		HSSFWorkbook targetWb = null;
		HSSFSheet targetSheet = null;
		FileInputStream templFileInputStream = null;

		// 模板相关POI
		POIFSFileSystem templateFs = null;
		HSSFWorkbook templateWb = null;
		HSSFSheet templateSheet = null;
		// 源相关POI
		POIFSFileSystem sourceFs = null;
		HSSFWorkbook sourceWb = null;
		HSSFSheet sourceSheet = null;

		// 取模板count
		int sheetCount = 0;

		try {
			// 创建新模板文件,将所有源文件,合并写入新模板文件
			this.createTargetTemplate(templatePath, templFilePath);
			// 最终合并形成的文件
			fileOut = new FileOutputStream(newFilePath);
			// 目的相关
			// targetFs = new FileOutputStream(newFilePath);
			templFileInputStream = new FileInputStream(templFilePath);
			targetFs = new POIFSFileSystem(templFileInputStream);
			targetWb = new HSSFWorkbook(targetFs);

			// 模板相关
			templateFs = new POIFSFileSystem(new FileInputStream(templatePath));
			templateWb = new HSSFWorkbook(templateFs);
			// 遍历每个file
			for (int i = 0; i < filePath.length; i++) {
				// 新建源文件
				sourceFs = new POIFSFileSystem(new FileInputStream(filePath[i]));
				sourceWb = new HSSFWorkbook(sourceFs);
				// 取得模板count数
				sheetCount = vt.getSheetCount(templateWb);
				System.out.println("template's sheetcount is "+sheetCount);
				// 遍历所有sheet
				for (int j = 0; j < sheetCount; j++) {
					// 设置目标sheetName为原sheetName

					// 因为模板是被复制过的.所以不用设置sheetName
					// targetWb.setSheetName(j, sourceWb.getSheetName(j));

					// 取得源sheet
					sourceSheet = sourceWb.getSheetAt(j);
					// 取目标sheet
					targetSheet = targetWb.getSheetAt(j);
					// 取模板sheet
					templateSheet = templateWb.getSheetAt(j);
					// 设置列高度
					// this.setColumnHeight(templateSheet, sourceSheet,
					// targetSheet, rowSize);
					// 复制源的cell值到目的cell
					this.copyRow(templateSheet, sourceSheet, targetSheet,
							rowSize);
				}

			}
			// 写入最终文件
			targetWb.write(fileOut);
			
			/*Workbooks(1).SaveAs( "filename.html ",44);//保存为html 
Workbooks(1).SaveAs( "filename.html ",44);//保存为mhtml*/

		} catch (Exception e) {
			throw e;
		} finally {
			// 需要先关闭临时文件,才可删除文件
			templFileInputStream.close();
			fileOut.close();
//			//删除临时文件
//			File file = new File(templFilePath);
//			if (file.exists())
//				file.delete();
			

		}

	}

	/**
	 * 用源模板文件创建新的目标整合文件
	 * 
	 * @param templatePath
	 *            源模板文件
	 * @param newFilePath
	 *            目标文件
	 * @throws ExcelException
	 *             在文件路径不正确时会抛异常
	 */
	protected void createTargetTemplate(String templatePath, String newFilePath)
			throws ExcelException {
		java.io.FileInputStream in = null;
		java.io.FileOutputStream out = null;
		System.out.println("entered C createTargetTemplate");
		try {
			in = new java.io.FileInputStream(templatePath);
			out = new java.io.FileOutputStream(newFilePath);
			byte Buff[] = new byte[1024];
			int len;
			while ((len = in.read(Buff)) > -1) {
				out.write(Buff, 0, len);
			}

		} catch (Exception e) {
			throw new ExcelException(
					ExcelExceptionMsg.TEMPLATE_NEWFILE_PATH_NULL_ERROR_MSG);
		} finally {
			try {
				in.close();
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}

		}

	}

	/**
	 * 复制行 从源sheet复制到目的sheet
	 * 
	 * @param templateSheet
	 *            模板sheet
	 * @param sourceSheet
	 *            源sheet
	 * @param targetSheet
	 *            目标sheet
	 * @param rowSize
	 *            取rowCount时用
	 * @throws ExcelException
	 *             当调用copyCell时出问题抛出来
	 */
	protected void copyRow(HSSFSheet templateSheet, HSSFSheet sourceSheet,
			HSSFSheet targetSheet, int rowSize) throws ExcelException {
		System.out.println("entered C copyRow");
		// 取得模板的行数
		int templateRowCount = vt.getRowCount(templateSheet, rowSize);
		// 取源文件(要合并到目标文件)的数量
		int sourceRowCount = vt.getRowCount(sourceSheet, rowSize);
		// 取目标行数
		int targetRowCount = 0;
		// 以模板行数为初值值,以源的rowCount为终值,以行为单位copy每一行
		for (int i = templateRowCount; i < sourceRowCount; i++) {
			// 取源row
			HSSFRow sourceRow = sourceSheet.getRow(i);
			// 取目的row
			HSSFRow targetRow = targetSheet.getRow(i);
			if (sourceRow == null) {
				continue;
			}
			// 取目标row数量
			targetRowCount = vt.getRowCount(targetSheet, rowSize);
			// 新建目标row
			targetRow = targetSheet.createRow(targetRowCount);
			// 设置row的高度
			targetRow.setHeight(sourceRow.getHeight());
			// 遍历每一个cell
			for (int j = sourceRow.getFirstCellNum(); j < sourceRow
					.getLastCellNum(); j++) {

				HSSFCell sourceCell = sourceRow.getCell((short) j);
				if (sourceCell == null) {
					continue;
				}
				// 创建cell
				HSSFCell targetCell = targetRow.createCell((short) j);
				// 设置编码方式
				//targetCell.setEncoding(sourceCell.getEncoding());
				// 设置样式
				targetCell.setCellStyle(sourceCell.getCellStyle());
				// 设置类型
				targetCell.setCellType(sourceCell.getCellType());
				// 复制cell的值,若出问题,抛异常
				try {
					this.copyCell(sourceCell, targetCell);
				} catch (ExcelException e) {
					throw e;
				}
			}
		}

	}

	/**
	 * 设置目标sheet为模板的列宽
	 * 
	 * @param templateSheet
	 *            模板sheet
	 * @param sourceSheet
	 *            源sheet
	 * @param targetSheet
	 *            目标sheet
	 * @param rowSize
	 *            取行时用到的
	 * @throws ExcelException
	 *             当sheet为空时抛异常
	 */
	protected void setColumnHeight(HSSFSheet templateSheet,
			HSSFSheet sourceSheet, HSSFSheet targetSheet, int rowSize)
			throws ExcelException {
		System.out.println("entered C setColumnHeight");
		// 若模板sheet或目标sheet为空,则抛异常
		if (null == templateSheet || null == targetSheet || null == sourceSheet) {
			throw new ExcelException(
					ExcelExceptionMsg.SOURCE_TARGET_ROW_ERROR_NULL_MSG);

		}

		// 取得模板的行数
		int templateRowCount = vt.getRowCount(templateSheet, rowSize);
		// 源行的count
		int sourceRowCount = vt.getRowCount(sourceSheet, rowSize);

		// 通过模板行数遍历
		for (int i = templateRowCount; i < sourceRowCount; i++) {
			// 取得每行
			HSSFRow sourceRow = sourceSheet.getRow(i);
			for (int j = sourceRow.getFirstCellNum(); j < sourceRow
					.getLastCellNum(); j++) {
				// 设置每列的宽度
				targetSheet.setColumnWidth((short) j, sourceSheet
						.getColumnWidth((short) j));
			}
		}

	}

	// 复制cell
	private void copyCell(HSSFCell sourceCell, HSSFCell targetCell)
			throws ExcelException {
		System.out.println("entered C copyCell");
		// 如果源cell或目标cell为空,则抛异常
		if (null == sourceCell || null == targetCell)
			throw new ExcelException(
					ExcelExceptionMsg.SOURCE_TARGET_CELL_ERROR_NULL_MSG);

		// 对应原cell,写入targetCell
		switch (sourceCell.getCellType()) {
		case HSSFCell.CELL_TYPE_BOOLEAN:
			targetCell.setCellValue(sourceCell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_ERROR:
			targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			// 转换ATTR(semiVolatile)删掉
			targetCell
					.setCellFormula(parseFormula(sourceCell.getCellFormula()));
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			targetCell.setCellValue(sourceCell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_STRING:
			targetCell.setCellValue(sourceCell.getStringCellValue());
			break;

		}

	}

	/**
	 * POI对Excel公式的支持是相当好的，但是我发现一个问题，如果公式里面的函数不带参数，
	 * 比如now()或today()，那么你通过getCellFormula()取出来的值就是
	 * now(ATTR(semiVolatile))和today(ATTR(semiVolatile))，
	 * 这样的值写入Excel是会出错的，这也是我上面copyRow的函数在写入公式前要调用parseFormula的原因，
	 * parseFormula这个函数的功能很简单，就是把ATTR(semiVolatile)删掉，
	 * 
	 * @param pPOIFormula
	 *            要转换的串
	 * @return 返回转换后的字符串
	 */
	private String parseFormula(String pPOIFormula) {
		System.out.println("entered C parseFormula");
		final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
		StringBuffer result = null;
		int index;

		result = new StringBuffer();
		index = pPOIFormula.indexOf(cstReplaceString);
		if (index >= 0) {
			result.append(pPOIFormula.substring(0, index));
			result.append(pPOIFormula.substring(index
					+ cstReplaceString.length()));
		} else {
			result.append(pPOIFormula);
		}

		return result.toString();
	}
    
	public static void exceltoHTML(String excelFilename, String htmlFilename) 
    { 
        ComThread.InitSTA(); 
        ActiveXComponent xl = new ActiveXComponent("Excel.Application"); 
        
        try 
        { 
            File fo = new File(htmlFilename); 
            if (fo.exists()&&fo.canRead()){ 
                fo.delete(); 
            }            
            xl.setProperty("Visible", new Variant(false)); 
            Dispatch workbooks = xl.getProperty("Workbooks").toDispatch(); 
            Dispatch workbook = Dispatch.invoke(workbooks, "Open", Dispatch.Method, new Object[] 
            {excelFilename, new Variant(false), new Variant(true) },// 是否以只读方式打开 
                    new int[1]).toDispatch(); 
            Dispatch.invoke(workbook, "SaveAs", Dispatch.Method, new Object[] 
            {htmlFilename, new Variant(44) }, new int[1]); // 44,45 
            Dispatch.call((Dispatch) workbook, "Close", new Variant(false)); 
        } catch (Exception e) 
        { 
            e.printStackTrace(); 
        } finally 
        { 
            xl.invoke("Quit", new Variant[] 
            {}); 
            ComThread.Release(); 
        } 
    } 
}