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:
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#
- Create a Workbook object;
- Access the specified worksheet;
- 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:
Write Arrays to an Excel Worksheet in C#
- Creates a Workbook object;
- Access the specified worksheet;
- Define a two-dimensional array;
- Write the two-dimensional array to the specified location in the worksheet through the Worksheet.InsertArray(Object\[,\] objectArray, int firstRow, int firstColumn) method;
- 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:
Write DataTable to an Excel Worksheet in C#
- Creates a Workbook object;
- Access the specified worksheet;
- Create a DataTable object and add data;
- 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;
- 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:
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:
API reference: https://www.e-iceblue.com/apireference/net/Spire.XLS/html/N_Spire_Xls.htm