I have 3 tables having almost same data and same data colom.I need to expoert this 3 table together to one excel sheet side by side. i have 3 datatable ,each dt have 2 cloms so my excel should have 2+2+2= 6 coloms.How can i add like this? below is my methods.where i have to change? please help
public static void CreateExcelSheet(DataSet result, string excelPath) { var ds = new DataSet(); DataTable dt1 = new DataTable(); dt1.Columns.Add("Name", typeof (string)); dt1.Columns.Add("Age", typeof (int)); // Create a DataRow, add Name and Age data, and add to the DataTable DataRow dr1 = dt1.NewRow(); dr1["Name"] = "Mohammadsd"; // or dr[0]="Mohammad"; dr1["Age"] = 24; // or dr[1]=24; dt1.Rows.Add(dr1); result.Tables.Add(dt1); DataTable dt2 = new DataTable(); dt2.Columns.Add("Name", typeof (string)); dt2.Columns.Add("Age", typeof (int)); // Create a DataRow, add Name and Age data, and add to the DataTable DataRow dr2 = dt2.NewRow(); dr2["Name"] = "Mohammad"; // or dr[0]="Mohammad"; dr2["Age"] = 24; // or dr[1]=24; dt2.Rows.Add(dr2); result.Tables.Add(dt2); DataTable dt3 = new DataTable(); dt3.Columns.Add("Name", typeof (string)); dt3.Columns.Add("sex", typeof (int)); // Create a DataRow, add Name and Age data, and add to the DataTable DataRow dr3 = dt3.NewRow(); dr3["Name"] = "last"; // or dr[0]="Mohammad"; dr3["sex"] = 24; // or dr[1]=24; dt3.Rows.Add(dr3); ds.Tables.Add(dt3); object misValue = System.Reflection.Missing.Value; Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue); for (int i = 0; i < ds.Tables.Count; i++) { DataTable table = ds.Tables[i]; Excel._Worksheet xlWorkSheet = xlWorkBook.Sheets.Count <= i ? (Excel._Worksheet) xlWorkBook.Sheets.Add(After: xlWorkBook.Sheets[xlWorkBook.Sheets.Count]) : (Excel._Worksheet) xlWorkBook.Sheets[1]; //same sheet xlWorkSheet.Columns.NumberFormat = "@"; AddDataToWorksheet(xlWorkSheet, table); xlWorkSheet.Columns.AutoFit(); xlWorkSheet.Rows.AutoFit(); Marshal.ReleaseComObject(xlWorkSheet); } xlWorkBook.SaveAs(excelPath); xlWorkBook.Close(); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); } private static void AddDataToWorksheet(Excel._Worksheet sheet, DataTable table) { sheet.Name = "Report"; for (int i = 0; i < table.Columns.Count; i++) { sheet.Cells[1, i + 1] = table.Columns[i].ColumnName; } for (int i = 0; i < table.Rows.Count; i++) { int rowNumber = i + 2; DataRow row = table.Rows[i]; for (int j = 0; j < table.Columns.Count; j++) { sheet.Cells[rowNumber, j + 1] = Convert.ToString(row[j]); } } }