Photo by Campaign CreatorsOriginally 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 IronXLFirstly 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 DocumentReading 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 TSV
- WorkBook workbook = WorkBook.Load("data.xlsx");
- WorkSheet sheet = workbook.WorkSheets.First();
- //Select cells easily in Excel notation and return the calculated value, date, text or formula
- int 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);
- }
Copy code to clipboardVB
C#
Creating New Excel DocumentsTo 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 WorkSheet
- WorkSheet xlsSheet = xlsxWorkbook.CreateWorkSheet("main_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
- xlsxWorkbook.SaveAs("NewExcelFile.xlsx");
Copy code to clipboardVB
C#
Exporting as CSV, XLS, XLSX, JSON or XMLWe can also save or export as many common structured spreadsheet file formats.
- //Export to many formats with fluent saving
- xlsxWorkbook.SaveAs("NewExcelFile.xls");
- xlsxWorkbook.SaveAs("NewExcelFile.xlsx");
- xlsxWorkbook.SaveAsCsv("NewExcelFile.csv");
- xlsxWorkbook.SaveAsJson("NewExcelFile.json");
- xlsxWorkbook.SaveAsXml("NewExcelFile.xml");
Copy code to clipboardVB
C#
Styling Cells and RangesExcel 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;
Copy code to clipboardVB
C#
Sorting RangesUsing 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 worksheet
- Range range = sheet["A2:A8"];
- //Sort the range in the sheet
- range.SortAscending();
- workbook.Save("test.xls")
Copy code to clipboardVB
C#
Editing FormaulasEditing 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 formula
- xlsSheet["A1"].Value = "=SUM(A2:A10)";
- //get the calculated value
- decimal sum = xlsSheet["A1"].DecimalValue
Copy code to clipboardVB
C#
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 ForwardTo 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.
