java操作excel文件(一)

这里我们项目用到POI组件(是Apache组件的一个开源项目，其开发目的就是让Java语言可以对Microsoft的Office系列办公软件进行读/写操作)来操作Excel,要用到第三方的jar包，我们项目中用的是poi-3.0-rc4-20070503.jar(已上传到我的资源，可下载)

package com.mys.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
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;

 

/**
 * Excel文件解析类

 * @param <T>
 */
public class ExcelReader {
 private static Logger log = Logger.getLogger(ExcelReader.class);
 private File excelFile;   //需要解析的Excel文件
 private InputStream input;  //需要解析的文件流
 private HSSFWorkbook wb;
 
 /**
  * 文件参数构造方法
  * @param excelFile
  */
 public ExcelReader(File excelFile) {
  this.excelFile = excelFile;
  try {
   wb = new HSSFWorkbook(new FileInputStream(excelFile));
  } catch (FileNotFoundException e) {
   log.error("", e);
  } catch (java.io.IOException e) {
   log.error("", e);
  }
 }
 
 /**
  * 获取需要解析的文件
  * @return
  */
 public File getFile () {
  return excelFile;
 }
 
 /**
  * 获取需要解析的文件流
  * @return
  */
 public InputStream getInputStream(){
  return input;
 }
 
 /**
  * 文件流参数构造方法
  * @param input
  */
 public ExcelReader(InputStream input){
  this.input = input;
  try {
   wb = new HSSFWorkbook(input);
  } catch (java.io.IOException e) {
   log.error("", e);
  }
 }
 
 /**
  * 获取Excel文件包含的sheet数
  * @return
  */
 public int numOfSheets(){
  return wb.getNumberOfSheets();
 }
 
 /**
  * 解析Excel文件
  * 以Object对象
  * 集合返回
  * @param trimFirst
  * @return
  */
 public List<Object[]> parse2ObjArrays(boolean trimFirst){
  List<Object[]> result = new ArrayList<Object[]>();
  //sheet数目
  int sc = numOfSheets();
  for(int i=0;i<sc;i++){
   HSSFSheet sheet = wb.getSheetAt(i);
   if(sheet == null || sheet.getLastRowNum() == 0){
    continue;
   }
   //最后一行行号
   int rc = sheet.getLastRowNum();
   //是否去除第一行
   int j = trimFirst?1:0;
   for(;j<=rc;j++){
    HSSFRow row = sheet.getRow(j);
    if(row == null || row.getLastCellNum() < 0){
     continue;
    }
//    if(row.getCell((short)0)==null || row.getCell((short)0).toString() == null){
//     continue;
//    }
    //最后一列列号
    int cc = row.getLastCellNum();
    Object[] objs = new Object[cc+1];
    for(int k=0;k<=cc;k++){
     short cellIndex = (short)k;
     HSSFCell cell = row.getCell(cellIndex);
     if(cell == null){
      objs[k] = null;
      continue;
     }
     int cellType = cell.getCellType();
     //根据单元格数据类型转型数据
     switch(cellType){
      case HSSFCell.CELL_TYPE_BLANK:  //空白
       objs[k] = null;
       break;
      case HSSFCell.CELL_TYPE_BOOLEAN: //布尔型
       objs[k] = new Boolean(cell.getBooleanCellValue());
       break;
      case HSSFCell.CELL_TYPE_ERROR:  //错误
       objs[k] = null;
       break;
      case HSSFCell.CELL_TYPE_NUMERIC: //数字类型
       objs[k] = new Long((long)cell.getNumericCellValue());
       break;
      case HSSFCell.CELL_TYPE_STRING:  //字符串类型
       objs[k] = new String(cell.toString());
     }
    }
    result.add(objs);
   }
  }
  return result;
 }
 
 /**
  * 获取文档第一条记录
  * @return
  */
 public Object[] getFirstRow(){
  Object[] result = null;
  HSSFSheet sheet = wb.getSheetAt(0);
  if(sheet == null){
   return null;
  }
  HSSFRow row = sheet.getRow(0);
  if(row == null){
   return null;
  }
  int cellCount = row.getLastCellNum()+1;
  result = new Object[cellCount];
  for(int i=0;i<cellCount;i++){
   short cellIndex = (short)i;
   HSSFCell cell = row.getCell(cellIndex);
   if(cell == null){
    result[i] = null;
    continue;
   }
   int cellType = cell.getCellType();
   //根据单元格数据类型转型数据
   switch(cellType){
    case HSSFCell.CELL_TYPE_BLANK:  //空白
     result[i] = null;
     break;
    case HSSFCell.CELL_TYPE_BOOLEAN: //布尔型
     result[i] = new Boolean(cell.getBooleanCellValue());
     break;
    case HSSFCell.CELL_TYPE_ERROR:  //错误
     result[i] = null;
     break;
    case HSSFCell.CELL_TYPE_NUMERIC: //数字类型
     result[i] = new Long((long)cell.getNumericCellValue());
     break;
    case HSSFCell.CELL_TYPE_STRING:  //字符串类型
     result[i] = new String(cell.toString());
   }
  }
  return result;
 }
 
 public int totalRows(){
  int totalRows = 0;
  int sc = numOfSheets();
  for(int i=0;i<sc;i++){
   HSSFSheet sheet = wb.getSheetAt(i);
   int curSheetRows = sheet.getLastRowNum();
   if(curSheetRows > 0){
    totalRows += curSheetRows+1;
   }
  }
  return totalRows;
 }
 public static void main(String[] args) {
  File file = new File("C:\\detail.xls");
  ExcelReader reader = new ExcelReader(file);

  //获取文档第一条记录 即列头
  Object[] firstRow = reader.getFirstRow();
   if (firstRow == null) {
    System.out.println("文件内容为空");
   }

   // 是否剔除第一行
   boolean trimFirst = false;
   if ("姓名".equals(firstRow[0])) {//第一列列名
    trimFirst = true;
   }
   // 验证文件上传条数
   int totalRows = reader.totalRows();
   if (trimFirst) {
    totalRows = totalRows - 1;
   }

   if (totalRows > 1000) {
    System.out.println("一次最大上传条数为1000条");
    }

    List<Object[]> objects = reader.parse2ObjArrays(trimFirst);

    for (int i = 0; i < objects.size(); i++) {
    Object[] objs = objects.get(i);

    //以下是对objs进行操作

   }

 }
}