xlsx2json2file

最新推荐文章于 2024-09-15 07:06:32 发布

張萠飛

最新推荐文章于 2024-09-15 07:06:32 发布

阅读量254

点赞数

分类专栏： Java 文章标签： xlsx2json

本文链接：https://blog.csdn.net/zpf_940810653842/article/details/107690976

版权

Java 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

使用 POI 实现解析 xlsx 转换成 json 写入文件

Maven 依赖

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

    <!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-databind -->
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.9.3</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-core -->
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-core</artifactId>
      <version>2.9.3</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-annotations -->
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-annotations</artifactId>
      <version>2.9.3</version>
    </dependency>

public static void main(String[] args) throws IOException {
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        List<Map<String, String>> list = null;
        String cellData = null;
        String filePath = "F:\\workSpace\\IdeaProjects\\c_working\\asiainfo\\eda\\excel2json\\src\\main\\java\\a.xlsx";
        String[] columns = {"channel_name", "polygon_id", "point_order", "lng", "lat"};
        wb = readExcel(filePath);
        if (wb != null) {
            //用来存放表中数据
            list = new ArrayList<>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            // String initName = sheet.getRow(1).getCell(0).getStringCellValue() + "-" + new Double(sheet.getRow(1).getCell(1).getNumericCellValue()).intValue() + "";
            String initName = "";
            ArrayList<Object> list3 = new ArrayList<>();
            ArrayList<Double> list4 = null;
            for (int i = 1; i < rownum; i++) {
                list4 = new ArrayList<>();
                row = sheet.getRow(i);
                String name = row.getCell(0).getStringCellValue() + "-" + new Double(row.getCell(1).getNumericCellValue()).intValue() + "";
                double lng = row.getCell(3).getNumericCellValue();
                double lat = row.getCell(4).getNumericCellValue();
                System.out.println(initName + "?" + name);
                list4.add(lng);
                list4.add(lat);

                if ("".equals(initName)) {
                    initName = name;
                }
                if (!initName.equals(name)) {
                    System.out.println("\r\n");
                    // json 第一层
                    Map<String, Object> fmap = new HashMap<>(16);
                    ArrayList<Object> features = new ArrayList<>();
                    fmap.put("type", "FeatureCollection");

                    Map<String, Object> propertiesMap = new HashMap<>(16);
                    propertiesMap.put("name", "urn:ogc:def:crs:OGC:1.3:CRS84");
                    Map<String, Object> crsMap = new HashMap<>(16);
                    crsMap.put("type", "name");
                    crsMap.put("properties", propertiesMap);
                    fmap.put("crs", crsMap);

                    Map<String, Object> featureMap = new HashMap<>(16);
                    featureMap.put("type", "Feature");
                    Map<String, Object> fpMap = new HashMap<>(1);

                    Map<String, Object> geometryMap = new HashMap<>(16);
                    geometryMap.put("type", "MultiPolygon");

                    fmap.put("name", initName);
                    fpMap.put("NAME", initName);
                    featureMap.put("properties", fpMap);
                    ArrayList<Object> list1 = new ArrayList<>();
                    ArrayList<Object> list2 = new ArrayList<>();

                    list3.add(list3.get(0));
                    list2.add(list3);
                    list1.add(list2);

                    geometryMap.put("coordinates", list1);
                    featureMap.put("geometry", geometryMap);

                    System.out.println("换下个文件写出");
                    features.add(featureMap);
                    fmap.put("features", features);
                    ObjectMapper mapper = new ObjectMapper();
                    String s = mapper.writerWithDefaultPrettyPrinter().writeValueAsString(fmap);
                    System.out.println(initName + ":" + list3.size());
                    list3.clear();
                    list3.add(list4);

                    File file = new File(initName + ".geojson");
                    if (!file.exists()) {
                        file.createNewFile();
                    }
                    //使用true，即进行append file
                    FileWriter fileWritter = new FileWriter(file.getName(), false);
                    fileWritter.write(s);
                    fileWritter.close();
                    initName = name;

                }else{
                    list3.add(list4);
                }
            }
        }
    }

    //读取excel
    public static Workbook readExcel(String filePath) {
        Workbook wb = null;
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                return wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return wb = new XSSFWorkbook(is);
            } else {
                return wb = null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }