.Net实现Excel的导入与导出

一、导出Excel

 <span style="white-space:pre">	</span>public void CreateExcel(DataTable dt, string FileName)
        {
            HttpResponse resp;
            resp = Page.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            string colHeaders = "", ls_item = "";
            //定义表对象与行对象，同时用DataSet对其值进行初始化
             DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int i = 0;
            int cl = dt.Columns.Count;
            //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
            for (i = 0; i < cl; i++)
            {
                if (i == (cl - 1))//最后一列，加n
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\n";
                }
                else
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\t";
                }
            }
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息
            //逐行处理数据　
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据　　
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加n
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }
                resp.Write(ls_item);
                ls_item = "";
            }
            resp.End();
            
        }


        protected void Button1_Click(object sender, EventArgs e)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select name from t");
            string name = "" + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Hour.ToString() + "_" + DateTime.Now.Minute.ToString() + "_" + DateTime.Now.Second.ToString() + ".xls";
             CreateExcel(dt,name);
        }

二、导入Excel

 /// <summary>
        /// 上传文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button1_Click(object sender, EventArgs e)
        {

            string fileName = fileId.FileName;
            string savePath = Server.MapPath("~/file/");
            FileOperatpr(fileName, savePath);
            fileId.SaveAs(savePath + fileName);
            DataOperator(fileName, savePath);
        }
        /// <summary>
        /// 数据操作
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="savePath"></param>
        private void DataOperator(string fileName, string savePath)
        {
            //string myString = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =  " + savePath + fileName + ";Extended Properties=Excel 8.0;";此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
            string myString = "Provider=Microsoft.Ace.OleDb.12.0; Data Source =" + savePath + fileName + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
            // "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
            // "IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 

            OleDbConnection oconn = new OleDbConnection(myString);
            oconn.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", oconn);
            oda.Fill(ds);
            oconn.Close();
            DataSetOperator(ds, savePath + fileName);
        }
        /// <summary>
        /// 数据集操作
        /// </summary>
        /// <param name="ds"></param>
        private void DataSetOperator(DataSet ds, string filePath)
        {
            //SqlConnection conn = new SqlConnection("server=.\\sqlexpress;database=HuaGongDB;User Id=sa;Password=sasa");
            SqlConnection conn = null;
            conn = SqlHelper.OpenConnection();
            SqlTransaction str = conn.BeginTransaction();//利用事务处理 防止中断
            int k = 0;
            if (ds.Tables[0].Rows.Count < 1)
            {
                Response.Write("<script >alert('没有数据！')</script>");
                // --></mce:script>");
                return;
            }
            try
            {
                string s = "";
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    //string sqlStr = "insert into IntoExcel(Tname,Tage,Taddress) values";
                    //sqlStr +="('"+ ds.Tables[0].Rows[i][0].ToString()+"',";
                    //sqlStr += ds.Tables[0].Rows[i][1].ToString()+",";
                    //sqlStr +="'" +ds.Tables[0].Rows[i][2].ToString()+"')";
                    s += ds.Tables[0].Rows[i][0].ToString();
                    this.Label1.Text = s;
                    string sqlStr = "insert into t values";
                    sqlStr += "('" + ds.Tables[0].Rows[i][0].ToString() + "')";
                    sqlStr += ds.Tables[0].Rows[i][1].ToString() + ",";
                    sqlStr += "'" + ds.Tables[0].Rows[i][2].ToString() + "')";
                    SqlCommand cmd = new SqlCommand(sqlStr, conn, str);
                    cmd.Transaction = str;
                    k += cmd.ExecuteNonQuery();
                }
                str.Commit();
            }
            catch (Exception ex)
            {
                Response.Write("发生异常,数据已回滚/n信息/n" + ex.Message);
                str.Rollback();
            }
            finally
            {
                Response.Write("上传成功" + k + "条");
                File.Delete(filePath);
            }
        }
        /// <summary>
        /// 文件操作
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="savePath"></param>
        private void FileOperatpr(string fileName, string savePath)
        {
            if (!Directory.Exists(savePath))
            {
                Directory.CreateDirectory(savePath);
            }
            if (File.Exists(savePath + fileName))
            {
                File.Delete(savePath + fileName);
            }
        }