How to get data from every single cell?

Question

I'm doing an app for my friend who works in hospital.

What the app basically doing is just grab data from excel, sort it and display it. I stuck on grabbing all the data. The excel file has 3 columns and n rows. The cells of first column are not always filled in.

I have tried different ways of taking that data, and always I wasn't able to make it working right. I was trying to include empty cells into search, but now temp returns me all empty strings.

How to iterate each row until the end, and on every iteration grab data from each column, even if it is empty - return ""?

            Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(currentExcelLocation, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows,
                                                                        "", false, true, 0, false, false, false);
            Excel.Sheets excelSheets = excelWorkbook.Worksheets;
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(3);
            int iTotalRows = excelWorksheet.UsedRange.Rows.Count;
            for (int i = 1; i <= iTotalRows; ++i)
            {
                string tempL = (excelWorksheet.Cells[i, 1] as Excel.Range).SpecialCells(Excel.XlCellType.xlCellTypeBlanks).Text.ToString();
                if (tempL != "") { temp = tempL; }
                string tempS = (excelWorksheet.Cells[i, 2] as Excel.Range).Value2.ToString();
                string tempD = (excelWorksheet.Cells[i, 3] as Excel.Range).Value2.ToString();
                DataScheme.Symptoms tempSD = new DataScheme.Symptoms(tempS, tempD);
                if (data.FindIndex(a => a.Location == temp) < 0) { data.Add(new DataScheme(temp)); }
                data.Find(b => b.Location == temp).SympList.Add(tempSD);
                textBox6.Text += temp + Environment.NewLine;
            }
            excelWorkbook.Close(0);
            excelApp.Quit();
            Marshal.FinalReleaseComObject(excelApp);
            Marshal.FinalReleaseComObject(excelWorkbook);
            Marshal.FinalReleaseComObject(excelSheets);
            Marshal.FinalReleaseComObject(excelWorksheet);

Answer 1

get closedXML from Nuget, then follow this method to return the excel data into a Datatable then that way you can get the data from each row.

    public static DataTable ImportSheet(string fileName)
    {
        var datatable = new DataTable();
        var workbook = new XLWorkbook(fileName);
        var xlWorksheet = workbook.Worksheet(1);
        var range = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed());

        var col = range.ColumnCount();
        var row = range.RowCount();

        datatable.Clear();
        for (var i = 1; i <= col; i++)
        {
            var column = xlWorksheet.Cell(1, i);
            datatable.Columns.Add(column.Value.ToString());
        }

        var firstHeadRow = 0;
        foreach (var item in range.Rows())
        {
            if (firstHeadRow != 0)
            {
                var array = new object[col];
                for (var y = 1; y <= col; y++)
                {
                    array[y - 1] = item.Cell(y).Value;
                }

                datatable.Rows.Add(array);
            }
            firstHeadRow++;
        }
        return datatable;
    }