poi mysql_纯手写利用poi读取xls文件，并通过JDBC存入MySQL数据库

[Java] 纯文本查看 复制代码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 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();

}

}

}

}

}