Sujeet Raman

Sujeet Raman

  • 862
  • 927
  • 351.7k

How to Append data coloms in a worksheet from a data table in interop

Feb 2 2022 6:42 PM


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

 


Answers (5)