locked
A problem with export data from DataTable to Excel RRS feed

  • 問題

  • Hi everyone,

     

    I'm Florin G and my problem is:

    I want to write into an Excel file using Odbc.

     

    So my code is:

     

    OdbcConnection    dbExcel;

    OdbcDataAdapter  daDate;

    DataTable             dtDate;

    string                   strConectExcel;

    string                   strComSql;

     

    strConectExcel = String.Format("Driver={{Microsoft Excel Driver (*.xls)}}; DBQ={0}\\Test2.xls;",

    Directory.GetCurrentDirectory());

    dbExcel = new OdbcConnection(strConectExcel);

    try

    {

    dbExcel.Open();

    }

    catch(OdbcException exc)

    {

    MessageBox.Show(exc.Message);

    }

     

    strComSql = String.Format("SELECT * FROM [Sheet1$]");

    daDate = new OdbcDataAdapter(strComSql, dbExcel);

    new OdbcCommandBuilder(daDate);

    dtDate = new DataTable("DateExcel");

    try

    {

    daDate.Fill(dtDate);

    }

    catch (OdbcException exc)

    {

    MessageBox.Show(exc.Message);

    }

     

    //Here I want to write something in dtDate and then to update in Excel file

     

     

    daDate.Update(dtDate);

     

    dbExcel.Close();

     

     

    I don't know how to write in the Excel file.

    Please help me!

     

    Maybe with .InsertCommand or .UpdateCommand I don't know!

     

     

    Thank you very much!

     

    2008年12月3日 13:32

所有回覆

  • Check if this is what you want~

    /// <summary>
    /// 寫入Excel
    /// </summary>
    /// <param name="table"></param>
    /// <param name="st"></param>
    private void ExportToExcel(ref DataTable table, ref String st)//匯出Excel方法
    {
     string file = Request.PhysicalApplicationPath + @"Modules\MemberRole\excel\UsersProfile.xls";
     if (File.Exists(file))
     {
      try
      {
       File.Delete(file);
      }
      catch (Exception ex)
      {
       MessageAlert.Show(this.Page, ex.Message.ToString());
      }
     }
    
     string fileName = file.Replace("\\", "\\\\");
     string conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=YES\"";
     OleDbConnection oleCon = new OleDbConnection();
     oleCon.ConnectionString = conn;
     OleDbCommand oleCmd = new OleDbCommand();
     oleCmd.Connection = oleCon;
     oleCon.Open();
    
     try
     {
      StringBuilder sb = new StringBuilder(@"CREATE TABLE " + "UsersProfile " + st);
      oleCmd.CommandText = sb.ToString();
      oleCmd.ExecuteNonQuery();
    
      for (int i = 0; i < table.Rows.Count; ++i)
      {
       sb = new StringBuilder(@"INSERT INTO " + "UsersProfile " + "VALUES('");
    
       for (int j = 0; j < table.Columns.Count; ++j)
       {
        sb.Append(table.Rows[i][j].ToString() + "','");
       }
    
       oleCmd.CommandText = sb.ToString().Substring(0, sb.ToString().Length - 2) + ")";
       oleCmd.ExecuteNonQuery();
      }
    
      oleCon.Close(); //需先結束連線再轉存資料,否則會出現"檔案正由另一個程序使用"的錯誤訊息。
    
      //將建好的 Excel 下載到 Client Side。
      Response.ContentType = "Application/vnd.ms-excel";
      Response.AddHeader("Content-Disposition", "attachment; filename=UsersProfile.xls");
      string Path = MapPath(@"~\Modules\MemberRole\excel\UsersProfile.xls");
      Response.Buffer = true;
      Response.WriteFile(Path);
      Response.End();
     }
     catch (Exception ex)
     {
      MessageAlert.Show(this.Page, ex.Message.ToString());
     }
    }
    

    小中中的學習筆記
    2010年7月24日 6:26
  • Hi Florin,

    Maybe you could try to use this library, which could export DataTable to Excel XLSX or PDF.

    http://sharechiwai.blogspot.com/2010/07/net-export-datatable-to-excel-xlsx-pdf.html

    Hope you find it useful.

     

    Please forgive me if I mis-understood your questions.


    Chi
    2010年7月24日 23:48