Setting column width on exporting grids to Excel doesn't seem to work?

[DRS]

I am writing a function that takes a basic HTML table on the page, clones it, converts it to a kendo grid, so I can use the grid's export to Excel feature on any <table> markup regardless if it's a kendo grid or not.

I have it working, but one column is excessively wide in the export, I tried manually setting the column width to be smaller, but it won't get smaller. I have tried manually setting the column width to something huge to test, and that does work. So not sure why the smaller part isn't. 

This same column also has <br> tags that I had to convert to \n and then manually adjust the cell height which works fine.

Here is the my function being bound to the excelExport event:

kendoGridObject.bind("excelExport", function (e) {
            var sheet = e.workbook.sheets[0];
            var columns = e.workbook.sheets[0].columns;
            var defaultColumnWidth = 64;
            var columnMaxCharCounts = [];
            console.log("exporting to excel");
            for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
                var row = sheet.rows[rowIndex];
                var heightMultiplier = 1;

                for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex++) {
                    var cell = row.cells[cellIndex];
                    
                    var cellMaxCharLength;

                    if (cell.value && cell.value.toString().indexOf("<br>") >= 0) {

                        var stringsSplitByLineBreaks = cell.value.split("<br>");
                        //add to the height multiplier for each instance of line breaks found in the cell
                        heightMultiplier += cell.value.trim().indexOf("<br>") !== -1 ? stringsSplitByLineBreaks.length - 1 : 0;
                        cell.value = cell.value.trim().replaceAll("<br>", "\n");
                        cell.value = cell.value.trim();
                        cell.wrap = true;

                        //get the longest character count from all the strings for determining how wide the column should be
                        cellMaxCharLength = stringsSplitByLineBreaks.reduce(function (a, b) {
                            return a.length > b.length ? a.length : b.length;
                        }
                        );
                    } else {
                        cellMaxCharLength = cell.value.length;
                    }

                    var currentMaxCharCount = columnMaxCharCounts.find(function (x) { return x.ColumnIndex == cellIndex });
                    if (currentMaxCharCount == null || currentMaxCharCount == undefined) {
                        columnMaxCharCounts.push({ ColumnIndex: cellIndex, CharCount: cellMaxCharLength });
                    } else {
                        currentMaxCharCount.CharCount = cellMaxCharLength > currentMaxCharCount.CharCount ?
                            cellMaxCharLength : currentMaxCharCount.CharCount;
                    }
                }

                if (heightMultiplier != 1) {
                    row.height = heightMultiplier * 20; //the default excel row height
                }
            }

            //go over every column and set the new widths based on the max character count
            for (var i = 0; i < columns.length; i++) {
                var column = columns[i];
                var maxCharLength = columnMaxCharCounts.find(function (x) { return x.ColumnIndex == i });
                column.width = maxCharLength.CharCount + 10;
                console.log("Post-Change: Column width for column " + i + ": " + column.width);
            }
        });

Yet went i open the export the third column (index 2) looks like this:

I thought there might have been some extra white space or something, but i made sure to trim everything and my console outputs the following column widths when I export:

Post-Change: Column width for column 0: 49
Post-Change: Column width for column 1: 19
Post-Change: Column width for column 2: 51
Post-Change: Column width for column 3: 23
Post-Change: Column width for column 4: 24
Post-Change: Column width for column 5: 19

So the widths are being set, and that should show the 3rd column is roughly equal in with to the first, yet it's about 3-4x as wide in the excel file.

This is but one example, when using this across several different tables it can happen to 1 or mulitple columns. I thought it had to do with the fact that I am converting <br> to \n and that messed with the column widths, but I've seen it even happen on columns with no <br> tag.

What's going on and how do I make that third column smaller?

[Neli]

Hi,

I have already replied in the support thread regarding the same issue. However, for convenience as it could be helpful to the other users in the forum, I will paste my reply below as well:

 

I would suggest taking a look at the following forum thread where a similar issue is discussed:

https://www.telerik.com/forums/column-width-didn%27t-get-set-properly-in-excel-output#4362763

As suggested in the thread, please try to set the autoWidth property to false. Please note, that when the autoWidth is disabled it needs to be disabled for all the columns of the Grid and not for a single column. Otherwise, the width is still determined automatically.

With the above said I added the following line to the provided Dojo example:

 for (var i = 0; i < columns.length; i++) {
                var column = columns[i];
                var maxCharLength = columnMaxCharCounts.find(function (x) { return x.ColumnIndex == i });              
              	column.autoWidth = false;
                column.width = maxCharLength.CharCount + 10;              	
                console.log("Post-Change: Column width for column " + i + ": " + column.width);
            }

 

As you can see on the screencast linked here, after the mentioned change, the columns are skrinked in the exported Excel file. Here you will find the modified Dojo example. 

 

Regards,

Neli