Reputation: 4863
Aspose Worksheet not parsing decimal numbers correctly on German server; ignoring period delimiter (123.45 -> 12345)
I have an import task that parses a huge .dat file as an Aspose Workbook.
This was the original code:
public static List<QueryResult> GetAsposeExcelDocumentWithSheetIndex(string filename, string propertyNames, int sheetIndex = 0, int skipRows = 0, int columnToScanForValidEntries = 0, Stream stream = null)
{
Sitecore.Diagnostics.Log.Info("Begin GetAsposeExcelDocumentWithSheetIndex: Filename: " + filename, typeof(UploadHelper));
var loadOptions = new Aspose.Cells.LoadOptions(LoadFormat.Excel97To2003);
if (filename.EndsWith(".xlsx"))
{
loadOptions = new Aspose.Cells.LoadOptions(LoadFormat.Xlsx);
}
else if (filename.EndsWith(".dat") || filename.EndsWith(".csv"))
{
loadOptions = new Aspose.Cells.LoadOptions(LoadFormat.CSV);
}
//var fsStats = new StreamWriter(HttpContext.Current.Server.MapPath("/_uploads/ftp/import.txt"), false);
//fsStats.WriteLine(DateTime.Now.ToString("h:mm:ss") + " - Opening file: " + filename);
//fsStats.Flush();
loadOptions.ParsingFormulaOnOpen = false;
try
{
var workbook = stream != null ? new Workbook(stream: stream, loadOptions: loadOptions) : new Aspose.Cells.Workbook(filename, loadOptions);
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//fsStats.WriteLine(DateTime.Now.ToString("h:mm:ss") + " - Opened sheet");
//fsStats.Flush();
var list = new List<QueryResult>();
for (int i = skipRows, loopTo = worksheet.Cells.Rows.Count + skipRows - 1; i <= loopTo; i++)
{
//fsStats.WriteLine(DateTime.Now.ToString("h:mm:ss") + " - Reading row: " + i);
//fsStats.Flush();
if (worksheet.Cells.GetCell(i, columnToScanForValidEntries) != null &&
worksheet.Cells.GetCell(i, columnToScanForValidEntries).Value != null)
{
NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
var obj = new QueryResult();
var propNames = propertyNames.Split(',');
for (int j = 0, loopTo1 = propNames.Length - 1; j <= loopTo1; j++)
{
Cell cellInfo = worksheet.Cells.GetCell(i, j);
if (cellInfo != null)
{
// specific excluded properties
var propName = propNames[j];
var parseAsString = PropNameShouldNeverBeParsedAsDate(propName);
if ((cellInfo.Type == CellValueType.IsDateTime) && !parseAsString && cellInfo.DateTimeValue.Year > 2010 && !cellInfo.Name.StartsWith("X") && !cellInfo.Name.StartsWith("Y") && !cellInfo.Name.StartsWith("AC")) // this second check is bc some dates weren't return as a DateTime type in the Iplot file
{
// Colmun X and Y should NEVER be a date time
obj.Properties.Add(propNames[j], cellInfo.DateTimeValue);
}
else
{
obj.Properties.Add(propNames[j], cellInfo.StringValue);
}
}
else
{
obj.Properties.Add(propNames[j], null);
}
}
list.Add(obj);
}
}
//fsStats.WriteLine(DateTime.Now.ToString("h:mm:ss") + " - Done reading");
//fsStats.Flush();
//fsStats.Close();
return list;
}
catch (Exception ex)
{
Sitecore.Diagnostics.Error.LogError("Error GetAsposeExcelDocumentWithSheetIndex:" + ex.ToString() + ex.StackTrace);
//fsStats.WriteLine(DateTime.Now.ToString("h:mm:ss") + " - Error: " + ex.ToString());
//fsStats.Flush();
//fsStats.Close();
}
return null;
}
This worked as expected on my local, however the production website is hosted in Germany. The code runs correctly when manually triggered from the admin page (which requires being logged into Sitecore), but when run automatically as a scheduled task, it fails because it's not parsing numbers correctly
public static List<QueryResult> GetAsposeExcelDocumentWithSheetIndex(string filename, string propertyNames, int sheetIndex = 0, int skipRows = 0, int columnToScanForValidEntries = 0, Stream stream = null)
{
//var fsStats = new StreamWriter(HttpContext.Current.Server.MapPath("/_uploads/ftp/import.txt"), false);
//fsStats.WriteLine(DateTime.Now.ToString("h:mm:ss") + " - Opening file: " + filename);
//fsStats.Flush();
loadOptions.ParsingFormulaOnOpen = false;
try
{
var workbook = stream != null ? new Workbook(stream: stream, loadOptions: loadOptions) : new Aspose.Cells.Workbook(filename, loadOptions);
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//fsStats.WriteLine(DateTime.Now.ToString("h:mm:ss") + " - Opened sheet");
//fsStats.Flush();
var list = new List<QueryResult>();
for (int i = skipRows, loopTo = worksheet.Cells.Rows.Count + skipRows - 1; i <= loopTo; i++)
{
//fsStats.WriteLine(DateTime.Now.ToString("h:mm:ss") + " - Reading row: " + i);
//fsStats.Flush();
if (worksheet.Cells.GetCell(i, columnToScanForValidEntries) != null &&
worksheet.Cells.GetCell(i, columnToScanForValidEntries).Value != null)
{
NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
var obj = new QueryResult();
var propNames = propertyNames.Split(',');
for (int j = 0, loopTo1 = propNames.Length - 1; j <= loopTo1; j++)
{
Cell cellInfo = worksheet.Cells.GetCell(i, j);
if (cellInfo != null)
{
// specific excluded properties
var propName = propNames[j];
var parseAsString = PropNameShouldNeverBeParsedAsDate(propName);
if ((cellInfo.Type == CellValueType.IsDateTime) && !parseAsString && cellInfo.DateTimeValue.Year > 2010 && !cellInfo.Name.StartsWith("X") && !cellInfo.Name.StartsWith("Y") && !cellInfo.Name.StartsWith("AC")) // this second check is bc some dates weren't return as a DateTime type in the Iplot file
{
// Colmun X and Y should NEVER be a date time
obj.Properties.Add(propNames[j], cellInfo.DateTimeValue);
}
else
{
var value = cellInfo.StringValue;
if (cellInfo.Type == CellValueType.IsNumeric || propName.Contains("NUM_VALUE"))
{
try
{
if (cellInfo.StringValue == "2.074.927" || cellInfo.DoubleValue == 2074927 || cellInfo.DoubleValue == 2074.927)
{
Sitecore.Diagnostics.Log.Info("Value: " + cellInfo.Value.ToString() + " StringValue: " + cellInfo.StringValue + " DoubleValue: " + cellInfo.DoubleValue.ToString(nfi), typeof(UploadHelper));
Sitecore.Diagnostics.Log.Info("Convert.ToDouble Value: " + Convert.ToDouble(cellInfo.Value) + ", " + Convert.ToDouble(cellInfo.Value).ToString(nfi), typeof(UploadHelper));
Sitecore.Diagnostics.Log.Info("DoubleValue: " + (cellInfo.DoubleValue) + ", " + (cellInfo.DoubleValue).ToString(nfi), typeof(UploadHelper));
Sitecore.Diagnostics.Log.Info("StringValue: " + (cellInfo.StringValue), typeof(UploadHelper));
Sitecore.Diagnostics.Log.Info("FloatValue: " + (cellInfo.FloatValue) + ", " + (cellInfo.FloatValue).ToString(nfi), typeof(UploadHelper));
Sitecore.Diagnostics.Log.Info("StringValueWithoutFormat: " + (cellInfo.StringValueWithoutFormat), typeof(UploadHelper));
Sitecore.Diagnostics.Log.Info("IntValue: " + (cellInfo.IntValue), typeof(UploadHelper));
}
if (Convert.ToDouble(cellInfo.Value).ToString() != value)
{
Double val;
if (Double.TryParse(value, System.Globalization.NumberStyles.Any, nfi, out val))
{
Sitecore.Diagnostics.Log.Info("Using value " + val, typeof(UploadHelper));
obj.Properties.Add(propNames[j], val);
}
else
{
Sitecore.Diagnostics.Log.Info("Failed to parse as decimal, using stringValue " + value, typeof(UploadHelper));
obj.Properties.Add(propNames[j], value);
}
}
else
{
Sitecore.Diagnostics.Log.Info("Value as double matches StringValue", typeof(UploadHelper));
obj.Properties.Add(propNames[j], Convert.ToDouble(cellInfo.Value));
}
}catch(Exception ex)
{
Sitecore.Diagnostics.Log.Info(ex.Message, typeof(UploadHelper));
obj.Properties.Add(propNames[j], cellInfo.StringValue);
}
}
else
{
obj.Properties.Add(propNames[j], cellInfo.StringValue);
}
}
}
else
{
obj.Properties.Add(propNames[j], null);
}
}
list.Add(obj);
}
}
The reason why I added all the additional code for numeric values is because on the German server, cellInfo.StringValue returns an improperly formatted number as a string - it uses periods instead of commas, so large decimal values end up being returned like 12.703.1005 instead of 12,703.1005.
So, I tried to return cellInfo.DoubleValue instead when the cell is numeric, but that ALSO returns wrong. Some decimals like 12.7 are being interpreted as dates, so cellInfo.DoubleValue is entirely wrong, but cellInfo.StringValue gives me the correct number (as a string). But the one thing that's still not working either as strings or decimals is large numbers that have a comma and a decimal.
In the second block of code you can see all the logging I added in. Here is the output on my local, for parsing a value of 2074.927
27908 10:47:59 INFO Value: 2074.927 StringValue: 2074.927 DoubleValue: 2074.927
27908 10:48:02 INFO Convert.ToDouble Value: 2074.927, 2074.927
27908 10:48:04 INFO DoubleValue: 2074.927, 2074.927
27908 10:48:07 INFO StringValue: 2074.927
27908 10:48:10 INFO FloatValue: 2074.927, 2074.927
27908 10:48:21 INFO StringValueWithoutFormat: 2074.9270000000001
27908 10:48:24 INFO IntValue: 2074
and here is the output on the German server:
ManagedPoolThread #12 15:36:04 INFO Value: 2074927 StringValue: 2.074.927 DoubleValue: 2074927
ManagedPoolThread #12 15:36:04 INFO Convert.ToDouble Value: 2074927, 2074927
ManagedPoolThread #12 15:36:04 INFO DoubleValue: 2074927, 2074927
ManagedPoolThread #12 15:36:04 INFO StringValue: 2.074.927
ManagedPoolThread #12 15:36:04 INFO FloatValue: 2074927, 2074927
ManagedPoolThread #12 15:36:04 INFO StringValueWithoutFormat: 2074927
ManagedPoolThread #12 15:36:04 INFO IntValue: 2074927
On the German server, I don't have a single instance of the correct number. It early strips the decimal place (returning the number several orders of magnitude larger than it should be), or it gives me a string value that has a period instead of a comma, which I cannot parse as a decimal.
Why is the German server messing this up? How can I change my code to parse correctly on the German server? As you can see in the code I'm already trying to use NumberFormatInfo to give me the correct delimiters but it doesn't matter since the DoubleValue is returning with no delimeter to begin with.
Upvotes: 1
Views: 799
Answers (2)
Reputation: 1931
@Anshul Samaiyar,
If you could specify your custom formatting, i.e., "## ###.##%" for the given value to the cell in MS Excel manually, you will also get this result ("1231123.%"). So, Aspose.Cells works the same way as MS Excel does. Your custom formatting is not right. Please see the following sample code with (updated) custom formatting and give it a try, it will give you expected results.
e.g.
Sample code:
//Create a new (empty) workbook
Workbook workbook = new Workbook();
//Get the first (default) worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
//Get A1 cell
Cell cell = worksheet.getCells().get("A1");
//Specify double value
Double dataValue = 12311.23;
dataValue = Double.parseDouble(dataValue.toString());
cell.putValue(dataValue);
//Create a style and set the custom formattings
Style cellStyle = workbook.createStyle();
cellStyle.setCustom("## ###.##\\%");
//Apply the style to the cell
cell.setStyle(cellStyle);
//Autofit column to show all values
worksheet.autoFitColumn(0);
//Save the Excel file
workbook.save("f:\\files\\out1.xlsx");
//Save to PDF file
workbook.save("f:\\files\\out12.pdf");
Hope this helps, you may also post queries in the dedicated forums.
Upvotes: 1
Reputation: 69
Facing similar issue where
dataValue = 12311.23;
dataValue = Double.parseDouble(dataValue.toString());
cellStyle.setCustom("## ###.##%");
is not getting change to 12 311.23 but getting g converted to 1231123.% any solution for the issue is welcome.
Upvotes: 0
Related Questions
- Power App - Dutch Number format and 2 Decimal Place check for Dutch or UK number formats
- Convert scientific number format to number in german Excel
- Decimal place issue with Excel
- Decimal number in string-data-type with large amount of decimals always interpreted as large integer (regional decimal separator issue)
- Parse US number format in German Excel
- Inconsistent behavior of conversion to/from locales
- Why does Excel parse numbers with more than two decimal places as whole numbers?
- Incorrect conversion when decimal point embedded in VT_BSTR and German locale used
- German UI Culture de-DE decimal changing to comma value issue in asp.net
- Aspose Cells Numeric Formatting With Processed Datasource