layui 读取excel文件 数据表格展示 支持条件查询

1，效果图

excel 内容

html 页面内容

2，文件存放目录：

3, 后端代码IndexController

import java.io.*;
import java.util.*;

import com.alibaba.fastjson.JSON;
import com.dldata.drgscase.utils.POIUtil;
import com.dldata.drgscase.utils.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 */
@Controller
@RequestMapping("")
public class IndexController {

    //获取查询参数
    //读取excel内容
    //返回结果集数据
    @RequestMapping(value = "/search")
    @ResponseBody
    public Map search(@RequestParam Map<String,Object> paramMap, Model model) throws Exception {
        try {
            HashMap<String, Object> map = new HashMap<>();
            map.put("success", Boolean.TRUE);
            map.put("msg", "");
            HashMap<String, Object> argmap = new HashMap<>();
            if(!StringUtil.isEmptyOrLength0(paramMap.get("name"))){
                argmap.put("name", paramMap.get("name"));
            }
            if(!StringUtil.isEmptyOrLength0(paramMap.get("banji"))){
                argmap.put("banji", paramMap.get("banji"));
            }
            Map strlist=read(argmap);
            map.put("list", strlist.get("list"));
            return map;
        } catch (Exception e) {
            e.printStackTrace();
            HashMap<String,Object> map=new HashMap<>();
            map.put("success",Boolean.FALSE);
            map.put("msg","");
            return map;
        }
    }

    public Map read(HashMap<String, Object> argmap) {

        InputStream inputStream = null;
        Workbook workbook = null;
        try {
        //相对地址
            inputStream = this.getClass().getResourceAsStream("/excel/目录.xlsx");
            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 = row.getCell(0);
            //得到单元格样式
            CellStyle cellStyle = cell.getCellStyle();
            System.out.println("行数：" + rowLength + ",列数：" + colLength);
            List<Map<String,Object>> list=new LinkedList<>();
            HashSet<String> key=new LinkedHashSet<>();
            for (int i = 0; i < rowLength; i++) {
                Boolean flag=true;
                row = sheet.getRow(i);
                HashMap<String,Object> map=new LinkedHashMap<>();
                for (int j = 0; j < colLength; j++) {
                    cell = row.getCell(j);
                    //Excel数据Cell有不同的类型，当我们试图从一个数字类型的Cell读取出一个字符串时就有可能报异常：
                    //Cannot get a STRING value from a NUMERIC cell
                    //将所有的需要读的Cell表格设置为String格式
                    if (cell != null){
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    }
					//获取标题数值，作为key
                    if (i == 0){
                        key.add(cell.getStringCellValue());
                    }
					//使用参数过滤内容
                    if (i != 0){
                        String val="";
                        if(!StringUtil.isEmptyOrLength0(cell)){
                            val=cell.getStringCellValue();
                        }
                        if (!StringUtil.isEmptyOrLength0(argmap.get("name"))&&j==0){
                            if(!val.contains(argmap.get("name").toString())){
                                flag=false;
                                break;
                            }
                        }
                        if (!StringUtil.isEmptyOrLength0(argmap.get("banji"))&&j==2){
                            if(!val.contains(argmap.get("banji").toString())){
                                flag=false;
                                break;
                            }
                        }
                     //对号存放数据
                        int k=0;
                        for (String s : key){
                            if(k==j){
                                map.put(s,val);
                                break;
                            }
                            k++;
                        }
                    }
                }
                if(i != 0&&flag==true){
                    list.add(map);
                }
            }
            HashMap<String,Object> map=new HashMap<>();
            map.put("success",Boolean.TRUE);
            map.put("list",list);
            return map;
        } catch (Exception e) {
            e.printStackTrace();
            HashMap<String,Object> map=new HashMap<>();
            map.put("success",Boolean.FALSE);
            return map;
        }
    }
//测试
//    public static void main(String[] args) {
//        new IndexController().read();
//    }

}

3，前端主要代码 index.html

    <div class="layui-inline">
        <label style="margin-left: 10px;">姓名</label>
        <div class="layui-input-inline"  >
            <input type="text" id="name" name="name" style="width: 120px;height: 25px;margin-left: 10px;border: 1px solid #e6e6e6"  />
        </div>
    </div>
    <div class="layui-inline">
        <label style="margin-left: 10px;">班级</label>
        <div class="layui-input-inline"  >
            <input type="text" id="banji" name="banji" style="width: 120px;height: 25px;margin-left: 10px;border: 1px solid #e6e6e6"  />
        </div>
    </div>
    <div class="layui-btn layui-btn-xs buttonclass" style="margin-left: 10px;height: 26px;width: 60px;" data-type="reload" onclick="searchBtn()">查询</div>

    <div class="fp-table" style="width: 100%;padding-top: 5px">
        <div class="fp-table" style="width: 100%;">
            <table id="hstable" lay-filter="hstable"></table>
        </div>
    </div>


    function searchBtn() {
        var postdata={
            name: $("#name").val(),
            banji: $("#banji").val(),
        }

        $.getJSON("/search",postdata,function(data){
            if (data.success) {
                hstable(data.list,true)
            }else {
                hstable([],true)
            }
        },true);
    }
   

    function hstable(data) {
        var table = layui.table;
        var ins1=table.render({
            elem: '#hstable',
            id: 'hstable',
            height: "full-70",
            // width: 7* CLIENTWIDTH / 10,
            size: 'sm' ,
            filter:{
                items:['colum','data','condition','editCondition'],
                bottom:false
            },
            page: false,
            limit:200,
            limits:[15,30,50,100,200,500,1000,2000,5000,10000],
            cols: [ [
                {field: '姓名', title: '姓名', width: 140, sort: true, align: 'left', halign: 'center',filter:true},
                {field: '年龄', title: '年龄', width: 140, sort: true, align: 'left', halign: 'center',filter:true},
                {field: '班级', title: '班级', width: 140, sort: true, align: 'left', halign: 'center',filter:true},              
                {field: 'operate', title: '操作', width: 100, align: 'center', halign: 'center', templet: function (d) {
                        return '<a style="color: #409EFF;" οnclick="fileurl(\'' + d.姓名+ '\')">查看</a>'
                    }
                },
            ]],
            data: data? data : [],
            done: function (res, curr, count) {
                layui.soulTable.render(this)
            }
        });
    }
    
 //文件名链接
    function fileurl(filename) {
        var url="/static/html/"+filename+".html"
        window.topWin.layer.open({
            type: 2, // 这里为 2   0:信息框,默认 1:页面层 2:iframe层 3:加载层 4:tips层
            title: '信息',
            skin: 'layui-layer-rim',
            area : ['100%', '100%'], // 可为百分比
            content: url
        });
    }
    
</script>
</html>