Implement Spreadsheet in ASP.Net Core

Home Forums SpreadsheetML Implement Spreadsheet in ASP.Net Core

This topic contains 2 replies, has 2 voices, and was last updated by  Jim Snyder 6 years, 10 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #4369

    JNickVA
    Participant

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

    #4370

    JNickVA
    Participant

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

    #4371

    Jim Snyder
    Participant

    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”

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.