Eric White's Blog

I am trying to create a simple spreadsheet in a MemoryStream, add some sample data, add a formula, and then download to the user’s browser. I end up with an empty file on every try. My code follows. Can someone give some advice?

public Stream FetchNewMemorySpreadsheet()
{
// Declare the object to return from the method.
SpreadsheetDocument spreadsheet = null;

//
try
{
// Make sure that the MemoryStream is valid.
if (_memStream == null) _memStream = new MemoryStream();

// Create a new Spreadsheet Document using the MemoryStream as storage.
spreadsheet = SpreadsheetDocument.Create(_memStream, SpreadsheetDocumentType.Workbook);

// Add a WorkbookPart to the document.
var workbookpart = spreadsheet?.AddWorkbookPart();

// Verify that we obtained a valid WorkbookPart.
if (workbookpart == null)
{
throw new Exception(“Error creating Workbook.”);
}
// Create a new Workbook.
workbookpart.Workbook = new Workbook();

// Add a WorksheetPart.
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
if (worksheetPart == null)
{
throw new Exception(“Error creating Worksheet.”);
}
// Create a new Worksheet using a blank SheetData object.
worksheetPart.Worksheet = new Worksheet(new SheetData());

// Add a sheets list.
var sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());

// Verify that we obtained a Sheets collection.
if (sheets == null)
{
throw new Exception(“Error creating Sheets.”);
}
// Append the new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
// Set the associated Worksheet Part ID.
Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
// Set the Sheet ID.
SheetId = 1,
// Set the Name of the new Sheet.
Name = “SampleSheet”
};
// Add the new Sheet to the List of children of the Sheets collection.
sheets.AppendChild(sheet);

// Get the sheetData cell table.
var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

// Add a row to the cell table.
var row = new Row() { RowIndex = 1 };
sheetData.Append(row);

// In the new row, find the column location to insert a cell in A1.
Cell refCell = null;
foreach (var cell in row.Elements<Cell>())
{
if (string.Compare(cell.CellReference.Value, “A1”, StringComparison.OrdinalIgnoreCase) <= 0) continue;
refCell = cell;
break;
}

// Add the cell to the cell table at A1.
var newCell = new Cell() { CellReference = “A1” };
row.InsertBefore(newCell, refCell);

// Set the cell value to be a numeric value of 100.
newCell.CellValue = new CellValue(“100”);
newCell.DataType = new EnumValue<CellValues>(CellValues.Number);

// Close the document.
spreadsheet.Close();

//
_memStream.Flush();

//
_memStream.Seek(0, SeekOrigin.Begin);

//
return _memStream;
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
//
spreadsheet?.Dispose();
}
}