Freeze/lock first row in datatables excel export file

sramer123

Hi, is it possible to freeze/lock first row in datatables excel export file? Any built-in option or custom code?

colin

Do you mean you want to create a new row to go at the start of the file? Or do you want a specific row in the table to go to the top?

Colin

sramer123

I mean exacly the "Freeze Top Row" function from excel (screenshot),

colin

You would need to understand that XML format for the spreadsheet for that, that's beyond what's offered in the standard options.

Colin

Blackline1

I managed to get something similar working and thought I'd share. In our case, we needed to freeze the first 3 columns of the exported table. After reviewing the XML before and after enabling 'Freeze Panes', we used the following excelHtml5 customize function:

customize: function(xlsx) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    var f1 = `<sheetViews>
                        <sheetView tabSelected="1" workbookViewId="0">
                               <pane xSplit="3" topLeftCell="D1" activePane="topRight" state="frozen" />
                               <selection pane="topRight" activeCell="D1" sqref="D1" />
                        </sheetView>
                   </sheetViews>`;
    $('worksheet', sheet).prepend(f1);
}

There may be a cleaner way to do this - I'm not sure if the <selection> node is needed, but it worked!

colin

Nice, thanks for posting,

Colin