tentavarious
asked on
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.
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.
https://www.experts-exchange.com/questions/20957177/Export-Dataset-Datagrid-into-PDF-format.html
ASKER
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?
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?
ASKER
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(objDat aset, "TimeCards1")
myReport.SetDataSource(obj Dataset)
timeview.ReportSource = myReport
timeview.DataBind()
End Sub
Sub exportpdf()
'exports to pdf
Dim myexportoptions As CrystalDecisions.Shared.Ex portOption s
Dim mydiskfile As CrystalDecisions.Shared.Di skFileDest inationOpt ions
Dim myexportfile As String
Dim myReport2 As New timecards
myexportfile = "c:\inetpub\wwwroot\tool\t ool128\mis c" & Session.SessionID.ToString & ".pdf"
mydiskfile = New CrystalDecisions.shared.Di skFileDest inationOpt ions
mydiskfile.DiskFileName = myexportfile
myexportoptions = myReport2.ExportOptions
With myexportoptions
.DestinationOptions = mydiskfile
.ExportDestinationType = .ExportDestinationType.Dis kFile
.ExportFormatType = .ExportFormatType.Portable DocFormat
End With
myReport2.Export()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.WriteFile(myexpor tfile)
Response.Flush()
Response.Close()
System.IO.File.Delete(myex portfile)
End Sub
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(objDat
myReport.SetDataSource(obj
timeview.ReportSource = myReport
timeview.DataBind()
End Sub
Sub exportpdf()
'exports to pdf
Dim myexportoptions As CrystalDecisions.Shared.Ex
Dim mydiskfile As CrystalDecisions.Shared.Di
Dim myexportfile As String
Dim myReport2 As New timecards
myexportfile = "c:\inetpub\wwwroot\tool\t
mydiskfile = New CrystalDecisions.shared.Di
mydiskfile.DiskFileName = myexportfile
myexportoptions = myReport2.ExportOptions
With myexportoptions
.DestinationOptions = mydiskfile
.ExportDestinationType = .ExportDestinationType.Dis
.ExportFormatType = .ExportFormatType.Portable
End With
myReport2.Export()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.WriteFile(myexpor
Response.Flush()
Response.Close()
System.IO.File.Delete(myex
End Sub
ASKER
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(objDat aset, "TimeCards1")
myReport.SetDataSource(obj Dataset)
timeview.ReportSource = myReport
timeview.DataBind()
End Sub
Sub exportpdf()
Dim myexportoptions As CrystalDecisions.Shared.Ex portOption s
Dim mydiskfile As CrystalDecisions.Shared.Di skFileDest inationOpt ions
Dim myexportfile As String
' Dim myReport2 As New timecards
myexportfile = "c:\inetpub\wwwroot\tool\t ool128\mis c" & Session.SessionID.ToString & ".pdf"
mydiskfile = New CrystalDecisions.shared.Di skFileDest inationOpt ions
mydiskfile.DiskFileName = myexportfile
myexportoptions = myReport.ExportOptions
With myexportoptions
.DestinationOptions = mydiskfile
.ExportDestinationType = .ExportDestinationType.Dis kFile
.ExportFormatType = .ExportFormatType.Portable DocFormat
End With
myReport.Export()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.WriteFile(myexpor tfile)
Response.Flush()
Response.Close()
System.IO.File.Delete(myex portfile)
End Sub
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(objDat
myReport.SetDataSource(obj
timeview.ReportSource = myReport
timeview.DataBind()
End Sub
Sub exportpdf()
Dim myexportoptions As CrystalDecisions.Shared.Ex
Dim mydiskfile As CrystalDecisions.Shared.Di
Dim myexportfile As String
' Dim myReport2 As New timecards
myexportfile = "c:\inetpub\wwwroot\tool\t
mydiskfile = New CrystalDecisions.shared.Di
mydiskfile.DiskFileName = myexportfile
myexportoptions = myReport.ExportOptions
With myexportoptions
.DestinationOptions = mydiskfile
.ExportDestinationType = .ExportDestinationType.Dis
.ExportFormatType = .ExportFormatType.Portable
End With
myReport.Export()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.WriteFile(myexpor
Response.Flush()
Response.Close()
System.IO.File.Delete(myex
End Sub
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/
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.
ASKER
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.
ASKER
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.
ASKER
It works like it is suppose to on my computer, only the selected records get exported
Yeah in the Export to PDF function when are you setting the SQL string to use??
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??
You might want to look at this someone else has this working for them!
https://www.experts-exchange.com/questions/21152590/How-do-You-Export-Crystal-Report-to-PDF-Without-Writing-to-File-and-With-no-Memory-Issues.html
https://www.experts-exchange.com/questions/21152590/How-do-You-Export-Crystal-Report-to-PDF-Without-Writing-to-File-and-With-no-Memory-Issues.html
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!!
ASKER
It didnt work, it still tried to export the whole table, somewhere on the server it is loosing the dataset when it exports
Yeah I would suggest possibly trying to do it all in one sub as in the link above?
ASKER
I tried that also, still isnt working
Could you possibly post the modified code so I can see that you did?
ASKER
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(objDat aset, "TimeCards1")
myReport.SetDataSource(obj Dataset)
timeview.ReportSource = myReport
strConn.Close()
strConn.Dispose()
objDataset.Dispose()
'export pdf
Dim myexportoptions As CrystalDecisions.Shared.Ex portOption s
Dim mydiskfile As New CrystalDecisions.Shared.Di skFileDest inationOpt ions
Dim myexportfile2 As String
myexportfile2 = "c:\inetpub\wwwroot\tool\t ool128\cry stal\" & Session.SessionID.ToString & ".pdf"
' mydiskfile = New CrystalDecisions.shared.Di skFileDest inationOpt ions
mydiskfile.DiskFileName = myexportfile2
myexportoptions = myReport.ExportOptions
With myexportoptions
.DestinationOptions = mydiskfile
.ExportDestinationType = .ExportDestinationType.Dis kFile
.ExportFormatType = .ExportFormatType.Portable DocFormat
End With
myReport.Export()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.WriteFile(myexpor tfile2)
Response.Flush()
Response.Close()
System.IO.File.Delete(myex portfile2)
End Sub
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(objDat
myReport.SetDataSource(obj
timeview.ReportSource = myReport
strConn.Close()
strConn.Dispose()
objDataset.Dispose()
'export pdf
Dim myexportoptions As CrystalDecisions.Shared.Ex
Dim mydiskfile As New CrystalDecisions.Shared.Di
Dim myexportfile2 As String
myexportfile2 = "c:\inetpub\wwwroot\tool\t
' mydiskfile = New CrystalDecisions.shared.Di
mydiskfile.DiskFileName = myexportfile2
myexportoptions = myReport.ExportOptions
With myexportoptions
.DestinationOptions = mydiskfile
.ExportDestinationType = .ExportDestinationType.Dis
.ExportFormatType = .ExportFormatType.Portable
End With
myReport.Export()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.WriteFile(myexpor
Response.Flush()
Response.Close()
System.IO.File.Delete(myex
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I haven't tested so might have made some typeos!
ASKER
Nope, same issue, it works on my system but not on the server, it tries to load the entire table.
ASKER
Maybe instead of using a oledbdataset, how can i get this to work?
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(Configuratio nSettings. AppSetting s("connect ionString" ))
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?
Dim myCn As New SqlConnection(Configuratio
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?
ASKER
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.
ASKER
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.C lear()
paramField.ParameterFieldN ame = "formid"
discreteVal.Value = formid2
paramField.CurrentValues.A dd(discret eVal)
paramFields.Add(paramField )
toolrequestviewer2.Paramet erFieldInf o = paramFields
toolrequestviewer2.ReportS ource = "c:\inetpub\wwwroot\tool\t ool128\too lrequest2. rpt"
toolrequestviewer2.DataBin d()
End Sub
Sub exportpdf()
Dim formid2 As Integer
formid2 = Session("formnum")
Dim myexportoptions As CrystalDecisions.Shared.Ex portOption s
Dim mydiskfile As CrystalDecisions.Shared.Di skFileDest inationOpt ions
Dim myexportfile As String
Dim myReport As New toolrequest2
myexportfile = "c:\inetpub\wwwroot\tool\t ool128\mis c" & Session.SessionID.ToString & ".pdf"
mydiskfile = New CrystalDecisions.shared.Di skFileDest inationOpt ions
mydiskfile.DiskFileName = myexportfile
myexportoptions = myReport.ExportOptions
With myexportoptions
.DestinationOptions = mydiskfile
.ExportDestinationType = .ExportDestinationType.Dis kFile
.ExportFormatType = .ExportFormatType.Portable DocFormat
End With
myReport.SetParameterValue ("formid", formid2)
myReport.Export()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.WriteFile(myexpor tfile)
Response.Flush()
Response.Close()
System.IO.File.Delete(myex portfile)
End Sub
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.C
paramField.ParameterFieldN
discreteVal.Value = formid2
paramField.CurrentValues.A
paramFields.Add(paramField
toolrequestviewer2.Paramet
toolrequestviewer2.ReportS
toolrequestviewer2.DataBin
End Sub
Sub exportpdf()
Dim formid2 As Integer
formid2 = Session("formnum")
Dim myexportoptions As CrystalDecisions.Shared.Ex
Dim mydiskfile As CrystalDecisions.Shared.Di
Dim myexportfile As String
Dim myReport As New toolrequest2
myexportfile = "c:\inetpub\wwwroot\tool\t
mydiskfile = New CrystalDecisions.shared.Di
mydiskfile.DiskFileName = myexportfile
myexportoptions = myReport.ExportOptions
With myexportoptions
.DestinationOptions = mydiskfile
.ExportDestinationType = .ExportDestinationType.Dis
.ExportFormatType = .ExportFormatType.Portable
End With
myReport.SetParameterValue
myReport.Export()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.WriteFile(myexpor
Response.Flush()
Response.Close()
System.IO.File.Delete(myex
End Sub
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.
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.
ASKER
I found this site that i believe addresses my issues
http://support.businessobjects.com/library/kbase/articles/c2011912.asp
http://support.businessobjects.com/library/kbase/articles/c2011912.asp
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!
ASKER
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.
Ok well good luck, let us know what the outcome is!
ASKER
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.
ASKER
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.
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.
ASKER
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
http://support.businessobjects.com/communityCS/TechnicalPapers/crnet_deployment.pdf.asp
ASKER
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.
VC_User_CRT71_RTL_X86_---.
VC_User_STL71_RTL_X86_---.
they contain the dlls that the database driver is dependent on.