12. WPS报表JS宏查询加入筛选条件

本节将在宏查询的SQL语句加入筛选条件。

在上一节中使用常量定义了whereStr，在本节中，将这个语句设定成从报表上面标黄的查询条件中动态获取。

修改查询按钮代码如下：

 //查询按钮的单击事件
function CommandButton1_Click()
{
  	//使用变量ReportSh 引用工作表对象：报表
	let ReportSh = Application.Worksheets.Item("报表")
	
	 //使用变量ReportQuery 引用报表工作表内的查询对象
    let ReportQuery = ReportSh.QueryTables.Item(1)
    
    //DictSh 引用工作表对象：字典
	let DictSh = Application.Worksheets.Item("字典")	
   
     //先定义一个 where 语句来测试 sql 语句
    //const whereStr = 'where (left([单位],6)="101006") and ([计划月份] between "01" and "05")'
    /******* 注释掉上面的语句，使用getWhereStr函数动态替换 ***/
     /* 
     *	将字典工作表D1单元格存储的sql语句取出
     *	并使用 getWhereStr函数的返回值替换语句中的 ？
     */
    let sqlstr = DictSh.Range("D1").Value2.replace(/\?/g, getWhereStr(ReportSh))
  //  Console.log(sqlstr)
  
  	//将sqlstr 赋值给查询对象，并刷新查询
    ReportQuery.CommandText = sqlstr
    ReportQuery.Refresh()
}

切换到lib模块，将 getWhereStr()这个函数完善。代码如下：

    /*  函数名：getWhereStr 获取报表工作表上设定的条件
     *	参   数：传入一个参数Sh 为工作表的引用
     *	返回值：sql语句
     */
function getWhereStr(Sh)
{
	//定义where语句, true后面方便加语句，即便不加语句，也不会出错
	let WhereStr = " where true"	
	
	//定义单位名称 使用Text属性，适应单元格为空的情况
  //单元格为空时，属性Value2是undefined，不能使用substr
  //非要使用Value2，则可以：if (Sh.Range('f1').Value2){ alert("可用")} else {alert("不可用")}
	let UnitName = Sh.Range('f1').Text.substr(0,7)
	
	//加入条件字符串 如果单位选择全部或者没选择单位 则去掉单位筛选 
	if (UnitName == '' || UnitName =='0-全部'){}
	else {		
		WhereStr += " and (left([单位],7) = '" + UnitName + "')"
	}
	/* 三元表达式可精简上面if语句，但影响可读性：
    WhereStr += UnitName == '' || UnitName =='0-全部' ? "" :
                          " and (left([单位],7) = '" + UnitName + "')" 
   */
	//定义设置月份 也就是结束的月份
	let EndMonth = Sh.Range('c1').Text.substr(0,2)
	
	//定义月份期间 也就是开始的月份
	let BeginMonth = Sh.Range('c2').Text
	
	//没有设置月份的时候，不进行筛选，即提取所有月份的数据
	if (EndMonth !=''){
    
		/* 定义开始月份 并定义默认值为'01' 即年初
    变量(赋值符号)(条件) ? (表达式1):(表达式2) 
    三元表达式，条件为真时，返回表达式1，否则返回表达式2
    可以在表达式1和2中嵌套三元表达式使用 */
    
		BeginMonth = BeginMonth == '当月' ? EndMonth : '01'		
    
		//加入条件字符串 如果开始月份与结束月份一致，则使用=，否则使用between and
		WhereStr +=  BeginMonth == EndMonth ? 
			" and ([计划月份] = '" + EndMonth +"')" : 									
			" and ([计划月份] between '" + BeginMonth + "' and '"+ EndMonth +"')"			
	}
  //返回sql语句
	return WhereStr
}

在【报表】工作表中，点击【查询】按钮，可以看到数据被刷新了。

修改【报表】工作表上面的条件，可以看到不同的报表数据。

本节到此结束，下一节，将完善查询明细的按钮代码。