Problems after migration from Aspose.Cells 5.0.0.0 to Aspose.Cells 7.0.3.0

Aspose.Cells Product Family
1

Hi,

I'm having the following problems while upgrading Aspose.Cells assembly :

  • Encoding is not well reconize, I get chars like ? instead of a char é when I opens a CSV file with ANSI encoding (Encoding.Default)
  • French dates (dd/MM/yyyy) are translated to US format, when the excel file is handled by a server with en-US culture
  • How to save a file with semi colon separator (this option is available in Excel for european countries)

Everything was fine with Aspose.Cells 5.0.0.0.

Thanks

2

Hi,


1) & 2)
Please give us a sample console application to show the issues, we will check it soon.

3) Check the document:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/saving-files.html

I will paste from the document for your reference:

Saving Text Files with Custom Separator

Text files are used to contain spreadsheet data but without any formatting. The file is a kind of plain text file that can have some customized delimiters between its data.

Example:

[C#]

//Instantiate Text File’s Save Options

TxtSaveOptions options = new TxtSaveOptions();

//Specify the separator

options.Separator = Convert.ToChar(“;”);

//Save the file with the options

workbook.Save(“e:\test\abc.txt”, options);

[VB.NET]

'Instantiate Text File’s Save Options

Dim options As New TxtSaveOptions()

'Specify the separator

options.Separator = Convert.ToChar(“;”)

'Save the file with the options

workbook.Save(“e:\test\abc.txt”, options)

3

I did a Windows Forms Application, to show you 1&2 problems, that applications contains our licenses file. How I can send you the application ?

4

Please exclude/remove the license file in the sample application, zip it and post it, we will check it soon.


Thank you.
5

Summary of the problems :

1/ Encoding.Default is not supported while opening a CSV file

  • it works with an XLS file
  • it works with CSV and Aspose.Cells 5.0.0.0

2/ Dates are loosing the leading 0 on days and months

  • it works with an XLS file
  • it works with CSV and Aspose.Cells 5.0.0.0

Attached files :

  • "Aspose - CSV problems.docx" : This file explain the step to reproduce and has screenshot to show the different problems.
  • "ImportEngineTestTool (7.0.3.0).zip" : This zip file contains the test application with Aspose.Cells 7.0.3.0
  • "ImportEngineTestTool (5.0.0.0).zip" : This zip file contains the test application with Aspose.Cells 5.0.0.0
  • "Test import - export.zip" : The zip file contains some CSV / XLS files for the test application
  • "debug - Encoding.Default.png" : The picture shows you what contains "Encoding.Default" on our environment.

Thanks

6

Please let me know if you have successfully reproduced both problems.

7

Hi,


Thanks for the sample project with other files.

I can find the issue as you have mentioned. I tested your both projects, it works fine with v5.0.0 but does not work fine with v7.0.3.2. We need to investigate your issue. I have logged a ticket with an id: CELLSNET-40140. We will look into it soon.

thank you.
8

Hi,

1/ I've find another issue, the leading 0 are removed.

You can try with the attached file and ImportEngineTestTool (7.0.3.0) (column "Matricule").

2/ About the encoding problem, It works if I set the encoding setting to Encoding.Default in the the TxtLoadOptions, so it seems to be "only" a problem of encoding detection (but that's not an option for me, since I can't force users to upload excel files in a know encoding type).

3/ Is it possible to have a JIRA account, please?

Thanks

9

Hi,


Thanks for providing another file.

1) & 2)
We have logged it and we will look into it soon.

3) I am afraid, JIRA is our internal issue tracking system, so you cannot access. You may ask us to know about the status of the issue and we will reply you accordingly.

Thank you.

10

Hi,

For encoding issue of CSV file, we have changed the logic of detecting file encoding a bit for other requirements. In v5.0.0, we use Encoding.Default automatically when user does not specify encoding. In v7.0.x, we leave this option for user to set by TxtLoadOptions.Encoding = Encoding.Default. So please add this line of code to keep the same logic and get the same result
with v5.0.0.

For the issue of date format, in fact in v5.0.0 the datetime values have not been parsed but kept as string value when loading from CSV. So their values did not change when you print them as string. In v7.0.x, those values will be parsed into datetime value, but currently it is hard for us to make the date time format to be completely same with the original format in the template file. For your situation, to get the same result as in the CSV file, we think you can use the option:

TxtLoadOption.ConvertNumericData=false

Thank you.

11

Hi,

Thanks for the quick reply.

1/ Encoding issue, I need to explain my needs :

Our import engine (that use Aspose.Cells) is use in a web application by users around the world, users upload excel files to import data into the application, the files are created on their computer.

=> So If I harcode Encofing.Default, does all others encoding will still be correctly handled by our import engine ?

2/ Data formatting problems :

In the test applications that I provided, ConvertNumericData = false is already set, but not in TxtLoadOptions object, I set it in workbook.Settings.ConvertNumericData = false;

When I try to set this option in TxtLoadOptions with :

TxtLoadOptions loadOptions = new TxtLoadOptions();
loadOptions.Separator = separator.Value;
loadOptions.ConvertNumericData = false;
if (encoding != null)
    loadOptions.Encoding = encoding;

workbook = new Workbook(filename, loadOptions);

I get the exception :
System.ArgumentNullException was caught
Message=Value cannot be null.
Parameter name: pattern
Source=System
ParamName=pattern
StackTrace:
at System.Text.RegularExpressions.Regex…ctor(String pattern, RegexOptions options, Boolean useCache)
at ⡇.刺.ڎ(StreamReader ०, Cells א, Char 剃, Boolean 剀)
at Aspose.Cells.Workbook.᫑(Stream ԃ, LoadOptions ᫒)
at Aspose.Cells.Workbook.᫑(String و, LoadOptions ᫒)
at Aspose.Cells.Workbook…ctor(String file, LoadOptions loadOptions)
at ImportEngineTestTool.AsposeExcelWorkBookExtensionMethods.ExportDataTableAsString(Workbook workbook, String filename, Nullable`1 separator, Encoding encoding) in C:\Dev-= Divers =-\ImportEngineTestTool (7.0.3.0)\ImportEngineTestTool\AsposeExcelExtensionMethods.Workbook.cs:line 25
at ImportEngineTestTool.Form1.GetDataTableFromFile(String filename) in C:\Dev-= Divers =-\ImportEngineTestTool (7.0.3.0)\ImportEngineTestTool\Form1.cs:line 155
InnerException:

12

Hi,


1) Yes, everything will be handled and work fine.

2) Could you give us sample template text file here, we will check it soon.
By the way could you also specify the LoadFormat using loadOptions.LoadFormat attribute.
Thank you.
13

I can't set TxtLoadOptions.LoadFormat, it seems it's only a getter (the property comment is wrong ...), see attached picture.

I update my test application for the ConvertNumericData = false problem (see attachment).

14

We upgraded from 4.7.1.0 to 7.0.1.0 and we are seeing the same exception. Our sample code looks like this:


Aspose.Cells.TxtLoadOptions test = new Aspose.Cells.TxtLoadOptions(Aspose.Cells.LoadFormat.CSV);
test.ConvertNumericData = false;

Workbook wb = new Workbook(@“C:\Users\qchy\Desktop\Identifiers- importing multiple registers for multiple assets.csv”, test);

my CSV file is simple, it looks like this:

Card #, Vehicle #, Card Label, Department, Site, Station, Address, Date ,Time, Product ID, Product Description, Quantity, Amount, Total Tax, Federal Tax, State Tax, Other Tax, Odometer, MPG, Cost/Mile, Customer No., Manual, Price,Asset,FluidTypeCode,Readings1,Readings2,Readings 3,Identifiers
3,1,sdg,Fire,111,Blore,Blore1,9/17/2011,7:12 AM,1,Diesel #2,10,100,15,10,5,10,7108,0,0,44,7108,45,1,FTC2,100,200,300,100
4,2,fg,Electrical,222,Mysore,Mysore1,9/17/2011,8:12 AM,2,Diesel #3,20,200,25,20,10,20,8000,0,0,55555,7108,55,1,FTC2,200,300,400,100
5,3,ghhhj,police,333,Tumkur,Tumkur1,9/17/2011,9:12 AM,3,Diesel #4,30,300,35,30,20,30,9000,0,0,6666666,7108,65,1,FTC2,300,400,500,100
3,1,sdg,Fire,111,Blore,Blore1,9/18/2011,10:12 AM,1,Diesel #2,10,100,15,10,5,10,7108,0,0,44,7108,45,2,FTC2,100,200,300,101
4,2,fg,Electrical,222,Mysore,Mysore1,9/18/2011,11:12 AM,2,Diesel #3,20,200,25,20,10,20,8000,0,0,55555,7108,55,2,FTC2,200,300,400,101
5,3,ghhhj,police,333,Tumkur,Tumkur1,9/18/2011,12:12 PM,3,Diesel #4,30,300,35,30,20,30,9000,0,0,6666666,7108,65,2,FTC2,300,400,500,101


15

Hi,

I can find the issue (i.e. found the exception:… Message=Value cannot be null.
Parameter name: pattern) as you have mentioned by a simple CSV file (I created the csv file based on the content in your post):

Sample code:

TxtLoadOptions loadOptions = new TxtLoadOptions(LoadFormat.CSV);
loadOptions.Separator = ‘,’;
loadOptions.ConvertNumericData = false; //If I comment this line, it works fine.
loadOptions.Encoding = Encoding.Default;
Workbook workbook = new Workbook(“e:\test2\MyCSV.csv”, loadOptions);

I have logged it and we will soon look into it.

Thank you.

16

Hi,

Also, since I can't test ConvertNumericData = false solution, can you take care that it will fix the 2 formatting problems observed :

- missing 0 in dates

- missing leading 0 when the cell contains something that can be converted into a numeric

Thanks

17

Hi,

For the issue of TxtLoadOptions.ConvertNumericData=false, we will provide you a fix soon.

For the issue of encoding, we are afraid you have to specify the encoding to get the correct result. In fact if you change your system encoding other than ISO-8859-1, you will cannot open your given csv template file fine in any editor, including notepad and ms excel.

Thank you.

18

Is it possible to have a status on this problem, because right our next product packages are blocked due to this problem ...

It could be an option for us to have a temporary assembly (.NET 4.0) that fix the problem.

Thanks

19

Hi,

For the issue of TxtLoadOptions.ConvertNumericData=false, we will provide you a fix tomorrow.

Thank you for being patient.

20

Hi,

We have fixed this issue. Please download: Aspose.Cells for .NET v7.0.3.4