search
HomeBackend DevelopmentC#.Net TutorialDetailed 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!


Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
C# .NET: Exploring Core Concepts and Programming FundamentalsC# .NET: Exploring Core Concepts and Programming FundamentalsApr 10, 2025 am 09:32 AM

C# is a modern, object-oriented programming language developed by Microsoft and as part of the .NET framework. 1.C# supports object-oriented programming (OOP), including encapsulation, inheritance and polymorphism. 2. Asynchronous programming in C# is implemented through async and await keywords to improve application responsiveness. 3. Use LINQ to process data collections concisely. 4. Common errors include null reference exceptions and index out-of-range exceptions. Debugging skills include using a debugger and exception handling. 5. Performance optimization includes using StringBuilder and avoiding unnecessary packing and unboxing.

Testing C# .NET Applications: Unit, Integration, and End-to-End TestingTesting C# .NET Applications: Unit, Integration, and End-to-End TestingApr 09, 2025 am 12:04 AM

Testing strategies for C#.NET applications include unit testing, integration testing, and end-to-end testing. 1. Unit testing ensures that the minimum unit of the code works independently, using the MSTest, NUnit or xUnit framework. 2. Integrated tests verify the functions of multiple units combined, commonly used simulated data and external services. 3. End-to-end testing simulates the user's complete operation process, and Selenium is usually used for automated testing.

Advanced C# .NET Tutorial: Ace Your Next Senior Developer InterviewAdvanced C# .NET Tutorial: Ace Your Next Senior Developer InterviewApr 08, 2025 am 12:06 AM

Interview with C# senior developer requires mastering core knowledge such as asynchronous programming, LINQ, and internal working principles of .NET frameworks. 1. Asynchronous programming simplifies operations through async and await to improve application responsiveness. 2.LINQ operates data in SQL style and pay attention to performance. 3. The CLR of the NET framework manages memory, and garbage collection needs to be used with caution.

C# .NET Interview Questions & Answers: Level Up Your ExpertiseC# .NET Interview Questions & Answers: Level Up Your ExpertiseApr 07, 2025 am 12:01 AM

C#.NET interview questions and answers include basic knowledge, core concepts, and advanced usage. 1) Basic knowledge: C# is an object-oriented language developed by Microsoft and is mainly used in the .NET framework. 2) Core concepts: Delegation and events allow dynamic binding methods, and LINQ provides powerful query functions. 3) Advanced usage: Asynchronous programming improves responsiveness, and expression trees are used for dynamic code construction.

Building Microservices with C# .NET: A Practical Guide for ArchitectsBuilding Microservices with C# .NET: A Practical Guide for ArchitectsApr 06, 2025 am 12:08 AM

C#.NET is a popular choice for building microservices because of its strong ecosystem and rich support. 1) Create RESTfulAPI using ASP.NETCore to process order creation and query. 2) Use gRPC to achieve efficient communication between microservices, define and implement order services. 3) Simplify deployment and management through Docker containerized microservices.

C# .NET Security Best Practices: Preventing Common VulnerabilitiesC# .NET Security Best Practices: Preventing Common VulnerabilitiesApr 05, 2025 am 12:01 AM

Security best practices for C# and .NET include input verification, output encoding, exception handling, as well as authentication and authorization. 1) Use regular expressions or built-in methods to verify input to prevent malicious data from entering the system. 2) Output encoding to prevent XSS attacks, use the HttpUtility.HtmlEncode method. 3) Exception handling avoids information leakage, records errors but does not return detailed information to the user. 4) Use ASP.NETIdentity and Claims-based authorization to protect applications from unauthorized access.

In c language: What does it meanIn c language: What does it meanApr 03, 2025 pm 07:24 PM

The meaning of colon (':') in C language: conditional statement: separating conditional expressions and statement block loop statement: separating initialization, conditional and incremental expression macro definition: separating macro name and macro value single line comment: representing the content from colon to end of line as comment array dimension: specify the dimension of the array

What does a mean in C languageWhat does a mean in C languageApr 03, 2025 pm 07:21 PM

A in C language is a post-increase operator, and its operating mechanism includes: first obtaining the value of the variable a. Increase the value of a by 1. Returns the value of a after increasing.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: How To Unlock Everything In MyRise
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment