Excel + SQL Server 导入导出类

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web.UI.WebControls;
using System;
using System.Linq;
using System.Web;




namespace CableDB.Web.admin.Experiment.DB
{
    public class ExcelHelper
    {
        #region 将DataTable 写入数据库
        /// <summary>
        /// 事物插入到数据库中
        /// </summary>
        /// <param name="sqlConn">数据库连接字符串</param>
        /// <param name="dt">保存数据的DataTable</param>
        /// <param name="tableName">目标数据表的表名</param>
        /// <returns></returns>
        public static bool DataTableToDB(string sqlConn, DataTable dt, string tableName, )
        {
            using (SqlConnection conn = new SqlConnection(sqlConn))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }


                using (SqlBulkCopy sqlCopy = new SqlBulkCopy(conn))
                {
                    sqlCopy.DestinationTableName = tableName;
                    sqlCopy.BulkCopyTimeout = 180;


                    try
                    {
                        sqlCopy.WriteToServer(dt);


                    }
                    catch (System.Exception ex)
                    {
                        return false;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }


            return true;
        }
        #endregion


        #region
        /// <summary>
        /// 从Excel读入数据到DataSet
        /// </summary>
        /// <param name="strFilePath">Excel文件路径</param>
        /// <returns>返回Excel转化为的DataSet</returns>
        public static DataSet ExcelToDataSet(string strFilePath)
        {
            string strConn = "";
            if (System.IO.Path.GetExtension(strFilePath).Equals(".xls"))
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            }
            else if (System.IO.Path.GetExtension(strFilePath).Equals(".xlsx"))
            {
                strConn = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + strFilePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
            }
            else
            {
                return null;
            }
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//获得Excel中的所有sheetname
            OleDbDataAdapter odda;
            DataSet ds = new DataSet();
            foreach (DataRow dr in sheetNames.Rows)
            {
                DataSet dsOne = new DataSet();
                odda = new OleDbDataAdapter("select * from [" + dr[2] + "]", strConn);//dr[2] is sheetname
                odda.Fill(dsOne);
                if (dsOne.Tables.Count > 0)
                {
                    DataTable dt = dsOne.Tables[0].Copy();
                    dt.TableName = dr[2].ToString();
                    ds.Tables.Add(dt);
                }
            }
            conn.Close();
            return ds;
        }
        #endregion






        #region 将 DataSet 导入 Excel
        /// <summary>
        /// This method takes DataSet as input paramenter and it exports the same to excel
        /// </summary>
        /// <param name="ds"></param>
        public static void DataSetToExcel(DataSet ds, string strFileName)
        {
            //Creae an Excel application instance
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();


            //Create an Excel workbook instance and open it from the predefined location
            Microsoft.Office.Interop.Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(System.Reflection.Missing.Value);


            foreach (DataTable table in ds.Tables)
            {
                //Add a new worksheet to workbook with the Datatable name
                Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
                excelWorkSheet.Name = table.TableName;


                for (int i = 1; i < table.Columns.Count + 1; i++)
                {
                    excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
                }


                for (int j = 0; j < table.Rows.Count; j++)
                {
                    for (int k = 0; k < table.Columns.Count; k++)
                    {
                        excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                    }
                }
            }
            excelWorkBook.SaveAs(strFileName);
            excelWorkBook.Close();
            excelApp.Quit();


        }
        #endregion




        /// <summary>
        /// 执行SQL语句，将结果保存至DataSet中，然后返回。
        /// </summary>
        /// <param name="strConn">数据库连接字符串</param>
        /// <param name="strSQL">SQL语句</param>
        /// <returns>SQL执行结果</returns>
        public static DataSet DBToDataSet(string strConn, string strSQL)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {


                DataSet ds = new DataSet();
                try
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();


                    SqlCommand cmd = new SqlCommand(strSQL, conn);


                    using (SqlDataAdapter adpter = new SqlDataAdapter(cmd))
                    {
                        adpter.Fill(ds);
                    }
                }
                catch (Exception exp)
                {
                    CableDB.Common.Log.OutputError(exp.ToString());
                }


                return ds;
            }
        }




        /// <summary>
        /// 客户端下载Excel文件，同时删除服务器上的文件。
        /// </summary>
        /// <param name="Response"></param>
        /// <param name="strFileName">Excel文件路径</param>
        public static void DownloadExcel(HttpResponse Response, string strFileName)
        {
            //打开要下载的文件，并把该文件存放在FileStream中      
            System.IO.FileStream Reader = System.IO.File.OpenRead(strFileName);
            //文件传送的剩余字节数：初始值为文件的总大小            
            long Length = Reader.Length;
            Response.Buffer = false;
            Response.AddHeader("Connection", "Keep-Alive");
            Response.ContentType = "application/octet-stream";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
            Response.AddHeader("Content-Length", Length.ToString());
            byte[] Buffer = new Byte[10000];    //存放欲发送数据的缓冲区            
            int ByteToRead;              //每次实际读取的字节数            
            while (Length > 0)
            {
                //剩余字节数不为零，继续传送               
                if (Response.IsClientConnected)
                {                //客户端浏览器还打开着，继续传送                    
                    ByteToRead = Reader.Read(Buffer, 0, 10000); //往缓冲区读入数据                    
                    Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器
                    Response.Flush();   //立即写入客户端                  
                    Length -= ByteToRead;   //剩余字节数减少                
                }
                else
                {                //客户端浏览器已经断开，阻止继续循环                   
                    Length = -1;
                }
            }
            //关闭该文件            
            Reader.Close();
            if (System.IO.File.Exists(strFileName))
                System.IO.File.Delete(strFileName);
        }
    }
}