java纯手写利用poi读取xls文件，并通过JDBC存入MySQL数据库
前提：
手头有一份我们班的资料，然后要供大家随时查到各自的消息。需要存入到数据库供使用。，于是手写了几段代码，哪里可以优化，希望各位大佬指点。
创建学生对象：
package com.cn.poi.xieyi;
public class Student {
private String gender;
private String className;
private String address;
private String telNum;
private String IDNum;
private String xuehao;
private String name;
public String getXuehao() {
return xuehao;
}
public void setXuehao(String xuehao) {
this.xuehao = xuehao;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getTelNum() {
return telNum;
}
public void setTelNum(String telNum) {
this.telNum = telNum;
}
public String getIDNum() {
return IDNum;
}
public void setIDNum(String iDNum) {
IDNum = iDNum;
}
@Override
public String toString() {
return "Student [gender=" + gender + ", className=" + className
+ ", address=" + address + ", telNum="
+ telNum + ", IDNum=" + IDNum + ", xuehao=" + xuehao
+ ", name=" + name + "]";
}
}然后主代码，很混乱，因为是自己看，写的超级乱。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
package com.cn.poi.xieyi;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
public class Main {
private static POIFSFileSystem fs;//poi文件流
private static HSSFWorkbook wb;//获得execl
private static HSSFRow row;//获得行
private static HSSFSheet sheet;//获得工作簿
public static void main(String[] args) throws Exception {
//加载文件的位置
InputStream in= new FileInputStream("D:\\笔记\\15机电工程协议书号.xls");
readXlsx(in);
}
public static void readXlsx(InputStream in){
List<Student> list = new ArrayList<>();
try {
fs = new POIFSFileSystem(in);
wb = new HSSFWorkbook(fs);
sheet=wb.getSheetAt(0);
//int rowfirst=sheet.getFirstRowNum();
int rowend=sheet.getLastRowNum();//获取最后一行
/**
* 获取每一行
*/
for (int i = 0; i <=rowend; i++) {
row=sheet.getRow(i);
// int colNum = row.getPhysicalNumberOfCells();//一行总列数
Student s = new Student();
for(int j = 0; j<11;j++){
if( row.getCell(j) != null){
row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
String xuehao = row.getCell(j).getStringCellValue();
/**
* 根据每次循环获取到的内容不同，设置给student对象的各个属性
*/
switch (j) {
case 2:
s.setXuehao(xuehao);
break;
case 3:
s.setName(xuehao);
case 4:
s.setGender(xuehao);
case 5 :
s.setClassName(xuehao);
case 6 :
s.setAddress(xuehao);
case 8 :
s.setTelNum(xuehao);
case 9 :
s.setIDNum(xuehao);
}
}
}
System.out.println(s);
/**
* 发送到数据库
*/
send(s);
//添加到list中方便后续使用
//list.add(s);
}
}catch (Exception e) {
e.printStackTrace();
}
}
/**
* 发送数据库的实现方法
* @param s
*/
public static void send(Student s) {
String sql = "insert into student (xuehao,name,gender,class_name,address,tel_num,ID_card) values (?,?,?,?,?,?,?)";
Connection conn =null;
PreparedStatement pstmt = null;
//加载驱动
try {
Class.forName("com.mysql.jdbc.Driver") ;
String url = "jdbc:mysql://localhost:3306/student_list" ;
String username = "root" ;
String password = "root" ;
//连接数据库
conn = DriverManager.getConnection(url , username , password ) ;
pstmt = conn.prepareStatement(sql);
//设置参数
pstmt.setString(1, s.getXuehao());
pstmt.setString(2, s.getName());
pstmt.setString(3, s.getGender());
pstmt.setString(4, s.getClassName());
pstmt.setString(5, s.getAddress());
pstmt.setString(6, s.getTelNum());
pstmt.setString(7, s.getIDNum());
//执行sql被影响的条数
// int i = pstmt.executeUpdate();
// System.out.println(i);
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭一些东西
if(pstmt != null){
try {
pstmt.close();
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
maven依赖：
<!-- [url]https://mvnrepository.com/artifact/org.apache.poi/poi[/url]
[url]http://www.35cm.com/thread-161-1-1.html[/url]
-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- [url]https://mvnrepository.com/artifact/org.testcontainers/mysql[/url] -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.23</version>
</dependency>
</dependencies>