BKennedy2008
asked on
exporting to Excel at runtime using System.IO.MemoryStream
I have the following code below that I pulled from export to PDF at runtime, and just changed the memorystream to excel from PDF.
I do not see anywhere to put any options like autofit columns.
This works when a user clicks on the open report in excel button, but no format options.
Is there a better approach to this or is there format options I can do with the below code?
Dim report As New Screen_CostsSummarySheetNe wNoDates
Dim connectionInfo As New ConnectionInfo()
connectionInfo.DatabaseNam e = "LaborSheets"
connectionInfo.UserID =
connectionInfo.Password =
SetDBLogonForReport(connec tionInfo, report)
Dim JobHolder = report.ParameterFields("Jo bNumberHol der")
JobHolder.CurrentValues.Ad dValue(Job NumberHold er)
Dim InvoiceNumberHold = report.ParameterFields("TA XBRACKET")
InvoiceNumberHold.CurrentV alues.AddV alue(TaxBo x_Daily.Te xt)
Dim s As System.IO.MemoryStream = report.ExportToStream(Expo rtFormatTy pe.ExcelWo rkbook)
Dim fs As System.IO.FileStream
Dim w As System.IO.BinaryWriter
fs = New System.IO.FileStream("C:\T emp\Cost Summary Sheet ALL Dates for " & JLabel.Text & ".xlsx", IO.FileMode.OpenOrCreate)
w = New System.IO.BinaryWriter(fs)
w.Seek(0, System.IO.SeekOrigin.Begin )
w.Write(s.ToArray)
w.Close()
fs.Close()
System.Diagnostics.Process .Start(fs. Name)
s = Nothing
fs = Nothing
w = Nothing
I do not see anywhere to put any options like autofit columns.
This works when a user clicks on the open report in excel button, but no format options.
Is there a better approach to this or is there format options I can do with the below code?
Dim report As New Screen_CostsSummarySheetNe
Dim connectionInfo As New ConnectionInfo()
connectionInfo.DatabaseNam
connectionInfo.UserID =
connectionInfo.Password =
SetDBLogonForReport(connec
Dim JobHolder = report.ParameterFields("Jo
JobHolder.CurrentValues.Ad
Dim InvoiceNumberHold = report.ParameterFields("TA
InvoiceNumberHold.CurrentV
Dim s As System.IO.MemoryStream = report.ExportToStream(Expo
Dim fs As System.IO.FileStream
Dim w As System.IO.BinaryWriter
fs = New System.IO.FileStream("C:\T
w = New System.IO.BinaryWriter(fs)
w.Seek(0, System.IO.SeekOrigin.Begin
w.Write(s.ToArray)
w.Close()
fs.Close()
System.Diagnostics.Process
s = Nothing
fs = Nothing
w = Nothing
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
WHat is the result?
mlmcc
mlmcc
ASKER
none of the format is being applied
What version of Crystal are you using?
mlmcc
mlmcc
ASKER
My bad, got caught up in the end of the year reports.
Kudos to both,
I used the built in CR to create the report, then used the interop to open the file.
Works like a champ.
Thanks again
Kudos to both,
I used the built in CR to create the report, then used the interop to open the file.
Works like a champ.
Thanks again
ASKER
Dim CrExportOptions As ExportOptions
Dim CrDiskFileDestinationOptio
DiskFileDestinationOptions
Dim CrFormatTypeOptions As ExcelFormatOptions = ExportOptions.CreateExcelF
CrDiskFileDestinationOptio
CrFormatTypeOptions.ExcelT
CrFormatTypeOptions.ExcelU
CrFormatTypeOptions.ExcelC
CrExportOptions = report.ExportOptions
With CrExportOptions
.ExportDestinationType = ExportDestinationType.Disk
.ExportFormatType = ExportFormatType.ExcelWork
.DestinationOptions = CrDiskFileDestinationOptio
.FormatOptions = CrFormatTypeOptions
End With
report.Export(CrExportOpti