You must Sign In to post a response.
  • Category: .NET

    How to Export two datagridview data to one Excel file at same time?

    I have two datagridview in form1 windows application and have one export to excel button. That two datagridview have two different column names and column count. If i click Export to Excel button first datagridview data save in excel sheet1 and second datagridview data save in sheet2 of the same excel file. how to do?
  • #650720
    Add a reference of Microsfot Excel x.x Object Library COM component and use the following code:


    private void Form1_Load(object sender, EventArgs e)
    {
    DataTable dt = new DataTable();
    DataColumn dc = new DataColumn("id");
    dt.Columns.Add(dc);
    dc = new DataColumn("name");
    dt.Columns.Add(dc);
    dt.Rows.Add("1", "a");
    dt.Rows.Add("2", "b");
    dt.Rows.Add("3", "c");
    dataGridView1.DataSource = dt;

    DataTable dt1 = new DataTable();
    DataColumn dc1 = new DataColumn("id");
    dt1.Columns.Add(dc1);
    dc1 = new DataColumn("name");
    dt1.Columns.Add(dc1);
    dt1.Rows.Add("4", "j");
    dt1.Rows.Add("5", "k");
    dt1.Rows.Add("6", "l");
    dataGridView2.DataSource = dt1;
    }

    private void button1_Click(object sender, EventArgs e)
    {
    DataTable dt = (DataTable)dataGridView1.DataSource;
    DataTable dt1 = (DataTable)dataGridView2.DataSource;
    ConvertToExcel(dt, dt1);
    }

    public string ConvertToExcel(DataTable dt, DataTable dt1)
    {
    string FilePath;
    Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
    ExcelApp.Application.Workbooks.Add(Type.Missing);

    Microsoft.Office.Interop.Excel.Worksheet Sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[1];

    for (int i = 0; i < dt.Columns.Count; i++)
    {
    Sheet1.Cells[1, i + 1] = dt.Columns[i].ColumnName;
    }

    for (int i = 0; i < dt.Rows.Count; i++)
    {
    for (int j = 0; j < dt.Columns.Count; j++)
    {
    Sheet1.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
    }
    }

    Microsoft.Office.Interop.Excel.Worksheet Sheet2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[2];
    for (int i = 0; i < dt1.Columns.Count; i++)
    {
    Sheet2.Cells[1, i + 1] = dt1.Columns[i].ColumnName;
    }

    for (int i = 0; i < dt1.Rows.Count; i++)
    {
    for (int j = 0; j < dt1.Columns.Count; j++)
    {
    Sheet2.Cells[i + 2, j + 1] = dt1.Rows[i][j].ToString();
    }
    }

    FilePath = "d:\\" + Guid.NewGuid() + ".xls";
    if (FilePath != string.Empty)
    {
    ExcelApp.ActiveWorkbook.SaveAs(FilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

    ExcelApp.ActiveWorkbook.Saved = true;
    ExcelApp.Quit();
    }
    return FilePath;
    }


    Thanks & Regards
    Paritosh Mohapatra
    Microsoft MVP (ASP.Net/IIS)
    DotNetSpider MVM


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.