OpenOffice Writer宏开发：获取查询总记录数并跳转下一条记录

一、跳转至下一条数据库记录 #

直接利用DispatchHelper调用OpenOffice内置的数据库下一条记录命令即可实现跳转，替换原有注释行的代码如下：

' 跳转至下一条数据库记录
dispatcher.executeDispatch(odoc.getCurrentFrame(), ".uno:DBNextRecord", "", 0, Array())

如果需要通过插入文档的数据库字段触发跳转，也可以将创建好的oField插入文档并执行刷新：

' 将下一条记录字段插入文档并触发跳转
oVCur.getText().insertTextContent(oVCur, oField, False)
oField.refresh()

二、获取查询的全部记录数量 #

通过SDBC连接数据库，执行COUNT查询统计记录总数，添加以下代码：

' 获取查询的全部记录数量
Dim oContext As Object
Dim oDataSource As Object
Dim oConnection As Object
Dim oStatement As Object
Dim oResultSet As Object
Dim nRecordCount As Integer

' 获取服务上下文并连接数据库
oContext = GetDefaultContext()
oDataSource = oContext.getValueByName("/singletons/com.sun.star.sdb.DatabaseContext").getByName(NameSource)
oConnection = oDataSource.getConnection("", "") ' 无密码则留空用户名和密码参数

' 执行COUNT查询
oStatement = oConnection.createStatement()
oResultSet = oStatement.executeQuery("SELECT COUNT(*) FROM " & nameTable)

' 读取统计结果
If oResultSet.next() Then
    nRecordCount = oResultSet.getInt(1)
    MsgBox "查询记录总数：" & nRecordCount, 0, "记录统计"
End If

' 关闭数据库资源
oResultSet.close()
oStatement.close()
oConnection.close()

完整整合代码 #

odoc = thiscomponent
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oVCur = oDoc.getCurrentController.getViewCursor ' 获取当前视图光标
' 请确保NameSource和nameTable已定义为实际的数据库名和查询名

' 跳转至下一条数据库记录
dispatcher.executeDispatch(odoc.getCurrentFrame(), ".uno:DBNextRecord", "", 0, Array())

' 获取查询的全部记录数量
Dim oContext As Object
Dim oDataSource As Object
Dim oConnection As Object
Dim oStatement As Object
Dim oResultSet As Object
Dim nRecordCount As Integer

oContext = GetDefaultContext()
oDataSource = oContext.getValueByName("/singletons/com.sun.star.sdb.DatabaseContext").getByName(NameSource)
oConnection = oDataSource.getConnection("", "")
oStatement = oConnection.createStatement()
oResultSet = oStatement.executeQuery("SELECT COUNT(*) FROM " & nameTable)
If oResultSet.next() Then
    nRecordCount = oResultSet.getInt(1)
    MsgBox "查询记录总数：" & nRecordCount, 0, "记录统计"
End If

oResultSet.close()
oStatement.close()
oConnection.close()

内容的提问来源于stack exchange，提问作者Fab