How to extract data from an Excel (.xslx) file into a two dimensional array (by columns)?

Question

.NET

Hello.
There is an .xslx (Excel) extension file. In this file, on "Sheet 1", there are several columns filled with data:

I need to extract this data from a predefined range of cells B1:C17 into a two-dimensional array.
How can I extract data from this (given) range of cells, not by one cell, but for example, by columns?
I want to immediately read the columns because I have much more data than shown in the photo.
PS I know that similar topics have already been discussed on the Internet, but I have not found a code that would fit my case.

Answer 1

Made!
Rather, I found solutions here: stackoverflow.com and here csharpcoderr.com and redid it a bit.
The following code (written in C#) reads data from a specified column in an Excel spreadsheet (the entire column at once) and writes it to a one-dimensional array strArray. The data type stored in the resulting one-dimensional array is string.

//Создаём приложение.
        Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
        //Открываем книгу.                                                                                                                                                        
        Microsoft.Office.Interop.Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(pathToFile, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
        //Выбираем таблицу(лист).
        Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
        ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];

        // Указываем номер столбца (таблицы Excel) из которого будут считываться данные.
        int numCol = 2;
    
        Range usedColumn = ObjWorkSheet.UsedRange.Columns[numCol];
        System.Array myvalues = (System.Array)usedColumn.Cells.Value2;
        string[] strArray = myvalues.OfType<object>().Select(o => o.ToString()).ToArray();

        // Выходим из программы Excel.
        ObjExcel.Quit();

1) pathToFile - the path to the Excel file.
You can specify in the following way:
2) numCol - the number of the column in the Excel table from which the data will be read.
If you need to convert all the received elements of a one-dimensional array strArrayfrom a data type stringto another data type, for example double, then this can be done with the following code:

// Преобразование всех элементов одномерного массива 
        // strArray из строкового типа в тип double.
        for (int i = 0; i < strArray.Length; i++)
            DataArray[i] = Convert.ToDouble(strArray[i]);

Answer 2

To work with xlsx - SpreadsheetLight, NPOI, OpenXML SDK libraries, and you can also interact with MS Excel via COM, and also OleDb by analogy with MDB / AccDB (Access)
And to study the library and modify the code for your case, naive Chukchi?

Answer 3

If this is a one-time operation, then you can save the excel file into a text file and parse it.

Answer 4

Is it a console application?

Answer 5

If it's a one-time operation, save the file in the XML Table 2003 format and work with it as if it were XML. There, in general, everything is elementary, through the XPathNavigator you can safely unwind the document as you like.
Another option is to use the Microsoft.ACE.OLEDB.12.0 provider.
There's a connection string like this:
Here they write:
stackoverflow.com/questions/6649363/microsoft-ace-...
Both options used - the first on PHP, the second on .NET - work fine.

Answer 6

in my opinion, this task can be solved relatively simply through Power Query in Excel. Only your data will need to be saved as one table, and in the form of a so-called. "smart table". I won’t give you a ready-made step-by-step solution right away, but there is a 95% chance that this is done in Power Query

Answer 7

ExcelPackage (via nuget) for reading Excel 2007/2010/2013/2016 files (Office Open XML format, xlsx)

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            FileInfo info = new FileInfo("input.xlsx");
            using (ExcelPackage xlPackage = new ExcelPackage(info))
            {
                // get the first worksheet in the workbook
                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[0];
                
                // output the data in column 2
                for (int iCol = 2; iCol < 4; iCol++)
                {
                    for (int iRow = 1; true; iRow++)
                    {
                        if (string.IsNullOrWhiteSpace(worksheet.GetValue(iRow, iCol) as string))
                            break;
                        Console.WriteLine("Cell({0},{1}).Value={2}", iRow, iCol, 
                                worksheet.GetValue(iRow, iCol));
                    }
                }
            }