Category: ASP.Net

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.