C# .NET - Error occured while reading Excel file in windows application.......

Asked By Jipin M on 05-Sep-11 08:21 AM
Hi All,,,,,,,,,,,,,,,......

I use the following code for reading an EXCEL sheet data on a button click..
But it return the result : Cannot Update. Database or object is read-only.

public void uploadExcelfile()
            OpenFileDialog flg = new OpenFileDialog();
            if (flg.ShowDialog(this) == DialogResult.OK)
                    FileInfo fi = new FileInfo(flg.FileName);
                    txt_upload_csv.Text = flg.FileName;
                    string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" + fi.DirectoryName;
                    OleDbConnection objConn = new OleDbConnection(sConnectionString);
                    OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM " + fi.Name, objConn);
                    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
                    objAdapter1.SelectCommand = objCmdSelect;
                    DataSet objDataset1 = new DataSet();
                    objAdapter1.Fill(objDataset1, "test");
                    row_count = dt.Rows.Count;
                    column_count = dt.Columns.Count;
                catch (Exception ex)
                    MessageBox.Show("Cannot Upload the Excel file. An error occured.");
                    string error_excel = ex.Message;

Is anyone can help me...

dipa ahuja replied to Jipin M on 05-Sep-11 08:31 AM
Use code this way to import excel data:

Untitled document
private void button2_Click(object sender, EventArgs e)
   string ExcelConstr = @"Provider=Microsoft.ACE.OLEDB.12.0";
   ExcelConstr += "Data Source=d:\book1.xls;Extended Properties=Excel 12.0";
   string SqlConstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;";
   SqlConstr += "Integrated Security=True;User Instance=True";
   using (OleDbConnection con = new OleDbConnection(ExcelConstr))
     OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
     OleDbDataReader dr = com.ExecuteReader();
     using (SqlConnection sqlcon = new SqlConnection(SqlConstr))
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
         bulkCopy.DestinationTableName = "Table1";
         bulkCopy.ColumnMappings.Add("srno", "srno");
         bulkCopy.ColumnMappings.Add("amount", "amount");
   MessageBox.Show("successfully imported!");
   //display the imported data in the datagrid
   SqlDataAdapter da = new SqlDataAdapter("select * from Table1", SqlConstr);
   DataTable dt = new DataTable();
   dataGridViewX1.DataSource = dt;
Reena Jain replied to Jipin M on 05-Sep-11 08:42 AM

You can read the excel file and display data in datagrid
Also you can update using datagrid approach

put in your .cs file:

protected void btnImportExcelFile_Click(object sender, EventArgs e)
   string strConn;
   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
   "Data Source=C:\\exceltest.xls;" +
   "Extended Properties=Excel 8.0;";
   //You must use the $ after the object you reference in the spreadsheet
   OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Tabelle1$]", strConn);
   DataSet myDataSet = new DataSet();
   myCommand.Fill(myDataSet, "ExcelInfo");
   DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;