C# 导入Excel数据的方式（两种）

方式一、导入数据到数据集对象，只支持Excel的标准格式，即不能合并单元格等等

/// <summary>
    /// 导入数据到数据集中
    /// 备注:此种方法只支持excel原文件
    /// </summary>
    /// <param name="Path">文件路劲</param>
    /// <param name="exceptionMsg">异常信息</param>
    /// <returns></returns>
    public static System.Data.DataTable InputExcel(string Path, ref string exceptionMsg)
    {
        System.Data.DataTable dt = null;
        try
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                System.Data.DataTable sheetDt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string[] sheet = new string[sheetDt.Rows.Count];
                for (int i = 0; i < sheetDt.Rows.Count; i++)
                {
                    sheet[i] = sheetDt.Rows[i]["TABLE_NAME"].ToString();
                }
                string strExcel = string.Format("select * from [{0}]", sheet[0]);
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                dt = new System.Data.DataTable();
                myCommand.Fill(dt);
                conn.Close();
            }
        }
        catch (Exception ex)
        {
            exceptionMsg = ex.Message;
        }
        return dt;
    }

 

方法二、读取Excel文件，然后根据里面的数据信息拼装

#region 读取Excel表格中数据到DataTable中

    public static System.Data.DataTable ChangeExcelToDateTable(string _path)
    {
        System.Data.DataTable tempdt = new System.Data.DataTable();
        tempdt.TableName = "Excel";
        Application app = new Application();
        object obj = System.Reflection.Missing.Value;
        try
        {
            Workbook _wBook = app.Workbooks.Open(_path, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj);
            Worksheet _wSheet = (Worksheet)_wBook.Worksheets.get_Item(1);
            DataRow newRow = null;
            DataColumn newColumn = null;
            for (int i = 2; i <= _wSheet.UsedRange.Rows.Count; i++)
            {
                newRow = tempdt.NewRow();
                for (int j = 1; j <= _wSheet.UsedRange.Columns.Count; j++)
                {
                    if (i == 2 && j == 1)
                    {
                        //表头
                        for (int k = 1; k <= _wSheet.UsedRange.Columns.Count; k++)
                        {
                            string str = (_wSheet.UsedRange[1, k] as Range).Value2.ToString();
                            newColumn = new DataColumn(str);
                            newRow.Table.Columns.Add(newColumn);
                        }
                    }
                    Range range = _wSheet.Cells[i, j] as Range;
                    if (range != null && !"".Equals(range.Text.ToString()))
                    {
                        newRow[j - 1] = range.Value2;

                    }
                }
                tempdt.Rows.Add(newRow);
            }
            _wSheet = null;
            _wBook = null;
            app.Quit();
            Kill(app);
            int generation = System.GC.GetGeneration(app);
            app = null;
            System.GC.Collect(generation);
            return tempdt;
        }
        catch (Exception ex)
        {
            app.Quit();
            Kill(app);
            int generation = System.GC.GetGeneration(app);
            app = null;
            throw ex;
        }
    }
   
    #endregion

    #region 结束进程

    [DllImport("User32.dll", CharSet = CharSet.Auto)]
    private static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
    private static void Kill(Microsoft.Office.Interop.Excel.Application excel)
    {
        IntPtr t = new IntPtr(excel.Hwnd);   //得到这个句柄，具体作用是得到这块内存入口
        int k = 0;
        GetWindowThreadProcessId(t, out k);   //得到本进程唯一标志k
        System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);   //得到对进程k的引用
        p.Kill();     //关闭进程k
    }

    #endregion