This article describes the method of creating Access files and Excel files by C#.net programming. Share it for your reference, 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, two files that need to be exported may not exist in advance, so we need to program and generate them ourselves. Let’s sort out some methods for generating these two files, and only list the most commonly used ones. Not complete.
1. First generate an Excel file.
Solution 1. If you only save two-dimensional data in Excel, you can use it as a database.
The simplest thing is that you don’t have to reference any extra components, you just need to use OLEDB to create an Excel file. The example code is as follows.
using ;
public static void CreateExcelFile2()
{
string OLEDBConnStr = "Provider=.4.0;Data Source=c://;";
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();
= OLEDBConnStr;
OleDbCommand oCreateComm = new OleDbCommand();
= oConn;
= strCreateTableSQL;
();
();
();
}
using ;
public static void CreateExcelFile2()
{
string OLEDBConnStr = "Provider=.4.0;Data Source=c://;";
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();
= OLEDBConnStr;
OleDbCommand oCreateComm = new OleDbCommand();
= oConn;
= strCreateTableSQL;
();
();
();
}
While you are creating a table, if the system finds that the Excel file does not exist, it will automatically complete the creation of the Excel file. If you have never been in contact with this, you may not know about it.
As for the addition and modification operations to them, they are no different from ordinary databases, so I will not describe them.
Scheme 2: Directly generate a plain text file that uses interval symbols to separate each item of data, but the suffix of the file is XLS.
Note: At this time, if you open such a file directly with Excel, it will be fine, everything is normal, but if you use 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
Instead, it should be the following method:
OLEDB connection strings:
How to read TXT strings in ODBC:
Driver={Microsoft Text Driver (*.txt; *.csv)};
Dbq=C://;
Extensions=asc,csv,tab,txt;
Solution 3. The Excel file you want to create has some of the Excel features that need to be created, so you need to use Com, that is: Microsoft Excel Object Library
Please add a reference to it by Microsoft Excel 11.0 Object Library. Depending on the version of Office you installed, the version of this component library is also different.
Example code:
public static void CreateExcelFile()
{
string FileName = "c://";
Missing miss = ;
m_objExcel = new ();
m_objExcel.Visible = false;
m_objBooks = ()m_objExcel.Workbooks;
m_objBook = ()(m_objBooks.Add(miss));
m_objBook.SaveAs(FileName, miss, miss, miss, miss,
miss, , miss,
miss,miss, miss, miss);
m_objBook.Close(false, miss, miss);
m_objExcel.Quit();
}
I have just created an Excel file here, and there is no more operation for 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.
To create Access database files, you can use ADOX.
The difference between ADOX and OleDB: ADOX is a data api and is just an interface, OLEDB is a data provider, and the API calls the data provider.
Example code:
Before using, please add a reference Microsoft ADO Ext. for DDL and Security Depending on your operating system, the version here may be different.
using ADOX;
using ;
public static void CreateAccessFile(string FileName)
{
if(!(FileName))
{
cat = new ();
("Provider=.4.0;Data Source=" + FileName +";");
cat = null;
}
}
The above code just generates an Access database. You can also operate the database, add tables and other operations for ADOX.
using System;
using ADOX;
namespace WebPortal
{
/// <summary>
/// Summary description of CreateAccessDB. /// For different versions of ADO, different references need to be added /// Please add a reference to Microsoft ADO Ext. 2.7 for DDL and Security /// Please add a reference to Microsoft ADO Ext. 2.8 for DDL and Security /// </summary>
public class CreateAccessDB :
{
private void Page_Load(object sender, e)
{
//To facilitate testing, the database name adopts a relatively random name to prevent the need to restart IIS to delete the database when the addition is unsuccessful. string dbName = "D://NewMDB"+()+".mdb";
cat = new ();
("Provider=.4.0;Data Source=" + dbName +";");
("database:" + dbName + "Created successfully!");
tbl = new ();
= cat;
="MyTable";
//Add a field that automatically grows col = new ();
= cat;
=; // The field type must be set first = "id";
["Jet OLEDB:Allow Zero Length"].Value= false;
["AutoIncrement"].Value= true;
(col,,0);
//Add a text field col2 = new ();
= cat;
= "Description";
["Jet OLEDB:Allow Zero Length"].Value= false;
(col2,,25);
//Set the primary key ("PrimaryKey",,"id","","");
(tbl);
("<br>Database table:" + + "Created successfully!");
tbl=null;
cat = null;
}
#region Web Form Designer generated code override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required for the Web Form Designer. //
InitializeComponent();
(e);
}
/// <summary>
/// Designer supports required methods - do not use code editor to modify /// Contents of this method. /// </summary>
private void InitializeComponent()
{
+= new (this.Page_Load);
}
#endregion
}
}
For more information about C#, please visit the special topic of this site:Summary of XML file operation skills in C#》、《Tutorial on the usage of common C# controls》、《Summary of WinForm control usage》、《C# data structure and algorithm tutorial》、《Introduction to C# object-oriented programming tutorial"and"Summary of thread usage techniques for C# programming》
I hope this article will be helpful to everyone's C# programming.