Issues with creating Excel file and inserting data

2019-08-29 08:12发布

问题:

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?

标签: c# .net oledb