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);//
		
		
		//.....
	}

}

评论

1 楼 shutingwang 2012-11-20  

不错