Why the code contains error when creating excel file and want to work with it

Question

The entry was:

Create a C# code with the following requirements:

We have 3 sheets with the following names: (records, consumption_kardex, tool_lists). The code will ask for two values when it is started, the first value is for "part number" and the second value is for "operation". After the user enters these two values, the code will filter the column "I" in the records table based on the first value. Then it will filter the column "E" based on the second value. Next, it will calculate the sum of all values in columns F and G in the records table (the sum will be calculated after the I and E column filters are applied). This value will be written to the fourth sheet, column A, which will be created with the name "comparison.xlsx".

Then, the code will filter column A in the tool_lists sheet based on the user-entered first value (part number). In the tool_lists sheet, the code will then multiply columns D and E (D * E) and add all the results of this multiplication together into one result. This result will be written to column B in the fourth sheet named comparison.

Next, in the tool_lists sheet, where we will use filtering again in column A, we will take the values in column B from row 2 and filter column B with them in the consumption_kardex sheet. Then, in the filtered consumption_kardex sheet, we will calculate the sum of the values in column C and write them to the fourth sheet comparison in column C.

The error is at line 17, here is the code:

using System.IO;
using Microsoft.Office.Interop.Excel;

namespace ConsoleApp1
{
    class Program
    {
        public static void Main(string[] args)
        {
            // Získáme vstupní hodnoty od uživatele
            Console.WriteLine("Zadejte číslo dílce:");
            string cisloDilc = Console.ReadLine();
            Console.WriteLine("Zadejte operaci:");
            string operace = Console.ReadLine();

            // Vytvoříme nový sešit s názvem "srovnani.xlsx"
            Workbook workbook = new Workbook();
            workbook.SaveAs("srovnani.xlsx");

            // Vytvoříme instanci třídy Worksheet pro každý sešit
            Worksheet worksheet1 = workbook.Worksheets["zaznamy"];
            Worksheet worksheet2 = workbook.Worksheets["nastrojove_listy"];
            Worksheet worksheet3 = workbook.Worksheets["spotreba_kardex"];

            // Vyfiltrujeme sloupec "I" v tabulce "zaznamy" podle hodnoty "cisloDilc"
            worksheet1.Range["I1:I"].AutoFilter(cisloDilc);

            // Vyfiltrujeme sloupec "E" v tabulce "zaznamy" podle hodnoty "operace"
            worksheet1.Range["E1:E"].AutoFilter(operace);

            // Vypočítáme součet hodnot ze sloupců "F" a "G" v tabulce "zaznamy"
            int soucet = 0;
            for (int i = 1; i <= worksheet1.UsedRange.Rows.Count; i++)
            {
                soucet += Convert.ToInt32(worksheet1.Cells[i, 6].Value) + Convert.ToInt32(worksheet1.Cells[i, 7].Value);
            }

            // Zapíšeme součet do sloupce "A" v tabulce "srovnani"
            worksheet2.Cells[1, 1].Value = soucet;

            // Vyfiltrujeme sloupec "A" v tabulce "nastrojove_listy" podle hodnoty "cisloDilc"
            worksheet2.Range["A1:A"].AutoFilter(cisloDilc);

            // Vypočítáme součet hodnot ze sloupců "D" a "E" v tabulce "nastrojove_listy"
            soucet = 0;
            for (int i = 1; i <= worksheet2.UsedRange.Rows.Count; i++)
            {
                soucet += Convert.ToInt32(worksheet2.Cells[i, 4].Value) * Convert.ToInt32(worksheet2.Cells[i, 5].Value);
            }

            // Zapíšeme součet do sloupce "B" v tabulce "srovnani"
            worksheet3.Cells[1, 2].Value = soucet;

            // Vyfiltrujeme sloupec "B" v tabulce "nastrojove_listy" od řádku 2
            worksheet2.Range["B2:B"].AutoFilter(cisloDilc);

            // Vyfiltrujeme sloupec "B" v tabulce "spotreba_kardex" podle hodnot ze sloupce "B" v tabulce "nastrojove_listy"
            worksheet3.Range["B1:B"].AutoFilter(worksheet2.Range["B2:B"]);

            // Vypočítáme součet hodnot ze sloupce "C" v tabulce "spotreba_kardex"
            soucet = 0;
            for (int i = 1; i <= worksheet3.UsedRange.Rows.Count; i++)
            {
                soucet += Convert.ToInt32(worksheet3.Cells[i, 2].Value);
            }

            // Zapíšeme součet do sloupce "C" v tabulce "srovnani"
            worksheet3.Cells[1, 3].Value = soucet;

            // Uložíme sešit "srovnani.xlsx"
            workbook.Save();

            Console.WriteLine("Srovnání dokončeno.");
        }
    }
}

Thank you very much for help.

I tried to create excel file and I excepted, that it will create without problems.

Error:

System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {00020819-0000-0000-C000-000000000046} failed due to the following error: 80040154 Třída není zaregistrována (0x80040154 (REGDB_E_CLASSNOTREG)).

On the line of:

Workbook workbook = new Workbook();

Maybe my VS2022 is set wrong.

EDIT 13.10.2023

This is my code now:

using System.IO;
using Microsoft.Office.Interop.Excel;

namespace ConsoleApp1
{
    class Program
    {
        public static void Main(string[] args)
        {

            // Získáme vstupní hodnoty od uživatele
            Console.WriteLine("Zadejte číslo dílce:");
            string cisloDilc = Console.ReadLine();
            Console.WriteLine("Zadejte operaci:");
            string operace = Console.ReadLine();

            // Vytvoříme nový sešit s názvem "srovnani.xlsx"
            var oXL = new Microsoft.Office.Interop.Excel.Application();
            if (File.Exists("C:\\Users\\11uadber\\Desktop\\appvs\\srovnani.xlsx"))
            {
                File.Delete("C:\\Users\\11uadber\\Desktop\\appvs\\srovnani.xlsx");
            }
            var workbook = oXL.Workbooks.Add();
            workbook.SaveAs("C:\\Users\\11uadber\\Desktop\\appvs\\srovnani.xlsx");

            // Vytvoříme instanci třídy Worksheet pro každý sešit
            Worksheet worksheet1 = workbook.Worksheets["C:\\Users\\11uadber\\Desktop\\appvs\\zaznamy.xls"];
            Worksheet worksheet2 = workbook.Worksheets["C:\\Users\\11uadber\\Desktop\\appvs\\nastrojove_listy.xlsx"];
            Worksheet worksheet3 = workbook.Worksheets["C:\\Users\\11uadber\\Desktop\\appvs\\spotreba_kardex.xlsx"];

            // Vyfiltrujeme sloupec "I" v tabulce "zaznamy" podle hodnoty "cisloDilc"
            worksheet1.Range["I1:I"].AutoFilter(cisloDilc);

            // Vyfiltrujeme sloupec "E" v tabulce "zaznamy" podle hodnoty "operace"
            worksheet1.Range["E1:E"].AutoFilter(operace);

            // Vypočítáme součet hodnot ze sloupců "F" a "G" v tabulce "zaznamy"
            int soucet = 0;
            for (int i = 1; i <= worksheet1.UsedRange.Rows.Count; i++)
            {
                soucet += Convert.ToInt32(worksheet1.Cells[i, 6].Value) + Convert.ToInt32(worksheet1.Cells[i, 7].Value);
            }

            // Zapíšeme součet do sloupce "A" v tabulce "srovnani"
            worksheet2.Cells[1, 1].Value = soucet;

            // Vyfiltrujeme sloupec "A" v tabulce "nastrojove_listy" podle hodnoty "cisloDilc"
            worksheet2.Range["A1:A"].AutoFilter(cisloDilc);

            // Vypočítáme součet hodnot ze sloupců "D" a "E" v tabulce "nastrojove_listy"
            soucet = 0;
            for (int i = 1; i <= worksheet2.UsedRange.Rows.Count; i++)
            {
                soucet += Convert.ToInt32(worksheet2.Cells[i, 4].Value) * Convert.ToInt32(worksheet2.Cells[i, 5].Value);
            }

            // Zapíšeme součet do sloupce "B" v tabulce "srovnani"
            worksheet3.Cells[1, 2].Value = soucet;

            // Vyfiltrujeme sloupec "B" v tabulce "nastrojove_listy" od řádku 2
            worksheet2.Range["B2:B"].AutoFilter(cisloDilc);

            // Vyfiltrujeme sloupec "B" v tabulce "spotreba_kardex" podle hodnot ze sloupce "B" v tabulce "nastrojove_listy"
            worksheet3.Range["B1:B"].AutoFilter(worksheet2.Range["B2:B"]);

            // Vypočítáme součet hodnot ze sloupce "C" v tabulce "spotreba_kardex"
            soucet = 0;
            for (int i = 1; i <= worksheet3.UsedRange.Rows.Count; i++)
            {
                soucet += Convert.ToInt32(worksheet3.Cells[i, 2].Value);
            }

            // Zapíšeme součet do sloupce "C" v tabulce "srovnani"
            worksheet3.Cells[1, 3].Value = soucet;

            // Uložíme sešit "srovnani.xlsx"
            workbook.Save();

            Console.WriteLine("Srovnání dokončeno.");
        }
    }
}

And the problem is on this line now:

Worksheet worksheet1 = workbook.Worksheets["C:\\Users\\11uadber\\Desktop\\appvs\\zaznamy.xls"];

Error code:

System.Runtime.InteropServices.COMException: Neplatný index. (0x8002000B (DISP_E_BADINDEX))

EDIT 13.10.2023 14:55

Now I have error on this line:

worksheet1.Range["I1:I"].AutoFilter(cisloDilce);

System.Runtime.InteropServices.COMException: 0x800A03EC

Answer 1

First you need to get Excel app:

using System.IO;
using Microsoft.Office.Interop.Excel;

namespace ConsoleApp1
{
    class Program
    {
        public static void Main(string[] args)
        {
            // Získáme vstupní hodnoty od uživatele
            Console.WriteLine("Zadejte číslo dílce:");
            string cisloDilc = Console.ReadLine();
            Console.WriteLine("Zadejte operaci:");
            string operace = Console.ReadLine();

            // Vytvoříme nový sešit s názvem "srovnani.xlsx"
            var oXL = new Microsoft.Office.Interop.Excel.Application();
            var workbook = oXL.Workbooks.Add();
            workbook.SaveAs("srovnani.xlsx");
            ...

Addendum I

            var wb1 = oXL.Workbooks.Open("C:\\Users\\11uadber\\Desktop\\appvs\\zaznamy.xls")
            var worksheet1 = wb1.Worksheets["Prostěradlo1"];

Use the required worksheet name or index instead of "Prostěradlo1" dummy.