How to autofit column width when exporting DataTables to Excel

DevTN

Hello everyone, I know this question has been asked few times here but couldn't figure out a working solution.

My columns are set dynamically based on user selection so I don't want to set width manually to each column.

I created a function to customize my exported file

customize: function(xlsx) { 
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
        var col = $('col', sheet);
        var width_details = [];
        $('tr:nth-child(1) > td').each(function(){
        width_details.push($(this).width()); });
        var q = 0;
        col.each(function () {
        $(this).attr('width', width_details[q]);
        q++; });
}

I had an idea to check the width of each column in my table and store it in an array then while the data is exported to Excel I loop through my array and set the same width to excel columns.
The issue is the width in DataTables and Excel doesn't seem to be calculated the same way.

Here is a demo example https://jsfiddle.net/q4j8khLf/

Can you please suggest me what should i add more to autofit the columns width in the exported Excel file ? Thank you very much.

colin

I'm not clear what you're trying to autofit to? By standard DataTables will export with optimum width for the column. For Excel, I would've thought this would be the preference, unless I'm missing something.

Colin

DevTN

Thanks for your time. check please the first image

that's how DataTables exports by default. there is much space in column width not needed.

Now the same excel file if i apply autofit on it you see how the columns look like now.

The columns get the exact width of the largest cell. I need to do that.
is it clear now ? Thanks again.

kthorngren

The ExcelHTML5 docs button uses the XML Spreadsheet spec.

According to this SO thread auto-fit is is implemented by MS Excel and not apart fo the XML SS spec. The thread gives an example of how one might calculate these values.

Kevin

DevTN

it's clear now. Thank you very much

DeboraMezzalira

Hello DevTN, is there an update for this issue? I need to autoadjust the widths for my columns... you solved?