I am working with c# in WPF. Pulling data into datatables. Everything was working OK until I changed to using the actual worksheet name as pulled from the sheet via a foreach (worksheet in workbook) loop.
Now that I have the actual worksheet name and include it in my OLEDbCommand, the worksheet open on the screen.
I would like to prevent/stop the Excel file from opening on the screen as it is not needed nor desired.
Below is the connection string and the beginning of the try/catch that has the commands and query.
string con_string = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes'";
try
{
OleDbConnection con = new OleDbConnection(con_string);
con.Open();
//OleDbCommand ocon = new OleDbCommand("SELECT * FROM [" + myMonth + " " + year + "$]", con);
OleDbCommand ocon = new OleDbCommand("SELECT * FROM [" + myWorkSheet + "$]", con);
OleDbDataAdapter sda = new OleDbDataAdapter(ocon);
sda.Fill(data);
dGrid.DataContext = data;
}
If I revert back to the commented out line using the myMonth and year variables (created in a SelectionChanged method from a Calendar object), the spreadsheet does not open.
The following is the code that access and creates the list of actual worksheets I use to populate a comboBox dropdown.
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open(fullFilePath);
String[] excelSheets = new String[excelBook.Worksheets.Count];
int i = 0;
foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)
{
excelSheets[i] = wSheet.Name;
cmbBox2.Items.Add(excelSheets[i]);
i++;
}
