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
Excel export
Posted by: p.feenstra on 3 February 2022, 2:26 am EST
-
p.feenstra
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
Replied 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
Replied 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
Replied 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
Replied 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
Replied 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
Replied 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
Replied 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
Replied 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
Replied 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
Replied 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
