Error with setting the value of excel worksheet

Question

Without going into too much detail, this is my first project. I am very new at coding and this may be a very simple oversight on my part. I am an engineer and I need to add some "custom properties" to over 1000+ CAD files. I am using excel to assign set properties to each file.

I am using EPPlus.

I have a using OfficeOpenXml; statement.

During debugging, I get an error at line 73: worksheet.Cells[row, 6].Value = file;

System.NullReferenceException: 'Object reference not set to an instance of an object.worksheet was null."

I can see that the value for "var worksheet" at line 64 is null but I can't figure out why...

public static void InsertPropName2()
{
    string ExFile = @"J:\VB Test\Excel\SW.Prop.xlsx";
    var FileInfo = new FileInfo(ExFile);

    List<string> DrwPropName = new List<string>()
    {
        "MPN",
        "Description",
        "OEM Location",
        "Material",
        "Surface Finish",
        "Hardness",
        "Color",
    };

    List<string> PartPropName = new List<string>()
    {
        "MPN",
        "Description",
        "Drawn",
        "Q.A. Approved",
        "Checked",
        "Engineering Approved",
        "Revision",
        "Drawn By:",
        "Drawn Date",
        "Q.A. Approval",
        "Q.A. App. Date",
        "Checked By",
        "Checked Date",
        "Engineering Approval",
        "Engineering App. Date",
    };

    List<int> PartPropType = new List<int>()
    {
        30,
        30,
        30,
        30,
        30,
        30,
        30,
        30,
        64,
        30,
        64,
        30,
        64,
        30,
        64,
    };

    using (ExcelPackage excel = new ExcelPackage(FileInfo))
    {
        string directory = @"J:\Move (Test)\a";
        string[] Files = Directory.GetFiles(directory);

        foreach (string file in Files)
        {
           string WSName = file.Substring(file.Length - 31);

            //line 64
            var worksheet = excel.Workbook.Worksheets[WSName];

            string FileType = file.Substring(file.Length - 6);

            switch (FileType)
            {
                case "SLDDRW":
                    int row = 1;
                    int a = 0;

                    //line 73
                    worksheet.Cells[row, 6].Value = file;
                    foreach (string prop in DrwPropName)
                    {
                        worksheet.Cells[row, 7].Value = DrwPropName[a];
                        worksheet.Cells[row, 8].Value = 30;
                        a++;
                    }

                    break;

                case "SLDPRT":
                    int row2 = 1;
                    int b = 0;
                    worksheet.Cells[row2, 6].Value = file;
                    foreach (string prop in PartPropName)
                    {
                        worksheet.Cells[row2, 7].Value = PartPropName[b];
                        worksheet.Cells[row2, 8].Value = PartPropType[b];
                        b++;
                        row2++;
                    }

                    break;

                case "SLDASM":
                    int row3 = 1;
                    int c = 0;
                    worksheet.Cells[row3, 6].Value = file;
                    foreach (string prop in PartPropName)
                    {
                        worksheet.Cells[row3, 7].Value = PartPropName[c];
                        worksheet.Cells[row3, 8].Value = PartPropType[c];
                        c++;
                        row3++;
                    }

                    break;

                default:

                    break;
            }
        }
        excel.Save();
    }
}

Any help is greatly appreciated. Again sorry if this is a very simple noob error!

edit: I have another class that I run before I run this that creates an excel file with the worksheets in question above so I know the worksheet exists. I have also visual checked to see if that the excel file has the worksheets in question. the code in the other class executes and I have no errors with that part of my project.

The WSName is not declared anywhere else in the class so It is not the same as other NullReferenceException threads.

Part of the code that's in the other class:

using (ExcelPackage excel = new ExcelPackage(FileInfo))
{
    string WSName = file.Substring(file.Length - 31);

    var worksheet = excel.Workbook.Worksheets.Add(WSName);

Looks like there no worksheet with name `WSName` available in the workbook. Did you write this code to create new excel file and create new worksheet in it and write data to it? — Chetan, Aug 22 '18 at 00:37
during debugging when I check for the value at line 64 var worksheet is null — Sunil, Aug 22 '18 at 00:45
WSName is coded the same in both classes in this project it is the name of the CAD file. — Sunil, Aug 22 '18 at 00:58
When you debug through the code, please check the value of `WSName` in your `Immediate Window` using `?WSName`. What value is shown there? — mjwills, Aug 22 '18 at 02:05
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) — VDWWD, Aug 22 '18 at 06:28

score 0 · Answer 1 · answered Aug 22 '18 at 21:14

Thanks for trying to solve the issue. I figured out what the problem was.

WSName was set from code Directory.GetFiles(directory); so I can keep track of the properties to each CAD file.

When the first class member used the value of WSName to create the worksheet. The value for WSName had "\". The actual name of the worksheet omitted the "\" even though I used the "@" symbol in front of the string. It must me a limitation of excel. When the second class member tried to write to the worksheet even though WSName was coded the same way as the first member it could not find it due to the missing "\".

Error with setting the value of excel worksheet

1 Answers1