Insert Datatable data into Sheet

1stly you have to add a sheet through this you can access its property. go on this link
http://www.youtube.com/watch?v=FPDjOngX2cw

Excel _excel = new Excel();

try

{

_excel.AddWorkBook("");

_excel.DisplayAlerts(false);

// _excel.AddWorksheet();

_excel.SetSheetName("Sheet1", "T1");

//for mearging and wreptext

Excel.SelectionProp xlSelProp = new Excel.SelectionProp();

_excel.MergeCell("A1:C1", xlSelProp);

//for Font setting

Excel.XLFont xlFont = new Excel.XLFont();

xlFont.Bold = true;

_excel.SetFont("A1", xlFont);

_excel.SetColumnWidth("A:A", 30);

_excel.SetColumnWidth("B:B", 40);

_excel.SetColumnWidth("C:C", 30);

_excel.SetColumnWidth("D:D", 30);

_excel.SetColumnWidth("E:E", 30);

_excel.SetColumnWidth("F:F", 30);

_excel.SetColumnWidth("G:G", 30);

_excel.SetColumnWidth("H:H", 10);

_excel.SetColumnWidth("I:I", 10);

DataTable dtTable = new DataTable();

dtTable.Columns.Add("Client");

dtTable.Columns.Add("FileNo");

dtTable.Columns.Add("status");

dtTable.Columns.Add("Gender");

dtTable.Columns.Add("Name");

dtTable.Columns.Add("Designation");

dtTable.Columns.Add("DOJ");

dtTable.Rows.Add(new object[] { "bank", "1", "HUF", "male", "munesh", "developer", "24 feb 2014" });

dtTable.Rows.Add(new object[] { "BBBB", "2", "indi", "female", "rahul", "testing", "12 march 2014" });

dtTable.Rows.Add(new object[] { "CCCC", "3", "Developer", "male", "munesh", "developer", "24 feb 2014" });

dtTable.Rows.Add(new object[] { "DDDD", "4", "Developer", "male", "munesh", "developer", "24 feb 2014" });

dtTable.Rows.Add(new object[] { "EEEE", "5", "Developer", "male", "munesh", "developer", "24 feb 2014" });

dtTable.Rows.Add(new object[] { "FFFF", "6", "Developer", "male", "munesh", "developer", "24 feb 2014" });

_excel.ExportDataTable(4, 1, dtTable, true);

//connection with dtabase

string query = "select * from SVT_SERVICEMASTER";

SqlCommand cmd = new SqlCommand(query, con);

con.Open();

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = cmd;

DataTable dt = new DataTable();

da.Fill(dt);

_excel.ExportDataTable(14, 1, dt, true);

//for sheet 4 with database

_excel.SetColumnWidth("A:A", 10);

_excel.SetColumnWidth("B:B", 10);

_excel.SetColumnWidth("C:C", 10);

_excel.SetColumnWidth("D:D", 10);

_excel.SetColumnWidth("E:E", 10);

_excel.SetColumnWidth("F:F", 10);

_excel.SetColumnWidth("G:G", 30);

_excel.AddWorksheet();

_excel.SetSheetName("sheet4", "contact information");

_excel.SetCellValue("A1", "contact information");

string query1 = "select * from SVT_DETAILS";

SqlCommand cmd1 = new SqlCommand(query1, con);

SqlDataAdapter da1 = new SqlDataAdapter();

da.SelectCommand = cmd1;

DataTable dt1 = new DataTable();

da.Fill(dt1);

_excel.ExportDataTable(3, 1, dt1, true);

//for sheet 5

_excel.AddWorksheet();

_excel.SetSheetName("sheet5", "copy data");

_excel.SetCellValue("A1", "copy data");

_excel.DisplayAlerts(true);

_excel.ShowExcel(false);

this.Cursor = Cursors.Default;

}

catch

{

_excel.Dispose();

}

finally

{

this.Cursor = Cursors.Default;

_excel.Dispose();

_excel.CloseExcelApplication();

}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Munesh Sharma12y ago
1stly you have to add a sheet through this you can access its property. go on this link
http://www.youtube.com/watch?v=FPDjOngX2cw

Excel _excel = new Excel();
try
{

_excel.AddWorkBook("");
_excel.DisplayAlerts(false);
// _excel.AddWorksheet();
_excel.SetSheetName("Sheet1", "T1");

//for mearging and wreptext
Excel.SelectionProp xlSelProp = new Excel.SelectionProp();
_excel.MergeCell("A1:C1", xlSelProp);

//for Font setting

Excel.XLFont xlFont = new Excel.XLFont();
xlFont.Bold = true;
_excel.SetFont("A1", xlFont);

_excel.SetColumnWidth("A:A", 30);
_excel.SetColumnWidth("B:B", 40);
_excel.SetColumnWidth("C:C", 30);
_excel.SetColumnWidth("D:D", 30);
_excel.SetColumnWidth("E:E", 30);
_excel.SetColumnWidth("F:F", 30);
_excel.SetColumnWidth("G:G", 30);
_excel.SetColumnWidth("H:H", 10);
_excel.SetColumnWidth("I:I", 10);

DataTable dtTable = new DataTable();

dtTable.Columns.Add("Client");
dtTable.Columns.Add("FileNo");
dtTable.Columns.Add("status");
dtTable.Columns.Add("Gender");
dtTable.Columns.Add("Name");
dtTable.Columns.Add("Designation");
dtTable.Columns.Add("DOJ");

dtTable.Rows.Add(new object[] { "bank", "1", "HUF", "male", "munesh", "developer", "24 feb 2014" });
dtTable.Rows.Add(new object[] { "BBBB", "2", "indi", "female", "rahul", "testing", "12 march 2014" });
dtTable.Rows.Add(new object[] { "CCCC", "3", "Developer", "male", "munesh", "developer", "24 feb 2014" });
dtTable.Rows.Add(new object[] { "DDDD", "4", "Developer", "male", "munesh", "developer", "24 feb 2014" });
dtTable.Rows.Add(new object[] { "EEEE", "5", "Developer", "male", "munesh", "developer", "24 feb 2014" });
dtTable.Rows.Add(new object[] { "FFFF", "6", "Developer", "male", "munesh", "developer", "24 feb 2014" });

_excel.ExportDataTable(4, 1, dtTable, true);

//connection with dtabase
string query = "select * from SVT_SERVICEMASTER";
SqlCommand cmd = new SqlCommand(query, con);

con.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);

_excel.ExportDataTable(14, 1, dt, true);


//for sheet 4 with database
_excel.SetColumnWidth("A:A", 10);
_excel.SetColumnWidth("B:B", 10);
_excel.SetColumnWidth("C:C", 10);
_excel.SetColumnWidth("D:D", 10);
_excel.SetColumnWidth("E:E", 10);
_excel.SetColumnWidth("F:F", 10);
_excel.SetColumnWidth("G:G", 30);
_excel.AddWorksheet();
_excel.SetSheetName("sheet4", "contact information");
_excel.SetCellValue("A1", "contact information");
string query1 = "select * from SVT_DETAILS";
SqlCommand cmd1 = new SqlCommand(query1, con);

SqlDataAdapter da1 = new SqlDataAdapter();
da.SelectCommand = cmd1;
DataTable dt1 = new DataTable();
da.Fill(dt1);

_excel.ExportDataTable(3, 1, dt1, true);

//for sheet 5

_excel.AddWorksheet();
_excel.SetSheetName("sheet5", "copy data");
_excel.SetCellValue("A1", "copy data");

_excel.DisplayAlerts(true);
_excel.ShowExcel(false);
this.Cursor = Cursors.Default;

}
catch
{
_excel.Dispose();

}
finally
{
this.Cursor = Cursors.Default;
_excel.Dispose();
_excel.CloseExcelApplication();
}
}
Sarath Kumar12y ago
I want to fill data table data to sheet.
Munesh Sharma12y ago
you want to fill data of database or datatable in excel sheet