Saturday, 3 February 2018

Read Excel file in C# without Microsoft Office Interop Excel

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 FileNewProject. A new window will be open as shown below.
Now go to WebVisual Studio 2012 → select .NET Framework 4.5 → select ASP.NET Empty Web Application and give project name and click on OK.

Creating asp.net 4.5 empty project

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.

Installing NPOI in ASP.Net

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;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.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 DataTable ReadExcelFileToDataTable(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 < cellCount; i++)
        {
            var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
            table.Columns.Add(column);
        }
        //body
        for (var i = sheet.FirstRowNum + 1; i <= rowCount; i++)
        {
            var row = sheet.GetRow(i);
            var dataRow = table.NewRow();
            if (row != null)
            {
                for (int j = row.FirstCellNum; j < cellCount; 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:
                return cell.BooleanCellValue.ToString();
            case CellType.Error:
                return cell.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:
                return cell.StringCellValue;
            case CellType.Formula:
                try
                {
                    var e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    e.EvaluateInCell(cell);
                    return cell.ToString();
                }
                catch
                {
                    return cell.NumericCellValue.ToString();
                }
        }
    }
}
}

Below is excel file data stored at hard-drive location. Excel data need to be read and stored into C# datatable.

Read excel file in C#

When application runs the ReadExcelFileToDataTable method read excel file and returns data in datatable. Gridview is loaded with datatable's data as shown below.

Read excel file in C# without Microsoft Office Interop Excel
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON

Ads By Chitika