locked
C# read Excel and Obtain Range automatically RRS feed

  • Question

  • User1312422139 posted

    Hi,

    I want to obtain automatically the range of a worksheet without having to specify it in my code.  This is what I actually have: 

    excelRange = excelWorksheet.get_Range("A2","G120");

     

    Range will change according to the user data entry in the worksheet, so I do not want to hardcod it or enter those values in an xml file.  Is there any way to get the excel range automatically?  I have take a look at the usedRange but I still not been able to make it work.  I know also that in an excel worksheet you can select a range an give a name to that range, how can I get it in my code?

     

    Thank you.

     

    Friday, July 16, 2010 11:42 AM

Answers

  • User-624088818 posted

    Instead of that dynamic, you can use UsedRange in excel using interop excel, which will select the rows which got data everytime,  Please find the sample code below.

    excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                object missing = Type.Missing;
                excel.Range xlRange;
                excel.Sheets xlSheets = null;
                excel.Worksheet xlSheet = null;
                excel.Workbook xlWorkbook = null;
    
                xlWorkbook = excelApp.Workbooks.Open("C:\\Book1.xlsx", missing, missing, missing,
                                                missing, missing, missing, missing, missing, missing, missing,
                                                missing, missing, missing, missing);
    
                xlSheets = (excel.Sheets)xlWorkbook.Sheets;
                xlSheet = (excel.Worksheet)xlSheets[1];
                xlRange = xlSheet.UsedRange;
                if (xlRange != null)
                {
                    int nRows = xlRange.Rows.Count;
                    int nCols = xlRange.Columns.Count;
                    for (int iRow = 1; iRow <= nRows; iRow++)
                    {
                        for (int iCount = 1; iCount <= nCols; iCount++)
                        {
                            xlRange = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[iRow, iCount];
                            Console.WriteLine(xlRange.Text);
                        }
                    }
                    Console.ReadLine();
                }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 19, 2010 12:15 PM