Test in production without watermarks.
Works wherever you need it to.
Get 30 days of fully functional product.
Have it up and running in minutes.
Full access to our support engineering team during your product trial
Working with various Excel formats often requires reading data and then reconfiguring it programmatically. In this article, we will learn how to read a CSV file and parse data from an Excel spreadsheet in C# using IronXL, the perfect tool for the job.
CSV is a simple data format, but there can be many differences; it can be difficult to read programmatically in our C# projects because it uses several delimiters for distinguishing between rows and columns of data. This article will show you how to use the IronXL library to read CSV files.
Before you can make use of IronXL to read CSV files in MVC, ASP.NET or .NET Core, you need to install it. Here is a quick walk-through.
Search for IronXL in NuGet Package Manager in Visual Studio
When you need to read CSV files in C#, IronXL is the perfect tool. You can read a CSV file with commas, or any other delimiter, as seen in the code segments below.
// Load a CSV file and interpret it as an Excel-like workbook
WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
// Access the default worksheet in the workbook
WorkSheet ws = workbook.DefaultWorkSheet;
// Save the workbook to a new Excel file
workbook.SaveAs("Csv_To_Excel.xlsx");
// Load a CSV file and interpret it as an Excel-like workbook
WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
// Access the default worksheet in the workbook
WorkSheet ws = workbook.DefaultWorkSheet;
// Save the workbook to a new Excel file
workbook.SaveAs("Csv_To_Excel.xlsx");
' Load a CSV file and interpret it as an Excel-like workbook
Dim workbook As WorkBook = WorkBook.LoadCSV("Weather.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
' Access the default worksheet in the workbook
Dim ws As WorkSheet = workbook.DefaultWorkSheet
' Save the workbook to a new Excel file
workbook.SaveAs("Csv_To_Excel.xlsx")
Output:
Output CSV file with comma delimiter
Code Explanation:
A WorkBook
object is created. The LoadCSV
method for the WorkBook object is then used to specify the name of the CSV, its format, and which delimiters are used in the CSV file being read. In this case, commas are used as delimiters.
A WorkSheet
object is then created. This is where the contents of the CSV file will be placed. The file is saved under a new name and format.
Data displayed in Microsoft Excel
Use IronXL for your project as a streamlined way to work with Excel file formats in C#. You can either install IronXL via direct download. Alternatively, you can use NuGet Install for Visual Studio. The software is free for development.
dotnet add package IronXL.Excel
WorkBook
and Access WorkSheet
WorkBook
is the class of IronXL whose object provides full access to the Excel file and all of its functions. For example, if we want to access an Excel file, we'd use the code:
// Load the Excel file
WorkBook wb = WorkBook.Load("sample.xlsx"); // Excel file path
// Load the Excel file
WorkBook wb = WorkBook.Load("sample.xlsx"); // Excel file path
' Load the Excel file
Dim wb As WorkBook = WorkBook.Load("sample.xlsx") ' Excel file path
To access the specific worksheet of an Excel file, IronXL provides the WorkSheet
class.
// Access a specific worksheet by name
WorkSheet ws = wb.GetWorkSheet("Sheet1"); // by sheet name
// Access a specific worksheet by name
WorkSheet ws = wb.GetWorkSheet("Sheet1"); // by sheet name
' Access a specific worksheet by name
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1") ' by sheet name
Once you have obtained the Excel worksheet ws
, you can extract any type of data from it and perform all Excel functions on it. Data can be accessed from the Excel worksheet ws
through this process:
using IronXL;
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Iterate through a range of cells and display their values
foreach (var cell in ws["A2:A10"])
{
Console.WriteLine("Value is: {0}", cell.Text);
}
Console.ReadKey();
}
}
using IronXL;
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Iterate through a range of cells and display their values
foreach (var cell in ws["A2:A10"])
{
Console.WriteLine("Value is: {0}", cell.Text);
}
Console.ReadKey();
}
}
Imports IronXL
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load the workbook and access a specific worksheet
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
' Iterate through a range of cells and display their values
For Each cell In ws("A2:A10")
Console.WriteLine("Value is: {0}", cell.Text)
Next cell
Console.ReadKey()
End Sub
End Class
Using IronXL, it is very easy to operate with an Excel WorkSheet
as a DataTable.
DataTable dt = ws.ToDataTable(true); // Converts the worksheet to a DataTable, using the first row as column names
DataTable dt = ws.ToDataTable(true); // Converts the worksheet to a DataTable, using the first row as column names
Dim dt As DataTable = ws.ToDataTable(True) ' Converts the worksheet to a DataTable, using the first row as column names
Use the following namespaces:
using IronXL;
using System.Data;
using IronXL;
using System.Data;
Imports IronXL
Imports System.Data
Write the following code:
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("Weather.xlsx"); // Your Excel file Name
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Parse worksheet into datatable
DataTable dt = ws.ToDataTable(true); // Parse Sheet1 of sample.xlsx file into DataTable
// Iterate through rows and columns to display their values
foreach (DataRow row in dt.Rows) // Access rows
{
for (int i = 0; i < dt.Columns.Count; i++) // Access columns of corresponding row
{
Console.Write(row[i] + " ");
}
Console.WriteLine();
}
}
}
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("Weather.xlsx"); // Your Excel file Name
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Parse worksheet into datatable
DataTable dt = ws.ToDataTable(true); // Parse Sheet1 of sample.xlsx file into DataTable
// Iterate through rows and columns to display their values
foreach (DataRow row in dt.Rows) // Access rows
{
for (int i = 0; i < dt.Columns.Count; i++) // Access columns of corresponding row
{
Console.Write(row[i] + " ");
}
Console.WriteLine();
}
}
}
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load the workbook and access a specific worksheet
Dim wb As WorkBook = WorkBook.Load("Weather.xlsx") ' Your Excel file Name
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
' Parse worksheet into datatable
Dim dt As DataTable = ws.ToDataTable(True) ' Parse Sheet1 of sample.xlsx file into DataTable
' Iterate through rows and columns to display their values
For Each row As DataRow In dt.Rows ' Access rows
For i As Integer = 0 To dt.Columns.Count - 1 ' Access columns of corresponding row
Console.Write(row(i) & " ")
Next i
Console.WriteLine()
Next row
End Sub
End Class
Console output from a DataTable object
In this example, we will look at how to use an Excel file as a DataSet
.
class Program
{
static void Main(string[] args)
{
// Load the workbook and convert it to a DataSet
WorkBook wb = WorkBook.Load("sample.xlsx");
DataSet ds = wb.ToDataSet(); // Parse WorkBook wb into DataSet
// Iterate through tables to display their names
foreach (DataTable dt in ds.Tables)
{
Console.WriteLine(dt.TableName);
}
}
}
class Program
{
static void Main(string[] args)
{
// Load the workbook and convert it to a DataSet
WorkBook wb = WorkBook.Load("sample.xlsx");
DataSet ds = wb.ToDataSet(); // Parse WorkBook wb into DataSet
// Iterate through tables to display their names
foreach (DataTable dt in ds.Tables)
{
Console.WriteLine(dt.TableName);
}
}
}
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load the workbook and convert it to a DataSet
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ds As DataSet = wb.ToDataSet() ' Parse WorkBook wb into DataSet
' Iterate through tables to display their names
For Each dt As DataTable In ds.Tables
Console.WriteLine(dt.TableName)
Next dt
End Sub
End Class
Access sheet name from DataSet object
Let's look at another example of how to access each cell value across all Excel sheets. Here, we can access each cell value of every WorkSheet in an Excel file.
class Program
{
static void Main(string[] args)
{
// Load the workbook and convert it to a DataSet
WorkBook wb = WorkBook.Load("Weather.xlsx");
DataSet ds = wb.ToDataSet(); // Treat the complete Excel file as DataSet
// Iterate through each table and its rows and columns
foreach (DataTable dt in ds.Tables) // Treat Excel WorkSheet as DataTable
{
foreach (DataRow row in dt.Rows) // Corresponding Sheet's Rows
{
for (int i = 0; i < dt.Columns.Count; i++) // Sheet columns of corresponding row
{
Console.Write(row[i] + " ");
}
Console.WriteLine();
}
}
}
}
class Program
{
static void Main(string[] args)
{
// Load the workbook and convert it to a DataSet
WorkBook wb = WorkBook.Load("Weather.xlsx");
DataSet ds = wb.ToDataSet(); // Treat the complete Excel file as DataSet
// Iterate through each table and its rows and columns
foreach (DataTable dt in ds.Tables) // Treat Excel WorkSheet as DataTable
{
foreach (DataRow row in dt.Rows) // Corresponding Sheet's Rows
{
for (int i = 0; i < dt.Columns.Count; i++) // Sheet columns of corresponding row
{
Console.Write(row[i] + " ");
}
Console.WriteLine();
}
}
}
}
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load the workbook and convert it to a DataSet
Dim wb As WorkBook = WorkBook.Load("Weather.xlsx")
Dim ds As DataSet = wb.ToDataSet() ' Treat the complete Excel file as DataSet
' Iterate through each table and its rows and columns
For Each dt As DataTable In ds.Tables ' Treat Excel WorkSheet as DataTable
For Each row As DataRow In dt.Rows ' Corresponding Sheet's Rows
For i As Integer = 0 To dt.Columns.Count - 1 ' Sheet columns of corresponding row
Console.Write(row(i) & " ")
Next i
Console.WriteLine()
Next row
Next dt
End Sub
End Class
Console output of Dataset object
CSV files have an abundance of issues with how line breaks are handled in fields, or how fields can be contained in quotes that completely block a simple string split approach. I have recently discovered the following options when converting CSV in C# .NET by specifying a customizable delimiter instead of using string.Split(',')
to separate the values in a comma.
This process advances the reader through the next file. We read the CSV field files in TryGetField. We use the read function on field fields of the CSV files as record fields.
// Load a CSV file, specify the file format and delimiter
WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
// Access the default worksheet from the workbook
WorkSheet ws = workbook.DefaultWorkSheet;
// Convert worksheet to DataTable
DataTable dt = ws.ToDataTable(true); // Parse Sheet1 of sample.xlsx file into DataTable
// Iterate through rows and columns to display their values
foreach (DataRow row in dt.Rows) // Access rows
{
for (int i = 0; i < dt.Columns.Count; i++) // Access columns of corresponding row
{
Console.Write(row[i] + " ");
}
Console.WriteLine();
}
// Load a CSV file, specify the file format and delimiter
WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
// Access the default worksheet from the workbook
WorkSheet ws = workbook.DefaultWorkSheet;
// Convert worksheet to DataTable
DataTable dt = ws.ToDataTable(true); // Parse Sheet1 of sample.xlsx file into DataTable
// Iterate through rows and columns to display their values
foreach (DataRow row in dt.Rows) // Access rows
{
for (int i = 0; i < dt.Columns.Count; i++) // Access columns of corresponding row
{
Console.Write(row[i] + " ");
}
Console.WriteLine();
}
' Load a CSV file, specify the file format and delimiter
Dim workbook As WorkBook = WorkBook.LoadCSV("Weather.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
' Access the default worksheet from the workbook
Dim ws As WorkSheet = workbook.DefaultWorkSheet
' Convert worksheet to DataTable
Dim dt As DataTable = ws.ToDataTable(True) ' Parse Sheet1 of sample.xlsx file into DataTable
' Iterate through rows and columns to display their values
For Each row As DataRow In dt.Rows ' Access rows
For i As Integer = 0 To dt.Columns.Count - 1 ' Access columns of corresponding row
Console.Write(row(i) & " ")
Next i
Console.WriteLine()
Next row
Console output from DataTable
Now we can easily get any type of data, using a variety of methods, from the open Excel WorkSheet
. In the following example, we can see how to access a specific cell value and parse it to string
:
// Access the data by cell addressing
string val = ws["Cell Address"].ToString();
// Access the data by cell addressing
string val = ws["Cell Address"].ToString();
' Access the data by cell addressing
Dim val As String = ws("Cell Address").ToString()
In the line above, ws
is the WorkSheet
, which was defined in Step 2. This is 'the simple' approach, but you can read more and see different examples of how to access Excel file data.
When using Excel Spreadsheets for application builds, we often analyze the results based on data and need to parse Excel file data within C# into the required format to get the right results. Parsing data into different formats is made easy in the C# environment with the use of IronXL; see the steps below.
using IronXL;
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Parse Excel cell value into string
string str_val = ws["B3"].Value.ToString();
// Parse Excel cell value into Int32
Int32 int32_val = ws["G3"].Int32Value;
// Parse Excel cell value into Decimal
decimal decimal_val = ws["E5"].DecimalValue;
// Output parsed values to the console
Console.WriteLine("Parse B3 Cell Value into String: {0}", str_val);
Console.WriteLine("Parse G3 Cell Value into Int32: {0}", int32_val);
Console.WriteLine("Parse E5 Cell Value into decimal: {0}", decimal_val);
Console.ReadKey();
}
}
using IronXL;
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Parse Excel cell value into string
string str_val = ws["B3"].Value.ToString();
// Parse Excel cell value into Int32
Int32 int32_val = ws["G3"].Int32Value;
// Parse Excel cell value into Decimal
decimal decimal_val = ws["E5"].DecimalValue;
// Output parsed values to the console
Console.WriteLine("Parse B3 Cell Value into String: {0}", str_val);
Console.WriteLine("Parse G3 Cell Value into Int32: {0}", int32_val);
Console.WriteLine("Parse E5 Cell Value into decimal: {0}", decimal_val);
Console.ReadKey();
}
}
Imports IronXL
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load the workbook and access a specific worksheet
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
' Parse Excel cell value into string
Dim str_val As String = ws("B3").Value.ToString()
' Parse Excel cell value into Int32
Dim int32_val As Int32 = ws("G3").Int32Value
' Parse Excel cell value into Decimal
Dim decimal_val As Decimal = ws("E5").DecimalValue
' Output parsed values to the console
Console.WriteLine("Parse B3 Cell Value into String: {0}", str_val)
Console.WriteLine("Parse G3 Cell Value into Int32: {0}", int32_val)
Console.WriteLine("Parse E5 Cell Value into decimal: {0}", decimal_val)
Console.ReadKey()
End Sub
End Class
Now we move to how to parse Excel file data. First, we look at how to deal with numeric Excel data, and then how to parse it into our required format.
Summary table for each data type
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Parse Excel cell value into string
string str_val = ws["B3"].Value.ToString();
// Parse Excel cell value into Int32
Int32 int32_val = ws["G3"].Int32Value;
// Parse Excel cell value into Decimal
decimal decimal_val = ws["E5"].DecimalValue;
// Output parsed values to the console
Console.WriteLine("Parse B3 Cell Value into String: {0}", str_val);
Console.WriteLine("Parse G3 Cell Value into Int32: {0}", int32_val);
Console.WriteLine("Parse E5 Cell Value into decimal: {0}", decimal_val);
Console.ReadKey();
}
}
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Parse Excel cell value into string
string str_val = ws["B3"].Value.ToString();
// Parse Excel cell value into Int32
Int32 int32_val = ws["G3"].Int32Value;
// Parse Excel cell value into Decimal
decimal decimal_val = ws["E5"].DecimalValue;
// Output parsed values to the console
Console.WriteLine("Parse B3 Cell Value into String: {0}", str_val);
Console.WriteLine("Parse G3 Cell Value into Int32: {0}", int32_val);
Console.WriteLine("Parse E5 Cell Value into decimal: {0}", decimal_val);
Console.ReadKey();
}
}
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load the workbook and access a specific worksheet
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
' Parse Excel cell value into string
Dim str_val As String = ws("B3").Value.ToString()
' Parse Excel cell value into Int32
Dim int32_val As Int32 = ws("G3").Int32Value
' Parse Excel cell value into Decimal
Dim decimal_val As Decimal = ws("E5").DecimalValue
' Output parsed values to the console
Console.WriteLine("Parse B3 Cell Value into String: {0}", str_val)
Console.WriteLine("Parse G3 Cell Value into Int32: {0}", int32_val)
Console.WriteLine("Parse E5 Cell Value into decimal: {0}", decimal_val)
Console.ReadKey()
End Sub
End Class
This code will display the following output:
Console output with correct data type
And we can see the values of the Excel file sample.xlsx here:
Display correct data type in Excel
To parse Excel file data into Boolean data type, IronXL provides the BoolValue
function. It can be used as follows:
// Access a cell value as a boolean
bool Val = ws["Cell Address"].BoolValue;
// Access a cell value as a boolean
bool Val = ws["Cell Address"].BoolValue;
' Access a cell value as a boolean
Dim Val As Boolean = ws("Cell Address").BoolValue
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Convert a range into an array
var array = ws["B6:F6"].ToArray();
// Get the count of items in the array
int item = array.Count();
// Get the first item as a string
string total_items = array[0].Value.ToString();
// Output information about the array to the console
Console.WriteLine("First item in the array: {0}", item);
Console.WriteLine("Total items from B6 to F6: {0}", total_items);
Console.ReadKey();
}
}
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Convert a range into an array
var array = ws["B6:F6"].ToArray();
// Get the count of items in the array
int item = array.Count();
// Get the first item as a string
string total_items = array[0].Value.ToString();
// Output information about the array to the console
Console.WriteLine("First item in the array: {0}", item);
Console.WriteLine("Total items from B6 to F6: {0}", total_items);
Console.ReadKey();
}
}
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load the workbook and access a specific worksheet
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
' Convert a range into an array
Dim array = ws("B6:F6").ToArray()
' Get the count of items in the array
Dim item As Integer = array.Count()
' Get the first item as a string
Dim total_items As String = array(0).Value.ToString()
' Output information about the array to the console
Console.WriteLine("First item in the array: {0}", item)
Console.WriteLine("Total items from B6 to F6: {0}", total_items)
Console.ReadKey()
End Sub
End Class
WorkSheet
into a DataTableOne excellent feature of IronXL is that we can easily convert a specific Excel WorkSheet
into a DataTable. For this purpose, we can use the .ToDataTable()
function of IronXL as follows:
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Parse Sheet1 of sample.xlsx file into DataTable
// Setting 'true' makes the first row in Excel as the column names in DataTable
DataTable dt = ws.ToDataTable(true);
}
}
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Parse Sheet1 of sample.xlsx file into DataTable
// Setting 'true' makes the first row in Excel as the column names in DataTable
DataTable dt = ws.ToDataTable(true);
}
}
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load the workbook and access a specific worksheet
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
' Parse Sheet1 of sample.xlsx file into DataTable
' Setting 'true' makes the first row in Excel as the column names in DataTable
Dim dt As DataTable = ws.ToDataTable(True)
End Sub
End Class
If we want to parse a complete Excel file into a DataSet, then for this purpose we can use the .ToDataSet()
function in IronXL.
class Program
{
static void Main(string[] args)
{
// Load an entire workbook into a DataSet
WorkBook wb = WorkBook.Load("sample.xlsx");
// Convert workbook to DataSet
DataSet ds = wb.ToDataSet();
// We can also get a DataTable from the DataSet which corresponds to a WorkSheet
DataTable dt = ds.Tables[0];
}
}
class Program
{
static void Main(string[] args)
{
// Load an entire workbook into a DataSet
WorkBook wb = WorkBook.Load("sample.xlsx");
// Convert workbook to DataSet
DataSet ds = wb.ToDataSet();
// We can also get a DataTable from the DataSet which corresponds to a WorkSheet
DataTable dt = ds.Tables[0];
}
}
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load an entire workbook into a DataSet
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
' Convert workbook to DataSet
Dim ds As DataSet = wb.ToDataSet()
' We can also get a DataTable from the DataSet which corresponds to a WorkSheet
Dim dt As DataTable = ds.Tables(0)
End Sub
End Class
IronXL provides an intelligent method for reading Excel file data within a specific range. The range can be applied to both rows and columns.
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Get specified range values by loop
foreach (var item in ws["B3:B8"])
{
Console.WriteLine("Value is: {0}", item);
}
Console.ReadKey();
}
}
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Get specified range values by loop
foreach (var item in ws["B3:B8"])
{
Console.WriteLine("Value is: {0}", item);
}
Console.ReadKey();
}
}
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load the workbook and access a specific worksheet
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
' Get specified range values by loop
For Each item In ws("B3:B8")
Console.WriteLine("Value is: {0}", item)
Next item
Console.ReadKey()
End Sub
End Class
The above code displays the following output:
Console output to access all values in range B3:B8
And produces the Excel file sample.xlsx values:
Data display from sample.xlsx
Additionally, IronXL is also compatible with many Excel methods to interact with cells including styling and border, math functions, conditional formatting or creating charts from available data.
In application development, we need to make decisions based on the Boolean data type in Excel files.
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Traverse a range and output boolean values
foreach (var item in ws["G1:G10"])
{
Console.WriteLine("Condition is: {0}", item.BoolValue);
}
Console.ReadKey();
}
}
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Traverse a range and output boolean values
foreach (var item in ws["G1:G10"])
{
Console.WriteLine("Condition is: {0}", item.BoolValue);
}
Console.ReadKey();
}
}
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load the workbook and access a specific worksheet
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
' Traverse a range and output boolean values
For Each item In ws("G1:G10")
Console.WriteLine("Condition is: {0}", item.BoolValue)
Next item
Console.ReadKey()
End Sub
End Class
From this, we get the output:
Console output from getting Boolean Data
And the Excel file sample.xlsx with values from C1 to C10:
Excel sample to compare with Console output
It is simple to read a complete Excel WorkSheet by using rows and columns indexes. For this purpose, we use two loops: one for traversing all rows, and the second for traversing all columns of a specific row. Then we can easily obtain all the cell values within the entire Excel WorkSheet.
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("Weather.xlsx"); // Your Excel File Name
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Traverse all rows of Excel WorkSheet
for (int i = 0; i < ws.Rows.Count(); i++)
{
// Traverse all columns of specific Row
for (int j = 0; j < ws.Columns.Count(); j++)
{
// Get the values
string val = ws.Rows[i].Columns[j].Value.ToString();
Console.WriteLine("Value of Row {0} and Column {1} is: {2}", i, j, val);
}
}
Console.ReadKey();
}
}
class Program
{
static void Main(string[] args)
{
// Load the workbook and access a specific worksheet
WorkBook wb = WorkBook.Load("Weather.xlsx"); // Your Excel File Name
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Traverse all rows of Excel WorkSheet
for (int i = 0; i < ws.Rows.Count(); i++)
{
// Traverse all columns of specific Row
for (int j = 0; j < ws.Columns.Count(); j++)
{
// Get the values
string val = ws.Rows[i].Columns[j].Value.ToString();
Console.WriteLine("Value of Row {0} and Column {1} is: {2}", i, j, val);
}
}
Console.ReadKey();
}
}
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load the workbook and access a specific worksheet
Dim wb As WorkBook = WorkBook.Load("Weather.xlsx") ' Your Excel File Name
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
' Traverse all rows of Excel WorkSheet
For i As Integer = 0 To ws.Rows.Count() - 1
' Traverse all columns of specific Row
For j As Integer = 0 To ws.Columns.Count() - 1
' Get the values
Dim val As String = ws.Rows(i).Columns(j).Value.ToString()
Console.WriteLine("Value of Row {0} and Column {1} is: {2}", i, j, val)
Next j
Next i
Console.ReadKey()
End Sub
End Class
Console output from reading all values
IronXL is an Excel Library for C# and .NET which allows developers to read and edit Excel data from XLS and XLSX Documents without using Microsoft.Office.Interop.Excel.
The API allows us to create, read, manipulate, save and export Excel files intuitively for:
Add the following namespaces:
using IronXL;
using System;
using System.Linq;
using IronXL;
using System;
using System.Linq;
Imports IronXL
Imports System
Imports System.Linq
Now write the following code inside the main function.
class Program
{
static void Main(string[] args)
{
// Load an Excel file and access the first worksheet
WorkBook workbook = WorkBook.Load("Weather.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);
}
}
}
class Program
{
static void Main(string[] args)
{
// Load an Excel file and access the first worksheet
WorkBook workbook = WorkBook.Load("Weather.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);
}
}
}
Friend Class Program
Shared Sub Main(ByVal args() As String)
' Load an Excel file and access the first worksheet
Dim workbook As WorkBook = WorkBook.Load("Weather.xlsx")
Dim sheet As WorkSheet = workbook.WorkSheets.First()
' Select cells easily in Excel notation and return the calculated value
Dim 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
End Sub
End Class
Console output from each cell
IronXL also fully supports ASP.NET, MVC, Windows, macOS, Linux, iOS, and Android Mobile application development.
In addition to CSV parsing in C#, IronXL converts CSV files to Excel with just two lines of code!
Using C# or VB.NET, it is very easy to use IronXL's Excel API without the need for Interop. You can read, edit, and create Excel spreadsheets or work with other Excel formats such as XLS/XLSX/CSV/TSV. With the support of multiple frameworks, you can buy 5 products for the price of two. Click on our pricing page for further information.
5 products of Iron Suite
CSV is a simple data format with several delimiters used for distinguishing between rows and columns, making it difficult to read programmatically in C# projects.
To read a CSV file in C# using IronXL, you need to install the IronXL library via NuGet in Visual Studio, then use the WorkBook.LoadCSV method to load and interpret the CSV file.
Before using IronXL in a C# project, you need to install it via NuGet Package Manager in Visual Studio. The library supports .NET Framework 4.5+, .NET Core 2+, and other platforms.
You can convert a worksheet to a DataTable using the WorkSheet.ToDataTable method, which parses the worksheet into a DataTable object.
You can read data from a specific range of cells in an Excel sheet using a loop to iterate through the desired range, utilizing IronXL's indexing capabilities.
Yes, IronXL allows parsing Excel cell values into numeric (e.g., Int32, Decimal) and boolean data types using methods like Int32Value and BoolValue.
To read a complete Excel worksheet, you can use nested loops to traverse all rows and columns, extracting cell values using IronXL's methods.
No, IronXL does not require Microsoft Office Interop. It is a standalone library that allows developers to read and edit Excel data without using Microsoft.Office.Interop.Excel.
You can install IronXL by using the NuGet Package Manager in Visual Studio. Search for IronXL.Excel and install it in your project.
IronXL offers benefits like easy installation, no dependency on Interop, support for multiple Excel formats (XLS, XLSX, CSV, TSV), and compatibility with various .NET frameworks and platforms.