how to get excel sheet name using index c#

0

I am trying to import multiple excel workbook and bind it to datagridview as shown below my problem is I want to get sheet name using index to make this method fixable I am getting error that says

I tried zero index I am get error too Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

Note that excel workbook may has one or more sheets

System.Runtime.InteropServices.COMException was unhandled
Message=Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)) Source=Microsoft.Office.Interop.Excel
ErrorCode=-2147352565 StackTrace: at Microsoft.Office.Interop.Excel.Sheets.get_Item(Object Index) at BlackList.F0100.ImportExcel(String FilePath) in d:\Documents\Visual Studio 2013\Projects\BlackList\BlackList\F0100.cs:line 88 at BlackList.F0100.GetFilesList() in d:\Documents\Visual Studio 2013\Projects\BlackList\BlackList\F0100.cs:line 71 at BlackList.F0100.B_Import_Data_Click(Object sender, EventArgs e) in d:\Documents\Visual Studio 2013\Projects\BlackList\BlackList\F0100.cs:line 125 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at BlackList.Program.Main() in d:\Documents\Visual Studio 2013\Projects\BlackList\BlackList\Program.cs:line 18 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:

    public void ImportExcel(string FilePath)
    {
        string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
        string cmdtxt = @"select * from [Sheet222$]";

        /////////////////////////////////////////////////////////////////////////////////
        Excel.Application excelApp = new Excel.Application();
        excelApp.Visible = true;

        Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(FilePath,
                0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                true, false, 0, true, false, false);

        Excel.Sheets excelSheets = excelWorkbook.Worksheets;

        Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(0);

        MessageBox.Show(excelWorksheet.Name, "test msg");

        excelWorkbook.Close(0);

        /////////////////////////////////////////////////////////////////////////////////

        using (OleDbConnection conn = new OleDbConnection(ConnStr))
        {
            conn.Open();

            OleDbDataAdapter DA = new OleDbDataAdapter(cmdtxt, conn);
            DA.Fill(dt);
            DGV_Data.DataSource = dt;

            conn.Close();
        }

        //Calculate record counts
        L_Rows_Count.Text = "count: " + (DGV_Data.Rows.Count - 1).ToString("n0");

    }
c#
.net
visual-studio-2013
.net-3.5
asked on Stack Overflow Feb 11, 2018 by sam • edited Feb 11, 2018 by sam

1 Answer

1

I think,

Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(1);
            string name = excelWorksheet.Name;//Sheet Name

User contributions licensed under CC BY-SA 3.0