Display Excel (byte data) in browser with GridView using C# and VB.Net in ASP.Net

Last Reply one year ago By dharmendr

Posted one year ago

How to modify the below code to use it for excel sheet online.

private void WriteWordFile(object fileobj)

{

object missingType = Type.Missing;

object readOnly = true;

object isVisible = false;

object documentFormat = 8;

string randomName = DateTime.Now.Ticks.ToString();

object htmlFilePath = Server.MapPath("~/Temp/") + randomName + ".htm";

string directoryPath = Server.MapPath("~/Temp/") + randomName + "_files";

ApplicationClass applicationclass = new ApplicationClass();

applicationclass.Documents.Open(ref fileobj,

ref readOnly,

ref missingType, ref missingType, ref missingType,

ref missingType, ref missingType, ref missingType,

ref missingType, ref missingType, ref isVisible,

ref missingType, ref missingType, ref missingType,

ref missingType, ref missingType);

applicationclass.Visible = false;

Document document = applicationclass.ActiveDocument;

document.SaveAs(ref htmlFilePath, ref documentFormat, ref missingType,

ref missingType, ref missingType, ref missingType,

ref missingType, ref missingType, ref missingType,

ref missingType, ref missingType, ref missingType,

ref missingType, ref missingType, ref missingType,

ref missingType);

document.Close(ref missingType, ref missingType, ref missingType);

byte[] bytes;

using (FileStream fs = new FileStream(htmlFilePath.ToString(), FileMode.Open, FileAccess.Read))

{

BinaryReader reader = new BinaryReader(fs);

bytes = reader.ReadBytes((int)fs.Length);

fs.Close();

}

Response.BinaryWrite(bytes);

Response.Flush();

File.Delete(htmlFilePath.ToString());

foreach (string file in Directory.GetFiles(directoryPath))

{

File.Delete(file);

}

Directory.Delete(directoryPath);

if (File.Exists(fileobj.ToString()))

{

File.Delete(fileobj.ToString());

}

Response.End();

}
You are viewing reply posted by: dharmendr one year ago.
Posted one year ago

Hi surbhik82,

Refer the below sample.

C#

public void ProcessRequest(int id, HttpContext context)
{
    byte[] bytes;
    string fileextension;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            cmd.Parameters.AddWithValue("@FileID", id);
            cmd.CommandText = "SELECT Name, Data, ContentType FROM tblFiles WHERE Id=@FileID";
            using (SqlDataReader sdr2 = cmd.ExecuteReader())
            {
                sdr2.Read();
                bytes = (byte[])sdr2["Data"];
                fileextension = sdr2["Name"].ToString();
            }
            con.Close();
        }
    }
    context.Response.Buffer = true;
    context.Response.Charset = "";
    context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
    if (fileextension.Substring(fileextension.IndexOf('.') + 1).ToLower() == "xls" || fileextension.Substring(fileextension.IndexOf('.') + 1).ToLower() == "xlsx")
    {
        File.WriteAllBytes(Server.MapPath("~/Temp/" + fileextension), bytes);
        GridView GvExcel = new GridView();
        GvExcel.DataSource = WriteExcelFile(Server.MapPath("~/Temp/" + fileextension), fileextension.Substring(fileextension.IndexOf('.') + 1).ToLower());
        GvExcel.DataBind();
        form1.Controls.Add(GvExcel);
    }
}

private DataSet WriteExcelFile(string filePath, string extension)
{
    System.Data.DataTable dtExcel = new System.Data.DataTable();
    string str = string.Empty;
    if (extension.ToLower() == "xls")
    {
        str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes'";
    }
    else if (extension.ToLower() == "xlsx")
    {
        str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes'";
    }

    OleDbConnection con = new OleDbConnection(str);
    OleDbCommand cmdExcel = new OleDbCommand();
    cmdExcel.Connection = con;
    con.Open();
    dtExcel = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    con.Close();
    con.Open();
    OleDbDataAdapter da = new OleDbDataAdapter();
    DataSet ds = new DataSet();
    string SheetName = dtExcel.Rows[0]["TABLE_NAME"].ToString();
    cmdExcel.CommandText = "SELECT * FROM [" + SheetName + "]";
    da.SelectCommand = cmdExcel;
    da.Fill(ds);
    con.Close();
    return ds;
}

protected void View(object sender, EventArgs e)
{
    ProcessRequest(14, HttpContext.Current);
}

VB.Net

Public Sub ProcessRequest(id As Integer, context As HttpContext)
    Dim bytes As Byte()
    Dim fileextension As String
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand()
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            cmd.Parameters.AddWithValue("@FileID", id)
            cmd.CommandText = "SELECT Name, Data, ContentType FROM tblFiles WHERE Id=@FileID"
            Using sdr2 As SqlDataReader = cmd.ExecuteReader()
                sdr2.Read()
                bytes = DirectCast(sdr2("Data"), Byte())
                fileextension = sdr2("Name").ToString()
            End Using
            con.Close()
        End Using
    End Using
    context.Response.Buffer = True
    context.Response.Charset = ""
    context.Response.Cache.SetCacheability(HttpCacheability.NoCache)
    If fileextension.Substring(fileextension.IndexOf("."C) + 1).ToLower() = "xls" OrElse fileextension.Substring(fileextension.IndexOf("."C) + 1).ToLower() = "xlsx" Then
        File.WriteAllBytes(Server.MapPath(Convert.ToString("~/Temp/") & fileextension), bytes)
        Dim GvExcel As New GridView()
        GvExcel.DataSource = WriteExcelFile(Server.MapPath(Convert.ToString("~/Temp/") & fileextension), fileextension.Substring(fileextension.IndexOf("."C) + 1).ToLower())
        GvExcel.DataBind()
        form1.Controls.Add(GvExcel)
    End If
End Sub

Private Function WriteExcelFile(filePath As String, extension As String) As DataSet
    Dim dtExcel As New System.Data.DataTable()
    Dim str As String = String.Empty
    If extension.ToLower() = "xls" Then
        str = (Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=") & filePath) + ";Extended Properties='Excel 8.0;HDR=Yes'"
    ElseIf extension.ToLower() = "xlsx" Then
        str = (Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=") & filePath) + ";Extended Properties='Excel 8.0;HDR=Yes'"
    End If

    Dim con As New OleDbConnection(str)
    Dim cmdExcel As New OleDbCommand()
    cmdExcel.Connection = con
    con.Open()
    dtExcel = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
    con.Close()
    con.Open()
    Dim da As New OleDbDataAdapter()
    Dim ds As New DataSet()
    Dim SheetName As String = dtExcel.Rows(0)("TABLE_NAME").ToString()
    cmdExcel.CommandText = (Convert.ToString("SELECT * FROM [") & SheetName) + "]"
    da.SelectCommand = cmdExcel
    da.Fill(ds)
    con.Close()
    Return ds
End Function

Protected Sub View(sender As Object, e As EventArgs)
    ProcessRequest(14, HttpContext.Current)
End Sub

Input Excel as in Binary format in database

CustomerId Name Country
1 Mudassar Khan India
2 Maria Austria
3 Ana Trujillo France
4 Antonio Moreno Brazil
5 Christina Berglund Ireland

Output in Browser

CustomerId Name Country
1 Mudassar Khan India
2 Maria Austria
3 Ana Trujillo France
4 Antonio Moreno Brazil
5 Christina Berglund Ireland