How to open Excel in WinForms using C#, VB.NET?
The Syncfusion® Excel (XlsIO) library is a .NET Excel Library used to create, open, and edit Excel documents without the dependency of the interop Excel library. Additionally, it converts Excel documents to PDF files and much more.
This article demonstrates how to open an Excel file and read Excel data in C# and VB.NET by using the Syncfusion Excel (XlsIO) library.
Steps to open an Excel file and read Excel data programmatically:
Step 1: Create a new C# console application project.
Create a new C# console application
Step 2: Install the Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework applications from the NuGet.org.
Install XlsIO NuGet package
Step 3: Include the following namespace in the Program.cs file.
C#
using Syncfusion.XlsIO;
VB.NET
Imports Syncfusion.XlsIO
Step 4: Add the below code snippet to open Excel file and read its data. The description of some important class members used in the code example are as follows:
ExcelEngine should be initialized to access the Excel object.
The Open() method of IWorkbooks interface opens the existing Excel workbook from the specified location. Moreover, you can also open the specified stream.
You can access any of the Worksheets with its zero-based index for further manipulations.
String values can be read from a cell range using the Text property.
Likewise, number values can be read from a cell range using the Number property.
Date and time values can be read from a cell using the DateTime property.
Excel formulas can be read using the Formula property.
The EnableSheetCalculations() method of IWorksheet interface should be invoked to perform calculation in an Excel workbook, and to evaluate formula.
Excel formulas can be evaluated using the CalculatedValue property.
The DisplayText property can be used to retrieve the resultant value of a cell with its number format applied.
Finally, the SaveAs() method can be used to save the Excel file to the disk.
The following code snippet in C# and VB.NET opens an Excel file, prints the different types of data present in the Excel file, and saves the loaded Excel file using XlsIO.
C#
// Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
// Initialize application
IApplication app = excelEngine.Excel;
// Set default application version as Excel 2016
app.DefaultVersion = ExcelVersion.Excel2016;
// Open existing Excel workbook from the specified location
string inputFileName = "Sample.xlsx";
IWorkbook workbook = app.Workbooks.Open(inputFileName, ExcelOpenType.Automatic);
// Access the first worksheet
IWorksheet worksheet = workbook.Worksheets[0];
// Read number value
int id = (int)worksheet["A2"].Number;
// Read text value
string name = worksheet["B2"].Text;
// Read date value
DateTime dob = worksheet["C2"].DateTime;
// Read formula
string ageFormula = worksheet["D2"].Formula;
// Enable calculations
worksheet.EnableSheetCalculations();
// Read calculated value
string formulaValue = worksheet["D2"].CalculatedValue;
// Get display text
string displayText = worksheet["C2"].DisplayText;
// Save the Excel workbook to disk in xlsx format
workbook.SaveAs("Output.xlsx");
// Print the values that are read from the Excel file
Console.WriteLine("Number in cell A2: {0}", id);
Console.WriteLine("Text in cell B2: {0}", name);
Console.WriteLine("DateTime in cell C2: {0}", dob);
Console.WriteLine("Formula in cell D2: {0}", ageFormula);
Console.WriteLine("Calculated formula value in cell D2: {0}", formulaValue);
Console.WriteLine("Display text in cell C2: {0}", displayText);
Console.Read();
}
VB.NET
'Instantiate the spreadsheet creation engine
Using excelEngine As ExcelEngine = New ExcelEngine
'Initialize application
Dim app As IApplication = excelEngine.Excel
'Set default application version as Excel 2016
app.DefaultVersion = ExcelVersion.Excel2016
'Open existing Excel workbook from the specified location
Dim inputFileName As String = "Sample.xlsx"
Dim workbook As IWorkbook = app.Workbooks.Open(inputFileName, ExcelOpenType.Automatic)
'Access the first worksheet
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Read number value
Dim id As Integer = CInt(worksheet("A2").Number)
'Read text value
Dim name As String = worksheet("B2").Text
'Read date value
Dim dob As DateTime = worksheet("C2").DateTime
'Read formula
Dim ageFormula As String = worksheet("D2").Formula
'Enable calculations
worksheet.EnableSheetCalculations()
'Read calculated value
Dim formulaValue As String = worksheet("D2").CalculatedValue
'Get display text
Dim displayText As String = worksheet("C2").DisplayText
'Save the Excel workbook to disk in xlsx format
workbook.SaveAs("Output.xlsx")
'Print the values that are read from the Excel file
Console.WriteLine("Number in cell A2: {0}", id)
Console.WriteLine("Text in cell B2: {0}", name)
Console.WriteLine("DateTime in cell C2: {0}", dob)
Console.WriteLine("Formula in cell D2: {0}", ageFormula)
Console.WriteLine("Calculated formula value in cell D2: {0}", formulaValue)
Console.WriteLine("Display text in cell C2: {0}", displayText)
Console.Read()
End Using
A complete working example to open Excel file and read its data from code along with the input Excel file used can be downloaded from Open Excel File.zip.
By executing the program, you will get the loaded and saved Excel file as shown below.
Opened and saved Excel document
Also, the values are read from the Excel file and printed in the output console window as follows.
Read Excel data in console output window
Take a moment to explore the rich set of Syncfusion® Excel (XlsIO) library features.
Besides, here is an online sample link to open Excel file.
To learn more about the Syncfusion® Excel (XlsIO) library, refer to the documentation where you will find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheets or workbooks, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions with code examples.
See Also:
Create an Excel file in C# and VB.NET
Convert Excel file to CSV in C# and VB.NET
Create Excel from DataTable in C# and VB.NET
Insert the Excel rows and columns in C# and VB.NET
Copy an Excel worksheet to another workbook in C# and VB.NET
Merge or combine multiple Excel files in C# and VB.NET
Create an Excel file in ASP.NET Core
Create an Excel file in ASP.NET MVC
Create an Excel file in ASP.NET Web Forms
Create an Excel file in Xamarin
Create an Excel file in Xamarin.Android
Create an Excel file in Xamarin.iOS
Create an Excel file in Azure platform
Starting with v16.2.0.x, if you reference Syncfusion® assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering Syncfusion® license key in your application to use the components without trial message.
Conclusion
I hope you enjoyed learning about how to open Excel file in C#, VB.NET using XIsIO.
You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.
If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion® components.
If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!