HOW TO WRITE EXCEL FILE FROM DATATABLE IN C#

Pravesh Singh

Total Post:412

Points:2888
Posted by  Pravesh Singh
C# 
C#
 15699  View(s)
Ratings:
Rate this:

Hello Everyone,

Can anyone tell me how to write excel file from datatable in C#.Net. Please resolve my problem as soon as possible.

Thanks in advance!!

  1. Arun Singh

    Post:68

    Points:476
    Re: How to write excel file from datatable in c#

    Hello Pravesh Singh,
     
    Check this code...
     
    // Create sql connection string
     
    string conString = @"Data Source =  ServerName ; Initial Catalog = MyDatabase; User Id = XXXXXX ; Password = XXXXXX;";
     
    SqlConnection sqlCon = new SqlConnection(conString);
     
     sqlCon.Open();
     
    // Fill data into MyTable
     
    SqlDataAdapter da = new SqlDataAdapter("select * from MyTableName", sqlCon);
     
    System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
     
    da.Fill(dtMainSQLData);
     
    DataColumnCollection dcCollection = dtMainSQLData.Columns;
     
    // Export Data into EXCEL Sheet

    Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
     
    ExcelApp.Application.Workbooks.Add(Type.Missing);
     
     // Write data into excel sheet cells

    for (int i = 1; i < dtMainSQLData.Rows.Count + 1; i++)
     
    {
     
      for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
     
        {
     
     if (i == 1)
     
        ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
     
     else
     
      ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 1][j - 1].ToString();
     
       }
     
     }
     
    // save excel file
     
    ExcelApp.ActiveWorkbook.SaveCopyAs("SaveAsFilePath");
     
    ExcelApp.ActiveWorkbook.Saved = true;
     
    ExcelApp.Quit();
     
    It might be resolve your problem.

      Modified On Jan-10-2012 08:44:04 AM
  1. Richard Boolman

    Post:1

    Points:7
    Re: How to write excel file from datatable in c#

    Hi,

    You can use this Excel .NET component to write excel file from a datatable.
    The C# code below shows how to do this:

    DataTable dt = new DataTable();
    ExcelWorkbook Wbook = new ExcelWorkbook();
    Wbook.Worksheets.Add("WSheet1").ReadFromDataTable(dt);
    Wbook.WriteXLS(@"c:\output.xls");

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!