DataTable 정보를 엑셀로 저장하기

 

 

MSSQL GETDATE()  CONVERT 변환DataTable 정보를 엑셀로 저장하기

 

 

public bool SaveExcelData(string path, DataTable table, bool bTitle)
  {
   bool result = true;
   OleDbConnection conn = null;

   try
   {
    OleDbCommand cmmd = null;
    string connectString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"Excel 8.0;HDR=NO\"", path);
    conn = new OleDbConnection(connectString);
    conn.Open();



    // 테이블 생성
    StringBuilder sQryField = new StringBuilder();
    StringBuilder sQryValue = new StringBuilder();
    int cnt = 0;
    foreach (DataColumn column in table.Columns)
    {
     DataRow row = null;
     if (table.Rows.Count > 0) row = table.Rows[0];

     if (sQryField.Length > 0) sQryField.Append(",");
     sQryField.Append("[" + column.ColumnName.Replace("'", "''") + "] CHAR(255)");
    }
    cmmd = new OleDbCommand("CREATE TABLE Sheet1 (" + sQryField.ToString() + ")", conn);
    cmmd.ExecuteNonQuery();



    // 데이터 넣기
    for (int i = 0; i < table.Rows.Count; i++)
    {
     DataRow row = table.Rows[i];
     sQryField = new StringBuilder();
     sQryValue = new StringBuilder();
     cnt = 0;
     foreach (DataColumn column in table.Columns)
     {
      if (sQryField.Length > 0)
      {
       sQryField.Append(",");
       sQryValue.Append(",");
      }

      sQryField.Append("F" + (++cnt).ToString());
      sQryValue.Append("'" + row[column.ColumnName].ToString().Replace("'", "''") + "'");
     }

     cmmd = new OleDbCommand("INSERT INTO [Sheet1$] (" + sQryField.ToString() + ") VALUES(" + sQryValue.ToString() + ")", conn);
     cmmd.ExecuteNonQuery();
    }
   }
   catch (Exception)
   {
    result = false;
   }
   finally
   {
    if (conn != null) conn.Close();
   }

   return result;
  }

 

public bool SaveExcelData(string path, DataTable table, bool bTitle)
  {
   bool result = true;
   OleDbConnection conn = null;

   try
   {
    OleDbCommand cmmd = null;
    string connectString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"Excel 8.0;HDR=NO\"", path);
    conn = new OleDbConnection(connectString);
    conn.Open();



    // 테이블 생성
    StringBuilder sQryField = new StringBuilder();
    StringBuilder sQryValue = new StringBuilder();
    int cnt = 0;
    foreach (DataColumn column in table.Columns)
    {
     DataRow row = null;
     if (table.Rows.Count > 0) row = table.Rows[0];

     if (sQryField.Length > 0) sQryField.Append(",");
     sQryField.Append("[" + column.ColumnName.Replace("'", "''") + "] CHAR(255)");
    }
    cmmd = new OleDbCommand("CREATE TABLE Sheet1 (" + sQryField.ToString() + ")", conn);
    cmmd.ExecuteNonQuery();



    // 데이터 넣기
    for (int i = 0; i < table.Rows.Count; i++)
    {
     DataRow row = table.Rows[i];
     sQryField = new StringBuilder();
     sQryValue = new StringBuilder();
     cnt = 0;
     foreach (DataColumn column in table.Columns)
     {
      if (sQryField.Length > 0)
      {
       sQryField.Append(",");
       sQryValue.Append(",");
      }

      sQryField.Append("F" + (++cnt).ToString());
      sQryValue.Append("'" + row[column.ColumnName].ToString().Replace("'", "''") + "'");
     }

     cmmd = new OleDbCommand("INSERT INTO [Sheet1$] (" + sQryField.ToString() + ") VALUES(" + sQryValue.ToString() + ")", conn);
     cmmd.ExecuteNonQuery();
    }
   }
   catch (Exception)
   {
    result = false;
   }
   finally
   {
    if (conn != null) conn.Close();
   }

   return result;
  }