Click here to Skip to main content
15,890,609 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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.I am allowed to use excel interop only


What I have tried:

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

            }

        }

    }
Posted
Updated 2-Feb-22 22:18pm

1 solution

I have modified code to get all columns and merged into sheet acceding to your requirements. Check the code, hope you will get your desired output.
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel._Workbook xlWorkBook;
Microsoft.Office.Interop.Excel._Worksheet xlWorkSheet;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = (Microsoft.Office.Interop.Excel._Workbook)(xlApp.Workbooks.Add(""));
xlWorkSheet = (Microsoft.Office.Interop.Excel._Worksheet)xlWorkBook.ActiveSheet;
int rowIndex = 2, colIndex = 1, actualIndex = 1;
for (int i = 0; i < resultDS.Tables.Count; i++)
{
    rowIndex = 2;
    colIndex = 1;
    if (i > 0)
        actualIndex += resultDS.Tables[i - 1].Columns.Count;
    foreach (DataRow row in resultDS.Tables[i].Rows)
    {
        colIndex = actualIndex;
        for (int k = 0; k < resultDS.Tables[i].Columns.Count; k++)
        {
            if(rowIndex == 2) // For Sheet Header
            xlWorkSheet.Cells[rowIndex-1, colIndex]=             resultDS.Tables[i].Columns[k].ColumnName;
            xlWorkSheet.Cells[rowIndex, colIndex] = row[resultDS.Tables[i].Columns[k]];
            colIndex++;
        }
        rowIndex++;
    }
}
xlWorkBook.SaveAs("YourPath\dtEmployee.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkBook.Close();
xlApp.Quit();
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900