SaveCustomColumnHeaders combined with UseOOXMLFormat

SaveCustomColumnHeaders combined with UseOOXMLFormat

Posted by: dovevim on 24 December 2018, 4:36 am EST

• 

  dovevim

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 24 December 2018, 4:36 am EST

  Hi, Using ver. 12.45.20181.0

  When saving to xlsx file, the headers don’t show. There is a frozen row for the headers but all the cells are empty:
  
  sp1.SaveExcel(“C:\Report.xlsx”, FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders Or FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat)

  If I save to xls (without UseOOXMLFormat) then the headers are there:
  
  sp1.SaveExcel(“C:\Report.xls”, FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders)

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 25 December 2018, 9:29 pm EST

  Hi,

  Can you please show us what your custom headers look like, and how you’re creating them.

  Thanks,
  
  Jitender

• 

  dovevim

  • Post Options:
  • Link
    Copy

  Posted 25 December 2018, 9:55 pm EST

  Hi,
  
  I create an SQL query that has field names, so the headers are created automatically:
  
  strQueryAlerts = “SELECT [ChannelState] AS ‘VStatus’
  
  ,[ZNumber] AS ‘ZNumber’
  
  ,[VehicleType] AS ‘VTyoe’
  
  ,[MaxDeviationValue] AS ‘MaxDev’
  
  ,[AlertDuration] AS ‘Duration’
  
  ,[AlertEnd] AS ‘Stop’
  
  ,[AlertStart] AS ‘Start’
  
  ,[AlertName] AS ‘Desc’
  
  ,[RemoteIO] AS ‘Charger’
  
  ,[MainGroup] AS ‘Location’
  
  FROM [mivnit].[dbo].[MasterAlertReport]
  
  where alertStart<='” & Format(dtTo.Value, “yyyy-MM-dd”) & “’
  
  AND alertEnd>='” & Format(dtFrom.Value, “yyyy-MM-dd”) & “’
  
  ORDER BY MainGroup,RemoteIO,AlertName,AlertStart”

  then I set the spread’s datasource to my dataset:
  
  sp1.DataSource = dstAlerts.Tables(0)

  I attached an image of the spread with the headers.

  P.S. The auto sizing of a column does not reflect the header row and the headers a clipped. Can I overcome it?

  Thanks

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 26 December 2018, 7:20 pm EST

  Hi,

  Looks like the attachment didn’t go through. Can you please attach it again?

  Also, I used a DataTable as the DataSource, and the exported excel correctly displayed the column headers. Can you please see if I’m missing anything in the attached sample. You can modify this sample to replicate the same behavior so that we can further investigate this.

  For auto-sizing, you can use this code:

  For col = 0 To spread.ActiveSheet.ColumnCount - 1
              spread.AsWorkbook().ActiveSheet.Columns(col).AutoFit()
  Next
  

  Regards,
  
  Jitender

  CustomColumnHeaders_SaveExcel.zip

• 

  dovevim

  • Post Options:
  • Link
    Copy

  Posted 26 December 2018, 10:15 pm EST - Updated 30 September 2022, 4:52 am EST

  Thank You,

  I downloaded you zip file and it worked fine.
  
  But when I remarked then lines: Private spread As FpSpread & spread = New FpSpread()
  
  and just put a spread at design time from the toolbox and named it spread it didn’t work, the autofit doesn’t work and the export is without headers.
  
  I hope the attachments are coming through this time.

  Spread put at design time:
  
  

  

  836×586 66.1 KB

  Spread put at run time:
  
  

  

  836×586 64.1 KB

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 26 December 2018, 10:45 pm EST

  In your Form.Designer file there should be a line which instantiates FpSpread, it should look something like:

  Me.FpSpread1 = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, CType(resources.GetObject("resource1"), System.IO.MemoryStream))
  

  What is the LegacyBehaviors enumeration in your file? If it is set to LegacyBehaviors.All, you would need to set it to LegacyBehaviors.None to use some of the new feature of Spread 12.

  You can also use this code instead of AutoFit (this would also work with previous Spread versions):

  For col = 0 To spread.ActiveSheet.ColumnCount - 1
              spread.ActiveSheet.SetColumnWidth(col,
                  spread.ActiveSheet.GetPreferredColumnWidth(col, 	PreferredSizeColumnOptions.IncludeAll))
  Next
  

  This line is commented out in my previous sample. You can just uncomment in the previous sample.

  Regards,
  
  Jitender

• 

  dovevim

  • Post Options:
  • Link
    Copy

  Posted 26 December 2018, 11:00 pm EST

  LegacyBehaviors is set to NONE
  
  I uncommented that line and still the wrong result with narrow columns as in my first image.

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 30 December 2018, 4:44 pm EST

  Hi,

  We can observe the behavior, as such we’ve escalated this to the development team [Internal Tracking ID: 268750]. We’ll let you know when we have an update on this.

  Meanwhile, as a workaround, you can set LegacyBehaviors to All.

  Thanks,
  
  Jitender

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 29 May 2019, 9:40 pm EST

  Hi,

  This issue has been fixed in Spread SP1. You can download it from here: https://www.grapecity.com/download/spreadnet

  Thanks,
  
  Jitender