The front-end js writes Excel

Keywords: Javascript Excel xml IE

How can the front end write excel, in fact, is relatively simple, but did not touch this piece, of course, here is only a simple introduction.
There are two main ways to support the mainstream browser and the Ie browser.

Mainstream browsers

This side mainly uses data protocol to parse Excel's Content type (application/vnd.ms-excel) through data protocol.
So the format here is'data:+Content-type;+content'
The content format of excel is templated as follows:

<html 
    xmlns:o="urn:schemas-microsoft-com:office:office" 
    xmlns:x="urn:schemas-microsoft-com:office:excel" 
    xmlns="http://www.w3.org/TR/REC-html40">
    <head>
        <meta charset="UTF-8"><!--[if gte mso 9]>
        <xml>
            <x:ExcelWorkbook>
                <x:ExcelWorksheets>
                    <x:ExcelWorksheet>
                        <x:Name>sheet</x:Name>
                        <x:WorksheetOptions>
                            <x:DisplayGridlines/>
                        </x:WorksheetOptions>
                    </x:ExcelWorksheet>
                </x:ExcelWorksheets>
            </x:ExcelWorkbook></xml>
    </head>
    <body>
        {tableData}
    </body>
</html>

Then you can create it according to the above template. Here is how to export excel directly.

(function() {
    var template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta charset="UTF-8"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>sheet</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml></head><body>{tableData}</body></html>'
    var Excel_URL = 'data:application/vnd.ms-excel;base64,'
    var Excel = {
        ToExcel: function (data) {
            var isIe = window.navigator.userAgent.toLocaleUpperCase().indexOf('trident')
            if (isIe !== -1) {
                this._IEExport(data)
            } else {
                this._otherExport(data)
            }
        },
        _otherExport: function (data) {
            var content = ''
            if (typeof data === 'string') {
                // Pass in the id to get the contents of the table
                var ele = document.querySelector(data)
                content = template.replace('{tableData}', ele.outerHTML)
            } // else can do more
            var aEle = document.createElement('a')
            aEle.href = Excel_URL + window.btoa(unescape(encodeURIComponent(content)))
            aEle.download = 'test.xls'
            aEle.click()
        }
    }
    window.Excel = Excel
})()

IE browser

IE mainly uses ActiveXObject to achieve: see the following code specifically

(function() {
    var template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta charset="UTF-8"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>sheet</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml></head><body>{tableData}</body></html>'
    var Excel_URL = 'data:application/vnd.ms-excel;base64,'
    var Excel = {
        ToExcel: function (data) {
            var isIe = window.navigator.userAgent.toLocaleUpperCase().indexOf('trident')
            if (isIe !== -1) {
                this._IEExport(data)
            } else {
                this._otherExport(data)
            }
        },
        _IEExport: function (data) {
            // Open excel
            var oXL = new ActiveXObject('Excel:Application')
            // New Work Blog
            var oWB = oXL.WorkBooks.Add()
            // Activate New Work Blog
            var oSheet = oWB.ActiveSheet

            if (typeof data === 'string') {
                // table id
                var table = document.querySelector(data)
                // Create a container for content
                var sel = document.body.createTextRange()
                // Move the contents of the table into the container
                sel.moveToElementText(table)
                // Select the content to be moved in
                try {
                    console.log(sel.select)
                    sel.select()
                } catch (e) {
                    console.log(e)
                }
                // Copy the contents in the container
                sel.execCommand("Copy")
                // Paste into excel Workbook
                oSheet.Paste()
            }
            // Turn off Excel
            var filename = oXL.Application.GetSaveAsFilename('test.xls', 'Excel Spreadsheet (*.xls),*.xls')
            // Keep A Workbook
            oWB.SaveAs(filename)
            oWB.close()
            oXL.quit()
        }
    }
    window.Excel = Excel
})()

This side just learned, to make a good excel still need more in-depth understanding of API.

Posted by rtown on Sun, 24 Mar 2019 18:57:28 -0700

Programmer Group

The front-end js writes Excel

Mainstream browsers

IE browser

Hot Keywords