I'm trying to create an excel file and insert data into the excel file. The creation of the excel file doesnt fail, but when i try to insert data into a sheet in the excel file i get this error:
The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
This is my code:
private void exportToExcel_Click(object sender, EventArgs e)
{
string excelPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"\TrackITExportFile - " + DateTime.Today.ToShortDateString();
File.Exists(excelPath);
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel._Workbook workBook = null;
Microsoft.Office.Interop.Excel.Sheets sheet = null;
Microsoft.Office.Interop.Excel._Worksheet newSheet = null;
object misValue = Missing.Value;
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = true;
workBook = (Microsoft.Office.Interop.Excel._Workbook)(excel.Workbooks.Add(Missing.Value));
sheet = workBook.Sheets as Microsoft.Office.Interop.Excel.Sheets;
newSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.Add(sheet[1], Type.Missing, Type.Missing, Type.Missing);
workBook.SaveAs(excelPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault,
misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workBook.Close();
excel.Quit();
releaseObject(workBook);
releaseObject(excel);
releaseObject(newSheet);
releaseObject(sheet);
InsertData(excelPath);
}
public void InsertData(string fileName)
{
System.Data.OleDb.OleDbConnection connection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=YES;\"");
connection.Open();
myCommand.Connection = connection;
sql = "Insert into [Sheet1$](A1) Values ('" + ds.Tables[0].Columns[0] + "')";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
connection.Close();
}
Am i creating the file wrong?