Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 570
  • Last Modified:

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_CostsSummarySheetNewNoDates
                            Dim connectionInfo As New ConnectionInfo()

                            connectionInfo.DatabaseName = "LaborSheets"
                            connectionInfo.UserID =
                            connectionInfo.Password =
                            SetDBLogonForReport(connectionInfo, report)
                            Dim JobHolder = report.ParameterFields("JobNumberHolder")
                            JobHolder.CurrentValues.AddValue(JobNumberHolder)
                            Dim InvoiceNumberHold = report.ParameterFields("TAXBRACKET")
                            InvoiceNumberHold.CurrentValues.AddValue(TaxBox_Daily.Text)


                            Dim s As System.IO.MemoryStream = report.ExportToStream(ExportFormatType.ExcelWorkbook)
                            Dim fs As System.IO.FileStream
                            Dim w As System.IO.BinaryWriter


                            fs = New System.IO.FileStream("C:\Temp\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
0
BKennedy2008
Asked:
BKennedy2008
  • 3
  • 3
2 Solutions
 
mlmccCommented:
Is this a Crystal Reports question?

If so, why are you exporting that way rather than using the built in Crystal exporting methods?

mlmcc
0
 
CodeCruiserCommented:
To be able to format the Excel file, you will need to open it using Excel interop and set those properties.
0
 
BKennedy2008Author Commented:
using the CR Built in options, what did I mess up?

   Dim CrExportOptions As ExportOptions
                            Dim CrDiskFileDestinationOptions As New  _
                          DiskFileDestinationOptions()
                            Dim CrFormatTypeOptions As ExcelFormatOptions = ExportOptions.CreateExcelFormatOptions()
                            CrDiskFileDestinationOptions.DiskFileName = "C:\Temp\Cost Summary Sheet ALL Dates for " & JLabel.Text & ".xlsx"
                            CrFormatTypeOptions.ExcelTabHasColumnHeadings = True
                            CrFormatTypeOptions.ExcelUseConstantColumnWidth = True
                            CrFormatTypeOptions.ExcelConstantColumnWidth = "15"
                            CrExportOptions = report.ExportOptions
                            With CrExportOptions
                                .ExportDestinationType = ExportDestinationType.DiskFile
                                .ExportFormatType = ExportFormatType.ExcelWorkbook
                                .DestinationOptions = CrDiskFileDestinationOptions
                                .FormatOptions = CrFormatTypeOptions
                            End With
                            report.Export(CrExportOptions)
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
mlmccCommented:
WHat is the result?

mlmcc
0
 
BKennedy2008Author Commented:
none of the format is being applied
0
 
mlmccCommented:
What version of Crystal are you using?

mlmcc
0
 
BKennedy2008Author Commented:
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now