0

When I working with Excel by C#. If I worked in the same worksheet, it's ok. But when I worked in difference worksheets. It has an error : System.NullReferenceException: 'Object reference not set to an instance of an object.'
Anyone can help me?
Sorry about my English.
Thanks so much ``` 
private void GetActiveExcelFile()
{
    try
    {
        
        e = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
    }
    catch (Exception ex)
    {
        Type type = Type.GetTypeFromProgID("Excel.Application");
        e = System.Activator.CreateInstance(type);
    };
    Microsoft.Office.Interop.Excel.Application oExcel = (Microsoft.Office.Interop.Excel.Application)e;
    oWB = oExcel.ActiveWorkbook;
}
bool q1()
{    
    GetActiveExcelFile();
    try
    {   
        Excel.Worksheet esh=new Excel.Worksheet();     
        esh = oWB.Worksheets["sheet 1"]; //My error here: System.NullReferenceException: Object reference not set to an instance of an object.
        if (esh.ListObjects["Table1"].ShowTableStyleRowStripes)
            return true;
    }
    catch { return false }
    return false;
}

bool q2()
{
    GetActiveExcelFile();
    try
    {
        Excel.Worksheet esh=new Excel.Worksheet();
        esh = oWB.Worksheets["sheet 2"];
        if (esh.ListObjects["Table2"].ListColumns["Total"].Range.NumberFormat.ToString() == "0.000" ||
            esh.Cells.Range["B2:B9"].NumberFormat.ToString() == "0.000" ||
            esh.Cells.Range["B1:B9"].NumberFormat.ToString() == "0.000")
            return true;
    }
    catch { return false; }
    return false;
}
Van Le Thanh
  • 81
  • 1
  • 7
  • Did you try debugging? The way the code is written, all errors are swallowed and `oWB` could easily be null when execution reaches this point – Panagiotis Kanavos Jun 30 '22 at 13:55
  • I am debugging and this error in "esh = oWB.Worksheets["sheet 1"];" in bool q1(){...}. Can you help me to fix this error – Van Le Thanh Jun 30 '22 at 13:58
  • If you want to create Excel files it's far easier to use a library like EPPlus, ClosedXML, NPOI and more, to create real Excel files without having to buy and install Excel. A single like `sheet.Cells.LoadFromCollection(items)` could create and fill a table with columns and values coming from a List – Panagiotis Kanavos Jun 30 '22 at 13:59
  • This is a large project in my team, and it just have an error. I can not change to use another library – Van Le Thanh Jun 30 '22 at 14:02
  • That's not `just`. It means nothing is working at all. The way this code is written you can never be certain if it works or not, because objects that need tight lifetime control are stored globally, errors are hidden instead of handled. At the very least, start a new small Console application and try to open and use a single Excel application. Then *read and apply all COM interop guidelines* and the examples shown in many SO questions. That means *not* letting COM objects leak outside the method that created them. Use `using` blocks to ensure they're always closed. – Panagiotis Kanavos Jun 30 '22 at 14:09
  • It means *not* communicating between methods using fields or global stat you know nothing about. In this case, you're trying to use an `oWB` *field* that's obviously null. – Panagiotis Kanavos Jun 30 '22 at 14:11
  • Even if `GetActiveExcel` didn't fail, there may not be any active workbook. The code is leaky, which means it may have left one or more Excel instances open after they were used. Those instances won't have any open workbooks. That's why you need a `using` block, to ensure instances are properly closed. – Panagiotis Kanavos Jun 30 '22 at 14:12
  • I understand the error return null value but I don't know where and how to fix it. If I know how to fix my code, I won't post my problem. I've newbie in programming. – Van Le Thanh Jun 30 '22 at 14:19
  • Read the duplicate, because this really is a duplicate. `oWB` is null. Why? You need to investigate it. Debug your code and check where that value gets assinged - assuming it is. When you get at `oWB=oExcel.ActiveWorkbook`, does `ActiveWorkbook` have a value or is it null? Does *anything* ever close and dispose `oExcel` or does `GetActiveExcelFile` keep returning the same ghost instance? We can't debug your code or guess what else may be going wrong, or where those methods are called from – Panagiotis Kanavos Jun 30 '22 at 14:26
  • A good first step would be to remove all fields and all those `catch` blocks. Instead of assigning a new Excel reference to an `e` field that never gets cleared, turn that into a local variable and return it. Same for all other methods that store heavy COM objects to fields. – Panagiotis Kanavos Jun 30 '22 at 14:28
  • What do you see when you open Task Manager? Do you see multiple Excel instances perhaps, even though none is visible? – Panagiotis Kanavos Jun 30 '22 at 14:29
  • @PanagiotisKanavos if I call q2 before q1 it's work perfect, but if I call q1 before it's has the error. May be I will change the order between q1 and q2. Thank you so much. – Van Le Thanh Jun 30 '22 at 23:36

0 Answers0