locked
Read from Excel (using interop) RRS feed

  • Question

  • User1786833724 posted

    I haved tried serveral differant ways but neither worked, I am getting error of - invalid index - all the ways I've tried.

            Microsoft.Office.Interop.Excel.Application _ExcelApplication;
            Microsoft.Office.Interop.Excel.Workbook _ExcelWorkbook;
            Microsoft.Office.Interop.Excel.Worksheet _ExcelWorkSheet;
            Object misVal = System.Reflection.Missing.Value;
    
            public void ReadExcel(string filename)
            {
                _ExcelApplication = new Application();
                //_ExcelWorkbook = _ExcelApplication.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                _ExcelWorkbook = _ExcelApplication.Workbooks.Open(filename);
                _ExcelWorkSheet = _ExcelWorkbook.Worksheets[1];
                Range r = _ExcelWorkSheet.Cells["A1"];
                object rowVal = r.Value;
                string formattedText = r.Text;
                Console.WriteLine("Raw Value = {0} \t formatted = {1}", rowVal, formattedText);
            }
    

    Error:

    "Exception from HRESULT: 0x800A03EC"

    class ExcelDemo
        {
            Microsoft.Office.Interop.Excel.Application _ExcelApplication;
            Microsoft.Office.Interop.Excel.Workbook    _ExcelWorkbook;
            Microsoft.Office.Interop.Excel.Worksheet _ExcelWorkSheet;
            Object misVal = System.Reflection.Missing.Value;
    
            public void OpenExcelSpreadSheet(string filename)
            {
                try
                {
                    _ExcelApplication = new Application();
                    //_ExcelWorkbook = _ExcelApplication.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                    //_ExcelWorkbook = _ExcelApplication.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    _ExcelWorkbook = _ExcelApplication.Workbooks.Open(filename);
                    int NoOfSheets = _ExcelWorkbook.Sheets.Count;
    
                    _ExcelWorkSheet = (Worksheet)_ExcelWorkbook.Worksheets["Sheet 1"];
    
                    Range ExcelRange = _ExcelWorkSheet.UsedRange;
    
    
                    //foreach (Microsoft.Office.Interop.Excel.Range row in ExcelRange.Rows)
                    //{
                    //    string[] str = GetRange("A" + row.Row + ":D" + row.Row + "", _ExcelWorkSheet);
                    //}
    
                    //for (int n = 1; n < NoOfSheets + 1; n++)
                    //{
                    //    _ExcelWorkSheet = (Worksheet)_ExcelWorkbook.Sheets[n];
                    //    //ExcelRange object below takes the range of the sheet
                    //    Range ExcelRange = _ExcelWorkSheet.UsedRange;
    
                    //    //Getting an object array of all of the values of the cells in te sheet. Once we get the valueArray, one can manipulate them as wanted
                    //    //object[,] valueArray = (object[,])ExcelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
    
                    //    int rows = ExcelRange.Rows.Count;
                    //    int columns = ExcelRange.Columns.Count;
    
                    //    for (int i = 0; i <= rows; i++)
                    //    {
                    //        for (int j = 0; j <= columns; j++)
                    //        {
                    //            Console.WriteLine(ExcelRange.Cells[i, j].Value2.ToString());
                    //        }
                    //    }
                    //}
    
                    
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
                finally
                {
                    //Release memory
                    _ExcelWorkbook.Close(false, filename, null);
                    Marshal.ReleaseComObject(_ExcelWorkbook);
                }
            }
            public string[] GetRange(string range, Worksheet excelWorkSheet)
            {
                Microsoft.Office.Interop.Excel.Range workingRangeCells = excelWorkSheet.get_Range(range, Type.Missing);
                System.Array array = (System.Array)workingRangeCells.Cells.Value2;
                return ((string[])array);
            }
        }

    Error:

    Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))



    Sunday, December 2, 2012 4:34 PM

All replies