新建Java项目ImportExcelDemo,环境是eclipse+jdk1.8+mysql5.5,主要是完成一个单表,没有主外键关联的导入导出的功能.将excel中的数据,导入到数据库中;将数据库中的数据导出到excel中.
主要代码,先使用sql语句生成,t_student表.然后,建立student的entity类,属性包括主键,学号,姓名,性别,学院和专业.使用的DBHelper的jdbc原生sql语句处理,封装了2个简单的方法,查询和添加.
工具库帮助类
/**
* 数据库帮助类,提供:连接数据库,增删改查功能
* @author liuyanling
*
*/
public class DBHelper {
//驱动
final String driver = "com.mysql.jdbc.Driver";
//数据库连接
final String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8";
Connection connection = null;
//查询结果集
ResultSet resultSet = null;
/**
* 实例化时,连接数据库
*/
public DBHelper() {
// 1.加载驱动
try {
Class.forName(driver);
// 2.连接
connection = DriverManager.getConnection(url,"root","123456");
} catch (ClassNotFoundException e) {
System.err.println("装载 JDBC驱动程序失败。");
e.printStackTrace();
} catch (SQLException e) {
System.err.println("无法连接数据库");
e.printStackTrace();
}
}
/**
* 查询记录,根据sql语句和参数集
* @param sql
* @param strParamters
* @return
*/
public ResultSet query(String sql,String[] strParamters ) {
try {
//1.预编译
PreparedStatement preStatement = connection.prepareStatement(sql);
//2.添加参数
if(strParamters != null) {
for(int i = 0;i<strParamters.length;i++) {
preStatement.setString(i+1, strParamters[i]);
}
}
//3.执行查询,并返回结果
resultSet = preStatement.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return resultSet;
}
/**
* 添加记录,根据sql语句和参数集
* @param sql
* @param strParamters
* @return
*/
public int add(String sql,String[] strParamters) {
//受影响的条数
int resultCount = 0;
try {
//1.预处理
PreparedStatement preStatement = connection.prepareStatement(sql);
//2.添加参数
if(strParamters != null) {
for(int i = 0;i<strParamters.length;i++) {
preStatement.setString(i+1, strParamters[i]);
}
}
//3.执行更新
resultCount = preStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return resultCount;
}
}
导入Excel
然后导入Excel的主要代码,就是将excel中的数据,读出,变成student的实体集合,最后更新这个实体集合就行.
在ImportExcel类的main方法中,调用studentService的静态方法getStudentByExcel.需要写一个地址,在该地址上放上这个文件.
// 1.获取excel的数据,【要求在指定路径下,必须有这么一个文件】
List<Student> listStudentFromExcel = StudentService
.getStudentByExcel("D://student.xls");
文件中的内容,第一行为英文的表头,对应数据库中的字段名.但是实际上,用中文的表头才比较常见.
然后是getStudentByExcel()方法.
使用jxl.jar中的Workbook类,获取get指定excel的工作簿,然后工作簿获取Sheet工作表.然后将工作表中的数据,一行一行循环,将一行的单元格一个一个的取出,赋值到Student实体上.最后,循环掉一行,就是一个实体了.循环完一个工作表,就是一个实体集合了.而由于代码取第一列单元格是id,第二列是stuNo,所以对于excel模板不能任意换列.
/**
* 将Excel文件中的数据,转换为Student的集合,并返回
*
* @param ExcelFile
* @return
*/
public static List<Student> getStudentByExcel(String ExcelFile) {
// excel中的数据转换为的student的集合
List<Student> listStudent = new ArrayList<Student>();
// 1.获取excel
try {
Workbook wBook = Workbook.getWorkbook(new File(ExcelFile));
// 2.获取工作簿,sheet(工作表)
Sheet sheet = wBook.getSheet(0);
// 3.读取sheet中的数据,生成List<Student>,学生信息集合
int colLength = sheet.getColumns();
int rowLength = sheet.getRows();
// 3.1.遍历行列,取出cell中的数据,放到Student对象中
// 3.2.第一行是表头,所以从第二行(下标为1),开始遍历
for (int i = 1; i < rowLength; i++) {
// 3.3.列,从0开始,自增遍历
int j = 0;
// 3.4.取出cell,第i行和第j列,【要求excel模板中列的顺序必须为指定的顺序,否则取出数据会不对】
String id = sheet.getCell(j++, i).getContents();
String stuNo = sheet.getCell(j++, i).getContents();
String stuName = sheet.getCell(j++, i).getContents();
String stuSex = sheet.getCell(j++, i).getContents();
String department = sheet.getCell(j++, i).getContents();
String major = sheet.getCell(j++, i).getContents();
// 3.5.添加student集合中
Student student = new Student(id, stuNo, stuName, stuSex,
department, major);
listStudent.add(student);
// 打印信息
System.out
.println("---excel中的学生-------: " + student.toString());
}
} catch (BiffException | IOException e) {
e.printStackTrace();
}
// 4.返回学生信息集合,没有数据,则size为0
return listStudent;
}
然后更新到数据库中,就是一条记录一条记录的添加到数据库中.这里的问题是,I/O操作太多,严重影响效率.
DBHelper dbHelper = new DBHelper();
// 2.判断数据库中excel的数据是否存在;存在,更新;不存在,添加;
// 2.1.从Student中的集合取出student
for (Student stu : listStudentFromExcel) {
// 获取主键id,用于判断数据是否存在
String id = stu.getId();
String sql = "";
String[] strParamters = null;
// 2.2.不存在,添加
if (!StudentService.isExist(id)) {
sql = "insert into t_student(id, stuNo, stuName, stuSex,department, major) values(?,?,?,?,?,?)";
strParamters = new String[] { stu.getId(), stu.getStuNo(),
stu.getStuName(), stu.getStuSex(),
stu.getDepartment(), stu.getMajor() };
} else {
// 2.3.存在,更新
sql = "update t_student set stuNo=?, stuName=?, stuSex=?,department=?, major=? where id=?";
strParamters = new String[] {stu.getStuNo(),
stu.getStuName(), stu.getStuSex(),
stu.getDepartment(), stu.getMajor(), stu.getId()};
}
//3.执行
dbHelper.add(sql,strParamters);
}
导出Excel
在ExportExcel的main方法中,也是指定好路径.没有这个文件就创建一下.若是web项目,也可以通过response将excel输出到界面上,用户下载即可.
ExportExcel.export("D://student2.xls");
导出的代码就是将就是将数据库中的数据取出,变成实体集合.
然后创建create工作簿和Sheet工作表,接着创建表头.然后将实体集合遍历,一个实体就是一行记录,将数据一个一个的赋值到单元格中.不过赋值不是直接添加的cell中,而是创建Label,指明label的列,行和label中的值.然后Sheet添加addCell(label).最后将所有数据写到工作簿中,关闭工作簿.
/**
* 导出数据,到指定的Excel中
*/
public static void export(String ExcelFile) {
// 1.新建导出的Excel文件
File file = new File(ExcelFile);
try {
if (!file.exists()) {
file.createNewFile();
}
// 1.1.建立工作簿和Sheet
WritableWorkbook wwBook = Workbook.createWorkbook(file);
WritableSheet wSheet = wwBook.createSheet("学生信息", 0);
// 2.读取数据库中的数据
List<Student> listStudentFromDB = StudentService.getStudentByDB();
// 3.将数据写入到Excel文件中,cell中
// 3.1.准备表头
Label labelHeaderId = new Label(0, 0, "编号(id)");// 第1列,第1行,编号为id
Label labelHeaderStuNo = new Label(1, 0, "学号(stuNo)");
Label labelHeaderStuName = new Label(2, 0, "姓名(stuName)");
Label labelHeaderStuSex = new Label(3, 0, "性别(stuSex)");
Label labelHeaderDepartment = new Label(4, 0, "学院(department)");
Label labelHeaderMajor = new Label(5, 0, "专业(major)");
wSheet.addCell(labelHeaderId);
wSheet.addCell(labelHeaderStuNo);
wSheet.addCell(labelHeaderStuName);
wSheet.addCell(labelHeaderStuSex);
wSheet.addCell(labelHeaderDepartment);
wSheet.addCell(labelHeaderMajor);
// 3.2.准备表内容,从第二行开始填充
for (int i = 0; i < listStudentFromDB.size(); i++) {
Label labelId = new Label(0, i + 1, listStudentFromDB.get(i)
.getId());
Label labelStuNo = new Label(1, i + 1, listStudentFromDB.get(i)
.getStuNo());
Label labelStuName = new Label(2, i + 1, listStudentFromDB.get(
i).getStuName());
Label labelStuSex = new Label(3, i + 1, listStudentFromDB
.get(i).getStuSex());
Label labelDepartment = new Label(4, i + 1, listStudentFromDB
.get(i).getDepartment());
Label labelMajor = new Label(5, i + 1, listStudentFromDB.get(i)
.getMajor());
wSheet.addCell(labelId);
wSheet.addCell(labelStuNo);
wSheet.addCell(labelStuName);
wSheet.addCell(labelStuSex);
wSheet.addCell(labelDepartment);
wSheet.addCell(labelMajor);
}
// 4.写进文档,关闭工作簿
wwBook.write();
wwBook.close();
} catch (IOException | WriteException e) {
e.printStackTrace();
}
}
而从数据库中获取所有记录的功能,也就是一个select查询,然后处理了一下resultSet的结果,变成list集合.
/**
* 从数据库中查询所有的学生记录,返回
*
* @return
*/
public static List<Student> getStudentByDB() {
// 1.执行查询
ResultSet rs = dbHelper.query("select * from t_student", null);
List<Student> listStudent = new ArrayList<Student>();
// 2.结果集变为学生集合
try {
while (rs != null && rs.next()) {
//2.1取出数据
String id = rs.getString("id");
String stuNo = rs.getString("stuNo");
String stuName = rs.getString("stuName");
String stuSex = rs.getString("stuSex");
String department = rs.getString("department");
String major = rs.getString("major");
//2.2添加到集合中
Student student = new Student(id, stuNo, stuName, stuSex,
department, major);
listStudent.add(student);
// 打印信息
System.out.println("---数据库中的学生-------: " + student.toString());
}
} catch (SQLException e) {
e.printStackTrace();
}
//3.返回学生列表
return listStudent;
}
以上,就是一个excel简单的导入导出,尽管完成excel导入导出的功能没有问题.但是这个excel项目很多问题,写死了excel的地址,excel上传应该是通过文件上传工具,上传的.Excel的模板的列不能变,还是英文的表头,并且代码中根本没用表头,直接用的是excel的数据内容.并且插入或更新到数据库中的I/O操作太多,效率很低.所以还需要优化.
附注
项目下载:上传下载Excel的Demo