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.

Debrief

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;
        }
    }

Ochoa

Thank you for the code - unfortuanetly, I am starting at a perfectly blank sheet, there are no rows to write to. I will try out the inner code - it looks very promising! – Debrief

its a fresh method which worked well, you can make use of the code where you create the Cell and add the cell value and CellType it is nothing but the formatting of Cell. I hope this gives your answer. – Ochoa