How to insert data in excel using asp.net?

Question

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,sname,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");
}

BenMorel · Accepted Answer · 2014-02-02 20:40:14Z

1

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.

edited Feb 2, 2014 at 20:40

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Feb 19, 2011 at 11:27

Shiraz Bhaiji

65.7k37 gold badges149 silver badges260 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gsharp · Accepted Answer · 2011-02-19 11:36:56Z

1

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

answered Feb 19, 2011 at 11:36

gsharp

28k26 gold badges93 silver badges137 bronze badges

Comments

Yoko Zunna · Accepted Answer · 2011-02-19 11:18:03Z

-1

// 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();

answered Feb 19, 2011 at 11:18

Yoko Zunna

1,82414 silver badges21 bronze badges

2 Comments

gsharp Over a year ago

please don't do that in an asp.net application. Excel isn't meant to be used on a Server.

DarkDust Over a year ago

Copy & pasted from the Microsoft Knowledgebase without attribution.

Collectives™ on Stack Overflow

How to insert data in excel using asp.net?

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related