JAVA使用POI读取EXCEL文件的简单model
一.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<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器
while (rows.hasNext()) {
Row row = rows.next(); //获得行数据
System.out.println("Row #" + row.getRowNum()); //获得行号从0开始
Iterator<Cell> 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(cell 53 .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(cell 60 .getBooleanCellValue()); 61 break; 62 case HSSFCell.CELL_TYPE_FORMULA: 63 arrayString[i][j] = String.valueOf(cell 64 .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<arrayString.length;i++) 76 { 77 String[] row = arrayString[i]; 78 s.append("insert into table(name,sex,address) values("); 79 for(int j = 0; j < row.length; j++) 80 { 81 if(j==0) 82 { 83 s.append(row[j]); 84 }else 85 { 86 s.append(",").append(row[j]); 87 } 88 } 89 s.append(");"); 90 s.append("\n"); 91 } 92 System.out.println(s.toString()); 93 94 } catch (IOException ex) { 95 ex.printStackTrace(); 96 } 97 } 98 }
