ExcelWorksheet Dimension NullReferenceException

Question

So I have this code for a window:

public partial class List : Window
{
    DataTable table = null;
    ExcelWorksheet ws = null;
    string user = System.Environment.UserName;

    public void Initialize()
    {
        string path = "Log.xlsx";
        FileInfo file = new FileInfo(path);

        try
        {
            if (File.Exists(path))
            {
                using (ExcelPackage pack = new ExcelPackage(file))
                {
                    bool sheetfound = false;

                    //runs through each sheet to find a specific one
                    foreach (ExcelWorksheet sheet in pack.Workbook.Worksheets)
                    {
                        if (sheet.Name.Equals(user))
                        {
                            sheetfound = true;
                            ws = pack.Workbook.Worksheets[user];
                            break;
                        }
                    }

                    //Creates new sheet if it hasn't found the specific one
                    if (!(sheetfound))
                    {
                        ws = MainWindow.Create_Worksheet(pack);
                        pack.Save();
                    }
                }
            }
            else
            {
                using (ExcelPackage pack = new ExcelPackage(file))
                {
                        ExcelWorksheet ws = MainWindow.Create_Worksheet(pack);
                        pack.Save();
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Exception caught:\n\n" + ex as string, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
        }

        fUpdate(new Object, new RoutedEventArgs);
    }

    public void fUpdate(object sender, RoutedEventArgs e)
    {
        table.Rows.Clear();

        MessageBox.Show(ws.Dimension.End.Row.ToString());
    }
}

and this one from the main window:

public partial class MainWindow : Window
{
    public static ExcelWorksheet Create_Worksheet(ExcelPackage pack)
    {
        ExcelWorksheet ws = pack.Workbook.Worksheets.Add(System.Environment.UserName);

        ws.Cells[1, 1].Value = "Date";
        ws.Cells[1, 2].Value = "Time";

        ws.View.FreezePanes(2, 1);

        return ws;
    }
}

What this is supposed to do right now is, when the second window launches, it sets the Excel file and worksheet. I used Quickwatch to see if it works and it does work, ws gets set to the specific sheet that I wanted and ws.Dimension.End.Row returns 1. However, after it gets out of the try-catch part (once it reaches fUpdate), ws.Dimension.End.Row suddenly throws a NullReferenceException. I checked and ws is still the same ExcelWorksheet object and it didn't go through anything (that I know of) that would change its value. What causes this error? Thanks!

(ws returns the ExcelWorksheet object but ws.Dimensions return the exception)

Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Sir Rufo, Aug 16 '17 at 21:50

score 4 · Answer 1 · answered Aug 15 '17 at 15:51

The Dimension object of the ExcelWorksheet will be null if the worksheet was just initialized and is empty.

For example:

ExcelWorksheet worksheet = new ExcelPackage().Workbook.Worksheets.Add("Sheet1");
Console.WriteLine(worksheet.Dimension.End.Row);

This code will throw a NullReferenceException since the Dimension object is null.

On the other hand:

ExcelWorksheet worksheet = new ExcelPackage().Workbook.Worksheets.Add("Sheet1");
worksheet.Cells[1, 1].Value = "Some text value";
Console.WriteLine(worksheet.Dimension.End.Row);

This code will not throw an exception since the Dimension object was initialized by adding content to the worksheet.

If the loaded ExcelWorksheet already contains data, you will not face this issue.

score 0 · Answer 2 · answered Jul 16 '17 at 16:56

0

You may get NullReferenceException if your file doesn't exists. In this case you're getting into ELSE block and assigning created WorkSheet to a local method variable instead of class variable.

using (ExcelPackage pack = new ExcelPackage(file))
{
    // ExcelWorksheet ws = MainWindow.Create_Worksheet(pack); // wrong
    ws = MainWindow.Create_Worksheet(pack); // right
    pack.Save();
}

answered Jul 16 '17 at 16:56

opewix

4,993
1
20
42

I'll keep that as a precaution soon, thanks! However, I made another system somewhere in the program that creates the file if it doesn't exist already and it does detect that file and go into the `if` block where I actually get what I want – Ulyzses Jul 16 '17 at 17:02

score 0 · Answer 3 · answered Aug 04 '21 at 17:39

For anyone coming along years later like me and facing this issue. This also happens if the ExcelPackage is disposed.

Specifically I had a method where I had

using ExcelPackage excelPackage = new ExcelPackage(fi);
return excelPackage.Workbook.Worksheets.First();

This returns a worksheet, but because the ExcelPackage is disposed when the method is exited, the Dimensions property is null (as are many other properties).

ExcelWorksheet Dimension NullReferenceException

3 Answers3