Apache POI SpreadSheet的一些简单应用（二）

我用的版本是 poi-3.5,您可以去官方上下载。希望对大家能有些帮助:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

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


public class XZou {

	/**
	 * copy一个Excel中指定的sheet而后制作成另外一个Excel文件
	 * @param srcPath 源路径
	 * @param srcFileName 原文件名
	 * @param sheetNames 指定的sheet名称集合
	 * @return 新的文件对象
	 */
	public static File copySpecialSheet(String srcPath,String srcFileName,String[] sheetNames,String targetPath){

		srcPath += "/";

		File src = new File(srcPath + srcFileName);

		try {

			HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(src)));//load src excel

			int sheetCounts = wb.getNumberOfSheets();//总的sheet数量

			List<Integer> xb = new ArrayList<Integer>();//下标值集合

			int xiaBiao = 0;

			for(int i = 0; i<sheetCounts; i++){

				String sName = wb.getSheetName(i);

				boolean boo = false;

				for(String name: sheetNames){

					if(name.equals(sName)){

						boo = true;

						break;
					}
				}

				if(boo){
					xiaBiao++;

				}else{
					xb.add(xiaBiao);
				}

			}

			for(int flag: xb){

				wb.removeSheetAt(flag);//删除指定的sheet的下标
			}

			OutputStream out = null;

			try{

				File targetFile = new File(targetPath + "/" + System.currentTimeMillis() + ".xls");//在targetPath下生成新的excel文件。

				out = new FileOutputStream(targetFile);
				/*
				sheetCounts = wb.getNumberOfSheets();

				for(int i = 0; i<sheetCounts; i++){
					//wb.setSheetHidden(i, true);// 把所有的sheet工作表都给隐藏掉
					wb.setSheetHidden(i,2);//把所有的sheet工作表都给隐藏掉,0=显示 1=隐藏 2=非常隐秘(我在Excel中找不到。但是程序找得到~_~)
				}
				*/
				wb.write(out);

				return targetFile;

			}catch(Exception ex){
				ex.printStackTrace();

				throw new RuntimeException("生成新Excel文件失败:",ex);
			}finally{
				try{
					if(out!=null)
						out.close();
				}catch(IOException ex){

					ex.printStackTrace();
				}
			}
		} catch (FileNotFoundException e) {

			throw new RuntimeException("文件不存在" ,e);
		} catch (IOException e) {

			throw new RuntimeException("加载XLS文件出现异常:",e);
		}

	}

	/**
	 * 打印一个sheet中的每个合并单元格区的位置
	 * @param sheet
	 */
	public static void sysRange(HSSFSheet sheet){

		int count = sheet.getNumMergedRegions();//找到当前sheet单元格中共有多少个合并区域

		for(int i = 0; i<count; i++){

			CellRangeAddress range = sheet.getMergedRegion(i);//一个合并单元格代表 CellRangeAddress

			System.out.println(range.formatAsString());//打印合并区域的字符串表示方法 如： B2:C4

			System.out.println(range.getFirstRow() + "." + range.getLastRow() + ":" 
					+ range.getFirstColumn() + "." + range.getLastColumn() );//打印起始行、结束行、起始列、结束列

		}

	}

	/**
	 * 根据一行找出有效的起始列号
	 * @param row
	 * @return
	 */
	public int getStartCell(HSSFRow row){

		if(row==null){

			throw new RuntimeException("无效行");
		}
		int start = 0;

		for(int i = 0; i<row.getLastCellNum(); i++){
			HSSFCell cell = row.getCell(i);

			if(cell!=null){

				if(cell.getCellType()!=HSSFCell.CELL_TYPE_BLANK){

					start = i;

					break;				
				}

			}
		}
		return start;
	}


	/**
	 * 根据一行找出有效的结尾列,空白列不算、但是在合并区域时算
	 * @param row
	 * @return
	 */
	public int getLastCell(HSSFRow row){

		if(row==null){

			throw new RuntimeException("无效行");
		}

		HSSFSheet sheet = row.getSheet();

		int merged = sheet.getNumMergedRegions();//获取单元格区域数

		int end = 0;

		for(int x = row.getLastCellNum() - 1; x>=0; x--){

			HSSFCell cell = row.getCell(x);//获取列

			if(cell==null)
				continue;

			if(cell.getCellType()==HSSFCell.CELL_TYPE_BLANK){//空白单元格

				int rowNumber = cell.getRowIndex();

				int cellNumber = cell.getColumnIndex();

				boolean flag = false;

				for(int i = 0; i<merged; i++){

					CellRangeAddress rane = sheet.getMergedRegion(i);

					if(rowNumber>=rane.getFirstRow()&&rowNumber<=rane.getLastRow()){//确立在行里面

						if(cellNumber>=rane.getFirstColumn()&&cellNumber<=rane.getLastColumn()){//确立在列里面

							flag = true;

							break;
						}
					}


				}

				if(flag){//说明当前单元格是空白单元格并且在合并区域中.可以认定为有效结束列 
					end = x;
					break;
				}

			}else{//不为空白单元格

				end = x;

				break;
			}

		}


		return end;
	}


	/**
	 * 判断一行是否为空行或指定的前几列
	 * @param row
	 * @return
	 */
	public boolean checkRowIsNull(HSSFRow row,int ...cd){

		boolean boo = true;

		if(row==null){
			return boo;
		}

		int length = 0;

		int j = 0;

		if(cd.length==0){
			j = 0;
			length = row.getLastCellNum();
		}else{
			j = 1;
			length = cd[0];
		}

		for(; j<length; j++){

			HSSFCell cell = row.getCell(j);

			if(cell!=null){

				if(cell.getCellType()!=HSSFCell.CELL_TYPE_BLANK){

					if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){
						if(!cell.getRichStringCellValue().toString().trim().equals("")){
							boo = false;
							break;
						}
					}else{
						boo = false;
					}

				}

			}

		}
		return boo;

	}

	/**
	 * 根据单元格坐标得出相应的值 如：B3
	 * @param cellRowCode :坐标
	 * @param sheet ：工作表
	 * @return 返回值
	 */
	public Serializable getValueByCellCode(String cellRowCode,HSSFSheet sheet){

	    String thisSheetName = sheet.getSheetName();

	    CellReference ref =  new CellReference(cellRowCode);

		int xy[] = {ref.getRow(),ref.getCol()};

		HSSFCell cell = sheet.getRow(xy[1]-1).getCell(xy[0]);

		switch (cell.getCellType()){

			case HSSFCell.CELL_TYPE_BLANK:
				return "";			
			case HSSFCell.CELL_TYPE_NUMERIC:
				return (cell.getNumericCellValue());

			case HSSFCell.CELL_TYPE_STRING:
				return cell.getRichStringCellValue().toString() ;
			case HSSFCell.CELL_TYPE_FORMULA:
			    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet.getWorkbook());   
			    try{
                    evaluator.evaluateFormulaCell(cell);//检测公式有效性
                }catch(java.lang.IllegalArgumentException ex){
                    throw new RuntimeException("错误的单元格["+thisSheetName+"->"+cellRowCode+"]");
                }
                if(evaluator.evaluateFormulaCell(cell)==HSSFCell.CELL_TYPE_ERROR){

                    throw new RuntimeException("错误的单元格["+thisSheetName+"->"+cellRowCode+"]");
                }
                if(evaluator.evaluateFormulaCell(cell)==HSSFCell.CELL_TYPE_NUMERIC){
                    return cell.getNumericCellValue();
                }else if(evaluator.evaluateFormulaCell(cell)==HSSFCell.CELL_TYPE_STRING){
                    return cell.getRichStringCellValue().toString();
                }

			case HSSFCell.CELL_TYPE_BOOLEAN:
				return (cell.getBooleanCellValue());
			default: 
				System.out.print("*");
				break;
		}

		return null;

	}

	public static void main(String[] args) throws Exception {

		//copySpecialSheet("c:/","test.xls",new String[]{"Sheet1","Sheet2"},"d:/");//把c盘下的test.xls文件中的Sheet1和Sheet2工作表拷贝出来然后生成一个新的Excel放到d盘下



		HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream("c:/test.xls")));
		HSSFSheet sheet = wb.getSheet("测试");//得到名字叫 测试 的一个工作表。
		sysRange(sheet);//


		//.....
	}

}