Reading european-style decimals (coma format)

Aspose.Cells Product Family
1

Hello, I have an urgent problem -- I received a .csv file where the client has some numbers using a coma style for decilmals. I cannot get the value read in correctly. Code snippet here:

Cell cell = wb.Worksheets[0].Cells["E2"];

Console.WriteLine(cell.StringValue);

the value in the .csv file is 6,01 so I am hoping to see 6.01 as the value or anything indicating that this is a decimal but all the values seem to be read in as 601.

I tried setting the cell Style.Number to 4 but it doesn't seem to help the reading part

The assembly version of my Aspose.Cells dll is 5.0.0.0

Help much appreciated

2

Hi,

Please try this code.

C#


string filePath = @“F:\Downloads\sam\Book1.csv”;


LoadOptions opt = new LoadOptions(LoadFormat.CSV);

opt.LanguageCode = CountryCode.France; //specify code of your choice.

opt.ConvertNumericData = true;


Workbook wb = new Workbook(filePath,opt);

Worksheet ws = wb.Worksheets[0];


Cell cl = ws.Cells[“A1”];

Or you can set the ConvertNumericData to false and use this code. In this code, you will read the string and replace the comma with dot and convert it to double.

C#

string filePath = @"F:\Downloads\sam\Book1.csv";

LoadOptions opt = new LoadOptions(LoadFormat.CSV);
opt.LanguageCode = CountryCode.France; //specify code of your choice.
opt.ConvertNumericData = false;

Workbook wb = new Workbook(filePath,opt);
Worksheet ws = wb.Worksheets[0];

Cell cl = ws.Cells["A1"];

double val = Double.Parse(cl.StringValue.Replace(",", "."));

3

Thank you, this is exacatly what I need.

Just one question -- i do not see LoadOptions class in assembly version 5.0.0.0. Could you let me know in what version of the product the class was introduced? (i.e what's the minimum DLL version to get this working?)

P.S. as a side suggestion it would be great if the API docs on the site listed "introduced in version" information for each type -- not sure how easy it would be but would greatly help.

4

Hi,

Thanks. Please download the latest version Aspose.Cells
for .NET v5.3.0.6.

At first, you should check the release notes for different
official versions of Aspose.Cells for .NET (especially v5.0.0 and 5.1.0
etc.) for API changes.

  1. See Release Notes Aspose.Cells for .NET 5.0.0
  2. See Release Notes Aspose.Cells for .NET 5.1.0


Note: Check the description under “Notable Changes for Existing Users” for API changes.

For older Workbook.Open and Workbook.Save methods, please see the documents for your reference to check what is updated now:

  1. Opening Files
  2. Saving Files

Moreover, please check the documentation of the product for complete details:

  1. Programmer’s Guide

Let me come towards your significant errors that you may encounter, so you can fix them accordingly:

1)
You need to import the relevant namesapaces to your project pages or
use fully qualified naming when declaring objects for classes etc.:

e.g

using System;

using System.Web;

using Aspose.Cells;

using Aspose.Cells.Pivot;

using Aspose.Cells.Charts;

using Aspose.Cells.Drawing;

using Aspose.Cells.Properties;


etc.


2) As I said above, some classes have been renamed.

e.g

i) Validations --> ValidationCollection
ii) PivotTable --> PivotTableCollection

etc.


3) Aspose.Cells.Style property is eliminated/obsoleted now, you should adjust your code to use Aspose.Cells.GetStyle() and Aspose.Cells.SetStyle() methods, it will also enhance the performance to certain extent:

e.g

Your sample code using Style attribute should be updated accordingly, e.g

Style style = wsNdaa.Cells[0, 0].GetStyle();
style.Font.IsBold = true;
wsNdaa.Cells[0, 0].SetStyle(style);
Style style2 = cells["I1"].GetStyle();
style2.HorizontalAlignment = TextAlignmentType.Center;
style2.BackgroundColor = System.Drawing.Color.Navy;
style2.Font.IsBold = true;
cells["I1"].SetStyle(style2);


For complete reference about Cell.GetStyle/SetStyle approach, please see the documents in the section:

  1. Working with Data Formatting


4) And above all, please check Aspose.Cells for .NET API Reference:

5

Hello, I tried the version of the .dll recommended but am having trouble with ConvertNumericData = true. Can you please advise what's wrong? (when I set it to false I do indeed see string value correctly come in with the coma but the approach shown below is preferable to me if i can get it to work.)

content of .csv file is just two lines:

Date;Type;TM;Type
23-02-2011;BT;5,01;M

Code is just as this:

TxtLoadOptions lo = new TxtLoadOptions();

lo.Separator = ';';

lo.LanguageCode = CountryCode.France;

lo.ConvertNumericData = true;

Workbook wb = new Workbook(@"c:\Bugs\CSV_CultureTest.txt", lo);

Cell cell = wb.Worksheets[0].Cells["c2"];

Console.WriteLine(cell.DoubleValue);

Console.WriteLine(cell.StringValue);

6

Hi,

This is actually a bug. We will update you once it is fixed.

I have logged this as CELLSNET-25274 .

7

Hi,

We have fixed the issue of reading csv with specified region setting.

Please download and try the latest fix:
Aspose.Cells
for .NET v7.2.1.4