springboot 数据excel下载

最新推荐文章于 2024-08-15 00:07:53 发布

zzm_fengye

最新推荐文章于 2024-08-15 00:07:53 发布

阅读量4.9k

点赞数

文章标签： SpringBoot excel

本文链接：https://blog.csdn.net/zzm9876789/article/details/78800423

版权

需要的jar

compile("org.apache.poi:poi:3.17")
compile("org.apache.poi:poi-ooxml:3.17")

controller的代码

package com.controller.downloadDataIsExcel;

import com.controller.important.AdminController;
import com.pojo.user.Classmate;
import com.service.ClassmateService;
import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
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.text.SimpleDateFormat;
import java.util.*;

@RestController
@RequestMapping("downloadExcelClassmate")
public class DownloadExcelClassmate {
    private static final Logger logger = LoggerFactory.getLogger(AdminController.class);

    private ClassmateService classmateService;

    @RequestMapping(value = "downloadAllClassmateData", method = RequestMethod.GET)
    public void downloadAllClassmate(HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("信息表");
        createTitle(sheet);
        List<Classmate> classmateList = classmateService.getAllClassmate();

        //拼装blobName
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
        String fileName = "Classmate-" + new Date().getTime() + ".xls";
        //新增数据行，并且设置单元格数据
        int rowNum = 1;
        for (Classmate classmate : classmateList) {
            HSSFRow row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue(classmate.getUsername());
            row.createCell(1).setCellValue(classmate.getGender());
            row.createCell(2).setCellValue(classmate.getTel());
            row.createCell(3).setCellValue(classmate.getHomeAddress());
            row.createCell(4).setCellValue(dateFormat.format(classmate.getBirthTime()));
            row.createCell(5).setCellValue(classmate.getHobby());
            rowNum++;
        }

        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }


    /***
     * 创建表头
     * @param sheet
     */
    private void createTitle(HSSFSheet sheet)
    {
        HSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(0, 10*256);
        sheet.setColumnWidth(1, 10*256);
        sheet.setColumnWidth(2, 15*256);
        sheet.setColumnWidth(3, 80*256);
        sheet.setColumnWidth(4, 15*256);
        sheet.setColumnWidth(5, 80*256);


        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("姓名");

        cell = row.createCell(1);
        cell.setCellValue("性别");

        cell = row.createCell(2);
        cell.setCellValue("电话");

        cell = row.createCell(3);
        cell.setCellValue("家庭地址");

        cell = row.createCell(4);
        cell.setCellValue("出生年月");

        cell = row.createCell(5);
        cell.setCellValue("兴趣爱好");
    }


    @Autowired
    public void setClassmateService(ClassmateService classmateService) {
        this.classmateService = classmateService;
    }
}