C#.net How to create access files and Excel files programmatically _c# tutorials

The example in this article describes how c#.net programmatically creates access files and Excel files. Share to everyone for your reference, specific as follows:

Some systems may need to export data to access or Excel file formats to facilitate the transfer of data, printing, and so on.

Excel files or access these two types of files that need to be exported may not be in advance, which requires us to programmatically build them, and here are some ways to build these two files, listing only the most common. Not all.

First, build the Excel file.

Solution One, if the use of Excel to save only two-dimensional data, that is, to use him as a database.

The simplest thing to do is to use OLE DB to complete the creation of Excel files without referencing any additional components. The example code is as follows.

Using System.Data.OleDb; public static void CreateExcelFile2 () {String oledbconnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c://aa2.xls  
  ;";
  Oledbconnstr + = "Extended properties=excel 8.0;";
  String strcreatetablesql = @ "CREATE TABLE";
  Strcreatetablesql = @ "Test table";
  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;Data SourCe=c://aa2.xls; ";
  Oledbconnstr + = "Extended properties=excel 8.0;";
  String strcreatetablesql = @ "CREATE TABLE";
  Strcreatetablesql = @ "Test table";
  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. If this is not in contact with the person, may not know.

As for the increase, modify the operation, and the normal database is no different, it is not described.

Program two, directly generate a use interval symbol to separate each data of the plain text file, but the file suffix is XLS.

Note: This time, if you open such a file directly in Excel, everything is fine, but if you use Ado.net to read this file, your link engine should not be Excel, but a text file (Microsoft text Driver). That is, the link string should not be

Copy Code code as follows:

"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 way to connect strings:

Copy Code code as follows:

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)};
Dbq=c://11.txt;
Extensions=asc,csv,tab,txt;

Solution III, you want to create the Excel file, there are some Excel own features need to be created, which requires the use of Com, that is: Microsoft Excel Object Library

Add a reference to it from the Microsoft Excel 11.0 Object Library, which is not the same version of the component library, depending on the version of Office you installed.

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 here simply to create an Excel file, no more operations Excel, if you want to further study, you can refer to the relevant articles of this site.

II. Building an Access database

Access is, after all, a database, so Excel The first method above does not apply.
You can use ADOX to create an Access database file.
The difference between ADOX and OLE DB: ADOX is that the data API is just an interface, and OLE DB is the provider, the API, to invoke 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, perhaps the version here is not the same.

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, and for ADOX you can also manipulate databases, add tables, and more.

Using System;
Using ADOX;
 Summary description of namespace Webportal {///<summary>///createaccessdb. For different versions of ADO, you need to add different references///add references Microsoft ADO Ext. 2.7 For DDL and Security///Add a reference to Microsoft ADO Ext. 2.8 for DDL D Security///</summary> public class CreateAccessDB:System.Web.UI.Page {private void Page_Load (object send
   Er, System.EventArgs e) {//To facilitate testing, the database name takes a more random name to prevent the addition of unsuccessful additions to the IIS 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; Field type Col must be set first.
   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 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 code generated by the Web Forms Designer override protected void OnInit (EventArgs e) {///CODEGEN: This call is required by the ASP.net Web Forms Designer
   needed.
   InitializeComponent (); Base.
  OnInit (e);
  ///<summary>///Designer supports the desired method-do not modify the contents of///this method using the Code editor. </summary> private void InitializeComponent () {this. Load + = new System.EventHandler (this.
  Page_Load);

 } #endregion}}

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.