Springboot导出Excel表格

下载Excel一个简单的小demo

1、创建实体类

package com.example.demo2.entity;

public class Bgm {
    private String id;
    private String author;
    private String name;
    private String path;

    
    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPath() {
        return path;
    }

    public void setPath(String path) {
        this.path = path;
    }

    @Override
    public String toString() {
        return "Bgm{" +
                "id='" + id + '\'' +
                ", author='" + author + '\'' +
                ", name='" + name + '\'' +
                ", path='" + path + '\'' +
                '}';
    }
}

2、创建dao接口

package com.example.demo2.dao;

import com.example.demo2.entity.Bgm;

import java.util.List;

public interface BgmDao {

    List<Bgm> queryBgm();

}

3、创建service接口

package com.example.demo2.service;

import com.example.demo2.entity.Bgm;

import java.util.List;

public interface BgmService {

    List<Bgm> getBgmList();
}

4、实现接口

package com.example.demo2.service.impl;

import com.example.demo2.dao.BgmDao;
import com.example.demo2.entity.Bgm;
import com.example.demo2.service.BgmService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;


@Service
public class BgmServiceImpl implements BgmService {

    @Autowired
    private BgmDao bgmDao;

    @Override
    public List<Bgm> getBgmList() {
        return bgmDao.queryBgm();
    }
}

5、编写controller

package com.example.demo2.web;

import com.example.demo2.entity.Bgm;
import com.example.demo2.service.BgmService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;


@RestController
@RequestMapping("/BgmExcelDownloads")
public class BgmExcelDownloads {

    @Autowired
    private BgmService bgmService;

    @RequestMapping(value = "/downloadsExcelDown", method = RequestMethod.GET)
    public void downloadsExcelDown(HttpServletResponse response) throws IOException {
        List<Bgm> bgmExcelDownloads = bgmService.getBgmList();
        System.out.printf("------------" + bgmExcelDownloads.toString());
        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet sheet = wb.createSheet("获取excel测试表格");

        HSSFRow row = null;

        row = sheet.createRow(0);//创建第一个单元格
        row.setHeight((short) (26.25 * 20));
        row.createCell(0).setCellValue("用户信息列表");//为第一行单元格设值

        /*为标题设计空间
         * firstRow从第1行开始
         * lastRow从第0行结束
         *
         *从第1个单元格开始
         * 从第3个单元格结束
         */
        CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 2);
        sheet.addMergedRegion(rowRegion);

      /*CellRangeAddress columnRegion = new CellRangeAddress(1,4,0,0);
      sheet.addMergedRegion(columnRegion);*/

        row = sheet.createRow(1);
        row.setHeight((short) (22.50 * 20));//设置行高
        row.createCell(0).setCellValue("Id");//为第一个单元格设值
        row.createCell(1).setCellValue("作者");//为第二个单元格设值
        row.createCell(2).setCellValue("歌名");//为第三个单元格设值
        row.createCell(2).setCellValue("路径");//为第四个单元格设值
        //遍历所获取的数据
        for (int i = 0; i < bgmExcelDownloads.size(); i++) {
            row = sheet.createRow(i + 2);
            Bgm bgm = bgmExcelDownloads.get(i);
            row.createCell(0).setCellValue(bgm.getId());
            row.createCell(1).setCellValue(bgm.getAuthor());
            row.createCell(2).setCellValue(bgm.getName());
            row.createCell(2).setCellValue(bgm.getPath());
        }
        sheet.setDefaultRowHeight((short) (16.5 * 20));
        //列宽自适应
        for (int i = 0; i <= 13; i++) {
            sheet.autoSizeColumn(i);
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        OutputStream os = response.getOutputStream();
        response.setHeader("Content-disposition", "attachment;filename=BGM.xls");//默认Excel名称
        wb.write(os);
        os.flush();
        os.close();
    }
}

这时候在浏览器访问 http://localhost:8080/demo/BgmExcelDownloads/downloadsExcelDown 就可以下载

技术有限，望大神纠错指正！！！