validatortemplate.java

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

package com.dao.join;

import java.io.FileInputStream;
import java.util.Calendar;
import java.util.GregorianCalendar;

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: 校验模板头是否符合条件
 * </p>
 * <p/> Description:
 * </p>
 * <p/> Date:2006-7-13 16:50:23
 * </p>
 * 
 * @author TEAM3 曲静波
 * @version 1.0
 */

public class ValidatorTemplate {

	public final static int VALIDATOR_TEMPLATE_ROW_SIZE = 30;

	/**
	 * 验证excel模板的有效性,支持纵、横向验证表头。
	 * 
	 * @param excelPath
	 *            excel路径
	 * @param rowSize
	 *            验证rowSize个行数
	 * @throws ExcelException 
	 * 
	 */
	public void validator(String excelPath, int rowSize) throws ExcelException {
		System.out.println("entered V validator and excelPath is "+excelPath);
		Calendar currTime=new GregorianCalendar();
		int h=currTime.get(currTime.HOUR_OF_DAY);
		int m=currTime.get(currTime.MINUTE);
		int s=currTime.get(currTime.SECOND);
	    System.out.println("The current time is:"+h+":"+m+":"+s+".");
		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;

		HSSFSheet sheet = null;
		HSSFRow row1 = null;
		HSSFRow row2 = null;
		// sheet的总数
		int sheetCount = 0;
		// 用于标识以前sheet是否为空的xls
		boolean isEmpty[] = null;
		int rowCount = 0;

		try {

			fs = new POIFSFileSystem(new FileInputStream(excelPath));
			wb = new HSSFWorkbook(fs);
			sheetCount = this.getSheetCount(wb);
			isEmpty = new boolean[sheetCount];
			// 判断每个sheet是否合法
			for (int sheetI = 0; sheetI < sheetCount; sheetI++) {
				sheet = wb.getSheetAt(sheetI);
				// 模板非法返回-1
				rowCount = this.getRowCount(sheet, rowSize);
				// 模板非法
				if (-1 == rowCount) {
					throw new ExcelException(
							ExcelExceptionMsg.TEMPLATE_ERROR1_MSG);
				}
				// 行的列数不为0判断
				if (0 != rowCount) {
					// 若整个列与最后一列不相等，则为false，不符合
					if (!this.topEqualsBottom(this.getTopRow(sheet), this
							.getBottomRow(sheet, rowCount))) {
						throw new ExcelException(
								ExcelExceptionMsg.TEMPLATE_ERROR2_MSG);
					}
				}
				// 如果是空sheet，需要判断以前是否有sheet为有内容的。
				/*
				 * if (0 == rowCount && !isEmpty) return false; else if (0 ==
				 * rowCount && isEmpty) continue;
				 */

				// 取第0行的列数
				int columnSize = this.getColumnCount(sheet.getRow(0));

				for (int i = 0; i < rowCount - 1; i++) {
					// 取前一行
					row1 = sheet.getRow(i);
					// 取后一行
					row2 = sheet.getRow(i + 1);

					int cellCount1 = this.getCellCount(row1);
					int cellCount2 = this.getCellCount(row2);
					// 如果前一行大于后一行或者后一行大于第一行列值则非法
					if (cellCount1 > cellCount2 || cellCount2 > columnSize) {
						throw new ExcelException(
								ExcelExceptionMsg.TEMPLATE_ERROR3_MSG);
					}

				}
				// 若行数不为0则，设为true,表示不为空
				if (0 != rowCount) {
					isEmpty[sheetI] = true;
				} else {
					isEmpty[sheetI] = false;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new ExcelException(ExcelExceptionMsg.TEMPLATE_ERROR4_MSG);
		}

		if (!this.isAvailable(isEmpty)) {
			throw new ExcelException(ExcelExceptionMsg.TEMPLATE_ERROR5_MSG);
		}

	}

	/**
	 * 取某row的列数,若在取列时遇到为null则 确认列数
	 * 
	 * @param row
	 *            取row的列数
	 * 
	 * @return 返回长度，若非法返回-1
	 */
	protected int getCellCount(HSSFRow row) {
		System.out.println("entered V getCellCount");
		// 若row为空则返回0
		if (null == row)
			return 0;
		int count = 0;
		HSSFCell cell = row.getCell((short) 0);
		for (int rowCount = 1; null != cell; rowCount++) {
			// 若为blank则证明是合并的
			if (HSSFCell.CELL_TYPE_BLANK != cell.getCellType()) {
				count++;
			}
			cell = row.getCell((short) rowCount);
		}

		return count;
	}

	/**
	 * 取有内容的行数
	 * 
	 * @param sheet
	 *            取sheet的有内容的行数
	 * @return 返回int 行数
	 */
	protected 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;
	}

	/**
	 * 取某行的列数
	 * 
	 * @param row
	 *            某行
	 * @return 返回某行的列数,int
	 */
	protected int getColumnCount(HSSFRow row) {
		System.out.println("entered V getColumnCount");
		// 若row为空则返回0
		if (null == row)
			return 0;
		int count = 0;
		HSSFCell cell = row.getCell((short) 0);
		for (int rowCount = 1; null != cell; rowCount++) {
			count++;
			cell = row.getCell((short) rowCount);
		}

		return count;
	}

	/**
	 * 获取所有sheet数量
	 * 
	 * @param wb
	 * @return 返回该sheet数量
	 */
	protected 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;
	}

	/**
	 * 多个sheet是否有效，若1,2,3个sheet,sheet1为有效，sheet3为有效，sheet2无效，则整个无效
	 * 
	 * @param isEmpty
	 *            boolean数组，sheet有效数组
	 * @return boolean值，是否有效
	 */
	protected boolean isAvailable(boolean isEmpty[]) {
		System.out.println("entered V isAvailable");
		int length = null != isEmpty ? isEmpty.length : 0;
		// 如果sheet1无效则整个无效
		if (length > 0) {
			if (!isEmpty[0])
				return false;
		} else {
			return false;
		}
		// 如果后一个为true，而前一个为false则为无效
		for (int i = 1; i < length - 1; i++) {
			if (!isEmpty[i] && isEmpty[i + 1]) {
				return false;
			}
		}
		return true;

	}

	/**
	 * 首行的列数必须与尾行列数相等
	 * 
	 * @param topRow
	 *            首行row
	 * @param bottomRow
	 *            尾行row
	 * @return 返回boolean值，相等返回true，反之false
	 */
	protected boolean topEqualsBottom(HSSFRow topRow, HSSFRow bottomRow) {
		System.out.println("entered V topEqualsBottom");
		if (null == topRow || null == bottomRow) {
			return false;
		}
		if (this.getColumnCount(topRow) != this.getCellCount(bottomRow)) {
			return false;
		}
		return true;
	}

	protected HSSFRow getTopRow(HSSFSheet sheet) {
		System.out.println("entered V getTopRow");
		return sheet.getRow(0);
	}

	protected HSSFRow getBottomRow(HSSFSheet sheet, int rowCount) {
		System.out.println("entered V getBottomRow");
		return sheet.getRow(rowCount - 1);
	}

}