This article describes the method of using oledb to export data to excel in C#. Share it for your reference, as follows:
Now half of the application process will encounter data export problems, export to word, export to excel, and other things. Of course, most of the exported types are still ms office. Recently, I have been doing data conversion and have encountered this export problem. Of course, luckily, I don’t need to touch Word, so there are only xml, csv, tsv and the most "painful" excel.
The initial idea was to get everything done, but there is a problem that excel only supports xml after the xp version. I cannot force users to upgrade their office to xp after the xp. After all, they use the genuine version. So there is another method for this, which is also a more commonly used online - write it as html and change the suffix to xls, or change the type in the response header to Application/Excel (it seems like this, I can't remember it, I mainly directed word like this). Of course, this is a bit unpleasant, because what is given is not real excel after all, but it can still be done with xslt.
However, when preparing to do this, I found a .xls method that can produce at least more authentic than the html method, that is, using the oledb method. The first thing I saw was to use the oledb method to check the excel data, and then I thought that since I can select, then insert should be OK. But I tried it and it didn't work. I always said that there were no fields to be formulated. Haolai found some information and found that the fields are actually used as a line of data, so the problem can be solved. Here is my experimental code:
string strCon = " Provider = .4.0 ; Data Source = c:\\;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); ( ) ; (); (); ( ) ;
This is fine. If the "c:\\" in the connection string does not exist, the system will create one by itself, but the subsequent file cannot be opened normally. You need to create a table (if it can be called a table), and you can specify the type. This is no problem. I may find it strange, why it is divided into two sentences? This is because when I combine it into a commad command string, I don’t know which character to use to distinguish it. “;” is not possible. I also need to find this on msdn. I tested it under .net 1.1. It can be used, but there will be an extra line of data representing the field name, but this should be deleted with delete. It should be possible under .net 2.0. After all, this is a data access interface provided by the underlying layer, which should not have much to do with your programming environment. For that "Excel 8.0", I'm just sure it's OK, and I haven't tried it for the higher ones, so I'm going to take a look.
For more information about C# related content, please check out the topic of this site:Summary of XML file operation skills in C#》、《Tutorial on the usage of common C# controls》、《Summary of WinForm control usage》、《C# data structure and algorithm tutorial》、《Introduction to C# object-oriented programming tutorial"and"Summary of thread usage techniques for C# programming》
I hope this article will be helpful to everyone's C# programming.