0

I honestly don't see what I'm missing here.

The code selects an excel file, the excel file is opened with EPPlus and I attempt to edit one of the cells. However, when I try to save, it errors out.

I'm getting the following error on my p.Saveas(): NullReferenceException: Object reference not set to an instance of an object. Error saving file C:\2.xlsx' I can clearly see my path is valid. 

OpenFileDialog odf = new OpenFileDialog();
odf.ShowDialog();
FileInfo file = new FileInfo(odf.FileName);
Console.WriteLine(file);
foreach (DataGridViewRow row in DGVmain.SelectedRows)
{
    using (ExcelPackage p = new ExcelPackage(file))
    {
        using (ExcelWorkbook WBook = p.Workbook)
        {
            using (OfficeOpenXml.ExcelWorksheet WS = WBook.Worksheets.First())
            {
                if (WBook != null)
                {
                    if (WBook.Worksheets.Count > 0)
                    {
                        string k = Convert.ToString(row.Cells[1].Value);
                        string ks = Convert.ToString(row.Cells[2].Value);
                        string kss = Convert.ToString(row.Cells[3].Value);

                        WS.Cells[14, 2].Value = k + ",  " + ks + " " + kss;
                    }
                } 
            }
        }
        p.SaveAs(file);
    } 
}

The file is meant to save as the original file, and replace it if it already exists, which is why I use saveas versus save, which will throw an error at that point,

Jonas
  • 6,641
  • 8
  • 29
  • 50
Aroueterra
  • 326
  • 2
  • 15
  • 6
    I know that this has been marked as duplicate, but I have to disagree (sorry Patrick). The reason you're getting the exception is through overuse of the "using" keyword. By using it on your declaration of WBook and WS, you're essentially disposing of them before you save the ExcelPackage, hence the NullReferenceException. You only need a "using" on your declaration of the ExcelPackage - try that and you'll be fine. – Pete Jul 04 '17 at 08:14
  • 1
    Oh, and once you've declared WBook your first job is to say: if (WBook.Worksheets.Count == 0) WBook.Worksheets.Add("Sheet1"); - if you don't then the declaration of WS will throw an exception because if you're creating a new spreadsheet it will not contain any worksheets at first. – Pete Jul 04 '17 at 08:16
  • 1
    Your code can essentially be compressed down to: var path = @"c:\temp\test.xlsx"; var foo = DateTime.Now.ToString("dd MMM yyyy HH:mm:ss"); var file = new FileInfo(path); using (var p = new ExcelPackage(file)) { var wb = p.Workbook; var ws = wb.Worksheets.FirstOrDefault() ?? wb.Worksheets.Add("Sheet1"); ws.Cells[1, 1].Value = foo; p.SaveAs(file); } – Pete Jul 04 '17 at 08:19
  • @Pete, Yeah, I actually figured it out eventually, however, I did not know specifically why the error was occurring, your explanation seems to satisfy that. Regarding the workbook, I'm getting the first sheet of the found workbook, not necessarily adding one to it, since the present data is required. Thanks for an example using a null coalescer, I can never think to use these... the error messages I was getting earlier were very unhelpful, like: Value cannot be null, Null parameter: param. ??? So I wasn't sure what was actually missing. – Aroueterra Jul 04 '17 at 09:00

0 Answers0