Top
ArticleCity.comArticle Categories Excel Spreadsheet Files in C# & VB.Net Applications

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.Excel

https://www.nuget.org/packages/ironxl.excel/

Reading an Excel Document

Reading data from an excel file with IronXL takes a few lines of code.

  1. using IronXL;
  2. //Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
  3. WorkBook workbook = WorkBook.Load("data.xlsx");
  4. WorkSheet sheet = workbook.WorkSheets.First();
  5. //Select cells easily in Excel notation and return the calculated value, date, text or formula
  6. int cellValue = sheet["A2"].IntValue;
  7. // Read from Ranges of cells elegantly.
  8. foreach (var cell in sheet["A2:B10"])
  9. {
  10. Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
  11. }

 

 

Creating New Excel Documents

To create Excel documents in C# or VB.Net; IronXL provides a simple, fast interface.

  1. using IronXL;
  2. //Create new Excel WorkBook document.
  3. WorkBook xlsxWorkbook = WorkBook.Create(ExcelFileFormat.XLSX);
  4. xlsxWorkbook.Metadata.Author = "IronXL";
  5. //Add a blank WorkSheet
  6. WorkSheet xlsSheet = xlsxWorkbook.CreateWorkSheet("main_sheet");
  7. //Add data and styles to the new worksheet
  8. xlsSheet["A1"].Value = "Hello World";
  9. xlsSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
  10. xlsSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;
  11. //Save the excel file
  12. xlsxWorkbook.SaveAs("NewExcelFile.xlsx");

 

 

Exporting as CSV, XLS, XLSX, JSON or XML

We can also save or export as many common structured spreadsheet file formats.

  1. //Export to many formats with fluent saving
  2. xlsxWorkbook.SaveAs("NewExcelFile.xls");
  3. xlsxWorkbook.SaveAs("NewExcelFile.xlsx");
  4. xlsxWorkbook.SaveAsCsv("NewExcelFile.csv");
  5. xlsxWorkbook.SaveAsJson("NewExcelFile.json");
  6. xlsxWorkbook.SaveAsXml("NewExcelFile.xml");

 

 

Styling Cells and Ranges

Excel cells and ranges can be styled using the IronXL.Range.Style object.

  1. //...
  2. xlsSheet["A1"].Value = "Hello World";
  3. xlsSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
  4. xlsSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;

 

 

Sorting Ranges

Using an IronXL we can sort a range of Excel Cells using Range.

  1. using IronXL;
  2. using System.Linq;
  3. WorkBook workbook = WorkBook.Load("test.xls");
  4. WorkSheet sheet = workbook.WorkSheets.First();
  5. //This is how we get range from Excel worksheet
  6. Range range = sheet["A2:A8"];
  7. //Sort the range in the sheet
  8. range.SortAscending();
  9. 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.

  1. //set a formula
  2. xlsSheet["A1"].Value = "=SUM(A2:A10)";
  3. //get the calculated value
  4. decimal 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.

No Comments

Sorry, the comment form is closed at this time.