How to Create a New Excel Spreadsheet from Column and Row Data in C# .NET Framework

Creating blank Excel documents from code expedites the process of programmatically filling out spreadsheets. With a low-code API, we can take that process one step further and start our new spreadsheets with pre-set data.

Using the ready-to-run code examples further down the page, we can take advantage of a free API that generates new Excel spreadsheets with pre-set column and row data of our choosing. We can name our worksheet and specify input data using the following request parameters:

{
  "WorksheetName": "string",
  "Rows": [
    {
      "Path": "string",
      "Cells": [
        {
          "Path": "string",
          "TextValue": "string",
          "CellIdentifier": "string",
          "StyleIndex": 0,
          "Formula": "string"
        }
      ]
    }
  ]
}

This operation will return a temporary editing URL we can use to chain additional programmatic changes to the document. We can return the contents of our fully formed file by calling a separate API to process the temporary URL.

To authorize our API calls, we’ll need a free API key, which will allow us to make up to 800 API calls per month with no additional commitments (our total will reset the following month once we reach it).

To structure our API call, let’s start by installing the .NET SDK. We can install via NuGet by running the below command in our Package Manager console:

Install-Package Cloudmersive.APIClient.NET.DocumentAndDataConvert -Version 3.4.2

To call the function that generates our custom spreadsheet, let’s copy the below code into our file and format our request parameters like the earlier example:

using System;
using System.Diagnostics;
using Cloudmersive.APIClient.NET.DocumentAndDataConvert.Api;
using Cloudmersive.APIClient.NET.DocumentAndDataConvert.Client;
using Cloudmersive.APIClient.NET.DocumentAndDataConvert.Model;

namespace Example
{
    public class EditDocumentXlsxCreateSpreadsheetFromDataExample
    {
        public void main()
        {
            // Configure API key authorization: Apikey
            Configuration.Default.AddApiKey("Apikey", "YOUR_API_KEY");

            var apiInstance = new EditDocumentApi();
            var input = new CreateSpreadsheetFromDataRequest(); // CreateSpreadsheetFromDataRequest | Document input request

            try
            {
                // Create a new Excel XLSX spreadsheet from column and row data
                CreateSpreadsheetFromDataResponse result = apiInstance.EditDocumentXlsxCreateSpreadsheetFromData(input);
                Debug.WriteLine(result);
            }
            catch (Exception e)
            {
                Debug.Print("Exception when calling EditDocumentApi.EditDocumentXlsxCreateSpreadsheetFromData: " + e.Message );
            }
        }
    }
}

When we receive our temporary document editing URL, we can convert that to a fully formed Excel document by calling this function:

using System;
using System.Diagnostics;
using Cloudmersive.APIClient.NET.DocumentAndDataConvert.Api;
using Cloudmersive.APIClient.NET.DocumentAndDataConvert.Client;
using Cloudmersive.APIClient.NET.DocumentAndDataConvert.Model;

namespace Example
{
    public class EditDocumentFinishEditingExample
    {
        public void main()
        {
            // Configure API key authorization: Apikey
            Configuration.Default.AddApiKey("Apikey", "YOUR_API_KEY");

            var apiInstance = new EditDocumentApi();
            var reqConfig = new FinishEditingRequest(); // FinishEditingRequest | Cloudmersive Document URL to complete editing on

            try
            {
                // Finish editing document, and download result from document editing
                byte[] result = apiInstance.EditDocumentFinishEditing(reqConfig);
                Debug.WriteLine(result);
            }
            catch (Exception e)
            {
                Debug.Print("Exception when calling EditDocumentApi.EditDocumentFinishEditing: " + e.Message );
            }
        }
    }
}

We can authorize both API calls with the same API key. Just like that, we have a simple two-step process for generating new & customized Excel files with code.