Use system separators

Hi,
Does aspose.cells support any code to control the checkbox “Use system separators” like the screenshot below? Or do we have a way to control the “Decimal separator” and “Thousands separator” dynamically?
image.png (56.6 KB)

@985664960,

You may use NumberDecimalSeparator and NumberGroupSeparator properties under WorkbookSettings to control it for your needs. See the sample lines of code for your reference.
e.g.
Sample code:

.......
workbook.Settings.NumberDecimalSeparator = '...';//specify the decimal separator
workbook.Settings.NumberGroupSeparator = '... '; //specify the group/thousand separator
......

Please note, Aspose.Cells an only support the settings for formatting values of cells, chart, …etc. in the worksheet. You may choose the CultureInfo (see the sample lines of code below) with your expected separators, or specify them by the properties we mentioned above. However, we cannot support “System” separators. If you found some other features that you need the specified separator but currently you could not apply them by Aspose.Cells APIs, you may describe the scenario and your requirement in details, so we can investigate it further.
e.g.
Sample code:

Workbook workbook = new Workbook();

//Customize the culture information for numbers only for Germany Dutch.
System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("de-DE");
ci.NumberFormat.NumberDecimalSeparator = ".";
ci.NumberFormat.NumberGroupSeparator = ",";
workbook.Settings.CultureInfo = ci;
.......

Hi,
Actually I have tried this code before, and the settings in the excel already success, but I need manually unchecked “Use system separators” to make the formatting work as what I set by code. We have case here, sometimes we need the export excel by aspose to use NumberDecimalSeparator =’.’ , NumberGroupSeparator =’,’ and sometimes we need it be NumberDecimalSeparator =’,’ , NumberGroupSeparator =’.’. Do you have any suggestion?

We need to evaluate if such setting is of MS Excel or it is there at Workbook level. I have logged a ticket with an id “CELLSNET-48458” for your requirements/issue. We will look into it soon. If Excel Workbook contains such a settings in its source data, then we might support it. Once we have an update on it, we will let you know.

@985664960,

We evaluated your issue in details.
By the exported Excel file, we are afraid there is no solution for your requirements:
Firstly, “Use system separators” is not the setting of the generated Excel file, but the setting of MS Excel itself on your machine. We cannot access such kind of settings in any case.

Secondly, the language settings is also of MS Excel itself. There is no setting in the generated Excel file to make MS Excel show the file in specified language. MS Excel always uses the default locale of the machine or the user’s specified language of MS Excel itself.

If you just need to display the generated Excel file with the formatted values, we think you may try to replace cells’ values with the formatted string:

            workbook.Settings.NumberDecimalSeparator = ...; //specify the expected separators
            //or
            //workbook.Settings.Region = ...; //specify the locale of the workbook to use the locale which is using the expected separators
            ...
            int maxRow = cells.MaxDataRow;
            int maxCol = cells.MaxDataColumn;
            for (int i = 0; i <= maxRow; i++)
            {
                for (int j = 0; j <= maxCol; j++)
                {
                    Cell cell = cells.CheckCell(i, j);
                    if (cell != null && cell.IsNumericValue)
                    {
                        cell.PutValue(cell.StringValue);
                    }
                }
            }

Hope, this helps a bit.

Thank you very much

@985664960,
You are welcome.