We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Formattting Issue

I am having a formatting issue with a user in Dublin


She is getting the following results
Amount Absolute Amount
€47.20 €47.20
€34.91 €34.91
€129.18 €129.18
(€10.00) €10.00
(€2.50) €2.50
(€70.00) €70.00
€342.00 €342.00
€114.00 €114.00
(€7.50) €7.50

I am setting the style to number 8.

Range range = worksheet.Cells.CreateRange(1, x, this.GLList.Count, 1);
style.Number = 8;
range.SetStyle(style);
workbook.Save(sPath, SaveFormat.Xlsx);

Hi,


Thanks for providing some details.

Kindly provide your template file (if any), sample code (runnable) and output Excel file here, we will check it soon. Also provide your expected Excel file that you may manually set your desired formatting for the data in MS Excel, this will help us really to evaluate your issue properly to consequently figure it out soon.

PS. Please see the document on how you can specify formatting to a range of cells for your complete reference:
http://www.aspose.com/docs/display/cellsnet/Named+Ranges

Thank you.
I not sure if I was clear, but the issue is not happening to all users.

We have one user in dublin that is reporting this error. Everyone else seems to be fine.

Below is the code I am using. I highlighted the columns that are in question.

string[] dtArry = new string[]
{
"JOURNAL_DATE",
"system_date",
"POSTED_DATE"
};
string[] amtArry = new string[]
{
"Amount",
"AbsAmount"
};

string[] strArry = new string[]
{
"operating_unit",
"Account_No"
};
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Data";
worksheet.Cells.ImportCustomObjects(this.GLList, clmAry, true, 0, 0, ((System.Collections.ICollection)this.GLList).Count, true, "MM/dd/yyyy", false);
string sourceData = "=Data!A1:AN" + this.GLList.Count.ToString();
Aspose.Cells.Style style = new Aspose.Cells.Style();
string[] array = amtArry;
for (int i = 0; i < array.Length; i++)
{
string column = array[i];
int x = System.Array.FindIndex(clmAry, (string r) => r.ToString() == column);
Range range = worksheet.Cells.CreateRange(1, x, this.GLList.Count, 1);
style.Number = 8;
range.SetStyle(style);
}
array = dtArry;
for (int i = 0; i < array.Length; i++)
{
string column = array[i];
int x = System.Array.FindIndex(clmAry, (string r) => r.ToString() == column);
Range range = worksheet.Cells.CreateRange(1, x, this.GLList.Count, 1);
style.Number = 14;
range.SetStyle(style);
}

array = strArry;
for (int i = 0; i < array.Length; i++)
{
string column = array[i];
int colIndex = System.Array.FindIndex(clmAry, (string r) => r.ToString() == column);
// Range range = worksheet.Cells.CreateRange(1, x, this.GLList.Count, 1);
Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Style colStyle = sheet.Cells.Columns[colIndex].Style;
StyleFlag flag = new StyleFlag();
flag.NumberFormat = true;
style.Number = 49;
sheet.Cells.Columns[colIndex].ApplyStyle(colStyle, flag);
}

// Set the Header values
for (int i = 0; i < headerAry.Length; i++)
{

Cell cell = worksheet.Cells[CellsHelper.CellIndexToName(0, i)];
cell.PutValue(headerAry[i]);
}

if (this.SelectedExportFormat.ToLower() == ".xlsx")
{
workbook.Save(sPath, SaveFormat.Xlsx);
}
else
{
workbook.Save(sPath, SaveFormat.CSV);
}

Hi,


It might be locale issue. What is the regional settings/ locale set on your user’s machine in Dublin?
Could you try using the Custom attribute instead of Number in your code:
e.g
you may change the line of code:
style.Number = 8;

to:
style.Custom = “€#,##0.00;[Red]€-#,##0.00”;

if it make any difference there.

Thank you.

is there a way to force the locale?

Hi,


Well, you may try to use Workbook.Settings.Region attribute to set the region/locale.

Thank you.

My current results are better. but the currency is being changed.

Amount Absolute Amount
-€14.98 €14.98
€1.57 €1.57
€99.72 €99.72


I am doing the following.
style.Custom = "$#,##0.00;[Red]$(-#,##0.00)";

Hi,


Thanks for providing us further details.

What’s wrong with the currency symbol, is it not euro “”? Could you elaborate and explain the issue with some screen shots and provide a template Excel file taken on the system of your Dublin user, we will check it soon.

Thank you.

The issue is that the number is suppose to be in dollars($). and when the user is exporting the results for our dublin users, it is displaying in Euros(€)


I want to only display $

thank you

Hi John,


I am afraid, it is not easy to troubleshoot the problem without evaluating the case on our side with the actual sample, and for that we require sample spreadsheet from your end. Anyway, the problem should have been resolved by setting the custom format using the $ symbol for currency. Aren’t you getting the correct results with custom format $#,##0.00;[Red]$(-#,##0.00)? If not, please try setting the Region to Default such as follow.

C#

Aspose.Cells.Workbook document = new Workbook(content);
document.Settings.Region = CountryCode.Default;

Hi,


Well, if you want to use ‘$’ sign for
all different regions, you can try below custom string that would work fine:

e.g

Sample code:


style.Custom = "\$#,##0.00;[Red]\$(-#,##0.00)";

Thank
you.

That is what I have minus the backslash and it still appears as a euro


style.Custom = “$#,##0.00;[Red]$(-#,##0.00)”;

Hi,


Could you try with the following formatting string having backslash in it:
e.g
Sample code:

style.Custom = “$#,##0.00;[Red]$(-#,##0.00)”;

Let us know your feedback.

Thank you.

thank you for your help. looks great

Hi,


Thank you for the confirmation. Please feel free to contact us back in case you need our further assistance with Aspose APIs.