CODEWITHSUNDEEP
excelkendo-uikendo-grid
John Lord
John Lord

Reputation: 2185

Kendo grid how do i auto-size an excel export row height?

I have a custom excel output class that I'm using to parse the grid, and in some cases replace the data in the grid with template data. In this particular instance, the data i want to output is multi-line. I have it working to that point but the exported sheet is one line high so you can't see lines two thru-seven in the field. desired result:

desired

actual result:

actual result

Here's a relevant section of my code. It's the parsing loop that applies the templates and strips html, but adds line breaks first.

if (me.ColumnTemplates && $.isArray(me.ColumnTemplates)) {
   for (let c = 0; c < me.ColumnTemplates.length; c++) {
       let ct = me.ColumnTemplates[c];
       if (ct.template(dr).includes("</br>")) {
           sheet.rows[r + 1].cells[ct.cellIndex - 1].wrap = true;
       }
       me.elem.innerHTML = ct.template(dr).replace(/<\/br>/g, "\n");
       sheet.rows[r + 1].cells[ct.cellIndex - 1].value = me.elem.textContent || me.elem.innerText || "";
    }
}

any help would be appreciated. I would like to either have a setting that makes this "just work" or have a way to compute the needed height and set it manually. Either is fine.

Upvotes: 1

Views: 2045

Answers (2)

Bhanu Pratap
Bhanu Pratap

Reputation: 1761

Set column width to auto

Solution 1 When kendo grid bound to data source in JavaScript/jQuery

excelExport: function(e) {
      var columns = e.workbook.sheets[0].columns;
      columns.forEach(function(column){
        // also delete the width if it is set
        delete column.width;
        column.autoWidth = true;
      });
    }

more details

Solution 2 When kendo grid is taking data source from action controller not bound to data source in jQuery then add event to call a JavaScript function on excel export

 function exportToExcelSheetColumnWidthChange(e) {
    var columns = e.workbook.sheets[0].columns;
    columns.forEach(function (column) {
        delete column.width;
        column.autoWidth = true;
    });
};

Add event to the kendo grid control

.Events(e => e.ExcelExport("exportToExcelSheetColumnWidthChange"))

Upvotes: 0

G_P
G_P

Reputation: 2168

I'm not aware of a way to auto-size it, but you can set row height it via sheets.rows.height:

<script>
    var workbook = new kendo.ooxml.Workbook({
      sheets: [{
          rows: [{
              cells: [{ value: "this row is 100px high" }],
              height: 100
          }, {
              cells: [{ value: "this row is 200px high" }],
              height: 200
          }]
      }]
    });
</script>

example found here

Updating your code to utilize each in the template html you can do something like the following:

if (me.ColumnTemplates && $.isArray(me.ColumnTemplates)) {
   for (let c = 0; c < me.ColumnTemplates.length; c++) {
       let ct = me.ColumnTemplates[c];
       if (ct.template(dr).includes("</br>")) {
           sheet.rows[r + 1].cells[ct.cellIndex - 1].wrap = true;
          
           sheet.rows[r + 1].height = (ct.template(dr).match(/<\/br>/g) || []).length * 20 + 20; //20 was default row height.
       }
       me.elem.innerHTML = ct.template(dr).replace(/<\/br>/g, "\n");
       sheet.rows[r + 1].cells[ct.cellIndex - 1].value = me.elem.textContent || me.elem.innerText || "";
    }
}

Upvotes: 1

Related Questions