C# variables from an excel sheet

Question

so i want to get data from certain boxes in an excel file. i have written the following code:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;

namespace Work
{
    class Excel
    {
        string path = "";
        _Application excel = new _Excel.Application();
        Workbook wb;
        Worksheet ws;
        public Excel(string path, int Sheet)
        {
            this.path = path;
            wb = excel.Workbooks.Open(path);
            ws = (_Excel.Worksheet)wb.Worksheets[Sheet];
        }
    }
}

and this is my program:

using System;

namespace Work
{
    class Program
    {
        static void Main(string[] args)
        {
            string file_name = "C:\\Users\\Aidan\\Desktop\test.xlsx";
            Excel excel = new Excel(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", file_name), 1);
        }
    }
}

for some reason i get this following message:

Unhandled exception. System.IO.FileNotFoundException: Could not load file or assembly 'Interop.Microsoft.Office.Interop.Excel, Version=1.9.0.0, Culture=neutral, PublicKeyToken=null'. The system cannot find the file specified. File name: 'Interop.Microsoft.Office.Interop.Excel, Version=1.9.0.0, Culture=neutral, PublicKeyToken=null'

does someone know what is there to do? i appreciate any help!

EDIT: so ive found a new way to write the code online and it still gives me the same error but a bit diffrent:

Unhandled exception. System.IO.FileNotFoundException: Could not load file or assembly 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'. The system cannot find the file specified. File name: 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

this is the new code (replaces the class Excel):

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
namespace Work
{
    class Program
    {
        const int n = 30;
        static void getExcelFile()
        {

            //Create COM Objects. Create a COM object for everything that is referenced
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\Orna\Desktop\test.xlsx");
            Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            //iterate over the rows and columns and print to the console as it appears in the file
            //excel is not zero based!!
            for (int i = 1; i <= n; i++)
            {
                d[i - 1] = new Date(xlRange.Cells[i, 1].ToString(), xlRange.Cells[i, 2].ToString(), (int)xlRange.Cells[i, 23], (int)xlRange.Cells[i, 4], (int)xlRange.Cells[i, 5], (int)xlRange.Cells[i, 6], (int)xlRange.Cells[i, 7], (int)xlRange.Cells[i, 8], (int)xlRange.Cells[i, 9], (int)xlRange.Cells[i, 10], (int)xlRange.Cells[i, 11] , (int)xlRange.Cells[i, 12], (int)xlRange.Cells[i, 13], (int)xlRange.Cells[i, 14]);
            }

            //cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();

            //rule of thumb for releasing com objects:
            //  never use two dots, all COM objects must be referenced and released individually
            //  ex: [somthing].[something].[something] is bad

            //release com objects to fully kill excel process from running in the background
            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet);

            //close and release
            xlWorkbook.Close();
            Marshal.ReleaseComObject(xlWorkbook);

            //quit and release
            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);
        }

btw dont mind the date class, thats something else and the problem doesnt come from that. id still very much appreciate the help! thanks.

Answer 1

For the simplest, fastest way to read from Excel in C# I wouldn't recommend the overhead of Interop it's too clunky and cumbersome.

There is a great little library that's super easy and fast to use called LightWeightExcelReader. https://github.com/ChrisHodges/LightweightExcelReader. Check the readme file for a simple example of doing exactly what you want.

If you need to write as well as read Excel files, I'd recommend https://github.com/ClosedXML/ClosedXML instead

Answer 2

According to your description, you want to query data from excel sheet.

You can try the following code to solve this problem.

Please install nuget package->LightWeightExcelReader firstly.

using LightWeightExcelReader;
using System;
using System.Collections.Generic;
namespace _4._4Excel
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.getExcelFile();
        }
    }
    class Excel
    {
        public static void getExcelFile()
        {

            //Instatiate a spreadsheet reader by file path:
            var excelReader = new ExcelReader(@"\...\test1.xlsx");         
            //Get a worksheet by index:
            var sheetReader = excelReader[0];
            var dictionaryOfCellValues = new Dictionary<string, object>();
            //Use ReadNext() to read the next cell in the spreadsheet:
            while (sheetReader.ReadNext())
            {
                dictionaryOfCellValues.Add(sheetReader.Address, sheetReader.Value);
            }
            IEnumerable<object> cellsFromA1ToD4 = sheetReader["A1", "C4"];
            foreach (object obj in cellsFromA1ToD4)
            Console.WriteLine(obj);
            Console.ReadKey();         
        }
    }
}

Excel:

Result: