C uses oledb to export data to excel

This article illustrates how C # uses oledb to export data to excel. Share it for your reference, as follows:

At present, 1 and a half applications will encounter the problem of data export, export to word, export to excel, and other things. Of course, most of the exported types are ms and office. Recently, when I was doing data conversion, I also encountered this export problem. Fortunately, I didn't have to touch word, so only xml, csv, tsv and the most "painful" excel were involved.

The initial idea is xml+xslt, and get it all done, but there is a problem that excel only supports xml after xp. I can't force users to upgrade their office to xp. After all, everyone uses the genuine version. Therefore, for this, there is another method, which is also widely used on the Internet-write it as html and change its suffix to xls, or change the type in the response header to Application/Excel (it seems so, I can't remember clearly, I mainly introduced word in this way). Of course, this is a bit uncomfortable, because what is given is not the real excel after all, but it can still be done with xslt.

However, when preparing to do so, I found a way that can produce at least one more authentic way than html. xls, that is, oledb. The first thing I saw was to look up the data of excel by oledb, and then I thought that since select can be done, insert should be able to do it naturally. But I tried it once, but I couldn't. I always said there was no formulated field. Well, I found some data and found that the field is actually as a line 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 ( ) ;

That's it. In the connection string, "c:\\ 2. xls", if it doesn't exist, the system will build one by itself, but this is the file that can't be opened normally. If you want create1 tables (if you can call them tables), the type can be specified. This is no problem. It may be strange why it is divided into two sentences, because when it is combined into an commad command string, I still don't know which character to use to distinguish ";" Is not good, this I also want to look for 1 on msdn, I was in. net 1.1 under the test, can be used, that is, there will be an extra line of field name data, but this should be able to use delete deleted. . net 2.0 should also be possible. After all, this is the bottom layer to provide a data access interface, which should not have much to do with your programming environment. For that "Excel 8.0", I'm just sure I can. For the higher one, I haven't tried it, so I'm going to look at it again.

For more readers interested in C # related content, please check the topics on this site: "Summary of XML File Operation Skills in C #", "C # Common Control Usage Tutorial", "WinForm Control Usage Summary", "C # Data Structure and Algorithm Tutorial", "C # Object-Oriented Programming Introduction Tutorial" and "C # Programming Thread Use Skills Summary"

I hope this article is helpful to everyone's C # programming.