C#导入Excel数据

2021-09-18 21:52 125阅读 0赞

开始前先创建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();
    }