C# Create Excel File Tutorial
This tutorial will guide you step-by-step on how to create an Excel Workbook file on any platform that supports .NET Framework 4.5 or .NET Core. Creating Excel files in C# can be simple, even without dependency on the legacy Microsoft.Office.Interop.Excel library. Use IronXL to set worksheet properties like freeze panes and protection, set print properties, and more.
How To Create Excel Files in C# .NET:
Overview
IronXL Creates C# Excel Files in .NET
IronXL is an intuitive C# & VB Excel API that allows you to read, edit & create Excel spreadsheet files in .NET with lightning fast performance. There is no need to install MS Office or even the Excel InterOp.
IronXL fully supports .NET Core, .NET Framework, Xamarin, Mobile, Linux, MacOS and Azure.
IronXL Features:
- Human support directly from our .NET development team
- Rapid installation with Microsoft Visual Studio
- FREE for development. Licenses from $399.
Create and Save an Excel File: Quick Code
www.nuget.org/packages/IronXL.Excel/
As an alternative, the IronXL.Dll can be downloaded and added to your project.
using IronXL;
//default file format is XLSX, we can override it using CreatingOptions
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var sheet = workbook.CreateWorkSheet("example_sheet");
sheet["A1"].Value = "Example";
//set value to multiple cells
sheet["A2:A4"].Value = 5;
sheet["A5"].Style.SetBackgroundColor("#f0f0f0");
//set style to multiple cells
sheet["A5:A6"].Style.Font.Bold = true;
//set formula
sheet["A6"].Value = "=SUM(A2:A4)";
if (sheet["A6"].IntValue == sheet["A2:A4"].IntValue)
{
Console.WriteLine("Basic test passed");
}
workbook.SaveAs("example_workbook.xlsx");using IronXL;
//default file format is XLSX, we can override it using CreatingOptions
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var sheet = workbook.CreateWorkSheet("example_sheet");
sheet["A1"].Value = "Example";
//set value to multiple cells
sheet["A2:A4"].Value = 5;
sheet["A5"].Style.SetBackgroundColor("#f0f0f0");
//set style to multiple cells
sheet["A5:A6"].Style.Font.Bold = true;
//set formula
sheet["A6"].Value = "=SUM(A2:A4)";
if (sheet["A6"].IntValue == sheet["A2:A4"].IntValue)
{
Console.WriteLine("Basic test passed");
}
workbook.SaveAs("example_workbook.xlsx");Imports IronXL
'default file format is XLSX, we can override it using CreatingOptions
Private workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Private sheet = workbook.CreateWorkSheet("example_sheet")
Private sheet("A1").Value = "Example"
'set value to multiple cells
Private sheet("A2:A4").Value = 5
sheet("A5").Style.SetBackgroundColor("#f0f0f0")
'set style to multiple cells
sheet("A5:A6").Style.Font.Bold = True
'set formula
sheet("A6").Value = "=SUM(A2:A4)"
If sheet("A6").IntValue = sheet("A2:A4").IntValue Then
Console.WriteLine("Basic test passed")
End If
workbook.SaveAs("example_workbook.xlsx")Step 1
1. Download the FREE IronXL C# Library
Install with NuGet
Install-Package IronXL.Excel
Install by Using NuGet
There are three different ways to install the IronXL NuGet package:
- Visual Studio
- Developer Command Prompt
- Download the NuGet Package directly
Visual Studio
Visual Studio provides the NuGet Package Manager for you to use to install NuGet packages in your projects. You can access it via the Project Menu, or by right clicking your project in the Solution Explorer. Both these options are shown below in Figures 3 and 4.
Figure 3 – Project menu
Figure 4 – Right click Solution Explorer
After you have clicked Manage NuGet Packages from either option, Browse for the IronXL.Excel package and install it as shown in Figure 5.
Figure 5 – Install IronXL.Excel NuGet Package
Developer Command Prompt
Open the Developer Command Prompt and follow these steps to install the IronXL.Excel NuGet package:
- Search for your Developer Command Prompt – it is usually under your Visual Studio folder
- Type in the following command:
- PM > Install-Package IronXL.Excel
- Press Enter
- The package will be installed.
- Reload your Visual Studio project
Download the NuGet Package directly
In order to download the NuGet package, make use of the next few steps:
- Navigate to the following URL: https://www.nuget.org/packages/ironxl.excel/
- Click on Download Package
- After the package has downloaded, double click it
- Reload your Visual Studio project
Install IronXL by Direct Download of the Library
The second way to install IronXL is by downloading it directly from the following URL: https://ironsoftware.com/csharp/excel/
Figure 6 – Download IronXL library
Reference the Library in your project by using the next steps:
- Right click the Solution in the Solution Explorer
- Select References
- Browse for the IronXL.dll library
- Click OK
Let's Go!
Now that you’re set up, we can start playing with the awesome features in the IronXL library!
How to Tutorials
2. Create an ASP.NET Project
- Navigate to the following URL:
- https://www.nuget.org/packages/ironxl.excel/
- Click on Download Package
- After the package has downloaded, double click it
- Reload your Visual Studio project
Make use of the following steps to create an ASP.NET Website
- Open Visual Studio
- Click File > New Project
- Select Web under Visual C# in the Project type listbox
- Select ASP.NET Web Application, as shown next
![]()
Figure 1 – New Project
ironsoftware.com/csharp/excel/
- Click OK
- On the next screen, select Web Forms as shown in Figure 2 underneath
Figure 2 – Web Forms
- Click OK
Now we have something to work with. Install IronXL to start customizing your file.
3. Create an Excel Workbook
It cannot be simpler to create a new Excel Workbook using IronXL! It is one line of code! Yes, really:
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)Both XLS (older Excel file version) and XLSX (current and newer file version) file formats can be created with IronXL.
3.1. Set a Default Worksheet
And, it’s even simpler to create a default Worksheet:
var sheet = workbook.CreateWorkSheet("2020 Budget");var sheet = workbook.CreateWorkSheet("2020 Budget");Dim sheet = workbook.CreateWorkSheet("2020 Budget")"Sheet" in the above code snippet represents the worksheet and you can use it to set cell values and almost everything Excel can do.
In case you are confused about the difference between a Workbook and a Worksheet, let me explain:
A Workbook contains Worksheets. This means that you can add as many Worksheets as you like into one Workbook. In a later article, I will explain how to do this. A Worksheet contains Rows and Columns. The intersection of a Row and a Column is called a Cell, and this is what you will manipulate whilst working with Excel.
4. Set Cell Values
4.1. Set Cell Values Manually
To set cell values manually, you simply indicate what cell you are working with, and set its value, as in the following example:
sheet["A1"].Value = "January";
sheet["B1"].Value = "February";
sheet["C1"].Value = "March";
sheet["D1"].Value = "April";
sheet["E1"].Value = "May";
sheet["F1"].Value = "June";
sheet["G1"].Value = "July";
sheet["H1"].Value = "August";
sheet["I1"].Value = "September";
sheet["J1"].Value = "October";
sheet["K1"].Value = "November";
sheet["L1"].Value = "December";sheet["A1"].Value = "January";
sheet["B1"].Value = "February";
sheet["C1"].Value = "March";
sheet["D1"].Value = "April";
sheet["E1"].Value = "May";
sheet["F1"].Value = "June";
sheet["G1"].Value = "July";
sheet["H1"].Value = "August";
sheet["I1"].Value = "September";
sheet["J1"].Value = "October";
sheet["K1"].Value = "November";
sheet["L1"].Value = "December";sheet("A1").Value = "January"
sheet("B1").Value = "February"
sheet("C1").Value = "March"
sheet("D1").Value = "April"
sheet("E1").Value = "May"
sheet("F1").Value = "June"
sheet("G1").Value = "July"
sheet("H1").Value = "August"
sheet("I1").Value = "September"
sheet("J1").Value = "October"
sheet("K1").Value = "November"
sheet("L1").Value = "December"Here, I have populated Columns A to L, and the first row of each to a name of a different month.
4.2. Set Cell Values Dynamically
Setting values dynamically is almost similar to the previous code segment. The nice thing about this is that you do not have to hard-code the cell location. In the next code example, you will create a new Random object to create random numbers, and then make use of a for loop to iterate through the range of cells you’d like to populate with values.
Random r = new Random();
for (int i = 2; i <= 11; i++)
{
sheet["A" + i].Value = r.Next(1, 1000);
sheet["B" + i].Value = r.Next(1000, 2000);
sheet["C" + i].Value = r.Next(2000, 3000);
sheet["D" + i].Value = r.Next(3000, 4000);
sheet["E" + i].Value = r.Next(4000, 5000);
sheet["F" + i].Value = r.Next(5000, 6000);
sheet["G" + i].Value = r.Next(6000, 7000);
sheet["H" + i].Value = r.Next(7000, 8000);
sheet["I" + i].Value = r.Next(8000, 9000);
sheet["J" + i].Value = r.Next(9000, 10000);
sheet["K" + i].Value = r.Next(10000, 11000);
sheet["L" + i].Value = r.Next(11000, 12000);
}Random r = new Random();
for (int i = 2; i <= 11; i++)
{
sheet["A" + i].Value = r.Next(1, 1000);
sheet["B" + i].Value = r.Next(1000, 2000);
sheet["C" + i].Value = r.Next(2000, 3000);
sheet["D" + i].Value = r.Next(3000, 4000);
sheet["E" + i].Value = r.Next(4000, 5000);
sheet["F" + i].Value = r.Next(5000, 6000);
sheet["G" + i].Value = r.Next(6000, 7000);
sheet["H" + i].Value = r.Next(7000, 8000);
sheet["I" + i].Value = r.Next(8000, 9000);
sheet["J" + i].Value = r.Next(9000, 10000);
sheet["K" + i].Value = r.Next(10000, 11000);
sheet["L" + i].Value = r.Next(11000, 12000);
}Dim r As New Random()
For i As Integer = 2 To 11
sheet("A" & i).Value = r.Next(1, 1000)
sheet("B" & i).Value = r.Next(1000, 2000)
sheet("C" & i).Value = r.Next(2000, 3000)
sheet("D" & i).Value = r.Next(3000, 4000)
sheet("E" & i).Value = r.Next(4000, 5000)
sheet("F" & i).Value = r.Next(5000, 6000)
sheet("G" & i).Value = r.Next(6000, 7000)
sheet("H" & i).Value = r.Next(7000, 8000)
sheet("I" & i).Value = r.Next(8000, 9000)
sheet("J" & i).Value = r.Next(9000, 10000)
sheet("K" & i).Value = r.Next(10000, 11000)
sheet("L" & i).Value = r.Next(11000, 12000)
Next iEvery cell from A2 to L11 contains a unique value that was randomly generated.
Talking about dynamic values, how about learning how to dynamically add data into cells directly from a database? The next code snippet quickly shows how this is done, assuming you have set up your database connections correctly.
4.3. Add Directly from a Database
//Create database objects to populate data from database
string contring;
string sql;
DataSet ds = new DataSet("DataSetName");
SqlConnection con;
SqlDataAdapter da;
//Set Database Connection string
contring = @"Data Source=Server_Name;Initial Catalog=Database_Name;User ID=User_ID;Password=Password";
//SQL Query to obtain data
sql = "SELECT Field_Names FROM Table_Name";
//Open Connection & Fill DataSet
con = new SqlConnection(contring);
da = new SqlDataAdapter(sql, con);
con.Open();
da.Fill(ds);
//Loop through contents of dataset
foreach (DataTable table in ds.Tables)
{
int Count = table.Rows.Count - 1;
for (int j = 12; j <= 21; j++)
{
sheet["A" + j].Value = table.Rows[Count]["Field_Name_1"].ToString();
sheet["B" + j].Value = table.Rows[Count]["Field_Name_2"].ToString();
sheet["C" + j].Value = table.Rows[Count]["Field_Name_3"].ToString();
sheet["D" + j].Value = table.Rows[Count]["Field_Name_4"].ToString();
sheet["E" + j].Value = table.Rows[Count]["Field_Name_5"].ToString();
sheet["F" + j].Value = table.Rows[Count]["Field_Name_6"].ToString();
sheet["G" + j].Value = table.Rows[Count]["Field_Name_7"].ToString();
sheet["H" + j].Value = table.Rows[Count]["Field_Name_8"].ToString();
sheet["I" + j].Value = table.Rows[Count]["Field_Name_9"].ToString();
sheet["J" + j].Value = table.Rows[Count]["Field_Name_10"].ToString();
sheet["K" + j].Value = table.Rows[Count]["Field_Name_11"].ToString();
sheet["L" + j].Value = table.Rows[Count]["Field_Name_12"].ToString();
}
Count++;
}//Create database objects to populate data from database
string contring;
string sql;
DataSet ds = new DataSet("DataSetName");
SqlConnection con;
SqlDataAdapter da;
//Set Database Connection string
contring = @"Data Source=Server_Name;Initial Catalog=Database_Name;User ID=User_ID;Password=Password";
//SQL Query to obtain data
sql = "SELECT Field_Names FROM Table_Name";
//Open Connection & Fill DataSet
con = new SqlConnection(contring);
da = new SqlDataAdapter(sql, con);
con.Open();
da.Fill(ds);
//Loop through contents of dataset
foreach (DataTable table in ds.Tables)
{
int Count = table.Rows.Count - 1;
for (int j = 12; j <= 21; j++)
{
sheet["A" + j].Value = table.Rows[Count]["Field_Name_1"].ToString();
sheet["B" + j].Value = table.Rows[Count]["Field_Name_2"].ToString();
sheet["C" + j].Value = table.Rows[Count]["Field_Name_3"].ToString();
sheet["D" + j].Value = table.Rows[Count]["Field_Name_4"].ToString();
sheet["E" + j].Value = table.Rows[Count]["Field_Name_5"].ToString();
sheet["F" + j].Value = table.Rows[Count]["Field_Name_6"].ToString();
sheet["G" + j].Value = table.Rows[Count]["Field_Name_7"].ToString();
sheet["H" + j].Value = table.Rows[Count]["Field_Name_8"].ToString();
sheet["I" + j].Value = table.Rows[Count]["Field_Name_9"].ToString();
sheet["J" + j].Value = table.Rows[Count]["Field_Name_10"].ToString();
sheet["K" + j].Value = table.Rows[Count]["Field_Name_11"].ToString();
sheet["L" + j].Value = table.Rows[Count]["Field_Name_12"].ToString();
}
Count++;
}'Create database objects to populate data from database
Dim contring As String
Dim sql As String
Dim ds As New DataSet("DataSetName")
Dim con As SqlConnection
Dim da As SqlDataAdapter
'Set Database Connection string
contring = "Data Source=Server_Name;Initial Catalog=Database_Name;User ID=User_ID;Password=Password"
'SQL Query to obtain data
sql = "SELECT Field_Names FROM Table_Name"
'Open Connection & Fill DataSet
con = New SqlConnection(contring)
da = New SqlDataAdapter(sql, con)
con.Open()
da.Fill(ds)
'Loop through contents of dataset
For Each table As DataTable In ds.Tables
Dim Count As Integer = table.Rows.Count - 1
For j As Integer = 12 To 21
sheet("A" & j).Value = table.Rows(Count)("Field_Name_1").ToString()
sheet("B" & j).Value = table.Rows(Count)("Field_Name_2").ToString()
sheet("C" & j).Value = table.Rows(Count)("Field_Name_3").ToString()
sheet("D" & j).Value = table.Rows(Count)("Field_Name_4").ToString()
sheet("E" & j).Value = table.Rows(Count)("Field_Name_5").ToString()
sheet("F" & j).Value = table.Rows(Count)("Field_Name_6").ToString()
sheet("G" & j).Value = table.Rows(Count)("Field_Name_7").ToString()
sheet("H" & j).Value = table.Rows(Count)("Field_Name_8").ToString()
sheet("I" & j).Value = table.Rows(Count)("Field_Name_9").ToString()
sheet("J" & j).Value = table.Rows(Count)("Field_Name_10").ToString()
sheet("K" & j).Value = table.Rows(Count)("Field_Name_11").ToString()
sheet("L" & j).Value = table.Rows(Count)("Field_Name_12").ToString()
Next j
Count += 1
Next tableYou simply have to set the Value property of the particular cell to the Field name to be entered into the cell.
5. Apply Formatting
5.1. Set Background Colors of Cells
To set the nackground color of a cell or a range of cells, you simply need a line of code that looks like the following:
sheet["A1:L1"].Style.SetBackgroundColor("#d3d3d3");sheet["A1:L1"].Style.SetBackgroundColor("#d3d3d3");sheet("A1:L1").Style.SetBackgroundColor("#d3d3d3")This sets the background color of the range of cells to gray. The color is in RGB (Red, Green, Blue) format where the first two characters represent Red, the next two, Green and the last two, Blue. The values range from 0 to 9, then A to F (Hexidecimal).
5.2. Create Borders
Creating borders with IronXL is very simple, as shown next:
sheet["A1:L1"].Style.TopBorder.SetColor("#000000");
sheet["A1:L1"].Style.BottomBorder.SetColor("#000000");
sheet["L2:L11"].Style.RightBorder.SetColor("#000000");
sheet["L2:L11"].Style.RightBorder.Type = IronXL.Styles.BorderType.Medium;
sheet["A11:L11"].Style.BottomBorder.SetColor("#000000");
sheet["A11:L11"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Medium;sheet["A1:L1"].Style.TopBorder.SetColor("#000000");
sheet["A1:L1"].Style.BottomBorder.SetColor("#000000");
sheet["L2:L11"].Style.RightBorder.SetColor("#000000");
sheet["L2:L11"].Style.RightBorder.Type = IronXL.Styles.BorderType.Medium;
sheet["A11:L11"].Style.BottomBorder.SetColor("#000000");
sheet["A11:L11"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Medium;sheet("A1:L1").Style.TopBorder.SetColor("#000000")
sheet("A1:L1").Style.BottomBorder.SetColor("#000000")
sheet("L2:L11").Style.RightBorder.SetColor("#000000")
sheet("L2:L11").Style.RightBorder.Type = IronXL.Styles.BorderType.Medium
sheet("A11:L11").Style.BottomBorder.SetColor("#000000")
sheet("A11:L11").Style.BottomBorder.Type = IronXL.Styles.BorderType.MediumIn the above code I have set black Top and Bottom borders to Cells A1 to L1, then i have set the Right border to cells L2 to L11 and the style of the border is set to Medium. Lastly, I have set the Bottom border for cells A11 to L11
6. Use Formulas in Cells
I keep saying that IronXL makes everything so easy, but it really does, and I can’t highlight it enough! The following code allows you to use formulas:
decimal sum = sheet["A2:A11"].Sum();
decimal avg = sheet["B2:B11"].Avg();
decimal max = sheet["C2:C11"].Max();
decimal min = sheet["D2:D11"].Min();
sheet["A12"].Value = sum;
sheet["B12"].Value = avg;
sheet["C12"].Value = max;
sheet["D12"].Value = min;decimal sum = sheet["A2:A11"].Sum();
decimal avg = sheet["B2:B11"].Avg();
decimal max = sheet["C2:C11"].Max();
decimal min = sheet["D2:D11"].Min();
sheet["A12"].Value = sum;
sheet["B12"].Value = avg;
sheet["C12"].Value = max;
sheet["D12"].Value = min;Dim sum As Decimal = sheet("A2:A11").Sum()
Dim avg As Decimal = sheet("B2:B11").Avg()
Dim max As Decimal = sheet("C2:C11").Max()
Dim min As Decimal = sheet("D2:D11").Min()
sheet("A12").Value = sum
sheet("B12").Value = avg
sheet("C12").Value = max
sheet("D12").Value = minWhat’s nice about this is the fact that you can set the data type of the cell thus the result of the formula. The above code shows how to use the SUM (sums values), AVG (averages values), MAX (gets the highest value) and MIN (gets the lowest value) formulas.
7. Set Worksheet and Print Properties
7.1. Set Worksheet Properties
Worksheet properties include freezing rows and columns and protecting the worksheet with a password. This is shown next:
sheet.ProtectSheet("Password");
sheet.CreateFreezePane(0, 1);sheet.ProtectSheet("Password");
sheet.CreateFreezePane(0, 1);sheet.ProtectSheet("Password")
sheet.CreateFreezePane(0, 1)The first row is frozen and will not scroll along with the rest of the Worksheet. The worksheet is also protected from any edits with a password. Figures 7 and 8 shows this in action.
Figure 7 – Freeze Panes
Figure 8 – Protected Worksheet
7.2. Set Page and Print Properties
You can set Page properties such as the Orientation of the page, the size of the page as well as the PrintArea to name a few.
sheet.SetPrintArea("A1:L12");
sheet.PrintSetup.PrintOrientation = IronXL.Printing.PrintOrientation.Landscape;
sheet.PrintSetup.PaperSize = IronXL.Printing.PaperSize.A4;sheet.SetPrintArea("A1:L12");
sheet.PrintSetup.PrintOrientation = IronXL.Printing.PrintOrientation.Landscape;
sheet.PrintSetup.PaperSize = IronXL.Printing.PaperSize.A4;sheet.SetPrintArea("A1:L12")
sheet.PrintSetup.PrintOrientation = IronXL.Printing.PrintOrientation.Landscape
sheet.PrintSetup.PaperSize = IronXL.Printing.PaperSize.A4The Print area gets set to A1 to L12. The Orientation gets set to Landscape and the paper size gets set to A4
Figure 9 – Print Setup
8. Save Workbook
To save the Workbook, use the following code:
workbook.SaveAs("Budget.xlsx");workbook.SaveAs("Budget.xlsx");workbook.SaveAs("Budget.xlsx")Tutorial Quick Access
Download this Tutorial as C# Source Code
The full free C# for Excel Source Code for this tutorial is available to download as a zipped Visual Studio 2017 project file.
DownloadExplore this Tutorial on GitHub
The source code for this project is available in C# and VB.NET on GitHub.
Use this code as an easy way to get up and running in just a few minutes. The project is saved as a Microsoft Visual Studio 2017 project, but is compatible with any .NET IDE.
How to Create Excel File in C# on GitHub
Read the XL Object Reference
Explore the Object Reference for IronXL, outlining the details of all of IronXL’s features, namespaces, classes, methods fields and enums.
View the Object Reference
Jonas Schmidt
C# Developer
Jonas is a lead developer at a Munich .Net Software House. Jonas uses IronXL to drive the data exchange between a customer's accounting and sales pipeline excel documents. IronXL is becoming a common tool in many of Jonas' customer accounting system projects.
