Click here to Skip to main content
16,021,430 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello All,

I have two data tables which I need to export to excel sheet. I have to send one table data to one sheet (sheet1) and another to another sheet (sheet2).

Is it possible to do?... If so can you please give me the referrals.

Thanks in advance...

Regards,
Naveen.
Posted

1 solution

Yes you can do that.

From StackOverFlow. hope it helps.

http://stackoverflow.com/a/13973274[^]

C#
// Export DataTable into an excel file with field names in the header line
    // - Save excel file without ever making it visible if filepath is given
    // - Don't save excel file, just make it visible if no filepath is given
    public static void ExportToExcel(this DataTable Tbl, string ExcelFilePath = null)
    {
        try
        {
            if (Tbl == null || Tbl.Columns.Count == 0)
                throw new Exception("ExportToExcel: Null or empty input table!\n");

            // load excel, and create a new workbook
            Excel.Application excelApp = new Excel.Application();
            excelApp.Workbooks.Add();

            // single worksheet
            Excel._Worksheet workSheet = excelApp.ActiveSheet;

            // column headings
            for (int i = 0; i < Tbl.Columns.Count; i++)
            {
                workSheet.Cells[1, (i+1)] = Tbl.Columns[i].ColumnName;
            }

            // rows
            for (int i = 0; i < Tbl.Rows.Count; i++)
            {
                // to do: format datetime values before printing
                for (int j = 0; j < Tbl.Columns.Count; j++)
                {
                    workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
                }
            }

            // check fielpath
            if (ExcelFilePath != null && ExcelFilePath != "")
            {
                try
                {
                    workSheet.SaveAs(ExcelFilePath);
                    excelApp.Quit();
                    MessageBox.Show("Excel file saved!");
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                        + ex.Message);
                }
            }
            else    // no filepath is given
            {
                excelApp.Visible = true;
            }
        }
        catch(Exception ex)
        {
            throw new Exception("ExportToExcel: \n" + ex.Message);
        }
    }
}
 
Share this answer
 
v2
Comments
NaVeN Kumar 12-Jul-13 7:58am    
Is this works to export data to second sheet as I did not see any sheet names specifically.
Sushil Mate 12-Jul-13 8:15am    
you want to add in same file or another workbook?
NaVeN Kumar 13-Jul-13 0:36am    
In same file...
Sushil Mate 14-Jul-13 8:50am    
you can add workbook.. iterate same code

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