java的poi技术读取Excel数据到MySQL
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
java的poi技术读取Excel数据到MySQL
项目结构：
Excel中的测试数据：
数据库结构：
对应的 SQL：
1 CREATE TABLE `student_info` ( 2 3 4 5 6 7 `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`)
8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据成功：
如果重复数据，则丢掉：
=============================================
源码部分：
=============================================
29 }
/ExcelTest/src/com/b510/excel/ReadExcel.java
1 /** 2 * 3 */ 4 package com.b510.excel; 5 6 import java.io.FileInputStream; 7 import java.io.IOException; 8 import java.io.InputStream; 9 import java.util.ArrayList; 10 import java.util.List; 11 12 import org.apache.poi.hssf.usermodel.HSSFCell; 13 import org.apache.poi.hssf.usermodel.HSSFRow; 14 import org.apache.poi.hssf.usermodel.HSSFSheet; 15 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 16 17 import com.b510.common.Common; 18 import com.b510.excel.vo.Student; 19 20 /** 21 * @author Hongten 22 * @created 2014-5-18 23 */ 24 public class ReadExcel { 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 } } } } // 循环行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); 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;
54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 } }
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()); } }
/ExcelTest/src/com/b510/common/Common.java
1 /** 2 * 3 */ 4 package com.b510.common; 5 6 /** 7 * @author Hongten 8 * @created 2014-5-18 9 */ 10 public class Common { 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ?)"; 26 27 28 public static final String UPDATE_STUDENT_SQL = "update student_info set no = ?, name = ?, age= ?, score = ? public static final String SELECT_STUDENT_ALL_SQL = "select id,no,name,age,score from student_info"; public static final String SELECT_STUDENT_SQL = "select * from student_info where name like "; where id = ? "; // sql public static final String INSERT_STUDENT_SQL = "insert into student_info(no, name, age, score) values(?, ?, ?, // common public static final String EXCEL_PATH = "lib/student_info.xls"; // connect the database public static final String DRIVER = "com.mysql.jdbc.Driver"; public static final String DB_NAME = "test"; public static final String USERNAME = "root"; public static final String PASSWORD = "root"; public static final String IP = "192.168.1.103"; public static final String PORT = "3306"; public static final String URL = "jdbc:mysql://" + IP + ":" + PORT + "/" + DB_NAME;
/ExcelTest/src/com/b510/client/Client.java
1 /** 2 * 3 */ 4 package com.b510.client; 5 6 import java.io.IOException; 7 import java.sql.SQLException; 8 9 import com.b510.excel.SaveData2DB; 10 11 /** 12 * @author Hongten 13 * @created 2014-5-18 14 */ 15 public class Client { 16 17 18 19 20 21 22 } } public static void main(String[] args) throws IOException, SQLException { SaveData2DB saveData2DB = new SaveData2DB(); saveData2DB.save(); System.out.println("end");
/ExcelTest/src/com/b510/excel/SaveData2DB.java
1 /** 2 * 3 */ 4 package com.b510.excel; 5 6 import java.io.IOException; 7 import java.sql.SQLException; 8 import java.util.List; 9 10 import com.b510.common.Common; 11 import com.b510.excel.util.DbUtil; 12 import com.b510.excel.vo.Student; 13 14 /** 15 * @author Hongten 16 * @created 2014-5-18 17 */ 18 public class SaveData2DB { 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 } } } } 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 = " + @SuppressWarnings({ "rawtypes" }) public void save() throws IOException, SQLException { ReadExcel xlsMain = new ReadExcel(); Student student = null; List<Student> list = xlsMain.readXls();
项目结构：
Excel中的测试数据：
数据库结构：
对应的 SQL：
1 CREATE TABLE `student_info` ( 2 3 4 5 6 7 `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`)
8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据成功：
如果重复数据，则丢掉：
=============================================
源码部分：
=============================================
29 }
/ExcelTest/src/com/b510/excel/ReadExcel.java
1 /** 2 * 3 */ 4 package com.b510.excel; 5 6 import java.io.FileInputStream; 7 import java.io.IOException; 8 import java.io.InputStream; 9 import java.util.ArrayList; 10 import java.util.List; 11 12 import org.apache.poi.hssf.usermodel.HSSFCell; 13 import org.apache.poi.hssf.usermodel.HSSFRow; 14 import org.apache.poi.hssf.usermodel.HSSFSheet; 15 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 16 17 import com.b510.common.Common; 18 import com.b510.excel.vo.Student; 19 20 /** 21 * @author Hongten 22 * @created 2014-5-18 23 */ 24 public class ReadExcel { 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 } } } } // 循环行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); 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;
54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 } }
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()); } }
/ExcelTest/src/com/b510/common/Common.java
1 /** 2 * 3 */ 4 package com.b510.common; 5 6 /** 7 * @author Hongten 8 * @created 2014-5-18 9 */ 10 public class Common { 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ?)"; 26 27 28 public static final String UPDATE_STUDENT_SQL = "update student_info set no = ?, name = ?, age= ?, score = ? public static final String SELECT_STUDENT_ALL_SQL = "select id,no,name,age,score from student_info"; public static final String SELECT_STUDENT_SQL = "select * from student_info where name like "; where id = ? "; // sql public static final String INSERT_STUDENT_SQL = "insert into student_info(no, name, age, score) values(?, ?, ?, // common public static final String EXCEL_PATH = "lib/student_info.xls"; // connect the database public static final String DRIVER = "com.mysql.jdbc.Driver"; public static final String DB_NAME = "test"; public static final String USERNAME = "root"; public static final String PASSWORD = "root"; public static final String IP = "192.168.1.103"; public static final String PORT = "3306"; public static final String URL = "jdbc:mysql://" + IP + ":" + PORT + "/" + DB_NAME;
/ExcelTest/src/com/b510/client/Client.java
1 /** 2 * 3 */ 4 package com.b510.client; 5 6 import java.io.IOException; 7 import java.sql.SQLException; 8 9 import com.b510.excel.SaveData2DB; 10 11 /** 12 * @author Hongten 13 * @created 2014-5-18 14 */ 15 public class Client { 16 17 18 19 20 21 22 } } public static void main(String[] args) throws IOException, SQLException { SaveData2DB saveData2DB = new SaveData2DB(); saveData2DB.save(); System.out.println("end");
/ExcelTest/src/com/b510/excel/SaveData2DB.java
1 /** 2 * 3 */ 4 package com.b510.excel; 5 6 import java.io.IOException; 7 import java.sql.SQLException; 8 import java.util.List; 9 10 import com.b510.common.Common; 11 import com.b510.excel.util.DbUtil; 12 import com.b510.excel.vo.Student; 13 14 /** 15 * @author Hongten 16 * @created 2014-5-18 17 */ 18 public class SaveData2DB { 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 } } } } 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 = " + @SuppressWarnings({ "rawtypes" }) public void save() throws IOException, SQLException { ReadExcel xlsMain = new ReadExcel(); Student student = null; List<Student> list = xlsMain.readXls();