jxl导出Excel文件案例

迈不过友情╰ 2021-12-10 00:15 417阅读 0赞

导入jar包：

<dependency>
          <groupId>net.sourceforge.jexcelapi</groupId>
          <artifactId>jxl</artifactId>
          <version>2.6.10</version>
    </dependency>

**jxl文件导出关键方法：**

// 创建workbook对象                                    输出流
    WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
    
    // 创建一张表                                表名    下标
    WritableSheet sheet1 = workbook.createSheet("sheet1", 0);
    
    //        新建单元格    列号 行号 单元格的值
    Label gender = new Label(0, 0, "性别");
    
    // 添加单元格
    sheet1.addCell(gender);
    
    // 将文件写出
    workbook.write();
    
    // 关闭
    workbook.close();

**实体类：**

@Builder
    @Getter
    @Setter
    @AllArgsConstructor
    @NoArgsConstructor
    @Entity
    @ToString
    @Table(name = "student")
    public class Student {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private int id;
    
        private String username;
    
        private String password;
    
        private Boolean gender;
    
        private int grade;
    
        private Boolean isDel;
    
        @DateTimeFormat(pattern = "yyyy-MM-dd")
        @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
        private Date birthday;
    }

**导出文件类： **

@RestController
    @RequestMapping("/index")
    public class StudentController {
    
        @GetMapping("/downloadBatch")
        public void downloadBatch( HttpServletResponse response) {
            try {
                // 通过查询条件获得数据
                BaseResp<List<Student>> baseResp = studentService.selectAll();
                List<Student> data = baseResp.getData();
    
                ServletOutputStream outputStream = response.getOutputStream();
                response.setContentType("application/binary;charset=UTF-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("student.xls", "UTF-8"));
                // 创建workbook对象
                WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
                // 创建一张表
                WritableSheet sheet1 = workbook.createSheet("sheet1", 0);
    
                //设置表每一列的宽度
                for (int i = 0; i < 6; i++) {
                    sheet1.setColumnView(i, 20);
                }
                //设置字体大小和颜色
                WritableFont baseFont = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD, false);
                WritableCellFormat wCellFormatC = new WritableCellFormat(baseFont);
                // 设置对齐方式
                wCellFormatC.setAlignment(Alignment.CENTRE);
                // 设置垂直对齐方式
                wCellFormatC.setVerticalAlignment(VerticalAlignment.CENTRE);
                //        新建单元格      列号  行号
                Label gender = new Label(0, 0, "性别", wCellFormatC);
                Label garde = new Label(1, 0, "成绩", wCellFormatC);
                Label password = new Label(2, 0, "密码", wCellFormatC);
                Label username = new Label(3, 0, "用户名", wCellFormatC);
                Label isDel = new Label(4, 0, "是否被删除", wCellFormatC);
                Label birthday = new Label(5, 0, "生日", wCellFormatC);
                // 添加单元格
                sheet1.addCell(gender);
                sheet1.addCell(garde);
                sheet1.addCell(password);
                sheet1.addCell(username);
                sheet1.addCell(isDel);
                sheet1.addCell(birthday);
    
                int size = data.size();
                for (int i = 0; i <size ; i++) {
                    Student student = data.get(i);
                    Boolean gender1 = student.getGender();
                    int grade1 = student.getGrade();
                    String password1 = student.getPassword();
                    String username1 = student.getUsername();
                    Boolean isDel1 = student.getIsDel();
                    Date birthday1 = student.getBirthday();
                    if(gender1!=null){
                        Label label = new Label(0, i + 1, gender1 ? "男" : "女");
                        sheet1.addCell(label);
                    }
                    Label label1 = new Label(1, i + 1, Convert.toStr(grade1));
                    Label label2 = new Label(2, i + 1, password1);
                    Label label3 = new Label(3, i + 1, username1);
                    if(isDel1!=null){
                        Label label = new Label(4, i + 1, isDel1 ? "是" : "否");
                        sheet1.addCell(label);
                    }
                    if(birthday1!=null){
                        DateTime dateTime = new DateTime(5,i+1,birthday1);
                        sheet1.addCell(dateTime);
                    }
                    sheet1.addCell(label1);
                    sheet1.addCell(label2);
                    sheet1.addCell(label3);
                }
                // 将文件写出
                workbook.write();
                // 关闭
                workbook.close();
            } catch (Exception e){
                e.printStackTrace();
            }
        }
    }

导出结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1cHJlemhlbmc_size_16_color_FFFFFF_t_70][]

### jxl导入Excel数据请参考：---》[https://blog.csdn.net/suprezheng/article/details/95907774][https_blog.csdn.net_suprezheng_article_details_95907774] ###

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1cHJlemhlbmc_size_16_color_FFFFFF_t_70]: /images/20211209/cba3c772265e4657906d21efb0dd0132.png
[https_blog.csdn.net_suprezheng_article_details_95907774]: https://blog.csdn.net/suprezheng/article/details/95907774