Converting Column to Number

Aspose.Cells Product Family
1

I have simple switch case statement C# .Net, to format and set style for each column,
I would like to convert column 1 to Number
I tried using Number style but still getting the output as string.

                              case 1:
                            wkSheet.Cells.SetColumnWidth(column, 15);

                            Aspose.Cells.Style numberStyle = wkSheet.Cells[row, column].GetStyle();
                            Aspose.Cells.StyleFlag flag = new StyleFlag();
                            numberStyle = wkSheet.Cells.Columns[1].Style;
                            numberStyle.Number = 1;
                            wkSheet.Cells.Columns[1].ApplyStyle(numberStyle, flag);

                           break;

Thank you

2

@hamadtou,

Please note, by applying numbers formattings only sets display/view for data/values (see the document for your reference) and does not convert the string values to numeric values.

Well, you need to convert the data by yourselves after or before inserting the values into the cells. See the sample code for your reference:
e.g
Sample code:

//Set A1 cell existing value to double. For example, if the cell has value "123.23" but stored as string, it will be converted to double.
Cell cell = worksheet.Cells[0, 0];
double d = double.Parse(cell.StringValue);
cell.PutValue(d);

Once you convert the data (in the cell) to your desired type (numeric, DateTime, etc.), then you may apply the display formatting accordingly.

Let us know if we understand you correctly or you need something else, please elaborate with details and sample Excel file, we will check it soon.

3

Thank you Amjad,
In my case, I exporting generated report from DB to EXCEL, I have some columns contains numbers in text format. I would like to convert them to numbers in the output Excel file. I tired ConvertStringToNumericValue, it worked well but It converted all worksheet to number format.
I am trying to apply it to only specific Columns.
I am not sure I lay down the problem correct.

Regards

4

@hamadtou,

You have some ways to accomplish the task, you may choose any one.

  1. You may use Cells.ImportData() method where you may specify the relevant Boolean atribute to true while importing data from data source (e.g DataTable). See the sample code segment for your reference:
    e.g
    Sample code:

     .......
 ImportTableOptions tableOptions = new ImportTableOptions();
             tableOptions.ConvertNumericData = true;

             worksheet.Cells.ImportData(dt, 0, 0, tableOptions);

  2. If above approach is not suitable to you, you may re-insert data into those columns manually once you have imported all your data into the worksheet cells. For example, you may loop through your specific column cells range and re-insert the data (by parsing/converting to proper type values). See the sample code for your reference:
    e.g
    Sample code:

     ......
 //Get enumerator from your desired range.
  IEnumerator ee = workbook.Worksheets[0].Cells.CreateRange("A2:A100").GetEnumerator();

             while (ee.MoveNext())
             {
                 Cell cell = (Cell)ee.Current;

                 //We may use the overload: Cell.PutValue(string stringValue, bool isConverted) method, set "true" for the second parameter as it will automatically convert the data to property data type.
                 cell.PutValue(cell.StringValue, true);

             }
 ........

Hope, this helps a bit.

5

Thank for your help.
I am totally new to aspose.
I could manage to make it work with the code you provided.
here is part of my code I am trying to convert column 1 from general(string) to number

            Workbook wkBook = new Aspose.Cells.Workbook();
            Worksheet wkSheet = wkBook.Worksheets[0];

            wkSheet.Cells.ImportDataTable(myRpt.dsRpt.Tables[0], true, "A1");
            int maxDataColumn = wkSheet.Cells.MaxDataColumn;
            int maxDataRow = wkSheet.Cells.MaxDataRow;
            for (int row = 0; row<maxDataRow + 1; row++)
            {
                for (int column = 0; column<maxDataColumn + 1; column++)
                {
                    Aspose.Cells.Style style = wkSheet.Cells[row, column].GetStyle();
                    if (row == 0)
                    {
                        style.Font.IsBold = true;
                    }

                    style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                    switch (col)
                    {
                        case 0:
                         wkSheet.Cells.SetColumnWidth(col, 25);
                         break;
                          case 2:
                            wkSheet.Cells.SetColumnWidth(col, 25);
                            // Convert this column to number
                            break;

Thank you for your help.

6

@hamadtou,
You are welcome. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

7

@hamadtou,

We suppose your issue is sorted out now. If you still find the issue, kindly do provide a sample console demo application (runnable), zip the project and post us, we will check it and edit your code to sort it out. Please use dynamic dataset/datatable in code and remove inter-dependencies for external data source or database, so we could execute your code seamlessly and figure it out soon.