VB学习生成JavaBean

Application.ActiveWorkbook.Path 获取当前excel文件所在的文件地址

 

Excel VBA中表示当前工作簿,有Activeworkbook和Thisworkbook 两种表示方法,二者的相同点和区别是什么?

thisworkbook指当前VBA代码所处的Workbook,
Activeworkbook指当前活跃的Workbook

相同点：如果VBA代码只对本身工作簿进行操作，则一直是相同的
不同点：若VBA代码新建或打开了其它工作簿，则往往新建的或刚打开的是Activeworkbook，可以通过 “工作簿名。active”方法激活指定对象

用InputBox函数，功能是弹出一个对话框，在其中显示提示，等待用户输入文字并按下按钮，然后返回用户输入的文字。
Text1 = InputBox("请在下面输入内容：", "输入框", "默认值")

Cells(Rows.Count, 1).End(xlUp).Row的含义

    NextRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

    这句话的意思是 取活动单元表的第一列最后一个有值的行的下一行行号。

 

Rows.Count是指当前活动工作表的行数，为数字 1048576，很熟悉的一个数字，为Excel工作表的最大行数

 

Cells(Rows.Count, 1)，则是定位到第一列的最后一行cell，即单元格“A1048576”

 

主要是End(xlUp)比较难理解。

Range.End属性是返回一个 Range 对象，该对象代表包含源 区域的区域尾端的单元格。 

End(xlUp)等同于End mode下+UP键，或Ctrl+Up

 

IF 条件 THEN    END IF结尾

 

For  步长 To   最长

 Next

 

写文件  Print #1, "/**"

 

 

Sub convertjava()

'java文件名称获取
  Dim javaName As String
  
  Dim javaNote As String
 '定义步长
  Dim a&, b&, c&
  '列的步长
  Dim columnCount&, j&, k&, m&
  
  Dim columnName As String
  
  Dim name As String
  
  Dim typeName As String
  
  
   '表字段所在位置
    j = 2
   '表字段类型所在位置
    k = 3
   '表字段中文名称所在位置
    m = 1
    '表中文名称所在位置
    b = 1
    '表名称所在位置
    c = 2
  
  Text1 = InputBox("请输入包名：", "输入框", " ")
   
  If (InStr(Text1, " ") = 1) Then
     MsgBox "包名输入不正"
     
  Else
    For a = 2 To ActiveWorkbook.Worksheets.Count
      '获取表名称
      javaName = ActiveWorkbook.Worksheets(a).Cells(c, 1).Value
      '截取第一个无用字符串
      javaName = Mid(javaName, 2, Len(javaName))
      '字符串替换，下划线替换成空格
      javaName = Replace(javaName, "_", " ")
      '将表名称全部修改成首字母大写
      javaName = StrConv(javaName, vbProperCase)
      '将空格替换成空
      javaName = Replace(javaName, " ", "")
      
      '写文件
      Open Application.ActiveWorkbook.Path & "/" & javaName & ".java" For Output As #1
      
      Print #1, "package  " & Text1
      
      Print #1, "/**"
      Print #1, " *"
      Print #1, " *" & ActiveWorkbook.Worksheets(a).Cells(b, 1).Value
      Print #1, " *"
      Print #1, " */"
      
      Print #1, "public class " & javaName & "{"
      
      columnCount = ActiveWorkbook.Worksheets(a).Cells(Rows.Count, 1).End(xlUp).Row
      
      For i = 4 To columnCount + 1
         
            If (ActiveWorkbook.Worksheets(a).Cells(i, 4).Value <> 0 And ActiveWorkbook.Worksheets(a).Cells(i, 4).Value <> "") Then
            
             columnName = ActiveWorkbook.Worksheets(a).Cells(i, j).Value
            '字符串替换，下划线替换成空格
            columnName = Replace(columnName, "_", " ")
            '将表名称全部修改成首字母大写
            columnName = StrConv(columnName, vbProperCase)
            '将空格替换成空
            columnName = Replace(columnName, " ", "")
            
            columnName = LCase(Left(columnName, 1)) & Right(columnName, Len(columnName) - 1)
            
            Print #1, "private String " & columnName & ";//" & ActiveWorkbook.Worksheets(a).Cells(i, m).Value
            
            
            End If
        Next
        
        For i = 4 To columnCount + 1
        
          
            If (ActiveWorkbook.Worksheets(a).Cells(i, 4).Value <> 0 And ActiveWorkbook.Worksheets(a).Cells(i, 4).Value <> "") Then
            
                 columnName = ActiveWorkbook.Worksheets(a).Cells(i, j).Value
                '字符串替换，下划线替换成空格
                columnName = Replace(columnName, "_", " ")
                '将表名称全部修改成首字母大写
                columnName = StrConv(columnName, vbProperCase)
                '将空格替换成空
                columnName = Replace(columnName, " ", "")
                
                typeName = ActiveWorkbook.Worksheets(a).Cells(i, k).Value
                
                If 1 = 1 Then
                
                End If
                
                name = LCase(Left(columnName, 1)) & Right(columnName, Len(columnName) - 1)
                
                Print #1, "/**"
                Print #1, " *"
                Print #1, " * get  " & ActiveWorkbook.Worksheets(a).Cells(i, m).Value
                Print #1, " *"
                Print #1, " */"
                Print #1, " public String get" & columnName & "( ){"
                Print #1, "     return " & name & ";"
                Print #1, "}"
                Print #1, "/**"
                Print #1, " *"
                Print #1, " * set  " & ActiveWorkbook.Worksheets(a).Cells(i, m).Value
                Print #1, " *"
                Print #1, " */"
                Print #1, " public void set" & columnName & "( String " & name & "){"
                Print #1, "     this." & name & " = " & name & ";"
                Print #1, "}"
              End If
        Next
      Print #1, "}"
      Close #1
      Next a
      
      Close #1
    
      MsgBox "生成Java文件成功"
  
  End If
  
  
End Sub