We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on : November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Changed regional setting XlsIo Number Format

Thread ID:

Created:

Updated:

Platform:

Replies:

114773 Dec 7,2013 03:57 PM UTC Sep 3,2014 06:20 AM UTC ASP.NET Web Forms (Classic) 7
loading
Tags: XlsIO
Roimer Machacón Otero
Asked On December 7, 2013 03:57 PM UTC

Hi,

I am developing reports in Excel using Syncfusion xlsio for asp.net.

I would like to know how to configure the number format when change the regional Setting, example in es-ES the decimal symbol is the comma (,) but in en-US is the point (.)


Another example in es-ES have 1200,50 but in en-US this number change to 120050


Johnson Monohar M [Syncfusion]
Replied On December 12, 2013 09:15 AM UTC

Hi Nestor Alvarado Namen,

Thank you for using Syncfusion products.

By default, XlsIO supports cell number formatting for US culture settings. If a cell is assigned with a value 1200.50 with its number format as "###.#0", then the output will be as below.

 * en-US - 1200.50
 * es-ES -  1200,50
If the provided number format in not valid for the available culture settings, then a default format will be displayed in excel file. So, it is not necessary to change the number format in XlsIO, instead you can fix to US culture settings.

Kindly get back to us if you need more clarification on this along with the scenario you are trying to use.

Thanks,
Johnson

Roimer Machacón Otero
Replied On August 1, 2014 10:45 PM UTC

Hi Johnson

My question is this, We have a multilanguage web application in asp.net using globalization culture, so we are using the XlsIo for generate the excel report for the users. Now when switching from one language to another, the value of the numbers change.

Example in a report for spanish shown 1200,52 but when the report is generate with a login in english shown 120052, where it is not the same value.

Prasanth M [Syncfusion]
Replied On August 6, 2014 12:47 PM UTC

Hi Nestor,

We have tried in different scenarios and are unable to reproduce the issue. Could you please modify the attached sample to reproduce the issue and send us back? It will be helpful to us to analyze further on this. We would request you to use our Direct-Trac Support system for further follow up.

Please let us know if you need any clarification.

Regards,
Prasanth

 


Attachment: Number_Format_67d946e6.zip

Roimer Machacón Otero
Replied On August 20, 2014 09:08 PM UTC

Hi Prasanth M,

See the example it works ok, now when the information is brought of the database ocurrs the error. Example I've a table that return n rows from price of the articles. When generate the report in spanish this work correctly, now when generate in english failure.

Attached images in the zip file where this problem is shown, and with the code that import data of the database

Attachment: Example_71efe621.rar

Ishwarya Narayanan [Syncfusion]
Replied On August 25, 2014 05:17 AM UTC

Hi Nestor,

 

Thank you for updating us.

 

Your machine culture seems to be in Spanish and you are trying to import data and generate the file in US culture In that case, the output is similar to the one you have shared. This can be resolved by setting decimal separator to the required culture. This can be achieved by the highlighted line in the following code snippets.

 

Code:
        private void btnCreateExcel_Click(object sender, System.EventArgs e)

        {                    

            ExcelEngine excelEngine = new ExcelEngine();

 

            IApplication application = excelEngine.Excel;

            application.DefaultVersion = ExcelVersion.Excel97to2003;

 

            IWorkbook workbook = application.Workbooks.Open(@"../../Data/Sample.xls", ExcelOpenType.Automatic);

            IWorksheet sheet = workbook.Worksheets[0];

 

            //US Culture

            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

 

            ////Spanish Culture

            //System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("es-ES");

 

            application.DecimalSeparator = System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator;

 

            sheet.ImportDataTable(GetDataTable(), true, 1, 1, false);

 

            workbook.Version = ExcelVersion.Excel97to2003;

            string fileName = @"../../Output/Output.xls";

 

            workbook.SaveAs(fileName);

 

            workbook.Close();          

            excelEngine.Dispose();

 

            System.Diagnostics.Process.Start(Path.GetFullPath(fileName));

 

            this.Close();

        }   

 

        private DataTable GetDataTable()

        {

            DataTable dt = new DataTable("Data");

            dt.Columns.Add("PRECIO");

 

            DataRow dr1 = dt.NewRow();

            dr1["PRECIO"] = 1253.53;

            dt.Rows.Add(dr1);

 

            dt.Rows.Add(dt.NewRow()["PRECIO"] = 1253.53);

            dt.Rows.Add(dt.NewRow()["PRECIO"] = 2848.94);

            dt.Rows.Add(dt.NewRow()["PRECIO"] = 3112.01);

            dt.Rows.Add(dt.NewRow()["PRECIO"] = 3219.3);

            dt.Rows.Add(dt.NewRow()["PRECIO"] = 784.77);

            dt.Rows.Add(dt.NewRow()["PRECIO"] = 1139.58);

            dt.Rows.Add(dt.NewRow()["PRECIO"] = 1271.87);

            dt.Rows.Add(dt.NewRow()["PRECIO"] = 723.77);

 

            return dt;

        }

 

 

Kindly try this approach and get back to us if the issue is resolved.

 

Regards,

Ishwarya Narayanan.


Roimer Machacón Otero
Replied On August 25, 2014 09:46 PM UTC

Hi, Ishwarya Narayanan

The example sent by you work, now with this the numbers are shown how Strings accompanied with green tick in all cells, this is placed for Microsoft Excel.



Ishwarya Narayanan [Syncfusion]
Replied On September 3, 2014 06:20 AM UTC

Hi Nestor,

 

Thank you for updating us.

 

The issue you encountered occurs in versions prior to 12.1. if your working version is beyond 12.1, we strongly recommend you to upgrade to the latest version 12.2 which has more features and enhancements.

 

Latest Version: https://www.syncfusion.com/support/forums/announcements/116964

 

 

Regards,

Ishwarya Narayanan

 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;