Office Open XMl SDK Writing Numbers to Sheet

Question

I am trying wo write Numbers from a DataTable to an Datasheet - unfortunately, this does not work as expected, e. g. the DataSheet is corrupted.

I am using the following code:

private void AddDataToSheet(ExcelViewData data, SheetData sheetData)
{
    var excelData = data.WriteableDataTable; 
    
    // this returns a datatable
    // the numbers have a format like "8,1" "8,0" etc.
    for (int i = 0; i < excelData.Rows.Count; i++)
    {
        Row row = new Row();
        //row.RowIndex = (UInt32)i;
        for (int c = 0; c < excelData.Columns.Count; c++)
        {
            Cell cell = new Cell();
            CellValue cellvalue = new CellValue();
            //cell.CellReference = SharedMethods.GetExcelColumnName(i + 1) + (c + 1).ToString();
            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
            cellvalue.Text = excelData.Rows[i][c].ToString().Replace(",",".");
            cell.Append(cellvalue);
            row.Append(cell);
        }

        sheetData.Append(row);
    }
}

Any Idea why this fails? I have seem multiple tutorials with the same approach.

Answer 1

Try out this method:

public void InsertDataTableIntoExcel(SpreadsheetDocument _excelDoc, SheetData    SheetData,  DataTable excelData, int rowIndex = 1)
    {
        if (_excelDoc != null && SheetData != null)
        {
            if (excelData.Rows.Count > 0)
            {
                try
                {
                    uint lastRowIndex = (uint)rowIndex;
                    for (int row = 0; row < excelData.Rows.Count; row++)
                    {
                        Row dataRow = GetRow(lastRowIndex, true);
                        for (int col = 0; col < excelData.Columns.Count; col++)
                        {
                            Cell cell = GetCell(dataRow, col + 1, lastRowIndex);

                            string objDataType = excelData.Rows[row][col].GetType().ToString();
                            //Add text to text cell
                            if (objDataType.Contains(TypeCode.Int32.ToString()) || objDataType.Contains(TypeCode.Int64.ToString()) || objDataType.Contains(TypeCode.Decimal.ToString()))
                            {
                                cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                                cell.CellValue = new CellValue(objData.ToString());
                            }
                            else
                            {
                                cell.CellValue = new CellValue(objData.ToString());
                                cell.DataType = new EnumValue<CellValues>(CellValues.String);
                            }
                        }
                        lastRowIndex++;
                    }
                }
                catch (OpenXmlPackageException ex)
                {
                    throw ex;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else
            {
                OpenXmlPackageException openEx = new OpenXmlPackageException("No data from datatable");
                throw openEx;
            }
        }
        else
        {
            OpenXmlPackageException openEx = new OpenXmlPackageException("Workbook not found");
            throw openEx;
        }
    }