none
Export dataTable to Excel from C#

    Question

  • Hi,

    I neet to export DataTable to excel.
    I used with  RKLib ExportData and it's work well.
    My code:

    RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win");
    objExport.ExportDetails(dtExport, Export.ExportFormat.CSV, path + filename + ".csv");    

    But . my problem is that I need to add a header row BEFORE the columns header.
    I want that the excel file will be like:

    PERIOD:  01\03\09   -  31\03\09    -----row header
    Col 1           col 2              col 3 ....  ---------  columns header
    aaa             aaaa             aaaa
    bbb            bbbb               bbbb      ---data

    How can I do it?????

    One of the option of exportDetails function is:
    public void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] Headers, Export.ExportFormat FormatType, string FileName);
    But Headers refers to columns headers and I need other row BEFORE columns header.

    I hope that someone will be able to help me...

    Thanks.
    Thursday, March 26, 2009 8:24 AM

Answers

  • Hi,

    It seems the class does not support the functionality.

    However , we can try to add the row ourself with an overload of ExportDetails which takes a parameter (string[] Headers).

    Please try to add the headers into the first row of a new table.

     

    Then use the overload method to export the new table to an excel file,  the PERIOD:  01\03\09   -  31\03\09 will be added as headers :

    ExportDetails( datatable ,columlist, new string[]{“PERIOD:”,” 01\03\09  ”,” -  31\03\09”}, filename)

     

    If this way wont work for you , you might need to use excel automation to add the row into the excle file.

     

    Harry

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Harry Zhu Tuesday, March 31, 2009 3:26 AM
    Monday, March 30, 2009 2:18 AM

All replies

  • Hi,

    The RKLib componet is not supported in the c# forum. I have no idea if it provide the functionality inserting new row .

    But you might want to insert a new row in the front of the table .

                  
                DataTable dt = new DataTable();  
     
                dt.Rows.InsertAt(new DataRow(), 0); 


    Harry
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, March 27, 2009 4:32 AM
  • Thanks for your answer but I already tried it and it's not help me becouse the new row be added under the header columns.
    I want to add a new row BEFORE the header columns.
    Sunday, March 29, 2009 5:12 AM
  • Hi,

    It seems the class does not support the functionality.

    However , we can try to add the row ourself with an overload of ExportDetails which takes a parameter (string[] Headers).

    Please try to add the headers into the first row of a new table.

     

    Then use the overload method to export the new table to an excel file,  the PERIOD:  01\03\09   -  31\03\09 will be added as headers :

    ExportDetails( datatable ,columlist, new string[]{“PERIOD:”,” 01\03\09  ”,” -  31\03\09”}, filename)

     

    If this way wont work for you , you might need to use excel automation to add the row into the excle file.

     

    Harry

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Harry Zhu Tuesday, March 31, 2009 3:26 AM
    Monday, March 30, 2009 2:18 AM
  • Hi,

      Following code will help you add a Column/Row Header to the final exported excel. Invoke the ExportGridView function with required parameters.  Add the Header row, when we loop thru each row in the Grid and add it to the final table. Here with minor modifications you can use DataTable instead of GridView.

    using System;
    using System.Data;
    using System.IO;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    
    
    /// <summary>
    /// This class will be used to export the Grid View to Excel
    /// while maintaining the GridView controls.
    /// </summary>
    public class ExportToExcel
    {
       
        #region ExportGridView
    
        /// <summary>
        /// This method will export the GridView to Excel.
        /// </summary>
        /// <param name="currentPage">The current Page object.</param>
        /// <param name="gvInput">The GridView to be exported.</param>
        /// <param name="gridViewDataSource">GridView data source as table.</param>
        /// <param name="excelHeaderTitle">Header Title shown on the exported excel.</param>
        public void ExportGridView(Page currentPage, GridView gvInput,
            DataTable gridViewDataSource, String excelHeaderTitle)
        {
            // Clear the HttpConext contents
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.AddHeader(
                "content-disposition", string.Format("attachment; filename=PaladinReport.xls"));
            HttpContext.Current.Response.ContentType = "application/ms-excel";
    
            // Using the StringWriter and HtmlTextWriter process the GridView
            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    HtmlForm form = new HtmlForm();
    
                    //  Create a table to contain the grid
                    Table table = new Table();
    
                    //  Include the gridline settings
                    table.GridLines = gvInput.GridLines;
    
                    //  Add the header row to the table
                    if (gvInput.HeaderRow != null)
                    {
                        PrepareControlForExport(gvInput.HeaderRow);
                        table.Rows.Add(gvInput.HeaderRow);
                        table.Rows[0].BackColor = System.Drawing.ColorTranslator.FromHtml("#0067a2");
                        table.Rows[0].ForeColor = System.Drawing.Color.White;
                    }
    
                    // Get the DataTable from the supplied query.
                    gvInput.DataSource = gridViewDataSource;
                    gvInput.AllowPaging = false;
                    gvInput.DataBind();
    
                    // Add each of the data rows to the table
                    foreach (GridViewRow row in gvInput.Rows)
                    {
    		    // ADD THE HEADER TO THE FIRST ROW ONLY - HERE
                        PrepareControlForExport(row);
                        table.Rows.Add(row);
                    }
    
                    // Again enable paging
                    gvInput.AllowPaging = true;
    
                    //  add the footer row to the table
                    if (gvInput.FooterRow != null)
                    {
                        PrepareControlForExport(gvInput.FooterRow);
                        table.Rows.Add(gvInput.FooterRow);
                    }
    
                    // Add the Header Title for the exported excel
                    Label lblHeader = new Label();
                    lblHeader.ID = "lblHeader";
                    lblHeader.Text = "Paladin - " + excelHeaderTitle;
                    lblHeader.Font.Size = FontUnit.Large;
    
                    // Go to next line
                    HtmlGenericControl br = new HtmlGenericControl("BR");
    
                    // Add the created date to the excel
                    Label lblDateTimeStamp = new Label();
                    lblDateTimeStamp.ID = "lblDateTimeStamp";
                    lblDateTimeStamp.Text = "Created On - " + DateTime.Now.ToString("MMM, dd yyyy");
                    lblDateTimeStamp.Font.Size = FontUnit.Medium;
    
                    // Add all the controls to the form
                    form.Controls.Add(lblHeader);
                    form.Controls.Add(br);
                    form.Controls.Add(lblDateTimeStamp);
                    form.Controls.Add(table);
                    currentPage.Controls.Add(form);
    
                    //  Render the table into the HtmlTextWriter
                    form.RenderControl(htw);
    
                    //  Render the htmlwriter into the response
                    HttpContext.Current.Response.Write(sw.ToString());
                    HttpContext.Current.Response.End();
                }
            }
        }
    
        #endregion ExportGridView
    
        #region Prepare For Export To Excel
    
        /// <summary>
        /// Replace any of the contained controls with literals
        /// </summary>
        /// <param name="control"></param>
        private static void PrepareControlForExport(Control control)
        {
            Control current = null;
            for (int i = 0; i < control.Controls.Count || i == 0; i++)
            {
    
                if (control.Controls.Count == 0)
                {
                    current = control;
                }
                else
                {
                    current = control.Controls[i];
                }
    
                // You can add the type of control you want to show in excel here...
                if (control.Visible == true)
                {
                    if (current is LinkButton)
                    {
                        control.Controls.Remove(current);
                        control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                    }
                    else if (current is ImageButton)
                    {
                        control.Controls.Remove(current);
                        control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                    }
                    else if (current is HyperLink)
                    {
                        control.Controls.Remove(current);
                        control.Controls.AddAt(i, (current as HyperLink));
                    }
                    else if (current is DropDownList)
                    {
                        control.Controls.Remove(current);
                        control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                    }
                    else if (current is CheckBox)
                    {
                        control.Controls.Remove(current);
                        control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
                    }
                    else if (current is DataControlFieldCell)
                    {
                        DataControlFieldCell dataFieldCell = current as DataControlFieldCell;
    
                        for (int ctrl = 0; ctrl < dataFieldCell.Controls.Count; ctrl++)
                        {
                            PrepareControlForExport(dataFieldCell.Controls[ctrl]);
                        }
                    }
                }
    
                if (current.HasControls())
                {
                    PrepareControlForExport(current);
                }
            }
        }
    
        #endregion Prepare For Export To Excel
    }
    


    Hope this helps you.
    Sandeep Aparajit | http://sandeep-aparajit.blogspot.com | Mark useful posts as Answer/Helpful.
    Monday, March 30, 2009 4:45 AM

  • Hi Harry,

    I can't use with Headers parameter because Headers refers to columns headers and I need other row BEFORE columns header.

    thanks.
    Tuesday, March 31, 2009 6:11 AM
  • Hi,

    I mean adding your row as header, and insert row header into a new table instead of using default header.
    Does this work?

    Harry
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, March 31, 2009 6:32 AM
  • Sorry , but I can't understand you.
    I have a table with columns headers and I want add row header before the columns headers.
    and under the row header I need write the columns headers...

    Tuesday, March 31, 2009 6:39 AM
  • Hi,

    Please try to perform the steps below:

    1. Insert a new row containing headers to the table, so that the first row will show those headers.

    Col 1           col 2              col 3 ....  ---------  columns header
    Col 1           col 2              col 3  --->   inserted row
    aaa             aaaa             aaaa
    bbb            bbbb               bbbb      ---data


    2. Export the datatable using the overloaded method: ExportDetails( datatable ,columlist, new string[]{“PERIOD:”,” 01\03\09  ”,” -  31\03\09”}, filename)

    If that does not work , you might want to use automation to manupulate the excel file .

    Harry


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, March 31, 2009 6:46 AM
  • Hi, for an easy and efficient export DataTable to Excel take a look at this Excel C# / VB.NET library.

    Here is a sample Excel C# code how to do it:

    var ef = new ExcelFile();
    
    ef.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, true);
    
    ef.SaveXls(dataTable.TableName + ".xls");
    

    Friday, May 06, 2011 9:06 AM
  • > I neet to export DataTable to excel.

    The following 100%-free library will let you do this with one simple "CreateExcelDocument" command.   Just tell it which DataTable to export, and the name of the Excel file you want to create.

    http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

    DataTable dt = CreateSampleData();         //  Create a sample DataSet, and put some data in it.
    CreateExcelFile.CreateExcelDocument(dt,  "C:\\Sample.xlsx");
    

    This library uses Microsoft's free OpenXML libraries to create a real Excel 2007 (.xlsx) file, so you don't even need to have Excel installed on your server. 

    All source code is provided (free of charge) so you can change it as you please.  

    There's also a simple demo, showing how to use it.

    Tuesday, April 03, 2012 12:37 PM
  • I have come to prefer the XML/late-binding route when doing this...setting the class up to accept datatables, datagridviews, multi-column listbox, etc...

    I havent looked at the library Mike has mentioned, but being able to dynamically generate XML (adding/editing styles, types, etc...) is extremely useful when you find yourself exporting to excel frequently.

    Tuesday, April 03, 2012 4:24 PM