js导出数据到Excel表或WPS的电子表格

var xls,xlBook,xlsheet; //新建一个Excel文件 function newExcel(){ try{ xls = GetObject("", "Excel.Application"); } catch(e){ try { xls = new ActiveXObject("Excel.Application"); } catch (e) { try { xls = new ActiveXObject("ET.Application"); } catch (e) { alert("要在项目中使用Excel，您必须安装Excel电子表格软件或者WPS，同时浏览器须使用“ActiveX 控件”，您的浏览器须允许执行控件。 请点击【帮助】了解浏览器设置方法！"); return false; } } } xls.visible = true; //设置excel为可见 xlBook = xls.Workbooks.Add; xlsheet = xlBook.Worksheets(1); return true; } //打开一个已存在的工作簿 function openExcel(fileName) { try { xls = new ActiveXObject("Excel.Application"); } catch (e) { try { xls = new ActiveXObject("ET.Application"); } catch (e) { alert("要在项目中使用Excel，您必须安装Excel电子表格软件或者WPS，同时浏览器须使用“ActiveX 控件”，您的浏览器须允许执行控件。 请点击【帮助】了解浏览器设置方法！"); return false; } } xlBook = xls.Workbooks.Open(fileName); xls.Worksheets(1).Activate; xlsheet = xlBook.worksheets(1); xls.visible = true; return true; } //选定工作表 function selectSheet(index){ xls.WorkSheets(index).Activate; xlsheet=xls.WorkSheets(index); } //保存工作簿 function saveAs(fileName){ xls.SaveAs(fileName ); } //关闭Excel function closeExcel(){ xlBook.Close(savechanges=false); //xlsheet.Application.Quit(); xls.Quit(); xlsheet=null; xlBook=null; xls=null; } //打印工作簿 function printWorkBook(){ xlBook.PrintOut; } //打印工作表 function printWorkSheet(){ xlsheet.PrintOut; } //打印预览工作表 function PrintPreview(){ //xlsheet.PrintPriview; xls.ActiveSheet.PrintPreview; } //页面设置 direct为方向，row1为表头起始行，row2为表头结束行 function pageSet(direct,row1,row2){ xls.ActiveSheet.PageSetup.Orientation =direct;//2为横向,1为纵向 xls.ActiveSheet.PageSetup.PrintTitleRows = "$"+row1+":$"+row2; } //打印预览工作簿 function xlsBookPrintPreView(){ //xlBook.PrintPreview; xlBook.printOut(null,null,1,true,true); } //合并单元格 function hbCells(x1,y1,x2,y2){ var value=xlsheet.Cells(x1,y1).value; for(var i=x1;i<=x2;i++){ for(var j=y1;j<=y2;j++){ xlsheet.Cells(i,j).ClearContents; } } xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)).mergecells=true; xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)).value=value; } //设置行高 function setRowHight(startRow,endRow,hight){ xlsheet.Rows(startRow+":"+endRow).RowHeight=hight; } //设置所有行的行高 function setAllHight(hight){ xlsheet.Cells.Select; xls.Selection.RowHeight = hight; } //设置列宽 function setColumnWidth(startCol,endCol,width){ for(var i=startCol;i<=endCol;i++){ xlsheet.Columns(i).ColumnWidth =width; } } //设置字体 function setFont(x1,y1,x2,y2,fontName){ xlsheet.Range(xlsheet.Cells(x1,y1), ws.Cells(x2,y2)).Font.Name = fontName; } //给单元格填值 function setValue(row,col,value){ xlsheet.Cells(row,col).Value = value; } //设置表格线 function setGridLine(x1,y1,x2,y2){ //xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)).Borders.LineStyle = 1; xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)).Borders.Weight = 2; } //设置居中对齐 function setCenter(x1,y1,x2,y2){ xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)).HorizontalAlignment = 3; xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)).VerticalAlignment = 2; } //设置右对齐 function setRight(x1,y1,x2,y2){ var selection = xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)); selection.HorizontalAlignment = 4; selection.VerticalAlignment = 2; } //设置数字为字符 function setColumnChar(col){ xlsheet.Columns(col).NumberFormatLocal="@"; } //设置数字单元格 function setNumberCell(x1,y1,x2,y2){ var selection = xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)); selection.NumberFormatLocal = "#,##0.00_ "; selection.HorizontalAlignment = 4; selection.VerticalAlignment = 2; } //设置数字单元格 //sf为数字格式 function setNumberFormat(x1,y1,x2,y2,sf){ var selection = xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)); selection.NumberFormatLocal = sf; } //设置自动换行 function setWrapText(x1,y1,x2,y2){ xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)).WrapText=true; } //设置标题行 function setTitle(x1,y1,x2,y2,title){ var selection = xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)); var value=xlsheet.Cells(x1,y1).value; for(var i=x1;i<=x2;i++){ for(var j=y1;j<=y2;j++){ xlsheet.Cells(i,j).ClearContents; } } selection.mergecells=true; selection.value=title; selection.Font.size=20; selection.Font.Bold = true; selection.HorizontalAlignment = 3; selection.VerticalAlignment = 2; } //设置为用户控制Excel function setUserCrontrol(){ xls.UserControl=true; } //设置自动列宽 function setAutoColumnWidth(){ xlsheet.Columns.EntireColumn.AutoFit; //xls.Selection.EntireColumn.AutoFit; } //设置自动行高 function setAutoRowHeight(){ xlsheet.Rows.EntireRow.AutoFit; //xls.Selection.EntireColumn.AutoFit; } //对col列从row开始的行中相同内容进行合并处理 function columnHB(row,col){ var value=xlsheet.Cells(row,col).value; var j=row; var i=row+1; do{ value1=xlsheet.Cells(i,col); if(value1+""!=value+""){ if(i-j>1){ hbCells(j,col,i-1,col); } value=value1; j=i; } i++; } while(value+""!="undefined"); } function columnHB2(row0,col0,row,col){ var value0=xlsheet.Cells(row0,col0).value; var value=xlsheet.Cells(row,col).value; var j=row; var i=row+1; do{ value01=xlsheet.Cells(i,col0); value1=xlsheet.Cells(i,col); if(value1+""!=value+""||value01+""!=value0+""){ if(i-j>1){ hbCells(j,col,i-1,col); } value=value1; j=i; } i++; } while(value+""!="undefined"); } function setFontSize(x1,y1,x2,y2,size){ var selection = xlsheet.Range(xlsheet.Cells(x1,y1),xlsheet.Cells(x2,y2)); selection.Font.size=size; }