WPS常用表格对象应用

star星梦

已于 2023-11-11 13:39:39 修改

阅读量1k

点赞数 3

分类专栏： WPS-JS宏文章标签： wps java 数据库 javascript

于 2023-11-10 23:14:53 首次发布

本文链接：https://blog.csdn.net/qq_24818403/article/details/134341907

版权

WPS-JS宏专栏收录该内容

8 篇文章 18 订阅

订阅专栏

一、工作簿操作

function 新建工作簿()
{
	let wb=Workbooks.Add();
	wb.SaveAs("C:/Users/demo/Desktop/123/test.xlsx");
	wb.Close();
//	Workbooks.Add("C:/Users/demo/Desktop/123/工资表.xlsx")

}
function 删除文件(){
//	Kill("C:/Users/demo/Desktop/123/test.xlsx");
	Kill("C:/Users/demo/Desktop/123/*.txt")
}

function 新建工作簿(){
	let strpath = ThisWorkbook.Path;
	for(let datesV = 1 ;datesV<=12;datesV++){
		let wb = Workbooks.Add(`${strpath}/工资表.xlsx`);//以工作表为模板
		wb.SaveAs(`${strpath}/2023/${datesV}月.xlsx`);
		wb.Close()
	}
}

function 打开关闭工作簿(){
	let wb=Workbooks.Open("D:\\WPS JS宏视频教程\\第3章\\3-3\\demo.xlsx");
	Range("a1:d10").Value2=100;
//	wb.Save();
	wb.Close(true);
}

function 取文件名(){
	let strpath = Dir(ThisWorkbook.Path+"/产品/*.xls*")
	while(strpath != ""){
		console.log(strpath);
		strpath = Dir();
	}
}

function 汇总每个工作簿中的数据(){
	let strPath=Workbooks.Item("3-5.xlsm").Path;
	let strFileName=Dir(`${strPath}\\产品\\*.xlsx`);
	let intTotal=0;
	while (strFileName!=""){
		let wb=Workbooks.Open(`${strPath}\\产品\\${strFileName}`);
		intTotal +=wb.Sheets(1).Range("b14").Value();
		wb.Close(false);
		strFileName=Dir();
	}
	alert(`总金额为：${intTotal}`);

function 判断工作簿是否打开(){
	let intNum=0
	for (let wb of Workbooks){
		if (wb.Name=="调查表.xlsx"){
			intNum++;
		}
	}
	switch (intNum){
		case 0:
			Workbooks.Open("D:/WPS JS宏视频教程/第3章/3-6/调查表.xlsx");
			break;
		case 1:
			alert("调查表.xlsx,已打开！")
			break;
	}
}

二、工作表操作

新建:
Sheets.Add(Before, After, Count, Type)
可以创建的类型为：xlWorksheet（工作表）、xlChart（图表）、xlExcel4MacroSheet（宏表） 或 xlExcel4IntlMacroSheet（支持多语言的宏表）。
以新建工作表为例，讲解一下JS宏中的函数(也就是方法)，它的使用方法。
1.如果参数中带有?，表示可选参数。否则表示必选参数。
2.可选参数如果是最后一个位置，可以忽略不写。
3.可选参数如果不在最后，不能忽略不写，必需写undefined。
--------------------------
删除:
ActiveWindow.SelectedSheets.Delete //删除已经选择的工作表
Sheets(n).Delete(); //删除指定的单个工作表
注意：工作表可以用索引，也可以用名称。

function Macro2()
{
	let u=undefined;
	Sheets.Add(u, Sheets("Sheet15"), 1,u);
}
function Macro1()
{
//	Sheets.Item("Chart1").Select(false);
	Sheets(2).Delete();
//	Sheets.Item("Sheet2").Activate();
//	ActiveWindow.SelectedSheets.Delete();
}

function 提取工作簿和工作表名称(){
	let wbOpen=Workbooks.Item("3-8.xlsm");
	let strFileName=Dir(`${wbOpen.Path}\\销售表\\*.xlsx`);
	let intRowNum=1;
	while (strFileName!=""){
		let wb=Workbooks.Open(`${wbOpen.Path}\\销售表\\${strFileName}`)
		for (let ws of wb.Sheets){
			wbOpen.Sheets(1).Cells(intRowNum,"A").Value2=wb.Name;
			wbOpen.Sheets(1).Cells(intRowNum,"B").Value2=ws.Name;
			intRowNum++;
		}
		wb.Close(false);
		strFileName=Dir();
	}
}

function 工作表的复制和移动(){
	//移动（move）复制（copy）,两个参数为before和after
	//同一个工作簿下工作表的移动与复制
	Sheets("工资表").Move(Sheets("other")); 
	Sheets("工资表").Move(undefined,Sheets("other"));
	
	//不同工作簿下工作表的移动与复制
	let wbSoure=Workbooks.Item("3-9.xlsm");
	let wbTarget=Workbooks.Item("demo.xlsx");
	wbSoure.Sheets("工资表").Copy(wbTarget.Sheets(1))
	wbSoure.Sheets("工资表").Copy(undefined,wbTarget.Sheets(2))
	
	//工作表的移动复制到新工作簿
	Sheets(1).Copy();
}

function 批量创建工作表(){
	for (let intYear=2020;intYear<=2023;intYear++){
		let wbNew=Workbooks.Add();
		for (let intMonth=12;intMonth>=1;intMonth--){
			let wsNew=wbNew.Sheets.Add();
			wsNew.Name=`${intMonth}月`;
		}
		wbNew.Sheets(wbNew.Sheets.Count).Delete();
		wbNew.SaveAs(`${Workbooks.Item("3-10.xlsm").Path}\\年份\\${intYear}年.xlsx`);
		wbNew.Close();
	}
}

function 工作表拆分到多个工作簿(){
	for (let ws of Sheets){
		if (ws.Name.split("-")[1]=="A"){//拆分成数组
			ws.Copy();
			ActiveWorkbook.SaveAs(`${Workbooks("3-11.xlsm").Path}\\类别\\${ws.Name}.xlsx`);
			ActiveWorkbook.Close();
		}
	}
}

JavaScript split() 方法

function 将月工作表按季拆分到工作簿(){
	for (let intQ of [1,2,3,4]){
		let wb=Workbooks.Add();
		wb.SaveAs(`${Workbooks.Item("3-12.xlsm").Path}\\季度\\第${intQ}季度.xlsx`);
	}
	for (let intNum=1;intNum<=12;intNum++){
		let intN=parseInt((intNum+2)/3);
		let wbTarget=Workbooks.Item(`第${intN}季度.xlsx`);
		let wbSource=Workbooks.Item("3-12.xlsm");
		wbSource.Sheets(intNum).Copy(undefined,wbTarget.Sheets(wbTarget.Sheets.Count));
	}
	for (let wb of Workbooks){
		if (wb.Name!="3-12.xlsm"){
			wb.Sheets(1).Delete();
			wb.Close(true);
		} 
	}
}

JavaScript parseInt() 函数

三、单元格操作

向单元格写入数据的几种方法

function 向单元格写入数据的几种方法(){
	//1
	Sheets(1).Range("a4:d5").Copy(Sheets(2).Range("a1")
	//2
	Sheets(1).Range("a4:d5").Copy();
	Sheets(2).Range("a4").PasteSpecial(xlPasteValuesAndNumberFormats);
	//3
	Sheets(2).Range("a9:d11").Value2=Sheets(1).Range("a4:d5").Value()
}

单元格的相对定位

1.Range.Range		在原来位置重定位
2.Range.EntireRow		单元格所在行
3.Range.EntireColumn	单元格所在列

function test(){
	Range("a5").Range("a5").Range("b3").Select();
	Range("a5:a10").EntireRow.Select()
	Range("a5:c10").EntireColumn.Select()
	Range("c5:c10").EntireColumn.Range("a1:a2").Select()
}

单元格偏移与区域重选

Range.Item	偏移方法-1
Range.Offset	偏移方法-2（可以返回Range对象）
Range.Resize	区域重选（可以返回Range对象）

function test(){
	Range("b2").Item(2,3).Select();
	Range("b2:c4")(2,3).Select();
	
	Range("b2").Offset(2,3).Select();
	Range("b2:c4").Offset(2,3).Select();

	Range("b2").Resize(2,3).Select();
	Range("b2:c4").Resize(2,3).Select();
}

应用

1、模仿条件格式着色

function test(){
	Range("a2:d11").Interior.Pattern = xlPatternNone;//清除色彩
	for (let rngCell of Range("d2:d11")){
		if (rngCell.Value()>0){
//			rngCell.EntireRow.Range("a1:d1").Interior.ColorIndex=8;
			rngCell.Offset(0,-3).Resize(1,4).Interior.ColorIndex=8;
		}
	}
}

2、按条件筛选数据

function test(){
	let intRowNum=1;
	let intVal=Number(InputBox("请输入筛选的最低值："));
	Range("g2:j99").Clear();
	for (let rngCell of Range("d2:d11")){
		if (rngCell.Value()>=intVal){
			intRowNum++;
			rngCell.Offset(0,-3).Resize(1,4).Copy(Range(`g${intRowNum}`));
		}
	}
}

3、单元格的边界获取

Range.End(xlUp)		//向上
Range.End(xlDown)	//向下
Range.End(xlToLeft)	//向左
Range.End(xlToRight)	//向右

function test(){
	Range("c8").End(xlDown).Select();
	Range("c8").End(xlUp).Select();
	Range("c8").End(xlToLeft).Select();
	Range("c8").End(xlToRight).Select();
	Range("a1",Range("d"+Rows.Count).End(xlUp)).Select();
	a =Cells(Rows.Count,"A").End(xlUp).Row
}

4、单元格已使用区域

Range.CurrentRegion	//获取Range周围所有单元格区域
Sheet.UsedRange		//获取Sheet表所有已使用单元格区域

function test(){
	Range("g2").CurrentRegion.Select();
	Sheets(1).UsedRange.Select();
}

5、单元格的并集与交集

function test(){
	Union(Range("a2"),Range("a9")).Select();
	Intersect(Range("a1").CurrentRegion,Range("a2:d999")).Select();
	Intersect(Sheets(1).UsedRange,Range("a2:d999")).Select();
}

四、交互操作（用户自定义选择文件）

　　MsoFileDialogType 可为以下 MsoFileDialogType 常量之一。
　　　　1.msoFileDialogFilePicker 		允许用户选择文件。
　　　　2.msoFileDialogFolderPicker	允许用户选择一个文件夹。
　　　　3.msoFileDialogOpen  		允许用户打开文件。
　　　　4.msoFileDialogSaveAs  		允许用户保存一个文件。

1、2只能选择文件或文件夹，而3、4则可以通过Execute方法来实际打开或者保存文件。

function 选择文件(){
	var f=Application.FileDialog(msoFileDialogFilePicker);
	f.Show();
	Console.log(f.SelectedItems(1));
	Console.log(f.SelectedItems(2));//只选择一个文件会出错
}

五、文件管理（新建、删除、复制）

新建：MkDir ("C:\NewFolder") //新建文件夹（如果存在则提示错误）
删除文件：Kill ("C:\OldFolder\OldFile.ext") //删除文件（支持同配符）
删除文件夹：RmDir("C:\OldFolder")  //删除文件夹（如果文件夹不存在，或者文件夹不为空均会提示错误）
移动文件：Name "C:\OldFolder\OldFile.ext" As "C:\NewFolder\NewFile.ext" //(暂时不可用)
复制文件：FileCopy ("C:\OldFolder\OldFile.ext", "C:\NewFolder\NewFile.ext")

function test(){
//	MkDir("D:\\WPS JS宏视频教程\\第3章\\3-22\\demo");
//	RmDir("D:\\WPS JS宏视频教程\\第3章\\3-22\\demo");
	FileCopy(
		"D:/WPS JS宏视频教程/第3章/3-22/123/demo.xlsx",
		"D:/WPS JS宏视频教程/第3章/3-22/456/999.xlsx"
			)
}

六、实例

修改图片名称

function 修改图片名称(){
	let intnum = 0;
	let strpath = Dir(ThisWorkbook.Path + "/图片/*.png");
	while(strpath != ""){
		intnum ++;
		strsource = ThisWorkbook.Path+"/图片/"+strpath;
		strpath2 = ThisWorkbook.Path+"/修改后/"+intnum+"-"+strpath;
		FileCopy(strsource,strpath2);
		strpath = Dir();
	}
}

二维表转一维表

function test2(){
	let rngArea = Range("c2",Range("e"+Rows.Count).End(xlUp));
	let intnum = 9;
	for(let rngCell of rngArea){
		if(rngCell.Value2>=100){
			let rngRow = rngCell.EntireRow;
			let rngCol = rngCell.EntireColumn;
			let arr=[rngRow.Range("a1").Value(),
				rngRow.Range("b1").Value(),
				rngCol.Range("a1").Value(),
				rngCell.Value()]
			Range("g"+intnum).Resize(1,4).Value2 = arr;
			intnum++;
		}
	}
}

多表数据合并

function test2(){
	Range("a2:d"+Rows.Count).ClearContents();
	for(let sht of Sheets){
		if(sht.Name != "合并结果"){
			let rng = sht.Range("a2",sht.Range("a2").End(xlDown).End(xlToRight));
			let rngDestination = Range("a"+Rows.Count).End(xlUp).Offset(1,0);
			rngDestination.Resize(rng.Rows.Count,rng.Columns.Count).Value2 = rng.Value();
			rngDestination.Offset(0,3).Resize(rng.Rows.Count,1).Value2 = sht.Name;
		}
	}
}

多工作簿数据合并

function demo(){
	Range("a2:e9999").ClearContents();
	let wbThis=Workbooks.Item("合并.xlsm");
	let strFileName=Dir(wbThis.Path + "\\分数表\\*.xlsm");
	while (strFileName!=""){
		let wb=Workbooks.Open(wbThis.Path + "\\分数表\\" + strFileName);
		for (let ws of wb.Sheets){
			let rngSource=ws.Range("a2",ws.Range("a2").End(xlDown).End(xlToRight));
			let rngDestination=wbThis.Sheets(1).Range("a65536").End(xlUp).Offset(1,0);
			rngDestination.Resize(rngSource.Rows.Count,3).Value2=rngSource.Value();
			rngDestination.Offset(0,3).Resize(rngSource.Rows.Count,1).Value2=ws.Name;
			rngDestination.Offset(0,4).Resize(rngSource.Rows.Count,1).Value2=wb.Name;
		}
		wb.Close(false);
		strFileName=Dir();
	}
	
}

将数据拆分到不同工作表

在这里插入图片描述

function test(){
	let wb=Workbooks.Item("3-27.xlsm");
	let wbNew=Workbooks.Add();
	for (let strWsName of ["Word大全","Excel大全","函数之道"]){
		let wsNew=wbNew.Sheets.Add();
		wsNew.Name=strWsName;
		wsNew.Range("a1:d1").Value2=["日期","编号","品名","库存"]
	}
	let rngBooks=wb.Sheets(1).Range("c2",wb.Sheets(1).Range("c65536").End(xlUp));
	for (let rngCell of rngBooks){
		rngCell.EntireRow.Range("a1:d1").Copy(wbNew.Sheets(rngCell.Value()).Range("a65536").End(xlUp).Offset(1,0))
	}
	wbNew.Sheets("Sheet1").Delete();
	wbNew.SaveAs(wb.Path + "\\结果.xlsx")
	wbNew.Close();
}

批量提取请假条信息

function test(){
	Range("a3:j999").ClearContents();
	let wb=Workbooks.Item("3-28.xlsm");
	let strFileName=Dir(wb.Path + "\\请假条\\*.xlsx");
	let intNum=2;
	while (strFileName!=""){
		let wbOpen=Workbooks.Open(wb.Path + "\\请假条\\" + strFileName);
		let ws=wbOpen.Sheets(1);
		let arr=[
			ws.Range("b3").Value(),//申请日期
			ws.Range("f3").Value(),//请假类别
			ws.Range("b4").Value(),//部门
			ws.Range("d4").Value(),//姓名
			ws.Range("f4").Value(),//职务
			ws.Range("b5").Value(),//起始日期
			ws.Range("d5").Value(),//结束日期
			ws.Range("f5").Value(),//天数
			ws.Range("b6").Value(),//部门主管审批
			ws.Range("e6").Value()//经理审批
				]
		wbOpen.Close(false);
		intNum++;
		Range("a" + intNum).Resize(1,10).Value2=arr;
		strFileName=Dir();
	}
}

工资表批量生成PDF工资条

在这里插入图片描述

function test(){
	let wb=Workbooks.Item("3-29.xlsm");
	let wbOpen=Workbooks.Open(`${wb.Path}\\模板.xlsx`);
	let rngID=wb.Sheets(1).Range("d10",wb.Sheets(1).Range("d65536").End(xlUp));
	for (let rngCell of rngID){		
		wbOpen.Sheets(1).Range("a4:r4").Value2=rngCell.Resize(1,18).Value();
		wbOpen.Sheets(1).ExportAsFixedFormat(xlTypePDF,`${wb.Path}\\工资条\\${rngCell.Value()}-${rngCell(1,2).Value()}.pdf`)
	}
	wbOpen.Close(false);
}

批量转换Excel文件类型

function test(){
	let strName=Dir(Workbooks.Item("3-30.xlsm").Path + "\\名单\\*.xls")
	let intnum=0
	while (strName!=""){
		let wb=Workbooks.Open(Workbooks.Item("3-30.xlsm").Path + "\\名单\\" + strName);
		intnum++
		wb.SaveAs(Workbooks.Item("3-30.xlsm").Path + "\\结果\\" + wb.Name.split(".")[0] + ".xlsx");
		wb.Close(false);
		strName=Dir();
	}
}

star星梦

关注

3
点赞
踩
17

收藏

觉得还不错? 一键收藏
打赏
0
评论
WPS常用表格对象应用

MsoFileDialogType 可为以下 MsoFileDialogType 常量之一。1.msoFileDialogFilePicker 允许用户选择文件。2.msoFileDialogFolderPicker 允许用户选择一个文件夹。3.msoFileDialogOpen 允许用户打开文件。4.msoFileDialogSaveAs 允许用户保存一个文件。1、2只能选择文件或文件夹，而3、4则可以通过Execute方法来实际打开或者保存文件。function 选择文件(){
复制链接

扫一扫