C. net Programming to Create Access File and Excel File

This article illustrates how C #. net programming creates Access files and Excel files. Share it for your reference, as follows:

1 Some systems may need to export data to Access or Excel file format to facilitate data transfer, printing, etc.

Excel file or Access these two need to export files may not exist in advance, which requires us to program to generate them. The following is a series of methods to generate these two files, only listing the most commonly used ones. Not all.

1. Generate the Excel file first.

Scheme 1. If only 2-dimensional data is saved with Excel, it is used as a database.

At its simplest, you don't need to refer to any additional components, just use OLEDB to create an Excel file. The sample 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 create the table, if the system finds that the Excel file does not exist, it automatically completes the creation of the Excel file. This may not be known to people who have not been in contact with it.

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

Scenario 2. Directly generate a plain text file with a space symbol separating each item of data, but the suffix of the file is XLS.

Note: At this time, if you use Excel directly to open such a file, no problem, 1 is normal, but if you use ADO. net to read this file, your link engine should not be Excel, but text file (Microsoft ES40Driver). That is, the link string should not be

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

It should be the following way:

The way OLEDB connects the 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 writing:

Driver={Microsoft Text Driver (*.txt; *.csv)};
Dbq=C://11.txt;
Extensions=asc,csv,tab,txt;

Scenario 3. You want to create Excel file, there are 1 Excel own features need to create, this need to use Com, namely: Microsoft Excel Object Library

Please add a reference to Microsoft Excel 11.0 Object Library. Depending on the version of Office you installed, the version of this component library is different.

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 simply created here Excel file, there is no more operation Excel, if you want to study in depth, you can refer to the site related articles.

2. Generate an Access database

After all, Access is a database, so Excel method 1 above cannot be applied.
You can create Access database files using ADOX,
The difference between ADOX and OleDB: ADOX is data, api is only an interface, OLEDB is a data provider, and API calls the data provider.

Sample code:

Before using, please 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 generated the Access database, applicable to ADOX you can also operate the database, add tables and so on.

using System;
using ADOX;
namespace WebPortal
{
 /// <summary>
 /// CreateAccessDB  A summary description of. 
 ///  For different versions of ADO You need to add a different reference 
 ///  Please add a reference Microsoft ADO Ext. 2.7 for DDL and Security
 ///  Please add a reference Microsoft ADO Ext. 2.8 for DDL and Security
 /// </summary>
 public class CreateAccessDB : System.Web.UI.Page
 {
  private void Page_Load(object sender, System.EventArgs e)
  {
   // For the convenience of testing, the database name is randomly named to prevent the need to restart if the addition is unsuccessful IIS To delete 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";
   // Increase 1 Fields that grow automatically 
   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);
   // Increase 1 Text fields 
   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 tables: " + tbl.Name + " Has been created successfully! ");
   tbl=null;
   cat = null;
  }
  #region Web  Code Generated by Form Designer 
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN:  The call is  ASP.NET Web  Required by the form designer. 
   //
   InitializeComponent();
   base.OnInit(e);
  }
  /// <summary>
  ///  Designer supports the required methods  -  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
 }
}

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.