1

I am using OpenXML spreadsheet package in C# to convert datatable to excel. In code I am writing this to append cells with Numeric Type:

 public void MakeNumericCell(string cellReference, string cellStringValue, Row excelRow)
 {

    //  Add a new Excel Cell to our Row 
    Cell cell = new Cell() { CellReference = cellReference, DataType=CellValues.Number };
    CellValue cellValue = new CellValue(cellStringValue);       
    cell.Append(cellValue);
    excelRow.Append(cell);

 }

Please note that I am not asking How to convert Text cell to Number or change data type (format) of Excel Cell whose answer already exists on SO.

In my case, the cells do convert to number format in excel sheet. I can find the type of Excel cell by using formula =Type(Cell) which returns 1 if the cell type is numeric. As shown in image, the last column (name of column is Type of Cell) is evaluated using =Type(cell) formula which returns 1 for the C column, but the it still appears in General as highlighted in the image.

Am I doing something wrong here? Or is it an issue in Excel itself?

Numeric cells appear as General

Karan Desai
  • 3,012
  • 5
  • 32
  • 66
  • 2
    In addition to the answer, you may want to use `=CELL("format", A2)`, etc to check the formatting of the cell. For reference check [TYPE function](https://support.office.com/en-us/article/TYPE-function-45b4e688-4bc3-48b3-a105-ffa892995899) and [CELL function](https://support.office.com/en-us/article/CELL-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf). – Keyur PATEL Sep 20 '17 at 03:30

1 Answers1

4

You've misunderstood what the Type function does. It returns the type of the value, not the format of the cell.

If you leave the formatting alone, and change the contents of the cell to "test," the type will change to 2. If you change it to "true" the type will come back as 4. It has nothing to do with the formatting of the cell, which is independent of the content.

John Wu
  • 50,556
  • 8
  • 44
  • 80
  • Oh..That means cell format and cell type are altogether different? Can you please share how to modify the 'format' from 'General' to 'Number'? – Karan Desai Sep 20 '17 at 03:30
  • You've already found the formatting dropdown (the one that says "General"). Click it and pick a different option. – John Wu Sep 20 '17 at 03:32
  • 1
    I am sorry to be not clear. I want to change it programatically using C#. Not directly from Excel. – Karan Desai Sep 20 '17 at 03:32
  • 1
    Try reading & trying suggestions from [Changing DataType of cell to Double](https://stackoverflow.com/a/24256591/6741868) and [Applying % number format to a cell value using OpenXMl](https://stackoverflow.com/a/7900397/6741868). The second one changes to percentage, but you can modify it for number format. – Keyur PATEL Sep 20 '17 at 03:34
  • 1
    Well, you could easily find out how-- start recording a macro, then change the dropdown :) Or check [this link](http://www.excelhowto.com/macros/formatting-a-range-of-cells-in-excel-vba/). – John Wu Sep 20 '17 at 03:34