Link to home
Start Free TrialLog in
Avatar of Jedidia
JedidiaFlag for United States of America

asked on

OpenXML C# Excel datatype

I am dumping the content of a multidim array into excel via openxml.
It works but when I open the excel file, the numbers are stored as text which displays the little green triangle in the upper left of the cell.

Here is how I add the rows/cells and assign the array value.

  int arrayCounterID = 2;
                for (uint j = 2; j < rplUpdatedData.GetLength(0); j++)
                { 
                    row = new Row() { RowIndex = 7 + j };
                    uint rowNumber = 7+j;
                    
                    for (int i = 0; i < 15; i++)
                    {

                        Cell cell = new Cell()  
                        {
                            CellReference = data[i] + rowNumber.ToString(),                 
                            DataType = CellValues.String,
                            CellValue = new CellValue(rplUpdatedData[j, arrayCellID[i]].ToString()),
                            StyleIndex = 4
                        };

                        row.Append(cell);
                        arrayCounterID++;
                    }             
                sheetData.AppendChild(row);

Open in new window



I add an if statement to test the array value and set the datatype and value.

int arrayCounterID = 2;
for (uint j = 0; j < rplUpdatedData.GetLength(0); j++)
{ 
    row = new Row() { RowIndex = 7 + j };
    uint rowNumber = 7+j;

    for (int i = 0; i < 15; i++)
    {

	Cell cell = new Cell()  
	{ CellReference = data[i] + rowNumber.ToString(), StyleIndex = 4 };

	double isNum = 0;

	if (Double.TryParse(rplUpdatedData[j, arrayCellID[i]].ToString(), out isNum))
	{
	    cell.DataType = new EnumValue<CellValues>(CellValues.Number);
	    cell.CellValue = new CellValue(rplUpdatedData[j, arrayCellID[i]].ToString());
	}
	else
	{
	    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
	    cell.CellValue = new CellValue(rplUpdatedData[j, arrayCellID[i]].ToString());
	}                    
	
	row.Append(cell);
	arrayCounterID++;
    }

sheetData.AppendChild(row);
}

Open in new window



This creates the excel file and the XML source looks ok but the Excel file appears corrupt and will not open.
Any thought on my approach?
Avatar of Norie
Norie

Why not use IsNum?
if (Double.TryParse(rplUpdatedData[j, arrayCellID[i]].ToString(), out isNum))
	{
	    cell.DataType = new EnumValue<CellValues>(CellValues.Number);
	    cell.CellValue = new CellValue(IsNum));
	}
	else
	{
	    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
	    cell.CellValue = new CellValue(rplUpdatedData[j, arrayCellID[i]].ToString());
	}                    
	

Open in new window

This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.