C#导入Excel数据

第一步：

#region 导入Excel数据
        protected override void ExcelImport()
        {
            try
            {
                string filePath = Utils.SelectFile("excel");
                if (filePath == "")
                    return;
                DataTable excelDt = Utils.ImportExcelData(filePath);         //Excel表格中的数据
                string msgInfo = "";
                
                        msgInfo = bRole.ExcelImport(excelDt, frmMain.username);        //开始导入
                //重新加载数据
                this.BindRole();
            }
            catch (Exception ex)
            {
                Msg.InfoBox(ex.Message);
            }
        }
        #endregion

　　第二步：

/// <summary>
        /// 选择文件
        /// </summary>
        /// <param name="fileType">文件类型:输入xml为.xml文件，输入word为.doc文件，
        /// excel为.xls文件，access为.mdb文件，输入image表示图像文件，输入txt表示文本文件，
        /// 默认为文本文件和所有类型文件</param>
        /// <returns>文件路径</returns>
        public static string SelectFile(string fileType)
        {
            string filePath = "";
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Title = "选择文件";
            switch (fileType)
            {
                case "xml":
                    dialog.Filter = "XML文件(*.xml)|*.xml";
                    break;
                case "word":
                    dialog.Filter = "Word文件(*.doc)|*.doc|所有文件(*.*)|*.*";
                    break;
                case "excel":
                    dialog.Filter = "Excel文件(*.xls)|*.xls|所有文件(*.*)|*.*";
                    break;
                case "access":
                    dialog.Filter = "Access文件(*.mdb)|*.mdb|所有文件(*.*)|*.*";
                    break;
                case "image":
                    dialog.Filter = "JPG文件(*.jpg)|*.jpg|JPEG文件(*.jpeg)|*.jpeg|PNG文件(*.png)|*.png|BMP文件(*.bmp)|*.bmp|所有文件(*.*)|*.*";
                    break;
                case "txt":
                    dialog.Filter = "文本文件(*.txt)|*.txt";
                    break;
                default:
                    dialog.Filter = "文本文件(*.txt)|*.txt|所有文件(*.*)|*.*";
                    break;
            }
            dialog.FilterIndex = 0;
            dialog.Multiselect = false;
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                filePath = dialog.FileName;
            }
            else
            {
                filePath = "";
            }
            dialog.Dispose();

            return filePath;
        }

　　第三步：/// <summary>

        /// Excel数据导入
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        public static DataTable ImportExcelData(string filePath)
        {
            　DataTable sheetNameDt = null;

　　　　　　　　//之前 excel 2003
　　　　       　//string conStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
　　　　　　　 // , filePath);

　　　　　　　 //现在 以下此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
　　　　　　　 string conStr = string.Format("Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; "
　　　　　　　 + "HDR=Yes; IMEX=1'", filePath);

　　　　　　　　//备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
　　　　　　　　//"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。

            OleDbConnection conn = new OleDbConnection(conStr);
            try
            {
                conn.Open();
                sheetNameDt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                conn.Close();
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
            string sql = string.Format("SELECT * FROM [{0}$]", sheetNameDt.Rows[0][2].ToString().Trim().Replace("'", "").TrimEnd('$'));
            OleDbCommand comm = conn.CreateCommand();
            comm.CommandText = sql;
            OleDbDataAdapter adpt = new OleDbDataAdapter();
            adpt.SelectCommand = comm;
            DataSet ds = new DataSet();

            try
            {
                adpt.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
        }

　　也可以把excel导入datatable独立到一个类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.OleDb;

namespace CvnX2.Utils
{
    /// <summary>
    /// Excel 操作类
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 将Excel文档第一页中数据导入到DataTable，支持.xls及.xlsx格式
        /// </summary>
        /// <param name="path">Excel完整物理路径</param>
        /// <returns>DataTable</returns>
        public static DataTable ExcelToDataTable(string path)
        {
            DataTable dt = new DataTable();
            if (!File.Exists(path)) { return dt; }

            string strConn = "";
            string tableName = null;
            DataSet ds = new DataSet();

            try
            {

                if (Path.GetExtension(path).ToUpper() == ".XLS")
                {
                    if (OraDm.IsClientProject)
                        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
                    else
                        strConn = "Provider= Microsoft.Ace.OleDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;IMEX=1'";
                }
                else
                    strConn = "Provider= Microsoft.Ace.OleDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    conn.Open();
                    DataTable dt1 = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    tableName = dt1.Rows[0][2].ToString().Trim();

                    string strExcel = "";
                    OleDbDataAdapter myCommand = null;
                    strExcel = String.Format("select * from [{0}]", tableName);
                    myCommand = new OleDbDataAdapter(strExcel, strConn);
                    myCommand.Fill(ds);
                    conn.Close();
                }

                if (ds.Tables.Count == 0) { return new DataTable(); }

                //筛选空行

                //变量储存筛选条件
                string where = string.Empty;
                //遍历数据源的所有的列
                foreach (DataColumn item in ds.Tables[0].Columns)
                {
                    //叠加所有列,条件是为空的
                    where += "[" + item.ColumnName + "] is null and ";
                }

                //为空的条件取反,表示只显示全部列的数据不为空的行
                where = "(" + where.Substring(0, where.Length - 4) + ") = false";

                //设置筛选条件
                ds.Tables[0].DefaultView.RowFilter = where;

                //重新转换为数据源
                dt = ds.Tables[0].DefaultView.ToTable();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }

            return dt;
        }

        /// <summary>
        /// 将Excel文档所有页数据导入到DataSet，支持.xls及.xlsx格式，无数据的页不处理
        /// </summary>
        /// <param name="path">Excel完整物理路径</param>
        /// <returns>DataSet</returns>
        public static DataSet ExcelToDataSet(string path)
        {
            DataSet ds = new DataSet();
            if (!File.Exists(path)) { return ds; }

            string strConn = "";
            string tableName = null;

            try
            {
                if (Path.GetExtension(path).ToUpper() == ".XLS")
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
                else
                    strConn = "Provider= Microsoft.Ace.OleDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";


                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    DataSet dstmp = new DataSet();

                    conn.Open();
                    DataTable dt1 = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    for (int i = 0; i < dt1.Rows.Count; i++)
                    {
                        //tableName = dt1.Rows[0][2].ToString().Trim();
                        tableName = dt1.Rows[i][2].ToString().Trim();

                        string strExcel = "";
                        OleDbDataAdapter myCommand = null;
                        strExcel = String.Format("select * from [{0}]", tableName);
                        myCommand = new OleDbDataAdapter(strExcel, strConn);
                        dstmp.Clear();
                        myCommand.Fill(dstmp);

                        if (dstmp.Tables.Count == 0) { continue; }

                        //筛选空行

                        //变量储存筛选条件
                        string where = string.Empty;
                        //遍历数据源的所有的列
                        foreach (DataColumn item in dstmp.Tables[0].Columns)
                        {
                            //叠加所有列,条件是为空的
                            where += "[" + item.ColumnName + "] is null and ";
                        }

                        //为空的条件取反,表示只显示全部列的数据不为空的行
                        where = "(" + where.Substring(0, where.Length - 4) + ") = false";

                        //设置筛选条件
                        dstmp.Tables[0].DefaultView.RowFilter = where;
                        //重新转换为数据源
                        DataTable dt = dstmp.Tables[0].DefaultView.ToTable();
                        dt.TableName = "Table" + i.ToString();
                        if (dt.Rows.Count > 0)
                        {
                            ds.Tables.Add(dt);
                        }
                    }

                    conn.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e.InnerException);
            }

            return ds;
        }

    }
}