一.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
package file; import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List; 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 excel2sql {
public static void main(String[] args) {
readXml("D:/cxx.xlsx");
} 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);
} String[][] arrayString = null;
// for (int k = 0; k < wb.getNumberOfSheets(); k++) {
for (int k = 0; k < 1; k++) {
Sheet sheet = wb.getSheetAt(k);
for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
Row row = sheet.getRow(i);
for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
if (i == 0 && j == 0) {
int rowNum = sheet.getPhysicalNumberOfRows();
int columnNum = row.getPhysicalNumberOfCells();
arrayString = new String[rowNum][columnNum];
} Cell cell = row.getCell(j);
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
arrayString[i][j] = String.valueOf(cell
.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
arrayString[i][j] = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
arrayString[i][j] = String.valueOf(cell
.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
arrayString[i][j] = String.valueOf(cell
.getCellFormula());
break;
default:
arrayString[i][j] = "";
break;
}
}
}
} StringBuffer s = new StringBuffer();
for(int i =0;i<arrayString.length;i++)
{
String[] row = arrayString[i];
s.append("insert into table(name,sex,address) values(");
for(int j = 0; j < row.length; j++)
{
if(j==0)
{
s.append(row[j]);
}else
{
s.append(",").append(row[j]);
}
}
s.append(");");
s.append("\n");
}
System.out.println(s.toString()); } catch (IOException ex) {
ex.printStackTrace();
}
}
}