EasyExcel

[yilan0916]

public class WriteTest {

/**
 * 简单写方式一
 */
@Test
public void test01() {

    //创建一个Excel文档
    String fileName = "user1.xlsx";

    //根据User模板构建数据
    List<User> users = new ArrayList<>();
    User user1 = new User(1001, "李雷", "男", 1000.12, new Date());
    User user2 = new User(1002, "李雷", "男", 1000.12, new Date());
    User user3 = new User(1003, "李雷", "男", 1000.12, new Date());
    User user4 = new User(1004, "李雷", "男", 1000.12, new Date());
    users.add(user1);
    users.add(user2);
    users.add(user3);
    users.add(user4);

    //向Excel表格中写数据
    EasyExcel.write(fileName, User.class).sheet("用户信息").doWrite(users);
}

/**
 * 简单写方式二
 */
@Test
public void test02() {

    //创建一个Excel文档
    String fileName = "user2.xlsx";

    //根据User模板构建数据
    List<User> users = new ArrayList<>();
    User user1 = new User(1001, "李雷", "男", 1000.12, new Date());
    User user2 = new User(1002, "李雷", "男", 1000.12, new Date());
    User user3 = new User(1003, "李雷", "男", 1000.12, new Date());
    User user4 = new User(1004, "李雷", "男", 1000.12, new Date());
    users.add(user1);
    users.add(user2);
    users.add(user3);
    users.add(user4);

    //向Excel表格中写数据
    //创建ExcelWrite对象
    ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build();
    //创建Sheet对象
    WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
    excelWriter.write(users,writeSheet);
    //关闭
    excelWriter.finish();
}

/**
 * 排除掉模板中的某几个属性
 */
@Test
public void test03() {

    //创建一个Excel文档
    String fileName = "user3.xlsx";

    //根据User模板构建数据
    List<User> users = new ArrayList<>();
    User user1 = new User(1001, "李雷", "男", 1000.12, new Date());
    User user2 = new User(1002, "李雷", "男", 1000.12, new Date());
    User user3 = new User(1003, "李雷", "男", 1000.12, new Date());
    User user4 = new User(1004, "李雷", "男", 1000.12, new Date());
    users.add(user1);
    users.add(user2);
    users.add(user3);
    users.add(user4);

    //设置排除的属性
    Set<String> set = new HashSet<>();
    set.add("hireDate");
    set.add("salary");

    //写Excel
    EasyExcel.write(fileName, User.class).excludeColumnFiledNames(set).sheet("用户信息01").doWrite(users);
}

/**
 * 指定模板中的某几个属性写入到Excel中
 */
@Test
public void test04() {

    //创建一个Excel文档
    String fileName = "user4.xlsx";

    //根据User模板构建数据
    List<User> users = new ArrayList<>();
    User user1 = new User(1001, "李雷", "男", 1000.12, new Date());
    User user2 = new User(1002, "李雷", "男", 1000.12, new Date());
    User user3 = new User(1003, "李雷", "男", 1000.12, new Date());
    User user4 = new User(1004, "李雷", "男", 1000.12, new Date());
    users.add(user1);
    users.add(user2);
    users.add(user3);
    users.add(user4);

    //设置包含的属性
    Set<String> set = new HashSet<>();
    set.add("hireDate");
    set.add("salary");

    //写Excel
    EasyExcel.write(fileName, User.class).includeColumnFiledNames(set).sheet("用户信息01").doWrite(users);
}

/**
 * 复杂头
 */
@Test
public void test05() {

    //创建一个Excel文档
    String fileName = "user5.xlsx";

    //根据User模板构建数据
    List<ComplexHeadUser> users = new ArrayList<>();
    ComplexHeadUser user1 = new ComplexHeadUser(1001, "李雷", new Date());
    ComplexHeadUser user2 = new ComplexHeadUser(1001, "李雷", new Date());
    ComplexHeadUser user3 = new ComplexHeadUser(1001, "李雷", new Date());
    users.add(user1);
    users.add(user2);
    users.add(user3);

    //写Excel
    EasyExcel.write(fileName, ComplexHeadUser.class).sheet("用户信息05").doWrite(users);
}

/**
 * 输入重复写到同一个Excel表格的同一个sheet标签中
 */
@Test
public void test06() {

    //创建一个Excel文档
    String fileName = "user6.xlsx";

    //根据User模板构建数据
    List<User> users = new ArrayList<>();
    User user1 = new User(1001, "李雷", "男", 1000.12, new Date());
    User user2 = new User(1002, "李雷", "男", 1000.12, new Date());
    User user3 = new User(1003, "李雷", "男", 1000.12, new Date());
    User user4 = new User(1004, "李雷", "男", 1000.12, new Date());
    users.add(user1);
    users.add(user2);
    users.add(user3);
    users.add(user4);

    //重复写
    //创建ExcelWriter
    ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build();
    //创建WriteSheet
    WriteSheet writeSheet = EasyExcel.writerSheet("用户信息06").build();
    //重复写n次
    for (int i = 0; i < 5; i++) {
        excelWriter.write(users, writeSheet);
    }
    excelWriter.finish();
}

/**
 * 输入重复写到同一个Excel表格的不同的sheet标签中
 */
@Test
public void test07() {

    //创建一个Excel文档
    String fileName = "user7.xlsx";

    //根据User模板构建数据
    List<User> users = new ArrayList<>();
    User user1 = new User(1001, "李雷", "男", 1000.12, new Date());
    User user2 = new User(1002, "李雷", "男", 1000.12, new Date());
    User user3 = new User(1003, "李雷", "男", 1000.12, new Date());
    User user4 = new User(1004, "李雷", "男", 1000.12, new Date());
    users.add(user1);
    users.add(user2);
    users.add(user3);
    users.add(user4);

    //重复写
    //创建ExcelWriter
    ExcelWriter excelWriter = EasyExcel.write(fileName, User.class).build();

    //重复写n次
    for (int i = 0; i < 5; i++) {
        WriteSheet writeSheet = EasyExcel.writerSheet("用户列表" + i).build();
        excelWriter.write(users, writeSheet);
    }
    excelWriter.finish();
}

/**
 * 日期数字格式化
 */
@Test
public void test08() {

    //创建一个Excel文档
    String fileName = "user8.xlsx";

    //根据User模板构建数据
    List<User> users = new ArrayList<>();
    User user1 = new User(1001, "李雷", "男", 1000.1234, new Date());
    User user2 = new User(1002, "李雷", "男", 1000.1234, new Date());
    User user3 = new User(1003, "李雷", "男", 1000.1234, new Date());
    User user4 = new User(1004, "李雷", "男", 1000.1234, new Date());
    users.add(user1);
    users.add(user2);
    users.add(user3);
    users.add(user4);

    //向Excel表格中写数据
    EasyExcel.write(fileName, User.class).sheet("用户信息").doWrite(users);
}

/**
 * 将图片写入到Excel中
 */
@Test
public void test09() throws IOException {
    //创建一个Excel文件
    String fileName = "user9.xlsx";

    //构建数据
    List<ImageData> list = new ArrayList<>();
    ImageData imageData = new ImageData();
    imageData.setFile(new File("pic.png"));
    imageData.setInputStream(new FileInputStream(new File("pic.png")));
    imageData.setString("pic.png");
    imageData.setUrl(new URL("https://pics5.baidu.com/feed/e850352ac65c10384dcf6805a7540815b17e890b.jpeg?token=46fd105a95d8754d3daf93de7e8a7f6f"));

    byte[] b = new byte[(int) new File("pic.png").length()];
    InputStream in = new FileInputStream("pic.png");
    in.read(b, 0, (int) new File("pic.png").length());
    imageData.setByteArray(b);
    list.add(imageData);

    //写数据
    EasyExcel.write(fileName, ImageData.class).sheet("img").doWrite(list);
}

/**
 * 设置Excel宽高
 */
@Test
public void test10() throws IOException {
    //创建一个Excel文件
    String fileName = "user10.xlsx";

    //构建数据
    List<WidthAndHeightData> list = new ArrayList<>();
    WidthAndHeightData widthAndHeightData = new WidthAndHeightData();
    widthAndHeightData.setString("ABC");
    widthAndHeightData.setDoubleData(1001D);
    widthAndHeightData.setDate(new Date());
    list.add(widthAndHeightData);

    //写数据
    EasyExcel.write(fileName, WidthAndHeightData.class).sheet("img").doWrite(list);
}

/**
 * 设置Excel的样式
 */
@Test
public void test11() throws IOException {
    //创建一个Excel文件
    String fileName = "user11.xlsx";

    //构建数据
    List<DemoStyleData> list = new ArrayList<>();
    DemoStyleData styleData = new DemoStyleData("aaaa", new Date(), 100D);
    list.add(styleData);

    //写数据
    EasyExcel.write(fileName, DemoStyleData.class).sheet("img").doWrite(list);
}

[yilan0916]

package com.zk.dto;

import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.format.DateTimeFormat; import com.alibaba.excel.annotation.format.NumberFormat;

import java.util.Date;

/**

• @author zhengkang6
• @date 2022/4/16
• 创建User类模板，通过Use类模板向Excel中写数据

• @ExcelProperty： 这个注解的作用是描述设置Excel表格的头（起名字） */ public class User {

  @ExcelProperty(value = "用户编号", index = 0) private Integer userId; @ExcelProperty(value = "姓名", index = 1) private String userName; @ExcelProperty(value = "性别", index = 3) private String gender; @NumberFormat("#.##") @ExcelProperty(value = "工资", index = 2) private Double salary; @DateTimeFormat("yyyy年MM月dd日") @ExcelProperty(value = "入职时间", index = 4) private Date hireDate;

  public User() { }

  public User(Integer userId, String userName, String gender, Double salary, Date hireDate) { this.userId = userId; this.userName = userName; this.gender = gender; this.salary = salary; this.hireDate = hireDate; } public User(Integer userId) { this.userId = userId; }

  public Integer getUserId() { return userId; }

  public void setUserId(Integer userId) { this.userId = userId; }

  public String getUserName() { return userName; }

  public void setUserName(String userName) { this.userName = userName; }

  public String getGender() { return gender; }

  public void setGender(String gender) { this.gender = gender; }

  public Double getSalary() { return salary; }

  public void setSalary(Double salary) { this.salary = salary; }

  public Date getHireDate() { return hireDate; }

  public void setHireDate(Date hireDate) { this.hireDate = hireDate; }

  @Override public String toString() { return "User{" + "userId=" + userId + ", userName='" + userName + '\'' + ", gender='" + gender + '\'' + ", salary=" + salary + ", hireDate=" + hireDate + '}'; } }