ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Display Excel (byte data) in browser with GridView using C# and VB.Net in ASP.Net<p>Hi <a class="username" rel="surbhik82">surbhik82</a>,</p> <p>First you need to convert binary data to excel and then save in a folder and pass the folder to the method as in the below sample.</p> <h1 class="header"><a class="title f16" href="http://www.aspforums.net/Threads/516150/Open-Word-Excel-PDF-PowerPoint-PPT-files-in-new-browser-window-in-ASPNet/">Open Word Excel PDF PowerPoint (PPT) files in new browser window in ASP.Net</a></h1>https://www.aspforums.net:443/Threads/177819/Display-Excel-byte-data-in-browser-with-GridView-using-C-and-VBNet-in-ASPNet/https://www.aspforums.net:443/Threads/177819/Display-Excel-byte-data-in-browser-with-GridView-using-C-and-VBNet-in-ASPNet/Mon, 02 Oct 2017 00:05:21 GMTDisplay Excel (byte data) in browser with GridView using C# and VB.Net in ASP.Net<p>Hi There,</p> <p>No As per this code this is forcing to download excel.&nbsp;</p> <p>Plus this is using Grid View. I am not usign grid view.&nbsp;</p> <p>I have simple ADO .net code i am using to call a stored procedure.&nbsp;</p> <p>I want to display excel in browser using the code similar to writetoword function i provided earlier.&nbsp;</p> <p>I wanted to use Microsoft.interop.Office.Excel to view excel.</p> <p>Please modify code for me. Thanks</p> <p>&nbsp;</p> <p>&nbsp;</p> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/177819/Display-Excel-byte-data-in-browser-with-GridView-using-C-and-VBNet-in-ASPNet/https://www.aspforums.net:443/Threads/177819/Display-Excel-byte-data-in-browser-with-GridView-using-C-and-VBNet-in-ASPNet/Mon, 02 Oct 2017 21:26:55 GMTDisplay Excel (byte data) in browser with GridView using C# and VB.Net in ASP.Net<p>Hi <a class="username" rel="surbhik82"> surbhik82</a>,</p> <p>Refer the below sample.</p> <p><span style="text-decoration: underline;">C#</span></p> <pre class="brush: csharp">public void ProcessRequest(int id, HttpContext context) { &nbsp;&nbsp;&nbsp; byte[] bytes; &nbsp;&nbsp;&nbsp; string fileextension; &nbsp;&nbsp;&nbsp; string constr = ConfigurationManager.ConnectionStrings[&#34;constr&#34;].ConnectionString; &nbsp;&nbsp;&nbsp; using (SqlConnection con = new SqlConnection(constr)) &nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; using (SqlCommand cmd = new SqlCommand()) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cmd.CommandType = CommandType.Text; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cmd.Connection = con; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; con.Open(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cmd.Parameters.AddWithValue(&#34;@FileID&#34;, id); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cmd.CommandText = &#34;SELECT Name, Data, ContentType FROM tblFiles WHERE Id=@FileID&#34;; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; using (SqlDataReader sdr2 = cmd.ExecuteReader()) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sdr2.Read(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bytes = (byte[])sdr2[&#34;Data&#34;]; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; fileextension = sdr2[&#34;Name&#34;].ToString(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; con.Close(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp; context.Response.Buffer = true; &nbsp;&nbsp;&nbsp; context.Response.Charset = &#34;&#34;; &nbsp;&nbsp;&nbsp; context.Response.Cache.SetCacheability(HttpCacheability.NoCache); &nbsp;&nbsp;&nbsp; if (fileextension.Substring(fileextension.IndexOf(&#39;.&#39;) + 1).ToLower() == &#34;xls&#34; || fileextension.Substring(fileextension.IndexOf(&#39;.&#39;) + 1).ToLower() == &#34;xlsx&#34;) &nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; File.WriteAllBytes(Server.MapPath(&#34;~/Temp/&#34; + fileextension), bytes); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GridView GvExcel = new GridView(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GvExcel.DataSource = WriteExcelFile(Server.MapPath(&#34;~/Temp/&#34; + fileextension), fileextension.Substring(fileextension.IndexOf(&#39;.&#39;) + 1).ToLower()); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GvExcel.DataBind(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; form1.Controls.Add(GvExcel); &nbsp;&nbsp;&nbsp; } } private DataSet WriteExcelFile(string filePath, string extension) { &nbsp;&nbsp;&nbsp; System.Data.DataTable dtExcel = new System.Data.DataTable(); &nbsp;&nbsp;&nbsp; string str = string.Empty; &nbsp;&nbsp;&nbsp; if (extension.ToLower() == &#34;xls&#34;) &nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; str = &#34;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&#34; + filePath + &#34;;Extended Properties=&#39;Excel 8.0;HDR=Yes&#39;&#34;; &nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp; else if (extension.ToLower() == &#34;xlsx&#34;) &nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; str = &#34;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=&#34; + filePath + &#34;;Extended Properties=&#39;Excel 8.0;HDR=Yes&#39;&#34;; &nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp; OleDbConnection con = new OleDbConnection(str); &nbsp;&nbsp;&nbsp; OleDbCommand cmdExcel = new OleDbCommand(); &nbsp;&nbsp;&nbsp; cmdExcel.Connection = con; &nbsp;&nbsp;&nbsp; con.Open(); &nbsp;&nbsp;&nbsp; dtExcel = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); &nbsp;&nbsp;&nbsp; con.Close(); &nbsp;&nbsp;&nbsp; con.Open(); &nbsp;&nbsp;&nbsp; OleDbDataAdapter da = new OleDbDataAdapter(); &nbsp;&nbsp;&nbsp; DataSet ds = new DataSet(); &nbsp;&nbsp;&nbsp; string SheetName = dtExcel.Rows[0][&#34;TABLE_NAME&#34;].ToString(); &nbsp;&nbsp;&nbsp; cmdExcel.CommandText = &#34;SELECT * FROM [&#34; + SheetName + &#34;]&#34;; &nbsp;&nbsp;&nbsp; da.SelectCommand = cmdExcel; &nbsp;&nbsp;&nbsp; da.Fill(ds); &nbsp;&nbsp;&nbsp; con.Close(); &nbsp;&nbsp;&nbsp; return ds; } protected void View(object sender, EventArgs e) { &nbsp;&nbsp;&nbsp; ProcessRequest(14, HttpContext.Current); }</pre> <p><span style="text-decoration: underline;">VB.Net</span></p> <pre class="brush: vb">Public Sub ProcessRequest(id As Integer, context As HttpContext) &nbsp;&nbsp; &nbsp;Dim bytes As Byte() &nbsp;&nbsp; &nbsp;Dim fileextension As String &nbsp;&nbsp; &nbsp;Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString &nbsp;&nbsp; &nbsp;Using con As New SqlConnection(constr) &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;Using cmd As New SqlCommand() &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;cmd.CommandType = CommandType.Text &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;cmd.Connection = con &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;con.Open() &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;cmd.Parameters.AddWithValue("@FileID", id) &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;cmd.CommandText = "SELECT Name, Data, ContentType FROM tblFiles WHERE Id=@FileID" &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;Using sdr2 As SqlDataReader = cmd.ExecuteReader() &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;sdr2.Read() &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;bytes = DirectCast(sdr2("Data"), Byte()) &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;fileextension = sdr2("Name").ToString() &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;End Using &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;con.Close() &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;End Using &nbsp;&nbsp; &nbsp;End Using &nbsp;&nbsp; &nbsp;context.Response.Buffer = True &nbsp;&nbsp; &nbsp;context.Response.Charset = "" &nbsp;&nbsp; &nbsp;context.Response.Cache.SetCacheability(HttpCacheability.NoCache) &nbsp;&nbsp; &nbsp;If fileextension.Substring(fileextension.IndexOf("."C) + 1).ToLower() = "xls" OrElse fileextension.Substring(fileextension.IndexOf("."C) + 1).ToLower() = "xlsx" Then &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;File.WriteAllBytes(Server.MapPath(Convert.ToString("~/Temp/") &amp; fileextension), bytes) &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;Dim GvExcel As New GridView() &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;GvExcel.DataSource = WriteExcelFile(Server.MapPath(Convert.ToString("~/Temp/") &amp; fileextension), fileextension.Substring(fileextension.IndexOf("."C) + 1).ToLower()) &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;GvExcel.DataBind() &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;form1.Controls.Add(GvExcel) &nbsp;&nbsp; &nbsp;End If End Sub Private Function WriteExcelFile(filePath As String, extension As String) As DataSet &nbsp;&nbsp; &nbsp;Dim dtExcel As New System.Data.DataTable() &nbsp;&nbsp; &nbsp;Dim str As String = String.Empty &nbsp;&nbsp; &nbsp;If extension.ToLower() = "xls" Then &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;str = (Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=") &amp; filePath) + ";Extended Properties='Excel 8.0;HDR=Yes'" &nbsp;&nbsp; &nbsp;ElseIf extension.ToLower() = "xlsx" Then &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;str = (Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=") &amp; filePath) + ";Extended Properties='Excel 8.0;HDR=Yes'" &nbsp;&nbsp; &nbsp;End If &nbsp;&nbsp; &nbsp;Dim con As New OleDbConnection(str) &nbsp;&nbsp; &nbsp;Dim cmdExcel As New OleDbCommand() &nbsp;&nbsp; &nbsp;cmdExcel.Connection = con &nbsp;&nbsp; &nbsp;con.Open() &nbsp;&nbsp; &nbsp;dtExcel = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) &nbsp;&nbsp; &nbsp;con.Close() &nbsp;&nbsp; &nbsp;con.Open() &nbsp;&nbsp; &nbsp;Dim da As New OleDbDataAdapter() &nbsp;&nbsp; &nbsp;Dim ds As New DataSet() &nbsp;&nbsp; &nbsp;Dim SheetName As String = dtExcel.Rows(0)("TABLE_NAME").ToString() &nbsp;&nbsp; &nbsp;cmdExcel.CommandText = (Convert.ToString("SELECT * FROM [") &amp; SheetName) + "]" &nbsp;&nbsp; &nbsp;da.SelectCommand = cmdExcel &nbsp;&nbsp; &nbsp;da.Fill(ds) &nbsp;&nbsp; &nbsp;con.Close() &nbsp;&nbsp; &nbsp;Return ds End Function Protected Sub View(sender As Object, e As EventArgs) &nbsp;&nbsp; &nbsp;ProcessRequest(14, HttpContext.Current) End Sub</pre> <p><span style="text-decoration: underline;">Input Excel as in Binary format in database </span></p> <table border="1"> <tbody> <tr> <td><strong>CustomerId</strong></td> <td><strong>Name</strong></td> <td><strong>Country</strong></td> </tr> <tr> <td>1</td> <td>Mudassar Khan</td> <td>India</td> </tr> <tr> <td>2</td> <td>Maria</td> <td>Austria</td> </tr> <tr> <td>3</td> <td>Ana Trujillo</td> <td>France</td> </tr> <tr> <td>4</td> <td>Antonio Moreno</td> <td>Brazil</td> </tr> <tr> <td>5</td> <td>Christina Berglund</td> <td>Ireland</td> </tr> </tbody> </table> <p><span style="text-decoration: underline;">Output in Browser</span></p> <table border="1"> <tbody> <tr> <td><strong>CustomerId</strong></td> <td><strong>Name</strong></td> <td><strong>Country</strong></td> </tr> <tr> <td>1</td> <td>Mudassar Khan</td> <td>India</td> </tr> <tr> <td>2</td> <td>Maria</td> <td>Austria</td> </tr> <tr> <td>3</td> <td>Ana Trujillo</td> <td>France</td> </tr> <tr> <td>4</td> <td>Antonio Moreno</td> <td>Brazil</td> </tr> <tr> <td>5</td> <td>Christina Berglund</td> <td>Ireland</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/177819/Display-Excel-byte-data-in-browser-with-GridView-using-C-and-VBNet-in-ASPNet/https://www.aspforums.net:443/Threads/177819/Display-Excel-byte-data-in-browser-with-GridView-using-C-and-VBNet-in-ASPNet/Tue, 03 Oct 2017 04:31:21 GMT