Write Data to Excel Sheets in C#

Excel is a widely used spreadsheet software for managing, processing, analyzing, and presenting data. Its comprehensive features allow users to handle vast amounts of data with ease, offering functionalities that range from basic calculations to complex data analysis, making it one of the popular tools in various fields.

In this article, we will share the most basic operations performed in Excel using a free .NET library — writing data to Excel, including text, numeric values, arrays, and DataTable.

• Write Text and Numeric Values to Excel Cells in C#
• Write Arrays to an Excel Worksheet in C#
• Write DataTable to an Excel Worksheet in C#

Free .NET Library for Processing Excel

Free Spire.XLS for .NET is a free Excel library that supports performing various Excel manipulation functions in .NET (C#, VB.NET, ASP.NET, .NET Core) applications. Check below for its supported features and limitations:

Free .NET Excel API - Developing Excel in C#, VB.NET, ASP.NET

Installation: To begin with, we can search for “FreeSpire.XLS” in Visual Studio > NuGet Package Manager and click “Install” to import the library. Alternatively, we can download it via this link and then manually add the dlls as reference.

Write Text and Numeric Values to Excel Cells in C#

1. Create a Workbook object;
2. Access the specified worksheet;
3. Write data to the specified cell:

• Write text: Worksheet.Range[].Text or Worksheet.Range[].Value property;
• Write numbers: Worksheet.Range[].Value2 property;

4. Save the Excel file.

C# code:

using Spire.Xls;

namespace WriteDataToCells
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create an Excel workbook
            Workbook workbook = new Workbook();

            //Access to the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            //Write data to specified cells
            worksheet.Range["A1"].Text = "ID";
            worksheet.Range["B1"].Text = "Name";
            worksheet.Range["C1"].Value = "Age";
            worksheet.Range["D1"].Value = "Program";

            worksheet.Range["A2"].Value = "ST38-001";
            worksheet.Range["B2"].Value = "Mike G.";
            worksheet.Range["C2"].Value2 = 19;
            worksheet.Range["D2"].Value = "Science";

            worksheet.Range["A3"].Value = "ST38-002";
            worksheet.Range["B3"].Value = "John L.";
            worksheet.Range["C3"].Value2 = 21;
            worksheet.Range["D3"].Value = "Business";

            worksheet.Range["A4"].Value = "ST38-003";
            worksheet.Range["B4"].Value = "Russell S.";
            worksheet.Range["C4"].Value2 = 18;
            worksheet.Range["D4"].Value = "Arts";

            //Set font style
            worksheet.Range["A1:D1"].Style.Font.IsBold = true;

            //Save to Excel file
            workbook.SaveToFile("WriteDataToCells.xlsx", ExcelVersion.Version2016);
        }
    }
}

Output:

C# Write Data to Excel

Write Arrays to an Excel Worksheet in C#

1. Creates a Workbook object;
2. Access the specified worksheet;
3. Define a two-dimensional array;
4. Write the two-dimensional array to the specified location in the worksheet through the Worksheet.InsertArray(Object\[,\] objectArray, int firstRow, int firstColumn) method;
5. Save the Excel file.

C# code:

using Spire.Xls;

namespace WriteArraysToSheet
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create an Excel workbook
            Workbook workbook = new Workbook();

            //Access to the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            //Create a two-dimensional array
            string[,] twoDimensionalArray = new string[,]{

                {"Date", "Emp Id", "Name", "Depart.", "Email"},
                {"2020/9/11", "3021", "Lary", "Finance", "Lary@example.com"},
                {"2020/9/28", "3022", "Mohan", "Tech", "Mohan@example.com"},
                {"2020/10/9", "3023", "Jessica", "Purchaing", "Jessica@example.com"},
                {"2020/12/6", "3024", "Tammy", "Purchaing", "Tammy@example.com" }
            };

            //Write array to specified position in worksheet
            worksheet.InsertArray(twoDimensionalArray, 1, 1);

            //Set font style
            worksheet.Range["A1:E1"].Style.Font.IsBold = true;

            //Save to Excel file
            workbook.SaveToFile("WriteArrayToSheet.xlsx", ExcelVersion.Version2016);
        }
    }
}

Output:

C# Write Array to Excel

Write DataTable to an Excel Worksheet in C#

1. Creates a Workbook object;
2. Access the specified worksheet;
3. Create a DataTable object and add data;
4. Write the DataTable to the specified location in the worksheet through the Worksheet.InsertDataTable(DataTable dataTable,bool columnHeaders, int firstRow, int firstColumn, bool transTypes) method;
5. Save the Excel file.

C# code:

using System;
using System.Data;
using System.Drawing;
using Spire.Xls;

namespace WriteDataTableToSheet
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create an Excel workbook
            Workbook workbook = new Workbook();

            //Access to the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            //Create DataTable object
            DataTable dataTable = new DataTable();

            //Create rows and add data
            dataTable.Columns.Add("SKU", typeof(Int32));
            dataTable.Columns.Add("Product", typeof(String));
            dataTable.Columns.Add("Price ($)", typeof(Decimal));

            DataRow dr = dataTable.NewRow();
            dr[0] = 11060015;
            dr[1] = "Data Cable";
            dr[2] = "8.5";
            dataTable.Rows.Add(dr);

            dr = dataTable.NewRow();
            dr[0] = 11060037;
            dr[1] = "Mouse";
            dr[2] = "20.9";
            dataTable.Rows.Add(dr);

            dr = dataTable.NewRow();
            dr[0] = 11060021;
            dr[1] = "Keybord";
            dr[2] = "39.8";
            dataTable.Rows.Add(dr);

            //Write datatable to specified position in worksheet
            worksheet.InsertDataTable(dataTable, true, 1, 1, true);

            //Set font style
            worksheet.Range["A1:D1"].Style.Font.IsBold = true;

            //Save to Excel file
            workbook.SaveToFile("WriteDataTableToSheet.xlsx", ExcelVersion.Version2016);
        }
    }
}

Output:

C# Import DataTable to Excel

With the three examples provided above, you can write different types of data to an Excel worksheet with C#.

If you need to further analyze the data in Excel, or insert charts and other elements, you can click here to see a tutorial on the features provided by the free Spire.XLS library:

Spire.XLS for .NET Program Guide Content

API reference: https://www.e-iceblue.com/apireference/net/Spire.XLS/html/N_Spire_Xls.htm