Export To Excel Including Child Templates

12 posts, 0 answers
  1. Mahitha Madala
    Mahitha Madala avatar
    9 posts
    Member since:
    Aug 2009

    Posted 07 Jan 2010 Link to this post

    hi,

    I have Implemented Export to Excel for A RadGridView Content. It is working.
    But the problem here is, I have child templetes also in my application.

    Can you please tell me how to export The RadGridView content including Child templates
    Or is there any property to be assigned.

    Thank you.

    regards,
    Mahitha Madala.
  2. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 13 Jan 2010 Link to this post

    Hello Mahitha Madala,

    Thank you for the question.

    RadGridView export to excel functionality does not support exporting the child templates, because Microsoft Excel does not support hierarchical data. This is a Microsoft limitation and there is no clear way how to represent our hierarchy in Excel spreadsheet. 

    Best wishes,
    Martin Vasilev
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  3. UI for WinForms is Visual Studio 2017 Ready
  4. Versile
    Versile avatar
    20 posts
    Member since:
    May 2009

    Posted 07 Apr 2010 Link to this post

    Why wouldn't you set it up for each sheet?

    i.e. master grid, sheetname default to the datamember
    child template 1, sheetname default to the datamember
    child template 2, sheetname etc

    so in the above scenario you'd have 3 sheets, and be able to export hierarchial grid data?
  5. Versile
    Versile avatar
    20 posts
    Member since:
    May 2009

    Posted 09 Apr 2010 Link to this post

    Here's some code I wrote up, including the following:
    1. ExpandAllRows - Quick function to Expand/Collapse all rows
    2. getHTML - Export RadGrid to HTML (I plan on re-writing this to use the current template instead of just the parent grid for heirarchy uses)
    3. createTemplatefromMaster - Create a child template that is based on the Master Template (I use this then set the individual columns I need to modify when the data is extremely alike)
    4. ExportHierarchyGrid - Export Hierarchy Grid by making a seperate worksheet per Child Templates

    The Hierarchy export comes from a code snippet I read earlier in the forum (sorry I don't have the link to give proper credit), it just creates a sheet for the parent grid, and then a sheet for each child template attached to it.

    *Note the getHTML function requires you to add System.Web to the References of your project
    -- Edited as we improved the code tremendously and fixed a few minor bugs
    using System;  
    using System.Collections.Generic;  
    using System.Linq;  
    using System.Text;  
    using Telerik.WinControls.UI;  
    using System.Data;  
    using System.IO;  
    using System.Web.UI;  
    using Telerik.WinControls.UI.Export;  
    using System.Windows.Forms;  
    using System.Xml;  
     
    namespace Diamond.Objects.Classes  
    {  
        public static class RadGridOptions  
        {  
            public static void ExpandAllRows(ref RadGridView rGrid, bool expandBool)  
            {  
                // Begin and EndEdit prevent the grid from drawing in between as this is a memory intensive operation  
                rGrid.BeginEdit();  
                foreach (GridViewDataRowInfo gvdri in rGrid.Rows)  
                {  
                    gvdri.IsExpanded = expandBool;  
                }  
                rGrid.EndEdit();  
            }  
     
            public static string getHTML(Telerik.WinControls.UI.RadGridView rGrid)  
            {  
                DataTable table = new DataTable();  
                foreach (Telerik.WinControls.UI.GridViewDataColumn col in rGrid.Columns)  
                {  
                    if (col.IsVisible)  
                    {  
                        DataColumn dCol = new DataColumn();  
                        dCol.ColumnName = col.HeaderText;  
                        dCol.Prefix = col.UniqueName;  
                        dCol.DataType = col.DataType;  
                        dCol.Caption = col.FormatString;  
                        table.Columns.Add(dCol);  
                    }  
                }  
                DataRow row;  
                foreach (Telerik.WinControls.UI.GridViewDataRowInfo rowInfo in rGrid.Rows)  
                {  
                    row = table.NewRow();  
                    foreach (DataColumn col in table.Columns)  
                    {  
                        row[col] = rowInfo.Cells[col.Prefix].Value.ToString();  
                    }  
                    table.Rows.Add(row);  
                }  
                System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();  
                dg.AlternatingItemStyle.BackColor = System.Drawing.Color.LightGray;  
                dg.CellPadding = 1;  
                dg.GridLines = System.Web.UI.WebControls.GridLines.Both;  
                dg.AutoGenerateColumns = false;  
                foreach (DataColumn col in table.Columns)  
                {  
                    System.Web.UI.WebControls.BoundColumn bCol = new System.Web.UI.WebControls.BoundColumn();  
                    bCol.DataFormatString = col.Caption;  
                    bCol.HeaderText = col.ColumnName;  
                    bCol.DataField = col.Prefix;  
                    dg.Columns.Add(bCol);  
                }  
                foreach (DataColumn nCol in table.Columns)  
                {  
                    nCol.ColumnName = nCol.Prefix;  
                    nCol.Prefix = string.Empty;  
                }  
                dg.DataSource = table;  
                dg.DataBind();  
                StringBuilder SB = new StringBuilder();  
                StringWriter SW = new StringWriter(SB);  
                HtmlTextWriter htmlTW = new HtmlTextWriter(SW);  
                dg.RenderControl(htmlTW);  
     
                return SB.ToString();  
            }  
     
            public static void ExportGridToExcel(RadGridView rGrid, string fileNamewithoutGuid)  
            {  
                try 
                {  
                    ExpandAllRows(ref rGrid, false);  
                    foreach (GridViewDataColumn dCol in rGrid.Columns)  
                    {  
                        if (dCol.DataType == typeof(DateTime))  
                        {  
                            if (dCol.FormatString.ToLower() == "{0:d}")  
                            {  
                                dCol.ExcelExportType = DisplayFormatType.ShortDate;  
                            }  
                        }  
                        if (dCol.FormatString != null)  
                        {  
                            if (dCol.FormatString.ToLower() == "{0:c}")  
                            {  
                                dCol.ExcelExportType = DisplayFormatType.Currency;  
                            }  
                            if (dCol.FormatString.ToLower() == "{0:p}")  
                            {  
                                dCol.ExcelExportType = DisplayFormatType.Percent;  
                            }  
                            if (dCol.FormatString.ToLower() == "{0.##}")  
                            {  
                                dCol.ExcelExportType = DisplayFormatType.Custom;  
                                dCol.ExcelExportFormatString = "#,##0.00";  
                            }  
                        }  
                    }  
                    ExportToExcelML exporter = new ExportToExcelML(rGrid);  
                    fileNamewithoutGuid = fileNamewithoutGuid.Replace(" ""_");  
                    char[] c = Path.GetInvalidFileNameChars();  
                    char[] f = fileNamewithoutGuid.ToCharArray();  
                    foreach (char ch in c)  
                    {  
                        if (f.Contains<char>(ch))  
                        {  
                            fileNamewithoutGuid.Replace(ch.ToString(), string.Empty);  
                        }  
                    }  
                    string fileName = Environment.GetEnvironmentVariable("TEMP") + "\\" + fileNamewithoutGuid + "_" + Guid.NewGuid() + ".xls";  
                    exporter.SheetMaxRows = ExcelMaxRows._65536;  
                    exporter.HiddenColumnOption = HiddenOption.DoNotExport;  
                    exporter.HiddenRowOption = HiddenOption.DoNotExport;  
                    if (rGrid.DataMember.Length < 1)  
                    {  
                        exporter.SheetName = "Data";  
                    }  
                    else 
                    {  
                        exporter.SheetName = rGrid.DataMember;  
                    }  
                    exporter.ExportVisualSettings = true;  
                    exporter.RunExport(fileName);  
                    System.Diagnostics.Process p = new System.Diagnostics.Process();  
                    p.StartInfo.FileName = fileName;  
                    p.Start();  
                    p.Dispose();  
                }  
                catch (Exception exd)  
                {  
                    string x = exd.Message;  
                    DialogResult ds = Telerik.WinControls.RadMessageBox.Show("Click Yes to view Exception Detail, No to download an Excel Viewer, Cancel to do nothing""Error Exporting to Excel", MessageBoxButtons.YesNoCancel, Telerik.WinControls.RadMessageIcon.Error);  
                    switch (ds.ToString().ToLower())  
                    {  
                        case "yes":  
                            Telerik.WinControls.RadMessageBox.Show(exd.Message + "\r\n\r\n" + exd.StackTrace, "Error Exception Detail");  
                            break;  
                        case "no":  
                            System.Diagnostics.Process p1 = new System.Diagnostics.Process();  
                            p1.StartInfo.FileName = "http://www.ssidish.com/downloads/xlviewer.exe";  
                            p1.Start();  
                            p1.Dispose();  
                            break;  
                        case "cancel":  
                            // do nothing  
                            break;  
                    }  
                }  
            }  
     
            private const string NS_SPREADSHEET = "urn:schemas-microsoft-com:office:spreadsheet";  
            private const string NS_EXCEL = "urn:schemas-microsoft-com:office:excel";  
     
            public static void ExportHierarchyGrid(RadGridView rGrid, string fileNamewithoutGuid, bool finishExpanded)  
            {  
                // Expand all rows so we can look at them during export, otherwise we'll get a viewstate exception in child tables if they aren't expanded  
                // Unfortunately when done the grid is either going to be completely collapsed or completed expanded based on finishExpanded  
                ExpandAllRows(ref rGrid, true);  
                bool ExportHiddenColumns = false;  
                bool ExportHiddenRows = false;  
                bool processSuccess = false;  
                XmlTextWriter wr = null;  
                  
                // Clean up filename and use Guid to make unique  
                fileNamewithoutGuid = fileNamewithoutGuid.Replace(" ""_");  
                char[] c = Path.GetInvalidFileNameChars();  
                char[] f = fileNamewithoutGuid.ToCharArray();  
                foreach (char ch in c)  
                {  
                    if (f.Contains<char>(ch))  
                    {  
                        fileNamewithoutGuid.Replace(ch.ToString(), string.Empty);  
                    }  
                }  
                string fileName = Environment.GetEnvironmentVariable("TEMP") + "\\" + fileNamewithoutGuid + "_" + Guid.NewGuid() + ".xls";  
     
                try 
                {  
                    // Create Workbook base  
                    wr = new XmlTextWriter(fileName, Encoding.Default);  
                    wr.Formatting = Formatting.Indented;  
                    wr.Indentation = 4;  
                    wr.WriteStartDocument();  
                    wr.WriteStartElement("Workbook", NS_SPREADSHEET);  
                    wr.WriteAttributeString("xmlns""ss"null, NS_SPREADSHEET);  
                    wr.WriteAttributeString("xmlns""x"null, NS_EXCEL);  
                    wr.WriteStartElement("Styles", NS_SPREADSHEET); // Start Styles  
     
                    wr.WriteStartElement("Style", NS_SPREADSHEET);  
                    wr.WriteAttributeString("ID", NS_SPREADSHEET, "octopusDateStyle");  
                    wr.WriteStartElement("NumberFormat", NS_SPREADSHEET);  
                    wr.WriteAttributeString("Format", NS_SPREADSHEET, "Short Date");  
                    wr.WriteEndElement();  
                    wr.WriteEndElement();  
     
                    wr.WriteStartElement("Style", NS_SPREADSHEET);  
                    wr.WriteAttributeString("ID", NS_SPREADSHEET, "octopusTimeStyle");  
                    wr.WriteStartElement("NumberFormat", NS_SPREADSHEET);  
                    wr.WriteAttributeString("Format", NS_SPREADSHEET, "[$-F400]h:mm AM/PM");  
                    wr.WriteEndElement();  
                    wr.WriteEndElement();  
     
                    wr.WriteStartElement("Style", NS_SPREADSHEET);  
                    wr.WriteAttributeString("ID", NS_SPREADSHEET, "octopusCurrencyStyle");  
                    wr.WriteStartElement("NumberFormat", NS_SPREADSHEET);  
                    wr.WriteAttributeString("Format", NS_SPREADSHEET, "Currency");  
                    wr.WriteEndElement();  
                    wr.WriteEndElement();  
     
                    wr.WriteStartElement("Style", NS_SPREADSHEET);  
                    wr.WriteAttributeString("ID", NS_SPREADSHEET, "octopusPercentStyle");  
                    wr.WriteStartElement("NumberFormat", NS_SPREADSHEET);  
                    wr.WriteAttributeString("Format", NS_SPREADSHEET, "Percent");  
                    wr.WriteEndElement();  
                    wr.WriteEndElement();  
     
                    wr.WriteStartElement("Style", NS_SPREADSHEET);  
                    wr.WriteAttributeString("ID", NS_SPREADSHEET, "octopusDecimalStyle");  
                    wr.WriteStartElement("NumberFormat", NS_SPREADSHEET);  
                    wr.WriteAttributeString("Format", NS_SPREADSHEET, "#,##0.00");  
                    wr.WriteEndElement();  
                    wr.WriteEndElement();  
     
                    wr.WriteEndElement(); // End Styles  
                    GridViewTemplate template = rGrid.MasterGridViewTemplate;  
                    // Create each individual spreadsheet to add to workbook start at the Master  
                    int sheetNumber = 0; // Using sheetNumber to ensure each sheet name is unique  
     
                    exportSheet(ref wr, template, ExportHiddenColumns, ExportHiddenRows, sheetNumber);  
     
                    foreach (GridViewTemplate childTemplate in rGrid.MasterGridViewTemplate.ChildGridViewTemplates)  
                    {  
                        sheetNumber++;  
                        exportSheet(ref wr, childTemplate, ExportHiddenColumns, ExportHiddenRows, sheetNumber);  
                    }  
     
                    wr.WriteEndDocument();  
                    ExpandAllRows(ref rGrid, false);  
                    processSuccess = true;  
                }  
                catch (System.Exception ex)  
                {  
                    Telerik.WinControls.RadMessageBox.Show(ex.Message, "Failed to Export to Excel");  
                    processSuccess = false;  
                }  
                finally 
                {  
                    if (wr != null)  
                    {  
                        wr.Close();  
                    }  
                }  
                if (processSuccess)  
                {  
                    System.Diagnostics.Process p = new System.Diagnostics.Process();  
                    p.StartInfo.FileName = fileName;  
                    p.Start();  
                    p.Dispose();  
                }  
            }  
     
            private static void exportSheet(ref XmlTextWriter wr, GridViewTemplate template, bool ExportHiddenColumns, bool ExportHiddenRows, int sheetNumber)  
            {  
                // If no columns in grid there is a problem, throw exception  
                if (template.Columns.Count < 1)  
                {  
                    throw new Exception("There are no Columns");  
                }  
                // Create Sheet Name  
                string sheetName = "Data" + sheetNumber.ToString();  
                if (template.DataMember != null)  
                {  
                    if (template.DataMember.Length > 1)  
                    {  
                        sheetName = template.DataMember;  
                    }  
                }  
                // Start Spreadsheet  
                wr.WriteStartElement("Worksheet", NS_SPREADSHEET);  // Start Worksheet  
                wr.WriteAttributeString("Name", NS_SPREADSHEET, sheetName);  
                wr.WriteStartElement("Table", NS_SPREADSHEET);  // Start Table  
     
                // Create column styles from above styleIDs  
                if (template.Rows.Count > 0)  
                {  
                    foreach (GridViewDataColumn dCol in template.Columns)  
                    {  
                        if (dCol.IsVisible || ExportHiddenColumns)  
                        {  
                            wr.WriteStartElement("Column", NS_SPREADSHEET); // Start Column  
                            wr.WriteAttributeString("Width", NS_SPREADSHEET, dCol.Width.ToString());  
                            if (dCol.FormatString != null)  
                            {  
                                switch (dCol.FormatString.ToLower())  
                                {  
                                    case "{0:c}":  
                                        wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusCurrencyStyle");  
                                        break;  
                                    case "{0:p}":  
                                        wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusPercentStyle");  
                                        break;  
                                    case "{0.##}":  
                                        wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusDecimalStyle");  
                                        break;  
                                    case "{0:d}":  
                                        wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusDateStyle");  
                                        break;  
                                    case "{0:t}":  
                                        wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusTimeStyle");  
                                        break;  
                                }  
                            }  
                            else 
                            {  
                                if (dCol.DataType == typeof(DateTime))  
                                {  
                                    wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusDateStyle");  
                                }  
                            }  
     
                            wr.WriteEndElement();  // End Column  
                        }  
                    }  
                }  
     
                // Add column text  
                wr.WriteStartElement("Row", NS_SPREADSHEET); // Start Row  
                foreach (GridViewColumn col in template.Columns)  
                {  
                    if (col.IsVisible || ExportHiddenColumns)  
                    {  
                        wr.WriteStartElement("Cell", NS_SPREADSHEET); // Start Cell  
                        wr.WriteStartElement("Data", NS_SPREADSHEET); // Start Data  
                        wr.WriteAttributeString("Type", NS_SPREADSHEET, "String");  
                        wr.WriteString(col.HeaderText);  
                        wr.WriteEndElement(); // End Cell  
                        wr.WriteEndElement(); // End Data  
                    }  
                }  
                wr.WriteEndElement();  // End Row  
     
                // Add data  
                foreach (GridViewRowInfo row in template.Rows)  
                {  
                    if (row.IsVisible || ExportHiddenRows)  
                    {  
                        wr.WriteStartElement("Row", NS_SPREADSHEET);  // Start Row  
                        foreach (GridViewCellInfo cell in row.Cells)  
                        {  
                            if (cell.ColumnInfo.IsVisible || ExportHiddenColumns)  
                            {  
     
                                if (cell.ColumnInfo.DataType == typeof(DateTime))  
                                {  
                                    wr.WriteStartElement("Cell", NS_SPREADSHEET);  
                                    wr.WriteStartElement("Data", NS_SPREADSHEET);  
                                    wr.WriteAttributeString("Type", NS_SPREADSHEET, "DateTime");  
                                    DateTime dt = (DateTime)cell.Value;  
                                    wr.WriteString(dt.ToString("s") + ".000");  
                                    wr.WriteEndElement();  
                                    wr.WriteEndElement();  
                                }  
                                else if (cell.ColumnInfo.DataType == typeof(long) || cell.ColumnInfo.DataType == typeof(int) || cell.ColumnInfo.DataType == typeof(System.Decimal))  
                                {  
                                    wr.WriteStartElement("Cell", NS_SPREADSHEET);  
                                    wr.WriteStartElement("Data", NS_SPREADSHEET);  
                                    wr.WriteAttributeString("Type", NS_SPREADSHEET, "Number");  
                                    wr.WriteString(cell.Value.ToString());  
                                    wr.WriteEndElement();  
                                    wr.WriteEndElement();  
                                }  
                                else if (cell.ColumnInfo.DataType == typeof(bool))  
                                {  
                                    wr.WriteStartElement("Cell", NS_SPREADSHEET);  
                                    wr.WriteStartElement("Data", NS_SPREADSHEET);  
                                    wr.WriteAttributeString("Type", NS_SPREADSHEET, "Boolean");  
                                    bool bl = (bool)cell.Value;  
                                    if (bl)  
                                    {  
                                        wr.WriteString("1");  
                                    }  
                                    else 
                                    {  
                                        wr.WriteString("0");  
                                    }  
                                    wr.WriteEndElement();  
                                    wr.WriteEndElement();  
                                }  
                                else 
                                {  
                                    wr.WriteStartElement("Cell", NS_SPREADSHEET);  
                                    wr.WriteStartElement("Data", NS_SPREADSHEET);  
                                    wr.WriteAttributeString("Type", NS_SPREADSHEET, "String");  
                                    wr.WriteString(cell.Value.ToString());  
                                    wr.WriteEndElement();  
                                    wr.WriteEndElement();  
                                }  
                            }  
                        }  
                        wr.WriteEndElement(); // end row  
                    }  
                }  
                wr.WriteEndElement();  // End Table  
                wr.WriteEndElement();  // End Sheet  
            }  
     
            public static GridViewTemplate createTemplatefromMaster(RadGridView rGrid)  
            {  
                GridViewTemplate template = new GridViewTemplate();  
                template.AutoGenerateColumns = rGrid.MasterGridViewTemplate.AutoGenerateColumns;  
                Telerik.WinControls.UI.GridViewDataColumn[] col = new GridViewDataColumn[rGrid.MasterGridViewTemplate.Columns.Count];  
                rGrid.MasterGridViewTemplate.Columns.CopyTo(col, 0);  
                template.Columns.BeginItemUpdate();  
                foreach (GridViewDataColumn dCol in col)  
                {  
                    GridViewDataColumn newCol = new GridViewDataColumn(dCol.UniqueName, dCol.FieldName);  
                    newCol.HeaderText = dCol.HeaderText;  
                    newCol.HeaderTextAlignment = dCol.HeaderTextAlignment;  
                    newCol.FormatString = dCol.FormatString;  
                    newCol.TextAlignment = dCol.TextAlignment;  
                    newCol.MinWidth = dCol.MinWidth;  
                    newCol.MaxWidth = dCol.MaxWidth;  
                    newCol.Width = dCol.Width;  
                    newCol.AllowFiltering = dCol.AllowFiltering;  
                    newCol.AllowGroup = dCol.AllowGroup;  
                    newCol.AllowHide = dCol.AllowHide;  
                    newCol.AllowResize = dCol.AllowResize;  
                    newCol.AllowSort = dCol.AllowSort;  
                    newCol.DataType = dCol.DataType;  
                    newCol.DisableHTMLRendering = dCol.DisableHTMLRendering;  
                    newCol.ExcelExportFormatString = dCol.ExcelExportFormatString;  
                    newCol.HeaderImage = dCol.HeaderImage;  
                    newCol.ImageLayout = dCol.ImageLayout;  
                    newCol.ReadOnly = dCol.ReadOnly;  
                    newCol.TextImageRelation = dCol.TextImageRelation;  
                    newCol.VisibleInColumnChooser = dCol.VisibleInColumnChooser;  
                    newCol.WrapText = dCol.WrapText;  
                    newCol.IsVisible = dCol.IsVisible;  
                    template.Columns.Add(newCol);  
                }  
                template.Columns.EndItemUpdate();  
                return template;  
            }  
        }  
    }  
     
  6. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 13 Apr 2010 Link to this post

    Hello Versile,

    Thank you for writing.

    Actually, we are considering to include exporting hierarchical data for some of the next releases and we will take your ideas into account. I have updated your Telerik points for sharing your code with us. Let me know if you have any additional questions.

    Greetings,
    Martin Vasilev
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  7. Deepak
    Deepak avatar
    4 posts
    Member since:
    Jan 2011

    Posted 09 Feb 2011 Link to this post

    Hello Martin,
               I have 2010 Q3 and this is not part of it... correct?

    Thanks
    Deepak
  8. Versile
    Versile avatar
    20 posts
    Member since:
    May 2009

    Posted 09 Feb 2011 Link to this post

    Most recent version of my export code as it broke through the last release

    using System;
    using System.Data;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Web.UI;
    using System.Windows.Forms;
    using System.Xml;
    using Telerik.WinControls.UI;
    using Telerik.WinControls.UI.Export;
    using System.Collections;
      
      
    namespace Diamond.Objects.Classes
    {
        public static class RadGridOptions
        {
            public static DataSet ExcelToDataSet(string fileName, ref object[] returnObj)
            {
                // http://exceldatareader.codeplex.com/
                DataSet output = new DataSet();
                returnObj[0] = "Failed to Complete";
                try
                {
                    using (FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
                    {
                        Excel.IExcelDataReader excelReader = fileName.IndexOf(".xlsx") > -1 ? Excel.ExcelReaderFactory.CreateOpenXmlReader(stream) :
                                                                                              Excel.ExcelReaderFactory.CreateBinaryReader(stream);
                        excelReader.IsFirstRowAsColumnNames = true;
                        output = excelReader.AsDataSet(false);
                        if (excelReader.IsClosed)
                            excelReader.Close();
                    }
                    foreach (DataTable outputTable in output.Tables)
                    {
                        DataRow[] rowCol = new DataRow[outputTable.Rows.Count];
                        for (int i = 0; i < outputTable.Rows.Count; i++)
                        {
                            if (outputTable.Rows[i][0] == System.DBNull.Value)
                            {
                                rowCol[i] = outputTable.Rows[i];
                            }
                            string testStr1 = outputTable.Rows[i][0].ToString().Trim();
                            if (outputTable.Columns.Count > 1)
                            {
                                string testStr2 = outputTable.Rows[i][1].ToString().Trim();
                                if (testStr1 == string.Empty && testStr2 == string.Empty)
                                {
                                    rowCol[i] = outputTable.Rows[i];
                                }
                            }
                            else
                            {
                                if (testStr1 == string.Empty)
                                {
                                    rowCol[i] = outputTable.Rows[i];
                                }
                            }
                        }
                        foreach (DataRow colRow in rowCol)
                        {
                            if (colRow != null)
                                outputTable.Rows.Remove(colRow);
                        }
                    }
                    returnObj[0] = "Finished";
                    returnObj[1] = fileName;
                    returnObj[2] = output.Tables.Count.ToString() + " tables";
                }
                catch (Exception exd)
                {
                    returnObj[0] = "Failed to Complete";
                    returnObj[1] = exd.Message;
                    returnObj[2] = exd.StackTrace;
                }
                return output;
            }
      
            public static void ExpandAllRows(ref RadGridView rGrid, bool expandBool)
            {
                // Begin and EndEdit prevent the grid from drawing in between as this is a memory intensive operation
                rGrid.BeginEdit();
                foreach (GridViewDataRowInfo gvdri in rGrid.MasterTemplate.Rows)
                {
                    gvdri.IsExpanded = expandBool;
                }
                foreach (GridViewTemplate template in rGrid.Templates)
                {
                    foreach (GridViewDataRowInfo gvdri in template.Rows)
                    {
                        gvdri.IsExpanded = expandBool;
                    }
                }
                for (int i = 0; i < rGrid.MasterTemplate.Groups.Count; i++ )
                {
                    if (expandBool)
                    {
                        rGrid.MasterTemplate.Groups[i].Expand();                    
                    }
                    else
                    {
                        rGrid.MasterTemplate.Groups[i].Collapse();                   
                    }
                }
                rGrid.EndEdit();
            }
      
            public static string getHTML(Telerik.WinControls.UI.RadGridView rGrid)
            {
                DataTable table = new DataTable();
                foreach (Telerik.WinControls.UI.GridViewDataColumn col in rGrid.Columns)
                {
                    if (col.IsVisible)
                    {
                        DataColumn dCol = new DataColumn();
                        dCol.ColumnName = col.HeaderText;
                        dCol.Prefix = col.Name;
                        dCol.DataType = col.DataType;
                        dCol.Caption = col.FormatString;
                        table.Columns.Add(dCol);
                    }
                }
                DataRow row;
                foreach (Telerik.WinControls.UI.GridViewDataRowInfo rowInfo in rGrid.Rows)
                {
                    if (rowInfo.IsVisible)
                    {
                        row = table.NewRow();
                        foreach (DataColumn col in table.Columns)
                        {
                            row[col] = rowInfo.Cells[col.Prefix].Value.ToString();
                        }
                        table.Rows.Add(row);
                    }
                }
                System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
                dg.AlternatingItemStyle.BackColor = System.Drawing.Color.LightGray;
                dg.CellPadding = 1;
                dg.GridLines = System.Web.UI.WebControls.GridLines.Both;
                dg.AutoGenerateColumns = false;
                foreach (DataColumn col in table.Columns)
                {
                    System.Web.UI.WebControls.BoundColumn bCol = new System.Web.UI.WebControls.BoundColumn();
                    bCol.DataFormatString = col.Caption;
                    bCol.HeaderText = col.ColumnName;
                    bCol.DataField = col.Prefix;
                    dg.Columns.Add(bCol);
                }
                foreach (DataColumn nCol in table.Columns)
                {
                    nCol.ColumnName = nCol.Prefix;
                    nCol.Prefix = string.Empty;
                }
                dg.DataSource = table;
                dg.DataBind();
                StringBuilder SB = new StringBuilder();
                StringWriter SW = new StringWriter(SB);
                HtmlTextWriter htmlTW = new HtmlTextWriter(SW);
                dg.RenderControl(htmlTW);
      
                return SB.ToString();
            }
      
            public static string getSelectedRowsHTML(Telerik.WinControls.UI.RadGridView rGrid)
            {
                DataTable table = new DataTable();
                foreach (Telerik.WinControls.UI.GridViewDataColumn col in rGrid.Columns)
                {
                    if (col.IsVisible)
                    {
                        DataColumn dCol = new DataColumn();
                        dCol.ColumnName = col.HeaderText;
                        dCol.Prefix = col.Name;
                        dCol.DataType = col.DataType;
                        dCol.Caption = col.FormatString;
                        table.Columns.Add(dCol);
                    }
                }
                DataRow row;
                foreach (Telerik.WinControls.UI.GridViewDataRowInfo rowInfo in rGrid.Rows)
                {
                    if (rowInfo.IsVisible &&
                        rowInfo.IsSelected)
                    {
                        row = table.NewRow();
                        foreach (DataColumn col in table.Columns)
                        {
                            if (rowInfo.Cells[col.Prefix].Value != DBNull.Value )
                            {   
                                if (rowInfo.Cells[col.Prefix].Value.ToString() != "System.Drawing.Bitmap")
                                    row[col] = rowInfo.Cells[col.Prefix].Value.ToString();
                            }
                        }
                        table.Rows.Add(row);
                    }
                }
                System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
                dg.AlternatingItemStyle.BackColor = System.Drawing.Color.LightGray;
                dg.CellPadding = 1;
                dg.GridLines = System.Web.UI.WebControls.GridLines.Both;
                dg.AutoGenerateColumns = false;
                foreach (DataColumn col in table.Columns)
                {
                    System.Web.UI.WebControls.BoundColumn bCol = new System.Web.UI.WebControls.BoundColumn();
                    bCol.DataFormatString = col.Caption;
                    bCol.HeaderText = col.ColumnName;
                    bCol.DataField = col.Prefix;
                    dg.Columns.Add(bCol);
                }
                foreach (DataColumn nCol in table.Columns)
                {
                    nCol.ColumnName = nCol.Prefix;
                    nCol.Prefix = string.Empty;
                }
                dg.DataSource = table;
                dg.DataBind();
                StringBuilder SB = new StringBuilder();
                StringWriter SW = new StringWriter(SB);
                HtmlTextWriter htmlTW = new HtmlTextWriter(SW);
                dg.RenderControl(htmlTW);
      
                return SB.ToString();
            }
      
            public static void ExportGridToExcel(RadGridView rGrid, string fileNamewithoutGuid)
            {
                try
                {
                    ExpandAllRows(ref rGrid, false);
                    foreach (GridViewDataColumn dCol in rGrid.Columns)
                    {
                        if (dCol.DataType == typeof(DateTime))
                        {
                            if (dCol.FormatString.ToLower() == "{0:d}")
                            {
                                dCol.ExcelExportType = DisplayFormatType.ShortDate;
                            }
                        }
                        if (dCol.FormatString != null)
                        {
                            if (dCol.FormatString.ToLower() == "{0:c}")
                            {
                                dCol.ExcelExportType = DisplayFormatType.Currency;
                            }
                            if (dCol.FormatString.ToLower() == "{0:p}")
                            {
                                dCol.ExcelExportType = DisplayFormatType.Percent;
                            }
                            if (dCol.FormatString.ToLower() == "{0.##}")
                            {
                                dCol.ExcelExportType = DisplayFormatType.Custom;
                                dCol.ExcelExportFormatString = "#,##0.00";
                            }
                        }
                    }
                    ExportToExcelML exporter = new ExportToExcelML(rGrid);
                    fileNamewithoutGuid = fileNamewithoutGuid.Replace(" ", "_");
                    char[] c = Path.GetInvalidFileNameChars();
                    char[] f = fileNamewithoutGuid.ToCharArray();
                    foreach (char ch in c)
                    {
                        if (f.Contains<char>(ch))
                        {
                            fileNamewithoutGuid.Replace(ch.ToString(), string.Empty);
                        }
                    }
                    string fileName = Environment.GetEnvironmentVariable("TEMP") + "\\" + fileNamewithoutGuid + "_" + Guid.NewGuid() + ".xls";
                    exporter.SheetMaxRows = ExcelMaxRows._65536;
                    exporter.HiddenColumnOption = HiddenOption.DoNotExport;
                    exporter.HiddenRowOption = HiddenOption.DoNotExport;
                    if (rGrid.DataMember.Length < 1)
                    {
                        exporter.SheetName = "Data";
                    }
                    else
                    {
                        exporter.SheetName = rGrid.DataMember;
                    }
                    exporter.ExportVisualSettings = true;
                    exporter.RunExport(fileName);
                    System.Diagnostics.Process.Start(fileName);
                }
                catch (Exception exd)
                {
                    //string x = exd.Message;
                    DialogResult ds = Telerik.WinControls.RadMessageBox.Show("Click Yes to view Exception Detail, No to download an Excel Viewer, Cancel to do nothing", "Error Exporting to Excel", MessageBoxButtons.YesNoCancel, Telerik.WinControls.RadMessageIcon.Error);
                    switch (ds.ToString().ToLower())
                    {
                        case "yes":
                            Telerik.WinControls.RadMessageBox.Show(exd.Message + "\r\n\r\n" + exd.StackTrace, "Error Exception Detail");
                            break;
                        case "no":
                            System.Diagnostics.Process p1 = new System.Diagnostics.Process();
                            p1.StartInfo.FileName = "http://www.ssidish.com/downloads/xlviewer.exe";
                            p1.Start();
                            p1.Dispose();
                            break;
                        case "cancel":
                            // do nothing
                            break;
                    }
                }
            }
      
            private const string NS_SPREADSHEET = "urn:schemas-microsoft-com:office:spreadsheet";
            private const string NS_EXCEL = "urn:schemas-microsoft-com:office:excel";
      
            public static void ExportHierarchyGrid(RadGridView rGrid, string fileNamewithoutGuid, bool finishExpanded, bool showHiddenColumns = false)
            {
                // Expand all rows so we can look at them during export, otherwise we'll get a viewstate exception in child tables if they aren't expanded
                // Unfortunately when done the grid is either going to be completely collapsed or completed expanded based on finishExpanded
                ExpandAllRows(ref rGrid, true);
                ArrayList colList = new ArrayList();
                if (showHiddenColumns)
                {
                    ColumnVisibilityChange(ref rGrid, ref colList, showHiddenColumns);
                }
                bool ExportHiddenColumns = false;
                bool ExportHiddenRows = false;
                bool processSuccess = false;
                XmlTextWriter wr = null;
                  
                // Clean up filename and use Guid to make unique
                fileNamewithoutGuid = fileNamewithoutGuid.Replace(" ", "_");
                char[] c = Path.GetInvalidFileNameChars();
                char[] f = fileNamewithoutGuid.ToCharArray();
                foreach (char ch in c)
                {
                    if (f.Contains<char>(ch))
                    {
                        fileNamewithoutGuid.Replace(ch.ToString(), string.Empty);
                    }
                }
                string fileName = Environment.GetEnvironmentVariable("TEMP") + "\\" + fileNamewithoutGuid + "_" + Guid.NewGuid() + ".xls";
      
                try
                {
                    // Create Workbook base
                    wr = new XmlTextWriter(fileName, Encoding.Default);
                    wr.Formatting = Formatting.Indented;
                    wr.Indentation = 4;
                    wr.WriteStartDocument();
                    wr.WriteStartElement("Workbook", NS_SPREADSHEET);
                    wr.WriteAttributeString("xmlns", "ss", null, NS_SPREADSHEET);
                    wr.WriteAttributeString("xmlns", "x", null, NS_EXCEL);
                    wr.WriteStartElement("Styles", NS_SPREADSHEET); // Start Styles
      
                    wr.WriteStartElement("Style", NS_SPREADSHEET);
                    wr.WriteAttributeString("ID", NS_SPREADSHEET, "octopusDateStyle");
                    wr.WriteStartElement("NumberFormat", NS_SPREADSHEET);
                    wr.WriteAttributeString("Format", NS_SPREADSHEET, "Short Date");
                    wr.WriteEndElement();
                    wr.WriteEndElement();
      
                    wr.WriteStartElement("Style", NS_SPREADSHEET);
                    wr.WriteAttributeString("ID", NS_SPREADSHEET, "octopusTimeStyle");
                    wr.WriteStartElement("NumberFormat", NS_SPREADSHEET);
                    wr.WriteAttributeString("Format", NS_SPREADSHEET, "[$-F400]h:mm AM/PM");
                    wr.WriteEndElement();
                    wr.WriteEndElement();
      
                    wr.WriteStartElement("Style", NS_SPREADSHEET);
                    wr.WriteAttributeString("ID", NS_SPREADSHEET, "octopusCurrencyStyle");
                    wr.WriteStartElement("NumberFormat", NS_SPREADSHEET);
                    wr.WriteAttributeString("Format", NS_SPREADSHEET, "Currency");
                    wr.WriteEndElement();
                    wr.WriteEndElement();
      
                    wr.WriteStartElement("Style", NS_SPREADSHEET);
                    wr.WriteAttributeString("ID", NS_SPREADSHEET, "octopusPercentStyle");
                    wr.WriteStartElement("NumberFormat", NS_SPREADSHEET);
                    wr.WriteAttributeString("Format", NS_SPREADSHEET, "Percent");
                    wr.WriteEndElement();
                    wr.WriteEndElement();
      
                    wr.WriteStartElement("Style", NS_SPREADSHEET);
                    wr.WriteAttributeString("ID", NS_SPREADSHEET, "octopusDecimalStyle");
                    wr.WriteStartElement("NumberFormat", NS_SPREADSHEET);
                    wr.WriteAttributeString("Format", NS_SPREADSHEET, "#,##0.00");
                    wr.WriteEndElement();
                    wr.WriteEndElement();
      
                    wr.WriteEndElement(); // End Styles
                    GridViewTemplate template = rGrid.MasterTemplate;
                    // Create each individual spreadsheet to add to workbook start at the Master
                    int sheetNumber = 0; // Using sheetNumber to ensure each sheet name is unique
      
                    exportSheet(ref wr, template, ExportHiddenColumns, ExportHiddenRows, sheetNumber);
      
                    foreach (GridViewTemplate childTemplate in rGrid.MasterTemplate.Templates)
                    {
                        sheetNumber++;
                        exportSheet(ref wr, childTemplate, ExportHiddenColumns, ExportHiddenRows, sheetNumber);
                        if (childTemplate.Templates.Count > 0)
                        {
                            foreach (GridViewTemplate childTemplate1 in childTemplate.Templates)
                            {
                                sheetNumber++;
                                exportSheet(ref wr, childTemplate1, ExportHiddenColumns, ExportHiddenRows, sheetNumber);
                            }
                        }
                    }
      
                    wr.WriteEndDocument();
                    processSuccess = true;
                }
                catch (System.Exception ex)
                {
                    Telerik.WinControls.RadMessageBox.Show(ex.Message + "\r\n\r\n" + ex.StackTrace, "Failed to Export to Excel");
                    processSuccess = false;
                }
                finally
                {
                    if (wr != null)
                    {
                        wr.Close();
                    }
                }
                if (processSuccess)
                {
                    if (showHiddenColumns)
                    {
                        ColumnVisibilityChange(ref rGrid, ref colList, false);
                    }
                    if (!finishExpanded)
                    {
                        ExpandAllRows(ref rGrid, false);
                    }
                    System.Diagnostics.Process.Start(fileName);
                }
            }
      
            private static void ColumnVisibilityChange(ref RadGridView rGrid, ref ArrayList colList, bool showHiddenColumns)
            {
                int counter = 0;
                if (showHiddenColumns) // UnHide columns
                {
                    ColumnVisibilityTemplates(rGrid.MasterTemplate, ref colList, true, ref counter);
                }
                else // Rehide columns
                {
                    ColumnVisibilityTemplates(rGrid.MasterTemplate, ref colList, false, ref counter);
                }
            }
      
            private static void ColumnVisibilityTemplates(GridViewTemplate template, ref ArrayList colList, bool hidden, ref int counter)
            {
                foreach (GridViewDataColumn col in template.Columns)
                {
                    ColumnVisibilityColumns(col, ref colList, hidden, ref counter);
                }
                foreach (GridViewTemplate t in template.Templates)
                {
                    counter++;
                    ColumnVisibilityTemplates(t, ref colList, hidden, ref counter);
                }
            }
      
            private static void ColumnVisibilityColumns(GridViewDataColumn col, ref ArrayList colList, bool hidden, ref int counter)
            {
                if (hidden)
                {
                    if (!col.IsVisible)
                    {
                        colList.Add(new ColumnInfo(counter, col.Name));
                        col.IsVisible = hidden;
                    }
                }
                else
                {
                    foreach (ColumnInfo colInfo in colList)
                    {
                        if (colInfo.TemplateID == counter &&
                            colInfo.ColumnName == col.Name)
                        {
                            col.IsVisible = false;
                            break;
                        }
                    }
                }
            }
      
            private class ColumnInfo
            {
                public ColumnInfo(int templateID = 0, string columnName = " ")
                {
                    TemplateID = templateID;
                    ColumnName = columnName;
                }
      
                public int TemplateID = 0;
                public string ColumnName = string.Empty;
            }
      
            private static void exportSheet(ref XmlTextWriter wr, GridViewTemplate template, bool ExportHiddenColumns, bool ExportHiddenRows, int sheetNumber)
            {
                // If no columns in grid there is a problem, throw exception
                if (template.Columns.Count < 1)
                {
                    throw new Exception("There are no Columns");
                }
                // Create Sheet Name
                string sheetName = "Data" + sheetNumber.ToString();
                if (template.DataMember != null)
                {
                    if (template.DataMember.Length > 1)
                    {
                        sheetName = template.DataMember;
                    }
                    else if (template.Caption != null)
                    {
                        if (template.Caption != string.Empty)
                            sheetName = template.Caption;
                    }
                }
                else
                {
                    if (template.Caption != null)
                    {
                        if (template.Caption != string.Empty)
                            sheetName = template.Caption;
                    }
                }
                // Start Spreadsheet
                wr.WriteStartElement("Worksheet", NS_SPREADSHEET);  // Start Worksheet
                wr.WriteAttributeString("Name", NS_SPREADSHEET, sheetName);
                wr.WriteStartElement("Table", NS_SPREADSHEET);  // Start Table
      
                // Create column styles from above styleIDs
                if (template.Rows.Count > 0)
                {
                    foreach (GridViewDataColumn dCol in template.Columns)
                    {
                        if (dCol.IsVisible || ExportHiddenColumns)
                        {
                            wr.WriteStartElement("Column", NS_SPREADSHEET); // Start Column
                            wr.WriteAttributeString("Width", NS_SPREADSHEET, dCol.Width.ToString());
                            if (dCol.FormatString != null)
                            {
                                switch (dCol.FormatString.ToLower())
                                {
                                    case "{0:c}":
                                        wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusCurrencyStyle");
                                        break;
                                    case "{0:p}":
                                        wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusPercentStyle");
                                        break;
                                    case "{0.##}":
                                        wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusDecimalStyle");
                                        break;
                                    case "{0:d}":
                                        wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusDateStyle");
                                        break;
                                    case "{0:t}":
                                        wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusTimeStyle");
                                        break;
                                    case "{0:mm/dd/yy}":
                                        wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusDateStyle");
                                        break;
                                }
                            }
                            else
                            {
                                if (dCol.DataType == typeof(DateTime))
                                {
                                    wr.WriteAttributeString("StyleID", NS_SPREADSHEET, "octopusDateStyle");
                                }
                            }
      
                            wr.WriteEndElement();  // End Column
                        }
                    }
                }
      
                // Add column text
                wr.WriteStartElement("Row", NS_SPREADSHEET); // Start Row
                foreach (GridViewColumn col in template.Columns)
                {
                    if (col.IsVisible || ExportHiddenColumns)
                    {
                        wr.WriteStartElement("Cell", NS_SPREADSHEET); // Start Cell
                        wr.WriteStartElement("Data", NS_SPREADSHEET); // Start Data
                        wr.WriteAttributeString("Type", NS_SPREADSHEET, "String");
                        wr.WriteString(col.HeaderText);
                        wr.WriteEndElement(); // End Cell
                        wr.WriteEndElement(); // End Data
                    }
                }
                wr.WriteEndElement();  // End Row
      
                // Add data
                foreach (GridViewRowInfo row in template.Rows)
                {
                    if (row.IsVisible || ExportHiddenRows)
                    {
                        wr.WriteStartElement("Row", NS_SPREADSHEET);  // Start Row
                        foreach (GridViewCellInfo cell in row.Cells)
                        {
                            if (cell.ColumnInfo.IsVisible || ExportHiddenColumns)
                            {
                                if (cell.Value != null &&
                                    cell.Value != System.DBNull.Value)
                                {
                                    if (((GridViewDataColumn)cell.ColumnInfo).DataType == typeof(DateTime))
                                    {
                                        wr.WriteStartElement("Cell", NS_SPREADSHEET);
                                        wr.WriteStartElement("Data", NS_SPREADSHEET);
                                        wr.WriteAttributeString("Type", NS_SPREADSHEET, "DateTime");
                                        DateTime dt = (cell.Value != System.DBNull.Value ? (DateTime)cell.Value : DateTime.MinValue);
                                        wr.WriteString((cell.Value != System.DBNull.Value ? dt.ToString("s") + ".000" : string.Empty));
                                        wr.WriteEndElement();
                                        wr.WriteEndElement();
                                    }
                                    else if (((GridViewDataColumn)cell.ColumnInfo).DataType == typeof(long) || ((GridViewDataColumn)cell.ColumnInfo).DataType == typeof(int) || ((GridViewDataColumn)cell.ColumnInfo).DataType == typeof(System.Decimal))
                                    {
                                        wr.WriteStartElement("Cell", NS_SPREADSHEET);
                                        wr.WriteStartElement("Data", NS_SPREADSHEET);
                                        wr.WriteAttributeString("Type", NS_SPREADSHEET, "Number");
                                        wr.WriteString(cell.Value.ToString());
                                        wr.WriteEndElement();
                                        wr.WriteEndElement();
                                    }
                                    else if (((GridViewDataColumn)cell.ColumnInfo).DataType == typeof(bool))
                                    {
                                        wr.WriteStartElement("Cell", NS_SPREADSHEET);
                                        wr.WriteStartElement("Data", NS_SPREADSHEET);
                                        wr.WriteAttributeString("Type", NS_SPREADSHEET, "Boolean");
                                        bool bl = false;
                                        try
                                        {
                                            bl = (bool)cell.Value;
                                        }
                                        catch { }
                                        if (bl)
                                        {
                                            wr.WriteString("1");
                                        }
                                        else
                                        {
                                            wr.WriteString("0");
                                        }
                                        wr.WriteEndElement();
                                        wr.WriteEndElement();
                                    }
                                    else
                                    {
                                        wr.WriteStartElement("Cell", NS_SPREADSHEET);
                                        wr.WriteStartElement("Data", NS_SPREADSHEET);
                                        wr.WriteAttributeString("Type", NS_SPREADSHEET, "String");
                                        wr.WriteString(cell.Value == null ? string.Empty : cell.Value.ToString());
                                        wr.WriteEndElement();
                                        wr.WriteEndElement();
                                    }
                                }
                                else
                                {
                                    wr.WriteStartElement("Cell", NS_SPREADSHEET);
                                    wr.WriteStartElement("Data", NS_SPREADSHEET);
                                    wr.WriteAttributeString("Type", NS_SPREADSHEET, "String");
                                    wr.WriteString(cell.Value == null ? string.Empty : cell.Value.ToString());
                                    wr.WriteEndElement();
                                    wr.WriteEndElement();
                                }
                            }
                        }
                        wr.WriteEndElement(); // end row
                    }
                }
                wr.WriteEndElement();  // End Table
                wr.WriteEndElement();  // End Sheet
            }
      
            public static GridViewTemplate createTemplatefromMaster(RadGridView rGrid)
            {
                GridViewTemplate template = new GridViewTemplate();
                template.AutoGenerateColumns = rGrid.MasterTemplate.AutoGenerateColumns;
                Telerik.WinControls.UI.GridViewDataColumn[] col = new GridViewDataColumn[rGrid.MasterTemplate.Columns.Count];
                rGrid.MasterTemplate.Columns.CopyTo(col, 0);
                template.Columns.BeginItemUpdate();
                foreach (GridViewDataColumn dCol in col)
                {
                    GridViewDataColumn newCol = getNewDataColumn(dCol);
                    template.Columns.Add(newCol);
                }
                template.Columns.EndItemUpdate();
                return template;
            }
      
            private static GridViewDataColumn getNewDataColumn(GridViewDataColumn dataColumn)
            {
                GridViewDataColumn newCol = null;
                if (dataColumn is GridViewTextBoxColumn)
                {
                    newCol = new GridViewTextBoxColumn();
                }
                else if (dataColumn is GridViewCheckBoxColumn)
                {
                    newCol = new GridViewCheckBoxColumn();
                }
                else if (dataColumn is GridViewComboBoxColumn)
                {
                    newCol = new GridViewComboBoxColumn();
                }
                else if (dataColumn is GridViewCommandColumn)
                {
                    newCol = new GridViewCommandColumn();
                }
                else if (dataColumn is GridViewDateTimeColumn)
                {
                    newCol = new GridViewDateTimeColumn();
                }
                else if (dataColumn is GridViewDecimalColumn)
                {
                    newCol = new GridViewDecimalColumn();
                }
                else if (dataColumn is GridViewImageColumn)
                {
                    newCol = new GridViewImageColumn();
                }
                else if (dataColumn is GridViewMaskBoxColumn)
                {
                    newCol = new GridViewMaskBoxColumn();
                }
                else if (dataColumn is GridViewMultiComboBoxColumn)
                {
                    newCol = new GridViewMultiComboBoxColumn();
                }
                newCol.Name = dataColumn.Name;
                newCol.FieldName = dataColumn.Name;
                newCol.HeaderText = dataColumn.HeaderText;
                newCol.HeaderTextAlignment = dataColumn.HeaderTextAlignment;
                newCol.FormatString = dataColumn.FormatString;
                newCol.TextAlignment = dataColumn.TextAlignment;
                newCol.MinWidth = dataColumn.MinWidth;
                newCol.MaxWidth = dataColumn.MaxWidth;
                newCol.Width = dataColumn.Width;
                newCol.AllowFiltering = dataColumn.AllowFiltering;
                newCol.AllowGroup = dataColumn.AllowGroup;
                newCol.AllowHide = dataColumn.AllowHide;
                newCol.AllowResize = dataColumn.AllowResize;
                newCol.AllowSort = dataColumn.AllowSort;
                newCol.DataType = dataColumn.DataType;
                newCol.DisableHTMLRendering = dataColumn.DisableHTMLRendering;
                newCol.ExcelExportFormatString = dataColumn.ExcelExportFormatString;
                newCol.HeaderImage = dataColumn.HeaderImage;
                newCol.ImageLayout = dataColumn.ImageLayout;
                newCol.ReadOnly = dataColumn.ReadOnly;
                newCol.TextImageRelation = dataColumn.TextImageRelation;
                newCol.VisibleInColumnChooser = dataColumn.VisibleInColumnChooser;
                newCol.WrapText = dataColumn.WrapText;
                newCol.IsVisible = dataColumn.IsVisible;
                return newCol;
            }
        }
    }


  9. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 14 Feb 2011 Link to this post

    Hi guys,

    Exporting hierarchical data is not included in Q3 2010 release. We are working on this feature but I still cannot give you an exact time-frame for it.

    Versile, I have added additional Telerik points for posting the updated code of your solution.

    Regards,
    Martin Vasilev
    the Telerik team
    Q3’10 SP1 of RadControls for WinForms is available for download; also available is the Q1'11 Roadmap for Telerik Windows Forms controls.
  10. Nathaniel
    Nathaniel avatar
    12 posts
    Member since:
    Feb 2009

    Posted 23 Jun 2011 Link to this post

    Telerik doesn't always get a chance to come back to older threads. The ExportHierarchy feature is included in the latest release:

    http://www.telerik.com/community/forums/winforms/gridview/excel-export-for-hierarchial-data-in-the-radgridview.aspx#1611645
  11. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 28 Jun 2011 Link to this post

    Hi Nathaniel,

    Thank you for updating these old forum threads with the fact we have an hierarchical data export implemented in the latest release. I have updated your Telerik points for your participation.

    Kind regards,
    Martin Vasilev
    the Telerik team
    Q1’11 SP1 of RadControls for WinForms is available for download; also available is the Q2'11 Roadmap for Telerik Windows Forms controls.
  12. Victoria F
    Victoria F avatar
    46 posts
    Member since:
    May 2010

    Posted 12 Oct 2011 Link to this post

    Hello guys,

    I'm exporting hierarchical grid to Excel.
    The problem is that while exporting the grid is loosing the formatting of the columns.
    Is there any property to set that keep format the same as grid has?
    I have 50 columns with a different format .. do I have to format it twice for the grid view and for the Excel ?

    Thank you ,
    Victoria.
  13. Martin Vasilev
    Admin
    Martin Vasilev avatar
    1061 posts

    Posted 17 Oct 2011 Link to this post

    Hello Victoria,

    Please find the answer in the other forum thread, which you have created for the same question.

    All the best,
    Martin Vasilev
    the Telerik team

    Q2’11 SP1 of RadControls for WinForms is available for download (see what's new); also available is the Q3'11 Roadmap for Telerik Windows Forms controls.

Back to Top
UI for WinForms is Visual Studio 2017 Ready