EasyExcel实现数据的导出

简单的测试demo,针对场景：只对一个sheet的导出操作

1,首先引入依赖

<dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>easyexcel</artifactId>
          <version>2.1.4</version>
      </dependency>

2,假设只对名称字段导出，对应的pojo类

@Data
public class User {
          
   
    private String Name;
    }

3，我们要创建一个对应pojo导出数据的的excel类,只是不建议直接在pojo上直接操作。

注意下面注解的使用

@Data
@Builder
@HeadRowHeight(value = 20)//设置表头行高
@ColumnWidth(value = 15)//设置表头行宽
public class UserExcel {
          
   

    @ColumnWidth(value = 15)//单独设置表头行宽
     @ExcelProperty(value = "患者姓名", index = 0)//”0“表示excel中的第一列
    private String Name;
   
    }

4，创建Excel导出工具类

import java.io.OutputStream;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;

public class ExcelUtils {
          
   

    /**
     * 导出
     * 
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName,
        String sheetName, Class clazz) throws Exception {
          
   
        // 表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
            .registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);

    }

    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
          
   
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + "-" + LocalDate.now() + ".xlsx");
        return response.getOutputStream();
    }
}

5，controller层

@GetMapping("/export")
    public void Export(HttpServletResponse response) {
          
   
        // 获取数据
        List<UserExcel> UserExcelList =userservice.getData();
        if (CollectionUtils.isEmpty(UserExcelList)) {
          
   
            return;
        }
        try {
          
   
            ExcelUtils.writeExcel(response, data, "文件名",
                "sheet名", UserExcel.class);
        } catch (Exception e) {
          
   
            e.printStackTrace();
        }
    }

6.service层

public List<UserExcel> getData (){
          
   
	List<User> userList=getUserData();
	return getExcelData(userList);

}
    private List<UserExcel>
        getExcelData(List<User> userList) {
          
   
        List<UserExcel> userExcelList = new ArrayList<>();
        for (User user : userList) {
          
   
            UserExcel userExcel = UserExcel.builder()
            .name(user.getName())
               .build();
            userExcelList.add(userExcel);
        }
        return userExcelList;
    }