C # Methods for exporting data to Excel using OLE DB _c# tutorial

The example in this article describes how C # uses OLE DB to export data to Excel. Share to everyone for your reference, specific as follows:

Now half of the application will run into data export problems, export to Word, export to Excel, and what else. Of course, the type of export is mostly in MS Office. Recently in the conversion of data, also encountered this export problem, of course, fortunately do not touch word, this involves only XML,CSV,TSV and the most "painful" excel.

The first idea was to xml+xslt, fix all, but one problem is that Excel only supports XML after the XP version, I can't force the user to upgrade their office to XP after all, they are using the genuine. So for this, there is also a method, is also used on the internet a lot of--write HTML and then change the suffix name to XLS, or the response to the type of the head to Application/excel (as if this is not clear, I mainly lead the word). Of course this is a bit uncomfortable, because the given is not really excel, but can still be done with XSLT.

But when you're ready to do this, you've found a way to generate at least a bit more authentic than HTML. xls, which is the OLE DB approach. The first thing to see is to look up Excel data in OLE DB, and then think that if you can select, the insert should be OK. But try it, no, always say there is no set of fields. Well come and find some data found that the field is actually as a row of data, so the problem can be solved, the following is my test code:

String Strcon = "Provider = microsoft.jet.oledb.4.0;" Data Source = C:\\2.xls; Extended
properties=excel 8.0 ";
OleDbConnection myconn = new OleDbConnection (Strcon);
String createcmd= "CREATE TABLE testtable (ID integer, UserID integer)";
String insertcmd = "INSERT into TestTable (Id,userid) VALUES (1,2)";
OleDbCommand cmd=new OleDbCommand (createcmd,myconn);
OleDbCommand excmd=new OleDbCommand (insertcmd,myconn);
MyConn.Open ();
Cmd. ExecuteNonQuery ();
Excmd. ExecuteNonQuery ();
Myconn.close ();

In this way, the "C:\\2.xls" in the connection string, if it does not exist, the system will build one itself, but this is after the file is not normal to open, to create a table (if can be called table), the type can specify that there is no problem. May be strange, why is divided into two sentences, this is because, when combined into a Commad command string, I do not know which character to distinguish, ";" It's not going to work, I'll look for it on MSDN, I tested it under. NET 1.1, and I could use a line that would represent the field name, but this should be deleted with delete.. NET 2.0 should also be OK, after all, this is the bottom provides a data access interface, It should not be much of a relationship with your programming environment. For that "Excel 8.0", I'm just sure that I can, for the higher not try, ready to look again.

Read more about C # Interested readers can view the site topics: "C # XML file Operation Tips Summary", "C # Common control usage Tutorial", "WinForm Control Usage Summary", "C # Data structure and algorithm tutorial", "C # object-oriented Program design Introductory Course" and "C # A summary of thread usage tips for programming

I hope this article will help you with C # programming.