Excel export

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

  • 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
  • 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]
  • 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
  • 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
  • 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
  • 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

  • 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
  • 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
  • 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
  • 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
  • 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
Need extra support?

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

Learn More

Forum Channels