SpringBoot下载excel表格

SpringBoot下载excel表格

git地址：https://gitee.com/benming-walnut/download-excel.git

1.目录结构

2.相关依赖

    <parent>
        <artifactId>spring-boot-starter-parent</artifactId>
        <groupId>org.springframework.boot</groupId>
        <version>2.3.8.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--处理excel、word-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
    </dependencies>

3.启动类

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DownloadExcelApplication {
    public static void main(String[] args) {
        SpringApplication.run(DownloadExcelApplication.class, args);
    }
}

4.工具类

package com.qq.utils;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 黔程似景
 * @description 用于对象转map
 * @date 2021/11/18 20:59
 **/
public class BeanUtils {

    private BeanUtils() {
    }

    /**
     * 对象转map集合
     *
     * @param o 需要转换成hashMap的集合
     * @return
     * @throws Exception
     */
    public static HashMap<String, Object> objectToMap(Object o) throws Exception {
        HashMap<String, Object> hashMap = new HashMap<>();
        Class cls = o.getClass();
        Field[] fields = cls.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field f = fields[i];
            f.setAccessible(true);
            hashMap.put(f.getName(), f.get(o));
        }
        return hashMap;
    }

    /**
     * 对象集合转map版本的集合
     *
     * @param list
     * @return
     * @throws Exception
     */
    public static <E> List<Map<String, Object>> getMapList(List<E> list) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();
        if (list == null) {
            return result;
        }

        for (Object o : list) {
            HashMap<String, Object> hashMap = objectToMap(o);
            result.add(hashMap);
        }

        return result;
    }

}

5.实体对象

/**
 * @author 黔程似景
 * @description TODO
 * @date 2021/11/18 21:04
 **/
public class User {

    private String id;
    private String name;

    public User(String id, String name) {
        this.id = id;
        this.name = name;
    }

}

6.Controller

import com.qq.service.DownloadExcelService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

/**
 * @author 黔程似景
 * @description TODO
 * @date 2021/11/18 20:56
 **/
@RestController
public class DownloadExcelController {

    @Resource
    private DownloadExcelService downloadExcelService;

    @GetMapping("/download")
    public void download(HttpServletResponse response){
        downloadExcelService.download(response);
    }

}

7.Service

import com.qq.entity.User;
import com.qq.service.DownloadExcelService;
import com.qq.utils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author 黔程似景
 * @description TODO
 * @date 2021/11/18 21:02
 **/
@Service
public class DownloadExcelServiceImpl implements DownloadExcelService {

    /**
     * 下载excel表格
     * @param response
     */
    @Override
    public void download(HttpServletResponse response) {
        try (OutputStream outputStream = response.getOutputStream()) {
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("UTF-8");
            String s = "导出资源" + ".xls";
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(s, "UTF-8"));

            //获取资源，仅支持对象，不支持hashMap
            List<User> list = new ArrayList<>();
            User user = new User("1", "法外狂徒");
            for (int i = 0; i < 10; i++) {
                list.add(user);
            }

            //excel表，表头和表数据对应关系
            LinkedHashMap<String, String> map = getColumn();

            //将对象转换成map
            List<Map<String, Object>> mapList = BeanUtils.getMapList(list);

            //进行打印导出
            HSSFWorkbook write = getHSSFWorkbook(map, mapList);
            write.write(outputStream);
        } catch (Exception e) {
            System.out.println("--------------------------下载异常--------------------------");
        }
    }

    /**
     * excel表，表头和表数据对应关系
     *
     * @return 表头与表数据的对应关系
     */
    private LinkedHashMap<String, String> getColumn() {
        LinkedHashMap<String, String> map = new LinkedHashMap<>();

        // map.put(表头, 表数据字段)
        map.put("ID", "id");
        map.put("用户名", "name");

        return map;
    }

    private HSSFWorkbook getHSSFWorkbook(LinkedHashMap<String, String> map, List<Map<String, Object>> list) {

        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一个sheet，括号里可以输入sheet名称，默认为sheet0
        Sheet sheet = workbook.createSheet();
        Row row0 = sheet.createRow(0);
        AtomicInteger columnIndex = new AtomicInteger();

        //添加首列为序号列
        row0.createCell(columnIndex.get()).setCellValue("序号");
        map.forEach((k, v) -> {
            row0.createCell(columnIndex.incrementAndGet()).setCellValue(k);
        });

        //判空
        if (list == null) {
            return workbook;
        }

        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> objectMap = list.get(i);
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < columnIndex.get() + 1; j++) {
                row.createCell(j);
            }
            columnIndex.set(0);

            //进行导入表数据
            //表序列号
            row.getCell(columnIndex.get()).setCellValue(i + 1);

            //表数据
            map.forEach((k, v) -> {
                Object o = objectMap.get(v);
                if (o != null) {
                    row.getCell(columnIndex.incrementAndGet()).setCellValue(o.toString());
                } else {
                    row.getCell(columnIndex.incrementAndGet()).setCellValue("");
                }
            });

        }

        return workbook;
    }

}

interface DownloadExcelService {

    /**
     * 下载excel表格
     * @param response
     */
    void download(HttpServletResponse response);
}

8.执行启动类

下载地址：http://localhost:8080/download