SpringBoot+Vue实现导入导出

pom依赖：

<!-- hutool -->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.20</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

<!-- 导出数据的依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

utils工具类：

package com.example.boot.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;

import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.ServletOutputStream;
import java.io.*;

/**
 * @ClassName EasyExcelUtil
 * @Description TODO
 * @Author gwq
 * @Date 2023/3/8 18:36
 **/
public class ExportEncodeUtil {

/**
* @Author gwq
* @Description 导出
* @Date 2023/4/12 20:52
* @Return void
* @param request
* @param response
* @param sheetName
* @param workbook
*/
public static void HeaderCode(HttpServletRequest request, HttpServletResponse response, String sheetName, HSSFWorkbook workbook) throws IOException {
        // 针对IE或者以IE为内核的浏览器：
        String userAgent = request.getHeader("User-Agent").toLowerCase();
        String fileName = sheetName + ".xls";
        if (userAgent.contains("msie") || userAgent.contains("trident")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
        } else {
                // 非IE浏览器的处理：
                fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
        }
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("success", "true");
        OutputStream os = response.getOutputStream();
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);//默认Excel名称

        workbook.write(os);
        os.flush();
        os.close();
}

/**
 * 自适应宽度(中文支持)
 *  @param sheet
 * @param size
 */
public static void setSizeColumn(HSSFSheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
                int columnWidth = sheet.getColumnWidth(columnNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                        HSSFRow currentRow;
                        //当前行未被使用过
                        if (sheet.getRow(rowNum) == null) {
                                currentRow = sheet.createRow(rowNum);
                        } else {
                                currentRow = sheet.getRow(rowNum);
                        }

                        if (currentRow.getCell(columnNum) != null) {
                                HSSFCell currentCell = currentRow.getCell(columnNum);
                                if (currentCell.getCellType() == CellType.STRING) {
                                        int length = currentCell.getStringCellValue().getBytes().length;
                                        if (columnWidth < length) {
                                                columnWidth = length;
                                        }
                                }
                        }
                }
                sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
}
/**
 * 写出excel文件到浏览器(下载文件)
 * @param wb excel对象
 * @param name 文件名，带后缀
 * 相比上个方法，该方法通用性更强
 */
public static void export(Workbook wb, String name, HttpServletResponse res){

        BufferedInputStream bis = null;
        try (ServletOutputStream out = res.getOutputStream();
             BufferedOutputStream bos = new BufferedOutputStream(out)){
                ByteArrayOutputStream os = new ByteArrayOutputStream();
                wb.write(os);
                byte[] content = os.toByteArray();

                InputStream is = new ByteArrayInputStream(content);
                // 设置response参数，可以打开下载页面
                res.reset();
                res.setContentType("application/vnd.ms-excel;charset=utf-8");
                res.setHeader("Content-Disposition",
                        "attachment;filename=" + new String(name.getBytes(), "iso-8859-1"));
                bis = new BufferedInputStream(is);
                byte[] buff = new byte[2048];
                int bytesRead;
                // Simple read/write loop.
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                        bos.write(buff, 0, bytesRead);
                }

        } catch (Exception e) {
                //log.info(e.getMessage());
        } finally {
                try {
                        if (bis != null) {
                                bis.close();
                        }
                }catch (IOException e){
                        //log.info(e.getMessage());
                }
        }
}

}

导出：

后端：

/**
 * 导出接口
 */
@AuthAccess
@GetMapping("/export")
public String export(HttpServletRequest request, HttpServletResponse response) throws IOException {
    //全查表信息
    List<Storein> list = storeinMapper.selectList(null);
    HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件
    HSSFSheet sheet = workbook.createSheet("入库管理信息");//Excel文件名字
    String sheetName = sheet.getSheetName();//创建目录链接Excel表格
    workbook.setSheetName(0, sheetName);

    sheet.setDefaultRowHeight((short) (20 * 20));//设置高度
    sheet.setColumnWidth(1, 20 * 256); // 100 * 256 表示100个字符的宽度，单位是 1/256 个字符的宽度
    sheet.setColumnWidth(3, 20 * 256); // 100 * 256 表示100个字符的宽度，单位是 1/256 个字符的宽度
    sheet.setColumnWidth(4, 20 * 256); // 100 * 256 表示100个字符的宽度，单位是 1/256 个字符的宽度
    sheet.setColumnWidth(5, 20 * 256); // 100 * 256 表示100个字符的宽度，单位是 1/256 个字符的宽度
    CellStyle style = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 12);
    style.setFont(font);

    int rowNum = 1;
    //表头数据
    String[] headers = {"仓库名称", "仓库编号", "物品名称", "物品编号", "库存量",};
    //headers表示excel表中第一行的表头
    HSSFRow rew = sheet.createRow(0);
    //在excel表中添加表头
    for (int i = 0; i < headers.length; i++) {
        HSSFCell cell = rew.createCell(i);
        HSSFRichTextString text = new HSSFRichTextString(headers[i]);
        cell.setCellValue(text);
    }
    //在表中存放查询到的数据放入对应的列
    for (Storein storein : list) {

        HSSFRow row1 = sheet.createRow(rowNum);
        row1.createCell(0).setCellValue(storein.getName());
        row1.createCell(1).setCellValue(storein.getCode());
        row1.createCell(2).setCellValue(storein.getGoods());
        row1.createCell(3).setCellValue(storein.getGoodscode());
        row1.createCell(4).setCellValue(storein.getNum());
        rowNum++;
    }
    ExportEncodeUtil.setSizeColumn(sheet,list.size());
    ExportEncodeUtil.HeaderCode(request,response,sheetName,workbook);
    return "导出成功";
}

前端：

//导出按钮

<el-button type="danger" style="margin-left: 10px;" @click="exp">导出<i class="el-icon-top"></i></el-button>

//导出方法

exp() {

      window.open("http://localhost:9090/storein/export")

    }, 

导入：

后端：

/**
 * excel 导入
 * @param file
 * * @throws
 * Exception
 * */
@PostMapping("/import")
public Result imp(MultipartFile file) throws Exception {
    InputStream inputStream = file.getInputStream();
    ExcelReader reader = ExcelUtil.getReader(inputStream);
    // 通过 javabean的方式读取Excel内的对象，但是要求表头必须是英文，跟javabean的属性要对应起来
    List<Storein> list = reader.readAll(Storein.class);
    list.forEach(o -> storeinMapper.insert(o));
    return Result.success();
}

前端：

//导入按钮

<el-upload action="http://localhost:9090/storein/import" :show-file-list="false" accept="xlsx" :on-success="handleExcelImportSuccess"  style="display: inline-block; margin-left: 10px;">

        <el-button type="danger" class="ml-5">导入<i class="el-icon-bottom"></i></el-button>

        </el-upload>

//导入方法：

handleExcelImportSuccess() {

      this.$message.success("导入成功")

      this.load()

    }