POI读取excel

-

先看代码，挨句解释：

一般遍历使用两种方式，1：得到总的行数和每行的列数，然后循环。2：使用迭代

先看第一种：

Java代码 复制代码 收藏代码
  1. package com.golden.test; 
  2.  
  3. import java.io.File; 
  4. import java.io.FileInputStream; 
  5.  
  6. import org.apache.poi.hssf.usermodel.HSSFCell; 
  7. import org.apache.poi.hssf.usermodel.HSSFRow; 
  8. import org.apache.poi.hssf.usermodel.HSSFSheet;
  9. import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
  10.  
  11. /**
  12. *
  13. * @author 崔素强
  14. *
  15. */ 
  16. public class PoiReadXls2 { 
  17.     public static void main(String[] args) { 
  18.         File f = new File("c:\\a.xls"); 
  19.         try { 
  20.             FileInputStream is = new FileInputStream(f); 
  21.             HSSFWorkbook wbs = new HSSFWorkbook(is); 
  22.             HSSFSheet childSheet = wbs.getSheetAt(0); 
  23.             // System.out.println(childSheet.getPhysicalNumberOfRows()); 
  24.             System.out.println("有行数" + childSheet.getLastRowNum()); 
  25.             for (int j = 0; j < childSheet.getLastRowNum(); j++) { 
  26.                 HSSFRow row = childSheet.getRow(j); 
  27.                 // System.out.println(row.getPhysicalNumberOfCells()); 
  28.                 // System.out.println("有列数" + row.getLastCellNum()); 
  29.                 if (null != row) { 
  30.                     for (int k = 0; k < row.getLastCellNum(); k++) { 
  31.                         HSSFCell cell = row.getCell(k); 
  32.                         if (null != cell) { 
  33.                             switch (cell.getCellType()) { 
  34.                             case HSSFCell.CELL_TYPE_NUMERIC: // 数字 
  35.                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
           
                                      Date date = cell.getDateCellValue();        //  日期
                                      SimpleDateFormat dateForm = new SimpleDateFormat("yyyy/MM/dd");
                                      value = dateForm.format(date);
                                    //value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toLocaleString();
                                  } else { // 纯数字
                                     value = String.valueOf("\t"+cell.getNumericCellValue()); 
                                 }
  36.                                 break; 
  37.                             case HSSFCell.CELL_TYPE_STRING: // 字符串 
  38.                                 System.out.print(cell.getStringCellValue() 
  39.                                         + "   "); 
  40.                                 break; 
  41.                             case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean 
  42.                                 System.out.println(cell.getBooleanCellValue() 
  43.                                         + "   "); 
  44.                                 break; 
  45.                             case HSSFCell.CELL_TYPE_FORMULA: // 公式 
  46.                                 System.out.print(cell.getCellFormula() + "   "); 
  47.                                 break; 
  48.                             case HSSFCell.CELL_TYPE_BLANK: // 空值 
  49.                                 System.out.println(" "); 
  50.                                 break; 
  51.                             case HSSFCell.CELL_TYPE_ERROR: // 故障 
  52.                                 System.out.println(" "); 
  53.                                 break; 
  54.                             default: 
  55.                                 System.out.print("未知类型   "); 
  56.                                 break; 
  57.                             } 
  58.                         } else { 
  59.                             System.out.print("-   "); 
  60.                         } 
  61.                     } 
  62.                 } 
  63.                 System.out.println(); 
  64.             } 
  65.         } catch (Exception e) { 
  66.             e.printStackTrace(); 
  67.         } 
  68.     } 
  69. } 

得到Excel的文件然后读取，这个很简单。关键有两个地方，也许在网上会看到有的这样使用有的那样使用。

System.out.println("有行数" + childSheet.getLastRowNum());

System.out.println(childSheet.getPhysicalNumberOfRows());

System.out.println("有列数" + row.getLastCellNum());

System.out.println(row.getPhysicalNumberOfCells());

如果人都拷贝代码进行使用了，不知道有什么区别。太多的区别不知道，但是有一点我发现了，那就是如果中间各行或者隔列的话getPhysicalNumberOfRows和getPhysicalNumberOfCells就不能读取到所有的行和列了。

再者，一定要对单元格的格式进行判断switch (cell.getCellType())，不同的单元格格式使用不同的方法。最后加上为止类型，以防万一。

而且在数字类型里，又分为了纯数字和时间格式：

Java代码 复制代码 收藏代码
  1. case HSSFCell.CELL_TYPE_NUMERIC: // 数值型 
  2.     if (HSSFDateUtil.isCellDateFormatted(cell)) { 
  3.         //  如果是date类型则 ，获取该cell的date值 
  4.         value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString(); 
  5.     } else { // 纯数字 
  6.         value = String.valueOf(cell.getNumericCellValue()); 
  7. } 

还有一种迭代的方法：

Java代码 复制代码 收藏代码
  1. package com.golden.test; 
  2.  
  3. import java.io.File; 
  4. import java.io.FileInputStream; 
  5. import java.io.IOException; 
  6. import java.io.InputStream; 
  7. import java.util.Iterator; 
  8. import org.apache.poi.hssf.usermodel.HSSFCell; 
  9. import org.apache.poi.hssf.usermodel.HSSFRow; 
  10. import org.apache.poi.hssf.usermodel.HSSFSheet; 
  11. import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
  12. import org.apache.poi.poifs.filesystem.POIFSFileSystem; 
  13.  
  14. /**
  15. *
  16. * @author 崔素强
  17. *
  18. */ 
  19. public class PoiReadXls { 
  20.     @SuppressWarnings( { "unchecked", "deprecation" }) 
  21.     public static void main(String[] args) { 
  22.         File f = new File("c:\\a.xls"); 
  23.         try { 
  24.             InputStream input = new FileInputStream(f); 
  25.             POIFSFileSystem fs = new POIFSFileSystem(input); 
  26.             HSSFWorkbook wb = new HSSFWorkbook(fs); 
  27.             HSSFSheet sheet = wb.getSheetAt(0); 
  28.             Iterator rows = sheet.rowIterator(); 
  29.             while (rows.hasNext()) { 
  30.                 HSSFRow row = (HSSFRow) rows.next(); 
  31.                 // System.out.print("行：" + row.getRowNum() + " "); 
  32.                 Iterator cells = row.cellIterator(); 
  33.                 while (cells.hasNext()) { 
  34.                     HSSFCell cell = (HSSFCell) cells.next(); 
  35.                     // System.out.println("列：" + cell.getCellNum()); 
  36.                     switch (cell.getCellType()) { 
  37.                     case HSSFCell.CELL_TYPE_NUMERIC: // 数字 
  38.                         System.out.print(cell.getNumericCellValue() + "   "); 
  39.                         break; 
  40.                     case HSSFCell.CELL_TYPE_STRING: // 字符串 
  41.                         System.out.print(cell.getStringCellValue() + "   "); 
  42.                         break; 
  43.                     case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean 
  44.                         System.out.println(cell.getBooleanCellValue() + "   "); 
  45.                         break; 
  46.                     case HSSFCell.CELL_TYPE_FORMULA: // 公式 
  47.                         System.out.print(cell.getCellFormula() + "   "); 
  48.                         break; 
  49.                     case HSSFCell.CELL_TYPE_BLANK: // 空值 
  50.                         System.out.println(" "); 
  51.                         break; 
  52.                     case HSSFCell.CELL_TYPE_ERROR: // 故障 
  53.                         System.out.println(" "); 
  54.                         break; 
  55.                     default: 
  56.                         System.out.print("未知类型   "); 
  57.                         break; 
  58.                     } 
  59.                 } 
  60.                 System.out.println(); 
  61.             } 
  62.         } catch (IOException ex) { 
  63.             ex.printStackTrace(); 
  64.         } 
  65.     } 
  66. } 
posted @ 2012-11-01 10:15  sunnyleer  阅读(322)  评论(1)  编辑  收藏  举报