前端下载excel JSON转excel

潺泓

已于 2024-01-31 10:20:56 修改

阅读量118

点赞数

文章标签：前端

于 2023-08-16 09:43:13 首次发布

本文链接：https://blog.csdn.net/qq_36048987/article/details/132205576

版权

excel数据流下载

intersectionService.exportIntersectionInfoByCondition(params).then(res =>{
                        let blob = new Blob([res], {
                            type: "application/vnd.ms-excel;charset=UTF-8"
                        });
                        let downloadElement = document.createElement("a");
                        let href = window.URL.createObjectURL(blob); //创建下载的链接
                        downloadElement.href = href;
                        downloadElement.download = "信息报表.xlsx"; //下载后文件名
                        document.body.appendChild(downloadElement);
                        downloadElement.click(); //点击下载
                        document.body.removeChild(downloadElement); //下载完成移除元素
                        window.URL.revokeObjectURL(href); //释放掉blob对象
                        ElMessage.success('下载成功！')
                        data.downLoading = false
                    })

json转excel

if(data.uniformityQcData.length>0){
                            
                            let str = '<tr><td>路口Id</td><td>sd-road-id真实</td><td>同时挂载问题</td><td>未删除</td><td>未删除要</td><td>重叠问题</td></tr>'
                            for(let i = 0; i < data.uniformityQcData.length; i++){
                                str += '<tr>';
                                    for(const key in data.uniformityQcData[i]){

                                        str += '<td>' + data.uniformityQcData[i][key] + '\t' + '</td>';
                                    }
                                    str += '</tr>';
                            }
                            const worksheet = 'Sheet1'
                            const uri = 'data:application/vnd.ms-excel;base64,'
                            const 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><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
                                                <x:Name>${worksheet}</x:Name>
                                                <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
                                                </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
                                                </head><body><table>${str}</table></body></html>`
                            const base64 = (s: string) => { return window.btoa(unescape(encodeURIComponent(s))) }
                            const link = document.createElement('a')
                            link.href = uri + base64(template)
                            link.download = '结果.xls'
                            document.body.appendChild(link)
                            link.click()
                            document.body.removeChild(link)
                            data.downloadState = false
                            ElMessage.success(' 文件已下载！')
                        }else{
                            ElMessage.success(' 无错误信息！')
                        }




//二
export const jsontoexcel = (JSONData, FileName) => {
  const arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData
  var excel = '<table>'
  var row = '<tr>'
  //设置表头
  var keys = Object.keys(JSONData[0])
  keys.forEach(function (item) {
    row += '<td>' + item + '</td>'
  })
  excel += row + '</tr>'
  //设置数据
  for (var i = 0; i < arrData.length; i++) {
    var row = '<tr>'
    for (var index in arrData[i]) {
      if (arrData[i][index] == null) arrData[i][index] = ''
      row += '<td>' + arrData[i][index] + '</td>'
    }
    excel += row + '</tr>'
  }

  excel += '</table>'

  var excelFile =
    "<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'>"
  excelFile +=
    '<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">'
  excelFile +=
    '<meta http-equiv="content-type" content="application/vnd.ms-excel'
  excelFile += '; charset=UTF-8">'
  excelFile += '<head>'
  excelFile += '<!--[if gte mso 9]>'
  excelFile += '<xml>'
  excelFile += '<x:ExcelWorkbook>'
  excelFile += '<x:ExcelWorksheets>'
  excelFile += '<x:ExcelWorksheet>'
  excelFile += '<x:Name>'
  excelFile += '{worksheet}'
  excelFile += '</x:Name>'
  excelFile += '<x:WorksheetOptions>'
  excelFile += '<x:DisplayGridlines/>'
  excelFile += '</x:WorksheetOptions>'
  excelFile += '</x:ExcelWorksheet>'
  excelFile += '</x:ExcelWorksheets>'
  excelFile += '</x:ExcelWorkbook>'
  excelFile += '</xml>'
  excelFile += '<![endif]-->'
  excelFile += '</head>'
  excelFile += '<body>'
  excelFile += excel
  excelFile += '</body>'
  excelFile += '</html>'

  var uri =
    'data:application/vnd.ms-excel;charset=utf-8,' +
    encodeURIComponent(excelFile)

  var link = document.createElement('a')
  link.href = uri

  link.style = 'visibility:hidden'
  link.download = FileName + '.xlsx'

  document.body.appendChild(link)
  link.click()
  document.body.removeChild(link)
}

用插件下载

                let title = [] as any
                for(const [key, value] of Object.entries(downloadTile)){
                    title.push(key)
                }
                let body = [] as any
                for(let i = 0; i < data.intersectionLogData.length; i++){
                    let _row = [] as any
                    for(const [key, value] of Object.entries(downloadTile)){
                        const _value = data.intersectionLogData[i][value]
                        _row.push(_value)
                    }
                    body.push(_row)
                }
                const csv = [
                    title,
                    ...body
                ]
                const wb = XLSX.utils.book_new()
                const ws = XLSX.utils.aoa_to_sheet(csv)
                XLSX.utils.book_append_sheet(wb, ws, 'sheet1')
                // 将 workbook 转换为 XLSX 文件
                var wbout = XLSX.write(wb, { bookType: 'xlsx',type: 'binary' });
                 // 将二进制数据转换为 Blob 对象
                let blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' });
                // 将 Blob 对象转换为 URL，实现下载
                const link = document.createElement('a')
                link.href = URL.createObjectURL(blob)
                link.download = '日志.xlsx'
                document.body.appendChild(link)
                link.click()
                document.body.removeChild(link)
                ElMessage.success('文件已下载！')