System.NullReferenceException when trying to create a Sheet Object

Question

I managed to create a workbook programmatically with the only input being an arbitrarily chosen excel file path. I encounter a System.NullReferenceException when trying to create the Sheet object. I suspect that this is because my workbook doesn't have any sheets. How can I debug this?

using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;
using System.IO;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using System.Reflection;

using System.Linq;



namespace ConsoleApp35
{
    class Program
    {
        static void Main(string[] args)
        {

            {

                SpreadsheetDocument spreadsheetDocument =
                SpreadsheetDocument.Create(@"C:\________________.xlsx", SpreadsheetDocumentType.Workbook);
                spreadsheetDocument.AddWorkbookPart();

                {
                    // Add a WorksheetPart.  
                    WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
                    newWorksheetPart.Worksheet = new Worksheet(new SheetData());

                    // Create Sheets object.  
                    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                    string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart);

                    // Create a unique ID for the new worksheet.  
                    uint sheetId = 1;
                    if (sheets.Elements<Sheet>().Count() > 0)
                    {
                        sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    // Give the new worksheet a name.  
                    string sheetName = "mySheet" + sheetId;

                    // Append the new worksheet and associate it with the workbook.  
                    Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                    sheets.Append(sheet);
                }
                Console.WriteLine("All done.");
                Console.ReadKey();
            }
        }
    }
}

Did you try this approach c-sharpcorner.com/article/creating-excel-file-using-openxml ? Clearly you are using MS tutorial: learn.microsoft.com/en-us/office/open-xml/… which was for 2.5 SDK. Now we have never version - maybe this is the issue (you are probably using the most recent one) Give — Piotr
– Piotr, Commented May 10, 2020 at 13:21

Automate This · Accepted Answer · 2020-05-10 16:15:25Z

Try this approach instead. There are just a few things I added/changed and I tested this.

Include references to:

Microsoftbase
DocumentFormat.OpenXML

static void Main(string[] args)
{
    string document = @"C:\Temp\MySpreadsheet.xlsx";

    // Open the document for editing.  
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(document, SpreadsheetDocumentType.Workbook,true))
    {
        // Add a WorkbookPart
        WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();

        // Add a WorksheetPart
        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        var sheetData = new SheetData();

        worksheetPart.Worksheet = new Worksheet(sheetData);
        workbookPart.Workbook.Append(new BookViews(new WorkbookView()));

        spreadsheetDocument.WorkbookPart.Workbook.Sheets = new Sheets();
        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();
        string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart);

        uint sheetId = 1;
        if (sheets.Elements<Sheet>().Count() > 0)
        {
            sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
        }

        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = "TestSheet" };
        sheets.Append(sheet);

        WorkbookView workbookView = workbookPart.Workbook.Descendants<WorkbookView>().FirstOrDefault(); 

        workbookPart.Workbook.Save();
        spreadsheetDocument.Close();

        Console.WriteLine("All Done");
        Console.ReadKey();
    }
}

Thanks Portland Runner. I ended up using ClosedXML since it was faster to export to excel.

Collectives™ on Stack Overflow

System.NullReferenceException when trying to create a Sheet Object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related