joinexcelcontrol.java

来自「利用poi插件和Java语言在web中实现excel的合并功能」· Java 代码 · 共 394 行

package com.dao.join;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

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 JoinExcelControl {

	
	/**
	 * 合并excel数据
	 * 
	 * @param templatePath
	 *            模板路径
	 * @param filePath
	 *            要合并的多个文件路径,为一个数组
	 * @param templFilePath
	 *            需要一临时文件路径
	 * @param newFilePath
	 *            新建文件的路径
	 * @param rowSize
	 *            在读rowCount时要用到的,建议设为20-50.设的越大精度越高,
	 *            但效率低.可以使用ValidatorTemplate.VALIDATOR_TEMPLATE_ROW_SIZE
	 * @throws Exception
	 *             出问题则抛出异常
	 */
	public static void combination(String filePath[],
			String templFilePath, String newFilePath, int rowSize)
			throws Exception {
		// 若任一路径为空则抛异常
		System.out.println("entered combination");
		if (null == filePath || null == newFilePath)
			System.out.println("file null");
		// 目的相关
		// 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 {
			// 创建新模板文件,将所有源文件,合并写入新模板文件
			createTargetTemplate(filePath[0], 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(filePath[0]));
			templateWb = new HSSFWorkbook(templateFs);
			// 遍历每个file
			for (int i = 0; i < filePath.length; i++) {
				System.out.println("filePath.length is "+filePath.length);
				// 新建源文件
				sourceFs = new POIFSFileSystem(new FileInputStream(filePath[i]));
				sourceWb = new HSSFWorkbook(sourceFs);
				// 取得模板count数
				sheetCount = 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
					copyRow(templateSheet, sourceSheet, targetSheet,
							rowSize);
				}

			}
			// 写入最终文件
			targetWb.write(fileOut);

		} 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 static void createTargetTemplate(String templatePath, String newFilePath) {
		java.io.FileInputStream in = null;
		java.io.FileOutputStream out = null;
		System.out.println("entered 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) {
			System.out.println("path null");
		} 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 static void copyRow(HSSFSheet templateSheet, HSSFSheet sourceSheet,
			HSSFSheet targetSheet, int rowSize){
		System.out.println("entered C copyRow");
		// 取得模板的行数
		int templateRowCount = getRowCount(templateSheet, rowSize);
		System.out.println("templateRowCount is "+templateRowCount);
		// 取源文件(要合并到目标文件)的数量
		int sourceRowCount = getRowCount(sourceSheet, rowSize);
		System.out.println("sourceRowCount is "+sourceRowCount);
		// 取目标行数
		int targetRowCount = 0;
		// 以模板行数为初值值,以源的rowCount为终值,以行为单位copy每一行
		for (int i = 0; i < sourceRowCount; i++) {
			// 取源row
			HSSFRow sourceRow = sourceSheet.getRow(i);
			// 取目的row
			HSSFRow targetRow = targetSheet.getRow(i);
			if (sourceRow == null) {
				continue;
			}
			// 取目标row数量
			targetRowCount = 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 {
					copyCell(sourceCell, targetCell);
				} catch (Exception e) {
					System.out.println(e);
				}
			}
		}

	}

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

		}

		// 取得模板的行数
		int templateRowCount = getRowCount(templateSheet, rowSize);
		// 源行的count
		int sourceRowCount = 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 static void copyCell(HSSFCell sourceCell, HSSFCell targetCell) {
		System.out.println("entered C copyCell");
		// 如果源cell或目标cell为空,则抛异常
		if (null == sourceCell || null == targetCell)
			System.out.println("cell null");

		// 对应原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 static 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();
	}
	/**
	 * 获取所有sheet数量
	 * 
	 * @param wb
	 * @return 返回该sheet数量
	 */
	protected static int getSheetCount(HSSFWorkbook wb) {
		System.out.println("entered V getSheetCount");
		int count = 0;
		try {
			for (int i = 0; null != wb.getSheetAt(i); i++) {
				count++;
			}
		} catch (Exception e) {
			return count;
		}
		return count;
	}
	/**
	 * 取有内容的行数
	 * 
	 * @param sheet
	 *            取sheet的有内容的行数
	 * @return 返回int 行数
	 */
	protected static int getRowCount(HSSFSheet sheet, int rowSize) {
		System.out.println("entered V getRowCount");
		int total = 0;
		System.out.println("System.out.println(sheet.getFirstRowNum())="+sheet.getFirstRowNum());
		System.out.println("System.out.println(sheet.getLastRowNum())="+sheet.getLastRowNum());		
		for (int i = 0;; i++) {
			// 取前第i行
			HSSFRow row = sheet.getRow(i);			
			if (null == row) {
				total = i;
				break;
			}
			for(int j=0;j<row.getLastCellNum();j++)
			{
				System.out.println("第"+i+"行 第"+j+"列");
				System.out.println(row.getCell((short)j).getNumericCellValue());
			}
		}
		for (int i = total; i < total + rowSize; i++) {
			HSSFRow row = sheet.getRow(i);
			if (null != row)
				return -1;
		}

		return total;
	}
}