Click here to Skip to main content
15,938,218 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to read and append the data from SQL Server table to Excel.
The values are properly reading,but the data's will not be appending in the excelsheet.
It says Error "Unable to read file"
I have more than 1000 Rows for the SQL Statement..
What is the problem...?
See my code
Partial Class AvailabilityReport
    Inherits System.Web.UI.Page
    Public Shared con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Emp").ConnectionString)
    Public Shared cmd As SqlCommand
    Dim dt As DataTable
    Private Sub LoadData()
        Dim dsMaster As DataSet
        Dim sbViewDetails As New StringBuilder
        Dim drDetails As SqlDataReader
            With sbViewDetails
                drDetails = ExecuteReaderWithOutParam("SpEmpReport")
                If drDetails .HasRows Then
                    While drDetails .Read()
                        .Append("<td>" & drDetails ("SrNo").ToString & "</td>")
                        .Append("<td>" & drDetails ("EmpNo").ToString & "</td>")
                        .Append("<td>" & drDetails ("Name").ToString & "</td>")
                        .Append("<td>" & drDetails ("Salary").ToString & "</td>")
                        .Append("<td>" & drDetails ("Join_Date").ToString & "</td>")
                    End While
                    .Append("<tr><td colspan="9" align="center">No record found</td></tr>")
                End If
                divAvailabilityReport.InnerHtml = sbViewDetails.ToString()
                ExportToExcel("Emp Report")
            End With
            If con.State = ConnectionState.Open Then
            End If
        End Try
    End Sub
    Private Function BuildReportHeader() As String
        Dim Header As String
        Header = "<table id="tblEmp" style="border:1px solid black;" width="100%" border="1" cellspacing="0" cellspading="1">"
        Header &= "<tr style=" background-color: YELLOW;color: RED; removed: pointer; font-family: verdana; font-weight: boldfont-size: 10pt;">"
        Header &= "<th> Sl.No </th>"
        Header &= "<th> EmpNo </th>"
        Header &= "<th> Name </th>"
        Header &= "<th> Salary</th>"
        Header &= "<th> Join Date </th>"
        Header &= "</tr>"
        Return Header
    End Function
    Protected Sub ExportToExcel(ByVal reportName As String)
        Dim attachment As String = "attachment; filename=" & reportName & ".xls"
        Response.AddHeader("content-disposition", attachment)
        Response.ContentType = "application/ms-excel"
    End Sub
    Public Shared Function ExecuteReaderWithOutParam(ByVal spName As String) As SqlDataReader
        cmd = New SqlCommand(spName, con)
        cmd.CommandType = CommandType.StoredProcedure
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()
        Return dr
    End Function
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Catch ex As Exception
        End Try
    End Sub
End Class</table>

[edit]Code block added, "Treat my content as plain text..." option disabled - OriginalGriff[/edit]
Updated 7-Sep-11 23:16pm

1 solution

First of all it has nothing to do with ASP.NET.
It says Error "Unable to read file"

From where are you running the application ? Did you try to debug it out, in which line its throwing the exception. Put a breakpoint and check if the file path is valid.
Share this answer
gani7787 8-Sep-11 5:54am    
Hi,I have checked in the code one by one.

if the record > 64, then it will not update anything in the excel file. When i 'am trying to open it say's "Unable to read File"

If the Record Less than < 60, then it will append the data in the Excel File and has displaying the data..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900