-1

I am attempting to read data from an excel file and store it in a 2D array. However, the functions that I call on my excel sheet seem to be returning 0, such as the row/column count, despite my excel sheet having data in it. Is there some sort of formatting I have to do to my excel sheet?

For example when I call the count row function on the general range, not the used range, on xlSheet, it returns 0 for some reason.

public static String[,] ReadExcelData(string fileName, string sheet)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName);
            Excel.Worksheet xlSheet = (Excel.Worksheet)xlWorkbook.Sheets[sheet];
            Excel.Range range = xlSheet.UsedRange;

            int rowCount = range.Rows.Count;
            int columnCount = range.Columns.Count;
            String[,] dataArray = new String[rowCount, columnCount];

            for (int currColumn = 1; currColumn <= columnCount; currColumn++)
            {
                for (int currRow = 1; currRow <= rowCount; currRow++)
                {
                    if (range.Cells[currRow, currColumn] == null || range.Cells[currRow, currColumn].ToString() == "n/a")
                    {
                        dataArray[currRow - 1, currColumn - 1] = "";
                    }
                    else
                    {
                        dataArray[currRow - 1, currColumn - 1] = range.Cells[currRow, currColumn].ToString();
                    }
                }
            }
            xlWorkbook.Close();
            return dataArray;
        }
Bob
  • 1
  • 2
  • Hi. Have you looked at this example ? https://stackoverflow.com/questions/43353073/c-sharp-excel-correct-way-to-get-rows-and-columns-count – Oddmar Dam Jun 13 '19 at 16:03

1 Answers1

1

I recomand ExcelDataReader if you only want to read data.

Example of ExcelDataReader: https://csharp.hotexamples.com/examples/-/ExcelDataReader/-/php-exceldatareader-class-examples.html

Tomer Nahary
  • 11
  • 2