Java读取Excel文件的方法

在应用程序中经常需要读取Excel文件中的信息，在此将解析Excel文件的代码贴出，供需要的参考：

       Excel2003之前与Excel2007之后的文件组织方式，因此读取代码也不尽相同。

1. 读取Excel2003文件，Excel2003文件最大行数为65536行，利用HSSFWorkbook类可以完成：

     

public class XLSReader {
       private List<String> lines = new ArrayList<String>();

       private List<String> titles = new ArrayList<String>();

       public void readFile(String fileNames) throws FileNotFoundException, IOException{

           Workbook wb = new HSSFWorkbook(new FileInputStream(fileNames));
           Sheet readsheet = wb.getSheetAt(0);
           Iterator<Row> iter = readsheet.rowIterator();

           //获取表头信息
           if(iter.hasNext()){
              titles.clear();
              Row row = iter.next();
              int colNum = row.getPhysicalNumberOfCells();
              for(int i=0; i<colNum; i++){
                 Cell cell = row.getCell(i);
                 String value = getCellValue(cell);
                 titles.add(value);
              }
           }

           while(iter.hasNext()){
              Row row = iter.next();
              int colNum = row.getPhysicalNumberOfCells();

              lines.clear();
              for(int i=0; i<colNum; i++){
                 Cell cell = row.getCell(i);
                 String value = getCellValue(cell);
                 lines.add(value);
              }
           }
       }

       public String getCellValue(Cell cell) {

          if(null==cell){
             return "";
          }

          switch(cell.getCellType()){

             case Cell.CELL_TYPE_NUMERIC:
                return String.valueOf(cell.getNumericCellValue());

             case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();

             case Cell.CELL_TYPE_FORMULA:
                 return cell.getCellFormula();
   
             case Cell.CELL_TYPE_BOOLEAN:
                 return cell.getBooleanCellValue()==true ? "true" : "false";

             case Cell.CELL_TYPE_ERROR:
                try {
                  return new String(new byte[]{cell.getErrorCellValue()}, "UTF-8");
                } catch (UnsupportedEncodingException e) {
                  return "";
                }

             case Cell.CELL_TYPE_BLANK:
               return "";

             default:
               return cell.toString();
             }
          }

         public static void main(String[] args) throws Exception{

              XLSReader reader = new XLSReader();
              reader.readFile("C:\\Users\\Administrator\\Desktop\\测试.xls");
         }
     }

2  读取Excel2007文件，由于Excel2007文件与Excel2003文件组织方式不同，当文件较小时，将上述代码的HSSFWorkbook换成XSSFWorkbook即可，但这样方式是将文件全部内容加载至内存中，当文件较大时，例如10万以上时，很容易造成内存溢出，Excel2007基于XML标准编写，基于这个思路利用SAX方式读取Excel2007文件:

public class SAXReader {

        private List<String> titlelist = new ArrayList<String>();

        private List<String> lines = new ArrayList<String>();

        public List<String> getTitle(){
               return this.titlelist;
        }

        public void processFirstSheet(String filename) throws Exception {  
             OPCPackage pkg = OPCPackage.open(filename);
             XSSFReader r = new XSSFReader(pkg);
             SharedStringsTable sst = r.getSharedStringsTable();  
             XMLReader parser = fetchSheetParser(sst);
    
             InputStream sheet2 = r.getSheet("rId1");
             InputSource sheetSource = new InputSource(sheet2);
             //解析xlsx文件转换的xml文档
             parser.parse(sheetSource);
             sheet2.close();
        }


        public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {  
             XMLReader parser =  XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");  
             ContentHandler handler = new SheetHandler(sst);
             parser.setContentHandler(handler);
             return parser;
        }

        /**  
         * 自定义解析处理器 
         * See org.xml.sax.helpers.DefaultHandler javadocs  
         */  
       private class SheetHandler extends DefaultHandler {  
          
          private SharedStringsTable sst;
          private String lastContents;
          private boolean nextIsString;
        
          private int curRow = 0;
          private int curCol = 0;
        
          //定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等  
          private String preRef = null, ref = null;
        
          private SheetHandler(SharedStringsTable sst) {
               this.sst = sst;
          }
          
          /** 
           * 解析一个element的开始时触发事件 
           */
          public void startElement(String uri, String localName, String name,  Attributes attributes) throws SAXException {
        
             if(name.equals("c")) {
                //前一个单元格的位置  
                 if(preRef == null){
                     preRef = attributes.getValue("r");  
                 }else{
                     preRef = ref;
                 }
                //当前单元格的位置  
                 ref = attributes.getValue("r");
                
                 // Figure out if the value is an index in the SST  
                 String cellType = attributes.getValue("t");  
                 if(cellType != null && cellType.equals("s")) {
                     nextIsString = true;
                 } else {
                     nextIsString = false;
                 }
             }
             lastContents = "";
         }
        
         /** 
          * 解析一个element元素结束时触发事件 
          */  
         public void endElement(String uri, String localName, String name) throws SAXException {
        
             if(nextIsString) {
                 int idx = Integer.parseInt(lastContents);
                 lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                 nextIsString = false;
             }
            
             //获取表头信息
             if (name.equals("v") && curRow==0) {
                 String value = lastContents.trim();
                 titlelist.add(curCol, value);
                 curCol++;
             }else {
                 if (name.equals("row")) {
                     curCol=0;
                     curRow++;
                 }
                
                 if(curCol==0){
                    lines.clear();
                }
                
                 if(name.equals("c") && curRow>0){
                    lines.add(lastContents.trim());
                    curCol++;
                 }
             }
         }
        
         /** 
          * 获取element的文本数据 
          */  
         public void characters(char[] ch, int start, int length) { 
             lastContents += new String(ch, start, length);
         }
        
     }
    
        public static void main(String[] args) throws Exception {
          
           SAXReader reader = new SAXReader();
           reader.processFirstSheet("C:\\Users\\Administrator\\Desktop\\测试.xlsx");
           reader.loadData();
        }

}