Autofit RowHeight and Column Width On Export to Excel

[Chong Yee Mei]

Hi,

Query1:

I am trying to autofit the rowheight and columnwidth when exporting the ggc data to excel, however using the code below, I find that the file size will increase drastically from 17kb to 2MB after i applied the autofit, and also that the performance is very bad. 

Query2:

I noticed that there will have 1 empty row between the column header and the data, how do i get rid of this row.

Thanks and best regards,

Chong

public static void ExporttoExcel(GridGroupingControl gridGroupingControl)

{

                GridExcelExport excel = new GridExcelExport(gridGroupingControl, "excel.xlsx", ConverterOptionsExt.All, Syncfusion.XlsIO.ExcelVersion.Excel2013);

                excel.GridGroupingControl.TopLevelGroupOptions.ShowCaption = false;

                excel.ExportNestedTable = true;

                excel.FormatExcelCellHandler += excel_FormatExcelCellHandler;

                excel.Export();

}

private static void excel_FormatExcelCellHandler(object sender, ExcelExportEventArgs e)

        {

                if (e.RowElement.Kind == DisplayElementKind.ColumnHeader)

                {

                    e.ExcelCell.AutofitColumns();

                }

                if (e.RowElement.Kind == DisplayElementKind.Record)

                {

                    e.ExcelCell.WrapText = true;

                    if (e.ExcelCell.Column == ((GridExcelExport)sender).ColCount)

                    {

                        e.ExcelCell.EntireColumn.AutofitRows(); //trigger this method on every last cell in a row will causes the bottleneck, tried to only trigger this on the cell that is on last row and last column                                                                                               //but couldnt find any easy way to get the exac

                    }

                }

                e.ExcelCell.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.White;

                e.ExcelCell.CellStyle.IncludeBorder = true;

                e.ExcelCell.BorderAround();

        }

[Shanmugaraja K]

Hi Chong,

Thanks for using Syncfusion products.

Query 1: I am trying to auto fit the rowheight and columnwidth when exporting the ggc data to excel?

We have analysed your given code snippet and we have found that you have been used AutofitRows for EntireColumn property, which is the cause of your reported issue. We suggest you to use AutofitRows property only for ExcelCell to resolve this performance issue. Please refer the below code snippets.

[CS]

private static void excel_FormatExcelCellHandler(object sender, ExcelExportEventArgs e)

        {

            if (e.RowElement.Kind == DisplayElementKind.ColumnHeader)

            {

                e.ExcelCell.AutofitColumns();

            }

            if (e.RowElement.Kind == DisplayElementKind.Record)

            {

                e.ExcelCell.WrapText = true;

                if ((e.ExcelCell.LastRow == ((GridExcelExport)sender).GridGroupingControl.Table.DisplayElements.Count - 1) && (e.ExcelCell.LastColumn == ((GridExcelExport)sender).ColCount)) // this condition satisfies while this on the cell that is on last row and last column.

                {

                    e.ExcelCell.AutofitRows();// this satisfies your requirement with better performance.

                }

            }

            e.ExcelCell.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.White;

            e.ExcelCell.CellStyle.IncludeBorder = true;

            e.ExcelCell.BorderAround();

        }

Also, we have created simple sample with modified code snippet and the same can be downloaded from the following link.

Sample: http://www.syncfusion.com/downloads/support/directtrac/118321/groupdroparea1-1982619337.zip

Query 2: Empty row added between column header and the data.

Before we start to analyse this query could you please provide more details about this issue. Did you enable Filtering or Add New property in your project? Could you please let us know that which scenario you have facing this issue and please share more code snippet with us regarding this issue.

 

Please let us know if you need further assistance.

Regards,

Shanmugaraja K

[Chong Yee Mei]

Hi Shanmugaraja K,

Thanks for the prompt reply,

I have tried the code you have send to me and it works fine in identifying the last  row and last column, however by using the code snippet below

e.ExcelCell.AutofitRows();  //this only auto fit the last row of data exported

e.ExcelCell.EntireColumn.AutofitRows(); //this will auto fit all rows but the file size increase drastically.

just replace the code below in the sample you sent to me and you will be able to simulate the issue

private void GetData()

        {

            List<MyClass> list = new List<MyClass>();

            for (int i = 0; i < 2; i++)

            {

                list.Add(new MyClass(1 + i, Convert.ToDateTime("10/10/2005 02:06:04 AM"), 15123 + i, "X" , 45879 + i));

                list.Add(new MyClass(1 + i, Convert.ToDateTime("10/10/2005 02:06:04 AM"), 15123 + i, "AAA" , 45879 + i));

                list.Add(new MyClass(1 + i, Convert.ToDateTime("10/10/2005 02:06:04 AM"), 15123 + i, "BBBB" , 45879 + i));

                list.Add(new MyClass(1 + i, Convert.ToDateTime("10/10/2005 02:06:04 AM"), 15123 + i, "CCCC" , 45879 + i));

                list.Add(new MyClass(1 + i, Convert.ToDateTime("10/10/2005 02:06:04 AM"), 15123 + i, "DDDD" , 45879 + i));

                list.Add(new MyClass(1 + i, Convert.ToDateTime("10/10/2005 02:06:04 AM"), 15123 + i, "EEEE" , 45879 + i));

                list.Add(new MyClass(1 + i, Convert.ToDateTime("10/10/2005 02:06:04 AM"), 15123 + i, "FFF", 45879 + i));

                list.Add(new MyClass(1 + i, Convert.ToDateTime("10/10/2005 02:06:04 AM"), 15123 + i, "GGG" , 45879 + i));

                list.Add(new MyClass(1 + i, Convert.ToDateTime("10/10/2005 02:06:04 AM"), 15123 + i, "SSS", 45879 + i));

                list.Add(new MyClass(1 + i, Convert.ToDateTime("10/10/2005 02:06:04 AM"), 15123 + i, "X" , 45879 + i));

                list.Add(new MyClass(1 + i, Convert.ToDateTime("10/10/2005 02:06:04 AM"), 15123 + i, "X<br/>X<br/>X", 45879 + i));

            }

            this.GridgroupingControl1.DataSource = list;

            this.GridgroupingControl1.DataBind();

        }

While for Query 2, i have resolve it by removing the filter bar.

Thanks and best regards,

Chong

[Shanmugaraja K]

Hi Chong,

Thanks for your update.

We would like to let you know that your requirement has been achieved by using UsedRange property. Please refer the below code snippets.

[CS]

private static void excel_FormatExcelCellHandler(object sender, ExcelExportEventArgs e)

        {

            if (e.RowElement.Kind == DisplayElementKind.ColumnHeader)

            {

                e.ExcelCell.AutofitColumns();

            }

            if (e.RowElement.Kind == DisplayElementKind.Record)

            {

                e.ExcelCell.WrapText = true;

                 if ( (e.ExcelCell.LastColumn == ((GridExcelExport)sender).ColCount))

                {

                    e.ExcelCell.Worksheet.UsedRange.AutofitRows();

                }

            }

            e.ExcelCell.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.White;

            e.ExcelCell.CellStyle.IncludeBorder = true;

            e.ExcelCell.BorderAround();

        }

Also, we have modified the sample with your suggestion and  this code snippets and the same can be downloaded from the following link.

Sample: http://www.syncfusion.com/downloads/support/directtrac/118321/groupdroparea1_(2)-927697222.zip

Please let us know if you need further assistance.

Regards

Shanmugaraja K

[Jair Piedrahita]

Hello there, 

I am trying to get the same result but using ASP. Net Core. Can you please give me a hint about it. 

Thanks 

Jair

[Seeni Sakthi Kumar Seeni Raj]

Hi Jair,  

 

We suspect that you are looking for the Asp.Net core Grid to rendered with an AutoFit alignment. This can be achieved by the IsAutoFit property of the GridExcelExport class which has been used to define the Grid Excel Exporting Properties. This property will fit the Table to the given space. Refer to the following code example. 

 

Please make a note that the IsAutoFit property is set to true by default. 

 

        public ActionResult ExportToExcel(string GridModel) 

        { 

            ExcelExport exp = new ExcelExport(); 

            var DataSource = order; 

            GridProperties gridProp = ConvertGridObject(GridModel); 

            GridExcelExport excelExp = new GridExcelExport(); 

            excelExp.IsAutoFit = false; 

            excelExp.FileName = "Export.xlsx";  

            excelExp.Excelversion = ExcelVersion.Excel2010; 

            excelExp.Theme = "flat-saffron"; 

            return exp.Export(gridProp, DataSource, excelExp); 

        } 

 

 

We have prepared a sample that can be downloaded from the following location.  

 

Sample: http://www.syncfusion.com/downloads/support/forum/131938/ze/WebApplication13-2013108155.zip 

 

If you are reporting a different requirement, please get back to us with the details. 

 

Regards, 

Seeni Sakthi Kumar S. 

[Seeni Sakthi Kumar Seeni Raj]

Hi Jair,  

 

Please follow modified response.  

 

We suspect that you are looking for the Asp.Net core Grid to rendered with an AutoFit alignment. This can be achieved by the IsAutoFit property of the GridExcelExport class which has been used to define the Grid Excel Exporting Properties. This property will fit the Table to the given space. Refer to the following code example. 

 

Please make a note that the IsAutoFit property is set to true by default. 

 

        public ActionResult ExportToExcel(string GridModel) 

        { 

            ExcelExport exp = new ExcelExport(); 

            var DataSource = order; 

            GridProperties gridProp = ConvertGridObject(GridModel); 

            GridExcelExport excelExp = new GridExcelExport(); 

            excelExp.IsAutoFit = true; 

            excelExp.FileName = "Export.xlsx";  

            excelExp.Excelversion = ExcelVersion.Excel2010; 

            excelExp.Theme = "flat-saffron"; 

            return exp.Export(gridProp, DataSource, excelExp); 

        } 

 

We have prepared a sample that can be downloaded from the following location.  

 

Sample: http://www.syncfusion.com/downloads/support/forum/131938/ze/WebApplication13-2013108155.zip 

 

If you are reporting a different requirement, please get back to us with the details. 

 

Regards, 

Seeni Sakthi Kumar S.