Excel Spreadsheet Files in C# & VB.Net Applications
Photo by Campaign Creators
Originally Posted On: How To Read and Write Excel Documents in C# and VB.Net | IronXL (ironsoftware.com)
Reading and creating Excel (XLS, XLSX and CSV) files s in C# and all other .NET languages is easy using the IronXL software library from Iron Software.
IronXL does not require Excel to be installed on your server or Interop. IronXL provides a faster and more intuitive API than Microsoft.Office.Interop.Excel.
IronXL works on .Net Core 2, Framework 4.5, Azure, Mono and, Mobile and Xamarin.
Install IronXL
Firstly install IronXL, using our NuGet package or by downloading the DLL. IronXL classes can be found in the IronXL namespace.
The easiest way to install IronXL is using the NuGet Package Manager for Visual-Studio: The package name is IronXL.Excel.
PM > Install-Package IronXL.Excelhttps://www.nuget.org/packages/ironxl.excel/
Reading an Excel Document
Reading data from an excel file with IronXL takes a few lines of code.
using IronXL;//Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSVWorkBook workbook = WorkBook.Load("data.xlsx");WorkSheet sheet = workbook.WorkSheets.First();//Select cells easily in Excel notation and return the calculated value, date, text or formulaint cellValue = sheet["A2"].IntValue;// Read from Ranges of cells elegantly.foreach (var cell in sheet["A2:B10"]){Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);}
Creating New Excel Documents
To create Excel documents in C# or VB.Net; IronXL provides a simple, fast interface.
using IronXL;//Create new Excel WorkBook document.WorkBook xlsxWorkbook = WorkBook.Create(ExcelFileFormat.XLSX);xlsxWorkbook.Metadata.Author = "IronXL";//Add a blank WorkSheetWorkSheet xlsSheet = xlsxWorkbook.CreateWorkSheet("main_sheet");//Add data and styles to the new worksheetxlsSheet["A1"].Value = "Hello World";xlsSheet["A2"].Style.BottomBorder.SetColor("#ff6600");xlsSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;//Save the excel filexlsxWorkbook.SaveAs("NewExcelFile.xlsx");
Exporting as CSV, XLS, XLSX, JSON or XML
We can also save or export as many common structured spreadsheet file formats.
//Export to many formats with fluent savingxlsxWorkbook.SaveAs("NewExcelFile.xls");xlsxWorkbook.SaveAs("NewExcelFile.xlsx");xlsxWorkbook.SaveAsCsv("NewExcelFile.csv");xlsxWorkbook.SaveAsJson("NewExcelFile.json");xlsxWorkbook.SaveAsXml("NewExcelFile.xml");
Styling Cells and Ranges
Excel cells and ranges can be styled using the IronXL.Range.Style object.
//...xlsSheet["A1"].Value = "Hello World";xlsSheet["A2"].Style.BottomBorder.SetColor("#ff6600");xlsSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;
Sorting Ranges
Using an IronXL we can sort a range of Excel Cells using Range.
using IronXL;using System.Linq;WorkBook workbook = WorkBook.Load("test.xls");WorkSheet sheet = workbook.WorkSheets.First();//This is how we get range from Excel worksheetRange range = sheet["A2:A8"];//Sort the range in the sheetrange.SortAscending();workbook.Save("test.xls")
Editing Formaulas
Editing an Excel formula is as easy as assigning a value with an ‘=’ equals sign at the start. The formula will be calculated live.
//set a formulaxlsSheet["A1"].Value = "=SUM(A2:A10)";//get the calculated valuedecimal sum = xlsSheet["A1"].DecimalValue
Why Choose IronXL?
IronXL features an easy API for developers to read and write excel documents for .NET.
IronXL does not require installation of Microsoft Office Excel on your server or to use Excel Interop to achieve accessing Excel manipulation scripts. This makes working with Excel files in .Net, a very quick and simple task.
Moving Forward
To get more out of IronXL, we encourage you to read the documentation, to visit us on GitHub, and to read the .NET Object Reference in an MSDN format.