前端应用Excel数据获取的创新可维护方案咨询

1. CI/CD-Automated Typed Static Asset Generation #

• Store the Excel file in a Git repo alongside your frontend code
• Set up a CI/CD pipeline (e.g., GitHub Actions, GitLab CI) that triggers whenever the Excel file is committed:
  • Use a CLI tool like xlsx-cli to convert the target sheet into a typed TypeScript file (instead of raw JSON)
  • Auto-generate TypeScript interfaces matching the Excel data structure to enforce type safety
  • Inject the generated file directly into your frontend build or commit it back to the repo
• Frontend imports the typed constants directly, eliminating runtime type errors and manual conversion steps
• Benefits: Fully automated updates, type safety, no extra backend services, audit trail of Excel changes via Git history

Example pipeline step (GitHub Actions snippet):

- name: Convert Excel to Typed TS
  run: |
    npx xlsx-cli constants.xlsx --output src/constants.ts --sheet "Q3 2024"
    # Add script to generate matching TypeScript interface here
- name: Commit generated file
  run: |
    git add src/constants.ts
    git commit -m "Auto-update constants from Excel [skip ci]"

2. Versioned Excel Source of Truth with Schema-Validated API #

• Host the Excel file in version-controlled storage (Git or object storage with versioning enabled)
• Build a lightweight backend service that:
  • Reads the latest (or user-specified) version of the Excel file
  • Validates data against a predefined schema (using tools like Zod or Joi) to catch formatting errors early
  • Exposes a versioned API endpoint (e.g., /api/constants/v2024q3) that returns structured JSON
• Frontend fetches the relevant version (or defaults to latest) and caches the response until the next quarter
• Add a webhook to notify the frontend when a new Excel version is available, triggering an auto-refresh
• Benefits: Centralized source of truth, schema validation prevents bad data from reaching the frontend, versioning allows easy rollbacks, clear audit trail of quarterly updates

3. Frontend-First Auto-Refreshing Parser with Cache & Validation #

• Host the Excel file on a static CDN with cache control headers set to expire after a quarter
• Build a frontend utility that:
  • Fetches the Excel file on app load (or on a scheduled interval)
  • Parses it using a lightweight library like sheetjs
  • Validates parsed data against a schema to ensure it matches the expected structure
  • Caches validated data in localStorage with a timestamp
  • Checks for updates by comparing the file's ETag or last-modified date on subsequent loads
• If the file has been updated, re-parse and refresh cached data; otherwise, use the cached version
• Benefits: No backend dependency, automated updates, cache reduces unnecessary network calls, validation ensures data integrity

Each solution addresses gaps in your existing approaches:

• The first eliminates manual work and adds type safety, reducing maintenance overhead
• The second adds robustness via validation and versioning, ideal for teams needing strict data governance
• The third keeps the stack lightweight and frontend-focused, perfect for smaller apps or teams without backend resources

内容的提问来源于stack exchange，提问作者K Sivaram