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.
Unfortunately, activation email could not send to your email. Please try again.

Changed regional setting XlsIo Number Format

Thread ID:

Created:

Updated:

Platform:

Replies:

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

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 04:15 AM

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 06:45 PM

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 08:47 AM

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 05:08 PM

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 01:17 AM

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 05:46 PM

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 02:20 AM

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.

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.

;