Hello guys 🙂
It is very common for applications to have import or export in excel format. There are plenty options that you can use to read or write an excel file in .net. Today I am going to introduce you two simple libraries that are open source and can help you in reading and writing excel files in any type of .net applications.
Here is a simple code that create an excel file by using EPPluslibrary, you can download the full source code directly from my git-hub
Before you start coding you need to make sure that you have imported these libraries :
using Excel; using OfficeOpenXml; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks;
Creating Excel:
public string CreateExcelFile(DirectoryInfo outputDir) { FileInfo newFile = new FileInfo(outputDir.FullName + @"\sample1.xlsx"); if (newFile.Exists) { newFile.Delete(); // ensures we create a new workbook newFile = new FileInfo(outputDir.FullName + @"\sample1.xlsx"); } using (ExcelPackage package = new ExcelPackage(newFile)) { // add a new worksheet to the empty workbook ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Student Lists"); //Here column to the current sheet worksheet.Cells[1, 1].Value = "Title"; worksheet.Cells[1, 2].Value = "Name"; worksheet.Cells[1, 3].Value = "Family"; worksheet.Cells[1, 4].Value = "Student Code"; //Add value to celles worksheet.Cells[2, 1].Value = "Mr."; worksheet.Cells[2, 2].Value = "Mehran"; worksheet.Cells[2, 3].Value = "Janfeshan"; worksheet.Cells[2, 4].Value = "ST54516"; worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); // add the sheet name to the footer worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName; //Save and export excel file package.Save(); } return newFile.FullName; }
Reading Excel :
public DataSet ReadExcel() { string filePath; filePath = "C:\\sample1.xlsx"; FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read); IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); excelReader.IsFirstRowAsColumnNames = true; DataSet result = excelReader.AsDataSet(); excelReader.Close(); return result; }
Please contact me if you have any issue with the code.