您的位置: 首页  >  文章  >  excel导入导出

excel导入导出

分类: 文章 • 2023-11-15 09:52:34

新建Java项目ImportExcelDemo,环境是eclipse+jdk1.8+mysql5.5,主要是完成一个单表,没有主外键关联的导入导出的功能.将excel中的数据,导入到数据库中;将数据库中的数据导出到excel中.

      主要代码,先使用sql语句生成，t_student表.然后,建立student的entity类,属性包括主键,学号,姓名,性别,学院和专业.使用的DBHelper的jdbc原生sql语句处理,封装了2个简单的方法,查询和添加.

工具库帮助类

[java] view plain copy
  1. /** 
  2.  * 数据库帮助类,提供:连接数据库,增删改查功能 
  3.  * @author liuyanling 
  4.  * 
  5.  */  
  6. public class DBHelper {  
  7.   
  8.     //驱动  
  9.     final String driver = "com.mysql.jdbc.Driver";  
  10.   
  11.     //数据库连接  
  12.     final String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8";  
  13.     Connection connection = null;  
  14.       
  15.     //查询结果集  
  16.     ResultSet resultSet = null;  
  17.       
  18.     /** 
  19.      * 实例化时,连接数据库 
  20.      */  
  21.     public DBHelper() {  
  22.         // 1.加载驱动  
  23.         try {  
  24.             Class.forName(driver);  
  25.             // 2.连接  
  26.             connection = DriverManager.getConnection(url,"root","123456");  
  27.         } catch (ClassNotFoundException e) {  
  28.             System.err.println("装载 JDBC驱动程序失败。");  
  29.             e.printStackTrace();  
  30.         } catch (SQLException e) {  
  31.             System.err.println("无法连接数据库");  
  32.             e.printStackTrace();  
  33.         }  
  34.     }  
  35.       
  36.     /** 
  37.      * 查询记录,根据sql语句和参数集 
  38.      * @param sql 
  39.      * @param strParamters 
  40.      * @return 
  41.      */  
  42.     public ResultSet query(String sql,String[] strParamters ) {  
  43.         try {  
  44.             //1.预编译  
  45.             PreparedStatement preStatement = connection.prepareStatement(sql);  
  46.             //2.添加参数  
  47.             if(strParamters != null) {  
  48.                 for(int i = 0;i<strParamters.length;i++) {  
  49.                     preStatement.setString(i+1, strParamters[i]);  
  50.                 }  
  51.             }  
  52.             //3.执行查询,并返回结果  
  53.             resultSet = preStatement.executeQuery();  
  54.         } catch (SQLException e) {  
  55.             e.printStackTrace();  
  56.         }  
  57.         return resultSet;  
  58.     }  
  59.       
  60.     /** 
  61.      * 添加记录,根据sql语句和参数集 
  62.      * @param sql 
  63.      * @param strParamters 
  64.      * @return 
  65.      */  
  66.     public int add(String sql,String[] strParamters) {  
  67.         //受影响的条数  
  68.         int resultCount = 0;  
  69.         try {  
  70.             //1.预处理  
  71.             PreparedStatement preStatement = connection.prepareStatement(sql);  
  72.             //2.添加参数  
  73.             if(strParamters != null) {  
  74.                 for(int i = 0;i<strParamters.length;i++) {  
  75.                     preStatement.setString(i+1, strParamters[i]);  
  76.                 }  
  77.             }  
  78.             //3.执行更新  
  79.             resultCount = preStatement.executeUpdate();  
  80.         } catch (SQLException e) {  
  81.             e.printStackTrace();  
  82.         }  
  83.           
  84.         return resultCount;  
  85.     }  
  86. }  

导入Excel

         然后导入Excel的主要代码,就是将excel中的数据,读出,变成student的实体集合,最后更新这个实体集合就行.

         在ImportExcel类的main方法中,调用studentService的静态方法getStudentByExcel.需要写一个地址,在该地址上放上这个文件.

[java] view plain copy
  1. // 1.获取excel的数据,【要求在指定路径下，必须有这么一个文件】  
  2. List<Student> listStudentFromExcel = StudentService  
  3.         .getStudentByExcel("D://student.xls");  

             文件中的内容,第一行为英文的表头,对应数据库中的字段名.但是实际上,用中文的表头才比较常见.


excel导入导出

         然后是getStudentByExcel()方法.

         使用jxl.jar中的Workbook类,获取get指定excel的工作簿,然后工作簿获取Sheet工作表.然后将工作表中的数据,一行一行循环,将一行的单元格一个一个的取出,赋值到Student实体上.最后,循环掉一行,就是一个实体了.循环完一个工作表,就是一个实体集合了.而由于代码取第一列单元格是id,第二列是stuNo,所以对于excel模板不能任意换列.

[java] view plain copy
  1. /** 
  2.  * 将Excel文件中的数据,转换为Student的集合,并返回 
  3.  *  
  4.  * @param ExcelFile 
  5.  * @return 
  6.  */  
  7. public static List<Student> getStudentByExcel(String ExcelFile) {  
  8.     // excel中的数据转换为的student的集合  
  9.     List<Student> listStudent = new ArrayList<Student>();  
  10.   
  11.     // 1.获取excel  
  12.     try {  
  13.         Workbook wBook = Workbook.getWorkbook(new File(ExcelFile));  
  14.         // 2.获取工作簿,sheet(工作表)  
  15.         Sheet sheet = wBook.getSheet(0);  
  16.         // 3.读取sheet中的数据,生成List<Student>，学生信息集合  
  17.         int colLength = sheet.getColumns();  
  18.         int rowLength = sheet.getRows();  
  19.   
  20.         // 3.1.遍历行列,取出cell中的数据,放到Student对象中  
  21.         // 3.2.第一行是表头,所以从第二行(下标为1),开始遍历  
  22.         for (int i = 1; i < rowLength; i++) {  
  23.             // 3.3.列,从0开始，自增遍历  
  24.             int j = 0;  
  25.             // 3.4.取出cell,第i行和第j列,【要求excel模板中列的顺序必须为指定的顺序,否则取出数据会不对】  
  26.             String id = sheet.getCell(j++, i).getContents();  
  27.             String stuNo = sheet.getCell(j++, i).getContents();  
  28.             String stuName = sheet.getCell(j++, i).getContents();  
  29.             String stuSex = sheet.getCell(j++, i).getContents();  
  30.             String department = sheet.getCell(j++, i).getContents();  
  31.             String major = sheet.getCell(j++, i).getContents();  
  32.   
  33.             // 3.5.添加student集合中  
  34.             Student student = new Student(id, stuNo, stuName, stuSex,  
  35.                     department, major);  
  36.             listStudent.add(student);  
  37.   
  38.             // 打印信息  
  39.             System.out  
  40.                     .println("---excel中的学生-------: " + student.toString());  
  41.         }  
  42.   
  43.     } catch (BiffException | IOException e) {  
  44.         e.printStackTrace();  
  45.     }  
  46.   
  47.     // 4.返回学生信息集合,没有数据,则size为0  
  48.     return listStudent;  
  49. }  

       然后更新到数据库中,就是一条记录一条记录的添加到数据库中.这里的问题是,I/O操作太多,严重影响效率.

[java] view plain copy
  1. DBHelper dbHelper = new DBHelper();  
  2. // 2.判断数据库中excel的数据是否存在;存在,更新;不存在,添加;  
  3. // 2.1.从Student中的集合取出student  
  4. for (Student stu : listStudentFromExcel) {  
  5.     // 获取主键id,用于判断数据是否存在  
  6.     String id = stu.getId();  
  7.     String sql = "";  
  8.     String[] strParamters = null;  
  9.       
  10.     // 2.2.不存在，添加  
  11.     if (!StudentService.isExist(id)) {  
  12.         sql = "insert into t_student(id, stuNo, stuName, stuSex,department, major) values(?,?,?,?,?,?)";  
  13.         strParamters = new String[] { stu.getId(), stu.getStuNo(),  
  14.                 stu.getStuName(), stu.getStuSex(),  
  15.                 stu.getDepartment(), stu.getMajor() };  
  16.     } else {  
  17.         // 2.3.存在，更新  
  18.         sql = "update t_student set stuNo=?, stuName=?, stuSex=?,department=?, major=? where id=?";  
  19.         strParamters = new String[] {stu.getStuNo(),  
  20.                 stu.getStuName(), stu.getStuSex(),  
  21.                 stu.getDepartment(), stu.getMajor(), stu.getId()};  
  22.     }  
  23.       
  24.     //3.执行  
  25.     dbHelper.add(sql,strParamters);  
  26. }  

导出Excel

       在ExportExcel的main方法中,也是指定好路径.没有这个文件就创建一下.若是web项目,也可以通过response将excel输出到界面上,用户下载即可.

[java] view plain copy
  1. ExportExcel.export("D://student2.xls");  

       导出的代码就是将就是将数据库中的数据取出,变成实体集合.

       然后创建create工作簿和Sheet工作表,接着创建表头.然后将实体集合遍历,一个实体就是一行记录,将数据一个一个的赋值到单元格中.不过赋值不是直接添加的cell中,而是创建Label,指明label的列,行和label中的值.然后Sheet添加addCell(label).最后将所有数据写到工作簿中,关闭工作簿.

[java] view plain copy
  1. /** 
  2.  * 导出数据,到指定的Excel中 
  3.  */  
  4. public static void export(String ExcelFile) {  
  5.     // 1.新建导出的Excel文件  
  6.     File file = new File(ExcelFile);  
  7.     try {  
  8.         if (!file.exists()) {  
  9.             file.createNewFile();  
  10.         }  
  11.   
  12.         // 1.1.建立工作簿和Sheet  
  13.         WritableWorkbook wwBook = Workbook.createWorkbook(file);  
  14.         WritableSheet wSheet = wwBook.createSheet("学生信息", 0);  
  15.   
  16.         // 2.读取数据库中的数据  
  17.         List<Student> listStudentFromDB = StudentService.getStudentByDB();  
  18.   
  19.         // 3.将数据写入到Excel文件中,cell中  
  20.         // 3.1.准备表头  
  21.         Label labelHeaderId = new Label(0, 0, "编号(id)");// 第1列，第1行，编号为id  
  22.         Label labelHeaderStuNo = new Label(1, 0, "学号(stuNo)");  
  23.         Label labelHeaderStuName = new Label(2, 0, "姓名(stuName)");  
  24.         Label labelHeaderStuSex = new Label(3, 0, "性别(stuSex)");  
  25.         Label labelHeaderDepartment = new Label(4, 0, "学院(department)");  
  26.         Label labelHeaderMajor = new Label(5, 0, "专业(major)");  
  27.   
  28.         wSheet.addCell(labelHeaderId);  
  29.         wSheet.addCell(labelHeaderStuNo);  
  30.         wSheet.addCell(labelHeaderStuName);  
  31.         wSheet.addCell(labelHeaderStuSex);  
  32.         wSheet.addCell(labelHeaderDepartment);  
  33.         wSheet.addCell(labelHeaderMajor);  
  34.   
  35.         // 3.2.准备表内容,从第二行开始填充  
  36.         for (int i = 0; i < listStudentFromDB.size(); i++) {  
  37.             Label labelId = new Label(0, i + 1, listStudentFromDB.get(i)  
  38.                     .getId());  
  39.             Label labelStuNo = new Label(1, i + 1, listStudentFromDB.get(i)  
  40.                     .getStuNo());  
  41.             Label labelStuName = new Label(2, i + 1, listStudentFromDB.get(  
  42.                     i).getStuName());  
  43.             Label labelStuSex = new Label(3, i + 1, listStudentFromDB  
  44.                     .get(i).getStuSex());  
  45.             Label labelDepartment = new Label(4, i + 1, listStudentFromDB  
  46.                     .get(i).getDepartment());  
  47.             Label labelMajor = new Label(5, i + 1, listStudentFromDB.get(i)  
  48.                     .getMajor());  
  49.   
  50.             wSheet.addCell(labelId);  
  51.             wSheet.addCell(labelStuNo);  
  52.             wSheet.addCell(labelStuName);  
  53.             wSheet.addCell(labelStuSex);  
  54.             wSheet.addCell(labelDepartment);  
  55.             wSheet.addCell(labelMajor);  
  56.         }  
  57.   
  58.         // 4.写进文档,关闭工作簿  
  59.         wwBook.write();  
  60.         wwBook.close();  
  61.   
  62.     } catch (IOException | WriteException e) {  
  63.         e.printStackTrace();  
  64.     }  
  65.   
  66. }  

       而从数据库中获取所有记录的功能,也就是一个select查询,然后处理了一下resultSet的结果,变成list集合.

[java] view plain copy
  1. /** 
  2.  * 从数据库中查询所有的学生记录，返回 
  3.  *  
  4.  * @return 
  5.  */  
  6. public static List<Student> getStudentByDB() {  
  7.     // 1.执行查询  
  8.     ResultSet rs = dbHelper.query("select * from t_student", null);  
  9.     List<Student> listStudent = new ArrayList<Student>();  
  10.     // 2.结果集变为学生集合  
  11.     try {  
  12.         while (rs != null && rs.next()) {  
  13.             //2.1取出数据  
  14.             String id = rs.getString("id");  
  15.             String stuNo = rs.getString("stuNo");  
  16.             String stuName = rs.getString("stuName");  
  17.             String stuSex = rs.getString("stuSex");  
  18.             String department = rs.getString("department");  
  19.             String major = rs.getString("major");  
  20.               
  21.             //2.2添加到集合中  
  22.             Student student = new Student(id, stuNo, stuName, stuSex,  
  23.                     department, major);  
  24.             listStudent.add(student);  
  25.   
  26.             // 打印信息  
  27.             System.out.println("---数据库中的学生-------: " + student.toString());  
  28.            
  29.         }  
  30.     } catch (SQLException e) {  
  31.         e.printStackTrace();  
  32.     }  
  33.       
  34.     //3.返回学生列表  
  35.     return listStudent;  
  36. }        

       以上,就是一个excel简单的导入导出,尽管完成excel导入导出的功能没有问题.但是这个excel项目很多问题,写死了excel的地址,excel上传应该是通过文件上传工具,上传的.Excel的模板的列不能变,还是英文的表头,并且代码中根本没用表头,直接用的是excel的数据内容.并且插入或更新到数据库中的I/O操作太多,效率很低.所以还需要优化.

相关推荐