Excel Export Error Spread 6

Posted by: jbrazill on 8 September 2017, 3:03 pm EST

  • Posted 8 September 2017, 3:03 pm EST

    I am trying to export a spread to Excel and am getting an error which I cannot determine what may be the cause.


    I have experimented with removing flags to no avail.


    I have hit upon two curiosities:


    If I fill in every cell, the export works fine.


    If I delete all but the first row and do not fill in every cell, the export works fine.


    I have checked the forums and have ensured that no spanned cells exist (looks like the same kind of error in a much earlier version of spread)


    I have also attempted to check for cells that are "nothing" and replace the value with a space.


    Attached please find a zip with a picture of the error and an xml file of the spread control attempting to be exported.


    Please help. Thanks, JP Brazill


    Private Sub ExportAllReqsToExcel(ByVal Title1 As String, ByVal Title2 As String, ByVal Title3 As String, ByVal Title4 As String)


          Dim SaveDialog As New SaveFileDialog()
          Dim grd As New FarPoint.Win.Spread.FpSpread
          Dim i As Integer
          Dim sv As New FarPoint.Win.Spread.SheetView
          Dim wList = New FarPoint.Excel.ExcelWarningList



          For i = 3 To 3
             sv = CopySheet2(grdRequirements.Sheets(i))
             sv.Protect = False
             grd.Sheets.Add(sv)
          Next


          grd.SuspendLayout()


          For i = 0 To grd.Sheets.Count - 1
             grd.Sheets(i).FrozenTrailingRowCount = 0
             grd.Sheets(i).Rows.Count -= 1
          Next


          SaveDialog.Filter = "Excel File|*.xls"
          SaveDialog.Title = "Save Excel File"
          If CurrentFileDirectory = "" Then
             SaveDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal)
          Else
             SaveDialog.InitialDirectory = CurrentFileDirectory
          End If


          SaveDialog.FileName = "SIM Reqs Dump - " & Title1 & " - " & ReportDate


          For i = 0 To grd.Sheets.Count - 1
             grd.Sheets(i).Protect = False
          Next


          If SaveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
             If SaveDialog.FileName <> "" Then
                CurrentFileDirectory = System.IO.Path.GetDirectoryName(SaveDialog.FileName)
                If Not FileInUse(SaveDialog.FileName) Then
                   Try
                      'grd.SaveExcel(SaveDialog.FileName, FarPoint.Excel.ExcelSaveFlags.NoFormulas Or FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders Or FarPoint.Excel.ExcelSaveFlags.SaveAsFiltered, wList)
                      grd.Save("c:\Users\BrazillJ\Desktop\ExcelExportSource.xml", False)
                      grd.SaveExcel(SaveDialog.FileName, FarPoint.Excel.ExcelSaveFlags.NoFormulas Or FarPoint.Excel.ExcelSaveFlags.SaveAsFiltered, wList)
                      MsgBox("Export Complete.", MsgBoxStyle.Information, "SIM")
                   Catch e As ApplicationException
                      MsgBox(e.ToString())
                   End Try
                End If
             End If
          End If


          For i = 0 To grd.Sheets.Count - 1
             grd.Sheets(i).Protect = True
          Next


          grd.ResumeLayout()


       End Sub


      Public Function CopySheet2(ByVal Sheet As FarPoint.Win.Spread.SheetView) As FarPoint.Win.Spread.SheetView


          Dim bf As System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
          Dim Stream As System.IO.Stream
          Dim sv As FarPoint.Win.Spread.SheetView


          If System.IO.File.Exists("SpreadSheet.bin") Then
             System.IO.File.Delete("SpreadSheet.bin")
          End If


          Stream = System.IO.File.Open("SpreadSheet.bin", System.IO.FileMode.CreateNew)
          bf = New System.Runtime.Serialization.Formatters.Binary.BinaryFormatter()
          bf.Serialize(Stream, Sheet)
          Stream.Close()


          Stream = System.IO.File.Open("SpreadSheet.bin", System.IO.FileMode.Open)
          sv = bf.Deserialize(Stream)
          Stream.Close()


          Return sv


       End Function


     


    2013/01/SpreadExportToExcelError.zip
  • Replied 8 September 2017, 3:03 pm EST

    Hello,

    I was able to replicate the issue at my end, basically it seems to be the issue related to the row of data being exported because when the row is removed it no longer throws any exception. I observe some style settings being applied to the sheet, could you please share the code that you used to create the Spread that you sent as an xml file. It would help me to observe all the style settings applied and might help me narrow down which setting is exactly causing the issue.

    Thanks,

    Manpreet Kaur

  • Replied 8 September 2017, 3:03 pm EST

    Hi Manpreet,


    There is a column called Solution Status which is a combobox that has list entries set in the designer. I have set up styles called NotStartedStyle, RunningStyle and ReadyStyle that correspond to the entries in the combobox. I have also set up conditional formatting in the designer using the style names that changes the background color of the cell. I have no VB code that does this. As an aside, I tried to review the conditional formatting I set up and I can not figure out how to review it in the Spread Designer. It seems that you can set conditions but I can't figure out how to look at them once they have been set. If you can tell me how that would be great. I ran an experiment and deleted all the conditional formatting and deleted all the styles and I still get the error. Attached is the original spread designer file saved as xml (I had to change the extension to .txt to get it to upload).


     Thanks for your help, JP


     

  • Replied 8 September 2017, 3:03 pm EST

    Hi Manpreet,


    I do have styles and conditional formatting that is set in the spread designer, no code. The column Solution Status is a dropdown with entries of Reset, Not Started, Running and Ready. I have set up styles of NotStartedStyle, RunningStyle and ReadyStyle that have specific background colors set. I specified conditional formatting of "=" for the combobox entries and the corresponding styles. As an aside, I can not figure out how to review conditional settings in the Spread Designer. It seems you can specify them but no way to see them once they are specified. If I have missed something please let me know. Anyway, I ran an experiment and deleted all styles and conditional formatting and still get the export error...


    So as a workaround I recoded the routine to manually copy over important elements of the spread to a temporary spread (column headers, row and column height, color, cell values, etc) and it works but on large spreads (multiple sheets with thousands of rows I am going to encounter potentially severe performance problems so I am anxious for a resolution with my original routine. Attached please find the original spread designer file saved as xml (I had to change the extension to .txt to get it to upload.


    Thanks, JP


    p.s. This is a second reply as on the first one I got an error when posting and thought everything was lost so I posted this one then saw the original really did post but I cannot delete it.


    2013/01/SpreadDesignerOfSpreadCausingExportToExcelCrash.txt
  • Replied 8 September 2017, 3:03 pm EST

    Hello,

    Thank you for sharing your observations further, however the issue seems to be a bug. I have reported the issue to the development team and the bug number for the same is #99930843. It would be fixed in the next maintenance release.

    Thanks,

    Manpreet Kaur

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels