二维码打印-非EXCEL控件和非SmartForm方式

项目遇到奇葩问题，优化工票打印增加二维码的功能时发现用户的电脑不支持EXCEL的二维码控件，但是二维码是车间MES APP 派工的必要条件而且现在的打印程序已经是EXCEL打印，excel模板有四个sheet页，通过SMARTFORM打印开发的工作量太大，给用户重装软件风险也是太大。因手里刚好有一个C语言编写的画二维码程序，被逼无奈用了一个奇葩思路，通过EXCEL的VBA调用EXE程序，生成一个图像，再通过VBA将图像插入EXCEL。

第一步，更改SMW0可以上传EXE类型文件。将可以绘制二维码的EXE文件类型上传至ERP服务器。

第二步，在代码中实现下载exe程序的逻辑，将exe文件放在固定文件夹。代码逻辑要控制判断文件路径是否存在，不存在则创建。exe程序同理，不存在则下载。

FORM DOWNLOAD_BARCODE.
  DATA: LV_OBJDATA LIKE WWWDATATAB,
        LV_OBJ_NAME LIKE WWWDATATAB-OBJID,
        LV_DESTINATION LIKE RLGRAP-FILENAME,
        LV_OBJID LIKE SY-REPID,
        LV_SUBRC LIKE SY-SUBRC.
  DATA:L_RET TYPE ABAP_BOOL,
       LV_ANSWER.
  DATA:LV_FILE TYPE STRING.
  DATA:L_PATH TYPE CHAR128,            " 下载后存放路径
       G_FILEN LIKE WWWDATATAB-OBJID.

  CLEAR L_PATH.
  CONCATENATE 'C:\ERP-DATA\'  'QRmake.exe' INTO L_PATH.

  MOVE L_PATH TO LV_FILE.
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_EXIST
    EXPORTING
      FILE                 = LV_FILE
    RECEIVING
      RESULT               = L_RET
    EXCEPTIONS
      CNTL_ERROR           = 1
      ERROR_NO_GUI         = 2
      WRONG_PARAMETER      = 3
      NOT_SUPPORTED_BY_GUI = 4
      OTHERS               = 5.

  IF SY-SUBRC <> 0.
    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
               WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    EXIT.
  ENDIF.

  IF L_RET <> 'X'.
    G_FILEN = 'ZQRMAKE'.
*    IF
    MOVE G_FILEN TO LV_OBJ_NAME.

    SELECT RELID OBJID
      FROM WWWDATA
      INTO  CORRESPONDING FIELDS OF LV_OBJDATA
      UP TO 1 ROWS
      WHERE SRTF2 = 0 AND RELID = 'MI'
        AND OBJID = LV_OBJ_NAME.
    ENDSELECT.

    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        KEY         = LV_OBJDATA
        DESTINATION = L_PATH
      IMPORTING
        RC          = LV_SUBRC.
    IF LV_SUBRC = 0.

    ENDIF.
  ENDIF.
ENDFORM 

第三步，ABAP打印工票程序中打印时调用VBA宏：

 

  CALL METHOD DOCUMENT->EXECUTE_MACRO
            EXPORTING
              MACRO_STRING = 'GP.MakeQRCode'
*                param1       = line_count
*                param_count  = 1
            IMPORTING
              ERROR        = ERRORS.

第四步，在execl模板中编写VBA代码，调用绘制二维码的程序，并将图片插入excel。

Private Point01 As Long, Point02 As Long, Point03 As Long
Private i As Integer

Sub MakeQRCode()
    If Dir("C:\ERP-DATA\QRmake.exe") = "" Then
        MsgBox "QRmake.exe文件丢失，请确认！", vbCritical, "外部程序调用"
        Exit Sub
    End If
    Sheets("GP").Select
    Range("I4:J7").Select
    i = MK_QR(ActiveCell.Value, "10", "5")
End Sub

Function MK_QR(Enc_Dat, ECL, SIZ)
    Dim F_Name As String
    F_Name = "[" & ActiveWorkbook.Name & "]" & ActiveSheet.Name & "!" & ActiveCell.Address
    Point01 = Shell("""" & "C:\ERP-DATA\QRmake.exe""" & " /S" & SIZ & " /L" & ECL + 1 & " /O""" & ThisWorkbook.Path & "\" & F_Name & ".bmp"" /T""" & Enc_Dat & """")
    Point02 = OpenProcess(&H100000, 1, Point01)
    Point03 = WaitForSingleObject(Point02, &HFFFFFFFF)
    Point03 = CloseHandle(Point02)
    Point01 = Empty
    Point02 = Empty
    Point03 = Empty
    ActiveCell.Offset(0, 0).Select
    With ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\" & F_Name & ".bmp")
        .Left = ActiveCell.Left
        .Top = ActiveCell.Top
    End With
     
    ActiveSheet.Shapes(1).Select    '选中第1个图形
    'ActiveSheet.Shapes("Picture 1").Select '或选中名称为Picture 1的图片
    With Selection
        rh = ActiveCell.MergeArea.Height '读取当前单元格(或合并单元格)的高度
        rw = ActiveCell.MergeArea.Width '读取当前单元格(或合并单元格)的宽度
        ph = .Height  '读取当前图形的高度
        pw = .Width  '读取当前图形的宽度
        'f = IIf(rh / ph < rw / pw, rh / ph, rw / pw) '计算不超出格子的允许最大的放大/缩小比例

        .Height = .Height * 0.7    '按该比例调整图形高度
        .Width = .Width * 0.7      '按该比例调整图形宽度
        
        .Top = ActiveCell.Top + (ActiveCell.MergeArea.Height - .Height) / 2 '设置图片顶位置
        .Left = ActiveCell.Left + (ActiveCell.MergeArea.Width - .Width) / 2 '设置图片左位置
    End With
    
     
    '将已经生成的二维码图像删除
    Kill (ThisWorkbook.Path & "\" & F_Name & ".bmp")
    ActiveCell.Offset(0, -1).Select
End Function

效果与EXCEL控件打印效果一致：

 

  欢迎关注公众号：总钻风来巡山~~~~~