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]);
}
}
}