1. 从后端获取要输出的数据,将取到的数据作为参数去调用另一个带参函数 var allInfor = eval(data.tableData); //data为AJAX返回的,数据格式为下 public class resultData { public bool success { get; set; } public object barData { get; set; } public object tableData { get; set; } public string error { get; set; } } 2. inforToExcelNotIE函数及相关函数定义 /* 导出excel表格js */
/*ie浏览器,通过table导出excel 导出思路与非ie相反 tableid :需要导出的table的id fileName :导出的文件的名字 startRows :开始的行数 startCells 开始的列数 dateCells :时间格式的列的数组 endRows : 结束的行数 endCells:最后几行不导出 最后一行不导出 : 1 */
function ieToExcel(tableid, fileName, startRows, startCells, dateCells, endRows, endCells) { var curTbl = document.getElementById(tableid); curTbl.style.border = "1px"; curTbl.style.backgroundClip = "padding-box"; curTbl.style.position = "relative";
var oXL; try { oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel } catch (e) { alert("无法启动Excel!\n\n如果您确信您的电脑中已经安装了Excel," + "那么请调整IE的安全级别。\n\n具体操作:\n\n" + "工具 → Internet选项 → 安全 → 自定义级别 → 对没有标记为安全的ActiveX进行初始化和脚本运行 → 启用"); return false; } var oWB = oXL.Workbooks.Add(); var oSheet = oWB.ActiveSheet;
var Lenr = curTbl.rows.length; //导出的行数 for (i = startRows; i < endRows; i++) { var Lenc = curTbl.rows(i).cells.length; if (i == 0) { //导出的列数 for (j = startCells; j < Lenc - endCells; j++) { oSheet.Cells(i + 1, j - 1).value = curTbl.rows(i).cells(j).innerText; } } else { for (j = 2; j < Lenc - 1; j++) { console.log("j = " + j) for (var a = 0; a < dateCells.length; a++) { if (j == dateCells[a]) {//是时间格式的列 var time = new String(curTbl.rows(i).cells(j).innerText); oSheet.Cells(i + 1, j - 1).value = time; break; } else { oSheet.Cells(i + 1, j - 1).value = curTbl.rows(i).cells(j).innerText; } } } } } try { var fname = oXL.Application.GetSaveAsFilename(fileName, "Excel Spreadsheets (*.xls), *.xls"); } catch (e) { print("Nested catch caught " + e); } finally { oWB.SaveAs(fname); oWB.Close(savechanges = false); oXL.Quit(); oXL = null; idTmr = window.setInterval("Cleanup();", 1); } } //结束进程 function Cleanup() { window.clearInterval(idTmr); CollectGarbage(); }
/* 前台导出源码解析:非ie 内容简介: 样式转换模板:tmplCellXML:excel行转化模板 worksheetsXML:excel sheet 转化模板 tmplWorkbookXML:excel 文件转化模板
1.取值:dateType、dateStyle、dataValue、dataFormula 并将这四个值,转换后放入ctx内 再使用format方法,将ctx内的数据转化为tmplCellXml格式 如: ctx = {attributeStyleID: dateStyle, nameType: dateType, data: dataValue, attributeFormula: dataFormula} 转化后:<Cell><Data ss:Type="String">设备编号</Data></Cell> 然后rowsXML = <Row> <Cell><Data ss:Type="String">设备编号</Data></Cell>++ </Rows> 拼接累加
2.ctx = { rows: rowsXML, nameWS: sheetName || 'Sheet' }; 使用format方法,将ctx内的数据转化为tmplWorksheetXML格式 worksheetsXML += format(tmplWorksheetXML, ctx);
3.ctx = { created: (new Date()).getTime(), worksheets: worksheetsXML }; 使用format方法,将ctx内的数据转化为tmplWorkbookXML格式 workbookXML = format(tmplWorkbookXML, ctx);
一个完整的excel文件的内容拼接OK,接着创建excel表格,赋值,下载 var link = document.createElement("A"); link.href = uri + base64(workbookXML); link.download = fileName || 'Workbook.xls'; document.body.appendChild(link); link.click(); document.body.removeChild(link); */
/*非ie浏览器导出 Param : tableID :需要导出的table的id sheetName :工作簿的名称 fileName 导出的excel的文件名称 appname 填EXCEL startRows 开始的行数 startCells 开始的列数 endRows : 结束的行数 endCells:最后几行不导出 最后一行不导出 : 1 非Ie浏览器导出,与导出table表格的内容 */ //非ie浏览器table导出excel function noIeToExcel(tableID, sheetName, fileName, appname, startRows, startCells, endRows, endCells) { var uri = 'data:application/vnd.ms-excel;base64,' , tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">' + '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>' + '<Styles>' + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>' + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>' + '</Styles>' + '{worksheets}</Workbook>' , tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>' , tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>' , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) } , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) } //debugger; var ctx = ""; var workbookXML = ""; var worksheetsXML = ""; var rowsXML = "";
var table = document.getElementById(tableID); for (var j = startRows; j < endRows; j++) { rowsXML += '<Row>'; for (var k = startCells; k < table.rows[j].cells.length - endCells; k++) { var dataType = table.rows[j].cells[k].getAttribute("data-type"); var dataStyle = table.rows[j].cells[k].getAttribute("data-style"); var dataValue = table.rows[j].cells[k].getAttribute("data-value"); dataValue = (dataValue) ? dataValue : table.rows[j].cells[k].innerHTML; var dataFormula = table.rows[j].cells[k].getAttribute("data-formula"); dataFormula = (dataFormula) ? dataFormula : (appname == 'Calc' && dataType == 'DateTime') ? dataValue : null;
ctx = { attributeStyleID: (dataStyle == 'Currency' || dataStyle == 'Date') ? ' ss:StyleID="' + dataStyle + '"' : '' , nameType: (dataType == 'Number' || dataType == 'DateTime' || dataType == 'Boolean' || dataType == 'Error') ? dataType : 'String' , data: (dataFormula) ? '' : dataValue , attributeFormula: (dataFormula) ? ' ss:Formula="' + dataFormula + '"' : '' }; rowsXML += format(tmplCellXML, ctx); } rowsXML += '</Row>' }
ctx = { rows: rowsXML, nameWS: sheetName || 'Sheet' }; worksheetsXML = format(tmplWorksheetXML, ctx);
ctx = { created: (new Date()).getTime(), worksheets: worksheetsXML }; workbookXML = format(tmplWorkbookXML, ctx);
var link = document.createElement("A"); link.href = uri + base64(workbookXML); link.download = fileName || 'Workbook.xls'; document.body.appendChild(link); link.click(); document.body.removeChild(link); }
//非ie导出 //JS直接与后台关联导出 function inforToExcelNotIE(allInfor, sheetName, fileName, appname, flag) {
var uri = 'data:application/vnd.ms-excel;base64,' , tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">' + '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>' + '<Styles>' + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>' + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>' + '</Styles>' + '{worksheets}</Workbook>' , tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>' , tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>' , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) } , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
var ctx = ""; var rowsXML = ""; var workbookXML = ""; var worksheetsXML = ""; rowsXML = productionPassRateToExcelNotIE(allInfor); }//debugger; ctx = { rows: rowsXML, nameWS: sheetName || 'Sheet' }; worksheetsXML = format(tmplWorksheetXML, ctx);
ctx = { created: (new Date()).getTime(), worksheets: worksheetsXML }; workbookXML = format(tmplWorkbookXML, ctx);
var link = document.createElement("A"); link.href = uri + base64(workbookXML); link.download = fileName || 'Workbook.xls'; document.body.appendChild(link); link.click(); document.body.removeChild(link); }/*获取浏览器内核信息*/ function getExplorer() { var explorer = window.navigator.userAgent; if (explorer.indexOf('Trident') > -1 && explorer.indexOf('rv:11') > -1) { return "ie"; } else if (explorer.indexOf('MSIE') > -1 && explorer.indexOf('Trident') > -1) { return "ie"; } else if (explorer.indexOf('MSIE') > -1) { return "ie"; } //firefox else if (explorer.indexOf("Firefox") >= 0) { return 'Firefox'; } //Chrome else if (explorer.indexOf("Chrome") >= 0) { return 'Chrome'; } //Opera else if (explorer.indexOf("Opera") >= 0) { return 'Opera'; } //Safari else if (explorer.indexOf("Safari") >= 0) { return 'Safari'; } }//产品合格率 分 根据不同的标志输出内容自定义 function productionPassRateToExcelNotIE(allInfor) { //debugger; var rowsXML = ""; rowsXML += '<Row>'; rowsXML += '<Cell><Data ss:Type="String">序号</Data></Cell>'; rowsXML += '<Cell><Data ss:Type="String">时间</Data></Cell>';// rowsXML += '<Cell><Data ss:Type="String">废品数</Data></Cell>';// rowsXML += '<Cell><Data ss:Type="String">合格数</Data></Cell>';// rowsXML += '<Cell><Data ss:Type="String">一次交检合格率</Data></Cell>';// rowsXML += '<Cell><Data ss:Type="String">良品率</Data></Cell>';// rowsXML += '</Row>'; alert("表数据长度:" + allInfor.length); var allCount = allInfor.length; var i = 1; debugger; $.each(allInfor, function (key, val) { debugger; rowsXML += '<Row>'; rowsXML += '<Cell><Data ss:Type="String">' + i + '</Data></Cell>'; rowsXML += '<Cell><Data ss:Type="String">' + val.somedate + '</Data></Cell>';// rowsXML += '<Cell><Data ss:Type="String">' + val.waste + '</Data></Cell>';// rowsXML += '<Cell><Data ss:Type="String">' + val.correct + '</Data></Cell>';//还没有 rowsXML += '<Cell><Data ss:Type="String">' + val.oncecorrectRate + '</Data></Cell>'; rowsXML += '<Cell><Data ss:Type="String">' + val.correctRate + '</Data></Cell>'; rowsXML += '</Row>'; i++; }); return rowsXML; } |
利用写EXCEL表格语句的方法导出Table的数据为Excel文件
最新推荐文章于 2023-03-15 13:44:27 发布