Import Excel using ClosedXML not reading data in ASP.Net

Hi arujdevia,

Use Microsoft.Office.Interop.Excel library.

Check this example. Now please take its reference and correct your code.

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Reflection
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dt = GetData();
    ExportDataSetToExcel(dt);
}

private void ExportDataSetToExcel(DataTable table)
{
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = Missing.Value;
    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Item[1];

    for (int column = 1; column < table.Columns.Count + 1; column++)
    {
        xlWorkSheet.Cells[1, column] = table.Columns[column - 1].ColumnName;
    }

    for (int row = 0; row < table.Rows.Count; row++)
    {
        for (int column = 0; column < table.Columns.Count; column++)
        {
            xlWorkSheet.Cells[row + 2, column + 1] = table.Rows[row].ItemArray[column].ToString();
        }
    }

    string filePath = Server.MapPath("~/Table.xlsx");
    if (File.Exists(filePath))
    {
        File.Delete(filePath);
    }

    xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue,
        misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange,
        misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlApp);
    releaseObject(xlWorkBook);
    releaseObject(xlWorkSheet);
}

private void releaseObject(object obj)
{
    try
    {
        Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
    }
    finally
    {
        GC.Collect();
    }
}

private DataTable GetData()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT * FROM Customers";
    using (SqlConnection con = new SqlConnection(conString))
    {
        SqlCommand cmd = new SqlCommand(query);
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);

                return dt;
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn(2) {
                        New DataColumn("Id"),
                        New DataColumn("Name"),
                        New DataColumn("Country")})
    dt.Rows.Add(1, "John Hammond", "United States")
    dt.Rows.Add(2, "Mudassar Khan", "India")
    dt.Rows.Add(3, "Suzanne Mathews", "France")
    dt.Rows.Add(4, "Robert Schidner", "Russia")
    ExportDataSetToExcel(dt)
End Sub

Private Sub ExportDataSetToExcel(ByVal table As DataTable)
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = Missing.Value
    xlApp = New Excel.Application()
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)
    For column As Integer = 1 To table.Columns.Count + 1 - 1
        xlWorkSheet.Cells(1, column) = table.Columns(column - 1).ColumnName
    Next

    For row As Integer = 0 To table.Rows.Count - 1
        For column As Integer = 0 To table.Columns.Count - 1
            xlWorkSheet.Cells(row + 2, column + 1) = table.Rows(row).ItemArray(column).ToString()
        Next
    Next

    Dim filePath As String = Server.MapPath("~/Table.xlsx")
    If File.Exists(filePath) Then
        File.Delete(filePath)
    End If

    xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue,
                        misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange,
                        misValue, misValue, misValue, misValue, misValue)
    xlWorkBook.Close(True, misValue, misValue)
    xlApp.Quit()
    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)
End Sub

Private Sub releaseObject(ByVal obj As Object)
    Try
        Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

Private Function GetData() As DataTable
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT * FROM Customers"
    Using con As SqlConnection = New SqlConnection(conString)
        Dim cmd As SqlCommand = New SqlCommand(query)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using dt As DataTable = New DataTable()
                sda.Fill(dt)

                Return dt
            End Using
        End Using
    End Using
End Function