Node.js中JSON转Xlsx及终端下载Excel文件方法求助

Hey there! Let's break down your two Node.js tasks step by step—these are super common workflows, so I’ve got practical, tested solutions for you.

The most reliable library for this job is xlsx (from SheetJS). It handles all the heavy lifting of converting JSON to Excel sheets and saving the file. Here's how to do it:

• Step 1: Install the library
  Open your terminal and run:

  npm install xlsx
  

• Step 2: Write the conversion code
  Create a script (e.g., json-to-xlsx.js) with this code:

  const XLSX = require('xlsx');
  
  // Sample JSON data (replace this with your actual data)
  const jsonData = [
    { name: 'Alice', age: 30, city: 'New York' },
    { name: 'Bob', age: 25, city: 'London' },
    { name: 'Charlie', age: 35, city: 'Paris' }
  ];
  
  // Convert JSON to a worksheet
  const worksheet = XLSX.utils.json_to_sheet(jsonData);
  
  // Create a workbook and add the worksheet
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Users'); // 'Users' is the sheet name
  
  // Save the workbook as an Excel file
  XLSX.writeFile(workbook, 'output.xlsx');
  
  console.log('JSON converted to Excel successfully! Check output.xlsx');
  

• Step 3: Run the script
  Execute it in your terminal:

  node json-to-xlsx.js
  

  You’ll find output.xlsx in your project folder with all your JSON data neatly formatted.

This usually involves two parts: fetching JSON data (from a remote API or local file) and converting it to Excel, then saving it locally. Let’s cover both scenarios:

Scenario 1: Download JSON from an API and convert to Excel #

We’ll use axios to fetch the remote JSON, plus xlsx for conversion.

• Step 1: Install dependencies

  npm install xlsx axios
  

• Step 2: Write the script (e.g., fetch-and-convert.js)

  const XLSX = require('xlsx');
  const axios = require('axios');
  
  async function fetchJsonAndConvertToExcel() {
    try {
      // Fetch JSON data from a remote API (replace with your API URL)
      const response = await axios.get('https://api.example.com/users');
      const jsonData = response.data;
  
      // Convert JSON to worksheet
      const worksheet = XLSX.utils.json_to_sheet(jsonData);
  
      // Create workbook and add sheet
      const workbook = XLSX.utils.book_new();
      XLSX.utils.book_append_sheet(workbook, worksheet, 'Fetched Users');
  
      // Save the Excel file
      const fileName = 'fetched-users.xlsx';
      XLSX.writeFile(workbook, fileName);
  
      console.log(`Success! Downloaded JSON and converted to ${fileName}`);
    } catch (error) {
      console.error('Error fetching data or converting to Excel:', error.message);
    }
  }
  
  // Run the function
  fetchJsonAndConvertToExcel();
  

• Step 3: Execute the script

  node fetch-and-convert.js
  

  The script will pull the JSON data, convert it, and save the Excel file right in your project directory.

Scenario 2: Convert a local JSON file to Excel (via terminal) #

If you already have a JSON file (e.g., data.json), you can read it directly and convert:

• Step 1: Ensure xlsx is installed
  (If you didn’t install it earlier, run npm install xlsx)

• Step 2: Create the script (e.g., local-json-to-xlsx.js)

  const XLSX = require('xlsx');
  const fs = require('fs');
  
  // Read local JSON file
  const jsonData = JSON.parse(fs.readFileSync('data.json', 'utf8'));
  
  // Convert to worksheet and workbook
  const worksheet = XLSX.utils.json_to_sheet(jsonData);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Local Data');
  
  // Save Excel file
  XLSX.writeFile(workbook, 'local-data.xlsx');
  
  console.log('Local JSON converted to Excel successfully!');
  

• Step 3: Run the script
  Make sure data.json is in the same folder, then run:

  node local-json-to-xlsx.js
  

内容的提问来源于stack exchange，提问作者Nasir Benali