通过url读取excel信息

1.pom

---

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

2.代码

package mq.demo.ibm.url;

import com.alibaba.fastjson.JSONObject;
import mq.demo.common.PhoneFormatCheckUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

/**
 * @program: mqdemo
 * @description:
 * @author: shenning
 * @create: 2020-08-17 17:26
 */
public class excelmap {


    public static List<JSONObject> readExcel(URL url) throws IOException {
        // 返回HttpURLConnection 对象
        URLConnection conn = url.openConnection();
        BufferedInputStream bis = null;
        bis = new BufferedInputStream(conn.getInputStream());

        //判断格式是否为excel文件
        //url.getPath得到文件的路径
        Workbook workbook = null;
        if (url.getPath().endsWith("xls")) {
            workbook = new HSSFWorkbook(bis);
        } else if (url.getPath().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(bis);
        } else {
            System.out.println("文件格式错误,请检查文件格式！");
        }
        //workbook.getNumberOfSheets() 总共多少页
        
        //第一页
        Sheet sheet = workbook.getSheetAt(0);

        List<JSONObject> list = new ArrayList<JSONObject>();
        JSONObject inserJson =null;
        //总共有多少行
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        for (int q = 1, w = physicalNumberOfRows; q < w; q++) {
            //第一行
            Row row = sheet.getRow(q);
            //第一行第一个
            int start = row.getFirstCellNum();
            //第一行最后一个
            int end = row.getLastCellNum();
            inserJson= new JSONObject();
            for (int i = start; i < end; i++) {
                Cell cell = row.getCell(i);
                cell.setCellType(CellType.STRING);
                switch (i) {
                    case 0:
                        //输入放入到json里
                        System.out.println(cell.getStringCellValue());
                        break;
                    case 1:
                        System.out.println(cell.getStringCellValue());
                        break;
                    case 2:
                        System.out.println(cell.getStringCellValue());
                        break;
                    case 3:
                        System.out.println(cell.getStringCellValue());
                        break;
                }
            }
            list.add(inserJson);
        }
        workbook.close();
        bis.close();
        return list;
    }

    public static void main(String[] args) throws IOException {
        URL url = new URL("http://172.10.103.61:15000/apis/file/dl/source/5f3a1c6b66e0dd7e6251ecd8.xlsx");
        readExcel(url);
    }

}

后续如果有更好的方法，我会更换的。欢迎留言