C# .NET - Export DataTable to Excel in Console application

Asked By Jem Savery on 20-Dec-11 09:01 AM

Hi Friends,

I need to export the dataTable to excel sheet (Not CSV) to the particular location on hard drive.

How can I do it in console application?

Please help.

Devil Scorpio replied to Jem Savery on 20-Dec-11 09:11 AM

HI,

here is a good code which does it,

  public void CreateCSVFile(DataTable dt, string strFilePath)
  {
    #region Export Grid to CSV
    // Create the CSV file to which grid data will be exported.
    StreamWriter sw = new StreamWriter(strFilePath, false);
    // First we will write the headers.
    //DataTable dt = m_dsProducts.Tables[0];
    int iColCount = dt.Columns.Count;
    for (int i = 0; i < iColCount; i++)
    {
      sw.Write(dt.Columns[i]);
      if (i < iColCount - 1)
      {
        sw.Write(",");
      }
    }
    sw.Write(sw.NewLine);
    // Now write all the rows.
    foreach (DataRow dr in dt.Rows)
    {
      for (int i = 0; i < iColCount; i++)
      {
        if (!Convert.IsDBNull(dr[i]))
        {
          sw.Write(dr[i].ToString());
        }
        if (i < iColCount - 1)
        {
          sw.Write(",");
        }
      }
      sw.Write(sw.NewLine);
    }
    sw.Close();
    #endregion
 }

Refer this website for help
http://www.codeproject.com/KB/cs/WriteDataToExcel.aspx

dipa ahuja replied to Jem Savery on 20-Dec-11 10:15 AM

Try this protected void Button3_Click(object sender, EventArgs e)

{

DataTable dt = Class2.GetData();

ExportDataTable(dt);

}

public void ExportDataTable(DataTable dt)

{

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=e1.xls");

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

string sTab = "";

foreach (DataColumn dc in dt.Columns)

{

HttpContext.Current.Response.Write(sTab + dc.ColumnName);

sTab = "\t";

}

HttpContext.Current.Response.Write("\n");

int i;

foreach (DataRow dr in dt.Rows)

{

sTab = "";

for (i = 0; i < dt.Columns.Count; i++)

{

HttpContext.Current.Response.Write(sTab + dr[i].ToString());

sTab = "\t";

}

HttpContext.Current.Response.Write("\n");

}

HttpContext.Current.Response.End();

}

Jitendra Faye replied to Jem Savery on 21-Dec-11 12:30 AM

Use this code-

public void WriteToExcelSpreadsheet(string fileName, System.Data.DataTable dt)

{

string filepath = getPath(fileName).Trim();

//dt = SQLProductProvider.GetExcelImport();

// dt.WriteXml(filepath, XmlWriteMode.IgnoreSchema);

Microsoft.Office.Interop.Excel.Application ExlApp = new Microsoft.Office.Interop.Excel.Application();

int iCol, iRow, iColVal;

Object missing = System.Reflection.Missing.Value;

// Open the document that was chosen by the dialog

Microsoft.Office.Interop.Excel.Workbook aBook;

try

{

//'re-initialize excel app

ExlApp = new Microsoft.Office.Interop.Excel.Application();if (ExlApp == null)

{

//'throw an exception

throw (new Exception("Unable to Start Microsoft Excel"));

}

else

{

//'supresses overwrite warnings

ExlApp.DisplayAlerts = false;

//aBook = New Excel.Workbook

//'check if file exists

if (File.Exists(filepath))

{

aBook = ExlApp.Workbooks._Open(filepath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

}

else

{

aBook = ExlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

}//End If

//With ExlApp

ExlApp.SheetsInNewWorkbook = 1;

//ExlApp.Worksheets[1].Select();

//For displaying the column name in the the excel file.

for (iCol = 0; iCol < dt.Columns.Count; iCol++)

{

//'clear column name before setting a new value

ExlApp.Cells[1, iCol + 1] = "";

ExlApp.Cells[1, iCol + 1] = dt.Columns[iCol].ColumnName.ToString();

}//next

//For displaying the column value row-by-row in the the excel file.

for (iRow = 0; iRow < dt.Rows.Count ; iRow++)

{

try

{

for (iColVal = 0; iColVal < dt.Columns.Count; iColVal++)

{

if (dt.Rows[iRow].ItemArray[iColVal] is string)

{

ExlApp.Cells[iRow + 2, iColVal + 1] = "'" + dt.Rows[iRow].ItemArray[iColVal].ToString();

}

else

{

ExlApp.Cells[iRow + 2, iColVal + 1] = dt.Rows[iRow].ItemArray[iColVal].ToString();

}//End If

}//next

}

catch (Exception ex)

{

Console.Write("ERROR: " + ex.Message);

}//End Try

}//next

if (File.Exists(filepath))

{

ExlApp.ActiveWorkbook.Save(); //fileName)

}

else

{

ExlApp.ActiveWorkbook.SaveAs(filepath.Trim(), missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);

}//End If

ExlApp.ActiveWorkbook.Close(true, missing, missing);

//End With

//Console.Write("File exported sucessfully");

}//End if

}

catch (System.Runtime.InteropServices.COMException ex)

{

Console.Write("ERROR: " + ex.Message);

}

catch (Exception ex)

{

Console.Write("ERROR: " + ex.Message);

}

finally

{

ExlApp.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlApp);

aBook = null;

ExlApp = null;

}//End Try

}//End Sub

Try this and let me know.