In this tutorial, I am going to explain you how to read excel file in C# without using Microsoft Office Interop Excel Provider. We will be using NPOI which is .NET version of POI Java project at http://poi.apache.org/. POI is an open source project which can help you read/write xls, doc, ppt files. It has a wide application. I will be using Visual Studio 2013. Below is step by step tutorial.
Creating ASP.NET Empty Application
First step is to create ASP.NET Empty WebForm project.
Go to File → New → Project. A new window will be open as shown below.
Now go to Web → Visual Studio 2012 → select .NET Framework 4.5 → select ASP.NET Empty Web Application and give project name and click on OK.
Now, an asp.net empty project will be created.
Installing NPOI using Nuget
Next step is to install NPOI. Right click on References of application and go to Manage NuGet Packages and after that a new window will open as shown below. Search for NPOI and install it as shown below.
Adding WebForm
Now add a new webform i.e WebForm1.aspx file to the application. Create a gridview control as shown below.
WebForm1.aspx Code:@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>
DOCTYPE html>
html xmlns="http://www.w3.org/1999/xhtml">
head runat="server">
title>title>
head>
body>
form id="form1" runat="server">
div>
asp:GridView ID="grdUserMaster" runat="server" CssClass="">
HeaderStyle HorizontalAlign="Center" />
HeaderStyle BackColor="#1e5873" Font-Bold="True" ForeColor="White" />
RowStyle HorizontalAlign="Center" />
asp:GridView>
div>
form>
body>
html>
Below is the C# aspx.cs file code.
WebForm1.aspx.cs Code:using System;
usingSystem.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
usingSystem.Web.UI.WebControls;
usingNPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
usingNPOI.XSSF.UserModel;
using System.Data;
using System.IO;
namespace WebApplication1
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string FilePath = @"H:\Demo\UserMaster.xlsx";
DataTable dt = new DataTable();
dt = ReadExcelFileToDataTable(FilePath);
grdUserMaster.DataSource = dt;
grdUserMaster.DataBind();
}
public DataTableReadExcelFileToDataTable(string filePath)
{
//string filename = @"H:\Demo\UserMaster.xlsx";
//byte[] bytes= System.IO.File.ReadAllBytes();
// FileStream excelStream = new FileStream(Server.MapPath(filename), FileMode.Open);
// FileStream excelStream = new FileStream(filename, FileMode.Open);
FileStream excelStream = new FileStream(filePath, FileMode.Open);
var table = new DataTable();
var book = new XSSFWorkbook(excelStream);
excelStream.Close();
var sheet = book.GetSheetAt(0);
var headerRow = sheet.GetRow(0);//
var cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
var rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
//header
for (int i = headerRow.FirstCellNum; i
{
var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
//body
for (var i = sheet.FirstRowNum + 1; i
{
var row = sheet.GetRow(i);
var dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j
{
if (row.GetCell(j) != null)
dataRow[j] = GetCellValue(row.GetCell(j));
}
}
table.Rows.Add(dataRow);
}
return table;
}
private string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.Blank:
return string.Empty;
case CellType.Boolean:
returncell.BooleanCellValue.ToString();
case CellType.Error:
returncell.ErrorCellValue.ToString();
case CellType.Numeric:
case CellType.Unknown:
default:
return cell.ToString();//This is a trick to get the correct value of the cell.
//NumericCellValue will return a numeric value no matter the cell value is a date or a number
case CellType.String:
returncell.StringCellValue;
case CellType.Formula:
try
{
var e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
returncell.NumericCellValue.ToString();
}
}
}
}
}
Below is excel file data stored at hard-drive location. Excel data need to be read and stored into C# datatable. |
 |
When application runs the ReadExcelFileToDataTable method read excel file and returns data in datatable. Gridview is loaded with datatable's data as shown below. |
 |
