This article describes the method of using oledb to operate excel files in C#. Share it for your reference. The specific analysis is as follows:
No matter what programming language, it will provide a way to operate Excel files. There are mainly the following ways to operate Excel in C#:
Note: Use Office's Excel component to operate excel files
Advantages: Ability to fully operate Excel files and generate rich file content
Disadvantages: You need to install Excel on the computer, and it will start the Excel process, which is very inconvenient on the web.
Description: A component that operates word processing documents includes Excel
Advantages: Ability to operate Excel 2007 version files
Disadvantages: Only able to operate Excel2007 files
Description: An open source Excel read and write library
Advantages: No need to install Excel
Disadvantages: Only able to operate Excel2003 documents, and the content control is not complete.
Note: Use the Microsoft Jet provider to connect to an Excel workbook to read and write Excel files as data source
Advantages: Simple and fast, able to operate higher versions of Excel
Disadvantages: Only limited operations can be performed (read, write)
Today I learn how to use OleDb to operate Excel files
Connection string: Provider=.4.0;Data Source=d:\;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'
Provider: Indicates the provider name
Data Source: Fill in the Excel file path here
Extended Properties: Set special properties of Excel
Extended Properties Value:
Excel 8.0 is for Excel 2000 and above, and Excel 5.0 is for Excel 97.
HDR=Yes means that the first row contains the column name, and the first row does not contain the first row when calculating the number of rows.
IMEX 0: Import mode, 1: Export mode: 2 Mixed mode
1. Read the excel file
if (() == )
{
String sConnectionString = "Provider=.4.0;" +
"Data Source="++";"+
"Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'";
//Instantiate an Oledbconnection class (Implement IDisposable, using) using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString))
{
ole_conn.Open();
using (OleDbCommand ole_cmd = ole_conn.CreateCommand())
{
//Search statement similar to SQL [Sheet1$ corresponds to a worksheet in the Excel file] ole_cmd.CommandText = "select * from [Sheet1$]";
OleDbDataAdapter adapter = new OleDbDataAdapter(ole_cmd);
DataSet ds = new DataSet();
(ds, "Sheet1");
for (int i = 0; i < [0].; i++)
{
([0].Rows[i]["Business Name"].ToString());
}
}
}
}
2. Get all worksheets in the workbook
if (() == )
{
String sConnectionString = "Provider=.4.0;" +
"Data Source="++";"+
"Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'";
//Instantiate an Oledbconnection class (Implement IDisposable, using) using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString))
{
ole_conn.Open();
using (OleDbCommand ole_cmd = ole_conn.CreateCommand())
{
DataTable tb = ole_conn.GetOleDbSchemaTable(, null);
foreach (DataRow row in )
{
(row["TABLE_NAME"].ToString());
}
}
}
}
3. Write data to Excel table
if (() == )
{
String sConnectionString = "Provider=.4.0;" +
"Data Source="++";"+
"Extended Properties=Excel 8.0;";
//Instantiate an Oledbconnection class (Implement IDisposable, using) using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString))
{
ole_conn.Open();
using (OleDbCommand ole_cmd = ole_conn.CreateCommand())
{
ole_cmd.CommandText = "insert into [Sheet1$](MerchantID,Business name)values('DJ001','Click on technology')";
ole_cmd.ExecuteNonQuery();
("Data insertion successfully...");
}
}
}
4. Create Excel file and write data
String sConnectionString = "Provider=.4.0;" +
"Data Source=d:\\;" +
"Extended Properties=Excel 8.0;";
//Instantiate an Oledbconnection class (Implement IDisposable, using) using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString))
{
ole_conn.Open();
using (OleDbCommand ole_cmd = ole_conn.CreateCommand())
{
ole_cmd.CommandText = "CREATE TABLE CustomerInfo ([CustomerID] VarChar,[Customer] VarChar)";
ole_cmd.ExecuteNonQuery();
ole_cmd.CommandText = "insert into CustomerInfo(CustomerID,Customer)values('DJ001','Click on technology')";
ole_cmd.ExecuteNonQuery();
("Generate Excel file successfully and write a piece of data...");
}
}
I hope this article will be helpful to everyone's C# programming.