如何用Excel数据自动替换GEOJSON指定字段值？

Got it, let's walk through how to batch update those ivl_a, ivl_f, and differentiel fields in your GeoJSON using your Excel data. This solution uses Python since it’s perfect for handling both Excel spreadsheets and JSON data easily.

First, make sure your Excel sheet has a clear structure that links each row to the corresponding Feature in your GeoJSON. You’ll need:

• A name column that exactly matches the name value in each GeoJSON Feature’s properties (case, spaces, and special characters must be identical—this is how we’ll match rows to Features)
• Three columns for the new values: ivl_a, ivl_f, and differentiel

Example Excel structure:

We’ll use two popular Python libraries: pandas for reading Excel data, and Python’s built-in json module for handling the GeoJSON.

First, install the required library #

Open your terminal/command prompt and run:

pip install pandas openpyxl

(The openpyxl library lets pandas read .xlsx files.)

Then, run this script #

Save the code below as update_geojson.py, then update the file paths to match your own files:

import pandas as pd
import json

# 1. Load your Excel data
# Replace "your_excel_file.xlsx" with your actual file path, and "Sheet1" if your data is on a different sheet
excel_df = pd.read_excel("your_excel_file.xlsx", sheet_name="Sheet1")

# Convert Excel data into a lookup dictionary (key = name, value = the three fields to update)
update_lookup = excel_df.set_index("name")[["ivl_a", "ivl_f", "differentiel"]].to_dict("index")

# 2. Load your original GeoJSON
with open("original_geojson.geojson", "r", encoding="utf-8") as geojson_file:
    geojson_data = json.load(geojson_file)

# 3. Loop through each Feature and update the fields
for feature in geojson_data["features"]:
    feature_name = feature["properties"]["name"]
    
    # Check if we have matching data for this Feature
    if feature_name in update_lookup:
        new_values = update_lookup[feature_name]
        # Replace the old values with the new ones from Excel
        feature["properties"]["ivl_a"] = new_values["ivl_a"]
        feature["properties"]["ivl_f"] = new_values["ivl_f"]
        feature["properties"]["differentiel"] = new_values["differentiel"]
    else:
        # Print a warning if no match is found (helps with debugging)
        print(f"Warning: No Excel data found for Feature named '{feature_name}'")

# 4. Save the updated GeoJSON to a new file (so we don't overwrite the original)
with open("updated_geojson.geojson", "w", encoding="utf-8") as output_file:
    json.dump(geojson_data, output_file, ensure_ascii=False, indent=2)

• Matching issues: If you see warnings about missing matches, double-check that the name values in Excel and GeoJSON are 100% identical (even extra spaces or capitalization differences will break the match)
• Data types: Ensure the values in your Excel ivl_a, ivl_f, and differentiel columns are numbers (not text) if your GeoJSON expects numeric values
• Backup first: Always keep a copy of your original GeoJSON file before running the script—just in case!

内容的提问来源于stack exchange，提问作者John Straight