java通过poi把excel文件导入mysql数据库报错 40
public Excel2MySql() throws Exception {
Connection con = null;
PreparedStatement ps =null;
DBConnection db = new DBConnection();
con = db.getDB();
String filePath = "C:\\Users\\Administrator\\workspace\\项目\\city.xls";
// 文件流指向excel文件
FileInputStream fin = new FileInputStream(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(fin);// 创建工作薄
HSSFSheet sheet = workbook.getSheetAt(0);// 得到工作表
HSSFRow row = null;// 对应excel的行
HSSFCell cell = null;// 对应excel的列
int totalRow = sheet.getLastRowNum();// 得到excel的总记录条数// 以下的字段一一对应数据库表的字段
String Id = "";
String Name = "";
String CountryCode = "";
String District = "";
String Population = "";
String sql = "insert into city(Id,Name,CountryCode,District,Population) " + "values(?,?,?,?,?)";
for (int i = 1; i <= totalRow; i++) {
row = sheet.getRow(i);
cell = row.getCell(1);
Id = cell.getRichStringCellValue().toString();
cell = row.getCell(2);
Name = cell.getRichStringCellValue().toString();
cell = row.getCell(3);
CountryCode = cell.getRichStringCellValue().toString();
cell = row.getCell(4);
District = cell.getRichStringCellValue().toString();
cell = row.getCell(5);
Population = cell.getRichStringCellValue().toString();
ps = con.prepareStatement(sql);
ps.setString(1, Id);
ps.setString(2, Name);
ps.setString(3, CountryCode);
ps.setString(4, District);
ps.setString(5, Population);
ps.execute();
}
ps.close();
con.close();
}
public static void main(String[] args) {
try {
@SuppressWarnings("unused")
Excel2MySql mysql=new Excel2MySql();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
问题我自己解决了。贴出修改部分的代码，大家看下。
row = sheet.getRow(i);
cell = row.getCell(0);
Id =(int)Double.parseDouble(cell.toString());
cell = row.getCell(1);
Name = cell.toString();
cell = row.getCell(2);
CountryCode = cell.toString();
cell = row.getCell(3);
District = cell.toString();
cell = row.getCell(4);
Population =(int)Double.parseDouble(cell.toString()); 展开
2016-05-21 · 百度知道合伙人官方认证企业
育知同创教育
java通过poi把excel文件导入mysql数据库报错是因为excel中的数据类型要跟mysql中的数据类型和长度对应，否则类型转换异常是最常见的。所以插入到mysql数据库的时候需要做类型检查。
1、Excel中的测试数据：
2、数据库表结构：
CREATE TABLE `student_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`no` varchar(20) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`age` varchar(10) DEFAULT NULL,
`score` float DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3、java源码部分ReadExcel.java：
/**
* 读取excel中的数据并插入db
*/
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.b510.common.Common;
import com.b510.excel.vo.Student;
/**
* @author pieryon
* @created 2016-5-18
*/
public class ReadExcel {
public List<Student> readXls() throws IOException {
InputStream is = new FileInputStream(Common.EXCEL_PATH);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
Student student = null;
List<Student> list = new ArrayList<Student>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
student = new Student();
HSSFCell no = hssfRow.getCell(0);
HSSFCell name = hssfRow.getCell(1);
HSSFCell age = hssfRow.getCell(2);
HSSFCell score = hssfRow.getCell(3);
student.setNo(getValue(no));
student.setName(getValue(name));
student.setAge(getValue(age));
student.setScore(Float.valueOf(getValue(score)));
list.add(student);
}
}
}
return list;
}
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
// 返回布尔类型的值
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
return String.valueOf(hssfCell.getNumericCellValue());
} else {
// 返回字符串类型的值
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
2、SaveData2DB.java
/**
* 插入数据到db
*/
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import com.b510.common.Common;
import com.b510.excel.util.DbUtil;
import com.b510.excel.vo.Student;
/**
* @author pieryon
* @created 2016-5-18
*/
public class SaveData2DB {
@SuppressWarnings({ "rawtypes" })
public void save() throws IOException, SQLException {
ReadExcel xlsMain = new ReadExcel();
Student student = null;
List<Student> list = xlsMain.readXls();
for (int i = 0; i < list.size(); i++) {
student = list.get(i);
List l = DbUtil.selectOne(Common.SELECT_STUDENT_SQL + "'%" + student.getName() + "%'", student);
if (!l.contains(1)) {
DbUtil.insert(Common.INSERT_STUDENT_SQL, student);
} else {
System.out.println("The Record was Exist : No. = " + student.getNo() + " , Name = " + student.getName() + ", Age = " + student.getAge() + ", and has been throw away!");
}
}
}
}
保存结果：
嗯。这么高深。脑子太笨了，不懂呢。
我贴出来了，希望大神帮我看看
