jxl导出Excel文件案例

导入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();
        }
    }
}

导出结果：

 

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