使用poi解析Excel

使用poi来解析Excel的xls和xlsx。

解析xls：

package xls;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
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;

public class XlsMain {

  public static void main( String[] args) throws IOException {
    XlsMain xlsMain = new XlsMain();
    
    xlsMain.readXls();
  }

  private void readXls() throws IOException{
    InputStream is = new FileInputStream( "D:\\excel\\xls_test2.xls");
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook( is); 
    
    // 循环工作表Sheet
    for(int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++){
      HSSFSheet hssfSheet = hssfWorkbook.getSheetAt( numSheet);
      if(hssfSheet == null){
        continue;
      }
      
      // 循环行Row 
      for(int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++){
        HSSFRow hssfRow = hssfSheet.getRow( rowNum);
        if(hssfRow == null){
          continue;
        }
        
        // 循环列Cell  
        for(int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++){
          HSSFCell hssfCell = hssfRow.getCell( cellNum);
          if(hssfCell == null){
            continue;
          }
          
          System.out.print("    " + getValue( hssfCell));
        }
        System.out.println();
      }
    }
  }
  
  @SuppressWarnings("static-access")
  private String getValue(HSSFCell hssfCell){
    if(hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN){
      return String.valueOf( hssfCell.getBooleanCellValue());
    }else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC){
      return String.valueOf( hssfCell.getNumericCellValue());
    }else{
      return String.valueOf( hssfCell.getStringCellValue());
    }
  }
  
}

解析xlsx：

package xlsx;

import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XlsxMain {

  public static void main( String[] args) throws IOException {
    XlsxMain xlsxMain = new XlsxMain();
    
    xlsxMain.readXlsx();
  }

  private void readXlsx() throws IOException{
    String fileName = "D:\\excel\\xlsx_test.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook( fileName);
    
    // 循环工作表Sheet
    for(int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++){
      XSSFSheet xssfSheet = xssfWorkbook.getSheetAt( numSheet);
      if(xssfSheet == null){
        continue;
      }
      
      // 循环行Row 
      for(int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++ ){
        XSSFRow xssfRow = xssfSheet.getRow( rowNum);
        if(xssfRow == null){
          continue;
        }
        
        // 循环列Cell   
        for(int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++){
          XSSFCell xssfCell = xssfRow.getCell( cellNum);
          if(xssfCell == null){
            continue;
          }
          System.out.print("   "+getValue(xssfCell));
        }
        System.out.println();
      }
    }
  }
  
  @SuppressWarnings("static-access")
  private String getValue(XSSFCell xssfCell){
    if(xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN){
      return String.valueOf( xssfCell.getBooleanCellValue());
    }else if(xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC){
      return String.valueOf( xssfCell.getNumericCellValue());
    }else{
      return String.valueOf( xssfCell.getStringCellValue());
    }
  }
  
}

评论

10 楼 wenm168 2017-01-01  

读取excel经典实现、支持图片读取、xlsx读取：http://www.anyrt.com/blog/list/importexcel.html

9 楼 entrence 2015-10-22  

8 楼 entrence 2015-10-22  

7 楼 乱花葬 2015-05-12  

谢谢，正赶上需要

6 楼 slientnight 2013-12-10  

谢谢啊!

5 楼 cjunying123 2013-08-28  

请问如果我只需要锁定表头 而其他地方内容是可以编辑的，应该怎么弄！ 多谢

4 楼 robin35java 2013-05-22  

现在这个方法new XSSFWorkbook( fileName); 不赞同用了

这样用了：
String path = "D:\\code.xlsx";
File file = new File(path);
XSSFWorkbook xssfWorkbook = null;
try {
xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

有帮助赞一个，多谢多谢~

3 楼 qiaoaaaa 2012-03-21  

源码如下：
public class WorkbookFactory {
/**
* Creates an HSSFWorkbook from the given POIFSFileSystem
*/
public static Workbook create(POIFSFileSystem fs) throws IOException {
return new HSSFWorkbook(fs);
}
/**
* Creates an XSSFWorkbook from the given OOXML Package
*/
public static Workbook create(OPCPackage pkg) throws IOException {
return new XSSFWorkbook(pkg);
}
/**
* Creates the appropriate HSSFWorkbook / XSSFWorkbook from
*  the given InputStream.
* Your input stream MUST either support mark/reset, or
*  be wrapped as a {@link PushbackInputStream}!
*/
public static Workbook create(InputStream inp) throws IOException, InvalidFormatException {
// If clearly doesn't do mark/reset, wrap up
if(! inp.markSupported()) {
inp = new PushbackInputStream(inp,;
}

if(POIFSFileSystem.hasPOIFSHeader(inp)) {
return new HSSFWorkbook(inp);
}
if(POIXMLDocument.hasOOXMLHeader(inp)) {
return new XSSFWorkbook(OPCPackage.open(inp));
}
throw new IllegalArgumentException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
}
}

2 楼 qiaoaaaa 2012-03-21  

WorkFactory.create(fis);

1 楼 qiaoaaaa 2012-03-21  

WorkFactory.crate(fis);