Java实现Excel导入数据库，数据库中的数据导入到Excel
来源：互联网 发布：厚街淘宝客服招聘 编辑：程序博客网 时间：2024/05/22 01:53
【注意添加jxl.jar 和 数据库jar包】
package com.hp.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 连接数据库的工具类
* @author Wolf
*
*/
public class DBhelper {
publicfinal String DBDriver = "com.mysql.jdbc.Driver";
publicfinal String URL = "jdbc:mysql://127.0.0.1:3306/onlinexam";
Connectioncon = null;
ResultSetres = null;
publicvoid DataBase() {
try{
Class.forName(DBDriver);
con= DriverManager.getConnection(URL, "root", "123456");
}catch (ClassNotFoundException e) {
//TODO Auto-generated catch block
System.err.println("装载 JDBC/ODBC驱动程序失败。");
e.printStackTrace();
}catch (SQLException e) {
//TODO Auto-generated catch block
System.err.println("无法连接数据库");
e.printStackTrace();
}
}
// 根据Stringsql, String str[]查询返回结果集
public ResultSet Search(String sql, String str[]) {
DataBase();
try {
PreparedStatement pst=con.prepareStatement(sql);
if (str != null) {
for (int i = 0; i <str.length; i++) {
pst.setString(i + 1, str[i]);
}
}
res = pst.executeQuery();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return res;
}
/*
* 方法功能：增删修改
* 如果数组str[] 不为Object类型，为某一类型（如String）。那么如果数据类型不唯一，再转换成String类型后，与sql语句整合后执行增删改操作就会抛出类型不匹配错误。
*/
public int AddU(String sql, Object str[]) {
int a = 0;
DataBase();
try {
PreparedStatement pst =con.prepareStatement(sql);
if (str != null) {
for (int i = 0; i <str.length; i++) {
pst.setObject(i + 1, str[i]);
}
}
a = pst.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return a;
}
}
===============================================================================================================================
package com.hp.excel;
import java.io.File;
import java.sql.SQLException;
import java.util.List;
import com.hp.bean.Student;
import com.hp.bean.Teacher;
import com.hp.dao.StudentDao;
import com.hp.dao.TeacherDao;
import com.hp.dao.impl.StudentDaoImpl;
import com.hp.dao.impl.TeacherDaoImpl;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
* 数据的数据导入到Excel表
*
* @author Wolf
*
*/
public class TestDbToExcel {
/*
* 创建main（）对方法进行测试
*/
publicstatic void main(String[] args) {
TestDbToExcelaa = new TestDbToExcel();
try{
aa.StudentTestDbToExcel();
System.out.println("StudentTestDbToExcel()执行完毕");
//aa.TeacherTestDbToExcel();
//System.out.println("TeacherTestDbToExcel()执行完毕");
}catch (Exception e) {
//TODO Auto-generated catch block
e.printStackTrace();
}
}
/*
* 对学生表进行操作
*/
publicvoid StudentTestDbToExcel() throws Exception {
StudentDaostudentDao = new StudentDaoImpl();
WritableWorkbookwwb = null;
//创建可写入的Excel工作簿
StringfileName = "D://onlinexam/StudentTestDbToExcel.xls";
Filefile = new File(fileName);
if(!file.exists()) {
file.createNewFile();
}
//以fileName为文件名来创建一个Workbook
wwb= Workbook.createWorkbook(file);
//创建工作表
//学生编号(stu_id)+学生姓名(stu_name)+学生密码(stu_password)+学生性别(stu_sex)+学生出生日期(stu_born)+学生所在班级(cla_id)+学生所在系别(dep_id)
WritableSheetws = wwb.createSheet("Test Shee 1", 0);
//查询数据库中所有的数据
List<Student>list = studentDao.getStudentAllByDb();
//要插入到的Excel表格的行号，默认从0开始
Labellabelstu_id = new Label(0, 0, "学生编号(stu_id)");// 表示第一列第一行
Labellabelstu_name = new Label(1, 0, "学生姓名(stu_name)");
Labellabelstu_password = new Label(2, 0, "学生密码(stu_password)");
Labellabelstu_sex = new Label(3, 0, "学生性别(stu_sex)");
Labellabelstu_born = new Label(4, 0, "学生出生日期(stu_born)");
Labellabelcla_id = new Label(5, 0, "学生所在班级(cla_id)");
Labellabeldep_id = new Label(6, 0, "学生所在系别(dep_id)");
ws.addCell(labelstu_id);
ws.addCell(labelstu_name);
ws.addCell(labelstu_password);
ws.addCell(labelstu_sex);
ws.addCell(labelstu_born);
ws.addCell(labelcla_id);
ws.addCell(labeldep_id);
for(int i = 0; i < list.size(); i++) {
LabellabelId_i = new Label(0, i + 1, list.get(i).getStu_id() + "");//String
LabellabelName_i = new Label(1, i + 1, list.get(i).getStu_name());
LabellabelPassword_i = new Label(2, i + 1, list.get(i)
.getStu_password());
LabellabelSex_i = new Label(3, i + 1, list.get(i).getStu_sex());
LabellabelBorn_i = new Label(4, i + 1, list.get(i).getStu_born()
.toString());
LabellabelClaId_i = new Label(5, i + 1, list.get(i).getCla_id()
+"");
LabellabelDepId_i = new Label(6, i + 1, list.get(i).getDep_id()
+"");
ws.addCell(labelId_i);
ws.addCell(labelName_i);
ws.addCell(labelPassword_i);
ws.addCell(labelSex_i);
ws.addCell(labelBorn_i);
ws.addCell(labelClaId_i);
ws.addCell(labelDepId_i);
}
//写进文档
wwb.write();
//关闭Excel工作簿对象
wwb.close();
}
/*
* 对教师表进行操作
*/
publicvoid TeacherTestDbToExcel() throws Exception {
TeacherDaoteacherDao = new TeacherDaoImpl();
WritableWorkbookwwb = null;
//创建可写入的Excel工作簿
StringfileName = "D://onlinexam/TeacherTestDbToExcel.xls";
Filefile = new File(fileName);
if(!file.exists()) {
file.createNewFile();
}
//以fileName为文件名来创建一个Workbook
wwb= Workbook.createWorkbook(file);
//创建工作表
//教师编号(tea_id)+教师姓名(tea_name)+教师密码(tea_password)+教师性别(tea_sex)+教师出生日期(tea_born)+教师所在系别(dep_id)
WritableSheetws = wwb.createSheet("Test Shee 1", 0);
//查询数据库中所有的数据
List<Teacher>list = teacherDao.getTeacherAllByDb();
//要插入到的Excel表格的行号，默认从0开始
Labellabeltea_id = new Label(0, 0, "教师编号(tea_id)");// 表示第一列第一行
Labellabeltea_name = new Label(1, 0, "教师姓名(tea_name)");
Labellabeltea_password = new Label(2, 0, "教师密码(tea_password)");
Labellabeltea_sex = new Label(3, 0, "教师性别(tea_sex)");
Labellabeltea_born = new Label(4, 0, "教师出生日期(tea_born)");
Labellabeldep_id = new Label(6, 0, "教师所在系别(dep_id)");
ws.addCell(labeltea_id);
ws.addCell(labeltea_name);
ws.addCell(labeltea_password);
ws.addCell(labeltea_sex);
ws.addCell(labeltea_born);
ws.addCell(labeldep_id);
for(int i = 0; i < list.size(); i++) {
LabellabelId_i = new Label(0, i + 1, list.get(i).getTea_id() + "");//String
LabellabelName_i = new Label(1, i + 1, list.get(i).getTea_name());
LabellabelPassword_i = new Label(2, i + 1, list.get(i)
.getTea_password());
LabellabelSex_i = new Label(3, i + 1, list.get(i).getTea_sex());
LabellabelBorn_i = new Label(4, i + 1, list.get(i).getTea_born()
.toString());
LabellabelDepId_i = new Label(6, i + 1, list.get(i).getDep_id()
+"");
ws.addCell(labelId_i);
ws.addCell(labelName_i);
ws.addCell(labelPassword_i);
ws.addCell(labelSex_i);
ws.addCell(labelBorn_i);
ws.addCell(labelDepId_i);
}
//写进文档
wwb.write();
//关闭Excel工作簿对象
wwb.close();
}
}
===============================================================================================================================
package com.hp.excel;
import java.util.List;
import com.hp.bean.Student;
import com.hp.bean.Teacher;
import com.hp.dao.StudentDao;
import com.hp.dao.TeacherDao;
import com.hp.dao.impl.StudentDaoImpl;
import com.hp.dao.impl.TeacherDaoImpl;
import com.hp.util.DBhelper;
/**
* Excel表中的数据导入到MySql数据库
*
* @author Wolf
*
*/
public class TestExcelToDb {
/*
* 创建main（）对方法进行测试
*/
publicstatic void main(String[] args) throws Exception {
TestExcelToDbaa = new TestExcelToDb();
aa.StudentTestExcelToDb();
System.out.println("StudentTestExcelToDb()执行完毕");
}
/*
* 读取学生表
*/
publicvoid StudentTestExcelToDb() {
StudentDaostudentDao = new StudentDaoImpl();
//得到表格中所有的数据
List<Student>listExcel = studentDao
.getStudentAllByExcel("D://onlinexam/StudentTestDbToExcel.xls");
//得到数据库表中所有的数据
//List<Student> listDb=studentDao.getStudentAllByDb();
DBhelperdb = new DBhelper();
for(Student student : listExcel) {
intid = student.getStu_id();
if(!studentDao.isStudentExist(id)) {
//不存在就添加,stu_id未设置为自动增长
Stringsql = "insert into t_student(stu_id,stu_name,stu_password,stu_sex,stu_born,cla_id,dep_id)values(?,?,?,?,?,?,?)";
//如果数组 str 不为Object类型，为某一类型（如String）。那么如果数据类型不唯一，再转换成String类型后，与sql语句整合后执行增删改操作就会抛出类型不匹配错误。
Object[]str = new Object[] { student.getStu_id() + "",
student.getStu_name(),student.getStu_password(),
student.getStu_sex(),student.getStu_born(),
student.getCla_id()+ "", student.getDep_id() + "" };
db.AddU(sql,str);
System.out.println("TestExcelToDb类——StudentTestExcelToDb()——1");
}else {
//存在就更新
Stringsql = "update t_student setstu_name=?,stu_password=?,stu_sex=?,stu_born=?,cla_id=?,dep_id=? wherestu_id=?";
//如果数组 str 不为Object类型，为某一类型（如String）。那么如果数据类型不唯一，再转换成String类型后，与sql语句整合后执行增删改操作就会抛出类型不匹配错误。
Object[]str = new Object[] { student.getStu_name(),
student.getStu_password(),student.getStu_sex(),
student.getStu_born(),
student.getCla_id()+ "", student.getDep_id() + "",id+"" };
db.AddU(sql,str);
System.out.println("TestExcelToDb类——StudentTestExcelToDb()——2");
}
}
}
/*
* 读取教师表
*/
publicvoid TeacherTestExcelToDb() {
TeacherDaoteacherDao = new TeacherDaoImpl();
//得到表格中所有的数据
List<Teacher>listExcel = teacherDao.getTeacherAllByExcel("D://onlinexam/TeacherTestDbToExcel.xls");
//得到数据库表中所有的数据
//List<Teacher> listDb=teacherDaoImpl.getTeacherAllByDb();
DBhelperdb = new DBhelper();
for(Teacher teacher : listExcel) {
intid = teacher.getTea_id();
if(!teacherDao.isTeacherExist(id)) {
//不存在就添加,tea_id未设置为自动增长
Stringsql = "insert into t_teacher(tea_id,tea_name,tea_password,tea_sex,tea_born,dep_id)values(?,?,?,?,?,?)";
//如果数组 str 不为Object类型，为某一类型（如String）。那么如果数据类型不唯一，再转换成String类型后，与sql语句整合后执行增删改操作就会抛出类型不匹配错误。
Object[]str = new Object[] { teacher.getTea_id()+ "",
teacher.getTea_name(),teacher.getTea_password(),
teacher.getTea_sex(),teacher.getTea_born(), teacher.getDep_id() + "" };
db.AddU(sql,str);
System.out.println("TestExcelToDb类——TeacherTestExcelToDb()——1");
}else {
//存在就更新
Stringsql = "update t_teacher settea_name=?,tea_password=?,tea_sex=?,tea_born=?,dep_id=? where tea_id=?";
//如果数组 str 不为Object类型，为某一类型（如String）。那么如果数据类型不唯一，再转换成String类型后，与sql语句整合后执行增删改操作就会抛出类型不匹配错误。
Object[]str = new Object[] {teacher.getTea_name(), teacher.getTea_password(),
teacher.getTea_sex(),teacher.getTea_born(), teacher.getDep_id() + "",id+""};
db.AddU(sql,str);
System.out.println("TestExcelToDb类——TeacherTestExcelToDb()——2");
}
}
}
}
===============================================================================================================================
package com.hp.bean;
import java.io.Serializable;
import java.util.Date;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="t_student")
public class Student implements Serializable {
/**
*
*/
privatestatic final long serialVersionUID = 1L;
privateint stu_id;
privateString stu_name;
privateString stu_password;
privateString stu_sex;
privateDate stu_born;
privateint cla_id;
privateint dep_id;
//privateint age;
publicDate getStu_born() {
returnstu_born;
}
publicvoid setStu_born(Date stu_born) {
this.stu_born= stu_born;
}
publicString getStu_sex() {
returnstu_sex;
}
publicvoid setStu_sex(String stu_sex) {
this.stu_sex= stu_sex;
}
publicint getDep_id() {
returndep_id;
}
publicvoid setDep_id(int dep_id) {
this.dep_id= dep_id;
}
@Id
publicint getStu_id() {
returnstu_id;
}
publicvoid setStu_id(int stu_id) {
this.stu_id= stu_id;
}
publicString getStu_name() {
returnstu_name;
}
publicvoid setStu_name(String stu_name) {
this.stu_name= stu_name;
}
publicString getStu_password() {
returnstu_password;
}
publicvoid setStu_password(String stu_password) {
this.stu_password= stu_password;
}
publicint getCla_id() {
returncla_id;
}
publicvoid setCla_id(int cla_id) {
this.cla_id= cla_id;
}
@Override
publicString toString() {
return"Student [stu_id=" + stu_id + ", stu_name=" + stu_name
+", stu_password=" + stu_password + ", stu_sex=" + stu_sex
+", stu_born=" + stu_born + ", cla_id=" + cla_id + ",dep_id="
+dep_id + "]";
}
publicStudent() {
}
publicStudent(int stu_id, String stu_name, String stu_password,
Stringstu_sex, Date stu_born, int cla_id, int dep_id) {
this.stu_id= stu_id;
this.stu_name= stu_name;
this.stu_password= stu_password;
this.stu_sex= stu_sex;
this.stu_born= stu_born;
this.cla_id= cla_id;
this.dep_id= dep_id;
}
}
===================================================================================================================================
package com.hp.dao;
import java.util.Date;
import java.util.List;
import com.hp.bean.*;
import com.hp.util.Page;
//学生业务逻辑接口
public interface StudentDao {
........省略其他功能的方法
/*
* 查询学生表的所有信息
*/
publicList<Student> getStudentAllByDb();
/*
* 查询指定目录中电子表格中所有的数据
* file 文件完整路径
*/
publicList<Student> getStudentAllByExcel(String file);
/*
* 通过Id判断该学生是否存在
*/
public boolean isStudentExist(int sys_id);
}
==================================================================================================================================
package com.hp.dao.impl;
import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import com.hp.bean.CourseView;
import com.hp.bean.Department;
import com.hp.bean.QuesView;
import com.hp.bean.Questions;
import com.hp.bean.StuTest;
import com.hp.bean.StuTestSimpleView;
import com.hp.bean.StuTestView;
import com.hp.bean.Student;
import com.hp.bean.StudentView;
import com.hp.bean.Test;
import com.hp.bean.TestView;
import com.hp.dao.StudentDao;
import com.hp.util.DBhelper;
import com.hp.util.HibernateSessionFactory;
import com.hp.util.MySessionFactory;
import com.hp.util.Page;
//学生业务逻辑接口的实现类
public class StudentDaoImpl implements StudentDao {
........省略其他功能的方法
/*
* 查询学生表的所有信息
*/
publicList<Student> getStudentAllByDb() {
List<Student>list = new ArrayList<Student>();
try {
DBhelperdb = new DBhelper();
Stringsql = "select * from t_student";
ResultSetrs = db.Search(sql, null);
while(rs.next()) {
intstu_id = rs.getInt("stu_id");
Stringstu_name = rs.getString("stu_name");
Stringstu_password = rs.getString("stu_password");
Stringstu_sex = rs.getString("stu_sex");
Datestu_born = rs.getDate("stu_born");
intcla_id = rs.getInt("cla_id");
intdep_id = rs.getInt("dep_id");
//System.out.println(stu_id+" "+stu_password+" "+stu_born+
//" "+dep_id);
list.add(newStudent(stu_id, stu_name, stu_password, stu_sex,
stu_born,cla_id, dep_id));
}
} catch(SQLException e) {
// TODOAuto-generated catch block
e.printStackTrace();
}
return list;
}
/*
* 查询指定目录中电子表格中所有的数据 file 文件完整路径
*/
publicList<Student> getStudentAllByExcel(String file) {
List<Student>list_t_student = new ArrayList<Student>();
try {
Workbookrwb = Workbook.getWorkbook(new File(file));
Sheet rs= rwb.getSheet("Test Shee 1");// 或者rwb.getSheet(0)
int clos= rs.getColumns();// 得到所有的列
int rows= rs.getRows();// 得到所有的行
System.out.println("clos:"+ clos + "\t" + " rows:" + rows);
for (inti = 1; i < rows; i++) {
for(int j = 0; j < clos; j++) {
//第一个是列数，第二个是行数
Stringstu_id = rs.getCell(j++, i).getContents();// 默认最左边编号也算一列
//所以这里得j++
String stu_name =rs.getCell(j++, i).getContents();
Stringstu_password = rs.getCell(j++, i).getContents();
Stringstu_sex = rs.getCell(j++, i).getContents();
Stringstu_born = rs.getCell(j++, i).getContents();
Stringcla_id = rs.getCell(j++, i).getContents();
Stringdep_id = rs.getCell(j++, i).getContents();
//将String类型的 stu_born 转换成java.util.Date类型的 stu_born_d
SimpleDateFormatsdf = new SimpleDateFormat("yyyy-MM-dd");// 小写的mm表示的是分钟
java.util.Datestu_born_d = new Date();
try{
stu_born_d= sdf.parse(stu_born);
}catch (ParseException e) {
//TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("Student[stu_id=" + stu_id
+", stu_name=" + stu_name + ", stu_password="
+stu_password + ", stu_sex=" + stu_sex
+", stu_born=" + stu_born + ", cla_id=" + cla_id
+", dep_id=" + dep_id + "]");
list_t_student
.add(newStudent(Integer.parseInt(stu_id),
stu_name,stu_password, stu_sex,
stu_born_d,Integer.parseInt(cla_id),
Integer.parseInt(dep_id)));
}
}
} catch(BiffException e) {
// TODOAuto-generated catch block
e.printStackTrace();
} catch(IOException e) {
// TODOAuto-generated catch block
e.printStackTrace();
}
returnlist_t_student;
}
/*
* 通过Id判断是否存在
*/
public booleanisStudentExist(int stu_id) {
try {
DBhelperdb = new DBhelper();
ResultSetrs = db.Search("select * from t_student where stu_id=?",
newString[] { stu_id + "" });
if(rs.next()) {
returntrue;
}
} catch(SQLException e) {
// TODOAuto-generated catch block
e.printStackTrace();
}
return false;
}
}
===================================================================================================================================
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- java实现Excel导入数据库，数据库中的数据导入到Excel表格中
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- 在子线程中创建新的窗体，遇到的问题。
- 简单爬虫，突破IP访问限制和复杂验证码，小总结
- linux软件包管理
- 完备环序列问题
- Dapper完美兼容Oracle，执行存储过程，并返回结果集。
- Java实现Excel导入数据库，数据库中的数据导入到Excel
- 数字图像处理：第五章 代数运算
- Aspx页面模拟WebService功能
- WEB前端开发人员须知的常见浏览器兼容问题及解决技巧
- 关于多核游览器指定渲染内核的方法。
- Java向上转型
- C语言结构体学习疑惑
- 我的VPS选择之路
- 【有效的单元测试】读书笔记第7章 可测的设计
