Excel Export create multiple sheets

novrob

This is the example that kthorngren http://live.datatables.net/qinoxinu/6/edit
created and i been trying to add a third sheet and want to add additional sheets
for example http://live.datatables.net/hayofusa/1/edit

So I tried to duplicate the function but all it does is either error out and display blank tables or display the third table as the second and third sheet.
I am missing a part and i have looked at all the examples and posts.

kthorngren

I looked at it for a bit but not sure exactly what needs to be set to add a third sheet. In the addSheet() function there are some hard coded references to sheet2.xml. I think this is part of the problem so I changed them to sheet' + sheetId + '.xml'. Its close, if you repair the spreadsheet when opening it does open but as you mention the 2nd and 3rd contain the same data. Probably a relationship is not being set properly.
http://live.datatables.net/kuyayeto/1/edit

There are more changes needed but not sure what they are. It will take understanding the Open XML format to correctly add the sheets. I have pointers to the docs in the thread where I posted the above example. Someone more familiar with Open XML may see this and offer help. But likely it will take reading through the docs to find the proper way to add a sheet.

Kevin

novrob

Kevin,

Thanks for the update, the issue appears with the ID of the sheet, it clones the ID 3 and ignores any additional id's.
So i have to figure out how to insert a new ID for sheet 3 and tie to the workbook. if anyone else looks at the example you have to rename the excel sheet from .xlsx to zip and then extract it and look at the structure of the workbook. I keep working on it once I have working solution i post it here for others.

you2525

Hi, have you solved your issue ? I'm really interested to look at the way you did it.

you2525

I fixed the issue : http://live.datatables.net/kuyayeto/9/edit
It works great, as many sheets as you want. I changed 'rId3' by 'rId'+sheetId+1, i looked into the zip to help me, thanks !

novrob

That is good news, I had to put it aside until I could work on it some more. i will try this out this weekend.

Thank you for posting an updated.

you2525

UPDATE for people who have to export big tables (where the number of columns are bigger than 24)

You have to change :
mergeCol = ((header.length - 1) + 10).toString(36).toUpperCase();
mergeCells = '<mergeCells count="1">'+'<mergeCell ref="A1:' + mergeCol + '1"/>' + '</mergeCells>';
in the function getTableData(table, title)
Because it doesnt manage the column looking like : 'AA1' (instead the value is something like '1A1', Excel doesn"t know how to interpret it so you have a message like : "We found a problem with some content in "filename.xlsx" Do you want us to try to recover as much as we can?" at the opening

so remplace the line by something like that :
mergeCol = "B1";
mergeCells = '<mergeCells count="1">'+'<mergeCell ref="A1:' + mergeCol + '"/>' + '</mergeCells>';