Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue Nov 09, 2021 5:06 pm

Hello,
I am trying the Spire XLS solution in C# and i have some troubles about import CSV and use values.

My CSV is :
Title1;Title2;Title3;Title4;SomeStuff1;SomeStuff2;SomeStuff3;SomeStuff4
Value1;Value2;Value3;Value4
Value1;Value2;Value3;Value4
Value1;Value2;Value3;Value4

So what I do is :
Code: Select all
/* -------------------- */
                // Spire XLS
                Workbook wb = new Workbook();
                wb.Version = ExcelVersion.Version2016;
                wb.LoadFromFile("record.csv", ";", 1, 1);
                Worksheet sheet = wb.Worksheets[0];

                Chart chart = sheet.Charts.Add(ExcelChartType.ScatterMarkers);

                // I have a problem here because the range is not a number value
                chart.DataRange = sheet.Range["L2:L20"];
                chart.SeriesDataFromRange = false;

                chart.LeftColumn = 1;
                chart.TopRow = 6;
                chart.RightColumn = 9;
                chart.BottomRow = 25;

                chart.ChartTitle = "Scatter Chart";
                chart.ChartTitleArea.IsBold = true;
                chart.ChartTitleArea.Size = 12;
                // X values
                chart.Series[0].Values = sheet.Range["L2:L20"];
                // Y values
                chart.Series[0].CategoryLabels = sheet.Range["A2:A20"];

                wb.SaveToFile("fileExcel.xlsx", ExcelVersion.Version2016);
                System.Diagnostics.Process.Start("fileExcel.xlsx");
                /* -------------------- */


The issue I have is it didn't convert to number even if i used the ConvertToNumber function.

I tried a lot of things with ConvertToNumber but it still returns "NaN".
Code: Select all
string test = sheet.Range["A2"].NumberText;
                double test1;

                sheet.Range["C2"].ConvertToNumber();
                sheet.Range["C3"].ConvertToNumber();
                sheet.Range["C4"].ConvertToNumber();
                sheet.Range["C5"].ConvertToNumber();
                sheet.Range["C6"].ConvertToNumber();
                sheet.Range["C7"].ConvertToNumber();
                sheet.Range["C8"].ConvertToNumber();
                sheet.Range["C9"].ConvertToNumber();

                // Return string value "-0.14"
                test = sheet.Range["C2"].Value;
                // Return double value NaN
                test1 = sheet.Range["C2"].NumberValue;

                test = sheet.Range["C3"].Value;
                test1 = sheet.Range["C3"].NumberValue;
                test = sheet.Range["C4"].Value;
                test1 = sheet.Range["C4"].NumberValue;
                test = sheet.Range["C5"].Value;
                test1 = sheet.Range["C5"].NumberValue;
                test = sheet.Range["C6"].Value;
                test1 = sheet.Range["C6"].NumberValue;
                test = sheet.Range["C7"].Value;
                test1 = sheet.Range["C7"].NumberValue;
                test = sheet.Range["C8"].Value;
                test1 = sheet.Range["C8"].NumberValue;
                test = sheet.Range["C9"].Value;
                test1 = sheet.Range["C9"].NumberValue;


I searched a lot but I didn't find any solution.

Thanks,
Simon

Simon_Emarre
 
Posts: 6
Joined: Tue Nov 09, 2021 4:49 pm

Wed Nov 10, 2021 3:49 am

Hello,

Thank you for your inquiry.
I simulated a CSV file and did an initial test, and found that using "sheet.Range["C2"].ConvertToNumber();" to convert the text format to the number format is completely correct. The version I used is the latest version(Spire.XLS Pack(Hotfix) Version:11.10.5). If you were not using the latest version, please first give it a try. If the issue still exists after trying, please provide the following information for further investigation. You could attach them here or send them to us via email (support@e-iceblue.com). Thanks in advance.
1) Your sample CSV file.
2) Your test environment, such as OS info (E.g. Windows 7, 64-bit) and region setting (E.g. China, Chinese).
3) Your application type, such as Console app (. Net Framework 4.5).

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Wed Nov 10, 2021 5:39 am

Hello Annika,
I still have the problem and I use the 11.10.5 version.
I am sending you an email with all the information you asked.
Thanks,
Simon

Simon_Emarre
 
Posts: 6
Joined: Tue Nov 09, 2021 4:49 pm

Wed Nov 10, 2021 8:52 am

Hi,

Thank you for sharing the information via email.
I tested your case and reproduced your issue after setting the system language to French. Please use the following code to solve this issue. If there is any question, please feel free to write back.
Code: Select all
CultureInfo cc = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;

Workbook wb = new Workbook();
wb. Version = ExcelVersion.Version2016;
wb.LoadFromFile("record.csv", ";", 1, 1);
...
wb.SaveToFile("fileExcel.xlsx", ExcelVersion.Version2016);

Thread.CurrentThread.CurrentCulture = cc;

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Wed Nov 10, 2021 2:14 pm

Hello,
It worked well !
Thanks,
Simon

Simon_Emarre
 
Posts: 6
Joined: Tue Nov 09, 2021 4:49 pm

Thu Nov 11, 2021 1:13 am

Hi Simon,

You're welcome.
If you have other questions about using our products in the future, please feel free to contact us.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Return to Spire.XLS