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)
            {
                try
                {
                    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);
                    objConn.Open();
                    OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM " + fi.Name, objConn);
                    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
                    objAdapter1.SelectCommand = objCmdSelect;
                    DataSet objDataset1 = new DataSet();
                    objAdapter1.Fill(objDataset1, "test");
                    objAdapter1.Fill(dt);
                    row_count = dt.Rows.Count;
                    column_count = dt.Columns.Count;
                    objConn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Cannot Upload the Excel file. An error occured.");
                    string error_excel = ex.Message;
                }
            }
        }

Is anyone can help me...

Regards................
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))
   {
     con.Open();
     OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
     OleDbDataReader dr = com.ExecuteReader();
     using (SqlConnection sqlcon = new SqlConnection(SqlConstr))
     {
       sqlcon.Open();
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
       {
         bulkCopy.DestinationTableName = "Table1";
         bulkCopy.ColumnMappings.Add("srno", "srno");
         bulkCopy.ColumnMappings.Add("amount", "amount");
         bulkCopy.WriteToServer(dr);
       }
     }
     dr.Close();
     dr.Dispose();
   }
   MessageBox.Show("successfully imported!");
   //display the imported data in the datagrid
   SqlDataAdapter da = new SqlDataAdapter("select * from Table1", SqlConstr);
   DataTable dt = new DataTable();
 
   da.Fill(dt);
 
   dataGridViewX1.DataSource = dt;
}
Reena Jain replied to Jipin M on 05-Sep-11 08:42 AM
HI,

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;
   DataGrid1.DataBind();
 
 }