locked
Read Excel File c# RRS feed

  • Question

  • how to read excel data when exporting .

    * i need to read lead data   from excel sheet .
    * can you send Sample Code.
    Thursday, December 12, 2013 9:25 AM

All replies

  • Check out below code which I used sometime back, hope you find useful, If it is useful, mark as answer.

      static void ReadExcel()
            {
                //Specify the Excel path 
                string fileName = @"C:\Excel\CRMAccounts.xlsx";
    
                //Create Excel object
                var excel = new Application();
                if (excel == null)
                    throw new ApplicationException("Error occurred while instantiating Excel object");
    
                //Set these to speed up the Excel application.
                excel.Visible = false;
                excel.ScreenUpdating = false;
                excel.DisplayAlerts = false;
    
                try
                {
    
                    //Read Excel
                    Workbook workbook = excel.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);
                    Sheets sheets = workbook.Worksheets;
                    Worksheet worksheet = (Worksheet)sheets.get_Item(1);
                    for (int x = 2; x <= worksheet.UsedRange.Rows.Count; x++)
                    {
                        //Specify the Excel Sheet Range
                        Range range = worksheet.get_Range("A" + x.ToString(), "I" + x.ToString());
                        System.Array myvalues = (System.Array)range.Value;
                        string[] strArray = ConvertFrom2DArrayTo1DArray(myvalues);
                        string str = string.Empty;
                        foreach (string strExcelData in strArray)
                        {
                            str = str + " " + strExcelData;
                        }
                        Console.Write(str);
                        Console.WriteLine();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (excel != null)
                    {
                        excel.Quit();
                    }
                }
            }
    
    
            //Convert two dimension array to single dimension array
            static string[] ConvertFrom2DArrayTo1DArray(System.Array values)
            {
                string[] strArray = new string[values.Length];
    
                for (int i = 1; i <= values.Length; i++)
                {
                    if (values.GetValue(1, i) == null)
                        strArray[i - 1] = string.Empty;
                    else
                        strArray[i - 1] = values.GetValue(1, i).ToString();
                }
                return strArray;
            }

    • Proposed as answer by VSomireddy Thursday, December 12, 2013 4:34 PM
    Thursday, December 12, 2013 3:52 PM
  • and by the way,to use above code, you need to add following DLL reference to your C# project.

    C:\Program Files (x86)\Microsoft Visual Studio 11.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.Excel.dll

    Thursday, December 12, 2013 3:55 PM