詳解Springboot下載Excel的三種方式

更新時(shí)間：2021年07月05日 08:30:15 作者：Empirefree

本文給大家分享Springboot下載Excel三種方式，主要分為瀏覽器下載和代碼本地下載實(shí)現(xiàn)的方式，針對(duì)每種實(shí)現(xiàn)方式給大家介紹的非常詳細(xì)，需要的朋友參考下吧

匯總一下瀏覽器下載和代碼本地下載實(shí)現(xiàn)的3種方式。

（其實(shí)一般都是在代碼生成excel，然后上傳到oss，然后傳鏈接給前臺(tái)，但是我好像沒有實(shí)現(xiàn)過直接點(diǎn)擊就能在瀏覽器下載的功能，所以這次一起匯總一下3種實(shí)現(xiàn)方式。）

🔥1.EasyExcel--瀏覽器下載

1.Maven環(huán)境

網(wǎng)絡(luò)上有很多maven的easyexcel版本，還是推薦alibaba的easyexcel，操作簡(jiǎn)單，代碼不冗余

  <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>

2.完整代碼實(shí)現(xiàn)

控制層：設(shè)置response格式然后直接下載即可

package com.empirefree.springboot.controller;

import com.alibaba.excel.EasyExcel;
import com.empirefree.springboot.pojo.User;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: springboot
 * @description:
 * @author: huyuqiao
 * @create: 2021/07/04 15:01
 */

@RestController
public class UserController {

    /**
     * Author: HuYuQiao
     * Description: 瀏覽器下載--excel
     */
    @GetMapping("/testRespExcel")
    public void testRespExcel(HttpServletResponse response){
        response.addHeader("Content-Disposition", "attachment;filename=" + "huyuqiao.xlsx");
        response.setContentType("application/vnd.ms-excel;charset=gb2312");
        try {
//            從HttpServletResponse中獲取OutputStream輸出流
            ServletOutputStream outputStream = response.getOutputStream();
            /*
             * EasyExcel 有多個(gè)不同的read方法，適用于多種需求
             * 這里調(diào)用EasyExcel中通過OutputStream流方式輸出Excel的write方法
             * 它會(huì)返回一個(gè)ExcelWriterBuilder類型的返回值
             * ExcelWriterBuilde中有一個(gè)doWrite方法，會(huì)輸出數(shù)據(jù)到設(shè)置的Sheet中
             */
            EasyExcel.write(outputStream, User.class).sheet("測(cè)試數(shù)據(jù)").doWrite(getAllUser());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public List<User> getAllUser(){
        List<User> userList = new ArrayList<>();
        for (int i=0;i<100;i++){
            User user = User.builder().name("胡宇喬"+ i).password("huyuqiao").age(i).build();
            userList.add(user);
        }
        return userList;
    }
}

實(shí)體類：給User設(shè)置對(duì)應(yīng)的excel屬性即可,value代表excel中名字，index代表第幾列

package com.empirefree.springboot.pojo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Builder;
import lombok.Data;

/**
 * @program: springboot
 * @description: user
 * @author: huyuqiao
 * @create: 2021/07/04 14:53
 */
@Data
@Builder
public class User  extends BaseRowModel{

    @ExcelProperty(value = "姓名",index = 0)
    private String name;

    @ExcelProperty(value = "密碼",index = 1)
    private String password;

    @ExcelProperty(value = "年齡",index = 2)
    private Integer age;
}

3.實(shí)現(xiàn)效果

🔥2.EasyExcel--本地下載

1.完整代碼實(shí)現(xiàn)

maven和上面一樣，只是文件輸出流設(shè)置一下即可

package com.empirefree.springboot.controller;

import com.alibaba.excel.EasyExcel;
import com.empirefree.springboot.pojo.User;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: springboot
 * @description:
 * @author: huyuqiao
 * @create: 2021/07/04 15:01
 */

@RestController
public class UserController {
    /**
     * Author: HuYuQiao
     * Description:本地生成--excel
     */
    @GetMapping("/testLocalExcel")
    public void testLocalExcel(){
        // 文件輸出位置
        OutputStream out = null;
        try {
            out = new FileOutputStream("C:\\Users\\EDY\\Desktop\\empirefree.xlsx");
            EasyExcel.write(out, User.class).sheet("測(cè)試數(shù)據(jù)").doWrite(getAllUser());
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }finally {
            try {
                // 關(guān)閉流
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }
    public List<User> getAllUser(){
        List<User> userList = new ArrayList<>();
        for (int i=0;i<100;i++){
            User user = User.builder().name("張三"+ i).password("1234").age(i).build();
            userList.add(user);
        }
        return userList;
    }
}

2.實(shí)現(xiàn)效果

🔥3.Poi--瀏覽器實(shí)現(xiàn)下載

1.Maven環(huán)境

<!-- excel導(dǎo)出工具 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>RELEASE</version>
		</dependency>

2.代碼實(shí)現(xiàn)

控制層

   /**
     * Author: HuYuQiao
     * Description: excle-export
     */
    @GetMapping("/export")
    public String exportExcel(HttpServletResponse response) {
        System.out.println("成功到達(dá)到處excel....");
        String fileName = "test.xls";
        if (fileName == null || "".equals(fileName)) {
            return "文件名不能為空！";
        } else {
            if (fileName.endsWith("xls")) {
                Boolean isOk = excelService.exportExcel(response, fileName, 1, 10);
                if (isOk) {
                    return "導(dǎo)出成功！";
                } else {
                    return "導(dǎo)出失??！";
                }
            }
            return "文件格式有誤！";
        }
    }

serviceimpl層

/**
     * Author: HuYuQiao
     * Description: excel-impl
     */
    @Override
    public Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {
        log.info("導(dǎo)出數(shù)據(jù)開始。。。。。。");
        //查詢數(shù)據(jù)并賦值給ExcelData
        List<User> userList = userMapper.find();
        System.out.println(userList.size() + "size");
        List<String[]> list = new ArrayList<String[]>();
        for (User user : userList) {
            String[] arrs = new String[4];
            arrs[0] = String.valueOf(user.getId());
            arrs[1] = user.getUsername();
            arrs[2] = user.getPassword();
            arrs[3] = String.valueOf(user.getEnable());
            list.add(arrs);
        }
        //表頭賦值
        String[] head = {"序列", "用戶名", "密碼", "狀態(tài)"};
        ExcelData data = new ExcelData();
        data.setHead(head);
        data.setData(list);
        data.setFileName(fileName);
        //實(shí)現(xiàn)導(dǎo)出
        try {
            ExcelUtil.exportExcel(response, data);
            log.info("導(dǎo)出數(shù)據(jù)結(jié)束。。。。。。");
            return true;
        } catch (Exception e) {
            log.info("導(dǎo)出數(shù)據(jù)失敗。。。。。。");
            return false;
        }
    }

工具類

package com.example.demo.utils;

import com.example.demo.entity.ExcelData;
import com.example.demo.entity.User;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import static org.apache.poi.ss.usermodel.CellType.*;

/**
 * Author: HuYuQiao
 * Description: excelUtil
 */
@Slf4j
public class ExcelUtil {

    /**
     * Author: HuYuQiao
     * Description: excelUtil-export
     */
    public static void exportExcel(HttpServletResponse response, ExcelData data) {
        log.info("導(dǎo)出解析開始，fileName:{}",data.getFileName());
        try {
            //實(shí)例化HSSFWorkbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            //創(chuàng)建一個(gè)Excel表單，參數(shù)為sheet的名字
            HSSFSheet sheet = workbook.createSheet("sheet");
            //設(shè)置表頭
            setTitle(workbook, sheet, data.getHead());
            //設(shè)置單元格并賦值
            setData(sheet, data.getData());
            //設(shè)置瀏覽器下載
            setBrowser(response, workbook, data.getFileName());
            log.info("導(dǎo)出解析成功!");
        } catch (Exception e) {
            log.info("導(dǎo)出解析失敗!");
            e.printStackTrace();
        }
    }

    /**
     * Author: HuYuQiao
     * Description: excelUtil-settitle
     */
    private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
        try {
            HSSFRow row = sheet.createRow(0);
            //設(shè)置列寬，setColumnWidth的第二個(gè)參數(shù)要乘以256，這個(gè)參數(shù)的單位是1/256個(gè)字符寬度
            for (int i = 0; i <= str.length; i++) {
                sheet.setColumnWidth(i, 15 * 256);
            }
            //設(shè)置為居中加粗,格式化時(shí)間格式
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            //創(chuàng)建表頭名稱
            HSSFCell cell;
            for (int j = 0; j < str.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(str[j]);
                cell.setCellStyle(style);
            }
        } catch (Exception e) {
            log.info("導(dǎo)出時(shí)設(shè)置表頭失敗！");
            e.printStackTrace();
        }
    }

    /**
     * Author: HuYuQiao
     * Description: excelUtil-setData
     */
    private static void setData(HSSFSheet sheet, List<String[]> data) {
        try{
            int rowNum = 1;
            for (int i = 0; i < data.size(); i++) {
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = 0; j < data.get(i).length; j++) {
                    row.createCell(j).setCellValue(data.get(i)[j]);
                }
                rowNum++;
            }
            log.info("表格賦值成功！");
        }catch (Exception e){
            log.info("表格賦值失敗！");
            e.printStackTrace();
        }
    }

    /**
     * Author: HuYuQiao
     * Description: excelUtil-setBrowser
     */
    private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
        try {
            //清空response
            response.reset();
            //設(shè)置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            //將excel寫入到輸出流中
            workbook.write(os);
            os.flush();
            os.close();
            log.info("設(shè)置瀏覽器下載成功！");
        } catch (Exception e) {
            log.info("設(shè)置瀏覽器下載失??！");
            e.printStackTrace();
        }

    }


    /**
     * Author: HuYuQiao
     * Description: excelUtil--importExcel
     */
    public static List<Object[]> importExcel(String fileName) {
        log.info("導(dǎo)入解析開始，fileName:{}",fileName);
        try {
            List<Object[]> list = new ArrayList<>();
            InputStream inputStream = new FileInputStream(fileName);
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            //獲取sheet的行數(shù)
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < rows; i++) {
                //過濾表頭行
                if (i == 0) {
                    continue;
                }
                //獲取當(dāng)前行的數(shù)據(jù)
                Row row = sheet.getRow(i);
                Object[] objects = new Object[row.getPhysicalNumberOfCells()];
                int index = 0;
                for (Cell cell : row) {
                    if (cell.getCellType().equals(NUMERIC)) {
                        objects[index] = (int) cell.getNumericCellValue();
                    }
                    if (cell.getCellType().equals(STRING)) {
                        objects[index] = cell.getStringCellValue();
                    }
                    if (cell.getCellType().equals(BOOLEAN)) {
                        objects[index] = cell.getBooleanCellValue();
                    }
                    if (cell.getCellType().equals(ERROR)) {
                        objects[index] = cell.getErrorCellValue();
                    }
                    index++;
                }
                list.add(objects);
            }
            log.info("導(dǎo)入文件解析成功！");
            return list;
        }catch (Exception e){
            log.info("導(dǎo)入文件解析失敗！");
            e.printStackTrace();
        }
        return null;
    }

    //測(cè)試導(dǎo)入
    public static void main(String[] args) {
        try {
            String fileName = "E:/test.xlsx";
            List<Object[]> list = importExcel(fileName);
            for (int i = 0; i < list.size(); i++) {
                User user = new User();
                user.setId((Integer) list.get(i)[0]);
                user.setUsername((String) list.get(i)[1]);
                user.setPassword((String) list.get(i)[2]);
                user.setEnable((Integer) list.get(i)[3]);
                System.out.println(user.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}