JS操作Excel大全(带目录).docx

JS操作Excel大全目录JS操作Excel大全1打印接口说明1定义了Excel操作对象1Excle控制相关函数列表2Check2CellsFormat2InitExcel3OpenExcel3SaveAsExcel3SetPage4SetFormat4SetMergeCells7SetColumnWidth7SetRowHeight8SetValue8SetValueFormatToString9SetValueFormatToCustom9SetBorder10SetUserControl11ClearExcel11QuitExcel11PrintPreviewExcel12PrintOutExcel12打印接口说明expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)parameter说明：expression 必需。该表达式返回“应用于”列表中的一个对象。From Variant 类型，可选。打印的开始页号。如果省略该参数，将从起始位置开始打印。To Variant 类型，可选。打印的终止页号。如果省略该参数，将打印至最后一页。Copies Variant 类型，可选。要打印的份数。如果省略该参数，将只打印一份。Preview Variant 类型，可选。如果该值为 True，则 Microsoft Excel 打印指定对象之前进行打印预览。如果该值为 False（或者省略此参数），则立即打印该对象。ActivePrinter Variant 类型，可选。设置活动打印机的名称。PrintToFile Variant 类型，可选。如果该值为 True，则打印输出到文件。如果没有指定 PrToFileName，则 Microsoft Excel 将提示用户输入要输出文件的文件名。Collate Variant 类型，可选。如果该值为 True，则逐份打印每份副本。PrToFileName 可选，Variant 类型。如果 PrintToFile 为 True，则本参数指定要打印到的文件名。expression.PrintOut(1,1,1,false,打印机名称,false,false)定义了Excel操作对象使用提示：无需创建对象新对象，使用方法列表中的方法既可以控制，脚步中定义了app（ActiveXObject）、xlWork（工作簿对象）、xlSheet（工作表对象），特殊功能可使用这三个对象控制。var app = null;var xlWork = null;var xlSheet = null;Excle控制相关函数列表Check/* 用于检测范围字符串的类型* Cells:表示单元格;Columns:表示列;Range:表示区域*/function Check(AreaStr) var CellsPatrn = /d+,1/d+$/; var ColPatrn = /(a-zA-Z+:1a-zA-Z+)|(a-zA-Z+)$/; var RangePatrn = /a-zA-Z+/d+:1a-zA-Z+/d+$/; var AreaRet = ; if (CellsPatrn.exec(AreaStr) AreaRet = Cells; if (ColPatrn.exec(AreaStr) AreaRet = Columns; if (RangePatrn.exec(AreaStr) AreaRet = Range; if (AreaStr = all) AreaRet = all; return AreaRet;CellsFormat/* 用于转换表示单元格的字符串* Cells:表示单元格;Columns:表示列;Range:表示区域*/function CellsFormat(CellsStr) var st = CellsStr.split(,); if (st.length = 2) st0 = parseInt(st0); st1 = parseInt(st1); return st; InitExcel/*初始化一个Excel对象*/function InitExcel() try app = new ActiveXObject(Excel.Application); catch (ex) alert(Excel对象无法创建，请开启浏览器的ActiveX控键限制，并安装Excel2003！);xlWork = app.Workbooks.Add();xlSheet = xlWork.Worksheets(1);OpenExcel /*打开一个Excel模版*/function OpenExcel(FileName) try if (FileName != ) app = new ActiveXObject(Excel.Application);xlWork = app.Workbooks.Open(FileName);xlSheet = xlWork.Worksheets(1); else alert(Excel文件路径错误); catch (ex) alert(Excel对象无法创建，请开启浏览器的ActiveX控键限制，并安装Excel2003！);SaveAsExcel/*保存一个Excel模版*/function SaveAsExcel(FileName) var filename = ;try if (FileName != ) xlWork.SaveAs(FileName);xlWork.Close(savechanges = false);app.Visible = false;app.Quit;filename = FileName; else alert(保存路径发生错误);filename = ; catch (ex) alert(Excel文件路径错误);return filename;function SetVisible(IsShow) if (IsShow) app.visible = true; else app.visible = false;SetPage /*设置页面边距,_Orientation：1表示纵向，2表示横向*/function SetPage(Orientation) with (xlSheet.PageSetup) Orientation = _Orientation; / 横向LeftMargin = app.InchesToPoints(0.748031496062992);RightMargin = app.InchesToPoints(0.748031496062992);TopMargin = app.InchesToPoints(0.984251968503937);BottomMargin = app.InchesToPoints(0.984251968503937);HeaderMargin = app.InchesToPoints(0.511811023622047);FooterMargin = app.InchesToPoints(0.511811023622047);SetFormat/* * 单元格设置字体 * AreaStr：表示范围的字符串 * FontName：字符串型，字体名称 * FontSize：整型，字体大小 * FontBold：Bool型， * True表示加粗 * FontItalic：Bool型，True表示斜体 * HorAlignment：整型，1是常规，2 是左，3 中，4 右 * VerAlignment：整型，1 上，2 中，3 下 * Wrap：Bool型，True表示自动换行 */function SetFormat(AreaStr, FontName, FontSize, FontBold, FontItalic,HorAlignment, VerAlignment, Wrap) var CheckStr = Check(AreaStr);if (CheckStr != ) switch (CheckStr) case Cells:var Cell = CellsFormat(AreaStr);if (FontName != ) xlSheet.Cells(Cell0, Cell1).Font.Name = FontName;if (FontSize != null) xlSheet.Cells(Cell0, Cell1).Font.Size = FontSize;if (FontBold != false) xlSheet.Cells(Cell0, Cell1).Font.Bold = true;if (FontItalic != false) xlSheet.Cells(Cell0, Cell1).Font.Italic = true;if (HorAlignment != null) xlSheet.Cells(Cell0, Cell1).HorizontalAlignment = HorAlignment;if (VerAlignment != null) xlSheet.Cells(Cell0, Cell1).VerticalAlignment = VerAlignment;if (Wrap != false) xlSheet.Cells(Cell0, Cell1).WrapText = true;break;case Columns:if (FontName != ) xlSheet.Columns(AreaStr).Font.Name = FontName;if (FontSize != null) xlSheet.Columns(AreaStr).Font.Size = FontSize;if (FontBold != false) xlSheet.Columns(AreaStr).Font.Bold = true;if (FontItalic != false) xlSheet.Columns(AreaStr).Font.Italic = true;if (HorAlignment != null) xlSheet.Columns(AreaStr).HorizontalAlignment = HorAlignment;if (VerAlignment != null) xlSheet.Columns(AreaStr).VerticalAlignment = VerAlignment;if (Wrap != false) xlSheet.Columns(AreaStr).WrapText = true;break;case Range:if (FontName != ) xlSheet.Range(AreaStr).Font.Name = FontName;if (FontSize != null) xlSheet.Range(AreaStr).Font.Size = FontSize;if (FontBold != false) xlSheet.Range(AreaStr).Font.Bold = true;if (FontItalic != false) xlSheet.Range(AreaStr).Font.Italic = true;if (HorAlignment != null) xlSheet.Range(AreaStr).HorizontalAlignment = HorAlignment;if (VerAlignment != null) xlSheet.Range(AreaStr).VerticalAlignment = VerAlignment;if (Wrap != false) xlSheet.Range(AreaStr).WrapText = true;break;SetMergeCells/* * 合并单元格 AreaStr：表示范围的字符串 */function SetMergeCells(AreaStr) var CheckStr = Check(AreaStr);if (CheckStr != ) if (CheckStr = Range) xlSheet.range(AreaStr).MergeCells = true;SetColumnWidth/* * 设置列宽 AreaStr：设置范围，如：A:D ColumnValue：列宽值 */function SetColumnWidth(AreaStr, ColumnValue) var CheckStr = Check(AreaStr);if (CheckStr != ) switch (CheckStr) case Cells:var Cell = CellsFormat(AreaStr);xlSheet.Cells(Cell0, Cell1).ColumnWidth = ColumnValue;break;case Columns:xlSheet.Columns(AreaStr).ColumnWidth = ColumnValue;break;case Range:xlSheet.range(AreaStr).ColumnWidth = ColumnValue;break;case all:xlSheet.Cells.ColumnWidth = ColumnValue;break;SetRowHeight/* * 设置行高 AreaStr：范围标示 HeightValue：行高值 */function SetRowHeight(AreaStr, HeightValue) var CheckStr = Check(AreaStr);if (CheckStr != ) switch (CheckStr) case Cells:var Cell = CellsFormat(AreaStr);xlSheet.Cells(Cell0, Cell1).RowHeight = HeightValue;break;case Columns:xlSheet.Columns(AreaStr).RowHeight = HeightValue;break;case Range:xlSheet.range(AreaStr).RowHeight = HeightValue;break;case all:xlSheet.Cells.RowHeight = HeightValue;break;SetValue/* * 设置单元格内容 AreaStr：范围标示 AreaValue：内容 */function SetValue(AreaStr, AreaValue) var CheckStr = Check(AreaStr);if (CheckStr != & CheckStr != Columns) switch (CheckStr) case Cells:var Cell = CellsFormat(AreaStr);xlSheet.Cells(Cell0, Cell1).value = AreaValue;break;case Range:xlSheet.range(AreaStr).value = AreaValue;break;SetValueFormatToString/* * 设置单元格内容的格式 - 字符串型 * AreaStr：范围标示 */function SetValueFormatToString(AreaStr) var CheckStr = Check(AreaStr);if (CheckStr != ) switch (CheckStr) case Cells:var Cell = CellsFormat(AreaStr);xlSheet.Cells(Cell0, Cell1).NumberFormatLocal = ;break;case Columns:xlSheet.Columns(AreaStr).NumberFormatLocal = ;break;case Range:xlSheet.range(AreaStr).NumberFormatLocal = ;break;case all:xlSheet.Cells.NumberFormatLocal = ;break;SetValueFormatToCustom/* * 设置单元格内容的格式 - 自定义型 AreaStr：范围标示 regix：格式表达式 */function SetValueFormatToCustom(AreaStr, regix) var CheckStr = Check(AreaStr);if (CheckStr != ) switch (CheckStr) case Cells:var Cell = CellsFormat(AreaStr);xlSheet.Cells(Cell0, Cell1).NumberFormatLocal = regix;break;case Columns:xlSheet.Columns(AreaStr).NumberFormatLocal = regix;break;case Range:xlSheet.range(AreaStr).NumberFormatLocal = regix;break;SetBorder/* * 设置范围的边框 AreaStr：范围标示 */function SetBorder(AreaStr) var CheckStr = Check(AreaStr);if (CheckStr != ) switch (CheckStr) case Cells:var Cell = CellsFormat(AreaStr);for (var i = 1; i 5; i+) xlSheet.Cells(Cell0, Cell1).Borders(i).LineStyle = 1;xlSheet.Cells(Cell0, Cell1).Borders(i).Weight = 2;break;case Columns:for (var i = 7; i 13; i+) xlSheet.Columns(Area