Add Worksheet to Excel Export

zendog74

Hi all. I am trying to add a worksheet to the excel export. It will be a cover sheet with an image on it. It should be in first position in the workbook.

Here is what I have tried so far with no success.

buttons: [
            'copy',
            'print',
            {
                extend: 'excelHtml5',
                text: 'Excel',
                filename: 'PIRA',
                title: '',
                customize: function(xlsx){
                    var sheets = xlsx.xl.worksheets;
                    var sheet1 = sheets['sheet1.xml'];
                    //add a worksheet
                    var coverSheet = jQuery.clone(sheet1);
                    //var coverSheet = xlsx.xl.worksheets['cover.xml'];
                    jQuery('row:first c', coverSheet).text('Custom text');
                    xlsx.xl.worksheets["cover.xml"] = coverSheet;
                    self.loggr.log(xlsx);
                    self.loggr.log(sheets);
                    self.loggr.log(coverSheet);
                    self.loggr.log(sheet1);
                    //$('row:first c', sheet).attr('s', '42');
                }
            },
            'pdf',
        ]

My new worksheet shows up when I log it out to the console, but it does not show up in the actual Excel file. I was thinking that perhaps it was because there was some id that was the same, as I am cloning the other sheet, but I can't find one in the worksheet object.

Has anyone done this or have any pointers on how to accomplish it?

Thanks.

colin

Hi @zendog74 ,

You are in the right place, and doing the right kind of stuff. The customize hook is kept open as it would be too much for us to offer it as an API - the hook gives you that entry to the Excel file.

I did an internet search and tried it myself without any success. It must be possible, but I suspect it may be fiddly. This link here looks promising if you can spare the time to experiment. If you get it working, please report back, I'd love to know.

Cheers,

Colin

zendog74

Thanks Colin.

I tried creating the new sheet using an XML string and parsing it and putting it into the workbook, but it still does not show up in the Excel document.

var coverSheetStr = '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac"><sheetData /></worksheet>';
coverSheet = jQuery.parseXML(coverSheetStr);
xlsx.xl.worksheets["cover.xml"] = coverSheet;

I am wondering if the last time there needs to be different. Instead of just adding the worksheet to the worksheets object, perhaps there is some call I need to make so that it "registers" and updates the entire object? I am not sure which JS library is being used there. Is it native DataTables or JSZip or something else? Is it even exposed for me to use?

If I can figure that piece out, I can try it. If it works, awesome. If not, I will have to cut bait and go back to our other way of generating Excel files on the server.

colin

Yep, that's part of the problem. The way you're doing it is just to modify the contents of the file that gets written, so it's getting your hands very dirty. If you have a library, such as ActiveX, it would be much easier, but that wouldn't be available on the client.

C