Springboot如何下载Excel

2023-06-20 13:06:43 248人浏览独家记忆

摘要

小编给大家分享一下SpringBoot如何下载excel，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！?1.EasyExcel--浏览器下载1.Maven环境网络

小编给大家分享一下SpringBoot如何下载excel，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

?1.EasyExcel--浏览器下载

1.Maven环境

网络上有很多maven的easyexcel版本，还是推荐alibaba的easyexcel，操作简单，代码不冗余

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

完整代码实现

控制层：设置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;@RestControllerpublic class UserController {        @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.write(outputStream, User.class).sheet("测试数据").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;    }}

实体类：给User设置对应的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;@Data@Builderpublic 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.实现效果

Springboot如何下载Excel

?2.EasyExcel--本地下载

1.完整代码实现

maven和上面一样，只是文件输出流设置一下即可

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;@RestControllerpublic class UserController {        @GetMapping("/testLocalExcel")    public void testLocalExcel(){        // 文件输出位置        OutputStream out = null;        try {            out = new FileOutputStream("C:\\Users\\EDY\\Desktop\\empirefree.xlsx");            EasyExcel.write(out, User.class).sheet("测试数据").doWrite(getAllUser());        } catch (FileNotFoundException e) {            e.printStackTrace();        }finally {            try {                // 关闭流                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.实现效果

Springboot如何下载Excel

?3.Poi--浏览器实现下载

1.Maven环境

<!-- excel导出工具 --><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.代码实现

控制层

       @GetMapping("/export")    public String exportExcel(HttpServletResponse response) {        System.out.println("成功到达到处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 "导出成功！";                } else {                    return "导出失败！";                }            }            return "文件格式有误！";        }    }

serviceimpl层

    @Override    public Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {        log.info("导出数据开始。。。。。。");        //查询数据并赋值给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 = {"序列", "用户名", "密码", "状态"};        ExcelData data = new ExcelData();        data.setHead(head);        data.setData(list);        data.setFileName(fileName);        //实现导出        try {            ExcelUtil.exportExcel(response, data);            log.info("导出数据结束。。。。。。");            return true;        } catch (Exception e) {            log.info("导出数据失败。。。。。。");            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.*;@Slf4jpublic class ExcelUtil {        public static void exportExcel(HttpServletResponse response, ExcelData data) {        log.info("导出解析开始，fileName:{}",data.getFileName());        try {            //实例化HSSFWorkbook            HSSFWorkbook workbook = new HSSFWorkbook();            //创建一个Excel表单，参数为sheet的名字            HSSFSheet sheet = workbook.createSheet("sheet");            //设置表头            setTitle(workbook, sheet, data.getHead());            //设置单元格并赋值            setData(sheet, data.getData());            //设置浏览器下载            setBrowser(response, workbook, data.getFileName());            log.info("导出解析成功!");        } catch (Exception e) {            log.info("导出解析失败!");            e.printStackTrace();        }    }        private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {        try {            HSSFRow row = sheet.createRow(0);            //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度            for (int i = 0; i <= str.length; i++) {                sheet.setColumnWidth(i, 15 * 256);            }            //设置为居中加粗,格式化时间格式            HSSFCellStyle style = workbook.createCellStyle();            HSSFFont font = workbook.createFont();            font.setBold(true);            style.setFont(font);            style.setDataFORMat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));            //创建表头名称            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("导出时设置表头失败！");            e.printStackTrace();        }    }        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();        }    }        private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {        try {            //清空response            response.reset();            //设置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("设置浏览器下载成功！");        } catch (Exception e) {            log.info("设置浏览器下载失败！");            e.printStackTrace();        }    }        public static List<Object[]> importExcel(String fileName) {        log.info("导入解析开始，fileName:{}",fileName);        try {            List<Object[]> list = new ArrayList<>();            InputStream inputStream = new FileInputStream(fileName);            Workbook workbook = WorkbookFactory.create(inputStream);            Sheet sheet = workbook.getSheetAt(0);            //获取sheet的行数            int rows = sheet.getPhysicalNumberOfRows();            for (int i = 0; i < rows; i++) {                //过滤表头行                if (i == 0) {                    continue;                }                //获取当前行的数据                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("导入文件解析成功！");            return list;        }catch (Exception e){            log.info("导入文件解析失败！");            e.printStackTrace();        }        return null;    }    //测试导入    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();        }    }}