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);        }    }}