/* * 預設轉換實作函數,若需要其他功能,需自行擴充* 參數: * tableID : HTML中Table物件id屬性值* 詳細用法請參考以下TableToExcel物件定義*/ function saveAsExcel(tableID){ va"/> /* * 預設轉換實作函數,若需要其他功能,需自行擴充* 參數: * tableID : HTML中Table物件id屬性值* 詳細用法請參考以下TableToExcel物件定義*/ function saveAsExcel(tableID){ va">
<script language="JavaScript" type="text/javascript"> /* * 默认转换实现函数,如果需要其他功能,需自行扩展 * 参数: * tableID : HTML中Table对象id属性值 * 详细用法参见以下 TableToExcel 对象定义 */ function saveAsExcel(tableID){ var tb = new TableToExcel(tableID); tb.setFontStyle("Courier New"); tb.setFontSize(10); tb.setTableBorder(2); tb.setColumnWidth(7); tb.isLineWrap(true); tb.getExcelFile(); } /** 功能:HTML中Table对象转换为Excel通用对象. * 参数:tableID HTML中Table对象的ID属性值 * 说明: * 能适应复杂的HTML中Table对象的自动转换,能够自动根据行列扩展信息 * 合并Excel中的单元格,客户端需要安装有Excel * 详细的属性、方法引用说明参见:Excel的Microsoft Excel Visual Basic参考 * 示范: * var tb = new TableToExcel('demoTable'); * tb.setFontStyle("Courier New"); * tb.setFontSize(10); //推荐取值10 * tb.setFontColor(6); //一般情况不用设置 * tb.setBackGround(4); //一般情况不用设置 * tb.setTableBorder(2); //推荐取值2 * tb.setColumnWidth(10); //推荐取值10 * tb.isLineWrap(false); * tb.isAutoFit(true); * * tb.getExcelFile(); * 如果设置了单元格自适应,则设置单元格宽度无效 * 版本:1.0*/ function TableToExcel(tableID) { this.tableBorder = -1; //边框类型,-1没有边框 可取1/2/3/4 this.backGround = 0; //背景颜色:白色 可取调色板中的颜色编号 1/2/3/4.... this.fontColor = 1; //字体颜色:黑色 this.fontSize = 10; //字体大小 this.fontStyle = "宋体"; //字体类型 this.rowHeight = 20; //行高 this.columnWidth = -1; //列宽 this.lineWrap = true; //是否自动换行 this.textAlign = -4108; //内容对齐方式 默认为居中 this.autoFit = true; //是否自适应宽度 this.tableID = tableID; } TableToExcel.prototype.setTableBorder = function (excelBorder) { this.tableBorder = excelBorder ; }; TableToExcel.prototype.setBackGround = function (excelColor) { this.backGround = excelColor; }; TableToExcel.prototype.setFontColor = function (excelColor) { this.fontColor = excelColor; }; TableToExcel.prototype.setFontSize = function (excelFontSize) { this.fontSize = excelFontSize; }; TableToExcel.prototype.setFontStyle = function (excelFont) { this.fontStyle = excelFont; }; TableToExcel.prototype.setRowHeight = function (excelRowHeight) { this.rowHeight = excelRowHeight; }; TableToExcel.prototype.setColumnWidth = function (excelColumnWidth) { this.columnWidth = excelColumnWidth; }; TableToExcel.prototype.isLineWrap = function (lineWrap) { if (lineWrap == false || lineWrap == true) { this.lineWrap = lineWrap; } }; TableToExcel.prototype.setTextAlign = function (textAlign) { this.textAlign = textAlign; }; TableToExcel.prototype.isAutoFit = function(autoFit){ if(autoFit == true || autoFit == false)this.autoFit = autoFit ; } //文件转换主函数 TableToExcel.prototype.getExcelFile = function () { var jXls, myWorkbook, myWorksheet, myHTMLTableCell, myExcelCell, myExcelCell2; var myCellColSpan, myCellRowSpan; try { jXls = new ActiveXObject('Excel.Application'); }catch (e) { alert("无法启动Excel!\n\n如果您确信您的电脑中已经安装了Excel,"+"那么请调整IE的安全级别。\n\n具体操作:\n\n"+"工具 → Internet选项 → 安全 → 自定义级别 → 对没有标记为安全的ActiveX进行初始化和脚本运行 → 启用"); return false; } jXls.Visible = true; myWorkbook = jXls.Workbooks.Add(); jXls.DisplayAlerts = false; myWorkbook.Worksheets(3).Delete(); myWorkbook.Worksheets(2).Delete(); jXls.DisplayAlerts = true; myWorksheet = myWorkbook.ActiveSheet; var readRow = 0, readCol = 0;var totalRow = 0, totalCol = 0; var tabNum = 0; //设置行高、列宽 if(this.columnWidth != -1) myWorksheet.Columns.ColumnWidth = this.columnWidth; else myWorksheet.Columns.ColumnWidth = 7; if(this.rowHeight != -1) myWorksheet.Rows.RowHeight = this.rowHeight ; //搜索需要转换的Table对象,获取对应行、列数 var obj = document.all.tags("table"); for (x = 0; x < obj.length; x++) { if (obj[x].id == this.tableID) { tabNum = x; totalRow = obj[x].rows.length; for (i = 0; i < obj[x].rows[0].cells.length; i++) { myHTMLTableCell = obj[x].rows(0).cells(i); myCellColSpan = myHTMLTableCell.colSpan; totalCol = totalCol + myCellColSpan; } } } //开始构件模拟表格 var excelTable = new Array(); for (i = 0; i <= totalRow; i++) { excelTable[i] = new Array(); for (t = 0; t <= totalCol; t++) { excelTable[i][t] = false; } } //开始转换表格 for (z = 0; z < obj[tabNum].rows.length; z++) { readRow = z + 1; readCol = 0; for (c = 0; c < obj[tabNum].rows(z).cells.length; c++) { myHTMLTableCell = obj[tabNum].rows(z).cells(c); myCellColSpan = myHTMLTableCell.colSpan; myCellRowSpan = myHTMLTableCell.rowSpan; for (y = 1; y <= totalCol; y++) { if (excelTable[readRow][y] == false) { readCol = y; break; } } if (myCellColSpan * myCellRowSpan > 1) { myExcelCell = myWorksheet.Cells(readRow, readCol); myExcelCell2 = myWorksheet.Cells(readRow + myCellRowSpan - 1, readCol + myCellColSpan - 1); myWorksheet.Range(myExcelCell, myExcelCell2).Merge(); myExcelCell.HorizontalAlignment = this.textAlign; myExcelCell.Font.Size = this.fontSize; myExcelCell.Font.Name = this.fontStyle; myExcelCell.wrapText = this.lineWrap; myExcelCell.Interior.ColorIndex = this.backGround; myExcelCell.Font.ColorIndex = this.fontColor; if(this.tableBorder != -1){ myWorksheet.Range(myExcelCell, myExcelCell2).Borders(1).Weight = this.tableBorder ; myWorksheet.Range(myExcelCell, myExcelCell2).Borders(2).Weight = this.tableBorder ; myWorksheet.Range(myExcelCell, myExcelCell2).Borders(3).Weight = this.tableBorder ; myWorksheet.Range(myExcelCell, myExcelCell2).Borders(4).Weight = this.tableBorder ; } myExcelCell.Value = myHTMLTableCell.innerText; for (row = readRow; row <= myCellRowSpan + readRow - 1; row++) { for (col = readCol; col <= myCellColSpan + readCol - 1; col++) { excelTable[row][col] = true; } } readCol = readCol + myCellColSpan; } else { myExcelCell = myWorksheet.Cells(readRow, readCol); myExcelCell.Value = myHTMLTableCell.innerText; myExcelCell.HorizontalAlignment = this.textAlign; myExcelCell.Font.Size = this.fontSize; myExcelCell.Font.Name = this.fontStyle; myExcelCell.wrapText = this.lineWrap; myExcelCell.Interior.ColorIndex = this.backGround; myExcelCell.Font.ColorIndex = this.fontColor; if(this.tableBorder != -1){ myExcelCell.Borders(1).Weight = this.tableBorder ; myExcelCell.Borders(2).Weight = this.tableBorder ; myExcelCell.Borders(3).Weight = this.tableBorder ; myExcelCell.Borders(4).Weight = this.tableBorder ; } excelTable[readRow][readCol] = true;readCol = readCol + 1; } } } if(this.autoFit == true) myWorksheet.Columns.AutoFit; jXls.UserControl = true; jXls = null; myWorkbook = null; myWorksheet = null; }; 以下内容为网络搜集,方便自己使用。出处不详(我忘了从哪找的了,呵呵)。 ------------------------------------------------------------------------------------------------------------------------------------- 使用JavaScript中的ActiveXObject填充并设置Excel格式 1.创建实例并创建工作表 var XLObj = new ActiveXObject("Excel.Application");//创建Excel实例 var xlBook = XLObj.Workbooks.Add; //新增工作簿 var ExcelSheet = xlBook.Worksheets(1); //创建工作表 2.保存表格 ExcelSheet.SaveAs("C:\\TEST.XLS"); 3.使 Excel 通过 Application 对象可见 ExcelSheet.Application.Visible = true;或者ExcelSheet.Visible=true; 4.打印 xlBook.PrintOut;或者:ExcelSheet.PrintOut; 5.关闭 xlBook.Close(savechanges=false);或者ExcelSheet.Close(savechanges=false); 6.结束进程 ExcelSheet.Application.Quit()或者XLObj.Quit(); XLObj=null; 7.页面设置 ExcelSheet.ActiveSheet.PageSetup.LeftMargin= 2/0.035; //页边距 左2厘米 ExcelSheet.ActiveSheet.PageSetup.RightMargin = 3/0.035; //页边距 右3厘米, ExcelSheet.ActiveSheet.PageSetup.TopMargin = 4/0.035; //页边距 上4厘米, ExcelSheet.ActiveSheet.PageSetup.BottomMargin = 5/0.035; //页边距 下5厘米 ExcelSheet.ActiveSheet.PageSetup.HeaderMargin = 1/0.035; //页边距 页眉1厘米 ExcelSheet.ActiveSheet.PageSetup.FooterMargin = 2/0.035; //页边距 页脚2厘米 ExcelSheet.ActiveSheet.PageSetup.CenterHeader = "页眉中部内容"; ExcelSheet.ActiveSheet.PageSetup.LeftHeader = "页眉左部内容"; ExcelSheet.ActiveSheet.PageSetup.RightHeader = "页眉右部内容"; ExcelSheet.ActiveSheet.PageSetup.CenterFooter = "页脚中部内容"; ExcelSheet.ActiveSheet.PageSetup.LeftFooter = "页脚左部内容"; ExcelSheet.ActiveSheet.PageSetup.RightFooter = "页脚右部内容"; 8.对单元格操作,带*部分对于行,列,区域都有相应属性 ExcelSheet.ActiveSheet.Cells(row,col).Value = "内容"; //设置单元格内容 ExcelSheet.ActiveSheet.Cells(row,col).Borders.Weight = 1; //设置单元格边框*() ExcelSheet.ActiveSheet.Cells(row,col).Interior.ColorIndex = 1; //设置单元格底色*(1-黑色,2-白色,3-红色,4-绿色,5-蓝色,6-黄色,7-粉红色,8-天蓝色,9-酱土色..可以多做尝试) ExcelSheet.ActiveSheet.Cells(row,col).Interior.Pattern = 1; //设置单元格背景样式*(1-无,2-细网格,3-粗网格,4-斑点,5-横线,6-竖线..可以多做尝试) ExcelSheet.ActiveSheet.Cells(row,col).Font.ColorIndex = 1; //设置字体颜色*(与上相同) ExcelSheet.ActiveSheet.Cells(row,col).Font.Size = 10; //设置为10号字* ExcelSheet.ActiveSheet.Cells(row,col).Font.Name = "黑体"; //设置为黑体* ExcelSheet.ActiveSheet.Cells(row,col).Font.Italic = true; //设置为斜体* ExcelSheet.ActiveSheet.Cells(row,col).Font.Bold = true; //设置为粗体* ExcelSheet.ActiveSheet.Cells(row,col).ClearContents; //清除内容* ExcelSheet.ActiveSheet.Cells(row,col).WrapText=true; //设置为自动换行* ExcelSheet.ActiveSheet.Cells(row,col).HorizontalAlignment = 3; //水平对齐方式枚举* (1-常规,2-靠左,3-居中,4-靠右,5-填充 6-两端对齐,7-跨列居中,8-分散对齐) ExcelSheet.ActiveSheet.Cells(row,col).VerticalAlignment = 2; //垂直对齐方式枚举*(1-靠上,2-居中,3-靠下,4-两端对齐,5-分散对齐) //行,列有相应操作: ExcelSheet.ActiveSheet.Rows(row). ExcelSheet.ActiveSheet.Columns(col). ExcelSheet.ActiveSheet.Rows(startrow+":"+endrow). //如Rows("1:5")即1到5行 ExcelSheet.ActiveSheet.Columns(startcol+":"+endcol). //如Columns("1:5")即1到5列 //区域有相应操作: XLObj.Range(startcell+":"+endcell).Select; //如Range("A2:H8")即A列第2格至H列第8格的整个区域 XLObj.Selection. //合并单元格 XLObj.Range(startcell+":"+endcell).MergeCells = true; //如Range("A2:H8")即将A列第2格至H列第8格的整个区域合并为一个单元格 或者:XLObj.Range("A2",XLObj.Cells(8, 8)).MergeCells = true; 9.设置行高与列宽 ExcelSheet.ActiveSheet.Columns(startcol+":"+endcol).ColumnWidth = 22;//设置从firstcol到stopcol列的宽度为22 ExcelSheet.ActiveSheet.Rows(startrow+":"+endrow).RowHeight = 22;//设置从firstrow到stoprow行的宽度为22 Var myrange oSheet.Range(oSheet.Cells(2,1),oSheet.Cells(2,6)); myRange.Columns.AutoFit() myRange.Rows.AutoFit()//好像不太管用 //设置该区域为自适应高度和宽度 使用JavaScript将表格内容导出到Excel的方式 1.整个表格拷贝到EXCEL中 function CopyTable(tableid) { //整个表格拷贝到EXCEL中 var curTbl = document.getElementById(tableid); var oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel var oWB = oXL.Workbooks.Add(); //获取workbook对象 var oSheet = oWB.ActiveSheet; //激活当前sheet var sel = document.body.createTextRange(); sel.moveToElementText(curTbl); //把表格中的内容移到TextRange中 sel.select(); //全选TextRange中内容 sel.execCommand("Copy"); //复制TextRange中内容 oSheet.Paste(); //粘贴到活动的EXCEL中 oXL.Visible = true; //设置excel可见属性 } 2.把单元格内容拷贝到Excel,并设置单元格格式. function method2(tableid) //读取表格中每个单元到EXCEL中 { var curTbl = document.getElementById(tableid); var oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel var oWB = oXL.Workbooks.Add(); //获取workbook对象 var oSheet = oWB.ActiveSheet; //激活当前sheet var Lenr = curTbl.rows.length; var y=document.getElementById('dropYear'); var m=document.getElementById('dropMonth'); //取得表格行数 oSheet.Cells( 1, 1).value='商户名称:工商银行商城'; oSheet.Range(oSheet.Cells(1,1),oSheet.Cells(1,6)).Merge() oSheet.Cells(2,1).value=日期; '+y.options[y.selectedIndex].value+'-'+m.options[m.selectedIndex].value; oSheet.Range(oSheet.Cells(2,1),oSheet.Cells(2,6)).Merge() oSheet.Cells( 3, 1).value='销售情况' oSheet.Range(oSheet.Cells(3,1),oSheet.Cells(3,6)).Merge() oSheet.Cells( 3, 1).HorizontalAlignment=3; oSheet.Cells( 3, 1).Font.Size=14; oSheet.Cells( 3, 1).Font.Bold = true; oSheet.Range(oSheet.Cells(4,1),oSheet.Cells(4,6)).Merge() oSheet.Cells(4,1).value=document.getElementById('lbMessage').innerText; for (i = 0; i < Lenr; i++) { var Lenc = curTbl.rows(i).cells.length; //取得每行的列数 for (j = 0; j < Lenc; j++) { oSheet.Cells(i + 5, j + 1).value = curTbl.rows(i).cells(j).innerText; //赋值 } } oSheet.Cells(i+6,2).value=document.getElementById('hidSum').value; oSheet.Columns.AutoFit(); oSheet.Rows.AutoFit(); oSheet.Rows(4).RowHeight = 30; oXL.Visible = true; //设置excel可见属性 oXL.Quit(); oXL = null; idTmr = window.setInterval("Cleanup();",1);//强制释放资源 } function Cleanup()//清除Excel资源 { window.clearInterval(idTmr); CollectGarbage(); } 使用JavaScript按照表格格式复制内容到剪切板 1.在Excel中进行粘贴 function copytable() { var content='' var tb=document.getElementById('GVmain'); var rows=tb.rows; for(var i=0;i<rows.length;i++) { var cells=rows[i].cells; for(var j=0;j<cells.length;j++) { content +=cells[j].innerText.toString()+'\t'; } content +='\n'; } if(content!='') { clipboardData.setData("text",content); alert("复制成功!") } }
以上是用javscript匯出html中的資料到excel表中的詳細內容。更多資訊請關注PHP中文網其他相關文章!