The C# Excel Library

 Read Excel Files without Interop
using IronXL;
using System.Linq;
    
//Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.WorkSheets.First();

//Select cells easily in Excel notation and return the calculated value
int cellValue = sheet["A2"].IntValue;


// Read from Ranges of cells elegantly.
foreach (var cell in sheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

//Calculate aggregate values such as Min, Max and Sum
decimal sum = sheet["A2:A10"].Sum();


//Linq compatible
decimal max = sheet["A2:A10"].Max(c => c.DecimalValue);Imports IronXL
Imports System.Linq

'Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private sheet As WorkSheet = workbook.WorkSheets.First()

'Select cells easily in Excel notation and return the calculated value
Private cellValue As Integer = sheet("A2").IntValue


' Read from Ranges of cells elegantly.
For Each cell In sheet("A2:A10")
	Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
Next cell

'Calculate aggregate values such as Min, Max and Sum
Dim sum As Decimal = sheet("A2:A10").Sum()


'Linq compatible
Dim max As Decimal = sheet("A2:A10").Max(Function(c) c.DecimalValue)

 Excel Worksheets
using IronXL;

//This is how we create new worksheet
WorkBook workBook = WorkBook.Load("test.xlsx");

//This is how we create new worksheet
var newSheet = workBook.CreateWorkSheet("new_sheet");
//You can perform any operations you want with the new sheet.
newSheet["B2"].Value = DateTime.Now;


// Work with an existing Worksheets 
WorkSheet oldSheet = workbook.GetWorkSheet("sheet1");
//...

// Work with the first Worksheet
WorkSheet firstSheet = workbook.DefaultWorkSheet;
//...


//Save in Excel format or export to many other file formats
workBook.SaveAs("test.xlsx");Imports IronXL

'This is how we create new worksheet
Private workBook As WorkBook = WorkBook.Load("test.xlsx")

'This is how we create new worksheet
Private newSheet = workBook.CreateWorkSheet("new_sheet")
'You can perform any operations you want with the new sheet.
Private newSheet("B2").Value = DateTime.Now


' Work with an existing Worksheets 
Private oldSheet As WorkSheet = workbook.GetWorkSheet("sheet1")
'...

' Work with the first Worksheet
Private firstSheet As WorkSheet = workbook.DefaultWorkSheet
'...


'Save in Excel format or export to many other file formats
workBook.SaveAs("test.xlsx")

 Excel Range Cells
using IronXL;
using System.Linq;

WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();

//This is how we get range from Excel worksheet
var range = sheet["A2:A8"];
//This is how we can iterate over our range and read or edit any cell
foreach (var cell in range)
{
    Console.WriteLine(cell.Value);
}


// Another Example
var oneMoreRange = sheet["A9:A10"];

//This is how we can combine our ranges into a single selection
var resultRange = range + oneMoreRange;

//Iterate over new range - get or set any Cell
foreach (var cell in resultRange)
{
    Console.WriteLine(cell.Value);
}Imports IronXL
Imports System.Linq

Private workbook As WorkBook = WorkBook.Load("test.xls")
Private sheet As WorkSheet = workbook.WorkSheets.First()

'This is how we get range from Excel worksheet
Private range = sheet("A2:A8")
'This is how we can iterate over our range and read or edit any cell
For Each cell In range
	Console.WriteLine(cell.Value)
Next cell


' Another Example
Dim oneMoreRange = sheet("A9:A10")

'This is how we can combine our ranges into a single selection
Dim resultRange = range + oneMoreRange

'Iterate over new range - get or set any Cell
For Each cell In resultRange
	Console.WriteLine(cell.Value)
Next cell

 Create a new Excel File
using IronXL;

//Create new Excel WorkBook document. 
//The default file format is XLSX, but we can override that for legacy support
WorkBook xlsWorkbook = WorkBook.Create(ExcelFileFormat.XLS);
xlsWorkbook.Metadata.Author = "IronXL";

//Add a blank WorkSheet
WorkSheet xlsSheet = xlsWorkbook.CreateWorkSheet("new_sheet");

//Add data and styles to the new worksheet
xlsSheet["A1"].Value = "Hello World";
xlsSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
xlsSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;

//Save the excel file
xlsWorkbook.SaveAs("NewExcelFile.xls");Imports IronXL

'Create new Excel WorkBook document. 
'The default file format is XLSX, but we can override that for legacy support
Private xlsWorkbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
xlsWorkbook.Metadata.Author = "IronXL"

'Add a blank WorkSheet
Dim xlsSheet As WorkSheet = xlsWorkbook.CreateWorkSheet("new_sheet")

'Add data and styles to the new worksheet
xlsSheet("A1").Value = "Hello World"
xlsSheet("A2").Style.BottomBorder.SetColor("#ff6600")
xlsSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double

'Save the excel file
xlsWorkbook.SaveAs("NewExcelFile.xls")

 Convert Spreadsheet File Types
using IronXL;

//Import any XLSX,XLS, CSV or TSV Spreadsheet.
WorkBook workbook = WorkBook.Load("test.xls");

//Optionally Edit the Spreadsheet
workbook.Metadata.Title = "Import & Export Example";

//Export to many formats with fluent saving
workbook.SaveAs("test.xls");
workbook.SaveAs("export.xlsx");
workbook.SaveAsCsv("export.csv");
workbook.SaveAsJson("export.json");
workbook.SaveAsXml("export.xml");

//Also Export to a DataSet to allow easy integration with DataGrids, SQL and EF
System.Data.DataSet dataSet = workbook.ToDataSet();Imports IronXL

'Import any XLSX,XLS, CSV or TSV Spreadsheet.
Private workbook As WorkBook = WorkBook.Load("test.xls")

'Optionally Edit the Spreadsheet
workbook.Metadata.Title = "Import & Export Example"

'Export to many formats with fluent saving
workbook.SaveAs("test.xls")
workbook.SaveAs("export.xlsx")
workbook.SaveAsCsv("export.csv")
workbook.SaveAsJson("export.json")
workbook.SaveAsXml("export.xml")

'Also Export to a DataSet to allow easy integration with DataGrids, SQL and EF
Dim dataSet As System.Data.DataSet = workbook.ToDataSet()

 Excel to SQL via System.Data.DataSet
using IronXL;
using System.Data;

 //Open any Excel document.
WorkBook workbook = WorkBook.Load("test.xls");

// Convert the whole Excel WorkBook to a DataSet
// This allows us to work with DataGrids and System.Data.SQL nicely
var dataSet = workbook.ToDataSet();
foreach (DataTable table in dataSet.Tables)
{
    Console.WriteLine(table.TableName);

     //Enumerate by rows or columns first at your preference
	foreach (DataRow row in table.Rows)
	{
	    for (int i = 0; i < table.Columns.Count; i++)
	    {
	        Console.Write(row[i]);
	    }
	}
}Imports IronXL
Imports System.Data

 'Open any Excel document.
Private workbook As WorkBook = WorkBook.Load("test.xls")

' Convert the whole Excel WorkBook to a DataSet
' This allows us to work with DataGrids and System.Data.SQL nicely
Private dataSet = workbook.ToDataSet()
For Each table As DataTable In dataSet.Tables
	Console.WriteLine(table.TableName)

	 'Enumerate by rows or columns first at your preference
	For Each row As DataRow In table.Rows
		For i As Integer = 0 To table.Columns.Count - 1
			Console.Write(row(i))
		Next i
	Next row
Next table

 Excel to SQL and DataGrid via DataTable
using IronXL;
using System.Data 

//Open any Excel document.
WorkBook workbook = WorkBook.Load("test.xls");


// Work with a single WorkSheet.  Can also use workbook.DefaultWorkSheet
WorkSheet sheet = workbook.GetWorkSheet("sheet1");

//Convert the worksheet to System.Data.DataTable
//Boolean parameter sets the first row as column names of your table.
System.Data.DataTable dataTable = sheet.ToDataTable(true);

//The DataTable can populate a DataGrid for example.

//Enumerate by rows or columns first at your preference
foreach (DataRow row in dataTable.Rows)
{
    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        Console.Write(row[i]);
    }
}Imports IronXL
Private System As using


' Work with a single WorkSheet.  Can also use workbook.DefaultWorkSheet
Private sheet As WorkSheet = workbook.GetWorkSheet("sheet1")

'Convert the worksheet to System.Data.DataTable
'Boolean parameter sets the first row as column names of your table.
Private dataTable As System.Data.DataTable = sheet.ToDataTable(True)

'The DataTable can populate a DataGrid for example.

'Enumerate by rows or columns first at your preference
For Each row As DataRow In dataTable.Rows
	For i As Integer = 0 To dataTable.Columns.Count - 1
		Console.Write(row(i))
	Next i
Next row

 Excel Conditional Formatting
using IronXL;
using IronXL.Formatting;
using IronXL.Formatting.Enums;
using IronXL.Styles;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

//Create a specific conditional formatting rule.
ConditionalFormattingRule rule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8");
//Set different style options.
rule.FontFormatting.IsBold = true;
rule.FontFormatting.FontColor = "#123456";
rule.BorderFormatting.RightBorderColor = "#ffffff";
rule.BorderFormatting.RightBorderType = BorderType.Thick;
rule.PatternFormatting.BackgroundColor = "#54bdd9";
rule.PatternFormatting.FillPattern = FillPattern.Diamonds;
//Add formatting with the specified region.
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule);

ConditionalFormattingRule rule1 = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10");
rule1.FontFormatting.IsItalic = true;
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single;
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1);

workbook.SaveAs("ApplyConditionalFormatting.xlsx");Imports IronXL
Imports IronXL.Formatting
Imports IronXL.Formatting.Enums
Imports IronXL.Styles

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private sheet As WorkSheet = workbook.DefaultWorkSheet

'Create a specific conditional formatting rule.
Private rule As ConditionalFormattingRule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8")
'Set different style options.
rule.FontFormatting.IsBold = True
rule.FontFormatting.FontColor = "#123456"
rule.BorderFormatting.RightBorderColor = "#ffffff"
rule.BorderFormatting.RightBorderType = BorderType.Thick
rule.PatternFormatting.BackgroundColor = "#54bdd9"
rule.PatternFormatting.FillPattern = FillPattern.Diamonds
'Add formatting with the specified region.
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule)

Dim rule1 As ConditionalFormattingRule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10")
rule1.FontFormatting.IsItalic = True
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1)

workbook.SaveAs("ApplyConditionalFormatting.xlsx")

 Convert XLS to XLSX in C#
  
using IronXL;

WorkBook workbook = WorkBook.Load("Test.xls");

//This is how we can convert .xls workbook to xlsx workbook.  
//Just SaveAs with the .xlsx file extension!
workbook.SaveAs("Test.xlsx");Imports IronXL

Private workbook As WorkBook = WorkBook.Load("Test.xls")

'This is how we can convert .xls workbook to xlsx workbook.  
'Just SaveAs with the .xlsx file extension!
workbook.SaveAs("Test.xlsx")

 Protect Excel Files
  
using IronXL;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

//This is how we can protect the worksheet setting the password to it
//Users will then need to know this password to edit the worksheet in Excel.
worksheet.ProtectSheet("password");
workbook.Save();

//This is how to unprotect the worksheet and remove any password locks 
//It works even if you do not know the password!
worksheet.UnprotectSheet();
workbook.Save();Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private worksheet As WorkSheet = workbook.DefaultWorkSheet

'This is how we can protect the worksheet setting the password to it
'Users will then need to know this password to edit the worksheet in Excel.
worksheet.ProtectSheet("password")
workbook.Save()

'This is how to unprotect the worksheet and remove any password locks 
'It works even if you do not know the password!
worksheet.UnprotectSheet()
workbook.Save()

 Excel Formulas in C#
using IronXL;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Set Formulas
worksheet["A1"].Formula = "Sum(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "Max(C3:C7)";

// Force recalculate all formula values in all sheets.  
workbook.EvaluateAll();

// Get Formulas
// Get the formula's calculated value.  e.g. "52"
string formulaValue = worksheet["G30"].Value;

//Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = worksheet["G30"].Formula;

//Save your changes with updated formulas and calculated values.
workbook.Save();Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private worksheet As WorkSheet = workbook.DefaultWorkSheet

' Set Formulas
Private worksheet("A1").Formula = "Sum(B8:C12)"
Private worksheet("B8").Formula = "=C9/C11"
Private worksheet("G30").Formula = "Max(C3:C7)"

' Force recalculate all formula values in all sheets.  
workbook.EvaluateAll()

' Get Formulas
' Get the formula's calculated value.  e.g. "52"
Dim formulaValue As String = worksheet("G30").Value

'Get the formula as a string. e.g. "Max(C3:C7)"
Dim formulaString As String = worksheet("G30").Formula

'Save your changes with updated formulas and calculated values.
workbook.Save()

 Save Excel to Database
using IronXL;
using System.Data;


WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.DefaultWorkSheet;

//This is how you convert your worksheet to DataTable
//Boolean parameter allows you to set whether you want to treat your first row as column names of your table.
//The default value is false
var dataTable = sheet.ToDataTable(true);

//Optionally inspect your data
foreach (DataRow row in dataTable.Rows)
{
    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        Console.Write(row[i]);
    }
}



//Also you can convert the whole workbook to DataSet and work with it the way you like.
var dataSet = workbook.ToDataSet();

foreach (DataTable table in dataSet.Tables)
{
    Console.WriteLine(table.TableName);
}Imports IronXL
Imports System.Data


Private workbook As WorkBook = WorkBook.Load("test.xls")
Private sheet As WorkSheet = workbook.DefaultWorkSheet

'This is how you convert your worksheet to DataTable
'Boolean parameter allows you to set whether you want to treat your first row as column names of your table.
'The default value is false
Private dataTable = sheet.ToDataTable(True)

'Optionally inspect your data
For Each row As DataRow In dataTable.Rows
	For i As Integer = 0 To dataTable.Columns.Count - 1
		Console.Write(row(i))
	Next i
Next row



'Also you can convert the whole workbook to DataSet and work with it the way you like.
Dim dataSet = workbook.ToDataSet()

For Each table As DataTable In dataSet.Tables
	Console.WriteLine(table.TableName)
Next table

 Edit Excel Metadata in C#
using IronXL;

WorkBook workbook = WorkBook.Load("test.xlsx");


//This is how we set  the author of the file
workbook.Metadata.Author = "Your Name";
//This is how we set the comments to the file
workbook.Metadata.Comments = "Monthly report";


//This is how we read the creation date of the excel file
DateTime? creationDate = workbook.Metadata.Created;

//This is how read the last printed date of the excel file
DateTime? printDate = workbook.Metadata.LastPrinted;

workbook.SaveAs();Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xlsx")


'This is how we set  the author of the file
workbook.Metadata.Author = "Your Name"
'This is how we set the comments to the file
workbook.Metadata.Comments = "Monthly report"


'This is how we read the creation date of the excel file
Dim creationDate? As DateTime = workbook.Metadata.Created

'This is how read the last printed date of the excel file
Dim printDate? As DateTime = workbook.Metadata.LastPrinted

workbook.SaveAs()

 Update Excel Database Records
using System.Data;
using System.Data.SqlClient;
using IronXL;

WorkBook wb = WorkBook.Load("test.xlsx");
DataSet dataSet = wb.ToDataSet();
//Your sql query
string sql = "SELECT * FROM Users";
//Your connection string
string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    //Open connections to the database
    connection.Open();
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);

    //Important - you can (and probably should) modify / crop the data in the dataset here to match the shape of your SQL query

    //Update the values in database using the values in Excel
    adapter.Update(dataSet);
}Imports System.Data
Imports System.Data.SqlClient
Imports IronXL

Private wb As WorkBook = WorkBook.Load("test.xlsx")
Private dataSet As DataSet = wb.ToDataSet()
'Your sql query
Private sql As String = "SELECT * FROM Users"
'Your connection string
Private connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"
Using connection As New SqlConnection(connectionString)
	'Open connections to the database
	connection.Open()
	Dim adapter As New SqlDataAdapter(sql, connection)

	'Important - you can (and probably should) modify / crop the data in the dataset here to match the shape of your SQL query

	'Update the values in database using the values in Excel
	adapter.Update(dataSet)
End Using

 Style Excel Cell Borders & Fonts
using System.Linq;
using IronXL;
using IronXL.Styles;

WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();
var range = sheet["A1:H10"];

var cell = range.First();

//This is how we set background color of the cell with an rgb string
cell.Style.SetBackgroundColor("#428D65");

//Styling can be applied to the whole range.

//This is how we set underline property to the font
//FontUnderlineType is enum that stands for different types of font underlying
range.Style.Font.Underline = FontUnderlineType.SingleAccounting;

//This is how we define whether to use horizontal line through the text or not
range.Style.Font.Strikeout = false;

//This is how we define whether the font is bold or not
range.Style.Font.Bold = true;

//This is how we define whether the font is italic or not
range.Style.Font.Italic = false;

//This is how we can get or set script property of a font
//Font script enum stands for available options
range.Style.Font.FontScript = FontScript.Super;

//This is how we can set  the type of the border line
//There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties
//BorderType enum indicates the line style of a border in a cell
range.Style.BottomBorder.Type = BorderType.MediumDashed;

//This is how we can indicate whether the cell should be auto-sized 
range.Style.ShrinkToFit = true;

//This is how we can set alignment of the cell
range.Style.VerticalAlignment = VerticalAlignment.Bottom;

//This is how we can set border color
range.Style.DiagonalBorder.SetColor("#20C96F");

//We need to define border type and border direction as well
range.Style.DiagonalBorder.Type = BorderType.Thick;

//DiagonalBorderDirection enum stands for direction of diagonal border inside cell
range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward;

//Set background color of cells
range.Style.SetBackgroundColor(Color.Aquamarine);

//This is how we can set fill pattern of the cell
//FillPattern enum indicates the style of fill pattern
range.Style.FillPattern = FillPattern.Diamonds;

//This is how we can set the number of spaces to intend the text
range.Style.Indention = 5;

//This is how we can indicate if the text is wrapped
range.Style.WrapText = true;

workbook.SaveAs("StylingOptions.xls");Imports System.Linq
Imports IronXL
Imports IronXL.Styles

Private workbook As WorkBook = WorkBook.Load("test.xls")
Private sheet As WorkSheet = workbook.WorkSheets.First()
Private range = sheet("A1:H10")

Private cell = range.First()

'This is how we set background color of the cell with an rgb string
cell.Style.SetBackgroundColor("#428D65")

'Styling can be applied to the whole range.

'This is how we set underline property to the font
'FontUnderlineType is enum that stands for different types of font underlying
range.Style.Font.Underline = FontUnderlineType.SingleAccounting

'This is how we define whether to use horizontal line through the text or not
range.Style.Font.Strikeout = False

'This is how we define whether the font is bold or not
range.Style.Font.Bold = True

'This is how we define whether the font is italic or not
range.Style.Font.Italic = False

'This is how we can get or set script property of a font
'Font script enum stands for available options
range.Style.Font.FontScript = FontScript.Super

'This is how we can set  the type of the border line
'There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties
'BorderType enum indicates the line style of a border in a cell
range.Style.BottomBorder.Type = BorderType.MediumDashed

'This is how we can indicate whether the cell should be auto-sized 
range.Style.ShrinkToFit = True

'This is how we can set alignment of the cell
range.Style.VerticalAlignment = VerticalAlignment.Bottom

'This is how we can set border color
range.Style.DiagonalBorder.SetColor("#20C96F")

'We need to define border type and border direction as well
range.Style.DiagonalBorder.Type = BorderType.Thick

'DiagonalBorderDirection enum stands for direction of diagonal border inside cell
range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward

'Set background color of cells
range.Style.SetBackgroundColor(Color.Aquamarine)

'This is how we can set fill pattern of the cell
'FillPattern enum indicates the style of fill pattern
range.Style.FillPattern = FillPattern.Diamonds

'This is how we can set the number of spaces to intend the text
range.Style.Indention = 5

'This is how we can indicate if the text is wrapped
range.Style.WrapText = True

workbook.SaveAs("StylingOptions.xls")

 Sort Excel Ranges in C#
using IronXL;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

//Select a Range
Range range = worksheet["A1:D20"];

//This is how we can sort the current range in ascending order (A to Z)
range.SortAscending();


//This is how we can sort the current range in descending order (Z to A)
range.SortDescending();

workbook.SaveAs("SortExcelRange.xlsx");Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private worksheet As WorkSheet = workbook.DefaultWorkSheet

'Select a Range
Private range As Range = worksheet("A1:D20")

'This is how we can sort the current range in ascending order (A to Z)
range.SortAscending()


'This is how we can sort the current range in descending order (Z to A)
range.SortDescending()

workbook.SaveAs("SortExcelRange.xlsx")

 Repeat Excel Rows & Columns
using IronXL;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

// SetRepeatingRows is how we can set repeating rows for the print title of the worksheet.  
// The Int parameters are the start and end zero-based row numbers.
// To remove  repeating rows you can use worksheet.RemoveRepeatingRows();  
worksheet.SetRepeatingRows(1, 2);


// SetRepeatingColumns is how we can set repeating columns.
// The Int parameters are the start and end zero-based column numbers (0 is "A");
// To remove repeating columns you can use worksheet.RemoveRepeatingColumns();
worksheet.SetRepeatingColumns(2,3);


workbook.SaveAs("RepeatingRows.xlsx");Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private worksheet As WorkSheet = workbook.DefaultWorkSheet

' SetRepeatingRows is how we can set repeating rows for the print title of the worksheet.  
' The Int parameters are the start and end zero-based row numbers.
' To remove  repeating rows you can use worksheet.RemoveRepeatingRows();  
worksheet.SetRepeatingRows(1, 2)


' SetRepeatingColumns is how we can set repeating columns.
' The Int parameters are the start and end zero-based column numbers (0 is "A");
' To remove repeating columns you can use worksheet.RemoveRepeatingColumns();
worksheet.SetRepeatingColumns(2,3)


workbook.SaveAs("RepeatingRows.xlsx")

 Read XLSX Files
using IronXL;

//Other supported formats for reading are: XLS, XLSM, CSV, TSV
WorkBook workbook = WorkBook.Load("test.xlsx");

//Other workbook operations include...


//Edit metadata
workbook.Metadata.Author = "IronXL";
WorkBook.Save();

//Read data
WorkSheet sheet = workbook.DefaultWorkSheet();
decimal myValue = sheet["A2"].DecimalValue;Imports IronXL

'Other supported formats for reading are: XLS, XLSM, CSV, TSV
Private workbook As WorkBook = WorkBook.Load("test.xlsx")

'Other workbook operations include...


'Edit metadata
workbook.Metadata.Author = "IronXL"
WorkBook.Save()

'Read data
Dim sheet As WorkSheet = workbook.DefaultWorkSheet()
Dim myValue As Decimal = sheet("A2").DecimalValue

 Excel Print Setup
using IronXL;
using IronXL.Printing;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;
//This is how we set the print header of the worksheet
worksheet.Header.Center = "My document";
//This is how we can set the header margin
worksheet.PrintSetup.HeaderMargin = 2.33;
//This is how we set the size of the paper
//Paper size enum represents different sizes of paper
worksheet.PrintSetup.PaperSize = PaperSize.B4;
//This is how we can set the print orientation of the worksheet
worksheet.PrintSetup.PrintOrientation = PrintOrientation.Portrait;
//This is how we can set black and white printing
worksheet.PrintSetup.NoColor = true;

workbook.SaveAs("PrintSetup.xlsx");Imports IronXL
Imports IronXL.Printing

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private worksheet As WorkSheet = workbook.DefaultWorkSheet
'This is how we set the print header of the worksheet
worksheet.Header.Center = "My document"
'This is how we can set the header margin
worksheet.PrintSetup.HeaderMargin = 2.33
'This is how we set the size of the paper
'Paper size enum represents different sizes of paper
worksheet.PrintSetup.PaperSize = PaperSize.B4
'This is how we can set the print orientation of the worksheet
worksheet.PrintSetup.PrintOrientation = PrintOrientation.Portrait
'This is how we can set black and white printing
worksheet.PrintSetup.NoColor = True

workbook.SaveAs("PrintSetup.xlsx")

 Preserve Excel Macros in XLSM
using IronXL;

WorkBook workbook = WorkBook.Load("test.xlsm");
//You can load xlsm file and work with it as you wish.
var sheet = workbook.CreateWorkSheet("one more sheet");
sheet["A1:B2"].Value = "Hi";
//Then you can save it with xlsm extension and all macros will be preserved.
workbook.SaveAs("test.edit.xlsm");Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xlsm")
'You can load xlsm file and work with it as you wish.
Private sheet = workbook.CreateWorkSheet("one more sheet")
Private sheet("A1:B2").Value = "Hi"
'Then you can save it with xlsm extension and all macros will be preserved.
workbook.SaveAs("test.edit.xlsm")

 Excel Number Format
using System.Linq;
using IronXL;

//Open an existing WorkSheet
WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();

//We should set cell value and then specify the format to represent data
//The cell value will look like 12300%
sheet["a2"].First().Value = 123;
sheet["a2"].First().FormatString = "0.0%";

//The cell value will look like  123.0000
sheet["a2"].First().FormatString = "0.0000";

DateTime dateValue = new DateTime(2020, 1, 1, 12, 12, 12);
sheet["A3"].First().Value = dateValue;

//The cell value will look like 01.01.2020  12:12:12
sheet["A3"].First().FormatString = "MM/dd/yy h:mm:ss";

workbook.SaveAs("CsvToExcelConversion.xls");Imports System.Linq
Imports IronXL

'Open an existing WorkSheet
Private workbook As WorkBook = WorkBook.Load("test.xls")
Private sheet As WorkSheet = workbook.WorkSheets.First()

'We should set cell value and then specify the format to represent data
'The cell value will look like 12300%
sheet("a2").First().Value = 123
sheet("a2").First().FormatString = "0.0%"

'The cell value will look like  123.0000
sheet("a2").First().FormatString = "0.0000"

Dim dateValue As New DateTime(2020, 1, 1, 12, 12, 12)
sheet("A3").First().Value = dateValue

'The cell value will look like 01.01.2020  12:12:12
sheet("A3").First().FormatString = "MM/dd/yy h:mm:ss"

workbook.SaveAs("CsvToExcelConversion.xls")

 Load Excel From SQL Database
using System.Data;
using System.Data.SqlClient;
using IronXL;

//Your sql query
string sql = "SELECT * FROM Users";

//Your connection string
string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    //Open connections to the database
    connection.Open();
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
    DataSet ds = new DataSet();
    // Fill DataSet with data
    adapter.Fill(ds);


    //Create an Excel workbook from the SQL DataSet in one line
    WorkBook workbook = WorkBook.Load(ds);
}Imports System.Data
Imports System.Data.SqlClient
Imports IronXL

'Your sql query
Private sql As String = "SELECT * FROM Users"

'Your connection string
Private connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"

Using connection As New SqlConnection(connectionString)
	'Open connections to the database
	connection.Open()
	Dim adapter As New SqlDataAdapter(sql, connection)
	Dim ds As New DataSet()
	' Fill DataSet with data
	adapter.Fill(ds)


	'Create an Excel workbook from the SQL DataSet in one line
	Dim workbook As WorkBook = WorkBook.Load(ds)
End Using

 Combine Excel Ranges
using System.Linq;
using IronXL;

WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();

//This is how we get a range from an Excel worksheet
var range = sheet["A2:A8"];
 
var combinedRange = range + sheet["A9:A10"];;

//Iterate over combined range
foreach (var cell in combinedRange)
{
    Console.WriteLine(cell.Value);
}Imports System.Linq
Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xls")
Private sheet As WorkSheet = workbook.WorkSheets.First()

'This is how we get a range from an Excel worksheet
Private range = sheet("A2:A8")

Private combinedRange = range + sheet("A9:A10")

'Iterate over combined range
For Each cell In combinedRange
	Console.WriteLine(cell.Value)
Next cell

 Select Excel Range
using System.Linq;
using IronXL;

WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();

//This is how we get range from Excel worksheet
var range = sheet["A2:A8"];

//This is how we can iterate over our range
foreach (var cell in range)
{
    Console.WriteLine(cell.Value);
}Imports System.Linq
Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xls")
Private sheet As WorkSheet = workbook.WorkSheets.First()

'This is how we get range from Excel worksheet
Private range = sheet("A2:A8")

'This is how we can iterate over our range
For Each cell In range
	Console.WriteLine(cell.Value)
Next cell

 Generate Excel Files
using IronXL;

//This is how we create new workbook. Default file format is XLSX, we can override it using CreatingOptions
WorkBook xlsWorkbook = WorkBook.Create(ExcelFileFormat.XLS);\

//This is how we create new worksheet
WorkSheet xlsSheet = xlsWorkbook.CreateWorkSheet("new_sheet");

//Add data and styles to the new worksheet
xlsSheet["A1"].Value = "Hello World";
xlsSheet["A2"].Style.BottomBorder.SetColor("#123456");
xlsSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;

//Save generated excel file
xlsWorkbook.SaveAs("GenerateExcelFile.xls");Imports IronXL

'This is how we create new workbook. Default file format is XLSX, we can override it using CreatingOptions
Private xlsWorkbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
Private \ WorkSheet xlsSheet = xlsWorkbook.CreateWorkSheet("new_sheet")

'Add data and styles to the new worksheet
Private xlsSheet("A1").Value = "Hello World"
xlsSheet("A2").Style.BottomBorder.SetColor("#123456")
xlsSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double

'Save generated excel file
xlsWorkbook.SaveAs("GenerateExcelFile.xls")

 XLSX to XLSM with Macros
  
using IronXL;
WorkBook workbook = WorkBook.Load("test.xlsx");

//This is how you can export workbook to .xlsm format
workbook.SaveAs("test.xlsm");Imports IronXL
Private workbook As WorkBook = WorkBook.Load("test.xlsx")

'This is how you can export workbook to .xlsm format
workbook.SaveAs("test.xlsm")

 Convert Excel to HTML
using IronXL;
using IronXL.Options;

WorkBook workbook = WorkBook.Load("test.xlsx");

var options = new HtmlExportOptions()
{
    //This is how we can make row/column numbers visible in html document
    OutputRowNumbers = true,
    OutputColumnHeaders = true,
    OutputHiddenColumns = true,

    //This is how we can make hidden rows/columns visible in html document
    OutputHiddenRows = true,
    OutputLeadingSpacesAsNonBreaking = true
};

//This is how we can export workbook to the HTML file
workbook.ExportToHtml("workbook.html",options);Imports IronXL
Imports IronXL.Options

Private workbook As WorkBook = WorkBook.Load("test.xlsx")

Private options = New HtmlExportOptions() With {
	.OutputRowNumbers = True,
	.OutputColumnHeaders = True,
	.OutputHiddenColumns = True,
	.OutputHiddenRows = True,
	.OutputLeadingSpacesAsNonBreaking = True
}

'This is how we can export workbook to the HTML file
workbook.ExportToHtml("workbook.html",options)

 Import Export Excel
  
using IronXL;
//This is how you import excel file, same for xlsx
WorkBook workbook = WorkBook.Load("test.xls");
workbook.CreateWorkSheet("new_sheet");
workbook.Metadata.Title = "Import & Export Example";

//You can export to both formats with fluent saving
//Other supported formats for saving include: XLS, XLSX, CSV, TSV, JSON, XML
workbook.SaveAs("ExportAndImportToExcel.xls");Imports IronXL
'This is how you import excel file, same for xlsx
Private workbook As WorkBook = WorkBook.Load("test.xls")
workbook.CreateWorkSheet("new_sheet")
workbook.Metadata.Title = "Import & Export Example"

'You can export to both formats with fluent saving
'Other supported formats for saving include: XLS, XLSX, CSV, TSV, JSON, XML
workbook.SaveAs("ExportAndImportToExcel.xls")

 Aggregate Excel Functions
using System.Linq;
using IronXL;

WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();

//This is how we get range from Excel worksheet
var range = sheet["A2:A8"];

//This is how we can get sum of all numeric cells within the range
decimal sum = range.Sum();

//This is how we can get average value of all numeric cells within the range
decimal avg = range.Avg();

//This is how we can get maximum value of all numeric cells within the range
decimal max = range.Max();

//This is how we can get minimum value of all numeric cells within the range
decimal min = range.Min();Imports System.Linq
Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xls")
Private sheet As WorkSheet = workbook.WorkSheets.First()

'This is how we get range from Excel worksheet
Private range = sheet("A2:A8")

'This is how we can get sum of all numeric cells within the range
Private sum As Decimal = range.Sum()

'This is how we can get average value of all numeric cells within the range
Private avg As Decimal = range.Avg()

'This is how we can get maximum value of all numeric cells within the range
Private max As Decimal = range.Max()

'This is how we can get minimum value of all numeric cells within the range
Private min As Decimal = range.Min()

 CSV to Excel
using IronXL;

//This is how we can load csv file and convert it to .xls or .xlsx workbook
//Also we can specify the column list delimiter
WorkBook workbook = WorkBook.LoadCSV("test.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
WorkSheet ws = workbook.DefaultWorkSheet;

workbook.SaveAs("CsvToExcelConversion.xlsx");Imports IronXL

'This is how we can load csv file and convert it to .xls or .xlsx workbook
'Also we can specify the column list delimiter
Private workbook As WorkBook = WorkBook.LoadCSV("test.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
Private ws As WorkSheet = workbook.DefaultWorkSheet

workbook.SaveAs("CsvToExcelConversion.xlsx")

 Create Excel Scatter Chart
using IronXL;
using IronXL.Drawing.Charts;


WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

//Set the chart type and it's position  on the worksheet.
var chart = sheet.CreateChart(ChartType.Scatter, 10, 2, 18, 5);

//Add the series to the chart
//Both axes of the scatter chart are value axes.
//The first parameter is the address of the range for horizontal(value) axis.
//The second parameter is the address of the range for vertical(value) axis.
var series = chart.AddSeries("B3:B8", "A3:A8");

var rnd = new Random();
foreach (var cell in sheet["C3:C8"])
{
    //Populate one more column with random values
    cell.Value = rnd.Next(10);
}

//For scatter chart there is an option to to add set of data only for vertical axis.
chart.AddSeries("C3:C8");

//The set of data in the horizontal axis starts from one in ascending order.

//Set the chart title.
series.Title = "Scatter Chart";

//Set the legend position.
//Can be removed by setting it to None.
chart.SetLegendPosition(LegendPosition.Top);

//Plot all the data that was added to the chart before.
//Multiple call of this method leads to plotting multiple charts instead of modyfying the existing chart.
//Yet there is no possibility to remove chart or edit it's series/position.
//We can just create new one.
chart.Plot();

workbook.SaveAs("CreateScatterChart.xlsx");Imports IronXL
Imports IronXL.Drawing.Charts


Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private sheet As WorkSheet = workbook.DefaultWorkSheet

'Set the chart type and it's position  on the worksheet.
Private chart = sheet.CreateChart(ChartType.Scatter, 10, 2, 18, 5)

'Add the series to the chart
'Both axes of the scatter chart are value axes.
'The first parameter is the address of the range for horizontal(value) axis.
'The second parameter is the address of the range for vertical(value) axis.
Private series = chart.AddSeries("B3:B8", "A3:A8")

Private rnd = New Random()
For Each cell In sheet("C3:C8")
	'Populate one more column with random values
	cell.Value = rnd.Next(10)
Next cell

'For scatter chart there is an option to to add set of data only for vertical axis.
chart.AddSeries("C3:C8")

'The set of data in the horizontal axis starts from one in ascending order.

'Set the chart title.
series.Title = "Scatter Chart"

'Set the legend position.
'Can be removed by setting it to None.
chart.SetLegendPosition(LegendPosition.Top)

'Plot all the data that was added to the chart before.
'Multiple call of this method leads to plotting multiple charts instead of modyfying the existing chart.
'Yet there is no possibility to remove chart or edit it's series/position.
'We can just create new one.
chart.Plot()

workbook.SaveAs("CreateScatterChart.xlsx")

 Create Excel Line Chart
using IronXL;
using IronXL.Drawing.Charts;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

//Set the chart type and it's position  on the worksheet.
var chart = sheet.CreateChart(ChartType.Line, 10, 10, 18, 20);

//Add the series to the chart
//The first parameter represents the address of the range for horizontal(category) axis.
//The second  parameter represents the address of the range for vertical(value) axis.
var series = chart.AddSeries("B3:B8", "A3:A8");

//Set the chart title.
series.Title = "Line Chart";

//Set the legend position.
//Can be removed by setting it to None.
chart.SetLegendPosition(LegendPosition.Bottom);

//We can change the position of the chart.
chart.Position.LeftColumnIndex = 2;
chart.Position.RightColumnIndex = chart.Position.LeftColumnIndex + 3;

//Plot all the data that was added to the chart before.
//Multiple call of this method leads to plotting multiple charts instead of modifying the existing chart.
//Yet there is no possibility to remove chart or edit it's series/position.
//We can just create new one.
chart.Plot();

workbook.SaveAs("CreateLineChart.xlsx");Imports IronXL
Imports IronXL.Drawing.Charts

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private sheet As WorkSheet = workbook.DefaultWorkSheet

'Set the chart type and it's position  on the worksheet.
Private chart = sheet.CreateChart(ChartType.Line, 10, 10, 18, 20)

'Add the series to the chart
'The first parameter represents the address of the range for horizontal(category) axis.
'The second  parameter represents the address of the range for vertical(value) axis.
Private series = chart.AddSeries("B3:B8", "A3:A8")

'Set the chart title.
series.Title = "Line Chart"

'Set the legend position.
'Can be removed by setting it to None.
chart.SetLegendPosition(LegendPosition.Bottom)

'We can change the position of the chart.
chart.Position.LeftColumnIndex = 2
chart.Position.RightColumnIndex = chart.Position.LeftColumnIndex + 3

'Plot all the data that was added to the chart before.
'Multiple call of this method leads to plotting multiple charts instead of modifying the existing chart.
'Yet there is no possibility to remove chart or edit it's series/position.
'We can just create new one.
chart.Plot()

workbook.SaveAs("CreateLineChart.xlsx")

 Create Excel Bar Chart
using IronXL;
using IronXL.Drawing.Charts;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

//Set the chart type and its position on the worksheet.
var chart = sheet.CreateChart(ChartType.Bar, 10, 2, 18, 5);


//Add the series to the chart
//For bar chart the first parameter is the address of the range for horizontal(value) axis.
//The second  parameter is the address of the range for vertical(category) axis.
var series = chart.AddSeries("A3:A8", "B3:B8");

//Set the chart title.
series.Title = "Bar Chart";


//Set the legend position.
//Can be removed by settind it to null.
chart.SetLegendPosition(LegendPosition.Left);


//Plot all the data that was added to the chart before.
//Multiple calls of this method leads to plotting multiple charts instead of modifying the existing chart.
//Yet there is no possibility to remove chart or edit it's series/position.
//We can just create the new one.
chart.Plot();

workbook.SaveAs("CreateBarChart.xlsx");Imports IronXL
Imports IronXL.Drawing.Charts

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private sheet As WorkSheet = workbook.DefaultWorkSheet

'Set the chart type and its position on the worksheet.
Private chart = sheet.CreateChart(ChartType.Bar, 10, 2, 18, 5)


'Add the series to the chart
'For bar chart the first parameter is the address of the range for horizontal(value) axis.
'The second  parameter is the address of the range for vertical(category) axis.
Private series = chart.AddSeries("A3:A8", "B3:B8")

'Set the chart title.
series.Title = "Bar Chart"


'Set the legend position.
'Can be removed by settind it to null.
chart.SetLegendPosition(LegendPosition.Left)


'Plot all the data that was added to the chart before.
'Multiple calls of this method leads to plotting multiple charts instead of modifying the existing chart.
'Yet there is no possibility to remove chart or edit it's series/position.
'We can just create the new one.
chart.Plot()

workbook.SaveAs("CreateBarChart.xlsx")

 Freeze Panes in Excel
using System.Linq;
using IronXL;

WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();

//This is how we can freeze specified amount of columns and rows.They will be visible while scrolling the screen
sheet.CreateFreezePane(2,5);

//sheet.RemovePane() can be used to remove this frozen pane.

workbook.SaveAs("CreateFreezePanes.xls");Imports System.Linq
Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xls")
Private sheet As WorkSheet = workbook.WorkSheets.First()

'This is how we can freeze specified amount of columns and rows.They will be visible while scrolling the screen
sheet.CreateFreezePane(2,5)

'sheet.RemovePane() can be used to remove this frozen pane.

workbook.SaveAs("CreateFreezePanes.xls")

 Create Excel Worksheet
using System;
using IronXL;

WorkBook workBook = WorkBook.Load("test.xlsx");

//This is how we create new worksheet
var newSheet = workBook.CreateWorkSheet("new_sheet");

//You can perform any operations you want with the new sheet.
newSheet["B2"].Value = DateTime.Now;

workBook.SaveAs("CreateExcelWorksheet.xlsx");Imports System
Imports IronXL

Private workBook As WorkBook = WorkBook.Load("test.xlsx")

'This is how we create new worksheet
Private newSheet = workBook.CreateWorkSheet("new_sheet")

'You can perform any operations you want with the new sheet.
Private newSheet("B2").Value = DateTime.Now

workBook.SaveAs("CreateExcelWorksheet.xlsx")

 Copy Excel Worksheets
using IronXL;

WorkBook firstBook = WorkBook.Load("test.xlsx");
WorkBook secondBook = WorkBook.Create();

//This is how we can get the first worksheet within the workbook
WorkSheet worksheet = firstBook.DefaultWorkSheet;

//This is how we can copy worksheet to the same workbook
worksheet.CopySheet("Copied Sheet");

//This is how we can copy worksheet to another workbook with the specified name
worksheet.CopyTo(secondBook,"Copied Sheet");

secondBook.SaveAs("CopyExcelWorksheet.xlsx");Imports IronXL

Private firstBook As WorkBook = WorkBook.Load("test.xlsx")
Private secondBook As WorkBook = WorkBook.Create()

'This is how we can get the first worksheet within the workbook
Private worksheet As WorkSheet = firstBook.DefaultWorkSheet

'This is how we can copy worksheet to the same workbook
worksheet.CopySheet("Copied Sheet")

'This is how we can copy worksheet to another workbook with the specified name
worksheet.CopyTo(secondBook,"Copied Sheet")

secondBook.SaveAs("CopyExcelWorksheet.xlsx")

Excel Tools

Name: C# Excel Library - IronXL
Rating: 4.81 (173 reviews)

April 20, 2022

How to Add Up a Column in Excel

This tutorial covers how To sum the total of a whole column, which are some things most people got to do quite often.