Insert Datatable data into Sheet

Question

Hi,

I have a spreadsheet with 3 sheets named as T1,T2 and T3.

I have three datatables in dataset named as T1,T2 and T3.

My requirement is, Insert T1 Datatable data into T1 Sheet.:Insert T2 Datatable data into T2 Sheet : Insert T3 Datatable data into T3 Sheet.

Here i am creted spreadsheet using Open XMl SDK.

If anyone knows please help me guys.....I'm struck with that work......

Munesh Sharma · Answer

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 Kumar · Answer

I want to fill data table data to sheet.

Munesh Sharma · Answer

you want to fill data of database or datatable in excel sheet

Insert Datatable data into Sheet

Answers (3)