将DataTable导出为Excel或从Excel读取到DataTable

        /// <summary>
        /// 从Excel中读取数据到DataTable
        /// </summary>
        /// <param name="Path">要读取的Excel文件名称</param>
        /// <returns>返回一个数据集</returns>
        public static DataTable ExcelToTable(string Path)
        {
            Microsoft.Office.Interop.Excel.Application Exl =
                new Microsoft.Office.Interop.Excel.Application();
            //Provider=Microsoft.Jet.Oledb.4.0;Data Source='database';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source='" + Path + "';" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");
            return ds.Tables[0];
        }

        /// <summary>
        /// 将DataTable写入Excel文档 ,注意需要有模板文件
        /// </summary>
        /// <param name="Path">文件名称</param>
        public static bool SaveToExcel(string Path, DataTable DT)
        {
            try
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source='" + Path + "';" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                for (int i = 0; i < DT.Rows.Count - 1; i++)
                {
                    cmd.CommandText = "INSERT INTO [sheet1$] (姓名,学号,性别,出生年月,...) VALUES('"
                        + DT.Rows[i]["姓名"].ToString() + "','"
                        + DT.Rows[i]["学号"].ToString() + "','"
                        + DT.Rows[i]["性别"].ToString() + "','"
                        + ((DateTime)DT.Rows[i]["出生年月"]).ToShortDateString()

                        ... ... + "')";
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
                return true;
            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
            }
            return false;
        }

        /// <summary>
        /// 无模板方式导出,需要添加Office引用
        /// </summary>
        /// <param name="DT">要导出的内存表</param>
        public static void ExportExcel(DataTable DT)
        {
            try
            {
                Microsoft.Office.Interop.Excel.Application excel;
                Microsoft.Office.Interop.Excel._Workbook xBk;
                Microsoft.Office.Interop.Excel._Worksheet xSt;
                excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                xBk = excel.Workbooks.Add(true);
                xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;
                excel.Columns.ColumnWidth = 10;
                for (int i = 0; i < DT.Columns.Count; i++)
                {
                    excel.Cells[1, i + 1] = DT.Columns[i].Caption;
                }
                for (int i = 0; i < DT.Rows.Count; i++)
                {
                    for (int j = 0; j < DT.Columns.Count; j++)
                    {
                        if (DT.Rows[i][j] is DateTime)
                        {
                            excel.Cells[2 + i, j + 1] = ((DateTime)DT.Rows[i][j]).ToShortDateString();
                        }
                        else
                        {
                            excel.Cells[2 + i, j + 1] = DT.Rows[i][j].ToString();
                        }
                    }
                }
                excel.Visible = true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
            }
        }