Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

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.

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.



This post first appeared on ASPArticles, please read the originial post: here

Share the post

Read Excel file in C# without Microsoft Office Interop Excel

×

Subscribe to Asparticles

Get updates delivered right to your inbox!

Thank you for your subscription

×