Converting JSON data into Excel or CSV formats is a common requirement for structured reporting and business analysis. Aspose.Cells for .NET provides an efficient way to achieve this with its powerful API.

Introduction

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

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 metered = new Metered();
metered.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.

More in this category