layui+poi 做 导入 Excel

点击导出按钮弹出导出选择框

写一个导出按钮

        <button class="layui-btn layui-btn-sm" lay-event="importExce" data-method="offset" data-type="auto" class="layui-btn layui-btn-normal">导入Excel</button>

写一个弹出框

<!--导出的上传框-->
<div class="layui-form-item" style="display: none;" id="importDiv">
    <div class="layui-upload-drag" id="importExcel">
        <i class="layui-icon"></i>
        <p>点击上传，或将Excel文件拖拽到此处</p>
        <div class="layui-hide"  id="importExcelDiv">
            <input type="hidden" name="importExcel"/>
        </div>
    </div>
</div>

 //头工具栏事件
        table.on('toolbar(aaa)', function(obj){

            $(':focus').blur();
            var data = obj.data;

            //新增
            if(obj.event === 'addPhone'){
                layer.open({
                    type: 1,
                    title:"新增手机",
                    content: $("#addForm"),
                    area:['50%','70%'],
                    success:function (layero,index) {
                        laydate.render({
                            elem:"#producedDate",
                            type:"datetime",
                            format:"yyyy-MM-dd HH:mm:ss",
                            trigger: 'click'
                        })

                        //新增
                       selectBrand("/phone/queryBrand.do","brandId1",null);
                        //新增
                       selectarea("/phone/queryArea.do","areaId1",null);

                        //上传图片
                        layui.$('#uploadDemoView').removeClass('layui-hide').find('img').attr('src', '');
                        $('#imgpath').val();
                    }
                    ,btn:["提交","取消"]
                    ,yes:function (index,layero) {
                        $.ajax({
                            url:"/phone/addPhone.do",
                            type:"post",
                            data:$("#addForm").serialize(),
                            dataType:"text",
                            success:function (result) {
                                if(result){
                                    layer.msg("添加成功");
                                    layer.close(index);
                                        // 重新加载表格数据
                                    tableIns.reload();
                                }else{
                                    layer.msg("新增失败");
                                }
                            },
                            error:function () {
                                layer.msg("新增异常");
                            }

                        })

                    }
                })

                //导出
            }else if(obj.event === 'exportExcel'){
                // 获取条件查询的form表单
                var seachForm = document.getElementById("seachForm");
                // 给form表单设置提交路径
                seachForm.action = "<%=request.getContextPath()%>/excelController/exportExcel2.do";
                // 提交form表单
                seachForm.submit();
            }else if(obj.event === 'importExce'){
                //导入方法
                importExce();
            }
        });

接收发起请求：

		  //导入Excel
        var imports;
        function importExce() {
            imports = layer.open({
                type:1,
                title:"导入Excel",
                content: $("#importDiv"),
                area:['auto'],
                anim : 4,
                btnAlign : 'c',
            })
        }

      //上传Excel
        upload.render({
            elem: '#importExcel',
            url: '/excelController/importExcel.do',
            accept : 'file',
            exts : 'xlsx',
            done: function(res){
                if(res){
                    layer.msg('导入成功');
                    layer.close(imports);
                    tableIns.reload();
                }else{
                    layer.msg('导入失败');
                    layer.close(imports);
                }

            }
        });


      

后端控制器

package com.jq.controller;

import com.jq.model.Area;
import com.jq.model.Brand;
import com.jq.model.Phone;
import com.jq.model.vo.PhoneVo;
import com.jq.service.PhoneService;
import com.jq.util.ExcelUtil;
import lombok.AllArgsConstructor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;

@Controller
@RequestMapping("excelController")
public class ExcelController {

    @Autowired
    private PhoneService phoneService;




    // 导入Excel
    @RequestMapping("importExcel")
    @ResponseBody															//importExcel是弹出框name值							
    public Map<String, Object> importExcel(@RequestParam("file") MultipartFile importExcel){
        Map<String, Object> map = new HashMap<>();
        try {
            //1.将文件封装成工作薄
            XSSFWorkbook workbook = new XSSFWorkbook(importExcel.getInputStream());
            //2.获取工作表的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            //3.循环所有的工作表，根据工作表下标获取对应的sheet
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = workbook.getSheetAt(i);
                // 4.获取当前工作表中数据的开始位置
                int firstRowNum = sheet.getFirstRowNum();
                // 5.获取当前工作表中数据的结束位置
                int lastRowNum = sheet.getLastRowNum();
                // 6.将文件中的数据封装为一个list保存到数据库中
                List<Phone> list = new ArrayList<Phone>();
                for (int j = firstRowNum+1; j <= lastRowNum; j++) {
                    // 获取sheet中的每一行
                    XSSFRow row = sheet.getRow(j);
                    // 获取行中单元格的值
                    Phone phone = getExcelDate(row);
                    //导入
                    phoneService.addPhone(phone);
                }
            }
            map.put("state", true);
        } catch (IOException e) {
            e.printStackTrace();
            map.put("state", false);
        }

        return map;
    }


    // 获取行中单元格的值并赋值
    private Phone getExcelDate(XSSFRow row) {
        String name = row.getCell(1).getStringCellValue();
        double price = row.getCell(2).getNumericCellValue();
        double screen = row.getCell(3).getNumericCellValue();
        String isup = row.getCell(4).getStringCellValue();
        String os = row.getCell(5).getStringCellValue();
        String config = row.getCell(6).getStringCellValue();
        Date producedDate = row.getCell(7).getDateCellValue();
        Date createDate = row.getCell(8).getDateCellValue();
        Date updateDate = row.getCell(9).getDateCellValue();
        //地区
        String area = row.getCell(10).getStringCellValue();
        String brand = row.getCell(11).getStringCellValue();
        //图片
        String img = row.getCell(12).getStringCellValue();

        //给对象赋值
        Phone  phone =  new Phone();
        phone.setName(name);
        phone.setPrice(price);
        phone.setScreen(screen);
        //单选
        phone.setIsup(isup.equals("是")?1:isup.equals("否")?2:null);
        //单选
        phone.setOs(os.equals("Android")?1:os.equals("IOS")?2:os.equals("Windows")?3:null);
        //多选
        phone.setConfig(config.replace("双卡双待", "1").replace("语音控制", "2").replace("指纹解锁", "3").replace("远程控制", "4"));
        phone.setProducedDate(producedDate);
        phone.setCreateDate(createDate);
        phone.setUpdateDate(updateDate);
        //地区
        phone.setAreaId(area.equals("山东")?1:area.equals("河南")?2:area.equals("北京")?3:null);
        //品牌
        phone.setBrandId(brand.equals("苹果")?1:brand.equals("oppo")?2:brand.equals("小米")?3:null);
        //图片
        phone.setImg(img);

        return phone;

    }





}

效果：