apache POI 学习笔记(读取excel)
POI项目SVN地址: http://svn.apache.org/repos/asf/poi/trunk/
需要jar包(不含依赖):poi-3.6.jar poi-ooxml-3.6.jar poi-ooxml-schemas-3.6.jar
1 import java.io.FileNotFoundException; 2 import java.io.IOException; 3 import java.io.InputStream; 4 import java.util.Iterator; 5 import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 6 import org.apache.poi.ss.usermodel.Cell; 7 import org.apache.poi.ss.usermodel.DateUtil; 8 import org.apache.poi.ss.usermodel.Row; 9 import org.apache.poi.ss.usermodel.Sheet; 10 import org.apache.poi.ss.usermodel.Workbook; 11 import org.apache.poi.ss.usermodel.WorkbookFactory; 12 public class ReadExcel { 13 14 private static final String fileName = "test03.xls"; 15 //private static final String fileName = "test07.xlsx"; 16 /** 17 * @param args 18 */ 19 public static void main(String[] args) 20 { 21 try 22 { 23 InputStream inp; 24 inp = ReadExcel.class.getClassLoader().getResourceAsStream(fileName); 25 //根据传入的文件类型返回相应Excel版本的Workbook 26 //HSSFWorkbook支持97-03版本 27 //XSSFWorkbook支持07版本 28 Workbook wb = WorkbookFactory.create(inp); 29 30 Sheet sheet = wb.getSheetAt(0); 31 for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) 32 { 33 // 迭代行 34 Row row = (Row) rit.next(); 35 // 迭代单元格 36 for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) 37 { 38 Cell cell = (Cell) cit.next(); 39 // 开始操作单元格 40 // 在每一行的输出都打印如 "5:6 例子字符串",5:6代表第5行,第6列 41 // 注意行和列是基于0索引的 42 System.out.print(cell.getRowIndex() + ":" + cell.getColumnIndex() 43 + " "); 44 // 打印单元格内的数据 45 switch (cell.getCellType()) 46 { 47 case Cell.CELL_TYPE_STRING: 48 System.out.println(cell.getRichStringCellValue().getString()); 49 break; 50 case Cell.CELL_TYPE_NUMERIC: 51 if (DateUtil.isCellDateFormatted(cell)) 52 { 53 System.out.println(cell.getDateCellValue()); 54 } else 55 { 56 System.out.println(cell.getNumericCellValue()); 57 } 58 break; 59 case Cell.CELL_TYPE_BOOLEAN: 60 System.out.println(cell.getBooleanCellValue()); 61 break; 62 case Cell.CELL_TYPE_FORMULA: 63 System.out.println(cell.getCellFormula()); 64 break; 65 case Cell.CELL_TYPE_BLANK: 66 System.out.println("blank!"); 67 default: 68 System.out.println(); 69 } 70 } 71 } 72 inp.close(); 73 } catch (FileNotFoundException e) 74 { 75 e.printStackTrace(); 76 } catch (InvalidFormatException e) 77 { 78 e.printStackTrace(); 79 } catch (IOException e) 80 { 81 e.printStackTrace(); 82 } 83 } 84 }
支持读取的数据类型:
数字——Cell.CELL_TYPE_NUMERIC
字符串——Cell.CELL_TYPE_STRING
布尔——Cell.CELL_TYPE_BOOLEAN
公式——Cell.CELL_TYPE_FORMULA
空类型——Cell.CELL_TYPE_BLANK
错误——CELL_TYPE_ERROR