C#操作操作Excel数据增删改查示例数据增删改查示例
C#操作Excel数据增删改查。
首先创建ExcelDB.xlsx文件，并添加两张工作表。
工作表1：
UserInfo表，字段：UserId、UserName、Age、Address、CreateTime。
工作表2：
Order表，字段：OrderNo、ProductName、Quantity、Money、SaleDate。
1、创建ExcelHelper.cs类，Excel文件处理类
代码如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
namespace MyStudy.DAL
{
/// <summary>
/// Excel文件处理类
/// </summary>
public class ExcelHelper
{
private static string fileName = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + @”/ExcelFile/ExcelDB.xlsx”;
private static OleDbConnection connection;
public static OleDbConnection Connection
{
get
{
string connectionString = “”;
string fileType = System.IO.Path.GetExtension(fileName);
if (string.IsNullOrEmpty(fileType)) return null;
if (fileType == “.xls”)
{
connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;” + “Data Source=” + fileName + “;” + “;Extended Properties=\”Excel
8.0;HDR=YES;IMEX=2\””;
}
else
{
connectionString = “Provider=Microsoft.ACE.OLEDB.12.0;” + “Data Source=” + fileName + “;” + “;Extended
Properties=\”Excel 12.0;HDR=YES;IMEX=2\””;
}
if (connection == null)
{
connection = new OleDbConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
/// <summary>
/// 执行无参数的SQL语句
/// </summary>
/// <param name=”sql”>SQL语句</param>
/// <returns>返回受SQL语句影响的行数</returns>
public static int ExecuteCommand(string sql)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
剩余6页未读，继续阅读
资源评论
