C# Create Excel File Tutorial

by Jonas Schmidt

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.

Create & Save Excel File
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")
Jump to Article
Set Cell Value Manually
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"
Jump to Article
Set Cell Value Dynamically
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 i
Jump to Article
Add Cells from 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
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 table
Jump to Article
Set Cell Background Color
sheet["A1:L1"].Style.SetBackgroundColor("#d3d3d3");
sheet("A1:L1").Style.SetBackgroundColor("#d3d3d3")
Jump to Article
Create Borders
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
Jump to Article
Use Formulas in Cells
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 = min
Jump to Article
Set Worksheet Properties
sheet.ProtectSheet("Password");
sheet.CreateFreezePane(0, 1);
sheet.ProtectSheet("Password")
sheet.CreateFreezePane(0, 1)
Jump to Article
Page & Print Properties
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
Jump to Article
Save Workbook
workbook.SaveAs("Budget.xlsx");
workbook.SaveAs("Budget.xlsx")
Jump to Article
Try IronXL free for development


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

https://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")
VB   C#


Step 1

1. Download the FREE IronXL C# Library

C# PDF DLL

Download DLL

Download DLL

Manually install into your project

or
C# Nuget Library for PDF

Install with NuGet

Install-Package IronXL.Excel

Install by Using NuGet

There are three different ways to install the IronXL NuGet package:

  1. Visual Studio
  2. Developer Command Prompt
  3. 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 3Project menu

Figure 4Right 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 5Install IronXL.Excel NuGet Package

Developer Command Prompt

Open the Developer Command Prompt and follow these steps to install the IronXL.Excel NuGet package:

  1. Search for your Developer Command Prompt – it is usually under your Visual Studio folder
  2. Type in the following command:
  3. PM > Install-Package IronXL.Excel
  4. Press Enter
  5. The package will be installed.
  6. Reload your Visual Studio project

Download the NuGet Package directly

In order to download the NuGet package, make use of the next few steps:

  1. Navigate to the following URL: https://www.nuget.org/packages/ironxl.excel/
  2. Click on Download Package
  3. After the package has downloaded, double click it
  4. 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 6Download IronXL library

Reference the Library in your project by using the next steps:

  1. Right click the Solution in the Solution Explorer
  2. Select References
  3. Browse for the IronXL.dll library
  4. 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

Make use of the following steps to create an ASP.NET Website

  1. Open Visual Studio
  2. Click File > New Project
  3. Select Web under Visual C# in the Project type listbox
  4. Select ASP.NET Web Application, as shown next

    Figure 1New Project

ironsoftware.com/csharp/excel/

  1. Click OK
  2. On the next screen, select Web Forms as shown in Figure 2 underneath

Figure 2Web Forms

  1. 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)
VB   C#

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")
VB   C#

"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"
VB   C#

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 i
VB   C#

Every 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 table
VB   C#

You 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")
VB   C#

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.Medium
VB   C#

In 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 = min
VB   C#

What’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)
VB   C#

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 7Freeze Panes

Figure 8Protected 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.A4
VB   C#

The Print area gets set to A1 to L12. The Orientation gets set to Landscape and the paper size gets set to A4

Figure 9Print Setup


8. Save Workbook

To save the Workbook, use the following code:

workbook.SaveAs("Budget.xlsx");
workbook.SaveAs("Budget.xlsx");
workbook.SaveAs("Budget.xlsx")
VB   C#


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.

Download

Explore 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 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.

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.