Implement Spreadsheet in ASP.Net Core

[JNickVAParticipant]

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

[JNickVAParticipant]

I should add that I tried to use the OpenXML Tools SDK but that will not load in a ASP.Net Core project.

[Jim SnyderParticipant]

I can’t help you with ASP.Net Core, but when I get no output, it is nearly always because I have the XML mismatched. Since XML definitions have order, the adding of OpenXML parts have to match. Here is the order (also obtainable by opening a spreadsheet renamed to .zip):
`<complexType name=”CT_Worksheet”>
<sequence>
<element name=”sheetPr”/>
<element name=”dimension”/>
<element name=”sheetViews”/>
<element name=”sheetFormatPr”/>
<element name=”cols”/>
<element name=”sheetData”/>
<element name=”sheetCalcPr”/>
<element name=”sheetProtection”/>
<element name=”protectedRanges”/>
<element name=”scenarios”/>
<element name=”autoFilter”/>
<element name=”sortState”/>
<element name=”dataConsolidate”/>
<element name=”customSheetViews”/>
<element name=”mergeCells”/>
<element name=”phoneticPr”/>
<element name=”conditionalFormatting”/>
<element name=”dataValidations”/>
<element name=”hyperlinks”/>
<element name=”printOptions”/>
<element name=”pageMargins”/>
<element name=”pageSetup”/>
<element name=”headerFooter”/>
<element name=”rowBreaks”/>
<element name=”colBreaks”/>
<element name=”customProperties”/>
<element name=”cellWatches”/>
<element name=”ignoredErrors”/>
<element name=”smartTags”/>
<element name=”drawing”/>
<element name=”legacyDrawing”/>
<element name=”legacyDrawingHF”/>
<element name=”picture”/>
<element name=”oleObjects”/>
<element name=”controls”/>
<element name=”webPublishItems”/>
<element name=”tableParts”/>
<element name=”extLst”/>
</sequence>
</complexType>
Note the <sequence> tag*

*This is copied from Vincent Tan’s “Spreasdsheet OpenXML From Scratch”. Vincent also has a chapter on “142 How to export your Open XML spreadsheet in ASP.NET”

[Author]

Posts