C#导入Excel数据

开始前先创建Order.xlsx文件，用于测试。该Excel文件有字段：订单编号、商品名称、数量、总金额、销售时间。并插入几条测试数据。

1、创建ExcelHandler.cs类，Excel文件处理类。

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.OleDb;namespace ConsoleApplication1{    /// <summary>    /// Excel文件处理类    /// </summary>    public class ExcelHandler    {        private string filePath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + @"/ExcelFile/";        /// <summary>        /// 读取Excel文件数据        /// </summary>        /// <param name="fileName"></param>        /// <returns></returns>        public DataSet GetFileDataSet(string fileName)        {            DataSet ds = new DataSet();            if (!String.IsNullOrEmpty(fileName))            {                string connStr = "";                string fileType = System.IO.Path.GetExtension(fileName);                if (string.IsNullOrEmpty(fileType)) return null;                filePath += fileName;                if (fileType == ".xls")                {                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";                }                else                {                    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";                }                OleDbConnection conn = null;                OleDbDataAdapter da = null;                DataTable dtSheetName = null;                string sql_F = "Select * FROM [{0}]";                  try                {                    // 初始化连接，并打开                      conn = new OleDbConnection(connStr);                    conn.Open();                    // 获取数据源的表定义元数据                                             string SheetName = "";                    dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                    // 初始化适配器                      da = new OleDbDataAdapter();                    for (int i = 0; i < dtSheetName.Rows.Count; i++)                    {                        SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];                        if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))                        {                            continue;                        }                        da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);                        DataSet dsItem = new DataSet();                        da.Fill(dsItem, SheetName);                        ds.Tables.Add(dsItem.Tables[0].Copy());                    }                }                catch (Exception ex)                {                    throw ex;                }                finally                {                    // 关闭连接                      if (conn.State == ConnectionState.Open)                    {                        conn.Close();                        da.Dispose();                        conn.Dispose();                    }                }            }            return ds;          }    }}

2、创建Order.cs类，订单实体类

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;namespace ConsoleApplication1{    /// <summary>    /// 订单实体类    /// </summary>    public class Order    {        /// <summary>        /// 订单编号        /// </summary>        public string OrderNo { get; set; }        /// <summary>        /// 商品名称        /// </summary>        public string ProductName { get; set; }        /// <summary>        /// 数量        /// </summary>        public int? Quantity { get; set; }        /// <summary>        /// 总金额        /// </summary>        public decimal? Money { get; set; }        /// <summary>        /// 销售时间        /// </summary>        public DateTime? SaleDate { get; set; }        /// <summary>        /// 将DataTable转换成List数据        /// </summary>        public static List<Order> ToList(DataSet dataSet)        {            List<Order> orderList = new List<Order>();            if (dataSet != null && dataSet.Tables.Count > 0)            {                foreach (DataRow row in dataSet.Tables[0].Rows)                {                    Order order = new Order();                    if (dataSet.Tables[0].Columns.Contains("订单编号") && !Convert.IsDBNull(row["订单编号"]))                        order.OrderNo = (string)row["订单编号"];                    if (dataSet.Tables[0].Columns.Contains("商品名称") && !Convert.IsDBNull(row["商品名称"]))                        order.ProductName = (string)row["商品名称"];                    if (dataSet.Tables[0].Columns.Contains("数量") && !Convert.IsDBNull(row["数量"]))                        order.Quantity = Convert.ToInt32(row["数量"]);                    if (dataSet.Tables[0].Columns.Contains("总金额") && !Convert.IsDBNull(row["总金额"]))                        order.Money = Convert.ToDecimal(row["总金额"]);                    if (dataSet.Tables[0].Columns.Contains("销售时间") && !Convert.IsDBNull(row["销售时间"]))                        order.SaleDate = Convert.ToDateTime(row["销售时间"]);                    orderList.Add(order);                }            }            return orderList;        }    }}

3、测试

static void Main(string[] args){    ExcelHandler excelHandler = new ExcelHandler();     DataSet ds = excelHandler.GetFileDataSet("Order.xlsx");    List<Order> orderList = Order.ToList(ds);    foreach (Order order in orderList)    {        Console.WriteLine("{0} {1} {2} {4}", order.OrderNo, order.ProductName, order.Quantity, order.Money, order.SaleDate);    }    Console.Read();}