?
Solved

exporting datagrid to pdf or excel

Posted on 2004-11-16
35
Medium Priority
?
1,812 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:tentavarious
  • 19
  • 15
35 Comments
 

Author Comment

by:tentavarious
ID: 12597584
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
 

Author Comment

by:tentavarious
ID: 12599163
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:tentavarious
ID: 12604174
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
 
LVL 7

Expert Comment

by:smolam
ID: 12604299
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
 
LVL 7

Expert Comment

by:smolam
ID: 12604309
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
 

Author Comment

by:tentavarious
ID: 12604596
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
 

Author Comment

by:tentavarious
ID: 12604675
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
 

Author Comment

by:tentavarious
ID: 12604698
It works like it is suppose to on my computer, only the selected records get exported
0
 
LVL 7

Expert Comment

by:smolam
ID: 12604719
Yeah in the Export to PDF function when are you setting the SQL string to use??
0
 
LVL 7

Expert Comment

by:smolam
ID: 12604808
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
 
LVL 7

Expert Comment

by:smolam
ID: 12604953
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
 
LVL 7

Expert Comment

by:smolam
ID: 12604978
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
 

Author Comment

by:tentavarious
ID: 12605027
It didnt work, it still tried to export the whole table, somewhere on the server it is loosing the dataset when it exports
0
 
LVL 7

Expert Comment

by:smolam
ID: 12605041
Yeah I would suggest possibly trying to do it all in one sub as in the link above?
0
 

Author Comment

by:tentavarious
ID: 12605086
I tried that also, still isnt working
0
 
LVL 7

Expert Comment

by:smolam
ID: 12605103
Could you possibly post the modified code so I can see that you did?
0
 

Author Comment

by:tentavarious
ID: 12605191
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
 
LVL 7

Accepted Solution

by:
smolam earned 1050 total points
ID: 12605246
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
 
LVL 7

Expert Comment

by:smolam
ID: 12605249
I haven't tested so might have made some typeos!
0
 

Author Comment

by:tentavarious
ID: 12605400
Nope, same issue, it works on my system but not on the server, it tries to load the entire table.
0
 

Author Comment

by:tentavarious
ID: 12605427
Maybe instead of using a oledbdataset, how can i get this to work?
0
 
LVL 7

Expert Comment

by:smolam
ID: 12605483
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
 

Author Comment

by:tentavarious
ID: 12605605
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
 

Author Comment

by:tentavarious
ID: 12605938
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
 
LVL 7

Expert Comment

by:smolam
ID: 12606083
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
 

Author Comment

by:tentavarious
ID: 12606355
I found this site that i believe addresses my issues
http://support.businessobjects.com/library/kbase/articles/c2011912.asp
0
 
LVL 7

Expert Comment

by:smolam
ID: 12606375
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
 

Author Comment

by:tentavarious
ID: 12606518
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
 
LVL 7

Expert Comment

by:smolam
ID: 12606595
Ok well good luck, let us know what the outcome is!
0
 

Author Comment

by:tentavarious
ID: 12607204
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
 

Author Comment

by:tentavarious
ID: 12615584
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
 
LVL 7

Expert Comment

by:smolam
ID: 12615600
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
 

Author Comment

by:tentavarious
ID: 12615813
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
 

Author Comment

by:tentavarious
ID: 12615864
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question