参考：
https://blog.csdn.net/qq_39291028/article/details/95065231
https://blog.csdn.net/w893932747/article/details/89354979

目标

• 导入
• 导出

特殊说明

本实例为了增强通用性和可移植性，采用的是前端上传文件后端springboot处理的方式

后端下载相关依赖

    <!-- excel支持 -->
       <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.13</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.13</version>
    </dependency>

导入

excel数据导入主要涉及三个步骤:
1.文件上传
2.excel解析
3.数据插入

前台

springboot-vue工程下的mulupload.vue

<template>
   <el-row>
        <el-col :span="8" :offset="8">
            <div id="upload">
                <label class="el-form-item__label" style="width: 80px;">上传图片</label>
                <!--elementui的上传图片的upload组件-->
                <el-upload
                  class="upload-demo"
                  :before-upload="beforeupload"
                  drag
                  action="http://localhost:8000/haha/multiUpload4Param"
                  style="margin-left:80px;"
                  >
                  <i class="el-icon-upload"></i>
                  <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
                  <!--<div class="el-upload__tip" slot="tip">只能上传jpg/png文件，且不超过500kb</div>-->
                </el-upload>
                
                <!--elementui的form组件-->
                <el-form ref="form" :model="form" label-width="80px">
                    <el-form-item label="活动名称">
                        <el-input v-model="form.name" name="names" style="width:360px;"></el-input>
                    </el-form-item>
                    <el-form-item>
                        <el-button type="primary" @click="onSubmit">立即创建</el-button>
                        <el-button>取消</el-button>
                    </el-form-item>
                </el-form>
            </div>
        </el-col>
        
        <!--展示选中图片的区域-->
        <el-col :span="4" >
            <div style="width:100%;overflow: hidden;margin-left:150px;">
                <img :src="src" alt="" style="width:100%;"/>
            </div>
        </el-col>
    </el-row>
</template>

    <script>
    export default {
        data() {
            return {
                form: {//form里面的参数
                  name: ''
               },
               param:"",//表单要提交的参数
               src:"https://afp.alicdn.com/afp-creative/creative/u124884735/14945f2171400c10764ab8f3468470e4.jpg" //展示图片的地址
            };
        },
        methods: {
            beforeRemove(file, fileList) {
                //return this.$confirm(`确定移除 ${ file.name }？`);
            },
            //阻止upload的自己上传，进行再操作
            beforeupload(file) {
                console.log(file);
                //创建临时的路径来展示图片
                var windowURL = window.URL || window.webkitURL;
                
                this.src=windowURL.createObjectURL(file);
                //重新写一个表单上传的方法
                this.param = new FormData();
                this.param.append('file', file, file.name);
                return false;
            },
            //覆盖默认的上传行为
            httprequest() {

            },
            onSubmit(){//表单提交的事件
                var names = this.form.name;
                //下面append的东西就会到form表单数据的fields中；
                this.param.append('name', names);
                let config = {
                    headers: {
                        'Content-Type': 'multipart/form-data'
                    }
                };
                //然后通过下面的方式把内容通过axios来传到后台
                //下面的this.$reqs 是在主js中通过Vue.prototype.$reqs = axios 来把axios赋给它;
                /* this.$axios.post("http://localhost:8000/haha/multiUpload4Param", this.param, config).then(function(result) {
                                console.log(result);
                }) */
                this.$axios.post("http://localhost:8000/haha/multiUpload4ParamExcel", this.param, config).then(function(result) {
                                alert(result.data);
                })
            }
        }
    }
</script>


<style>

</style>

后台

demo-his01工程下的UploadController

@PostMapping("/multiUpload4ParamExcel")
@ResponseBody
public String multiUpload4ParamExcel(HttpServletRequest request,String name,String age,MultipartFile[] file,String c_id) throws Exception {
    //System.out.println(name);
    //System.out.println(age);
    List<MultipartFile> files = ((MultipartHttpServletRequest) request).getFiles("file");
    for (int i = 0; i < files.size(); i++) {
        MultipartFile fileitem = files.get(i);
        if (fileitem.isEmpty()) {
            return "上传第" + (i++) + "个文件失败";
        }
        InputStream inputStream = fileitem.getInputStream();
        List<List<Object>> list = importService.getBankListByExcel(inputStream, fileitem.getOriginalFilename());
        inputStream.close();
        for (int j = 0; j < list.size(); j++) {
            List<Object> lo = list.get(j);
            //随意发挥,可以拼装对象并入库
            System.out.println(lo);
        }
    }

    return "上传成功";
}

service

package com.neuedu.demo.service.impl;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

@Service
public class ImportService {
    /**
     * 处理上传的文件
     *
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public List getBankListByExcel(InputStream in, String fileName) throws Exception {
        List list = new ArrayList<>();
        //创建Excel工作薄 根据后缀不同，创建不同的工作薄对象
        Workbook work = this.getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        
        Sheet sheet = null;//sheet页 工作表
        Row row = null;//行
        Cell cell = null;//列

        for (int i = 0; i < work.getNumberOfSheets(); i++) {//逐个读取工作表
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {//读取行
                row = sheet.getRow(j);
                if (row == null || row.getFirstCellNum() == j) {//路过第一行表头
                    continue;
                }

                List<Object> li = new ArrayList<>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(cell);
                }
                list.add(li);
            }
        }
        work.close();
        return list;
    }

    /**
     * 判断文件格式
     *
     * @param inStr
     * @param fileName
     * @return
     * @throws Exception
     */
    public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("请上传excel文件！");
        }
        return workbook;
    }

}

存成map

@PostMapping("excel/import")
public String excelImport(MultipartFile[] file, String name, String age) throws Exception {
    String result = "";
    // 打印文件上传的原始文件名
    System.out.println(name);
    for (MultipartFile fileitem : file) {
        // 获取原始文件名称
        String fileName = fileitem.getOriginalFilename();
        // 第一步构建 工作簿
        // 定义一个空的工作簿
        Workbook workbook = null;
        // 截取文件后缀
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {// 如果后缀为.xls 2007版之前的
            // HSSFWorkbook接受的是输入流参数
            workbook = new HSSFWorkbook(fileitem.getInputStream());
        } else if (".xlsx".equals(fileType)) {// 2007版本以后的
            workbook = new XSSFWorkbook(fileitem.getInputStream());
        } else {
            throw new Exception("请上传excel文件！");
        }

        // 第二步 读取工作表对象(假设有多个)
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        //List<Object> list = new ArrayList<>();
        List<Map<String,Object>> list=new ArrayList();
        int sheet_size = workbook.getNumberOfSheets();// 方法可以获取该工作簿里的工作表个数
        if (sheet_size > 0) {// 如果工作表的个数大于0
            for (int j = 0; j <sheet_size; j++) {
                // 用for循环逐个读取工作表
                // workbook.getSheet("工作表名");//用工作表名来读取
                sheet = workbook.getSheetAt(j);
                // 第三步 读取行:需要知道有多少行
                for (int k = sheet.getFirstRowNum(); k <= sheet.getLastRowNum(); k++) {
                    if(k<=1){
                        continue;
                    }
                    row = sheet.getRow(k);
                    Map<String,Object>  map=new HashMap();
                    for (int i = row.getFirstCellNum(); i <=row.getLastCellNum(); i++) {
                        cell = row.getCell(i);
                        // 第四步，读取列：需要知道有多少列
                        map.put(""+i, cell);
                        
                    }
                    list.add(map);
                }
            }

        }
        
        System.out.println(list);
        for(Map<String,Object> mapitem:list){
            System.out.println(mapitem.get("1"));
            System.out.println("----------------->");
            //封装对象，入库
        }
    }

    return result;
}

测试

image.png

后台打印

[1.0, wang.qj, 123456.0]
[2.0, zhangs, 123456.0]

image.png

导出-简单实例

@GetMapping("exportexcel")
public void exportPermMatrix(HttpServletRequest request, HttpServletResponse response) throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("员工信息表");
    //此处添加数据
    HSSFRow headerRow1 = sheet.createRow(0);
    headerRow1.createCell(0).setCellValue("编号");
    headerRow1.createCell(1).setCellValue("内容");
    HSSFRow headerRow2 = sheet.createRow(1);
    headerRow2.createCell(0).setCellValue("01");
    headerRow2.createCell(1).setCellValue("测试文本");
    //清空response
    response.reset();
    response.setContentType("multipart/form-data");
    response.setHeader("Content-Disposition",
            "attachment; filename=" + new String("excel模板".getBytes(), "iso8859-1") + ".xls");
    OutputStream os = new BufferedOutputStream(response.getOutputStream());  
    workbook.write(os);
    os.flush();
    os.close();
    workbook.close();
}

作业

学生完成his系统常数项表的导入和导出