excel表信息集锦，ExcelHandler

excel表信息汇总，ExcelHandler

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

/*  @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 blocke.printStackTrace();} catch (IOException e){// TODO Auto-generated catch blocke.printStackTrace();}}static public File createNewFile(String fileFullPath, String sheetName){//create fileFile file=new File(fileFullPath);try{file.createNewFile();} catch (IOException e1){System.out.println("new file"+fileFullPath+"create Error");}//connect to fileFileOutputStream outStream=null;try{outStream = new FileOutputStream(fileFullPath);} catch (FileNotFoundException e){System.out.println(fileFullPath+"not found");}//write new Excelfile to filetry{HSSFWorkbook myWorkbook=new HSSFWorkbook();myWorkbook.createSheet(sheetName);myWorkbook.write(outStream);} catch (IOException e){System.out.println(fileFullPath+"write failed");}return file;}}

/*  @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 filesfor (int fileIndex = 0; fileIndex < excelFiles.length; fileIndex++){inExcelFile=new ExcelHandler(excelFiles[fileIndex], 0);//得到列数colLengthint 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();}}