grid view export to excel with groups

8 posts, 0 answers
  1. Derek Boseley
    Derek Boseley avatar
    10 posts
    Member since:
    Jun 2010

    Posted 25 Jun 2012 Link to this post

    Hi I have a Rad Grid View that I am exporting to excel using the code below. This works fine, but I would like the code to export the grouped data so that excel sees it as grouped data rather than static cells. Is there a way to achieve this?

    Thanks in advance.



    private void button4_Click(object sender, EventArgs e)
      {
          ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
          exporter.HiddenColumnOption = Telerik.WinControls.UI.Export.HiddenOption.DoNotExport;
          exporter.ExportVisualSettings = true;
          exporter.SheetName = "Films";
          string fileName = "C:\\ExportedData.xls";
          exporter.RunExport(fileName);
      }
  2. Jack
    Admin
    Jack avatar
    2335 posts

    Posted 27 Jun 2012 Link to this post

    Hi Derek,

    Currently, we do not support the row grouping feature in Excel and all group rows are exported as static cells. However, this is a nice feature and I added it in our issue tracking system. You can track its status by using the following link. I updated also your Telerik points.

    Should you have any other questions, do not hesitate to ask.
     
    Regards,
    Jack
    the Telerik team
    RadControls for WinForms Q2'12 release is now live! Check out what's new or download a free trial >>
  3. UI for WinForms is Visual Studio 2017 Ready
  4. Exchange
    Exchange avatar
    11 posts
    Member since:
    Oct 2012

    Posted 01 Oct 2012 Link to this post

    **Scratch That***
  5. Marcello
    Marcello avatar
    8 posts
    Member since:
    Dec 2008

    Posted 08 Sep 2014 in reply to Jack Link to this post

    Hi, there's something new on this topic?
  6. Dess
    Admin
    Dess avatar
    1601 posts

    Posted 11 Sep 2014 Link to this post

    Hello Marcello,

    Thank you for writing.

    You can track the feature request's progress, subscribe for status changes and add your vote/comment to it on the following link - feedback item. Since we have already introduced the Document processing library in the Telerik UI for WinForms suite, we will consider this feature in the next releases.

    I hope this information helps. Should you have further questions, I would be glad to help.

    Regards,
    Desislava
    Telerik
     
    Check out Telerik Analytics, the service which allows developers to discover app usage patterns, analyze user data, log exceptions, solve problems and profile application performance at run time. Watch the videos and start improving your app based on facts, not hunches.
     
  7. Andrea
    Andrea avatar
    52 posts
    Member since:
    Oct 2012

    Posted 19 Dec 2014 in reply to Derek Boseley Link to this post

    I was here to resolve a problem like your, then i remembered to have used the NPOI library in the past, so I used NPOI to do the job: NPOI is released under Apache License, and is available as a nuget package.

    Being Apache licensed if you use NPOI you should include a text file on your executable folder citing the authors of POI and NPOI.

    If the above is not a problem you can check out the attached file, I know it still need refactoring and is not well tested yet but might be a good starting point, I share that code with you or who other might be interested, as is with no guarantee or whatever: I am not linked with Telerik, I'm a simple user of their beautiful library, if you want to use my code to build your, do it.

    The output excel file is in compatible xls binary format, shows group, filtered and hidden columns. It try to set the column width equals to the column width of the grid and to use the same grid font.

    The algorithm to calculate the column width could be improved, for the moment the approach was to check out which effective measure shows excel for each measure used in input.

    Never tried on hi-res monitor, only tested for default telerik grid template, but for the moment few users are happy of the result.

    Usage:

    GridToExcel.ExportGrid(grid, fileName, sheetName);

    Code:
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using Telerik.WinControls.UI;
     
    namespace ExtendingGrid
    {
        public class GridToExcel
        {
            #region public static method
     
            public static void ExportGrid(RadGridView grid, string outputFile, string sheetName = "Sheet1")
            {
                var hssfworkbook = new HSSFWorkbook();
     
                (new GridToExcel(grid, hssfworkbook.CreateSheet(sheetName))).ExportGrid();
                FileStream file = new FileStream(outputFile, FileMode.Create);
                hssfworkbook.Write(file);
                file.Close();
                hssfworkbook.Dispose();
                hssfworkbook = null;
            }
     
            #endregion
     
            #region private
     
            #region constructor
            GridToExcel(RadGridView grid, Sheet sheet)
            {
                this.grid = grid;
                this.sheet = sheet;
                this.book = sheet.Workbook;
                headerStyle = LoadHeaderStyle();
                groupHeaderStyle = LoadGroupHeaderStyle();
                dateStyle = LoadDateStyle();
            }
            #endregion
     
            #region immutable instance fields
            readonly RadGridView grid;
            readonly Sheet sheet;
            readonly Workbook book;
            readonly CellStyle headerStyle;
            readonly CellStyle groupHeaderStyle;
            readonly CellStyle dateStyle;
            #endregion
     
            #region helper methods
            void ExportGrid()
            {
                int i = sheet.LastRowNum;
                var row = sheet.CreateRow(i++);
                int j = 0;
     
                sheet.Workbook.GetFontAt(0).FontName = grid.Font.Name;
                sheet.Workbook.GetFontAt(0).FontHeightInPoints = TrimFloat(grid.Font.SizeInPoints);
     
                foreach (var col in grid.Columns)
                {
                    if (!ColumnVisiblePredicate(col))
                    {
                        continue;
                    }
                    var cell = row.CreateCell(j);
                    cell.SetCellValue(col.HeaderText);
                    cell.CellStyle = headerStyle;
                    sheet.SetColumnWidth(j, (int)(CharFromPixel(col.Width) * 256));
                    j++;
                }
     
                IterateGridRows(grid.ChildRows, i);
     
            }
     
            bool ColumnVisiblePredicate(GridViewDataColumn col)
            {
                return (col.IsVisible) && (!(col is GridViewCommandColumn));
            }
     
            void SetDataValue(Cell c, object o)
            {
                if (o == null)
                {
                    c.SetCellValue("");
                    return;
                }
                Type t = TypeFromNullableOrStandardType(o.GetType());
                if (t == typeof(DateTime))
                {
                    c.CellStyle = dateStyle;
                    c.SetCellValue((DateTime)o);
                    return;
                }
                TypeCode typeCode = Type.GetTypeCode(t);
     
                if ((int)typeCode >= 5 && (int)typeCode <= 15)
                {
                    c.CellStyle.DataFormat = 1;
                    c.SetCellValue(Convert.ToDouble(o));
                    return;
                }
                c.SetCellValue(o.ToString());
            }
     
            private int IterateGridRows(GridViewChildRowCollection childRows, int i)
            {
                Row row;
                foreach (var grow in childRows)
                {
                    if (grow is GridViewGroupRowInfo)
                    {
                        GridViewGroupRowInfo group = grow as GridViewGroupRowInfo;
                        row = sheet.CreateRow(i++);
                        Cell cell;
                        int j = 0;
                        foreach (var col in grid.Columns)
                        {
                            if (!ColumnVisiblePredicate(col))
                            {
                                continue;
                            }
                            cell = row.CreateCell(j);
                            cell.SetCellValue(col.HeaderText);
                            cell.CellStyle = groupHeaderStyle;
                            j++;
                        }
                        var cellRange = new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, j - 1);
                        sheet.AddMergedRegion(cellRange);
                        SetDataValue(row.GetCell(0), group.HeaderText);
                        int oldi = i;
                        i = IterateGridRows(group.ChildRows, i);
                        if (oldi != i)
                        {
                            sheet.GroupRow(oldi, i);
                        }
                        if (!group.IsExpanded)
                        {
                            sheet.SetRowGroupCollapsed(oldi, true);
                        }
                    }
                    else
                    {
                        row = sheet.CreateRow(i++);
                        int j = 0;
                        foreach (var col in grid.Columns)
                        {
                            if (!col.IsVisible)
                            {
                                continue;
                            }
                            if ((col is GridViewCommandColumn))
                            {
                                continue;
                            }
                            var cell = row.CreateCell(j);
                            SetDataValue(cell, grow.Cells[col.Index].Value);
                            j++;
                        }
                    }
                }
                return i;
            }
            #endregion
     
            #region Style Adapters
     
            private CellStyle LoadDateStyle()
            {
                var style = book.CreateCellStyle();
                style.DataFormat = 0xe;
                return style;
            }
     
            private CellStyle LoadHeaderStyle()
            {
                var style = book.CreateCellStyle();
     
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                style.VerticalAlignment = VerticalAlignment.CENTER;
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PALE_BLUE.index;
                style.FillPattern = FillPatternType.SOLID_FOREGROUND;
                style.BorderBottom = CellBorderType.THIN;
                style.BorderRight = CellBorderType.THIN;
                return style;
            }
     
            private CellStyle LoadGroupHeaderStyle()
            {
                var style = book.CreateCellStyle();
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
                style.VerticalAlignment = VerticalAlignment.CENTER;
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PALE_BLUE.index;
                style.FillPattern = FillPatternType.SOLID_FOREGROUND;
                style.BorderBottom = CellBorderType.THIN;
                style.BorderRight = CellBorderType.THIN;
                return style;
            }
     
            #endregion
     
            #region utilities
     
            short TrimFloat(float f)
            {
                if (f > short.MaxValue)
                    return short.MaxValue;
                if (f < short.MinValue)
                    return short.MinValue;
                return (short)f;
            }
     
            static Type TypeFromNullableOrStandardType(Type t)
            {
                if (t.IsGenericType &&
                    t.GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    t = t.GetGenericArguments()[0];
                }
                return t;
            }
     
            private float CharFromPixel(int pixel)
            {
                //substitute this one with your one if you are not happy
                return CharFromPixelUsingStatisticData(sheet, pixel);
            }
            #endregion
     
            #region EXCEL EMPYRICAL METRICS
     
            private static float CharFromPixelUsingStatisticData(Sheet sheet, int pixel)
            {
                //experimentally found that there is always a 5 pixel difference between excel and npoi output, that constant increase with font size
                //but still did not found the correct formula to calculate
                pixel += sheet.Workbook.GetFontAt(0).FontHeightInPoints - 5;
                Workbook wb = sheet.Workbook;
     
                NPOI.SS.UserModel.Font defaultFont = wb.GetFontAt((short)0);
     
                ExcelMetricFont emf;
                if (!ExcelMetricFont.ExcelMetric.TryGetValue(defaultFont.FontName, out emf))
                {
                    emf = ExcelMetricFont.ExcelMetric["Arial"];
                }
     
                return emf.GetCharSize(defaultFont.FontHeightInPoints, pixel);
            }
     
            class ExcelMetricFont
            {
                private readonly string fontName;
     
                public string FontName
                {
                    get
                    {
                        return this.fontName;
                    }
                }
     
                readonly List<ExcelMetricFontSize> Sizes;
     
                public ExcelMetricFont(string font, IEnumerable<ExcelMetricFontSize> fontSizes)
                {
                    this.fontName = font;
                    this.Sizes = fontSizes.OrderBy(a => a.FontSize).Where(a => a.FontSize > 0).ToList();
                    if (this.Sizes.Count == 0)
                    {
                        throw new InvalidOperationException("The Size list must have at least one value");
                    }
                }
     
                public float GetCharSize(int fontSize, int pixel)
                {
                    int index = Sysimex.Algorithms.Algorithms.BinarySearch(this.Sizes, fontSize, (s, sz) => s.FontSize - sz);
                    if (index < 0)
                    {
                        int next = ~index; //precise match not found, which is the element greather than pixel pixels?
                        if (next >= this.Sizes.Count)
                        {
                            next = this.Sizes.Count - 1;
                        }
     
                        if (next <= 0)//we suppose that Measures has always at least 1 measure
                        {
                            return this.Sizes[0].GetCharSize(pixel);
                        }
     
                        //now let's interpolate with simple linear algorithm
                        var x0 = this.Sizes[next - 1].FontSize;
                        var x1 = this.Sizes[next].FontSize;
                        var x = fontSize;
                        var y0 = this.Sizes[next - 1].GetCharSize(pixel);
                        var y1 = this.Sizes[next].GetCharSize(pixel);
     
                        return (y1 - y0) * (x - x0) / (x1 - x0) + y0;
                    }
                    return this.Sizes[index].GetCharSize(pixel);
                }
     
                public static readonly Dictionary<string, ExcelMetricFont> ExcelMetric; //empirical values from excel
     
                static ExcelMetricFont()
                {
                    //this data is used to calculate dimensions in excel units, if you want a compact solution you can set 7pixel=1character and avoid use this statistical info
                    ExcelMetric = new Dictionary<string, ExcelMetricFont>(StringComparer.OrdinalIgnoreCase);
     
                    ExcelMetric.Add("Arial", new ExcelMetricFont("Arial",
                        new ExcelMetricFontSize[]
                            {
                                new ExcelMetricFontSize(10, new ExcelMetricFontSizeValue[]
                                {
                                    new ExcelMetricFontSizeValue(12, 1.0f),
                                    new ExcelMetricFontSizeValue(15, 1.43f),
                                    new ExcelMetricFontSizeValue(19, 2f),
                                    new ExcelMetricFontSizeValue(23, 2.57f),
                                    new ExcelMetricFontSizeValue(26, 3.0f),
                                    new ExcelMetricFontSizeValue(139974, 20000f),
                                }),
                                new ExcelMetricFontSize(12, new ExcelMetricFontSizeValue[]
                                {
                                    new ExcelMetricFontSizeValue(12, 0.75f),
                                    new ExcelMetricFontSizeValue(16, 1f),
                                    new ExcelMetricFontSizeValue(25, 2f),
                                    new ExcelMetricFontSizeValue(34, 3f),
                                    new ExcelMetricFontSizeValue(43, 4f),
                                    new ExcelMetricFontSizeValue(52, 5f)
                                })
                            }));
     
                    ExcelMetric.Add("Segoe UI", new ExcelMetricFont("Segoe UI",
                        new ExcelMetricFontSize[]
                            {
                                new ExcelMetricFontSize(8, new ExcelMetricFontSizeValue[]
                                {
                                    new ExcelMetricFontSizeValue(11, 1.0f),
                                    new ExcelMetricFontSizeValue(17, 2f),
                                    new ExcelMetricFontSizeValue(23, 3.0f),
                                    new ExcelMetricFontSizeValue(29, 4.0f)
                                }),
                                new ExcelMetricFontSize(10, new ExcelMetricFontSizeValue[]
                                {
                                    new ExcelMetricFontSizeValue(12, 0.75f),
                                    new ExcelMetricFontSizeValue(19, 2f),
                                    new ExcelMetricFontSizeValue(26, 3f),
                                    new ExcelMetricFontSizeValue(139974, 20000f),
                                }),
                                new ExcelMetricFontSize(12, new ExcelMetricFontSizeValue[]
                                {
                                    new ExcelMetricFontSizeValue(12, 0.75f),
                                    new ExcelMetricFontSizeValue(16, 1f),
                                    new ExcelMetricFontSizeValue(25, 2f),
                                    new ExcelMetricFontSizeValue(34, 3f),
                                    new ExcelMetricFontSizeValue(43, 4f),
                                    new ExcelMetricFontSizeValue(52, 5f)
                                })
                            }));
                }
            }
     
            class ExcelMetricFontSize
            {
                readonly int fontSize;
     
                public int FontSize
                {
                    get
                    {
                        return this.fontSize;
                    }
                }
     
                /// <summary>
                /// This list memorize an empirical set of excel column width in pixel and in chars
                /// </summary>
                readonly List<ExcelMetricFontSizeValue> measures;
     
                public ExcelMetricFontSize(int fontSize, IEnumerable<ExcelMetricFontSizeValue> measures)
                {
                    this.measures = measures.OrderBy(a => a.Pixel).Where(a => a.Pixel > 0).ToList();
                    if (this.measures.Count == 0)
                    {
                        throw new InvalidOperationException("The measure list must have at least one value");
                    }
                    this.fontSize = fontSize;
                }
     
                /// <summary>
                /// convert pixel to excel column width expressed in excel chars
                /// </summary>
                /// <param name="pixel"></param>
                /// <returns></returns>
                public float GetCharSize(int pixel)
                {
                    int index = Sysimex.Algorithms.Algorithms.BinarySearch(this.measures, pixel, (s, px) => s.Pixel - px);
                    if (index < 0)
                    {
                        int next = ~index; //precise match not found, which is the element greather than pixel pixels?
                        if (next >= this.measures.Count)
                        {
                            next = this.measures.Count - 1;
                        }
     
                        if (next < 0)//we suppose that Measures has always at least 1 measure
                        {
                            return this.measures[0].Chars;
                        }
     
                        //now let's interpolate with simple linear algorithm
                        var x0 = this.measures[next - 1].Pixel;
                        var x1 = this.measures[next].Pixel;
                        var x = pixel;
                        var y0 = this.measures[next - 1].Chars;
                        var y1 = this.measures[next].Chars;
     
                        return (y1 - y0) * (x - x0) / (x1 - x0) + y0;
                    }
                    return this.measures[index].Chars;
                }
            }
     
            class ExcelMetricFontSizeValue
            {
                public readonly int Pixel;
                public readonly float Chars;
     
                public ExcelMetricFontSizeValue(int pixel, float chars)
                {
                    this.Pixel = pixel;
                    this.Chars = chars;
                }
            }
     
            #endregion
     
            #endregion
        }
    }

    Best Regards
    Andrea
  8. Longnd
    Longnd avatar
    2 posts
    Member since:
    Jun 2014

    Posted 26 May 2015 in reply to Andrea Link to this post

    Hi Andrea,

    Thank you very much. I will try your code and hope to have good result!

  9. Dess
    Admin
    Dess avatar
    1601 posts

    Posted 28 May 2015 Link to this post

    Hello guys,

    Thank you for writing.

    @Andrea  I have updated your Telerik points for community effort.

    Regards,
    Dess
    Telerik
    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 Feedback Portal and vote to affect the priority of the items
Back to Top
UI for WinForms is Visual Studio 2017 Ready