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>SQL语句</param>
/// <returns>返回受SQL语句影响的行数</returns>
public static int ExecuteCommand(string sql)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
int result = cmd.ExecuteNonQuery();
connection.Close();
return result;
}
/// <summary>
/// 执行有参数的SQL语句
/// </summary>
/// <param>SQL语句</param>
/// <param>参数集合</param>
/// <returns>返回受SQL语句影响的行数</returns>
public static int ExecuteCommand(string sql, params OleDbParameter[] values)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
connection.Close();
return result;
}
/// <summary>
/// 返回单个值无参数的SQL语句
/// </summary>
/// <param>SQL语句</param>
/// <returns>返回受SQL语句查询的行数</returns>
public static int GetScalar(string sql)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
return result;
}
/// <summary>
/// 返回单个值有参数的SQL语句
/// </summary>
/// <param>SQL语句</param>
/// <param>参数集合</param>
/// <returns>返回受SQL语句查询的行数</returns>
public static int GetScalar(string sql, params OleDbParameter[] parameters)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(parameters);
int result = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
return result;
}
/// <summary>
/// 执行查询无参数SQL语句
/// </summary>
/// <param>SQL语句</param>
/// <returns>返回数据集</returns>
public static DataSet GetReader(string sql)
{
OleDbDataAdapter da = new OleDbDataAdapter(sql, Connection);
DataSet ds = new DataSet();
da.Fill(ds, "UserInfo");
connection.Close();
return ds;
}
/// <summary>
/// 执行查询有参数SQL语句
/// </summary>
/// <param>SQL语句</param>
/// <param>参数集合</param>
/// <returns>返回数据集</returns>
public static DataSet GetReader(string sql, params OleDbParameter[] parameters)
{
OleDbDataAdapter da = new OleDbDataAdapter(sql, Connection);
da.SelectCommand.Parameters.AddRange(parameters);
DataSet ds = new DataSet();
da.Fill(ds);
connection.Close();
return ds;
}
}
}
2、 创建实体类
2.1 创建UserInfo.cs类，用户信息实体类。
复制代码 代码如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace MyStudy.Model
{
/// <summary>
/// 用户信息实体类
/// </summary>
public class UserInfo
{
public int UserId { get; set; }
public string UserName { get; set; }
public int? Age { get; set; }
public string Address { get; set; }
public DateTime? CreateTime { get; set; }
/// <summary>
/// 将DataTable转换成List数据
/// </summary>
public static List<UserInfo> ToList(DataSet dataSet)
{
List<UserInfo> userList = new List<UserInfo>();
if (dataSet != null && dataSet.Tables.Count > 0)
{
foreach (DataRow row in dataSet.Tables[0].Rows)
{
UserInfo user = new UserInfo();
if (dataSet.Tables[0].Columns.Contains("UserId") && !Convert.IsDBNull(row["UserId"]))
user.UserId = Convert.ToInt32(row["UserId"]);
if (dataSet.Tables[0].Columns.Contains("UserName") && !Convert.IsDBNull(row["UserName"]))
user.UserName = (string)row["UserName"];
if (dataSet.Tables[0].Columns.Contains("Age") && !Convert.IsDBNull(row["Age"]))
user.Age = Convert.ToInt32(row["Age"]);
if (dataSet.Tables[0].Columns.Contains("Address") && !Convert.IsDBNull(row["Address"]))
user.Address = (string)row["Address"];
if (dataSet.Tables[0].Columns.Contains("CreateTime") && !Convert.IsDBNull(row["CreateTime"]))
user.CreateTime = Convert.ToDateTime(row["CreateTime"]);
userList.Add(user);
}
}
return userList;
}
}
}
2.2 创建Order.cs类，订单实体类。
复制代码 代码如下: