poi包 读写excel文件

package com.hzqy.web.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

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.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.hzqy.commons.log.JLogger;
import com.hzqy.commons.log.LoggerFactory;
import com.hzqy.web.utils.ColumnConstants;
import com.hzqy.web.utils.JobHelper;

public class ExcelDeal {
 private static JLogger LOG = LoggerFactory.getLogger(ExcelDeal.class);
 
 
 /**
  * EXCEL解析
  *
  * @param  excelFile
  * @return  hashmap<sheetName, data[][]>
  */
 public HashMap<String,String[][]> readExcel(File sourceFile){
  
  long a = System.currentTimeMillis();
  HashMap<String,String[][]> map = new HashMap<String,String[][]>();
  String[][] value = null ;
   FileInputStream file = null;
  try{
     file = new FileInputStream(sourceFile);
    POIFSFileSystem  fs = new POIFSFileSystem(file);
    HSSFWorkbook hwb = new HSSFWorkbook(fs);   //创建工作簿
    int sheetNum = hwb.getNumberOfSheets();
   
    for(int s=0 ; s < sheetNum ; s++){
     HSSFSheet hst = hwb.getSheetAt(s);  //创建sheet
     String sheetName = "";
    if(hst==null){
     continue ;
    }
     int end = getRang(hst,s); //合并列为12是最后一行
     int maxCol = getMaxCol(hst,end);//最大的列数
    
    
     value = new String[end][maxCol + 1];
      sheetName = hwb.getSheetName(s);
     
      for(int r=0 ; r < end;r++){
       HSSFRow row = hst.getRow(r); //获得行
      
        if(row == null){
         continue ;
        }
       
        for(short c=0 ; c < row.getPhysicalNumberOfCells();c++){
         HSSFCell cell = row.getCell(c);//获得单元格
        
         if(cell == null){
          continue ;
         }
         value[r][c] = getCellValue(cell);
        
        }
        
      }
     if(sheetName != null && value != null){
         map.put(sheetName, value);
     }
    }
   
    file.close();
    LOG.debug("解析EXCEL用时：" +(System.currentTimeMillis()-a ));
    return map ;
   
    }catch(IOException e){
     LOG.error("EXCEL文件解析失败 " + e);
    }
        return map;
    }
 
 /**
  * CELL类型转换
  *
  */
  private  String getCellValue(HSSFCell cell) {
         int type = cell.getCellType();
         switch (type) {
          case HSSFCell.CELL_TYPE_BOOLEAN:
              return String.valueOf(cell.getBooleanCellValue());
          case HSSFCell.CELL_TYPE_NUMERIC:
              return String.valueOf((long)cell.getNumericCellValue());
          case HSSFCell.CELL_TYPE_FORMULA:
              return cell.getCellFormula();
          case HSSFCell.CELL_TYPE_STRING:
              return cell.getRichStringCellValue()== null ? null : cell.getRichStringCellValue().getString();
          default:
              break;
         }
         return null;
     }
 
  /**
   * 取得sheet中最大的列数
   */
     private int getMaxCol(HSSFSheet hst , int endRow){
    int maxCol = hst.getRow(0).getPhysicalNumberOfCells();
    int colNum = 0 ;
    for(int max = 0 ; max < endRow-1; max ++){
     if(hst.getRow(max)!=null ){
      colNum = hst.getRow(max).getPhysicalNumberOfCells();
      if(maxCol<colNum){
       maxCol = colNum;
      }
     }
    }
    return  maxCol ;
  }
 
 /**
  * 查找合并单元格 并返回最后一行的行序列
  */
  private  int getRang(HSSFSheet sheet ,int sheetNum) {
   int end = 0 ;
   List<Integer> list = new ArrayList();
   for (int i = 0; i < sheet.getNumMergedRegions(); i++){
//           LOG.debug("第"+i+"个合并单元格");
           Region region = sheet.getMergedRegionAt(i);
           int row=region.getRowTo()-region.getRowFrom()+1;
     int col=region.getColumnTo()-region.getColumnFrom()+1;
     
           if(sheetNum == 0 && col == ColumnConstants.CONTENT_COLS){
       list.add(new Integer(region.getRowFrom()));
     }else if(sheetNum == 1 && col == ColumnConstants.SET_COLS){
       list.add(new Integer(region.getRowFrom()));
     }
      }
  
   if(sheetNum == 0 && list.size() == 3){
    end = Math.max(Math.max(list.get(0), list.get(1)),Math.max(list.get(1), list.get(2)))-1;
   }else if(sheetNum == 1 && list.size() == 2){
    end = Math.max(list.get(0), list.get(1));
   }else{
    end = sheet.getLastRowNum();
   }
  
   return  end ;   
  }
 
  /**
   * 拷贝并修改新的EXCEL文件
   */
  public void writeResult(File excelFile){
   
     String copyExcelPath = jc.getDealedExcelPath(excelFile) ;

     try{
       FileOutputStream outFile = new FileOutputStream(copyExcelPath);
       FileInputStream inFile = new FileInputStream(excelFile);
       POIFSFileSystem ps = new POIFSFileSystem(inFile);
       HSSFWorkbook  hwb = new HSSFWorkbook(ps);
      
       //填写内容的处理结果
       HSSFSheet hst = hwb.getSheetAt(0); 
         for(int r = 0 ; r < hst.getPhysicalNumberOfRows() ; r++){
          if(ReadExcelData.allBookMap.get(r) != null){
          HSSFRow row = hst.getRow(r);
          if(row != null){
           row.createCell((short)ColumnConstants.CONTENT_RESULT).setCellValue(ReadExcelData.allBookMap.get(r));
          }
         
          }
           }
        
      //填写内容详细的处理结果
       hst = hwb.getSheetAt(1); 
            for(int r = 0 ; r < hst.getPhysicalNumberOfRows() ; r++){
           if(ReadExcelData.allSetMap.get(r) != null){
               HSSFRow row = hst.getRow(r);
           if(row != null){
            row.createCell((short)ColumnConstants.SET_RESULT).setCellValue(ReadExcelData.allSetMap.get(r));
           }
               
           }
         }  
       hwb.write(outFile);
       inFile.close();
       outFile.close();
      
     }catch(IOException e){
      e.printStackTrace();
     }
    
  }

 
}