【无标题】easyexcel创建教程

原创已于 2025-09-28 09:20:54 修改 · 331 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #蓝桥杯 #开发语言

于 2022-05-08 15:44:15 首次发布

这篇博客展示了如何使用阿里巴巴的EasyExcel库在Java中创建并写入Excel文件。通过创建一个UserData类来定义Excel的列名，并在main方法中生成模拟数据，然后调用EasyExcel的write方法将数据写入到指定的Excel文件中。

package dmybatistest.dmybatistest.model.domain;

import java.util.ArrayList;
import java.util.List;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;

import lombok.Data;

@Data
public class UserData {
	@ExcelProperty("用户id")
    private int uid;

    @ExcelProperty("用户名称")
    private String username;
    public static void main(String[] args) {
        // 要写到excel数据的list集合
        List<UserData> list = new ArrayList<>();
        for (int i=0; i<10; i++) {
            UserData data = new UserData();
            data.setUid(i);
            data.setUsername("用户名称"+i);
            list.add(data);
        }
        // 设置excel文件路径和文件名称
        String fileName = "C:\\easyexcel.xlsx";
        // 调用方法实现写操作
        EasyExcel.write(fileName, UserData.class).sheet("用户信息")
                .doWrite(list);
    }
}

<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>4.0.3</version>
			<exclusions>
				<exclusion>
					<groupId>org.apache.poi</groupId>
					<artifactId>poi-ooxml-schemas</artifactId>
				</exclusion>
			</exclusions>
		</dependency>

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@RestController
public class ExcelController {

    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("用户信息", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

        // 创建ExcelWriter
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), UserInfo.class).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("用户数据").build();
            // 模拟10万条数据，分批次写入（每批2000条）
            for (int i = 0; i < 100000; i += 2000) {
                List<UserInfo> batchData = generateBatchData(i, Math.min(i + 2000, 100000));
                excelWriter.write(batchData, writeSheet);
            }
        }
    }

    private List<UserInfo> generateBatchData(int start, int end) {
        List<UserInfo> list = new ArrayList<>();
        for (int i = start; i < end; i++) {
            UserInfo userInfo = new UserInfo();
            userInfo.setName("用户" + i);
            userInfo.setAge(20 + (i % 10));
            userInfo.setCreateTime(new Date());
            list.add(userInfo);
        }
        return list;
    }
}