NullReferenceException in creating Excel worksheet

Question

i want to fill an excel file and so i use ExcelPackage: Office Open XML Format . but i have an error. my code:

string fileName = "DBE_BAKIM_FORMU" + ".xlsx";
FileInfo fi = new FileInfo(HttpContext.Current.Server.MapPath("~/") + fileName);

using (ExcelPackage xlPackage = new ExcelPackage(fi))
{
    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
    dbeDataContext db = new dbeDataContext();
    CAGRI c = (from x in db.CAGRIs where x.CagriID == ID select x).SingleOrDefault();
    USER u = (from x in db.USERs where x.UserID == Convert.ToInt32(Session["user"]) select x).SingleOrDefault();

    worksheet.Cell(6, 3).Value = c.TalepTarihi.ToShortDateString();
    worksheet.Cell(7, 3).Value = c.TalepTuru;
    worksheet.Cell(8, 3).Value = c.ModulAdi;
    worksheet.Cell(9, 3).Value = c.EkranRaporAdi;
    worksheet.Cell(10, 3).Value = c.VerilenSure;
    worksheet.Cell(11, 4).Value = c.USER.UserName + " " + c.USER.UserSurname;
    worksheet.Cell(12, 4).Value = Convert.ToString(c.USER.UserTel);
    worksheet.Cell(13, 3).Value = c.Aciklama;
    worksheet.Cell(16, 4).Value = u.UserName + " " + u.UserSurname;
    worksheet.Cell(18, 3).Value = Convert.ToString(c.DegerlendirmeTarih);
    worksheet.Cell(19, 3).Value = c.Degerlendirme;
    xlPackage.Save();
}

i have this error in here: xlPackage.Save();

my error says that Object reference not set to an instance of an object.

when i take out xlPackage.Save();, it works and fills in the excel file but it does not save. why am i taking this error?

thanks in advance..

Fraktur

no it is not supported. i have tried xlPackage.Workbook.Worksheets[1].Save but it is not supported. – Fraktur

Well, seems like xlPackage itself is not null since you use it before. So please post the entire Stack Trace as it's something internal in the ExcelWorksheet class. – Refractometer

Is that line the lowest part of the stack trace in the NullReferenceException? – Scuba

my excel file already exists i want to write some informations into its cells. my all code is here. – Fraktur

does saving under another name work? (maybe the error has to do with overwriting an existing file) – Orangeman

does saving work if you do not change any of the cells? If so you can narrow it down to a specific line. – Orangeman

codeguru.com/csharp/.net/net_asp/tutorials/article.php/c13123/… this and your code seems to almost identical and apparently it works for him, might it have something to do with you not being allowed to write to that path? I.E does it work if you write to your own disk? – Betimes

when i do not change any cell, it gives the same error.. – Fraktur

@Thomas Lindwall i have tried .xls extended file but error does not change. – Fraktur

Yes, when file already exists, then this error comes, I am also facing this error – Anode

Answer 1

change line 562 of ExcelWorksheet.cs from:

XmlNode pageSetup = _worksheetXml.SelectSingleNode("//d:pageSetup", NameSpaceManager);

to:

XmlNode pageSetup = WorksheetXml.SelectSingleNode("//d:pageSetup", NameSpaceManager);

If the worksheet has not been accessed, calling the public accessor WorksheetXml instead of the private class variable initiallizes it correctly.

Brambling

Answer 2

Catching the null reference exception being thrown by the xlPackage.Save() method, and dumping the stack trace, the problem seems to be getting caused by ExcelWorksheet.cs:line 561 which corresponds to this line in the source:

XmlNode pageSetup = _worksheetXml.SelectSingleNode("//d:pageSetup", NameSpaceManager);

Interestingly enough, if you catch the exception the xlsx files seems to save ok anyway (at least for me it does, there might be some unexpected behaviour, but I couldn't produce any).

I'd try handle the exception and seeing if you file is actually saving. It might be worth raising an issue on the ExcelPackage site if this is a library you're going to be using a lot (I don't)

EDIT:

It seems that the ExcelPackage library is quite old and not maintained. I'd have a look at the EPPlus library, which is based on ExcelPackage. You'll need to tweak a bit of your code but nothing major. Saving works without problem.

http://epplus.codeplex.com/

Batson

i could not try because it gives an error about _worksheetXml and NameSpaceManager. error says that they do not exist in the current context. – Fraktur

Answer 3

I'd recommend looking into the Microsoft.Office.Interop.Excel reference. I wish there was more intellisense, but it's pretty easy to figure out.

I've set my using as:

using Excel = Microsoft.Office.Interop.Excel;

I'm guessing you'll want the application to be invisible:

Excel.Application application = new Excel.Application();
application.Visible = false;

I've not used HttpContext, but if it's a path, then I'd advise using the Path class:

string filename = "DBE_BAKIM_FORMU.xlsx";
Excel.Workbook book = application.Workbooks.Open(Path.Combine(HttpContext.Current.Server.MapPath("~/"), filename));

The code should be similar, here's how to grab a sheet and fill cells:

Excel.Worksheet sheet = book.Worksheets[1];
sheet.Cells[6, 3].Value2 = "Something";

Saving and closing:

book.Save();
book.Close();

The Microsoft.Office.Interop.Excel reference is included in Visual Studio 2012, and 2010 if you're still using that version. Excel is required on the machine with the executable.

Best of luck!

Samarasamarang

Since he's using the Open XML format, I would discourage him from using interop in favor of the Open XML SDK, which doesn't require a local instance of Excel and would be friendlier in a server environment (in case that's an issue): msdn.microsoft.com/en-us/library/office/bb448854.aspx – Pity