OpenXML Writes Values as String and When Converted to Numbers the Values Change

Question

I'm using OpenXML to write some data into Excel. I've successfully managed to input all the data into a spreadsheet but when I do it's all in String format. This is because I'm mostly utilizing the example code available on MSDN and they insert data into Excel as InsertSharedStringItem (A string). I did however find two ways to convert the cells into numbers.

The first was a simple CellValues.Number instead of CellValues.SharedString but that seems to change all the actual values in cells as well as changing them to a number. An example would be I had 0 in a column now it's 5, 11 is now 0 and 21 is 33, weird stuff like that (and if the number repeats like 0 they all change to, in 0's case, 5). Obviously that won't work.

I then found another method for converting strings to numbers in Excel and it was found here (If you click the link in that answer to go to that users source you need to add 'www.' to the URL). The problem with this solution is that it will convert all the strings into doubles but the values suffer the same mutation as the first solution just in double format this time.

It's also worth noting that my third column is supposed to be a string and doesn't need to be converted to a number but it does it anyways.

This is what I'm working with:

private static void WriteToExcel(string[] contents, char[] desiredColumns)
{
    string filePath = @"D:\Folder\test.xlsx";
    try
    {
        using (SpreadsheetDocument StatsCollection = SpreadsheetDocument.Open(filePath, true))
        {
            string sheetName = "";
            int totalRows = contents.Length / 15;
            int contentsIndex = 0;
            for (uint indexRow = 1; indexRow < totalRows; indexRow++)
            {
                for (int indexCol = 1; indexCol < 16; indexCol++)
                {
                    sheetName = "Sheet1";
                    SharedStringTablePart shareStringPart;
                    if (StatsCollection.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    {
                        shareStringPart = StatsCollection.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    }
                    else
                    {
                        shareStringPart = StatsCollection.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    }
                    int index = InsertSharedStringItem(contents[contentsIndex], shareStringPart);
                    WorkbookPart bookPart = StatsCollection.WorkbookPart;
                    Sheet mySheet = bookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
                    WorksheetPart sheetPart = (WorksheetPart)(bookPart.GetPartById(mySheet.Id));
                    Cell cell = InsertCellInWorksheet(desiredColumns[indexCol - 1].ToString(), indexRow, sheetPart);
                    cell.CellValue = new CellValue(index.ToString());

                    //I added the following if structure to check for numbers
                    if (IsNumeric(contents[contentsIndex].ToString()))
                    {
                        cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                    }
                    else
                    {
                        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                    }

                    /*I removed the following lines                            
                    Stylesheet styleSheet = bookPart.WorkbookStylesPart.Stylesheet;
                    uint _doubleStyleId = createCellFormat(styleSheet, null, null, UInt32Value.FromUInt32(4));
                    cell.StyleIndex = _doubleStyleId;
                    */
                    sheetPart.Worksheet.Save();
                    contentsIndex++;
                }
            }
            Console.WriteLine("Copy Complete.");
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine("Cannot find output Excel file.\n" + ex.Message);
    }
}

And here is the createCellFormat method that is from that other solution. Related to my update. I no longer need this method due to the changes I made but I'll leave it in since it's related to the original question.

private static UInt32Value createCellFormat(Stylesheet styleSheet, UInt32Value fontIndex, UInt32Value fillIndex, UInt32Value numberFormatId)
{
    CellFormat cellFormat = new CellFormat();
    if (fontIndex != null)
    {
        cellFormat.FontId = fontIndex;
    }
    if (fillIndex != null)
    {
        cellFormat.FillId = fillIndex;
    }
    if (numberFormatId != null)
    {
        cellFormat.NumberFormatId = numberFormatId;
        cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
    }
    styleSheet.CellFormats.Append(cellFormat);
    UInt32Value result = styleSheet.CellFormats.Count;
    styleSheet.CellFormats.Count++;
    return result;
}

When I try to convert cell values from strings to numbers it converts as expected but changes the value. Why is this happening and how can I solve this issue as well prevent my one column of actual strings from being changed to a number?

Update:

I managed to fix the string to number conversion by following this solution and I've updated my code accordingly and added the following method.

private static bool IsNumeric(string value)
{
    double output = 0;
    if(Double.TryParse(value, out output))
    {
        return true;
    }
    else
    {
        return false;
    }
}

Now everything that should be a number is a number and everything that needs to remain a string is a string. However the actual values of my numbers are still being altered (numbers that should be 0 are 5, 11 is 0, 21 is 33, etc.)

Answer 1

The problem was that CellValue was only being an index as a string. You should use the actual array contents[contentsindex] for numerics because you want to the display the contents of that array not the value of index. Add the CellValue portion into your if..else... statement as seen below. @BlueBarren and I worked through this together in chat.

if (IsNumeric(contents[contentsIndex]))
{
    cell.DataType = new EnumValue<CellValues>(CellValues.Number);
    cell.CellValue = new CellValue(contents[contentsIndex]);
}
else
{
    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    cell.CellValue = new CellValue(index.ToString());
}