7

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..

Improve this question
11
  • no it is not supported. i have tried xlPackage.Workbook.Worksheets[1].Save but it is not supported. Commented Aug 15, 2012 at 6:53
  • 2
    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. Commented Aug 15, 2012 at 6:53
  • Is that line the lowest part of the stack trace in the NullReferenceException? Commented Aug 15, 2012 at 6:54
  • my excel file already exists i want to write some informations into its cells. my all code is here. Commented Aug 15, 2012 at 6:59
  • 1
    does saving under another name work? (maybe the error has to do with overwriting an existing file) Commented Aug 15, 2012 at 7:01

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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/

Improve this answer

1 Comment

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

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!

Improve this answer

1 Comment

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.