excel表信息汇总，ExcelHandler

最近写了一个用来汇总excel表内容的小程序（设计好的excel表），即先要求他们按照某种excel格式填写，然后提交上来，放到一个文件夹里，然后就可以将他们信息汇总到一个表格里。所以就把POI中excel表的操作进行了部分的封装（只封装string类型处理），以便自己以后使用。

ExcelHandler类如下：

/*
  @author wesleydeng 2011-9-6
*/
package POI;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
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;

public class ExcelHandler
{
	File file;
	HSSFWorkbook workbook;
	HSSFSheet sheet;

	public ExcelHandler(File file, int sheetIndex)//已经存在的
	{
		super();
		this.file=file;
		try
		{
			POIFSFileSystem inFS=new POIFSFileSystem(new FileInputStream(file));    
			workbook = new HSSFWorkbook(inFS);
		} catch (FileNotFoundException e)
		{
			e.printStackTrace();
		} catch (IOException e)
		{
			System.out.println(file.getName());
			e.printStackTrace();
		}    
   
		sheet = workbook.getSheetAt(sheetIndex);    
	}
	 
	public String getStringValue(int rowIndex,int colIndex)
	{
		HSSFRow myRow=sheet.getRow(rowIndex);
		if(myRow==null)
		{
			throw new IllegalArgumentException("cells("+rowIndex+","+colIndex+") not exist!");
		}
		HSSFCell myCell=myRow.getCell(colIndex);
		if(myCell==null)
		{
			throw new IllegalArgumentException("cells("+rowIndex+","+colIndex+") not exist!");
		}
		
		return myCell.getStringCellValue();
	}
	
	public void setStringValue(int rowIndex,int colIndex,String value)
	{
		HSSFRow myRow=sheet.getRow(rowIndex);
		if(myRow==null)
		{
			myRow=sheet.createRow(rowIndex);
		}
		HSSFCell myCell=myRow.getCell(colIndex);
		if(myCell==null)
		{
			myCell=myRow.createCell(colIndex);
		}
		myCell.setCellValue(value);
	}
	
	public int getColLength(int rowIndex)
	{
		HSSFRow myRow=sheet.getRow(rowIndex);
		if(myRow==null)
		{
			throw new IllegalArgumentException("row:"+rowIndex+" not exist!");
		}
		return (int)sheet.getRow(rowIndex).getLastCellNum();
	}
	public void saveAndClose()
	{
		try
		{
			FileOutputStream outFile = new FileOutputStream(file);
			workbook.write(outFile);
			outFile.close();
		} catch (FileNotFoundException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	static public File createNewFile(String fileFullPath, String sheetName)
	{
		//create file
		File file=new File(fileFullPath);
		try
		{
			file.createNewFile();
		} catch (IOException e1)
		{
			System.out.println("new file"+fileFullPath+"create Error");
		}
		
		//connect to file
		FileOutputStream outStream=null;
		try
		{
			outStream = new FileOutputStream(fileFullPath);
		} catch (FileNotFoundException e)
		{
			System.out.println(fileFullPath+"not found");
		}
		
		//write new Excelfile to file
		try
		{
			HSSFWorkbook myWorkbook=new HSSFWorkbook();
			myWorkbook.createSheet(sheetName);
			myWorkbook.write(outStream);
		} catch (IOException e)
		{
			System.out.println(fileFullPath+"write failed");
		}
		return file;
	}
	
	
}

 

 使用该ExcelHandler的是InfoCollector：

/*
  @author wesleydeng 2011-9-5
*/
package POI;

import java.io.File;

public class InfoCollector
{
	public static void main(String[] args)
	{
	
	
	File folder=new File("C:\\Documents and Settings\\wesley\\桌面\\08校友通讯录");
	File[] excelFiles=folder.listFiles();
	
	ExcelHandler inExcelFile=null;
	File outfile=ExcelHandler.createNewFile(folder.getParent()+"\\通讯录汇总.xls", "sheet1");
	
	ExcelHandler outExcelFile=new ExcelHandler(outfile,0);
	String tempValue;
	//do loop to traverse each files
	for (int fileIndex = 0; fileIndex < excelFiles.length; fileIndex++)
	{
		inExcelFile=new ExcelHandler(excelFiles[fileIndex], 0);
		//得到列数colLength
		int colLength=inExcelFile.getColLength(0);
		if(fileIndex==0)//即获得“列名称”
		{
			for (int colIndex = 0; colIndex < colLength; colIndex++)
			{
				tempValue=inExcelFile.getStringValue(0, colIndex);
				outExcelFile.setStringValue(0, colIndex, tempValue);
			}
		}
		
		for (int colIndex = 0; colIndex < colLength; colIndex++)
		{
			//输入表第一行的数据（即excel的第二行）
			tempValue=inExcelFile.getStringValue(1, colIndex);
			//汇总表的fileindex+1行
			outExcelFile.setStringValue(fileIndex+1, colIndex, tempValue);
		}
	}
	System.out.println("OK");
	outExcelFile.saveAndClose();

	}
}