excel配合java和mysql_java-读excel，写入mysql

读excel

package xsl2mysql;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

public class xlsread {

public List readExcel(String xlspath) {

File file = new File(xlspath);

try {

// 创建输入流，读取Excel

InputStream is = new FileInputStream(file.getAbsolutePath());

// jxl提供的Workbook类

Workbook wb = Workbook.getWorkbook(is);

// Excel的页签数量

int sheet_size = wb.getNumberOfSheets();

for (int index = 0; index < sheet_size; index++) {

List outerList=new ArrayList();

// 每个页签创建一个Sheet对象

Sheet sheet = wb.getSheet(index);

// sheet.getRows()返回该页的总行数

for (int i = 0; i < sheet.getRows(); i++) {

List innerList=new ArrayList();

// sheet.getColumns()返回该页的总列数

for (int j = 0; j < sheet.getColumns(); j++) {

String cellinfo = sheet.getCell(j, i).getContents();

if(cellinfo.isEmpty()){

continue;

}

innerList.add(cellinfo);

//System.out.print(cellinfo);

}

outerList.add(i, innerList);

//System.out.println();

}

return outerList;

}

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (BiffException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

return null;

}

}

首先mysql需要建表

package xsl2mysql;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.*;

public class write2mysql {

// JDBC 驱动名及数据库 URL

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

static final String DB_URL = "jdbc:mysql://192.168.124.145:30963/RUNOOB";

// 数据库的用户名与密码，需要根据自己的设置

static final String USER = "root";

static final String PASS = "root";

public static void main(String[] args) {

Connection conn = null;

Statement stmt = null;

try{

// 注册 JDBC 驱动

Class.forName("com.mysql.cj.jdbc.Driver");

// 打开链接

System.out.println("连接数据库...");

conn = DriverManager.getConnection(DB_URL,USER,PASS);

// 执行查询

System.out.println(" 实例化Statement对象...");

stmt = conn.createStatement();

String sql;

sql = "CREATE TABLE bugall (id int not null, 问题类型 varchar(20) not null, 标识 char(20) not null, 主题 varchar(100) not null, 经办人 varchar(20) not null, 报告人 char(20) not null, 优先级 varchar(10) not null, 状态 varchar(10) not null,解决结果 varchar(10) not null,创建 Datetime not null,更新 Datetime not null, primary key (id));";

stmt.executeUpdate(sql);

stmt.close();

conn.close();

}catch(SQLException se){

// 处理 JDBC 错误

se.printStackTrace();

}catch(Exception e){

// 处理 Class.forName 错误

e.printStackTrace();

}finally{

// 关闭资源

try{

if(stmt!=null) stmt.close();

}catch(SQLException se2){

}// 什么都不做

try{

if(conn!=null) conn.close();

}catch(SQLException se){

se.printStackTrace();

}

}

System.out.println("Goodbye!");

}

}

读取到的List传入mysql

1.使用perparestatument

2.数据格式里面有个字符串转date，然后util.date转sql.date

package xsl2mysql;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.write.Label;

import java.io.File;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

public class test {

public void testsql(List newlist) throws Exception {

try {

Class.forName("com.mysql.jdbc.Driver");

} catch (Exception e) {

e.printStackTrace();

}

Connection c = DriverManager.getConnection("jdbc:mysql://192.168.124.145:30963/RUNOOB", "root", "root");

String sql = "insert into bugall values(?,?,?,?,?,?,?,?,?,?,?)";

PreparedStatement ps = c.prepareStatement(sql);

//SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy年MM月dd日 ");

SimpleDateFormat sDateFormat=new SimpleDateFormat("MM/dd/yy HH:mm");

for (int i = 0; i < newlist.size()-1; i++) {

List innerList = new ArrayList();

List list = (List) newlist.get(i);

ps.setInt(1, i + 1);

ps.setString(2, list.get(0).toString());

ps.setString(3, list.get(1).toString());

ps.setString(4, list.get(2).toString());

ps.setString(5, list.get(3).toString());

ps.setString(6, list.get(4).toString());

ps.setString(7, list.get(5).toString());

ps.setString(8, list.get(6).toString());

ps.setString(9, list.get(7).toString());

try{

Date date1 = sDateFormat.parse(list.get(8).toString());

java.sql.Date datesql1 = new java.sql.Date(date1.getTime());

ps.setDate(10, datesql1);

Date date2 = sDateFormat.parse(list.get(9).toString());

java.sql.Date datesql2 = new java.sql.Date(date2.getTime());

ps.setDate(11, datesql2);

} catch (ParseException px){

px.printStackTrace();

}

ps.execute();

}

}

}