一.JAVA使用POI读取EXCEL文件的简单model

1.所需要的jar

commons-codec-1.10.jar

commons-logging-1.2.jar

junit-4.12.jar

log4j-1.2.17.jar

poi-3.7-20101029.jar

poi-examples-3.7-20101029.jar

poi-ooxml-3.7-20101029.jar

poi-ooxml-schemas-3.7-20101029.jar

poi-scratchpad-3.7-20101029.jar

poi-3.6.jar

poi-3.6-dom4j-1.6.1.jar

poi-3.6-geronimo-stax-api_1.0_spec-1.0.jar

poi-3.6-xmlbeans-2.3.0.jar

poi-3.6-ooxml-20091214.jar

poi-3.6-ooxml-schemas-20091214.jar 

getPhysicalNumberOfRows()这个才是真正的行数

package poi;  import java.io.FileInputStream;  import java.io.IOException;  import java.io.InputStream;  import java.util.Iterator;  import org.apache.poi.hssf.usermodel.HSSFCell;  import org.apache.poi.hssf.usermodel.HSSFWorkbook;  import org.apache.poi.ss.usermodel.Cell;  import org.apache.poi.ss.usermodel.Row;  import org.apache.poi.ss.usermodel.Sheet;  import org.apache.poi.ss.usermodel.Workbook;  import org.apache.poi.xssf.usermodel.XSSFWorkbook;     public class ReadExcel001 {      public static void main(String[] args) {          readXml("D:/test.xlsx");          System.out.println("-------------");          readXml("d:/test2.xls");      }      public static void readXml(String fileName){          boolean isE2007 = false;    //判断是否是excel2007格式          if(fileName.endsWith("xlsx"))              isE2007 = true;          try {              InputStream input = new FileInputStream(fileName);  //建立输入流              Workbook wb  = null;              //根据文件格式(2003或者2007)来初始化              if(isE2007)                  wb = new XSSFWorkbook(input);              else                  wb = new HSSFWorkbook(input);              Sheet sheet = wb.getSheetAt(0);     //获得第一个表单              Iterator
      
        rows = sheet.rowIterator(); //获得第一个表单的迭代器              while (rows.hasNext()) {                  Row row = rows.next();  //获得行数据                  System.out.println("Row #" + row.getRowNum());  //获得行号从0开始                  Iterator
       
         cells = row.cellIterator();    //获得第一行的迭代器                  while (cells.hasNext()) {                      Cell cell = cells.next();                      System.out.println("Cell #" + cell.getColumnIndex());                      switch (cell.getCellType()) {   //根据cell中的类型来输出数据                      case HSSFCell.CELL_TYPE_NUMERIC:                          System.out.println(cell.getNumericCellValue());                          break;                      case HSSFCell.CELL_TYPE_STRING:                          System.out.println(cell.getStringCellValue());                          break;                      case HSSFCell.CELL_TYPE_BOOLEAN:                          System.out.println(cell.getBooleanCellValue());                          break;                      case HSSFCell.CELL_TYPE_FORMULA:                          System.out.println(cell.getCellFormula());                          break;                      default:                          System.out.println("unsuported sell type");                      break;                      }                  }              }          } catch (IOException ex) {              ex.printStackTrace();          }      }  }
       
      

个人实例

 

commons-logging-1.1.jar

dom4j-1.6.1.jar

fastjson-1.1.37.jar

geronimo-stax-api_1.0_spec-1.0.jar

junit-3.8.1.jar

log4j-1.2.13.jar

poi-3.9-20121203.jar

poi-examples-3.7-20101029.jar

poi-ooxml-3.7-20101029.jar

poi-ooxml-schemas-3.7-20101029.jar

poi-scratchpad-3.7-20101029.jar

xmlbeans-2.3.0.jar

 

 

1 package file; 2  3 import java.io.FileInputStream; 4 import java.io.IOException; 5 import java.io.InputStream; 6 import java.util.ArrayList; 7 import java.util.List; 8  9 import org.apache.poi.hssf.usermodel.HSSFCell;10 import org.apache.poi.hssf.usermodel.HSSFWorkbook;11 import org.apache.poi.ss.usermodel.Cell;12 import org.apache.poi.ss.usermodel.Row;13 import org.apache.poi.ss.usermodel.Sheet;14 import org.apache.poi.ss.usermodel.Workbook;15 import org.apache.poi.xssf.usermodel.XSSFWorkbook;16 17 public class excel2sql {18     public static void main(String[] args) {19         readXml("D:/cxx.xlsx");20     }21 22     public static void readXml(String fileName) {23         boolean isE2007 = false; // 判断是否是excel2007格式24         if (fileName.endsWith("xlsx"))25             isE2007 = true;26         try {27             InputStream input = new FileInputStream(fileName); // 建立输入流28             Workbook wb = null;29             // 根据文件格式(2003或者2007)来初始化30             if (isE2007) {31                 wb = new XSSFWorkbook(input);32             } else {33                 wb = new HSSFWorkbook(input);34             }35             36             String[][] arrayString = null;37             // for (int k = 0; k < wb.getNumberOfSheets(); k++) {
     38             for (int k = 0; k < 1; k++) {39                 Sheet sheet = wb.getSheetAt(k);40                 for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {41                     Row row = sheet.getRow(i);42                     for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {43                         if (i == 0 && j == 0) {44                             int rowNum = sheet.getPhysicalNumberOfRows();45                             int columnNum = row.getPhysicalNumberOfCells();46                             arrayString = new String[rowNum][columnNum];47                         }48 49                         Cell cell = row.getCell(j);50                         switch (cell.getCellType()) {51                         case HSSFCell.CELL_TYPE_NUMERIC:52                             arrayString[i][j] = String.valueOf(cell53                                     .getNumericCellValue());54                             break;55                         case HSSFCell.CELL_TYPE_STRING:56                             arrayString[i][j] = cell.getStringCellValue();57                             break;58                         case HSSFCell.CELL_TYPE_BOOLEAN:59                             arrayString[i][j] = String.valueOf(cell60                                     .getBooleanCellValue());61                             break;62                         case HSSFCell.CELL_TYPE_FORMULA:63                             arrayString[i][j] = String.valueOf(cell64                                     .getCellFormula());65                             break;66                         default:67                             arrayString[i][j] = "";68                             break;69                         }70                     }71                 }72             }73             74             StringBuffer s = new StringBuffer();75             for(int i =0;i