Export and Import Excel Data in C#

Back to: C#.NET Tutorials For Beginners and Professionals

Export and Import Excel Data in C# using the NPOI Library

In this article, I am going to discuss How to Export and Import Excel Data in C# with Examples using the NPOI Library. Please read our previous article, where we discussed the DirectoryInfo Class in C# with Examples. Whenever we are working on Real-Time Projects, it is a common requirement to Import data from an Excel File as well as to Export Data to an Excel File. So, here, first, we will discuss how to Export Excel Data, and then we will see How to Import Excel Data using NPOI Library.

What is NPOI?

NPOI is an Open-Source library that can help us perform read and write operations on XLS, DOC, and PPT file extensions. This NPOI Library is the .NET version of the POI Java project at http://poi.apache.org/. This NPOI Library covers almost all the important features of Excel, like styling, formatting, data formulas, extracting images, etc. And the most important thing is that it does not require Microsoft Office to be installed on the server. So, here, in this article, I will show you how to perform the Read and Write Operations on an Excel File using the NPOI Library.

Example to Understand Export Excel Data in C#:

Let us see an Example to Understand How to Export Excel Data in C# using the NPOI Library. So, we want to represent the Employee information of an organization in the following Excel Format.

As you can see in the above image, our Excel File is going to contain the following information.

On the Left-Hand Side top corner, we want to put the logo of the Company. On the right-hand side, we want to put the Company Name and Company Address. Here, we need to apply different Cell Styles for Company Name and Address.

Then, we need to provide the column headers, and for this, we are going to use different Cell Styles. After the Column Header, we have Data Rows, and for Data Rows, we have different cell styles. We have a different cell style for string data and another cell style for numeric data. Further, if you observe the ProfileURL column, we need to make that column value a Hyperlink, and on clicking on that hyperlink, it should open the appropriate URL on your browser. For this hyperlink column data, we have a different cell style.

Once the Data Rows are completed, we need to calculate the total salary. For this, we need to use Excel Formula, and for this, we are using a different cell style. The formula will be to sum all the column values of the Salary column and show the result on the Total Row.

Here, we also need to freeze the company logo, company name, company address, and headers while scrolling down to see the data rows. Let us proceed and see how to implement the above-discussed example in C# using the NPOI Library.

Export Excel Data Example in C# using NPOI Library:

So, create a Console application with the name WorkingWithExcel. Once you create the Project, then you need to install the NPOI Library from NuGet Package Manager. To do so, Click on Tools => NuGet Package Manager => Manage NuGet Packages for Solution, as shown in the below image.

Then go to the Browse tab, search for NPOI, and select the NPOI.Excel Library, select the project where you want to install the Library, and finally click on the Install button as shown in the below image.

Once you have Installed the NPOI Library, create a folder with the name Images, and inside this folder, please download and save the following image.

Next, create another folder with the name ExcelFiles and inside this folder, our generated Excel Files will be stored. Next, create a class file with the name Employee.cs and then copy and paste the following code. This is going to be our model class, which is going to hold the Employee data that we want to export into the Excel File.

namespace WorkingWithExcel
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string Email { get; set; }
        public string Mobile { get; set; }
        public bool IsPermanent { get; set; }
        public int RegdNo { get; set; }
        public int Salary { get; set; }
        public string ProfileURL { get; set; }
    }
}

Next, modify the Program class as follows. In the code below, we use the NPOI Library to Export the Data into an Excel file. The following Example code is self-explained, so please go through the comment lines for a better understanding.

using System;
using System.Collections.Generic;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using System.IO;
namespace WorkingWithExcel
{
public class Program
{
public static void Main()
{
try
{
#region Data
//In Real-Time you will get the data from the database,
//but here we have hard-coded the data
List<Employee> empList = new List<Employee>();
for (int i = 0; i < 100; i++)
{
Employee emp = new Employee()
{
ID = 100 + i,
Name = "Name-" + i,
Address = "Some Address",
Email = "SameEmail@dotnettutorials.net",
IsPermanent = true,
Mobile = "0123456789",
RegdNo = 12345 + i + 6789,
Salary = 10000 + i,
ProfileURL = "100" + i
};
empList.Add(emp);
}
#endregion
#region Creating Excel Sheet
//The following Pieces of Code will Create the Excel File, 
//Excel Sheet and font object which will be used later
HSSFWorkbook workbook = new HSSFWorkbook();
//The sheet name is going to be Dot Net Tutorials
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Dot Net Tutorials");
HSSFFont font = (HSSFFont)workbook.CreateFont();
#endregion
#region Creating Different Cell Styles
//Now, we going to create different cell styles for different data
#region Company Cell Styles
//We will use the following cell style with the Company Name
var Company = workbook.CreateCellStyle();
Company.Alignment = HorizontalAlignment.Left;
var CompanyFont = workbook.CreateFont();
CompanyFont.FontName = "Arial";
CompanyFont.Color = HSSFColor.Blue.Index;
CompanyFont.Boldweight = (short)FontBoldWeight.Bold;
CompanyFont.FontHeightInPoints = ((short)16);
Company.SetFont(CompanyFont);
#endregion
#region Address Cell Style
//We will use the following cell style with the Company Address
var Address = workbook.CreateCellStyle();
Address.Alignment = HorizontalAlignment.Left;
var AddressFont = workbook.CreateFont();
AddressFont.FontName = "Arial";
AddressFont.Boldweight = (short)FontBoldWeight.Bold;
AddressFont.FontHeightInPoints = ((short)10);
Address.SetFont(AddressFont);
#endregion
#region Header Cell Style
//We will use the following cell style with the Header
var Header = workbook.CreateCellStyle();
Header.Alignment = HorizontalAlignment.Center;
Header.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
Header.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
Header.FillPattern = FillPattern.SolidForeground;
var HeaderFont = workbook.CreateFont();
HeaderFont.FontName = "Arial";
HeaderFont.Boldweight = (short)FontBoldWeight.Bold;
HeaderFont.Color = HSSFColor.White.Index;
HeaderFont.FontHeightInPoints = ((short)10);
Header.SetFont(HeaderFont);
Header.BorderLeft = BorderStyle.Thin;
Header.BorderTop = BorderStyle.Thin;
Header.BorderRight = BorderStyle.Thin;
Header.BorderBottom = BorderStyle.Thin;
#endregion
#region Number Data Cell Style
//We will use the following cell style with Data which is Numeric
var NumData = workbook.CreateCellStyle();
var formatId = HSSFDataFormat.GetBuiltinFormat("##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
NumData.DataFormat = newDataFormat.GetFormat("##0.00");
}
else
NumData.DataFormat = formatId;
#endregion
#region Data Cell Style
//We will use the following cell style with Data that is NOT Numeric
var Data = workbook.CreateCellStyle();
Data.Alignment = HorizontalAlignment.Center;
var DataFont = workbook.CreateFont();
DataFont.FontName = "Arial";
DataFont.FontHeightInPoints = ((short)9);
Data.SetFont(DataFont);
Data.BorderLeft = BorderStyle.Thin;
Data.BorderTop = BorderStyle.Thin;
Data.BorderRight = BorderStyle.Thin;
Data.BorderBottom = BorderStyle.Thin;
#endregion
#region Link Data Cell Style
//We will use the following cell style with Hyperlink Data
var linkData = workbook.CreateCellStyle();
linkData.Alignment = HorizontalAlignment.Center;
var linkDataFont = workbook.CreateFont();
linkDataFont.FontName = "Arial";
linkDataFont.Color = HSSFColor.Blue.Index;
linkDataFont.FontHeightInPoints = ((short)9);
linkDataFont.Underline = FontUnderlineType.Single;
linkDataFont.Color = HSSFColor.Blue.Index;
linkData.SetFont(linkDataFont);
linkData.BorderLeft = BorderStyle.Thin;
linkData.BorderTop = BorderStyle.Thin;
linkData.BorderRight = BorderStyle.Thin;
linkData.BorderBottom = BorderStyle.Thin;
#endregion
#endregion
#region Creating Company and Address of the Excel Sheet
//Creating the Company Name from 2nd Row by applying Company Cell Style
// rowIndex is going to hold the Row Number. Inex means 0 - Based Index
int rowIndex = 2; //rowIndex 2 means 3rd Row
var row = sheet.CreateRow(rowIndex);
var cell = row.CreateCell(4);
cell.SetCellValue("Dot Net Tutorials Online Training");
cell.CellStyle = Company;
sheet.AddMergedRegion(new CellRangeAddress(4, 4, 4, 14));
//Creating the Company Address from 3rd Row by applying Address Cell Style
rowIndex = rowIndex + 1;
var row1 = sheet.CreateRow(rowIndex);
var cell1 = row1.CreateCell(4);
cell1.SetCellValue("1988/2019, 5th floor, Tower B, Bajrang Vihar, Patia, Bhubaneswar-400051, India");
cell1.CellStyle = Address;
sheet.AddMergedRegion(new CellRangeAddress(5, 5, 4, 14));
#endregion
// Set Row index for Header 
rowIndex = 7; //rowIndex 7 means 8th Row which is going to be our Header in Excel Sheet
var SR_NO = 0; //We want a unique Serial Number for Each Row in the Excel Sheet
#region Excel Data Headers
var cellheaderindex = 0;
var excelheaderrow = sheet.CreateRow(rowIndex);
var excelheadercell = excelheaderrow.CreateCell(cellheaderindex);
excelheadercell.SetCellValue("SR NO");
excelheadercell.CellStyle = Header;
cellheaderindex = cellheaderindex + 1;
excelheadercell = excelheaderrow.CreateCell(cellheaderindex);
excelheadercell.SetCellValue("ID");
excelheadercell.CellStyle = Header;
cellheaderindex = cellheaderindex + 1;
excelheadercell = excelheaderrow.CreateCell(cellheaderindex);
excelheadercell.SetCellValue("Name");
excelheadercell.CellStyle = Header;
cellheaderindex = cellheaderindex + 1;
excelheadercell = excelheaderrow.CreateCell(cellheaderindex);
excelheadercell.SetCellValue("Address");
excelheadercell.CellStyle = Header;
cellheaderindex = cellheaderindex + 1;
excelheadercell = excelheaderrow.CreateCell(cellheaderindex);
excelheadercell.SetCellValue("Email");
excelheadercell.CellStyle = Header;
cellheaderindex = cellheaderindex + 1;
excelheadercell = excelheaderrow.CreateCell(cellheaderindex);
excelheadercell.SetCellValue("IsPermanent");
excelheadercell.CellStyle = Header;
cellheaderindex = cellheaderindex + 1;
excelheadercell = excelheaderrow.CreateCell(cellheaderindex);
excelheadercell.SetCellValue("Mobile");
excelheadercell.CellStyle = Header;
cellheaderindex = cellheaderindex + 1;
excelheadercell = excelheaderrow.CreateCell(cellheaderindex);
excelheadercell.SetCellValue("RegdNo");
excelheadercell.CellStyle = Header;
cellheaderindex = cellheaderindex + 1;
excelheadercell = excelheaderrow.CreateCell(cellheaderindex);
excelheadercell.SetCellValue("Salary");
excelheadercell.CellStyle = Header;
cellheaderindex = cellheaderindex + 1;
excelheadercell = excelheaderrow.CreateCell(cellheaderindex);
excelheadercell.SetCellValue("ProfileURL");
excelheadercell.CellStyle = Header;
#endregion
#region Excel Data
foreach (Employee data in empList)
{
//Increase the rowIndex and SR_NO by 1 for each record in the empList
rowIndex = rowIndex + 1; //This will be the row number in the Excel Sheet
SR_NO = SR_NO + 1; //Unique Serial Number
var cellindex = 0; //Cell Number starting from 0
//Create the New Row
var gridrow = sheet.CreateRow(rowIndex);
//Create the first Cell in the new Row
var gridcell = gridrow.CreateCell(cellindex);
//Add value to the Cell 
gridcell.SetCellValue(SR_NO);
//Apply appropriate CSS Styles
gridcell.CellStyle = Data;
//Increse the Cell Index by 1 to create the next cell in the Row
cellindex = cellindex + 1;
//Create the new cell
gridcell = gridrow.CreateCell(cellindex);
//Add value to the Cell
gridcell.SetCellValue(data.ID);
//Apply appropriate CSS Styles
gridcell.CellStyle = Data;
//The Process will continue till the last cell in the Row
cellindex = cellindex + 1;
gridcell = gridrow.CreateCell(cellindex);
gridcell.SetCellValue(data.Name);
gridcell.CellStyle = Data;
cellindex = cellindex + 1;
gridcell = gridrow.CreateCell(cellindex);
gridcell.SetCellValue(data.Address);
gridcell.CellStyle = Data;
cellindex = cellindex + 1;
gridcell = gridrow.CreateCell(cellindex);
gridcell.SetCellValue(data.Email);
gridcell.CellStyle = Data;
cellindex = cellindex + 1;
gridcell = gridrow.CreateCell(cellindex);
gridcell.SetCellValue(data.IsPermanent);
gridcell.CellStyle = Data;
cellindex = cellindex + 1;
gridcell = gridrow.CreateCell(cellindex);
gridcell.SetCellValue(data.Mobile);
gridcell.CellStyle = Data;
cellindex = cellindex + 1;
gridcell = gridrow.CreateCell(cellindex);
gridcell.SetCellValue(data.RegdNo);
gridcell.CellStyle = Data;
cellindex = cellindex + 1;
gridcell = gridrow.CreateCell(cellindex);
gridcell.SetCellValue(Convert.ToDouble(data.Salary));
gridcell.CellStyle = NumData;
cellindex = cellindex + 1;
gridcell = gridrow.CreateCell(cellindex);
gridcell.SetCellValue(data.ProfileURL);
//Setting the Cell value as Hyper link
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url)
{
//On click on the Hyperlink, it will open the following URL in the browser
Address = "http://www.dotnettutorials.net/" + data.ProfileURL
};
gridcell.Hyperlink = (link);
gridcell.CellStyle = linkData;
}
#endregion
#region Freezing Point
//Setting the Freezing Point- Column 0 and Row Number 8
sheet.CreateFreezePane(0, 8, 0, 8);
for (int i = 0; i <= cellheaderindex; i++)
{
sheet.SetColumnWidth(i, 5000);
}
#endregion
#region TOTAL & FORMALA SECTION
//We need to calculate the sum of the salary column
var startrow = 9; //Data Rows started from Row Number 9
var lastdatarow = rowIndex + 1; //Last Row
rowIndex = rowIndex + 2; //Creating a New Rows to display the Total
var Formularow = sheet.CreateRow(rowIndex);
var Formulacell = Formularow.CreateCell(0);
Formulacell.SetCellValue("TOTAL");
Formulacell.CellStyle = Header;
//Creating a Cell to display the Total
Formulacell = Formularow.CreateCell(8);
//Formula to calculate the Total
Formulacell.CellStyle = Header;
String strFormula = "SUBTOTAL(9,I" + Convert.ToString(startrow) + ":I" + Convert.ToString(lastdatarow) + ")";
Formulacell.SetCellType(CellType.Formula);
Formulacell.SetCellFormula(strFormula);
HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
#endregion
#region LOGO
//Displaying the Logo on the TOP Left Corner of the Excel Sheet
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 255, 1, 2, 2, 5)
{
AnchorType = (int)NPOI.SS.UserModel.AnchorType.MoveAndResize
};
//Here, you need to replace the Image Path and Name as per your directory structure and Image Name
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(@"C:\Users\HP\source\repos\WorkingWithExcel\WorkingWithExcel\Images\DotNetTutorials.png", workbook));
picture.Resize();
picture.LineStyle = (LineStyle)HSSFPicture.LINESTYLE_NONE;
#endregion
//Finally, Create the Excel File and Save it on a specified Location
string FileName = "MyExcel_" + DateTime.Now.ToString("yyyy-dd-MM--HH-mm-ss") + ".xls";
//Here, you need to replace the Path as per your directory structure where you want to save the image
using (FileStream file = new FileStream(@"C:\Users\HP\source\repos\WorkingWithExcel\WorkingWithExcel\ExcelFiles\" + FileName, FileMode.Create))
{
workbook.Write(file);
file.Close();
Console.WriteLine("File Created Successfully...");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
//The following code is used to load the Image, Create the Image and return that image
public static int LoadImage(string path, HSSFWorkbook wb)
{
FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
byte[] buffer = new byte[file.Length];
file.Read(buffer, 0, (int)file.Length);
return wb.AddPicture(buffer, PictureType.JPEG);
}
}
}

With the above changes in place, now run the application code and see the output. If everything is fine then you will get the message saying File Created Successfully…. Now, go to the Excel Files folder and you will see the Excel should be generated with the required format, formula, and data.

Note: Styling the Header, Data, etc. are optional. If you don’t want to, you can remove them.

Import Excel Sheet Data in C# using NPOI Library:

Now, let us proceed and understand how to Import Excel Data in C# using the NPOI Library. Let us assume we have the following MyExcelFile.xlsx file in our Excel Files folder.

As you can see in the above image, this is nothing but the employee data of an organization. Now, we need to import the above Excel Sheet into our .NET Application. So, for this, please modify the Program class as follows. The following code is self-explained, so please go through the comment lines for a better understanding.

using System;
using System.Collections.Generic;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
namespace WorkingWithExcel
{
public class Program
{
public static void Main()
{
try
{
//The following Collection object is going to hold the Excel Sheet Data
List<Employee> listEmployees = new List<Employee>();
//Set the Excel File Path
string FilePath = @"C:\Users\HP\source\repos\WorkingWithExcel\WorkingWithExcel\ExcelFiles\MyExcelFile.xlsx";
//For Import we need to create an instance of XSSFWorkbook class
XSSFWorkbook xssfWorkbook;
using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
{
xssfWorkbook = new XSSFWorkbook(file);
}
//The Excel File Might have multiple sheets
//So create an ISheet object by specifying the appropriate sheet name or index position
//ISheet sheet = xssfWorkbook.GetSheet("Sheet1"); //Based on the Sheet Name
ISheet sheet = xssfWorkbook.GetSheetAt(0); //Based on the Index Position (0-Based Index Position)
//If First Row is Header, then Initialize the row = 1 else row = 0 inside the for loop
//As our Excel Sheet Contains the First Row as a Header, we are setting row = 1
//The following Loop will start from the First data Rows till the last data row in the sheet
for (int row = 1; row <= sheet.LastRowNum; row++)
{
//null is when the row only contains empty cells 
if (sheet.GetRow(row) != null)
{
//If the Row is not empty, then Fetch cell value and populate them with Employee Object
Employee emp = new Employee()
{
//For Accessing Numeric Cell Value we need to use NumericCellValue Property
//For Accessing String Cell Value we need to use StringCellValue Property
//For Accessing Boolean Cell Value we need to use BooleanCellValue Property
//First Cell is ID
ID = (int)sheet.GetRow(row).GetCell(0).NumericCellValue,
//Second Cell is Name
Name = sheet.GetRow(row).GetCell(1).StringCellValue,
//Third Cell is Address
Address = sheet.GetRow(row).GetCell(2).StringCellValue,
//Fourth Cell is Email
Email = sheet.GetRow(row).GetCell(3).StringCellValue,
//Fifth Cell is IsPermanent
IsPermanent = sheet.GetRow(row).GetCell(4).BooleanCellValue,
//Sixth Cell is Mobile
Mobile = sheet.GetRow(row).GetCell(5).StringCellValue,
//Seventh Cell is RegdNo
RegdNo = (int)sheet.GetRow(row).GetCell(6).NumericCellValue,
//Eighth Cell is Salary
Salary = (int)sheet.GetRow(row).GetCell(7).NumericCellValue,
};
//Add the employee data into listEmployees Collection
listEmployees.Add(emp);
}
}
//Once you import the data from the Excel File to your .NET Object, then you can do whatever you want
//Like Save the Data into the Database, Display the Data, Modify the Data, etc
//Here, we are going to Display the Excel Sheet Data
foreach (var emp in listEmployees)
{
Console.WriteLine($"ID: {emp.ID}, Name: {emp.Name}, Address: {emp.Address}, Salary: {emp.Salary}");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
}
public class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string Email { get; set; }
public string Mobile { get; set; }
public bool IsPermanent { get; set; }
public int RegdNo { get; set; }
public int Salary { get; set; }
}
}

Output:

In the next article, I will discuss How to generate PDF in C# with Examples. Here, in this article, I try to explain How to Perform Export and Import Excel Data in C# with Examples using the NPOI Library. I hope you enjoy this Export and Import Excel Data in C# with Examples article.