Missing Borderlines on merged cells after excel export

Missing Borderlines on merged cells after excel export

Posted by: info on 15 August 2023, 8:15 am EST

• 

  info

  • Post Options:
  • Link
    Copy

  Posted 15 August 2023, 8:15 am EST - Updated 15 August 2023, 8:20 am EST

  I am migrating an application from .NET 4.x to .NET Core 6.x. I am using the same version of Spread (16.2.0.1475) for both applications. Within the application screens, the Spread worksheets look identical, but when exporting to MS Excel, there are problems with the borders on merged cells (see figure). This is despite the fact that the program code is exactly the same. The Excelexport happens in both cases with

  Spread1.SaveExcel(Filename, FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat | FarPoint.Excel.ExcelSaveFlags.SaveAsViewed);

  Can anyone help me here?

  

  605×552 110 KB

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 16 August 2023, 4:56 am EST

  Hi Helmut,

  Unfortunately, we could not replicate the issue on our end. We have created a sample application that shows the implementation. (MergedCellBorderExport.zip)

  We request you update the attached sample or provide a stripped-down version of your sample application so that we could reproduce the issue on our end and provide a solution accordingly.

  In case the issue persists on your end using the attached sample, please provide the following information to us.

  1. OS version
  2. VS version
  3. Locale settings of the system
  4. Display setting of the system

  Thanks & Regards,
  
  Aastha

• 

  info

  • Post Options:
  • Link
    Copy

  Posted 16 August 2023, 8:01 am EST

  Dear Aastha,
  
  First of all thanks for your quick reply. Unfortunately, your example does not exactly match my problem. I am concerned with the assignment of borderlines. I have extended the example a bit, add a few cell contents via programming, connect a few cells via columnspan and assign the border to the first cell (Sheets[0].Cells[3,4]). In the WinForm is the result correct but in the Excelexport the result is not displayed correctly. I have also attached such an Excel to you. I have found a workaround in which I assign the borderline to each of the actually merged cells.
  
  With my old version in .NET 4.8 this was not necessary and it would be also time consuming to change, therefore my hope would be that also in the .NET 6 version the Excelexport can be done correct again. I hope you can understand from my example where the problem lies.
  
  Thanks for your effort.
  
  HelmutMergedCellBorderExport_VersionGabler20230815.zip

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 17 August 2023, 5:51 am EST

  Hi Helmut,

  Thanks for sharing the updated sample with us.

  We could replicate the behavior on our end. We are in discussions with the developers regarding this issue. [Internal Tracking ID: SPNET-32695]

  We will notify you on getting further updates from the developers.

  For the time being, you can consider a workaround to handle this problem. The workaround involves applying the border to all the cells contained within the specified row/column span, as demonstrated in the following code:

  fpSpread1.Sheets[0].Cells[3, 4].Value = "Title over three columns";
  fpSpread1.Sheets[0].Cells[3, 4].ColumnSpan = 3;
  fpSpread1.Sheets[0].Cells[3, 4].Border = bord2222;
  fpSpread1.Sheets[0].Cells[3, 5].Border = bord2222;
  fpSpread1.Sheets[0].Cells[3, 6].Border = bord2222;
  

  Kindly refer to the attached sample for full implementation. MergedCellBorderExport.zip

  Thanks & Regards,
  
  Aastha

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 12 July 2024, 4:49 am EST

  Hi Helmut,

  The developers have provided us with the information that this is the intended behavior. Earlier, our border appearance was not compatible with Excel. The merged cell used border of top-left cell to display but not borders of all merged cells like Excel.

  If you want to have same appearance in Excel after export, you should consider to using BorderCollapse.Enhanced (flat style must be used) and use new API to set border.

  Here is the code snippet for the same:

  fpSpread1.LegacyBehaviors = LegacyBehaviors.None; //make sure flat style is used.
  fpSpread1.Reset();
  fpSpread1.BorderCollapse = BorderCollapse.Enhanced;
  IWorksheet activeSheet = fpSpread1.AsWorkbook().ActiveSheet;
  fpSpread1.Sheets[0].Cells[3, 4].Value = "Title over three columns";
  fpSpread1.Sheets[0].Cells[3, 4].ColumnSpan = 3;
  fpSpread1.Sheets[0].Cells[4, 4].Value = "Subtitle 1";
  fpSpread1.Sheets[0].Cells[4, 5].Value = "Subtitle 2";
  fpSpread1.Sheets[0].Cells[4, 6].Value = "Subtitle 3";
  activeSheet.Cells["E4:G4"].Merge();
  activeSheet.Cells["E4:G4"].Borders.LineStyle = BorderLineStyle.Medium;
  activeSheet.Cells["E5:G5"].Borders.LineStyle = BorderLineStyle.Medium;
  fpSpread1.SaveExcel("D:\\temp\\NewTest.xlsx", FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat | FarPoint.Excel.ExcelSaveFlags.SaveAsViewed);

  Kindly refer to the updated sample for full implementation. See
  
  SpannedCellBorder_mod.zip

  Thanks & Regards,
  
  Aastha