新建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操作太多,效率很低.所以还需要优化.