Preview uploaded Excel (XLSX) file in ASP.Net GridView using ClosedXML

Hi landomarossi,

You need to bind GridView and write the data to csv file after completely reading the excel data and for csv you need to add comma instead of |.

Refer updated code.

HTML

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" runat="server" OnClick="Upload" />
<hr />
<asp:GridView runat="server" ID="GridView1"></asp:GridView>

Namespaces

C#

using System.Data;
using System.IO;
using ClosedXML.Excel;

VB.Net

Imports System.Data
Imports System.IO
Imports ClosedXML.Excel

Code

C#

protected void Upload(object sender, EventArgs e)
{
    if (!Directory.Exists(Server.MapPath("~/public/Files/")))
    {
        Directory.CreateDirectory(Server.MapPath("~/public/Files/"));
    }
    string filePath = Server.MapPath("~/public/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(filePath);
    //PanelGV.Visible = true;
    //Panel4.Visible = true;

    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);

        DataTable dt = new DataTable();

        Guid id = Guid.NewGuid();

        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
        }

        GridView1.Caption = "<b>Preview «" + Path.GetFileName(filePath) + "»</b><br /><br />";
        GridView1.DataSource = dt;
        GridView1.DataBind();

        string sourceFile = Path.GetFileName(filePath);
        string worksheetName = "table";
        string targetFile = Server.MapPath("~/public/Files/target_" + DateTime.Now.ToString("yyyyMMdd") + ".csv");

        StreamWriter wrtr = new StreamWriter(targetFile);
        for (int x = 0; x < dt.Rows.Count; x++)
        {
            string rowString = "";
            for (int y = 0; y < dt.Columns.Count; y++)
            {
                rowString += dt.Rows[x][y].ToString() + ",";
            }

            wrtr.WriteLine(rowString);
        }
        wrtr.Close();
        wrtr.Dispose();
    }
}

VB.Net

Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)
    If Not Directory.Exists(Server.MapPath("~/public/Files/")) Then
        Directory.CreateDirectory(Server.MapPath("~/public/Files/"))
    End If
    Dim filePath As String = Server.MapPath("~/public/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(filePath)
    Using workBook As XLWorkbook = New XLWorkbook(filePath)
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim dt As DataTable = New DataTable()
        Dim id As Guid = Guid.NewGuid()
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next
                firstRow = False
            Else
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As IXLCell In row.Cells()
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    i += 1
                Next
            End If
        Next

        GridView1.Caption = "<b>Preview «" & Path.GetFileName(filePath) & "»</b><br /><br />"
        GridView1.DataSource = dt
        GridView1.DataBind()
        Dim sourceFile As String = Path.GetFileName(filePath)
        Dim worksheetName As String = "table"
        Dim targetFile As String = Server.MapPath("~/public/Files/target_" & DateTime.Now.ToString("yyyyMMdd") & ".csv")
        Dim wrtr As StreamWriter = New StreamWriter(targetFile)

        For x As Integer = 0 To dt.Rows.Count - 1
            Dim rowString As String = ""
            For y As Integer = 0 To dt.Columns.Count - 1
                rowString += dt.Rows(x)(y).ToString() & ","
            Next
            wrtr.WriteLine(rowString)
        Next
        wrtr.Close()
        wrtr.Dispose()
    End Using
End Sub

Screenshot

Excel File

GridView displaying Excel data

Generated CSV