grid view export to excel with groups

[Derek Boseley]

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);
  }

[Jack]

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

[Exchange]

**Scratch That***

[Marcello]

Hi, there's something new on this topic?

[Dess | Tech Support Engineer, Principal]

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

 

 

[Andrea]

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

[Longnd]

Hi Andrea,

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

[Dess | Tech Support Engineer, Principal]

Hello guys,

Thank you for writing.

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

Regards,
Dess
Telerik