本章用的是MultipartFile实现的文件导入,页面使用的是html、thymeleaf以及若依封装的js,该章节基于若依一体项目!!!!!请注意!!!!!

来吧,话不多说上代码。

1. 创建实体,这里我就省略了,换成自己需要导入的实体即可。

2. 下面是自己写的一个工具类,用于根据文件头来判断文件类型,代码如下:

public class MineTypeUtils {

    /**
     * 缓存文件头信息-文件头信息
     */
    public static final HashMap<String, String> mFileTypes = new HashMap<String, String>();

    static {
        // images
        mFileTypes.put("FFD8FF", "jpg");
        mFileTypes.put("89504E47", "png");
        mFileTypes.put("47494638", "gif");
        mFileTypes.put("49492A00", "tif");
        mFileTypes.put("424D", "bmp");
        /*CAD*/
        mFileTypes.put("41433130", "dwg");
        mFileTypes.put("38425053", "psd");
        /* 日记本 */
        mFileTypes.put("7B5C727466", "rtf");
        mFileTypes.put("3C3F786D6C", "xml");
        mFileTypes.put("68746D6C3E", "html");
        // 邮件
        mFileTypes.put("44656C69766572792D646174653A", "eml");
        mFileTypes.put("D0CF11E0", "doc");
        //excel2003版本文件
        mFileTypes.put("D0CF11E0", "xls");
        mFileTypes.put("5374616E64617264204A", "mdb");
        mFileTypes.put("252150532D41646F6265", "ps");
        mFileTypes.put("255044462D312E", "pdf");
        mFileTypes.put("504B0304", "docx");
        //excel2007以上版本文件
        mFileTypes.put("504B0304", "xlsx");
        mFileTypes.put("52617221", "rar");
        mFileTypes.put("57415645", "wav");
        mFileTypes.put("41564920", "avi");
        mFileTypes.put("2E524D46", "rm");
        mFileTypes.put("000001BA", "mpg");
        mFileTypes.put("000001B3", "mpg");
        mFileTypes.put("6D6F6F76", "mov");
        mFileTypes.put("3026B2758E66CF11", "asf");
        mFileTypes.put("4D546864", "mid");
        mFileTypes.put("1F8B08", "gz");
    }

    /**
     * <p>Title:getFileType </p>
     * <p>Description: 根据文件路径获取文件头信息</p>
     *
     * @param filePath 文件路径(非网络文件)
     * @return 文件头信息
     */
    public static String getFileType(String filePath) {
        //返回十六进制  如:504B0304
        //System.out.println(mFileTypes.get(getFileHeader(filePath)));  //xlsx
        return mFileTypes.get(getFileHeader(filePath));
    }

    /**
     * <p>Title:getFileTypeByFileInputStream </p>
     * <p>Description: 根据文件流获取文件头信息</p>
     *
     * @param is 文件流
     * @return 文件头信息
     */
    public static String getFileTypeByFileInputStream(InputStream is) {
        return mFileTypes.get(getFileHeaderByFileInputStream(is));
    }

    /**
     * <p>Title:getFileHeader </p>
     * <p>Description: 根据网络文件路径获取文件类型信息 </p>
     *
     * @param netFilePath
     * @return 文件类型 @see mFileTypes
     * @throws Exception
     */
    public static String getFileTypeByNetFilePath(String netFilePath) throws Exception {
        trustAllHosts();
        URLConnection url = new URL(netFilePath).openConnection();
        url.connect();
        return getFileTypeByFileInputStream(url.getInputStream());
    }

    /**
     * <p>Title:getFileHeader </p>
     * <p>Description: 根据文件路径获取文件头信息 </p>
     *
     * @param filePath 文件路径
     * @return 十六进制文件头信息
     */
    private static String getFileHeader(String filePath) {
        FileInputStream is = null;
        String value = null;
        try {
            is = new FileInputStream(filePath);
            byte[] b = new byte[4];
            /*
             * int read() 从此输入流中读取一个数据字节。int read(byte[] b) 从此输入流中将最多 b.length
             * 个字节的数据读入一个 byte 数组中。 int read(byte[] b, int off, int len)
             * 从此输入流中将最多 len 个字节的数据读入一个 byte 数组中。
             */
            is.read(b, 0, b.length);
            value = bytesToHexString(b);
        } catch (Exception e) {
        } finally {
            if (null != is) {
                try {
                    is.close();
                } catch (IOException e) {
                }
            }
        }
        return value;
    }

    /**
     * <p>Title:getFileHeaderByFileInputStream </p>
     * <p>Description: 根据文件流获取文件头信息</p>
     *
     * @param is 文件流
     * @return 十六进制文件头信息
     */
    private static String getFileHeaderByFileInputStream(InputStream is) {
        String value = null;
        try {
            byte[] b = new byte[4];
            /*
             * int read() 从此输入流中读取一个数据字节。int read(byte[] b) 从此输入流中将最多 b.length
             * 个字节的数据读入一个 byte 数组中。 int read(byte[] b, int off, int len)
             * 从此输入流中将最多 len 个字节的数据读入一个 byte 数组中。
             */
            is.read(b, 0, b.length);
            value = bytesToHexString(b);
        } catch (Exception e) {
        } finally {
            if (null != is) {
                try {
                    is.close();
                } catch (IOException e) {
                }
            }
        }
        return value;
    }

    /**
     * <p>Title:bytesToHexString </p>
     * <p>Description: 将要读取文件头信息的文件的byte数组转换成string类型表示 </p>
     *
     * @param src 要读取文件头信息的文件的byte数组
     * @return 文件头信息
     */
    private static String bytesToHexString(byte[] src) {
        StringBuilder builder = new StringBuilder();
        if (src == null || src.length <= 0) {
            return null;
        }
        String hv;
        for (int i = 0; i < src.length; i++) {
            // 以十六进制(基数 16)无符号整数形式返回一个整数参数的字符串表示形式,并转换为大写
            hv = Integer.toHexString(src[i] & 0xFF).toUpperCase();
            if (hv.length() < 2) {
                builder.append(0);
            }
            builder.append(hv);
        }
        //System.out.println(builder.toString());
        return builder.toString();
    }

    /**
     * 解决网络证书问题
     * 如下异常: unable to find valid certification path to requested target
     *
     * @throws Exception
     */
    public static void trustAllHosts() throws Exception {
        // Create a trust manager that does not validate certificate chains
        TrustManager[] trustAllCerts = new TrustManager[]{new X509TrustManager() {
            @Override
            public java.security.cert.X509Certificate[] getAcceptedIssuers() {
                return new java.security.cert.X509Certificate[]{};
            }

            @Override
            public void checkClientTrusted(X509Certificate[] chain, String authType)
                    throws CertificateException {
            }

            @Override
            public void checkServerTrusted(X509Certificate[] chain, String authType)
                    throws CertificateException {
            }
        }
        };

        try {
            SSLContext sc = SSLContext.getInstance("TLS");
            sc.init(null, trustAllCerts, new java.security.SecureRandom());
            HttpsURLConnection.setDefaultSSLSocketFactory(sc.getSocketFactory());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //测试
    public static void main(String[] args) {

        String panFile = "E:\\杆塔.xls";
        String type = MineTypeUtils.getFileType(panFile);
        System.out.println(type);

    }

}

3. 在业务层撰写读取文件逻辑,代码如下:

说明:switch的判断是表格中自己需要数据的列,你需要的字段在第几列写第几列就好。

public class ReadXlsServiceImpl implements ReadXlsService {
	//判断该文件的内容是否是杆塔信息
     @Override
    public Boolean judgmentFileTower(String excelPath) {
        int number = 0;
        try {
            File excel = new File(excelPath);
            if (excel.isFile() && excel.exists()) {   //判断文件是否存在

                //获取文件类型
                String type = MineTypeUtils.getFileType(excelPath);
//                String[] split = excel.getName().split("\\.");  //.是特殊字符,需要转义
                Workbook wb = null;

                try {
                    //根据文件后缀(xls/xlsx)进行判断
                    if ("xls".equals(type)) {
                        FileInputStream fis = new FileInputStream(excel);   //文件流对象
                        wb = new HSSFWorkbook(fis);
                    } else if ("xlsx".equals(type)) {
                        wb = new XSSFWorkbook(excel);
                    } else {
                        log.error("文件类型错误!");
                    }

                    Sheet sheet = wb.getSheetAt(0);     //读取sheet 0

                    int firstRowIndex = sheet.getFirstRowNum();
                    int lastRowIndex = sheet.getFirstRowNum() + 1;

                    for (int rIndex = firstRowIndex; rIndex < lastRowIndex; rIndex++) {   //遍历行
                        Row row = sheet.getRow(rIndex);
                        if (row != null) {
                            int firstCellIndex = row.getFirstCellNum();
                            int lastCellIndex = row.getLastCellNum();
                            for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
                                Cell cell = row.getCell(cIndex);
                                if (cell != null) {
                                    try {
                                        switch (cell.getColumnIndex()) {
                                            case 0: if("设备名称".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 5: if("*所属线路".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 6: if("PM编号".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 8: if("*运维单位".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 9: if("*维护班组".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 10: if("是否农网".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 12: if("*电压等级".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 13: if("投运日期".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 14: if("*设备状态".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 16: if("*杆塔材质".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 17: if("*杆塔性质".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 51: if("运行设备编码".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 63: if("物理设备编码".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 65: if("地区特征".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 87: if("obj_id".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 88: if("x坐标".equals(cell.toString()))
                                                number += 1;
                                                break;
                                            case 89: if("y坐标".equals(cell.toString()))
                                                number += 1;
                                                break;
                                        }


                                    }catch (Exception e) {
                                        log.error(e.toString());
                                    }

                                }

                            }

                        }
                    }
                }catch (Exception e) {
                    log.error("文本错误:"+e.toString());
                }

            } else {
                log.error("找不到指定的文件");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if(number == 17){
            return true;
        }
        return false;

    }
    
    
   @Override
    public int readXls(String excelPath) {
        if(judgmentFileTower(excelPath)){

            try {
                File excel = new File(excelPath);
                if (excel.isFile() && excel.exists()) {   //判断文件是否存在

                    //获取文件类型
                    String type = MineTypeUtils.getFileType(excelPath);
//                    String[] split = excel.getName().split("\\.");  //.是特殊字符,需要转义
                    Workbook wb = null;

                    try {
                        //根据文件后缀(xls/xlsx)进行判断
                        if ("xls".equals(type)) {
                            FileInputStream fis = new FileInputStream(excel);   //文件流对象
                            wb = new HSSFWorkbook(fis);
                        } else if ("xlsx".equals(type)) {
                            wb = new XSSFWorkbook(excel);
                        } else {
                            log.error("文件类型错误!");
                        }
                        //开始解析
                        Sheet sheet = wb.getSheetAt(0);     //读取sheet 0

                        int firstRowIndex = sheet.getFirstRowNum() + 1;   //第一行是列名,所以不读
                        int lastRowIndex = sheet.getLastRowNum();
                        //System.out.println("firstRowIndex: " + firstRowIndex);
                        //System.out.println("lastRowIndex: " + lastRowIndex);

                        List<Power> list = new ArrayList<>();

                        for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {   //遍历行
                            Power power = new Power();

//                        System.out.println("rIndex: " + rIndex);

                            Row row = sheet.getRow(rIndex);
                            if (row != null) {
                                int firstCellIndex = row.getFirstCellNum();
                                int lastCellIndex = row.getLastCellNum();
                                for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
                                    Cell cell = row.getCell(cIndex);

                                    if (cell != null) {
                                        try {
                                            switch (cell.getColumnIndex()) {
                                                case 0: power.setEquipmentName(cell.toString());
                                                    break;
                                                case 5: power.setLine(cell.toString());
                                                    break;
                                                case 6: power.setPmNumber(cell.toString());
                                                    break;
                                                case 8: power.setOperation(cell.toString());
                                                    break;
                                                case 9: power.setMaintain(cell.toString());
                                                    break;
                                                case 10: power.setRuralPowerFrid(cell.toString());
                                                    break;
                                                case 12: power.setVoltageClasses(cell.toString());
                                                    break;
                                                case 13: power.setCommissioningDate(cell.toString());
                                                    break;
                                                case 14: power.setEquipmentStatus(cell.toString());
                                                    break;
                                                case 16: power.setTowerTexture(cell.toString());
                                                    break;
                                                case 17: power.setNature(cell.toString());
                                                    break;
                                                case 51: power.setRunNumber(cell.toString());
                                                    break;
                                                case 63: power.setMacNumber(cell.toString());
                                                    break;
                                                case 65: power.setRegionalism(cell.toString());
                                                    break;
                                                case 87: power.setObjId(cell.toString());
                                                    break;
                                                case 88: if(PositionUtil.isNumeric(cell.toString()))
                                                    power.setWGSLng(cell.toString());
                                                    break;
                                                case 89: if(PositionUtil.isNumeric(cell.toString()))
                                                    power.setWGSLat(cell.toString());
                                                    break;
                                            }
                                            if(null != power.getWGSLat() && null != power.getWGSLng()){
                                                Gps gcj02 = PositionUtil.gps84_To_Gcj02(Double.valueOf(power.getWGSLat()), Double.valueOf(power.getWGSLng()));
                                                power.setGcj02Lng(String.valueOf(gcj02.getWgLon()));
                                                power.setGcj02Lat(String.valueOf(gcj02.getWgLat()));
                                                Gps bd09 = PositionUtil.gcj02_To_Bd09(gcj02.getWgLat(), gcj02.getWgLon());
                                                power.setBd09Lng(String.valueOf(bd09.getWgLon()));
                                                power.setBd09Lat(String.valueOf(bd09.getWgLat()));
                                            }


                                        }catch (Exception e) {
                                            log.error(e.toString());
                                        }


                                    }

                                }

                                list.add(power);
                            }
                        }

                        if(list.size() > 0){

                            return readXlsMapper.insertXls(list);
                        }
                    }catch (Exception e) {
                        log.error("文本错误:"+e.toString());
                    }


                } else {
                    log.error("找不到指定的文件");
                    return 0;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

            return 0;
        }
        return -1;

    }
    
}

 4. mapper代码如下:

int insertXls(@Param("list") List<Power> powerList);

 5. XML文件如下:

<insert id="insertXls" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        insert into station_coord_dict(
        equipment_name,
        bd09_lng,
        bd09_lat,
        WGS_lng,
        WGS_lat,
        gcj02_lng,
        gcj02_lat,
        line,
        operation,
        maintain,
        voltage_classes,
        equipment_status,
        tower_texture,
        nature,
        pm_number,
        run_number,
        mac_number,
        regionalism,
        rural_power_frid,
        commissioning_date,
        obj_id,
        flg
        ) values
        <foreach item="data" collection="list" separator=",">
            (
            #{data.equipmentName},
            #{data.bd09Lng},
            #{data.bd09Lat},
            #{data.WGSLng},
            #{data.WGSLat},
            #{data.gcj02Lng},
            #{data.gcj02Lat},
            #{data.line},
            #{data.operation},
            #{data.maintain},
            #{data.voltageClasses},
            #{data.equipmentStatus},
            #{data.towerTexture},
            #{data.nature},
            #{data.pmNumber},
            #{data.runNumber},
            #{data.macNumber},
            #{data.regionalism},
            #{data.ruralPowerFrid},
            #{data.commissioningDate},
            #{data.objId},
            0
            )

        </foreach>
        on duplicate key update
        equipment_name = values(equipment_name),
        bd09_lng = values(bd09_lng),
        bd09_lat = values(bd09_lat),
        WGS_lng = values(WGS_lng),
        WGS_lat = values(WGS_lat),
        gcj02_lng = values(gcj02_lng),
        gcj02_lat = values(gcj02_lat),
        line = values(line),
        operation = values(operation),
        maintain = values(maintain),
        voltage_classes = values(voltage_classes),
        equipment_status = values(equipment_status),
        tower_texture = values(tower_texture),
        nature = values(nature),
        pm_number = values(pm_number),
        run_number = values(run_number),
        mac_number = values(mac_number),
        regionalism = values(regionalism),
        rural_power_frid = values(rural_power_frid),
        commissioning_date = values(commissioning_date),
        obj_id = values(obj_id)

    </insert>

6. controller代码来了:

//将杆塔数据导入数据库
    @PostMapping("/importXls")
    @ResponseBody
    public AjaxResult importXls(@RequestParam("file") MultipartFile file) {
        //获取上传文件  MultipartFile
        //获取文件的内容
        try {
            InputStream is = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }

        //获取原始文件名
        String originalFilename = file.getOriginalFilename();
        //System.out.println(originalFilename);
        //生成一个uuid名称
        String uuidFilename = UploadUtils.getUUIDName(originalFilename);
        //创建新的文件
        File newFile = new File("D:/",uuidFilename);
        //将文件输出到目标的文件中
        try {
            file.transferTo(newFile);
            int i = readXlsService.readXls("D:/" + uuidFilename);
            newFile.delete();
            if(i == -1){
                return toAjaxStant(i);
            }
            return toAjax(i);
        } catch (IOException e) {
            e.printStackTrace();
        }

       return toAjax(0);
    }

7. 页面代码如下:

<!DOCTYPE html>
<html lang="zh" xmlns:th="http://www.thymeleaf.org" xmlns:shiro="http://www.pollix.at/thymeleaf/shiro"
      xmlns:loding="http://www.w3.org/1999/xhtml">
<head>
    <th:block th:include="include :: header('XXXX')"/>
</head>
<body class="gray-bg">
<style>
    button.btn.btn-success {
        margin-left: 1622px;
    }

    .fixed-table-toolbar {
        float: left;
    }

    .table-striped {
        min-height: 100%;
    }

    .nav>li>a:hover {
        background-color: #2e6da4;
    }

    .nav>li {
        border-radius: 4px;
    }
</style>
<div class="container-div">

    <ul class="nav nav-pills">
        <li role="presentation" style="background-color: #2e6da4"><a href="/readXls" style="color: #FFF">杆塔</a></li>
        <li role="presentation"><a href="/readXls/powerPage">变电站</a></li>
    </ul>
    <div class="btn-group-sm" id="toolbar" role="group">

        <form th:action="@{/readXls/importXls}" method="post" enctype="multipart/form-data" style="display: flex"
              id="formFile">
            <button type="button" class="btn btn-sm btn-primary" style="margin-right: 10px" onclick="submitFile()" id="subButton">
                导入杆塔
            </button>
            <button type="button" class="btn btn-danger" onclick="deletePole()">清空</button>
            <input type="file" name="file" id="FileUpload" style="width: 187px; margin-left:10px;margin-top: 5px; display: none" onchange="fileInp()">
            <button type="button" class="btn btn-success" onclick="location='/'">返回首页</button>
        </form>
    </div>

    <div class="row" style="height: 94%">
        <div class="col-sm-12 select-table table-striped" style="margin-top: 0">
            <table id="bootstrap-table"></table>
        </div>
    </div>
</div>
<div th:include="include :: footer"></div>
<script th:inline="javascript">
    var prefix = ctx + "readXls";
    var datas = [[${@dict.getType('sys_normal_disable')}]];

    $(function () {
        var options = {
            url: prefix + "/poleList",
            showFooter: true,
            showSearch: false,
            showRefresh: false,
            showToggle: false,
            showColumns: false,
            modalName: "杆塔信息",
            pageSize: 15, //每页的记录行数(*)
            pageList: [10, 15, 50, 100, 200], //可供选择的每页的行数(*)
            footerStyle: footerStyle,
            columns: [{
                checkbox: true
            },
                {
                    field: 'id',
                    title: '序号'
                },
                {
                    field: 'equipmentName',
                    title: '杆塔名称'
                },
                {
                    field: 'line',
                    title: '所属线路'
                },
                {
                    field: 'nature',
                    title: '杆塔性质'
                },
                {
                    field: 'towerTexture',
                    title: '杆塔材质'
                },
                {
                    field: 'macNumber',
                    title: '物理设备编码'
                },
                {
                    field: 'operation',
                    title: '运维单位'
                },
                {
                    field: 'maintain',
                    title: '运维班组'
                },
                {
                    field: 'voltageClasses',
                    title: '电压等级'
                },
                {
                    field: 'equipmentStatus',
                    title: '设备状态'
                },
                {
                    field: 'commissioningDate',
                    title: '投运日期'
                }
            ]
        };
        $.table.init(options);
    });

    function footerStyle(column) {
        return {
//         	    userBalance: {
//         	        classes: 'class'
//         	    },
            userBalance: {
                css: {color: 'red', 'font-weight': 'normal'}
            }
        }[column.field]
    }

    function deletePole() {
        $.modal.confirm("确认要清空杆塔数据吗?", function () {
            $.operate.post(prefix + "/deleteInfoByFlg", {"flg": 0});
        })
    }

    function submitFile() {
        $("#FileUpload").click()

    }

    function fileInp(){
        var file = new FormData(document.getElementById("formFile"));
        if (file != null || file != '') {
            $.ajax({
                url: prefix + "/importXls",
                type: "post",
                dataType: "json",
                data: file,
                processData: false,
                contentType: false,
                beforeSend: function () {
                    $.modal.loading("正在处理中,请稍后...");
                    $.modal.disable();
                },
                success: function (result) {
                    $.operate.ajaxSuccess(result);
                    $("#FileUpload")[0].value = null
                    if (result.code === 0){
                        //调用修改地址接口
                        $.ajax({
                            url: prefix + "/updateSitePole",
                            type: "post",
                            processData: false,
                            contentType: false,
                            beforeSend: function () {
                                $.modal.loading("正在处理中,请稍后...");
                                $.modal.disable();
                            },
                            success: function (result) {
                                $.operate.ajaxSuccess(result);
                                $("#FileUpload")[0].value = null
                            }
                        })
                    }
                }

            })
        }

    }

</script>
<style>

</style>
</body>
</html>

本章结束,如果有什么问题欢迎留言,可发源码! 

Logo

快速构建 Web 应用程序

更多推荐