您的位置: 首页  >  文章  >  Excel导入导出——学习笔记

Excel导入导出——学习笔记

分类: 文章 • 2022-10-07 14:37:12

前言

  利用Poi进行Excel导入导出，在SpringBoot框架下进行测试，前端小小的使用了下Vue，并使用了Bootstrap-fileinput作为前端上传组件作为参考，下载采用原生ajax下载形式，因为一直忘记前端下载怎么写还有后端怎么设置Request-Header，所以提供一下一整个前后端的思路，以后方便进行查阅，只是个人学习用，所以仅供参考。

引入POI依赖

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

ExcelUtil工具类

  excel的工具类，这里基本上把导入导出的功能大致做了一遍，但还有值得改进的地方，不过想说的是，基本能用！

package com.podago.demo.utils;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

/**
 * Excel工具类 - 提供导入导出功能
 * @author chenxiaotao
 */
public class ExcelUtil {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    private static final String XLS = "xls";

    private static final String XLSX = "xlsx";

    /**
     * 读取Excel文件数据，并转换为二维数组形式输出
     * @param file 文件流
     * @return Excel数据
     */
    public static List<List<Object>> readExcel(MultipartFile file) {
        Workbook workbook = null;
        String fileName = file.getOriginalFilename();
        InputStream in = null;
        try {
            in = file.getInputStream();
            if (fileName != null && fileName.endsWith(XLS)) {
                // 2003
                workbook = readExcel2003(in);
            } else if (fileName != null && fileName.endsWith(XLSX)) {
                // 2007
                workbook = readExcel2007(in);
            }
        } catch (IOException e) {
            logger.error("excel输入流读取错误,返回空列表", e);
            return Collections.emptyList();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    logger.error("excel输入流关闭错误", e);
                }
            }
        }
        if (workbook == null) {
            return Collections.emptyList();
        }
        return readExcel(workbook);
    }


    /**
     * 读取2007+版本的excel文件，即后缀为xlsx
     */
    private static Workbook readExcel2007(InputStream in) {
        Workbook workbook = null;
        try {
            workbook = new XSSFWorkbook(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 读取2003版本的excel文件，即后缀为xls
     */
    private static Workbook readExcel2003(InputStream in) {
        Workbook workbook = null;
        try {
            workbook = new HSSFWorkbook(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }


    /**
     * 根据Workbook，读取Excel数据
     * @param workbook excel工作簿
     * @return excel中数据
     */
    private static List<List<Object>> readExcel(Workbook workbook) {
        List<List<Object>> res = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            int lastCellNum = row.getLastCellNum();
            List<Object> rdata = new ArrayList<>(lastCellNum);
            for (int j = 0 ; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                rdata.add(getCell(cell));
            }
            res.add(rdata);
        }
        return res;
    }

    /**
     * 获取单元格数据
     * @param cell 单元格对象
     * @return 单元格中数据
     */
    private static Object getCell(Cell cell) {
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                return cell.getNumericCellValue();
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return StringUtils.EMPTY;
            case ERROR:
                return StringUtils.EMPTY;
            case _NONE:
                return StringUtils.EMPTY;
            default:
                return null;
        }
    }

    /**
     * 输出Excel数据流，并最终关闭流
     * @param dataLists 传入数据
     * @param out 输出流
     */
    public static void createExcel(List<List<Object>> dataLists, OutputStream out) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFCellStyle titleCellStyle = workbook.createCellStyle();

        //设置单元标题样式
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleCellStyle.setFillForegroundColor(HSSFColorPredefined.SKY_BLUE.getIndex());
        titleCellStyle.setWrapText(true);
        //设置单元标题字体
        HSSFFont titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 13);
        titleCellStyle.setFont(titleFont);

        //设置表格内容单元样式
        HSSFCellStyle valueCellStyle = workbook.createCellStyle();
        valueCellStyle.setAlignment(HorizontalAlignment.CENTER);
        valueCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFFont cellFont = workbook.createFont();
        cellFont.setFontHeightInPoints((short) 12);
        valueCellStyle.setFont(cellFont);

        // 二维形式
        for (int i = 0; i < dataLists.size(); i++) {
            HSSFRow row = sheet.createRow(i);
            List<Object> objects = dataLists.get(i);

            for (int j = 0; j < objects.size(); j++) {
                HSSFCell valueCell = row.createCell(j);
                valueCell.setCellStyle(valueCellStyle);
                valueCell.setCellValue(objects.get(j).toString());
            }
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        //导出成功!
    }
}

分解读取Excel文件的思路

  1. 获取MultiPartFile文件，因为是用的SpringBoot，所以直接传入这个就可以获取到文件名以及文件流，如果觉得和Spring有耦合的话可以将方法写成 readExcel(String fileName, InputStream in)
  2. 判断文件类型，简单判断文件后缀名，然后根据文件类型（因为Excel2003和Excel2007+的读取方式不同），调用不同的读取方法，Excel2003是用的 HSSFWorkbook，Excel2007用的是XSSFWorkbook，获取后赋值给Workbook引用，这里利用了多态的思想。
  3. 获取到Workbook后，我们便可以开始读取数据 
        /**
     * 根据Workbook，读取Excel数据
     * @param workbook excel工作簿
     * @return excel中数据
     */
    private static List<List<Object>> readExcel(Workbook workbook) {
        List<List<Object>> res = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            int lastCellNum = row.getLastCellNum();
            List<Object> rdata = new ArrayList<>(lastCellNum);
            for (int j = 0 ; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                rdata.add(getCell(cell));
            }
            res.add(rdata);
        }
        return res;
    }

    解释下各个方法：

  • workbook.getSheetAt(index): 获取指定下标下的sheet，sheet就是你打开Excel后右下角就可以看到这样一个标识                

          Excel导入导出——学习笔记

  主要是用于打印时区分纸张，一个Sheet表示一张纸，一般我们读取第一个也就是下标为0的一个，当然你可以自行对方法进行扩展，写死总是不好的。

  • sheet.getLastRowNum(): 获取该Sheet行数
  • row.getLastCellNum(): 获取该行有多少单元格，即多少列

  了解这些方法后，我们就可以用遍历二维数组的思想去遍历每个单元格就行了,需要注意的是要根据单元格类型调用不同方法获取单元格数据

   /**
     * 获取单元格数据
     * @param cell 单元格对象
     * @return 单元格中数据
     */
    private static Object getCell(Cell cell) {
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                return cell.getNumericCellValue();
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return StringUtils.EMPTY;
            case ERROR:
                return StringUtils.EMPTY;
            case _NONE:
                return StringUtils.EMPTY;
            default:
                return null;
        }
    }

分解导出Excel的思路

  1. 创建一个空的Workbook (工作簿)
  2. 创建一个空的Sheet
  3. 设置样式
  4. 根据数据（依旧是二维数组的形式），来创建行，创建单元格
  5. 将工作簿内容写入输出流进行输出，最后关闭流
   /**
     * 输出Excel数据流，并最终关闭流
     * @param dataLists 传入数据
     * @param out 输出流
     */
    public static void createExcel(List<List<Object>> dataLists, OutputStream out) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFCellStyle titleCellStyle = workbook.createCellStyle();

        //设置单元标题样式
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleCellStyle.setFillForegroundColor(HSSFColorPredefined.SKY_BLUE.getIndex());
        titleCellStyle.setWrapText(true);
        //设置单元标题字体
        HSSFFont titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 13);
        titleCellStyle.setFont(titleFont);

        //设置表格内容单元样式
        HSSFCellStyle valueCellStyle = workbook.createCellStyle();
        valueCellStyle.setAlignment(HorizontalAlignment.CENTER);
        valueCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFFont cellFont = workbook.createFont();
        cellFont.setFontHeightInPoints((short) 12);
        valueCellStyle.setFont(cellFont);

        // 二维形式
        for (int i = 0; i < dataLists.size(); i++) {
            HSSFRow row = sheet.createRow(i);
            List<Object> objects = dataLists.get(i);

            for (int j = 0; j < objects.size(); j++) {
                HSSFCell valueCell = row.createCell(j);
                valueCell.setCellStyle(valueCellStyle);
                valueCell.setCellValue(objects.get(j).toString());
            }
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        //导出成功!
    }

ExcelController

  控制层，主要记忆一下SpringMVC的上传下载流程，还有下载文件时ResponseHeader的设置

package com.podago.demo.controller;

import com.podago.demo.utils.ExcelUtil;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

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

/**
 * @author chenxiaotao
 * @version 1.0
 * @date 2019/3/27 9:57
 **/
@RestController
@RequestMapping("/excel")
public class ExcelController {
    private static final String EXCEL2003_CONTENT_TYPE = "application/vnd.ms-excel";
    private static final String EXCEL2007_CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    @RequestMapping(value = "/upload", method = RequestMethod.POST)
    public List<List<Object>> upload(@RequestPart MultipartFile excel) {
        String contentType = excel.getContentType();
        if (contentType.equals(EXCEL2003_CONTENT_TYPE) || contentType.equals(EXCEL2007_CONTENT_TYPE)) {
            return ExcelUtil.readExcel(excel);
        } else {
            return new ArrayList<>(0);
        }
    }

    @RequestMapping(value = "/download", method = RequestMethod.POST, produces = {"application/vnd.ms-excel;charset=UTF-8"})
    public void download(@RequestBody List<List<Object>> data, HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=excel.xls");
        ServletOutputStream outputStream = response.getOutputStream();
        ExcelUtil.createExcel(data, outputStream);
    }
}

前端上传下载

  前端使用了boostrap-fileinput组件，这个组件是可以中文化的，并且感觉很强大，关于这个组件的使用这里不过多描述，可自行查阅资料。

  前端实现的就是，把Excel导入后转换为JSON数据返回回来，把这段JSON数据传回后端能够导出为Excel文件

  excel.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <!-- 最新版本的 Bootstrap 核心 CSS 文件 -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
    <!-- Bootstrap-FileInput CSS -->    
    <link rel="stylesheet" href="dict/plugins/bootstrap-fileinput/css/fileinput.css">

</head>
<body>
    <div id="app" class="panel col-md-8">
        <input id="txt_file" name="excel" type="file" class="file" multiple
                 data-show-upload="true" data-show-caption="true" data-show-preview="false" data-upload-url="/excel/upload" data-allowed-file-extensions='["xlsx","xls"]'>
        <h5>excel文件内容：</h5>
        <textarea class="form-control" disabled rows="5">{{excel_data}}</textarea>
        <a @click="download" class="btn btn-primary">导出Excel</a>
    </div>
</body>
<script src="dict/plugins/jquery-3.3.1.min.js"></script>
<!-- 最新的 Bootstrap 核心 JavaScript 文件 -->
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>
<!-- Bootstrap-FileInput JS -->
<script src="dict/plugins/bootstrap-fileinput/js/fileinput.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script>
<script src="dict/excel/excel.js"></script>
</html>

  excel.js

  这里的原生ajax异步下载可以多注意下，它异步获取后 response 的 blob 后利用a标签进行下载，我觉得是个挺好用的东西。

var app = new Vue({
   el: '#app',
   data: {
       excel_data: ''
   },methods:{
        download:function() {
        var url = '/excel/download';
        var xhr = new XMLHttpRequest();
        xhr.open('POST', url, true);        // 也可以使用POST方式，根据接口
        xhr.responseType = "blob";    // 返回类型blob
        xhr.setRequestHeader("Content-Type", "application/json;charset=utf-8");
        // 定义请求完成的处理函数，请求前也可以增加加载框/禁用下载按钮逻辑
        xhr.onload = function () {
            // 请求完成
            if (this.status === 200) {
                // 返回200
                var blob = this.response;
                var reader = new FileReader();
                var filename = this.getResponseHeader("Content-Disposition").substring(20);
                reader.readAsDataURL(blob);    // 转换为base64，可以直接放入a表情href
                reader.onload = function (e) {
                    // 转换完成，创建一个a标签用于下载
                    var a = document.createElement('a');
                    a.download = filename;
                    a.href = e.target.result;
                    $("body").append(a);    // 修复firefox中无法触发click
                    a.click();
                    $(a).remove();
                }
            }
        };
        // 发送ajax请求
        xhr.send(JSON.stringify(app.excel_data))

}
    }, mounted: function() {
        //导入文件上传完成之后的事件
        $("#txt_file").on("fileuploaded", function (event, data, previewId, index) {
            app.excel_data = data.response;
        });
   }
});

最后展示的页面：

Excel导入导出——学习笔记

测试

测试用Excel：

Excel导入导出——学习笔记

在前端页面选择文件后上传：

Excel导入导出——学习笔记

上传成功

 

测试一下 下载 功能：

Excel导入导出——学习笔记

点击导出Excel按钮进行导出，弹出下载框下载，打开Excel内容进行对比可知下载成功！

相关推荐