java+vue实现excel文件从服务器下载

excel处理全套流程

前端代码

<el-upload
  class="upload-demo"
  ref="upload"
  action="http://127.0.0.1:8080/help/file"
  accept=".xls,.xlsx"
  :on-success="handleAvatarSuccess"
  name="excelFile"
  :auto-upload="false">
  <el-button slot="trigger" size="small" type="primary">选取文件</el-button>
  <el-button style="margin-left: 10px;" size="small" type="success" @click="submitUpload">上传到服务器</el-button>
  <div slot="tip" class="el-upload__tip">上传文件请命名为tid.xlsx或者utid.xlsx,excel文件第一列为要转换的数据</div>
</el-upload>

on-success表示文件上传处理成功之后调用的下载函数
submitUpload为点击上传的函数

submitUpload () {
      if (this.type == null) {
        this.$alert('请选择转换类型', '警告')
        return
      }
      this.$refs.upload.submit()
    },
    handleAvatarSuccess () {
      axios({
        url: 'http://127.0.0.1:8080/help/download',
        method: 'post',
        //下面这句必须有，表示返回的是文件
        responseType: 'blob',
        headers: {
          'X-Requested-With': 'XMLHttpRequest'
        },
        params: {
          type: this.type
        }
      }).then(res => {
      	//获取文件名，需要在后端进行配置
        let filename = res.headers['filename']
        let type = res.headers['content-type'].split(';')[0]
        let blob = new Blob([res.data], { type: type })
        const a = document.createElement('a')

        // 创建URL
        const blobUrl = window.URL.createObjectURL(blob)
        a.download = filename
        a.href = blobUrl
        document.body.appendChild(a)
        // 下载文件
        a.click()
        // 释放内存
        URL.revokeObjectURL(blobUrl)
        document.body.removeChild(a)
      })
    }

后端代码

//文件下载
    @PostMapping(value = "/download")
    @ResponseBody
    public void downloadFiles(@RequestParam(value = "type", required = true) String type,
                              HttpServletResponse response) {
        if (StringUtils.isBlank(type)) {
            //return error(CommonErrorCode.COM_INVALID_PARAMETER, "文档id有误");
            System.out.println("请先输入要处理的文档");
        }

        try {
            String fileName;
            if (type.equals("tidToUtid")) {
                fileName = "tidToUtid.xlsx";
            } else {
                fileName = "utidToTid.xlsx";
            }
            //服务器对应的文件的路径
            String file = "/Users/dxm/Desktop/" + fileName;
            // 得到这个excel表格对象
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
            OutputStream fos = null;
            fos = response.getOutputStream();
            response.setCharacterEncoding("UTF-8");
            // 设置contentType为excel格式
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //下面这三行必须设置前端才可以读取到文件名
            response.setHeader("Content-Disposition", fileName);
            response.setHeader("filename", fileName);
            response.setHeader("Access-Control-Expose-Headers", "filename");
            wb.write(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            logger.error("下载文档异常-找不到文件:{}", e);
            //return error(CommonErrorCode.COM_UNKNOWN_ERROR, "找不到文件");
        } catch (UnsupportedEncodingException e) {
            logger.error("下载文档异常-编码转换:{}", e);
            //return error(CommonErrorCode.COM_UNKNOWN_ERROR, "编码转换失败");
        } catch (IOException e) {
            logger.error("下载文档异常-io:{}", e);
            //return error(CommonErrorCode.COM_UNKNOWN_ERROR, "下载文档异常");
        }
    }

java生成excel文件

public class ExcelExporter {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExporter.class);
    private static final String[] tableNameTU = {"***", "***", "***"};
    private static final String[] tableNameUT = {"***", "***", "***"};

    private String[] headerNames;
    private Workbook workBook = new XSSFWorkbook();
    private Sheet sheet;

    /**
     * @param tableName 表头
     */
    public ExcelExporter(String tableName) {
        if (tableName.equals("tidToUtid")) {
            this.headerNames = tableNameTU;
        } else {
            this.headerNames = tableNameUT;
        }
        sheet = workBook.createSheet("sheet1");
        initHeader();
    }

    /**
     * 初始化表头信息
     */
    private void initHeader() {
        // 创建第一行
        Row row = sheet.createRow(0);
        Cell cell = null;
        // 创建表头
        for (int i = 0; i < headerNames.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headerNames[i]);
            setCellStyle(cell);
        }
    }

    /**
     * 设置单元格样式
     *
     * @param cell 单元格
     */
    public void setCellStyle(Cell cell) {
        // 设置样式
        CellStyle cellStyle = workBook.createCellStyle();

        // 设置字体
        Font font = workBook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 13);

        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

    public void createTableRows(List<String> data) {
        int size = data.size();
        for (int i = 0; i < size; i++) {
            String[] flag = data.get(i).split(",");
            Row row = sheet.createRow(i + 1);
            Cell cell;
            int tableLength = 3;
            for (int j = 0; j < tableLength; j++) {
                String value = flag[j];

                cell = row.createCell(j);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(value);
            }

        }
    }

    /**
     * 根据表头自动调整列宽度
     */
    public void autoAllSizeColumn() {
        if (sheet instanceof SXSSFSheet) {
            // 如果是SXSSFSheet，需要调用trackAllColumnsForAutoSizing方法一次
            SXSSFSheet tmpSheet = (SXSSFSheet) sheet;
            tmpSheet.trackAllColumnsForAutoSizing();
        }
        for (int i = 0, length = headerNames.length; i < length; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    /**
     * 将数据写出到excel中
     *
     * @param outputStream
     */
    public void exportExcel(OutputStream outputStream) {
        // 导出之前先自动设置列宽
        this.autoAllSizeColumn();
        try {
            workBook.write(outputStream);
        } catch (IOException e) {
            LOGGER.error(" exportExcel error", e);
        } finally {
            IOUtils.closeQuietly(outputStream);
        }
    }

    /**
     * 将数据写出到excel中
     *
     * @param outputFilePath
     */
    public void exportExcel(String outputFilePath) {
        // 导出之前先自动设置列宽
        this.autoAllSizeColumn();
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(outputFilePath);
            workBook.write(outputStream);
        } catch (IOException e) {
            LOGGER.error(" exportExcel error", e);
        } finally {
            IOUtils.closeQuietly(outputStream);
        }
    }

}

java读取excel文件

public class ExcelUtils {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

    public static List<String> excelToShopIdList(InputStream inputStream) {
        List<String> list = new ArrayList<>();
        Workbook workbook;
        try {
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //总行数
            int rowLength = sheet.getLastRowNum() + 1;
            //工作表的列
            Row row = sheet.getRow(0);
            //总列数
            //int colLength = row.getLastCellNum();
            //得到指定的单元格
            Cell cell;
            for (int i = 0; i < rowLength; i++) {
                row = sheet.getRow(i);
                cell = row.getCell(0);
                if (cell != null) {
                    String data = cell.getStringCellValue();
                    data = data.trim();
                    if (!data.equals("")) {
                        list.add(data);
                    }
                }
            }
        } catch (Exception e) {
            LOGGER.error("parse excel file error :", e);
        }
        return list;
    }
}