Detailed explanation of how to create Access files and Excel files using C#.net programming

The examples in this article describe how to create Access files and Excel files using C#.net programming. Share it with everyone for your reference, the details are as follows:

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

Excel files or Access files that need to be exported may not exist in advance, which requires us to program and generate them ourselves. Here are some methods for generating these two files, and only the most commonly used ones are listed. of. Not all.

1. First generate the Excel file.

Option 1. If you use Excel to save only two-dimensional data, use it as a database.

The simplest, you don't need to reference any additional components, you only need to use OLEDB to create the Excel file. 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 += @" 测试表 ";
  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 += @" 测试表 ";
  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 a table, if the system finds that the Excel file does not exist, it will automatically complete the creation of the Excel file. People who have never been in contact with it may not know this.

As for the addition and modification operations, they are no different from ordinary databases and will not be described.

Option 2: Directly generate a plain text file that uses spacers to separate each item of data, but the suffix of the file is XLS.

Note: At this time, if you directly use Excel to open such a file, no problem, everything is normal, 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 to say, the link string should not be

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

but should be the following way:

OLEDB way to connect 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;

Option 3. The Excel file you want to create has some Excel own features that need to be created, which requires the use of Com, that is: Microsoft Excel Object Library

Please add Microsoft The Excel 11.0 Object Library references it. Depending on the version of Office you have 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 just simply created an Excel file here, and there is no more operation on Excel. If you want to learn more, you can refer to the relevant articles on this site.

2. Generate Access database

Access is a database after all, so the first method mentioned above in Excel cannot be applied.
You can use ADOX to create Access database files.
The difference between ADOX and OleDB: ADOX is the data api, which is just an interface. OLEDB is the data provider, and the API calls the data provider.

Sample code:

Before use, please add a reference to Microsoft ADO Ext. 2.x for DDL and Security Depending on your operating system, the version here may be different.

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 only generates the Access database. Using ADOX, you can also operate the database, add tables, etc.

using System;
using ADOX;
namespace WebPortal
{
 /// <summary>
 /// CreateAccessDB 的摘要说明。
 /// 对于不同版本的ADO，需要添加不同的引用
 /// 请添加引用Microsoft ADO Ext. 2.7 for DDL and Security
 /// 请添加引用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)
  {
   //为了方便测试，数据库名字采用比较随机的名字，以防止添加不成功时还需要重新启动IIS来删除数据库。
   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("数据库：" + dbName + "已经创建成功！");
   ADOX.TableClass tbl = new ADOX.TableClass();
   tbl.ParentCatalog = cat;
   tbl.Name="MyTable";
   //增加一个自动增长的字段
   ADOX.ColumnClass col = new ADOX.ColumnClass();
   col.ParentCatalog = cat;
   col.Type=ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
   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);
   //增加一个文本字段
   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);
   //设置主键
   tbl.Keys.Append("PrimaryKey",ADOX.KeyTypeEnum.adKeyPrimary,"id","","");
   cat.Tables.Append (tbl);
   Response.Write("<br>数据库表：" + tbl.Name + "已经创建成功！");
   tbl=null;
   cat = null;
  }
  #region Web 窗体设计器生成的代码
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
   //
   InitializeComponent();
   base.OnInit(e);
  }
  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  { 
   this.Load += new System.EventHandler(this.Page_Load);
  }
  #endregion
 }
}

I hope this article will be helpful to everyone in C# programming.

For more detailed explanations on how to create Access files and Excel files using C#.net programming, please pay attention to the PHP Chinese website for related articles!