locked
excel interop, usedReange, too many columns returned RRS feed

  • Question

  • User-1829088121 posted

    Hi Basically I'm opeing an excel sheet, selecting the 2nd workbook and trying to retrive only the columns, rows that have been populated. However when I do a column count I find an extra 20 columns with a null value.

    I checked the wroksheet name and it is accessing the correct one.

    does anyone know why this might be happening?

    Monday, February 22, 2010 4:00 PM

All replies

  • User-624088818 posted

    Please try the below code which will be display only columns which have values

    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();
                }



    Tuesday, February 23, 2010 2:41 AM
  • User-1199946673 posted

    You shouldn't use Excel interop at all:

    "Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment."

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2 

    Try

    http://npoi.codeplex.com/ 

    Tuesday, February 23, 2010 1:35 PM