Office Open XMl SDK Writing Numbers to Sheet

问题:

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.

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