在jeecgboot中制作查询报表非常方便,可以在online报表配置中快速完成。
在数据库中可以看到对应的两张表存放这些配置,一张是onl_cgreport_head,存放查询表的头部信息。
另一张是明细表onl_cgreport_item,存放查询报表的字段信息。
在开发数据大屏时需要很多查询数据,可以先在online报表中配置查询SQL,通过报表查看需要的数据,然后用VBA通过数据库中的这两张表可以生成jeecgboot后端接口代码。
首先在MySQL 官网上下载 Excel 连接 MySQL 数据库的工具,连接为:
https://dev.mysql.com/downloads/windows/excel/
连接mysql的方法
Public Sub OpenConnection(Server As String, PORT As Long, DB As String, UID As String, PWD As String)
Set conn = New ADODB.Connection
conn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & Server _
& ";Port=" & PORT & ";DB=" & DB & ";UID=" & UID & ";PWD=" & PWD & ";OPTION=3;"
conn.Open
End Sub
查询SQL数据并显示到listview的方法
Public Sub dataToListView(sql As String, lv As ListView)
lv.ListItems.Clear
lv.ColumnHeaders.Clear
Dim i, j
Dim rc As New ADODB.Recordset
rc.Open sql, conn, adOpenStatic
For i = 0 To rc.Fields.Count - 1
lv.ColumnHeaders.Add , , rc.Fields(i).Name
Next i
rc.MoveFirst
i = 1
Do Until rc.EOF
lv.ListItems.Add , , rc.Fields(0)
For j = 1 To rc.Fields.Count - 1
If Not IsNull(rc.Fields(j)) Then lv.ListItems(i).SubItems(j) = rc.Fields(j)
Next j
i = i + 1
rc.MoveNext
Loop
rc.Close
Set rc = Nothing
End Sub
将上面方法放到类模块中,就可以实例化对象来使用方法。
如连接数据库,将编码以"sql_"开头的的SQL报表显示到listview1中
msql.OpenMySQLConnection IP, PORT, DATABASE, USERNAME, PASSWORD
msql.dataToListView "select * from onl_cgreport_head where left(code,4)=""sql_""", ListView1
选择SQL报表,显示明细到listview2中。
Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
msql.dataToListView "select * from onl_cgreport_item where cgrhead_id=""" & Item.Text & """", ListView2
End Sub
一键生成文件夹、java和xml文件
If ListView1.SelectedItem Is Nothing Then
Else
folderPath = ThisWorkbook.Path & "\api"
entity = Replace(ListView1.SelectedItem.SubItems(1), "sql_", "")
desc = ListView1.SelectedItem.SubItems(2)
cf.MakeFolder folderPath
cf.MakeFolder folderPath & "\" & entity
cf.MakeFolder folderPath & "\" & entity & "\controller"
createController
cf.MakeFolder folderPath & "\" & entity & "\entity"
createEntity
cf.MakeFolder folderPath & "\" & entity & "\mapper"
cf.MakeFolder folderPath & "\" & entity & "\mapper\xml"
createXML
createMapper
cf.MakeFolder folderPath & "\" & entity & "\service"
createService
cf.MakeFolder folderPath & "\" & entity & "\service\impl"
createImpl
End If
其中生成实体类的代码
Dim cf As New CFileAction
With cf
.Clear
.WriteText "package org.jeecg.modules.demo.api." & entity & ".entity;" & vbCrLf
.WriteText "import lombok.Data;" & vbCrLf
.WriteText "@Data" & vbCrLf
.WriteText "public class " & entity & " {" & vbCrLf
For i = 1 To ListView2.ListItems.Count
.WriteText " private " & ListView2.ListItems(i).SubItems(5) & " " & ListView2.ListItems(i).SubItems(2) & ";" & vbCrLf
Next i
.WriteText "}" & vbCrLf
WriteUTF8File .txt, folderPath & "\" & entity & "\entity\" & entity & ".java"
End With
Set cf = Nothing
生成java代码时需要的是没有BOM的UTF-8编码,一开始使用ADODB.Stream对象来保存java文件,IDEA打开后每个文件都要切换GBK再换回UTF-8编码才能正常使用。后来用以下api函数来保存java文件,默认bBOM为false,生成的代码可以直接运行使用。
Public Declare PtrSafe Function MultiByteToWideChar Lib "kernel32" ( _
ByVal CodePage As Long, _
ByVal dwFlags As Long, _
ByRef lpMultiByteStr As Any, _
ByVal cchMultiByte As Long, _
ByVal lpWideCharStr As Long, _
ByVal cchWideChar As Long) As Long
Public Declare PtrSafe Function WideCharToMultiByte Lib "kernel32" ( _
ByVal CodePage As Long, _
ByVal dwFlags As Long, _
ByVal lpWideCharStr As LongPtr, _
ByVal cchWideChar As Long, _
ByRef lpMultiByteStr As Any, _
ByVal cchMultiByte As Long, _
ByVal lpDefaultChar As String, _
ByVal lpUsedDefaultChar As Long) As Long
Public Const CP_UTF8 = 65001
‘ 将输入文本写进UTF8格式的文本文件
‘ 输入
‘ strInput:文本字符串
‘ strFile:保存的UTF8格式文件路径
‘ bBOM:True表示文件带"EFBBBF"头,False表示不
Public Sub WriteUTF8File(strInput As String, strFile As String, Optional bBOM As Boolean = False)
Dim bByte As Byte
Dim ReturnByte() As Byte
Dim lngBufferSize As Long
Dim lngResult As Long
Dim TLen As Long
‘ 判断输入字符串是否为空
If Len(strInput) = 0 Then Exit Sub
‘On Error GoTo errHandle
‘ 判断文件是否存在,如存在则删除
If Dir(strFile) <> "" Then Kill strFile
TLen = Len(strInput)
lngBufferSize = TLen * 3 + 1
ReDim ReturnByte(lngBufferSize - 1)
lngResult = WideCharToMultiByte(CP_UTF8, 0, StrPtr(strInput), TLen, ReturnByte(0), lngBufferSize, vbNullString, 0)
If lngResult Then
lngResult = lngResult - 1
ReDim Preserve ReturnByte(lngResult)
Open strFile For Binary As #1
If bBOM = True Then
bByte = 239
Put #1, , bByte
bByte = 187
Put #1, , bByte
bByte = 191
Put #1, , bByte
End If
Put #1, , ReturnByte
Close #1
End If
End Sub
api接口测试成功。