How to set autowidth for column on server exported Excel

[AM]

Hello,

We are exporting data to Excel server-using something similar to:

https://docs.telerik.com/aspnet-core/html-helpers/data-management/grid/export/server-export

How do we set the column to autowidth=true or to adjust the width of the column to the size of the content?

Thanks

[Stoyan]

Hi Anya,

I am researching how to adjust the width of the Grid's Server Exported column. 

This involves combining the functionality of the Grid with the Telerik Document Processing library.

I'll consult the DPL team on their insights and will contact you again as soon as possible to let you know of our findings.

[Stoyan]

Hello Anya,

Here are the steps how to determine the width of the columns automatically in the export controller of the Grid Server Export Demo:

1. You need to use a text measurer to evaluate the width of the cell automatically.

   SpreadTextMeasurerBase fixedTextMeasurer = new SpreadFixedTextMeasurer();
   SpreadExtensibilityManager.TextMeasurer = fixedTextMeasurer;

2. To enable measuring of the text you need to get the font file that is going to be used in the export. This will enable the DPL to determine the width of each glyph.

   public class FontsProvider : Telerik.Windows.Documents.Extensibility.FontsProviderBase
       {
           public override byte[] GetFontData(FontProperties fontProperties)
           {
               string fontFileName = fontProperties.FontFamilyName + ".ttf";
               string fontFolder = Environment.GetFolderPath(Environment.SpecialFolder.Fonts);
               string targetPath = Path.Combine(fontFolder, fontFileName);
   
               DirectoryInfo directory = new DirectoryInfo(fontFolder);
               FileInfo[] fontFiles = directory.GetFiles("*.ttf");
               if (fontFiles.Any(s => s.Name.Equals(fontFileName, StringComparison.InvariantCultureIgnoreCase)))
               {
                   using (FileStream fileStream = File.OpenRead(targetPath))
                   {
                       using (MemoryStream memoryStream = new MemoryStream())
                       {
                           fileStream.CopyTo(memoryStream);
                           return memoryStream.ToArray();
                       }
                   }
               }
               else
               {
                   throw new FileNotFoundException();
               }
               return null;
           }
       }

   FontsProviderBase fontsProvider = new FontsProvider();
   FixedExtensibilityManager.FontsProvider = fontsProvider;

3.   Now you can iterate the data set and create a dictionary with the name of the fields/columns and calculate their width.
   (to simplify the sample I've hardcoded only 'ShipName' which is the widest column of the Demo to be added to the dictionary)

    Dictionary<string, double> columnNameToWidth = new Dictionary<string, double>();

         foreach( var row in rowsData)
               {
                   var text = row.ShipName;
                   var columnName = "Ship Name";
   
                   var width = CellContentSizeHelper.GetCellContentSize(text, cellFormat).Width;
   
                   if (!columnNameToWidth.ContainsKey(columnName))
                   {
                       columnNameToWidth[columnName] = width;
                   }
   
                   var maxWidth = Math.Max(columnNameToWidth[columnName], width);
                   columnNameToWidth[columnName] = maxWidth;
              }

4. Modify the ChangeColumnStyle method to use the auto determined width.

   Action<ExportColumnStyle> columnStyle = new Action<ExportColumnStyle>((columnStyle) => ChangeColumnStyle(columnStyle, columnNameToWidth));

    private void ChangeColumnStyle(ExportColumnStyle e, Dictionary<string, double> columnNameToWidth)
           {
               double width = e.Name == "Product name" || e.Name == "Category Name" ? 250 : 100;
   
               if (columnNameToWidth.ContainsKey(e.Name))
               {
                  width = columnNameToWidth[e.Name];
               }
   
               e.Column.SetWidthInPixels(width);
           }

5. Modify the ChangeCellStyle to expose the cellFormat in the scope of the Action method.

   Action<ExportCellStyle> cellStyle = new Action<ExportCellStyle>((cellStyle) => ChangeCellStyle(cellStyle, cellFormat, headerCellFormat));

               var headerForeColor = SpreadThemableColor.FromRgb(50, 54, 58);
               var cellForeColor = SpreadThemableColor.FromRgb(214, 214, 217);
               var headerFillColor = new SpreadColor(93, 227, 0);
               var cellFillColor = new SpreadColor(50, 54, 58);
               SpreadCellFormat cellFormat = new SpreadCellFormat
               {
                   ForeColor = cellForeColor,
                   IsItalic = true,
                   VerticalAlignment = SpreadVerticalAlignment.Center,
                   WrapText = false,
                   Fill = SpreadPatternFill.CreateSolidFill(cellFillColor)
               };
               SpreadCellFormat headerCellFormat = new SpreadCellFormat
               {
                   ForeColor = headerForeColor,
                   IsItalic = true,
                   VerticalAlignment = SpreadVerticalAlignment.Center,
                   WrapText = true,
                   Fill = SpreadPatternFill.CreateSolidFill(headerFillColor)
               };

For your convenience I am attaching the modified .cs file of the Controller to review. If you have the Demo project installed locally you can replace the file and review its behavior right away.

Regards,
Stoyan
Progress Telerik

[AM]

Thanks, we will give this a try,