Insert Datatable data into Sheet

Question

Hi I have a spreadsheet with 3 sheets named as T1T2 and T3. I have three datatables in dataset named as T1T2 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