Sign in
Microsoft.com
 
Microsoft
 
 
 

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

 > 

  • 問題

  • Question
    登入以投票
    0
    登入以投票

    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

所有回覆

  • Question
    登入以投票
    0
    登入以投票

    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
  • Question
    登入以投票
    0
    登入以投票

    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