How to pass datatable from windows application to excel sheet by using c# coding?

[vidhyaG]

Hi....

i want to pass datatable from windows application to excel sheet.im using c# language

if any one know this.... help me

[PsychoCoder]

If you're wanting to convert a DataTable to Excel first you can add a reference to the Office interops

using Microsoft.Office.Interop.Excel
using Microsoft.Office.Interop
 

Then it's just a matter of creating your Excel application, then your Workbook and a Worksheet inside the Worksheet. You then loop through each row in your DataTable adding the values to your worksheet.

Here's an example

public bool ExportDataToExcel(System.Data.DataTable data, string worksheetName, string saveAs)
{
    Excel.Application excel;
    Excel.Workbook workBook;
    Excel.Worksheet sheet;
    Excel.Range range;
 
    try
    {
        // Start Excel and get Application object.
        excel = new Excel.Application();
 
        // here we make sure Excel doesnt show while we're working with it
        excel.Visible = false;
        excel.DisplayAlerts = false;
 
        // create a new Workbook
        workBook = excel.Workbooks.Add;
 
        // get the first active sheet from the workbook
        sheet = (Excel.Worksheet)workBook.ActiveSheet;
        sheet.Name = worksheetName;
 
        // loop through each row and add values to our sheet
        int count = 1;
        foreach (DataRow row in data.Rows)
        {
            count += 1;
            for (int i = 1; i <= data.Columns.Count; i++)
            {
                // on the first iteration we add the column headers
                if (count == 2)
                {
                    sheet.Cells(1, i) = data.Columns[i - 1].ColumnName;
                }
                sheet.Cells(count, i) = row[i - 1].ToString();
            }
 
        }
 
        // now we resize the columns
        range = sheet.Range(sheet.Cells(1, 1), sheet.Cells(count, data.Columns.Count));
        range.EntireColumn.AutoFit();
 
        //now save the workbook and exit Excel
        workBook.SaveAs(saveAs);
        workBook.Close();
        excel.Quit();
        return true;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        return false;
    }
    finally
    {
        sheet = null;
        range = null;
        workBook = null;
    }
}
 

Hope that helps ;)

[vidhyaG]

hi,

thanks for your reply....

i used your code...but it makes error...

error occurring in line 18,46,47...

i am developing a project in windows application by using c# language....

and than i want to pass a datatable to excel and have to draw chart from that...(ex marksheet)

if you know. help me..

[Frinavale]

Do you have Excel installed on the machine where you are trying to execute the application?

What is the error message?

-Frinny

[vidhyaG]

yes i have installed Ms Excel.

i am doing this in windows application using c# language.i am creating student mark list.so if i cal any function it will show that marklist in excel sheet and have to disply chart for that.

1)In line No 18. workBook = excel.Workbooks.Add;

Error:43 Cannot convert method group 'Add' to non-delegate type Microsoft.Office.Interop.Excel.Workbook'. Did you intend to invoke the method?

2)In Line no 42. range = sheet.Range(sheet.Cells[1, 1], sheet.Cells[count, data.Columns.Count]);

Error 44:Property, indexer, or event 'Range' is not supported by the language; try directly calling accessor method

3)workBook.SaveAs(saveAs)

Error 45:No overload for method 'SaveAs' takes '1' arguments

4)workBook.Close()
Error 46:No overload for method 'Close' takes '0' arguments