syncfusion xlsio change column type from text to number

Question

I have a .xlsm file which I get from a web page. This file is an exported database, and it has numeric data written as text, numeric information that can't be used in formulas because excel thinks it is a text.

Which would be the most efficient way to convert the column data type from text to number? Right now I have been only capable of doing it with a for loop, but this files can have up to 10.000 registers.

for (int i = 2; i <= 10002; i++)
{
        sheet.Range["A"+i.ToString()].Number = sheet.Range["A" + i.ToString()].Text;
}

I also tried with numberformat property, but it doesn't seem to be related

sheet.Range["A2:A10002"].NumberFormat = "#";

Here is an example of the Excel file. https://drive.google.com/file/d/1Kz98NDYsj7k7zCmGyl-ohz_VErnsyGvV/view?usp=sharing

Answer 1

Syncfusion XlsIO doesn’t support Microsoft Excel’s Convert to Number option when Number Stored as Text. And, there is no other efficient way to get this achieved except looping and setting the number format as below.

Code snippet:

for (int i = 2; i <= 10002; i++)
{
    double value;
    string rowString = i.ToString();
    if (double.TryParse(sheet.Range["A" + rowString].Value, out value))
    {
        sheet.Range["A" + rowString].Number = value;
        sheet.Range["A" + rowString].NumberFormat = "0";
    }
}

Note: I work for Syncfusion.