Freeze/lock first row in datatables excel export file

Freeze/lock first row in datatables excel export file

sramer123sramer123 Posts: 2Questions: 1Answers: 0

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

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • sramer123sramer123 Posts: 2Questions: 1Answers: 0

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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

    Colin

  • Blackline1Blackline1 Posts: 5Questions: 1Answers: 0

    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!

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Nice, thanks for posting,

    Colin

This discussion has been closed.