一些Excel的相关操作

//Excel导入到数据库（sql/oracle）和Grid/DataTable导出Excel

    /// <summary>
    /// 将Excel文件数据批量倒入到数据库—SQL
    /// </summary>
    private void BatchUploadSql()
    {
        string myString = "provider = microsoft.jet.oledb.4.0; data source = E:/Workaround/TestItem/BatchExcel/test.xls;extended properties=excel 8.0";

        OleDbConnection cnnXls = new OleDbConnection(myString);
        OleDbDataAdapter myAdapter = new OleDbDataAdapter("select * from [sheet1$]", cnnXls);

        DataSet myDs = new DataSet();
        myAdapter.Fill(myDs);

        if (myDs.Tables[0].Rows.Count > 0)
        {
            string strSql = string.Empty;

            /*注意：cnnString不能有空格*/
            string cnnString = "provider=SqlOleDb;server=pwdpc;database=Northwind;uid=sa;pwd=123456Aa";

            OleDbConnection conn = new OleDbConnection(cnnString);
            conn.Open();

            OleDbCommand myCmd = null;

            for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)
            {
                strSql = string.Format("insert into Customers(CustomerID, CompanyName, ContactName) values ( ");
                strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "'" + ", ";
                strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "'" + ", ";
                strSql += "'" + myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "'" + ")";

                try
                {
                    myCmd = new OleDbCommand(strSql, conn);
                    myCmd.ExecuteNonQuery();
                    Response.Write(" ");
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            conn.Close();
        }
    }

    /// <summary>
    /// 将Excel文件数据批量倒入到数据库—ORACLE
    /// </summary>
    private void BatchExcelOracle()
    {
        if (System.IO.File.Exists(uploadFile.Value.ToString()))
        {
            string[] fileName = uploadFile.Value.ToString().Split(Convert.ToChar("."));
            string fileType = fileName[fileName.Length - 1];

            if (fileType.ToUpper() != "XLS")
            {
                Response.Write(" ");
            }
            else
            {
                string strConnUpload = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + uploadFile.Value.ToString().Replace("\\", "\\\\") + "; Extended Properties=Excel 8.0";

                OleDbConnection connUpload = new OleDbConnection(strConnUpload);
                connUpload.Open();

                string strSQL = "SELECT * FROM [Sheet1$]";
                OleDbDataAdapter daUpload = new OleDbDataAdapter(strSQL, connUpload);

                DataSet dsUpload = new DataSet();
                daUpload.Fill(dsUpload, "[Sheet1$]");

                /*注意:data source 对大小写敏感*/
                string strConn = "data source=ORACLE;user id=pwd;password=123456Aa;Min Pool Size=1";
                OracleConnection conn = new OracleConnection(strConn);
                conn.Open();

                int isSuccess = 0;
                int isFail = 0;

                OracleCommand cmd = null;

                for (int i = 0; i < dsUpload.Tables["[Sheet1$]"].Rows.Count; i++)
                {
                    string tempStr = string.Format("insert into MYTABLE(ID, Name, Sex) values ( ");
                    tempStr += dsUpload.Tables[0].Rows[i].ItemArray[0].ToString() + ", ";
                    tempStr += "'" + dsUpload.Tables[0].Rows[i].ItemArray[1].ToString() + "'" + ", ";
                    tempStr += "'" + dsUpload.Tables[0].Rows[i].ItemArray[2].ToString() + "'" + ")";

                    try
                    {
                        cmd = new OracleCommand(tempStr, conn);
                        cmd.ExecuteNonQuery();
                        isSuccess++;
                    }
                    catch
                    {
                        isFail++;
                    }
                }

                string message = "全部上传成功";
                if (isFail != 0)
                {
                    message = "成功" + isSuccess + "条，失败" + isFail + "条。";
                }

                conn.Close();
                connUpload.Close();

                Response.Write(" ");
            }
        }
        else
        {
            Response.Write(" ");
        }
    }

   /// <summary>
   /// 将Grid导出到Excel
   /// </summary>
   /// <param name="ctl"></param>
   /// <param name="FileName"></param>
    public static void ToExcel(System.Web.UI.Control ctl, string FileName)
    {
        HttpContext.Current.Response.Charset = "UTF-8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
        HttpContext.Current.Response.ContentType = "application/ms-excel";
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName + ".xls");
        ctl.Page.EnableViewState = false;
        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        ctl.RenderControl(hw);
        HttpContext.Current.Response.Write(tw.ToString());
        HttpContext.Current.Response.End();
    }

    /// <summary>
   /// 将DataTable中的数据导出到指定的Excel文件中
   /// </summary>
    /// Web页面对象
    /// 包含被导出数据的DataTable对象
    /// Excel文件的名称
    public static void Export(System.Web.UI.Page page, System.Data.DataTable dt, string FileName)
    {
        System.Web.HttpResponse httpResponse = page.Response;
        System.Web.UI.WebControls.DataGrid dataGrid = new System.Web.UI.WebControls.DataGrid();

        dataGrid.DataSource = dt.DefaultView;
        dataGrid.AllowPaging = false;
        dataGrid.HeaderStyle.BackColor = System.Drawing.Color.AliceBlue;
        dataGrid.HeaderStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
        dataGrid.HeaderStyle.Font.Bold = true;
        dataGrid.DataBind();

        System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName + ".xls");
        httpResponse.ContentEncoding = System.Text.Encoding.Default;
        httpResponse.ContentType = "application/ms-excel";

        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        dataGrid.RenderControl(hw);

        System.Web.HttpContext.Current.Response.Write(tw.ToString());
        System.Web.HttpContext.Current.Response.End();
    }

    首先建立一个EXCEL的模板放在服务器上，当用户将数据导出到EXCEL的时候复制一份模版的副本，并将导出的数据写到这个Excel里，这样在服务器端就有一个供用户下载的纯正Excel的文件了。用户下载之后将数据填写完毕上传，首先上传到服务器，然后再去读取这个文件的数据，避免读客户端数据引发不必要的麻烦。

   // ///
   // /// 复制Excel模板并向Excel中写入数据
   // ///
   // ///
   // private void WriteDataToExcel(DataTable dt)
   // {
   //     int isSuccess = 0;
   //     int isFail = 0;

   //     try
   //     {
   //         //复制excel文件          
   //         string TmpFileForDownLoad = UpLoadFilePath + Guid.NewGuid().ToString() + ".xls";//新生成的文件地址
   //         string TemplateFile = Server.MapPath("../BatchTemplate/"
   //             + ConfigInfo.GetConfigValue("TowerAdminAreaTmpName"));//用于复制的文件地址

   //         System.IO.File.Copy(TemplateFile, TmpFileForDownLoad, true);

   //         String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
   //             + TmpFileForDownLoad
   //             + ";Extended Properties=Excel 8.0;";

   //         //写入数据
   //         OleDbCommand cmd = new OleDbCommand(string.Empty, new OleDbConnection(strConn));
   //         cmd.Connection.Open();

   //         for (int i = 0; i < dt.Rows.Count; i++)
   //         {
   //             try
   //             {
   //                 cmd.CommandText = string.Format("insert into [Sheet1$] ({0},{1},{2},{3},{4}) " +
   //                     "values('{5}', '{6}', '{7}', '{8}', '{9}')",
   //                     "塔标识", "塔号", "区县", "乡镇", "村庄"
   //                     , dt.Rows[i]["TOWER_ID"].ToString()
   //                     , dt.Rows[i]["TOWER_CODE"].ToString()
   //                     , dt.Rows[i]["COUNTY"].ToString()
   //                     , dt.Rows[i]["TOWN"].ToString()
   //                     , dt.Rows[i]["VILLAGE"].ToString());
   //                 cmd.ExecuteNonQuery();
   //                 isSuccess++;
   //             }
   //             catch
   //             {
   //                 isFail++;
   //             }
   //         }
   //         cmd.Connection.Close();

   //         DownLoadExcel(TmpFileForDownLoad);
   //     }
   //     catch (Exception ex)
   //     {
   //         throw ex;
   //     }
   // }

   // ///
   // /// 使用Excel对象下载Excel文件
   // ///
   // /// 服务器端文件路径
   // public void DownLoad(string serverPath)
   // {
   //     try
   //     {
   //         Excel.Application xlApp = new Excel.Application();
   //         object missing = System.Reflection.Missing.Value;

   //         Excel.Workbook xlBook = xlApp.Workbooks.Open(serverPath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
   //         Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];

   //         //下载到本地的文件路径及文件名
   //         string fileName = "c:/EXCEL对象下载的文件.xls";
   //         xlSheet.SaveAs(fileName, missing, missing, missing, missing, missing, missing, missing, missing);

   //         if (fileName == string.Empty)
   //             return;
   //         Excel.Application NewApp = new Excel.Application();
   //         Excel.Workbook NewBook;
   //         Excel.Worksheet NewSheet;

   //         NewBook = NewApp.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
   //         NewSheet = (Excel.Worksheet)NewBook.Worksheets[1];

   //         NewApp.Visible = false;

   //         //关闭EXCEL工作台
   //         if (!NewApp.Visible)
   //         {
   //             object missing1 = System.Reflection.Missing.Value;

   //             xlBook.Close(missing1, missing1, missing1);
   //             NewBook.Close(missing1, missing1, missing1);
   //         }

   //         Response.Write(" ");
   //     }
   //     catch (Exception ex)
   //     {
   //         Response.Write(" ");
   //     }
   // }
   // ///
   // /// 使用WebClient方式下载Excel文件
   // ///
   // private void DownLoad1(string serverPath)
   // {
   //     System.Net.WebClient wc = new System.Net.WebClient();

   //     wc.DownloadFile(serverPath, @"c:\WebClient下载的文件.xls");
   // }
   // ///
   // /// 使用输出流的方式下载Excel文件
   // ///
   // private void DownLoad2(string serverPath)
   // {
   //     System.IO.FileInfo file = new System.IO.FileInfo(serverPath);
   //     Response.Clear();
   //     Response.AddHeader("Content-Disposition", "attachment;   filename=" + file.Name);

   //     Response.AddHeader("Content-Length", file.Length.ToString());

   //     Response.ContentType = "application/octet-stream";

   //     //或动态产生数据后写入Response.OutputStream  
   //     Response.WriteFile(file.FullName);

   //     Response.End();
   // }

   /////
   // /// 将Excel文件数据批量倒入到数据库
   // ///
   // private void BatchExcelOracle(HtmlInputFile hifFile)
   // {
   //     string clientPath = hifFile.Value;

   //     string[] fileName = clientPath.Split(Convert.ToChar("."));
   //     string fileType = fileName[fileName.Length - 1];

   //     if (fileType.ToUpper() != "XLS")
   //     {
   //         string message = "只能录入数据源只能是XLS格式的文件！";
   //         string url = string.Format("TowerAdminArea_List.aspx?Operation=ReadOnly") + UrlForNextPage();
   //         CommonMethod.RedirectPage(message, url, 0);
   //     }
   //     else
   //     {
   //         string NewFileName = UpLoadFilePath + Guid.NewGuid().ToString() + ".xls";//新生成的文件地址
   //         //上传文件
   //         hifFile.PostedFile.SaveAs(NewFileName);

   //         string strConnUpload = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + NewFileName + "; Extended Properties=Excel 8.0";

   //         OleDbConnection connUpload = new OleDbConnection(strConnUpload);
   //         connUpload.Open();

   //         string strSQL = "SELECT * FROM [Sheet1$]";
   //         OleDbDataAdapter daUpload = new OleDbDataAdapter(strSQL, connUpload);

   //         DataSet dsUpload = new DataSet();
   //         daUpload.Fill(dsUpload, "[Sheet1$]");

   //         int isSuccess = 0;
   //         int isFail = 0;

   //         for (int i = 0; i < dsUpload.Tables["[Sheet1$]"].Rows.Count; i++)
   //         {
   //             int towerID = Convert.ToInt32(dsUpload.Tables[0].Rows[i].ItemArray[0]);

   //             Tower towerObj = new Tower(towerID);
   //             if (towerObj.ProjectID != _mProjectId)
   //             {
   //                 isFail++;
   //                 break;
   //             }

   //             towerObj.County = dsUpload.Tables[0].Rows[i].ItemArray[2].ToString().Trim();
   //             towerObj.Town = dsUpload.Tables[0].Rows[i].ItemArray[3].ToString().Trim();
   //             towerObj.Village = dsUpload.Tables[0].Rows[i].ItemArray[4].ToString().Trim();

   //             try
   //             {
   //                 towerObj.Update();
   //                 isSuccess++;
   //             }
   //             catch (Exception ex)
   //             {
   //                 throw ex;
   //                 isFail++;
   //             }
   //         }

   //         string message = "全部上传成功，共" + isSuccess + "条。";
   //         if (isFail != 0)
   //         {
   //             message = "成功" + isSuccess + "条，失败" + isFail + "条。";
   //         }

   //         connUpload.Close();

   //         string url = string.Format("TowerAdminArea_List.aspx?Operation=ReadOnly") + UrlForNextPage();
   //         CommonMethod.RedirectPage(message, url, 0);
   //     }
   // }