ReadExcelByTypeInterop.cs

public static List<List<string>> ReadExcelByType(string filePath, string worksheetName, DataStorage type,
            int Index = 1)
        {
            List<List<string>> data = new List<List<string>>();
            // Open the Excel file and get the worksheet
            Application excelApp = null;
            bool alreadyOpen = false;
            try
            {
                excelApp = (Application) Marshal.GetActiveObject("Excel.Application");
                excelApp.ScreenUpdating = true;
                excelApp.Visible = false;
                alreadyOpen = true;
            }
            catch
            {
            }
            if (excelApp == null)
            {
                excelApp = new Application();
                excelApp.ScreenUpdating = true;
                alreadyOpen = false;
            }
            Workbook workbook = null;
            // See if the workbook is open already
            foreach (Workbook wb in excelApp.Workbooks)
                if (wb.FullName.ToLower() == filePath.ToLower())
                {
                    workbook = wb;
                    break;
                }
            if (workbook == null)
                workbook = excelApp.Workbooks.Open(filePath);
            Worksheet worksheet = null;
            foreach (Worksheet ws in workbook.Sheets)
                if (ws.Name.ToLower() == worksheetName.ToLower())
                {
                    worksheet = ws;
                    break;
                }
            // Read through the data in the excel file and add it to our data variable.
            if (null != worksheet)
            {
                int rowCount = 0;
                int colCount = 0;
                // Get the total used range of the selected worksheet
                Range usedRange = worksheet.UsedRange;
                colCount = usedRange.Columns.Count;
                rowCount = usedRange.Rows.Count;
                for (int i = Index - 1; i <= rowCount; i++)
                {
                    List<string> rowData = new List<string>();
                    Range cell = null;
                    switch (type)
                    {
                        case DataStorage.ByColumn:
                            for (int j = Index - 1; j <= colCount; j++)
                            {
                                cell = usedRange.Cells[i, j];
                                try
                                {
                                    rowData.Add(cell.Text);
                                }
                                catch
                                {
                                    rowData.Add(string.Empty);
                                }
                            }
                            break;
                        case DataStorage.ByRow:
                            for (int j = Index - 1; j <= rowCount; j++)
                            {
                                cell = usedRange.Cells[j, i];
                                try
                                {
                                    rowData.Add(cell.Text);
                                }
                                catch
                                {
                                    rowData.Add(string.Empty);
                                }
                            }
                            break;
                    }
                    data.Add(rowData);
                }
            }
            // Close the workbook if it was opened via this command
            if (alreadyOpen)
                return data;
            #region Close App
            workbook.Close();
            excelApp.Quit();
            #endregion
            //  Send that sweet data home.
            return data;
        }