JSON Converter
How to Convert JSON to Excel using C#

How to Convert JSON to Excel using C#

Converting JSON to Excel is essential for enabling structured data analysis and business reporting. Aspose.Cells for .NET provides powerful methods to import JSON directly into spreadsheets or CSV files using a simple and flexible API.

Why Convert JSON to Excel?

  1. Structured Reporting:
    • Turn API or service data in JSON into tabular formats for better readability.
  2. Automation:
    • Automate data import into Excel without manual formatting or intervention.
  3. Versatility:
    • Supports output as XLSX or CSV formats with layout customization.

Step-by-Step Guide to Convert JSON to Excel

Step 1: Install Aspose.Cells via NuGet

Install the Aspose.Cells library:

dotnet add package Aspose.Cells

Step 2: Configure Aspose.Cells License

Activate full functionality with your license:

Metered matered = new Metered();
matered.SetMeteredKey("PublicKey", "PrivateKey");

Step 3: Load JSON Input

Read JSON from a string or a .json file:

  • From file:
string jsonInput = File.ReadAllText("Data.json");
  • From string directly:
string jsonInput = "[{'nodeId':1,'reputation':1134},{'nodeId':2,'reputation':547}]";

Step 4: Create Workbook and Access Worksheet

Initialize a workbook and access its default worksheet:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

Step 5: Set JsonLayoutOptions

Configure layout options to treat arrays as tables:

JsonLayoutOptions options = new JsonLayoutOptions();
options.ArrayAsTable = true;

Step 6: Import JSON into Worksheet

Import JSON into the worksheet using the utility method:

JsonUtility.ImportData(jsonInput, worksheet.Cells, 0, 0, options);

Step 7: Save to Excel or CSV

Export the data to a desired format:

  • Save as Excel:
workbook.Save("output.xlsx");
  • Save as CSV:
workbook.Save("output.csv", SaveFormat.CSV);

Common Issues and Fixes

1. JSON Array Not Parsed Correctly

  • Solution: Ensure JsonLayoutOptions.ArrayAsTable is set to true to flatten arrays into rows.

2. File Not Saving

  • Solution: Check for valid paths and that your application has write permissions.

3. Licensing Warnings

  • Solution: Confirm that the license file is correctly referenced and loaded.
 English