exporting datagrid to pdf or excel

Hello all,
I was wondering how i could export the contents of a datagrid to a pdf or excel spreadsheet from a webform.  I am running into printing issues when trying to print from a browser, so basically i need to print a datagrid from a webform.
tentavariousAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tentavariousAuthor Commented:
I have tried exporting to excel and i dont like how it works?  My problem the datagrid usually gets built from a SQL stament  with a bunch of select options how can i send these so the crystal report can find the same data as my datagrid.  For example here is what the sql statement might look like but not always, i give  the user  the ability to choose from checkboxes, dropdowns, and textfields to select the appropriate records.

sql statement
SELECT * FROM TimeCards1, clocknumbers WHERE(Date_Time between CDate('10/17/2004') and CDate('11/16/2004') and Clock = '3715' and Planned = 'Y' )
I need to use this select in crystal  and i dont know how.  I know i can save this as a session variable and on the webform containing the crystal report use this parameter, but i am not sure how?
0
tentavariousAuthor Commented:
I am using crystal reports to export to pdf, but for some reason my whole database table is being loaded, what is going on.  Here is the code for the webform that holds the report.  Am i forgotting something in my export procedure?

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not Page.IsPostBack Then
            bindreport()
            exportpdf()
        End If
    End Sub
    Sub bindreport()
'fill the report
        Dim ssql As String = Session("stringhold")
        Dim myReport As New timecards
        Dim objDataAdapter As New OleDbDataAdapter(ssql, strConn)
        objDataAdapter.Fill(objDataset, "TimeCards1")
        myReport.SetDataSource(objDataset)
        timeview.ReportSource = myReport
        timeview.DataBind()
    End Sub

    Sub exportpdf()
'exports to pdf
        Dim myexportoptions As CrystalDecisions.Shared.ExportOptions
        Dim mydiskfile As CrystalDecisions.Shared.DiskFileDestinationOptions
        Dim myexportfile As String
        Dim myReport2 As New timecards
        myexportfile = "c:\inetpub\wwwroot\tool\tool128\misc" & Session.SessionID.ToString & ".pdf"
        mydiskfile = New CrystalDecisions.shared.DiskFileDestinationOptions
        mydiskfile.DiskFileName = myexportfile
        myexportoptions = myReport2.ExportOptions
        With myexportoptions
            .DestinationOptions = mydiskfile
            .ExportDestinationType = .ExportDestinationType.DiskFile
            .ExportFormatType = .ExportFormatType.PortableDocFormat
        End With
        myReport2.Export()
        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"
        Response.WriteFile(myexportfile)
        Response.Flush()
        Response.Close()
        System.IO.File.Delete(myexportfile)
    End Sub
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

tentavariousAuthor Commented:
I got the report to export to a pdf on my computer but when i try to export the pdf from the server i get a system has requested a time out error.  If i take out the the export procedure the report will load fine on the server,  I dont know why i am getting this error because, i have 3 reports out there that work fine and export to pdf's

dim myReport as new timecards
 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here

        If Not Page.IsPostBack Then
            bindreport()
            exportpdf()
        End If
    End Sub
    Sub bindreport()
        Dim ssql As String = Session("stringhold")
        Dim objDataAdapter As New OleDbDataAdapter(ssql, strConn)
        objDataAdapter.Fill(objDataset, "TimeCards1")
        myReport.SetDataSource(objDataset)
        timeview.ReportSource = myReport
        timeview.DataBind()
    End Sub
    Sub exportpdf()

        Dim myexportoptions As CrystalDecisions.Shared.ExportOptions
        Dim mydiskfile As CrystalDecisions.Shared.DiskFileDestinationOptions
        Dim myexportfile As String
        '  Dim myReport2 As New timecards
        myexportfile = "c:\inetpub\wwwroot\tool\tool128\misc" & Session.SessionID.ToString & ".pdf"
        mydiskfile = New CrystalDecisions.shared.DiskFileDestinationOptions
        mydiskfile.DiskFileName = myexportfile
        myexportoptions = myReport.ExportOptions
        With myexportoptions
            .DestinationOptions = mydiskfile
            .ExportDestinationType = .ExportDestinationType.DiskFile
            .ExportFormatType = .ExportFormatType.PortableDocFormat
        End With
        myReport.Export()
        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"
        Response.WriteFile(myexportfile)
        Response.Flush()
        Response.Close()
        System.IO.File.Delete(myexportfile)
    End Sub
0
smolamCommented:
Mmm not a very easy thing to do have you seen this article, you might be able to adapt some of its code? http://www.developerfusion.com/show/4266/
0
smolamCommented:
Does it take longer than 90 seconds to create your report, ASP.Net has a default script timeout of 90 seconds, which you are sure it will take that long to do what you want can be modified.
0
tentavariousAuthor Commented:
When i run it on my computer it takes about 5 seconds, to have the report exported.  Right now on the server i have set up 3 crystal reports that get exported to a pdf, they all work fine,  i have never tried building a report from a dataset before, like i am with this one, but that doesnt seem to be the problem because i can comment out the export procedure, and the report will load fine with all of the correct records.
0
tentavariousAuthor Commented:
Ok, i see what is happening for some reason the whole database table is being exported not the selected records, and the whole table has over 25000 records and ends up being over 1000 pages in the pdf.  i am not sure why this is happening maybe you could examine my code above.
0
tentavariousAuthor Commented:
It works like it is suppose to on my computer, only the selected records get exported
0
smolamCommented:
Yeah in the Export to PDF function when are you setting the SQL string to use??
0
smolamCommented:
And have you tried first to replace Session("stringhold") with "SELECT * FROM TimeCards1, clocknumbers WHERE(Date_Time between CDate('10/17/2004') and CDate('11/16/2004') and Clock = '3715' and Planned = 'Y' )" to see if that works on your server first??
0
smolamCommented:
You might want to look at this someone else has this working for them!
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21152590.html 
0
smolamCommented:
The way it is being done above in my view is better because then you don't have to worry about saving the file to disk, the report is exported straight into the response objects output, that way you don't have to worry about files being stored up and having to set up permissions!!
0
tentavariousAuthor Commented:
It didnt work, it still tried to export the whole table, somewhere on the server it is loosing the dataset when it exports
0
smolamCommented:
Yeah I would suggest possibly trying to do it all in one sub as in the link above?
0
tentavariousAuthor Commented:
I tried that also, still isnt working
0
smolamCommented:
Could you possibly post the modified code so I can see that you did?
0
tentavariousAuthor Commented:
Here is my modified code timecards is the name of the report, the first part works because i can bring up the report, without exporting, and the correct records are shown.


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page her

        bindreport()
    End Sub
    Sub bindreport()
        Dim myReport As New timecards
        Dim ssql As String = Session("stringhold")
        Dim objDataAdapter As New OleDbDataAdapter(ssql, strConn)
        objDataAdapter.Fill(objDataset, "TimeCards1")
        myReport.SetDataSource(objDataset)
        timeview.ReportSource = myReport
        strConn.Close()
        strConn.Dispose()
        objDataset.Dispose()
        'export pdf
        Dim myexportoptions As CrystalDecisions.Shared.ExportOptions
        Dim mydiskfile As New CrystalDecisions.Shared.DiskFileDestinationOptions
        Dim myexportfile2 As String

        myexportfile2 = "c:\inetpub\wwwroot\tool\tool128\crystal\" & Session.SessionID.ToString & ".pdf"
        '  mydiskfile = New CrystalDecisions.shared.DiskFileDestinationOptions
        mydiskfile.DiskFileName = myexportfile2
        myexportoptions = myReport.ExportOptions
        With myexportoptions
            .DestinationOptions = mydiskfile
            .ExportDestinationType = .ExportDestinationType.DiskFile
            .ExportFormatType = .ExportFormatType.PortableDocFormat
        End With
        myReport.Export()
        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"
        Response.WriteFile(myexportfile2)
        Response.Flush()
        Response.Close()
        System.IO.File.Delete(myexportfile2)
     
    End Sub
0
smolamCommented:
I haven't really had a chance to test this but if you change the section below 'export Pdf to what I have below does it work??

Dim myexportoptions As ExportOptions

        myexportoptions = myReport.ExportOptions
        With myexportoptions
            .FormatOptions = New PdfRtfWordFormatOptions()
            .ExportFormatType = ExportFormatType.PortableDocFormat
        End With

        Dim req As ExportRequestContext = New ExportRequestContext()
        req.ExportInfo = myexportoptions

        Dim st As System.IO.Stream
        st = myReport.FormatEngine.ExportToStream(req)

        timeview= Nothing

        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"

        Dim b(st.Length) As Byte

        st.Read(b, 0, st.Length)

        Response.BinaryWrite(b)

        st.Close()
        st = Nothing

        TimeView.Dispose()

        Response.End()
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smolamCommented:
I haven't tested so might have made some typeos!
0
tentavariousAuthor Commented:
Nope, same issue, it works on my system but not on the server, it tries to load the entire table.
0
tentavariousAuthor Commented:
Maybe instead of using a oledbdataset, how can i get this to work?
0
smolamCommented:
You could try and use the ADO,  You need to make sure you have ADO included in your references though.
     
Dim myCn As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

        Dim strSQL As String = "select * from sometable"

        'Fill dataset with Data
        '-----------------------------------------------------------------
        Dim SQLAdapter As New SqlDataAdapter(strSQL, myCn)
        Dim dstData As New DataSet
        SQLAdapter.Fill(dstData)


Have you stepped through the code in Debug mode to see what the string is that it is getting from the Session Variable?
0
tentavariousAuthor Commented:
Yep, like i said if i remove the export part of the sub procedure and just load the report it works fine, the correct records are showing up, on the server.  I can only export "correctly" in debug mode.  I am using msacces database so i thought i had to use the oledbadapter.  For some reason when i export on the server myReport is regenrating and loading the entire table.  If i could select the records from the report side, i think this problem would be solved.
0
tentavariousAuthor Commented:
I know before when i changed a report with a parameter i had to make sure i included that change again before i exported like i did below.  My basic layout of the timecards.rpt loads all the records from the database table "TimeCards1".  The dataadapter changes the records when i view the report, but when i try to export it is always jumping back to the orginial report and pulling the data from there.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not Page.IsPostBack Then
            addparameters()
            exportpdf()
        End If
    End Sub
    Sub addparameters()
        Dim formid2 As Integer
        formid2 = Session("formid")
        Dim paramFields As New ParameterFields
        Dim paramField As New ParameterField
        Dim discreteVal As New ParameterDiscreteValue
        paramField.CurrentValues.Clear()
        paramField.ParameterFieldName = "formid"
        discreteVal.Value = formid2
        paramField.CurrentValues.Add(discreteVal)
        paramFields.Add(paramField)
        toolrequestviewer2.ParameterFieldInfo = paramFields
        toolrequestviewer2.ReportSource = "c:\inetpub\wwwroot\tool\tool128\toolrequest2.rpt"
        toolrequestviewer2.DataBind()
    End Sub
    Sub exportpdf()
        Dim formid2 As Integer
        formid2 = Session("formnum")

        Dim myexportoptions As CrystalDecisions.Shared.ExportOptions
        Dim mydiskfile As CrystalDecisions.Shared.DiskFileDestinationOptions
        Dim myexportfile As String
        Dim myReport As New toolrequest2
        myexportfile = "c:\inetpub\wwwroot\tool\tool128\misc" & Session.SessionID.ToString & ".pdf"
        mydiskfile = New CrystalDecisions.shared.DiskFileDestinationOptions
        mydiskfile.DiskFileName = myexportfile
        myexportoptions = myReport.ExportOptions
        With myexportoptions
            .DestinationOptions = mydiskfile
            .ExportDestinationType = .ExportDestinationType.DiskFile
            .ExportFormatType = .ExportFormatType.PortableDocFormat
        End With
        myReport.SetParameterValue("formid", formid2)
        myReport.Export()
        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"
        Response.WriteFile(myexportfile)
        Response.Flush()
        Response.Close()
        System.IO.File.Delete(myexportfile)
    End Sub
0
smolamCommented:
But surely because you are creating a new Report when you set
Dim myReport As New toolrequest2 what if you changed that to:
Dim myReport As New toolrequest2 toolrequestviewer2.Report

Am really running out of ideas on this one.
0
tentavariousAuthor Commented:
I found this site that i believe addresses my issues
http://support.businessobjects.com/library/kbase/articles/c2011912.asp
0
smolamCommented:
Arggh so you just need the latest Service Pack then, man what a nightmare!  Suppose that should have been the first thing we tried doing!
0
tentavariousAuthor Commented:
Well i was reading, and by what the article said, and should never had worked at all, not even on my system.  So i will see, i am downloading the service pack, i will see if this solves it.
0
smolamCommented:
Ok well good luck, let us know what the outcome is!
0
tentavariousAuthor Commented:
Well i installed the service pack, same results, I am all out of ideas, i am pretty sure it would work if i did it other then at runtime.
0
tentavariousAuthor Commented:
I discovered that i need two merger modules installed on the server that deal with ado.net and dataset, thats why it works on my system, but not the server.
0
smolamCommented:
Man what a nightmare, well I am glad that you got it sorted, if there was an article that you found please can you post it so others can know your solution.
0
tentavariousAuthor Commented:
I actually found this pdf that walks through deploying a project, and making sure the correct merger modules are installed to run crystal report on a server.  Click on the link to get the pdf.  Here is the site
http://support.businessobjects.com/communityCS/TechnicalPapers/crnet_deployment.pdf.asp
0
tentavariousAuthor Commented:
I discovered that i needed these 2 meger modules installed, that deal with ado.net and dataset
VC_User_CRT71_RTL_X86_---.msm
VC_User_STL71_RTL_X86_---.msm
they contain the dlls that the database driver is dependent on.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.