Write Datatable to excel [C#2005]

I hope, you have the at least excel 2003.
Here is some tipps for excel using
http://www.c-sharpcorner.com/UploadFile/thiagu304/ExcelAutomation01052007080910AM/ExcelAutomation.aspx

Thanks a lot AlexNek. This is not what i am looking for. The link above teaches how to read an excel file. I want to write a datatable or dataset  in excel format.

Do you want to do it direct to the excel format without excel automation?

This is what I have. Within my code I have a datatable. I just want to write that datatable to excel. I could store it in a dataset if that will be easy.

Thanks.

You have some variants to do it.
- convert the database table to tab/comma delemited file and import it to excel.
- read database table row by row or the whole table and use excel automation to write it to excel file.
- read database table row by row or the whole table and write it direct to xls format.
Each way has the plus and the minus.  Which way do you want to use?

Here is one sample
http://www.codeproject.com/csharp/export2excel.asp

Library
http://filehelpers.sourceforge.net/

Very good commercial library
http://www.infragistics.com/dotnet/netadvantage/winforms/wingridexcelexporter.aspx#Overview

You can do this easily with ado. See my comments on this issue:
https://www.experts-exchange.com/questions/22059574/how-to-export-data-to-excel-from-gridview.html

This shows an example on how to do this from a DataGridView but it is the same procedure for exporting a datatable. You just have to change the two loops that reads your data.
- from "foreach (DataGridViewColumn col in dgv.Columns) {" to "foreach (DataColumn col in dgv.Columns) {"
- from "foreach (DataGridViewRow row in dgv.Rows) {" to "foreach (DataRow row in dgv.Rows) {"
- also change input parameter from "DataGridView dgv" to "DataTable dgv"

I have used this angle on multiple projects running in production, with the same requirements that you have. Please reply to this post if you have any problems.

Thanks redpipe. I guess this is what you want me to try. I can use the Datagridview option too so I can display my result to the users.



public void DoExportAdo(DataGridView dgv, string FilePath, ProgressBar prg, string xlSheet)
    {
      string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                          "Data Source=" + FilePath + ";" +
                          "Extended Properties=\"Excel 8.0;HDR=YES\""; //HDR => column headers
      OleDbCommand cmd = new OleDbCommand();
      StringBuilder sbCreate = new StringBuilder();
      StringBuilder sbCols = new StringBuilder();
      StringBuilder sbValues = new StringBuilder();
      string insertString;

      using (OleDbConnection conn = new OleDbConnection(connString)) {
        cmd.Connection = conn;
        foreach (DataGridViewColumn col in dgv.Columns) {
          sbCols.Append(col.HeaderText + ",");
          if (col.DefaultCellStyle.Format.StartsWith("N")) {
            sbCreate.Append(col.HeaderText + " INT, ");
          } else {
            sbCreate.Append(col.HeaderText + " CHAR(255), ");
          }
        }
        cmd.CommandText = "CREATE TABLE " + xlSheet + " (" +
                          StringUtils.removeFromLastChar(sbCreate, ',') + ")";
        if (conn.State != ConnectionState.Open) { conn.Open(); }
        cmd.ExecuteNonQuery();

        insertString = "INSERT INTO " + xlSheet + " (" +
                       StringUtils.ReplaceLast(sbCols.ToString(), ",", ") VALUES (");
        string value;
        foreach (DataGridViewRow row in dgv.Rows) {
          sbValues = new StringBuilder();
          if (row.IsNewRow) { continue; }
          foreach (DataGridViewColumn col in dgv.Columns) {
            if (StringUtils.isEmpty(row.Cells[col.Index].Value)) {
              sbValues.Append("'', ");
            } else {
              value = row.Cells[col.Index].Value.ToString();
              if(StringUtils.isNumeric(value)){
                sbValues.Append(StringUtils.toDbDecimal(value) + ", ");
              } else {
                sbValues.Append("'" + value + "', ");
              }
            }
          }
          cmd.CommandText = insertString +
                            StringUtils.ReplaceLast(sbValues.ToString(), ",", ")");
          if (conn.State != ConnectionState.Open) { conn.Open(); }
          cmd.ExecuteNonQuery();
          if (prg != null) {
            prg.PerformStep();
          }
          Application.DoEvents();
        }
        CloseResources(conn, cmd, null, null);
      }
    }

Yes Sirdots thats the code I presume will help you on your quest. Please comment freely on your experiences...

Thanks redpipe. I have having errors on stringutils and closeResources. Do I need to add a namespace to use this please?

sirdots.

My humble apologies! The missing methods are mine internal helper methods. Paste the code beneath into your code to resolve the problem:

==============================================
private void CloseResources(IDbConnection conn, IDbCommand cmd, IDataReader dr)
    {
      if (cmd != null) {
        cmd.Dispose();
        cmd = null;
      }

      if (dr != null) {
        dr.Close();
        dr.Dispose();
        dr = null;
      }

      if (conn != null) {
        try {
          conn.Close();
          conn.Dispose();
          conn = null;
        } catch (SqlException exSql) {
          Logger.LogException(exSql);
        } catch (OleDbException exOleDb) {
          Logger.LogException(exOleDb);
        }
      }
    }

public static string removeFromLastChar(StringBuilder sb, char splitChar)
    {
      return removeFromLastChar(sb.ToString(), splitChar);
    }

public static string removeFromLastChar(string s, char splitChar)
    {
      return s.Remove(s.LastIndexOf(splitChar));
    }

public static string ReplaceLast(string originalText, string searchPattern, string replacementText)
    {
      int i = originalText.LastIndexOf(searchPattern);
      string end = originalText.Substring(i).Replace(searchPattern, replacementText);
      return originalText.Substring(0, i) + end;
    }

public static bool isEmpty(object o)
    {
      if (o == null) {
        return true;
      } else if (o.GetType() == typeof(string)) {
        return isEmpty(Convert.ToString(o));
      } else {
        return false;
      }
    }

public static bool isEmpty(string s)
    {
      return String.IsNullOrEmpty(s);
    }

    public static bool isNumeric(String s)
    {
      if (isEmpty(s)) {
        return false;
      }
      for (int i = 0; i < s.Length; i++) {
        if (Char.IsDigit(s[i]) == false &&
            Char.IsWhiteSpace(s[i]) == false &&
            s[i] != NumberUtils.DecimalSeparatorChar &&
            s[i] != NumberUtils.NegativeSign &&
            s[i] != NumberUtils.DecimalGroupingSeparator) {
          return false;
        }
      }
      return true;
    }

public static char DecimalSeparatorChar
    {
      get { return Convert.ToChar(DecimalSeparator); }
    }

public static char NegativeSign
    {
      get { return Convert.ToChar(System.Globalization.NumberFormatInfo.CurrentInfo.NegativeSign); }
    }

public static char DecimalGroupingSeparator
    {
      get { return Convert.ToChar(System.Globalization.NumberFormatInfo.CurrentInfo.NumberGroupSeparator); }
    }

==============================================
I hope this helps...

Sorry I forgot one more:

public static void LogException(System.Exception ex)
    {
      Console.WriteLine(ex.Message);
    }

Thanks a lot redpipe. You actually forgot StringUtils. I wonder what that does.

Thanks again.

StringUtils is a helper class I have made for string manipulation. It contains amongst others the above mentioned methods:

public static string removeFromLastChar(StringBuilder sb, char splitChar)
public static string removeFromLastChar(string s, char splitChar)
public static string ReplaceLast(string originalText, string searchPattern, string replacementText)
public static bool isEmpty(object o)
public static bool isEmpty(string s)
public static bool isNumeric(String s)

In addition I have another helper class called NumberUtils that has been used in the code above. The corresponding methods in that class is:
public static char DecimalSeparatorChar
public static char NegativeSign
public static char DecimalGroupingSeparator

The CloseResources method is placed in a helper class called DbUtils in my code, and the LogException method is placed in a helper class called Logger.

Sorry about the "mess"! It clearly shows the challenges a quick copy paste can give you :-)

I',m sorry...This doesn't help me. I need to bulk export 100,000 records FROM SQL SEREVR TABLETO AN EXCEL TABLE. I'm stuck with that. I'm not using grid view. I'm used sql bulk copy to import it. Now, I want to export it using the same.
I'm stuck as to how I'm suppose to do it.Hope I can get the solution ASAP.IT'S EXTREMELY URGENT.