How to insert data in excel using asp.net?

I am trying to insert a record in Excel using asp.net but it is giving error like this..

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

My code is as follows:

string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\student.xls;Extended Properties=Excel 8.0;HDR=YES;";
con = new OdbcConnection(connect);        

string query = "insert into [Sheet1$](sid,s开发者_高级运维name,saddress) values('6','ravi','rajam')";

OdbcCommand objCmd = new OdbcCommand(query,con);

int i= objCmd.ExecuteNonQuery();
if (i == 1)
{
   Response.Write("inserted");
}
else
{
   Response.Write("Not inserted");
}

You are probably getting the error because you have a missing driver on your web server.

There are 5 basic ways to insert data into an excel sheet:

• Via ODBC connection, your approach
• 3rd party tool
• Start Excel via code and interop into it, Yokos approach. This has several problems: requires excel installed on server, uses a lot of memory, does not scale, does your licence cover it...
• Use SharePoint Excel Services, this is good if you already have SharePoint Enterprise, but too expensive just for this.
• SpreadSheetML

I would go with SpreadSheetML http://blogs.msdn.com/b/brian_jones/archive/2008/11/10/reading-data-from-spreadsheetml.aspx

We had a similar project that generated MS Word documents, switching from Interop to WordML reduced the generation time of a document from 40 seconds to 2.

going the odbc/oledb way has it limitations: Formatting and Char Limitations.

If you need it often in your applications then it's worth to invest in something like Aspose Cells

// Start a new workbook in Excel.
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));

// Add data to cells in the first worksheet in the new workbook.
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = "Last Name";
m_objRange = m_objSheet.get_Range("B1", m_objOpt);
m_objRange.Value = "First Name";
m_objRange = m_objSheet.get_Range("A2", m_objOpt);
m_objRange.Value = "Doe";
m_objRange = m_objSheet.get_Range("B2", m_objOpt);
m_objRange.Value = "John";

// Apply bold to cells A1:B1.
m_objRange = m_objSheet.get_Range("A1", "B1");
m_objFont = m_objRange.Font;
m_objFont.Bold=true;

// Save the Workbook and quit Excel.
m_objBook.SaveAs(m_strSampleFolder + "Book1.xls", m_objOpt, m_objOpt, 
    m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, 
    m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();