本文采用的是POI包读取Excel数据,兼容Excel 2003 和 Excel 2007。此Demo数据读取到oracle中。
此Demo引入jar包为:
poi-3.10-FINAL-20140208.jar
poi-ooxml-schemas-3.10-FINAL-20140208.jar
dom4j-1.6.1.jar
package com.domain;
1.该实体用来承接对象,不习惯创建实体的码友,可不用创建,后面只需用HashMap承接数据即可。
package com.domain;
public class PersonName {
private int id;
private String enName;
private String oriCountry;
private String meanString;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getEnName() {
return enName;
}
public void setEnName(String enName) {
this.enName = enName;
}
public String getOriCountry() {
return oriCountry;
}
public void setOriCountry(String oriCountry) {
this.oriCountry = oriCountry;
}
public String getMeanString() {
return meanString;
}
public void setMeanString(String meanString) {
this.meanString = meanString;
}
public PersonName(int id, String enName, String oriCountry, String meanString) {
super();
this.id = id;
this.enName = enName;
this.oriCountry = oriCountry;
this.meanString = meanString;
}
public PersonName() {
super();
}
}
2.该功能类中的方法见注释;
package com.service;
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.HSSFRow;
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.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.domain.PersonName;
/***
* 导入jar包:poi-3.10-FINAL-20140208.jar
* 由于poi.jar对Excel 2007默认不支持,因此需要引入如下两个jar包
* 1.poi-ooxml-schemas-3.10-FINAL-20140208.jar
* 2.dom4j-1.6.1.jar
* @author Administrator
*
*/
public class PoiRead {
public List
readXls(final String path) throws IOException {
InputStream is = new FileInputStream(path);
// HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);//excel2003及其以前版本
// XSSFWorkbook wb = new XSSFWorkbook(is);//Excel 2007及其以后版本
Workbook workbook = null;
if((path.toLowerCase()).endsWith(".xlsx")){//Excel 2007
workbook = new XSSFWorkbook(is);
}else{//Excel 2003
workbook = new HSSFWorkbook(is);
}
PersonName personName = null;
List
list = new ArrayList
();
int sheetCount = workbook.getNumberOfSheets();//获取Excel工作sheet的个数
for (int numSheet = 0; numSheet < sheetCount; numSheet++) {//遍历工作表Sheet
// String sheetNameString = workbook.getSheetName(numSheet);//获取sheet的名称
Sheet sheet = null;
if((path.toLowerCase()).endsWith(".xlsx")){
sheet = (XSSFSheet) workbook.getSheetAt(numSheet); //按索引引用sheet对象
}else{
sheet = workbook.getSheetAt(numSheet); //按索引引用sheet对象
}
// HSSFSheet sheet = workbook.getSheet("Sheet1");/按名称引用sheet对象
if (sheet == null) {//判断是否该sheet表单
continue;
}
int rownum = sheet.getLastRowNum();//获取表单对象最后一行的下标,下标由0开始
for (int rowNum = 0; rowNum <= rownum; rowNum++) {// 循环行Row
Row row = null;
if((path.toLowerCase()).endsWith(".xlsx")){
row = (XSSFRow)sheet.getRow(rowNum);//获取行对象
}else{
row = (HSSFRow)sheet.getRow(rowNum);
}
if (row != null) {//判断该行是否存在数据
personName = new PersonName();
personName.setEnName(getValue(getList(row.getCell(0))));//获取当前行第1列的数据
personName.setOriCountry(getValue(getList(row.getCell(2))));//获取当前行第3列的数据,本示例在2列无数据
personName.setMeanString(getValue(getList(row.getCell(3))));//获取当前行第4列的数据
list.add(personName);
}
}
}
return list;
}
@SuppressWarnings("static-access")
private String getValue(List
cell) {
if (cell.get(0).getCellType() == cell.get(0).CELL_TYPE_BOOLEAN) {
// 返回布尔类型的值
return String.valueOf(cell.get(0).getBooleanCellValue()).trim();
} else if (cell.get(0).getCellType() == cell.get(0).CELL_TYPE_NUMERIC) {
// 返回数值类型的值
return String.valueOf(cell.get(0).getNumericCellValue()).trim();
} else {
// 返回字符串类型的值
return String.valueOf(cell.get(0).getStringCellValue()).trim();
}
}
public List
getList(Cell cell){
List
resultList = new ArrayList<>(); resultList.add(cell); return resultList; } }
3.调用方法进行数据读取;
private static final String PATH2007= "E://enname.xlsx";
private static final String PAHT2003 = "E://name.xls";
public static void main(String[] args) throws SQLException, IOException {
PoiRead poiRead = new PoiRead();
List
list = poiRead.readXls(PATH2007);
Connection connection=DBUtil.getConnection();
PreparedStatement ps = null;
System.out.println("***"+list.size());
for (PersonName personName : list) {
String sql = "insert into testName(id,enname,oriCountry,means) values (englishname_id.nextval,?,?,?)";
System.out.println("*"+sql);
ps=connection.prepareStatement(sql);
ps.setString(1, personName.getEnName());
ps.setString(2, personName.getOriCountry());
ps.setString(3, personName.getMeanString());
ps.execute();
}
}
注意:
使用oracle的小伙伴需要注意,如果Excel中的数据超过300条记录(大于300次插入操作),会报下面错误:
Caused by: Java.sql.SQLException: ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01000: 超出打开游标的最大数
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01000: 超出打开游标的最大数
ORA-01000: 超出打开游标的最大数
此时需要修改一下oracle初始参数。由于oracle系统默认游标最大值为300,因此需要将这个初始值调大一些,操作如下;
以sysdba身份登录(command window),更改游标默认值
alter system set open_cursors=1000 scope=both;
查看游标默认值
show parameter open_cursors;
本人小菜鸟一枚,欢迎各位指导、更正,同时接受广大码友吐槽!愿我们一起在吐槽中成长!