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