简单Excel导出与导入

   /// <summary>
    /// DataTableToExcel 的摘要说明
    /// </summary>
    public class DataTableToExcel
    {
        /// <summary>
        /// 由 DataSet 导出 Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="FileName"></param>      
        public static void DataTableToExcelAndDownload(System.Data.DataTable dt, string FileName)
        {
            HttpResponse resp = HttpContext.Current.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);      
            string colHeaders = "", ls_item = "";
            int i = 0;

            DataRow[] myRow = dt.Select("");

            //取得数据表各列标题，各标题之间以/t分割，最后一个列标题后加回车符
            for (i = 0; i < dt.Columns.Count; i++)
            {
                if (i == dt.Columns.Count - 1)
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "/n";
                }
                else
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "/t";
                }
            }
            resp.Write(colHeaders);

            foreach (DataRow row in myRow)
            {
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    if (i == dt.Columns.Count - 1)
                    {
                        ls_item += row[i].ToString() + "/n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "/t";
                    }
                }

                resp.Write(ls_item);
                ls_item = "";
            }

            resp.End();
        }

        /// <summary>
        /// 由 DataSet 导出 Excel ，自定义字段名
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="FileName"></param>      
        public static void DataTableToExcelAndDownload(System.Data.DataTable dt, string FileName,string[] cells)
        {
            HttpResponse resp = HttpContext.Current.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            string colHeaders = "", ls_item = "";
            int i = 0;

            DataRow[] myRow = dt.Select("");

            //取得数据表各列标题，各标题之间以/t分割，最后一个列标题后加回车符
            for (i = 0; i < cells.Length; i++)
            {
                if (i == cells.Length - 1)
                {
                    colHeaders += cells[i].ToString() + "/n";
                }
                else
                {
                    colHeaders += cells[i].ToString() + "/t";
                }
            }
            resp.Write(colHeaders);

            foreach (DataRow row in myRow)
            {
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    if (i == dt.Columns.Count - 1)
                    {
                        ls_item += row[i].ToString() + "/n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "/t";
                    }
                }

                resp.Write(ls_item);
                ls_item = "";
            }

            resp.End();
        }

        /// <summary>
        /// 下载服务器端的文件到本地
        /// </summary>
        /// <param name="_Request"> </param>
        /// <param name="_Response"> </param>
        /// <param name="_fileName"> 目的文件名称 </param>
        /// <param name="_fullPath"> 源文件路径 </param>
        /// <param name="_speed"> 速度大小(1024000 -> 10k/s) </param>       
        public static bool DownloadFile(HttpRequest Request, HttpResponse Response,string fileName,string fullPath, long speed)
        {           
            try
            {
                FileStream myFile = new FileStream(fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                BinaryReader br = new BinaryReader(myFile);
                try
                {
                    Response.AddHeader("Accept-Ranges", "bytes");
                    Response.Buffer = false;
                    long fileLength = myFile.Length;
                    long startBytes = 0;

                    double pack = 10240; //10K bytes
                    //int sleep = 200;   //每秒5次   即5*10K bytes每秒
                    int sleep = (int)Math.Floor(1000 * pack / speed) + 1;
                    if (Request.Headers["Range"] != null)
                    {
                        Response.StatusCode = 206;
                        string[] range = Request.Headers["Range"].Split(new char[] { '=', '-' });
                        startBytes = Convert.ToInt64(range[1]);
                    }
                    Response.AddHeader("Content-Length", (fileLength - startBytes).ToString());
                    if (startBytes != 0)
                    {
                        //Response.AddHeader("Content-Range", string.Format(" bytes {0}-{1}/{2}", startBytes, fileLength-1, fileLength));
                    }
                    Response.AddHeader("Connection", "Keep-Alive");
                    Response.ContentType = "application/octet-stream";
                    Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.GetEncoding("GB2312")));

                    br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
                    int maxCount = (int)Math.Floor((fileLength - startBytes) / pack) + 1;

                    for (int i = 0; i < maxCount; i++)
                    {
                        if (Response.IsClientConnected)
                        {
                            Response.BinaryWrite(br.ReadBytes(int.Parse(pack.ToString())));
                            Thread.Sleep(sleep);
                        }
                        else
                        {
                            i = maxCount;
                        }
                    }
                }
                catch
                {
                    return false;
                }
                finally
                {
                    br.Close();

                    myFile.Close();
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        //*********************** Excel 导入功能 ***************************//

        /// <summary>
        /// 读取Excel文档中的数据到内存中
        /// </summary>
        /// <param name="Path"> 文件名称 </param>
        /// <returns> 返回一个数据集 </returns>
        public static DataSet ExcelToDataSet(string Path)
        {
            DataSet ds = new DataSet();

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";

            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;

            strExcel = "select * from [Sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            myCommand.Fill(ds);

            return ds;
        }

        /// <summary>
        /// 将DataSet中的数据插入到SqlServer中
        /// </summary>
        /// <param name="ds"> 结果集 </param>
        /// <param name="TableName"> 表名 </param>
        /// <param name="MasterID"> 订单主表ID </param>
        public static void DataSetToSqlServer(DataSet ds,string TableName,string MasterID)
        {
            DataTable dt = new DataTable();
            long ID = Shove._Convert.StrToLong(MasterID,0);     //主表ID

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string GoodNo = dt.Rows[i][0].ToString();
                    string GoodNorm = dt.Rows[i][1].ToString();
                    int Amount = Shove._Convert.StrToInt(dt.Rows[i][2].ToString(),0);

                    DAL.Procedures.P_DataTableToSqlServer(TableName, ID, GoodNo, GoodNorm, Amount);
                }
            }
        }

    }