We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on : November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Autofit RowHeight and Column Width On Export to Excel

Thread ID:

Created:

Updated:

Platform:

Replies:

131938 Feb 25,2015 07:31 AM UTC Aug 4,2017 12:56 PM UTC ASP.NET Core 6
loading
Tags: Grid
Chong Yee Mei
Asked On February 25, 2015 07:31 AM UTC

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

excel autofit
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 [Syncfusion]
Replied On February 26, 2015 09:27 AM UTC

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
Replied On February 26, 2015 11:28 AM UTC


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 [Syncfusion]
Replied On February 27, 2015 09:09 AM UTC

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
Replied On August 4, 2017 12:07 AM UTC

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 [Syncfusion]
Replied On August 4, 2017 12:43 PM UTC

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.  
 
 
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 [Syncfusion]
Replied On August 4, 2017 12:56 PM UTC

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.  
 
 
If you are reporting a different requirement, please get back to us with the details. 
 
Regards, 
Seeni Sakthi Kumar S. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;