C#.net programming How to create access files and Excel files

The examples in this article describe how c#.net programs create access files and Excel files. Share to everyone for your reference, as follows:

Some systems may need to export data to an Access or Excel file format for easy data transfer, printing, and more.

Excel file or Access two types of files that need to be exported may not be in advance, which requires us to program their own to build them, the following collation of the generation of these two files of some methods, only the most commonly used. Not all.

First, create an Excel file.

Scenario one, if you use Excel to save only two-dimensional data, that is, to use him as a database.

The simplest, you do not have to reference any additional components, just use OLE DB to complete the creation of Excel files. The sample code is as follows.

Using system.data.oledb;public static void CreateExcelFile2 () {String oledbconnstr = "Provider=Microsoft.Jet.OLEDB.4.0    ;D ATA Source=c://aa2.xls; ";  Oledbconnstr + = "Extended properties=excel 8.0;";  String strcreatetablesql = @ "CREATE TABLE";  Strcreatetablesql + = @ "Test sheet";  Strcreatetablesql + = @ "(";  Strcreatetablesql + = @ "ID INTEGER,";  Strcreatetablesql + = @ "UserID INTEGER,";  Strcreatetablesql + = @ "Userip VARCHAR,";  Strcreatetablesql + = @ "Posttime DATETIME,";  Strcreatetablesql + = @ "Fromparm VARCHAR";  Strcreatetablesql + = @ ")";   OleDbConnection oconn = new OleDbConnection ();   oconn.connectionstring = Oledbconnstr;  OleDbCommand Ocreatecomm = new OleDbCommand ();  Ocreatecomm.connection = oconn;  Ocreatecomm.commandtext = Strcreatetablesql;   oConn.Open ();  Ocreatecomm.executenonquery (); Oconn.close ();} Using system.data.oledb;public static void CreateExcelFile2 () {String oledbconnstr = "Provider=Microsoft.Jet.OLEDB.4.0    ;D ATA Source=c://aa2.xls; "; OledbcOnnstr + = "Extended properties=excel 8.0;";  String strcreatetablesql = @ "CREATE TABLE";  Strcreatetablesql + = @ "Test sheet";  Strcreatetablesql + = @ "(";  Strcreatetablesql + = @ "ID INTEGER,";  Strcreatetablesql + = @ "UserID INTEGER,";  Strcreatetablesql + = @ "Userip VARCHAR,";  Strcreatetablesql + = @ "Posttime DATETIME,";  Strcreatetablesql + = @ "Fromparm VARCHAR";  Strcreatetablesql + = @ ")";   OleDbConnection oconn = new OleDbConnection ();   oconn.connectionstring = Oledbconnstr;  OleDbCommand Ocreatecomm = new OleDbCommand ();  Ocreatecomm.connection = oconn;  Ocreatecomm.commandtext = Strcreatetablesql;   oConn.Open ();  Ocreatecomm.executenonquery (); Oconn.close ();}

When you execute the CREATE TABLE, the system automatically completes the creation of the Excel file if it finds that the Excel file does not exist. This may not be known to anyone who has not been exposed to it.

As for the increase, modification operation, and ordinary database is no different, it is not described.

Scenario two, a plain text file that separates each item of data with a spacer symbol is generated directly, but the file suffix is XLS.

Note: This time, if you open the file directly with Excel, no problem, everything is OK, but if you read this file with ADO, your link engine should not be Excel, but the text file (Microsoft text Driver). That is, the link string should not be

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c://aa2.xls; Extended Properties=excel 8.0; "

And it should be the following way:

OLE DB Mode connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c://11.txt; Extended properties= ' text; Hdr=no; Fmt=tabdelimited '

ODBC way to read TXT string notation:

Driver={microsoft Text Driver (*.txt; *.csv)};D bq=c://11.txt; Extensions=asc,csv,tab,txt;

Scenario three, you want to create an Excel file, some Excel own features need to be created, which requires the use of Com, that is: Microsoft Excel Object Library

Add the Microsoft Excel 11.0 Object Library Reference to it, depending on the version of Office you have installed, and the version of this component library is not the same.

Sample code:

public static void Createexcelfile () {  string FileName = "C://aa.xls";  Missing miss = Missing.Value;  Excel.Application m_objexcel = new Excel.Application ();  M_objexcel.visible = false;  Excel.Workbooks m_objbooks = (excel.workbooks) m_objexcel.workbooks;  Excel.Workbook m_objbook = (Excel.Workbook) (M_objbooks.add (Miss));  m_objBook.SaveAs (FileName, Miss, Miss, Miss, Miss, Miss, Excel.XlSaveAsAccessMode.xlNoChange, Miss, Miss,miss, Miss, Miss);  M_objbook.close (false, Miss, Miss);  M_objexcel.quit ();}

I am just simple to create an Excel file, no more operation of Excel, if you want to further study, you can refer to the relevant articles of this site.

Second, generate an Access database

Access is, after all, a database, so the first method above Excel cannot be applied.
You can use ADOX to create an Access database file.
The difference between ADOX and OLE DB: ADOX is the data API is just an interface, OLE DB is the provider, the API goes to call the data provider.

Sample code:

Before using, add a reference to Microsoft ADO Ext. 2.x for DDL and Security depending on your operating system, there may be different versions here.

Using adox;using system.io;public static void Createaccessfile (String FileName) {  if (! File.exists (FileName)  {  ADOX. Catalogclass cat = new ADOX. Catalogclass ();  Cat. Create ("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + FileName + ";");  cat = null;}  }

The above code just generates an Access database, for ADOX you can manipulate the database, add tables, and so on.

A summary description of the Using system;using adox;namespace webportal{//<summary>///CREATEACCESSDB. For different versions of ADO, you need to add a different reference///Add a reference to Microsoft ADO Ext. 2.7 for DDL and Security///Add a reference to Microsoft ADO Ext. 2.8 for DDL and Security//</summary> public class CreateAccessDB:System.Web.UI.Page {private void Page_Load (object sender, Sy Stem.   EventArgs e) {//For ease of testing, the database name uses a relatively random name to prevent the addition of unsuccessful additions that require IIS to be restarted to remove the database.   String dbName = "D://newmdb" +datetime.now.millisecond.tostring () + ". mdb"; ADOX. Catalogclass cat = new ADOX.   Catalogclass (); Cat.   Create ("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + DbName + ";"); Response.Write ("Database:" + DbName + "has been created successfully!)   "); ADOX. Tableclass tbl = new ADOX.   Tableclass (); Tbl.   ParentCatalog = cat; Tbl.   Name= "MyTable"; Add an auto-growing field ADOX. Columnclass col = new ADOX.   Columnclass (); Col.   ParentCatalog = cat; Col. Type=adox. Datatypeenum.adinteger; You must first set the field type Col.   Name = "id"; Col. properties["Jet oledb:allow Zero Length"].   Value= false; Col. properties["AUtoincrement "].   Value= true; Tbl. Columns.Append (Col,adox.   datatypeenum.adinteger,0); Add a text field ADOX. Columnclass col2 = new ADOX.   Columnclass (); Col2.   ParentCatalog = cat; Col2.   Name = "Description"; Col2. properties["Jet oledb:allow Zero Length"].   Value= false; Tbl. Columns.Append (Col2,adox.   datatypeenum.advarchar,25); Set the primary key tbl. Keys.append ("PrimaryKey", ADOX.   Keytypeenum.adkeyprimary, "id", "", "" "); Cat.   Tables.append (TBL); Response.Write ("<br> database table:" + tbl.) Name + "has been created successfully!   ");   Tbl=null;  cat = null;   #region The Web Forms Designer generated code override protected void OnInit (EventArgs e) {/////CODEGEN: This call is required by the ASP.   InitializeComponent (); Base.  OnInit (e);  }//<summary>///designer supports the required method-do not use the Code Editor to modify///The contents of this method. </summary> private void InitializeComponent () {this. Load + = new System.EventHandler (this.  Page_Load); } #endregion}}

I hope this article is helpful to you in C # program design.