Excel export

Excel export

Posted by: p.feenstra on 3 February 2022, 2:26 am EST

• 

  p.feenstra

  • Post Options:
  • Link
    Copy

  Posted 3 February 2022, 2:26 am EST

  Hi Jitender,

  Using Spread15

  I noticed that exporting to xlsx is not going well anymore. The number format is no longer correct. (PreferText does not help) Exporting to xls seems to work fine.

  Regards Piet

• 

  avnish.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 6 February 2022, 3:47 pm EST

  Hi,

  We have reported the issue to the development team and will let you know as soon as there is an update.

  Regards Avnish

  [Tracking ID : SPNET-22647]

• 

  avnish.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 23 February 2022, 4:36 pm EST

  Hi Piet,

  As per the development team, In case there is no FormatString, if you are using LegacyBehaviors.Style or export to XLS, the data type of cell value will be used to detect the exported format string. It’s old/legacy behavior of Spread. But if you don’t use LegacyBehaviors.Style and export to XLSX, the data type of cell value won’t be used to detect exported format string. Then, the cell will have the General number format. You need to set the FormatString property and modify your code as shown below:

  FarPoint.Win.Spread.CellType.GeneralCellType genlcell = new FarPoint.Win.Spread.CellType.GeneralCellType();
        System.Globalization.NumberFormatInfo numform = (System.Globalization.NumberFormatInfo)System.Globalization.NumberFormatInfo.CurrentInfo.Clone();
        numform.NumberDecimalDigits = 2;      
        genlcell.NumberFormat = numform;
        genlcell.NegativeRed = true;
  
        //Add formatstring 
        genlcell.FormatString = "n"; 
  
        Decimal myCurrency = new Decimal(123456.125);      
        fpSpread1.ActiveSheet.Cells[0, 0].CellType = genlcell;
        fpSpread1.ActiveSheet.Cells[0, 0].Value = "myCurrency";     
   fpSpread1.SaveExcel("test.xlsx", FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat);
  

  Regards Avnish

• 

  p.feenstra

  • Post Options:
  • Link
    Copy

  Posted 25 February 2022, 5:54 am EST

  Hi Avnish,

  Spend some time on the issue this afternoon but your suggestions do not work at all. Imported in Spread again the CellType = None.

  What did I do:

  • created a new Spread by code (LagacyBehaviours cannot be set)
  • for every sheet I cloned the original one
  • (re)set the number colums to the desired format
  • removed the hidden columns I don’t want to export
  • added the sheet to the new Spread

  The issue was (and is) that the decimal point is all over the place. Imho, this seems to be a bug or a shortcoming.

  Regards Piet

• 

  avnish.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 28 February 2022, 6:32 pm EST

  Hi Piet,

  Please refer to the sample attached. You can create a Spread instance with LegacyBehaviors by passing the LegacyBehaviors in the constructor. When you have the LegacyBehaviors.Style flag set then the number format will be exported automatically even without the FormatString. If you do not use the LegacyBehavior then you will need to set the FormatString.

  Regards Avnish Spread_Test_Export.zip

• 

  p.feenstra

  • Post Options:
  • Link
    Copy

  Posted 2 March 2022, 11:40 pm EST

  Hi Avnish,

  The export to Excel works fine now. But after loading the xlsx file in Spread the issue still occurs. (decimal setting has no effect). Maybe you could take a look at that. It’s not what you would expect.

  Regards Piet

• 

  avnish.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 3 March 2022, 6:17 pm EST

  Hi Piet,

  Could you please share a sample replicating the issue? As we mentioned before, the export works fine in the sample that we previously sent. The decimal place is also exported correctly.

  Regards Avnish

• 

  p.feenstra

  • Post Options:
  • Link
    Copy

  Posted 3 March 2022, 7:09 pm EST

  Hi Avnish,

  At this moment I have no time to create a sample for you. But look in my previous reply about how I did it. Looks like you forgot to clone the sheets…

  Regards Piet

• 

  avnish.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 6 March 2022, 8:34 pm EST

  Hi Piet,

  We cloned the sheet and still did not see the issue. Please refer to the sample attached. Could you please modify our sample according to what you are doing?

  Regards AvnishSpread_Test_Export_test2.zip

• 

  p.feenstra

  • Post Options:
  • Link
    Copy

  Posted 13 March 2022, 9:59 pm EST

  Hi Avnish,

  I still don’t have time for this but in the original sheets I have number cells where the thousand separator is defined as a space and the decimal separator as a comma. You might want to check out that combination.

  Regards Piet

• 

  avnish.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 14 March 2022, 3:50 pm EST

  Hi,

  We used the last sample we attached with French CultureInfo where the thousand separator is a space and the decimal separator is a comma. The export worked fine in this case too.

  Regards Avnish

• 

  lindabancher

  • Post Options:
  • Link
    Copy

  Posted 28 June 2022, 7:42 pm EST

  When you export data to Excel, Access creates a copy of the selected data, and then stores the copied data in a file that can be opened in Excel.