你这里有一些问题。
首先，你要把row添加到Sheet中，但它需要被添加到SheetData中。最简单的方法是保留对SheetData对象的引用，以便我们以后使用它：

SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
...
sheetData.Append(row);

其次，需要为每个单元格显式给予数据类型，因为如果没有指定数据类型，则默认值为number：

Cell header1 = new Cell() { CellReference = "A1", CellValue = new CellValue("Interval Period Timestamp"), DataType = CellValues.String };

最后，单元格引用不完全正确。您将所有内容添加到一行，但添加了对第1到4行的引用（A1-A4）。假设单元格在代码中被称为“标题”，我猜您实际上需要单元格A1-D1中的值，在这种情况下，您只需要更新CellReference值。如果您实际上需要A1-A4中的值，那么您可以'我需要将每个单元格添加到一个新行。
完整的程式码清单（假设您需要储存格A1-D1）为：

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("C:\\testpdfs\\mytest.xlsx", SpreadsheetDocumentType.Workbook))
{
    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    SheetData sheetData = new SheetData();
    worksheetPart.Worksheet = new Worksheet(sheetData);

    // Add Sheets to the Workbook.
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
        AppendChild<Sheets>(new Sheets());

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet()
    {
        Id = spreadsheetDocument.WorkbookPart.
        GetIdOfPart(worksheetPart),
        SheetId = 1,
        Name = ViewBag.Title
    };

    Row row = new Row() { RowIndex = 1 };
    Cell header1 = new Cell() { CellReference = "A1", CellValue = new CellValue("Interval Period Timestamp"), DataType = CellValues.String };
    row.Append(header1);
    Cell header2 = new Cell() { CellReference = "B1", CellValue = new CellValue("Settlement Interval"), DataType = CellValues.String };
    row.Append(header2);
    Cell header3 = new Cell() { CellReference = "C1", CellValue = new CellValue("Aggregated Consumption Factor"), DataType = CellValues.String };
    row.Append(header3);
    Cell header4 = new Cell() { CellReference = "D1", CellValue = new CellValue("Loss Adjusted Aggregated Consumption"), DataType = CellValues.String };
    row.Append(header4);

    sheetData.Append(row);

    sheets.Append(sheet);

    workbookpart.Workbook.Save();

    // Close the document.
    spreadsheetDocument.Close();
    return View();

}