Datatables Table tools xls export issue in windows.

k4rl85

Microsoft Excel 2011

allan

Very odd - that's what I'm using. I'm not sure why it wouldn't open for you but will for me. What browser / version are you using when creating the XLSX file?

Allan

jyotijariwal

@allan My export through buttons extension is working fine. I am having issue in exporting phone number.
When I export phone number with value 012345689 then it becomes 12345689. My zero is truncated. As this is phone number it should be imported with zero.
Do you have any solution for this?

allan

Currently no - sorry. I'll need to add an option to force writing a string rather than a number.

Allan

jyotijariwal

@allan ok thnx no problem. Pls inform me when you will add an option for this.

allan

I'm not sure at the moment I'm afraid. Keep an eye on the releases feed for the new Buttons releases.

Allan

jyotijariwal

@allan Can you tell me in buttons extension do we have cellRender function like we had in TableTools extension. And pls suggest some solution how I can render each cell data before export to excel or csv. I need to concatenate on string to a cell and then send it to buttons extension to export.

allan

Can you tell me in buttons extension do we have cellRender function like we had in TableTools extension

No but there is an orthogonal option which allows you to make use of DataTables orthogonal data. See the buttons.exportData() options and also csvHtml5 and the other buttons for how to customise the export data.

Allan

jyotijariwal

@allan do you know how we can give scrollX true after intializing datatable?

allan

You cannot alter the scrolling state of the table after initialisation. It is an initialisation time only option.

Allan

jyotijariwal

@allan do you know how can I export a value that has comma in it in CSV. I cannot use any other delimiter. When I export in csv it creates an extra column also shifits the data to other cell. Can you tell me some solution?

allan

Please try using the nightly version if you haven't already. It should work there. If not, please link to a test case showing the issue.

Allan

jyotijariwal

@allan I want to change extension of file exported as xlsx to xls. But when I change the extension to xls the file is downloaded as zip file. Do you know whats the issue? I am defining it like -

var buttons = new $.fn.dataTable.Buttons( table, {
buttons: [
{
extend: 'csv',
exportOptions: {
columns: ':visible',
},
},
{
extend: 'excel',
exportOptions: {
columns: ':visible',
},
extension: 'xls'
}
]
} );

allan

Daft question - but why would you want to do that? It is not a .xls file that is created, it is a xlsx formatted file.

If you want to set an extension you need to include the . - e.g.:

{ extend: 'excelHtml5', extension: '.xls' },

Allan

jyotijariwal

@allan

I have datable that gets data through ajax and now the export buttons are not getting all data. How can I work to export all data?

And on that datatable I have used other extensions also - Colvis, Colreorder.

allan

I would need a link to the page to see and debug the issue.

Allan

jyotijariwal

@allan

The main issue my datatables is I am using all plugins colvis, colreorder and csv, excel import.

The records in tables are above 5,000. I cannot use server side processing as it will become complicated to make all the plugins run and get the data through ajax.

The page is taking way too much time for loading.

Can you suggest me best possible solution for this? I have been using data tables for all my projects and was always satisfied with its performance.

But now the page is taking very much time. Pls suggest me some solution.

allan

If there were some magic goFaster: true option in DataTables, I would enable it by default :-).

Best I can suggest is that you read the speed FAQ.

Allan

TechCoder

Just started using datatables and, although struggling through all the settings, am really appreciating the quality of the package.

However, as k4rl85, I have 0% success with the Excel download - even from both example pages.
https://datatables.net/extensions/buttons/examples/flash/simple.html
https://datatables.net/extensions/buttons/examples/html5/simple.html

Both result in blank spreadsheets.

Running Win 10, with OpenOffice.

CSV, PDF and all other HTML5 export functions work great - only issue is with Excel.

We just put datatables into a pretty big app for a client and (sadly) they want Excel export... Right now, they can/will use CSV, but they are pushing for 'everything' to work (you know how that is :)

Thanks for an excellent package on tables - one of my 'nightmare' things for years!
(and, certainly you have my 'vote' for a goFaster: true as well as goFaster: now options!

allan

OpenOffice

That's the issue. Its a known bug in OpenOffice. There is some discussion about it in this thread.

Given that OOo development has very nearly stalled with most developers switching to LibreOffice, I'm not sure that bug will ever be fixed.

I do plan to rewrite the XSLX output to allow formatting in the next few months, which might help address this, but the file that is created by Buttons is a valid XSLX file and the error here is in OOo I'm afraid.

Allan

p.s. A goFaster: 'ultraMegaFast' option is something else I'd like to work on ;-)

jyotijariwal

@allan
Can you tell me is there any of saving the state of sorting and pagination on my ajax datatable?

allan

stateSave :-). Just typing state save into the search box at the top of the page will find that and other pages on the topic.

Allan

jyotijariwal

@allan

I am using datatables button extension for export data in excel.

I am exporting date field in excel. But that is exported as string in excel. But I want to set it as date in excel format.

Can you tell me any solution for this?

allan

Currently the only solution is to use the customize method of the excelHtml5 button type and modify the XLSX file that Buttons is creating. It doesn't currently support dates out of the box.

Allan

jyotijariwal

I am working on showing loader before data loads in my datatable. I am using ajax datatable.

I am using fnPreDrawCallback function. My code is below -

"fnPreDrawCallback": function( oSettings ) {
showLoading();
},
"fnDrawCallback" : function() {
hideLoading();
}

Both functions are called twice. For example On Colvis buttons extension when I hide column my loader function runs twice.

@allan Can you tell me what is the issue with the code?

Or can suggest a method so that I can show loader before any event of a datatable like pagination, show/hide column etc?

allan

I don't understand why there would be a draw at all with the column visibility buttons. Can you link to a test case please.

Allan

jyotijariwal

@allan I am using old ColVis extension that is using draw callback for show/hide columns. That is running twice. The issue is I am using datatables all over my site and now changing the extension is a lot of work for me. I am linking a test case you can check. If you can suggest me some solution for this or any other way by which I can apply loader before any event that will be great?

This is my test case
https://jsfiddle.net/1c3Lmace/11/

allan

ColVis is legacy software and no longer supported. If you can't update to Buttons, the best I can suggest is that you modify ColVis to not trigger the draw. It did so to try and address any column resizing issues.

Allan