not null

2016年10月25日星期二

eclipse neon java1.8更新後起動してない。

eclipse.iniにvmを追加する。old versionの指定
-vm
C:\Tool\jre1.8.0_101\bin\client\jvm.dll

2016年9月22日星期四

正規表現

１．含まない、含む両方の行。

例えば、「AAA」という文字列を含むが、「BBB」という文字列は含まない行を抽出するには以下のようにする。

^(?!.*BBB).*(?=AAA).*$

AAA ⇒ ヒットする
BBB ⇒ ヒットしない
AAABBB ⇒ ヒットしない
AAACCC ⇒ ヒットする

2．常用正则表达式。

匹配中文字符的正则表达式： [\u4e00-\u9fa5]
　　评注：匹配中文还真是个头疼的事，有了这个表达式就好办了

　　匹配双字节字符(包括汉字在内)：[^\x00-\xff]
　　评注：可以用来计算字符串的长度（一个双字节字符长度计2，ASCII字符计1）

　　匹配空白行的正则表达式：\n\s*\r
　　评注：可以用来删除空白行

　　匹配HTML标记的正则表达式：<(\S*?)[^>]*>.*?</\1>|<.*? />
　　评注：网上流传的版本太糟糕，上面这个也仅仅能匹配部分，对于复杂的嵌套标记依旧无能为力

　　匹配首尾空白字符的正则表达式：^\s*|\s*$
　　评注：可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式

　　匹配Email地址的正则表达式：\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*
　　评注：表单验证时很实用

　　匹配网址URL的正则表达式：[a-zA-z]+://[^\s]*
　　评注：网上流传的版本功能很有限，上面这个基本可以满足需求

　　匹配帐号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：^[a-zA-Z][a-zA-Z0-9_]{4,15}$
　　评注：表单验证时很实用

　　匹配国内电话号码：\d{3}-\d{8}|\d{4}-\d{7}
　　评注：匹配形式如 0511-4405222 或 021-87888822

　　匹配腾讯QQ号：[1-9][0-9]{4,}
　　评注：腾讯QQ号从10000开始

　　匹配中国邮政编码：[1-9]\d{5}(?!\d)
　　评注：中国邮政编码为6位数字

　　匹配身份证：\d{15}|\d{18}
　　评注：中国的身份证为15位或18位

　　匹配ip地址：\d+\.\d+\.\d+\.\d+
　　评注：提取ip地址时有用

　　匹配特定数字：
　　^[1-9]\d*$　　 //匹配正整数
　　^-[1-9]\d*$ 　 //匹配负整数
　　^-?[1-9]\d*$　　 //匹配整数
　　^[1-9]\d*|0$　 //匹配非负整数（正整数 + 0）
　　^-[1-9]\d*|0$　　 //匹配非正整数（负整数 + 0）
　　^[1-9]\d*\.\d*|0\.\d*[1-9]\d*$　　 //匹配正浮点数
　　^-([1-9]\d*\.\d*|0\.\d*[1-9]\d*)$　 //匹配负浮点数
　　^-?([1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0)$　 //匹配浮点数
　　^[1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0$　　 //匹配非负浮点数（正浮点数 + 0）
　　^(-([1-9]\d*\.\d*|0\.\d*[1-9]\d*))|0?\.0+|0$　　//匹配非正浮点数（负浮点数 + 0）
　　评注：处理大量数据时有用，具体应用时注意修正

　　匹配特定字符串：
　　^[A-Za-z]+$　　//匹配由26个英文字母组成的字符串
　　^[A-Z]+$　　//匹配由26个英文字母的大写组成的字符串
　　^[a-z]+$　　//匹配由26个英文字母的小写组成的字符串
　　^[A-Za-z0-9]+$　　//匹配由数字和26个英文字母组成的字符串
　　^\w+$　　//匹配由数字、26个英文字母或者下划线组成的字符串
　　评注：最基本也是最常用的一些表达式

2016年7月28日星期四

vba sheet copy, val copy.

Sub copyValToAnotherBook()
    Application.DisplayAlerts = False
    Dim srcSheet As Workbook
    Dim desSheet As Workbook
    Dim ws As Worksheet
    Dim vals As Variant
    '## Open both workbooks first:
    Set srcSheet = Workbooks.Open("C:\1\s.xlsx")
    Set desSheet = Workbooks.Open("C:\2\d.xlsx")

    '新規シート
    With ThisWorkbook
        Set ws = desSheet.Sheets.Add(After:=desSheet.Sheets(desSheet.Sheets.Count))
        ws.name = "news"
    End With
   Windows("s.xlsx").Activate
   srcSheet.Sheets("ss").Select
   srcSheet.Sheets("ss").Range("A1:B3").Select
   'Store the value in a variable:
   vals = srcSheet.Sheets("ss").Range("A1:B3").Value
    'Use the variable to assign a value to the other file/sheet:
    desSheet.Sheets("news").Range("A1:B3").Value = vals

    'Close x:
    srcSheet.Close
    desSheet.Save

    desSheet.Close
End Sub

Private Sub CreateSheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.name = "Tempo"
End Sub

Sub CopyAllSheetsToAnotherBook()
    Dim srcSheet As Workbook
    Dim desSheet As Workbook
    Dim currentSheet As Worksheet
    Dim sheetIndex As Integer
    sheetIndex = 1

    Set srcSheet = Workbooks.Open("C:\1\s.xlsx")
    Set desSheet = Workbooks.Open("C:\2\d.xlsx")

    srcSheet.Activate

    For Each currentSheet In Worksheets
        Windows("s.xlsx").Activate
        currentSheet.Select
        currentSheet.Copy Before:=Workbooks("d.xlsx").Sheets(desSheet.Sheets.Count)
        sheetIndex = sheetIndex + 1
    Next currentSheet

End Sub

Sub CopyOneSheetToAnotherBook()
    Application.DisplayAlerts = False 'dont show Msg when exit
    Dim srcSheet   As Workbook
    Dim desSheet   As Workbook
    Dim currentSheet As Worksheet
    Dim sheetIndex As Integer
    sheetIndex = 1

    Set srcSheet = Workbooks.Open("C:\1\s.xlsx")
    Set desSheet = Workbooks.Open("C:\2\d.xlsx")

    Windows("s.xlsx").Activate
    Sheets("ss2").Select
    Sheets("ss2").Copy Before:=Workbooks("d.xlsx").Sheets(desSheet.Sheets.Count)

     srcSheet.Close
     desSheet.Save

     desSheet.Close
End Sub

java robot method

import static java.awt.event.KeyEvent.*;
import java.awt.*;
import java.awt.datatransfer.Clipboard;
import java.awt.datatransfer.StringSelection;
import java.awt.event.KeyEvent;
public class RobotUtil {
private Robot robot;

//strをコーピして，貼り付け
public void pasteStr(String str) {
  //Ctrl + C (str)
  StringSelection selection = new StringSelection(str);
     Clipboard clipboard = Toolkit.getDefaultToolkit().getSystemClipboard();
     clipboard.setContents(selection, null);

     //Ctrl + V
     robot.keyPress(KeyEvent.VK_CONTROL);
  robot.keyPress(KeyEvent.VK_V);
  robot.keyRelease(KeyEvent.VK_V);
  robot.keyRelease(KeyEvent.VK_CONTROL);
}

public RobotUtil() throws AWTException {
  this.robot = new Robot();
}
public RobotUtil(Robot robot) {
  this.robot = robot;
}
public void type(CharSequence characters) {
  int length = characters.length();
  for (int i = 0; i < length; i++) {
   char character = characters.charAt(i);
   type(character);
  }
}
public void type(char character) {
  switch (character) {
  case 'a':
   doType(VK_A);
   break;
  case 'b':
   doType(VK_B);
   break;
  case 'c':
   doType(VK_C);
   break;
  case 'd':
   doType(VK_D);
   break;
  case 'e':
   doType(VK_E);
   break;
  case 'f':
   doType(VK_F);
   break;
  case 'g':
   doType(VK_G);
   break;
  case 'h':
   doType(VK_H);
   break;
  case 'i':
   doType(VK_I);
   break;
  case 'j':
   doType(VK_J);
   break;
  case 'k':
   doType(VK_K);
   break;
  case 'l':
   doType(VK_L);
   break;
  case 'm':
   doType(VK_M);
   break;
  case 'n':
   doType(VK_N);
   break;
  case 'o':
   doType(VK_O);
   break;
  case 'p':
   doType(VK_P);
   break;
  case 'q':
   doType(VK_Q);
   break;
  case 'r':
   doType(VK_R);
   break;
  case 's':
   doType(VK_S);
   break;
  case 't':
   doType(VK_T);
   break;
  case 'u':
   doType(VK_U);
   break;
  case 'v':
   doType(VK_V);
   break;
  case 'w':
   doType(VK_W);
   break;
  case 'x':
   doType(VK_X);
   break;
  case 'y':
   doType(VK_Y);
   break;
  case 'z':
   doType(VK_Z);
   break;
  case 'A':
   doType(VK_SHIFT, VK_A);
   break;
  case 'B':
   doType(VK_SHIFT, VK_B);
   break;
  case 'C':
   doType(VK_SHIFT, VK_C);
   break;
  case 'D':
   doType(VK_SHIFT, VK_D);
   break;
  case 'E':
   doType(VK_SHIFT, VK_E);
   break;
  case 'F':
   doType(VK_SHIFT, VK_F);
   break;
  case 'G':
   doType(VK_SHIFT, VK_G);
   break;
  case 'H':
   doType(VK_SHIFT, VK_H);
   break;
  case 'I':
   doType(VK_SHIFT, VK_I);
   break;
  case 'J':
   doType(VK_SHIFT, VK_J);
   break;
  case 'K':
   doType(VK_SHIFT, VK_K);
   break;
  case 'L':
   doType(VK_SHIFT, VK_L);
   break;
  case 'M':
   doType(VK_SHIFT, VK_M);
   break;
  case 'N':
   doType(VK_SHIFT, VK_N);
   break;
  case 'O':
   doType(VK_SHIFT, VK_O);
   break;
  case 'P':
   doType(VK_SHIFT, VK_P);
   break;
  case 'Q':
   doType(VK_SHIFT, VK_Q);
   break;
  case 'R':
   doType(VK_SHIFT, VK_R);
   break;
  case 'S':
   doType(VK_SHIFT, VK_S);
   break;
  case 'T':
   doType(VK_SHIFT, VK_T);
   break;
  case 'U':
   doType(VK_SHIFT, VK_U);
   break;
  case 'V':
   doType(VK_SHIFT, VK_V);
   break;
  case 'W':
   doType(VK_SHIFT, VK_W);
   break;
  case 'X':
   doType(VK_SHIFT, VK_X);
   break;
  case 'Y':
   doType(VK_SHIFT, VK_Y);
   break;
  case 'Z':
   doType(VK_SHIFT, VK_Z);
   break;
  case '`':
   doType(VK_BACK_QUOTE);
   break;
  case '0':
   doType(VK_0);
   break;
  case '1':
   doType(VK_1);
   break;
  case '2':
   doType(VK_2);
   break;
  case '3':
   doType(VK_3);
   break;
  case '4':
   doType(VK_4);
   break;
  case '5':
   doType(VK_5);
   break;
  case '6':
   doType(VK_6);
   break;
  case '7':
   doType(VK_7);
   break;
  case '8':
   doType(VK_8);
   break;
  case '9':
   doType(VK_9);
   break;
  case '-':
   doType(VK_MINUS);
   break;
  case '=':
   doType(VK_EQUALS);
   break;
  case '~':
   doType(VK_SHIFT, VK_BACK_QUOTE);
   break;
  case '!':
   doType(VK_EXCLAMATION_MARK);
   break;
  case '@':
   doType(VK_AT);
   break;
  case '#':
   doType(VK_NUMBER_SIGN);
   break;
  case '$':
   doType(VK_DOLLAR);
   break;
  case '%':
   doType(VK_SHIFT, VK_5);
   break;
  case '^':
   doType(VK_CIRCUMFLEX);
   break;
  case '&':
   doType(VK_AMPERSAND);
   break;
  case '*':
   doType(VK_ASTERISK);
   break;
  case '(':
   doType(VK_LEFT_PARENTHESIS);
   break;
  case ')':
   doType(VK_RIGHT_PARENTHESIS);
   break;
  case '_':
   doType(VK_UNDERSCORE);
   break;
  case '+':
   doType(VK_PLUS);
   break;
  case '\t':
   doType(VK_TAB);
   break;
  case '\n':
   doType(VK_ENTER);
   break;
  case '[':
   doType(VK_OPEN_BRACKET);
   break;
  case ']':
   doType(VK_CLOSE_BRACKET);
   break;
  case '\\':
   doType(VK_BACK_SLASH);
   break;
  case '{':
   doType(VK_SHIFT, VK_OPEN_BRACKET);
   break;
  case '}':
   doType(VK_SHIFT, VK_CLOSE_BRACKET);
   break;
  case '|':
   doType(VK_SHIFT, VK_BACK_SLASH);
   break;
  case ';':
   doType(VK_SEMICOLON);
   break;
  case ':':
   doType(VK_COLON);
   break;
  case '\'':
   doType(VK_QUOTE);
   break;
  case '"':
   doType(VK_QUOTEDBL);
   break;
  case ',':
   doType(VK_COMMA);
   break;
  case '<':
   doType(VK_SHIFT, VK_COMMA);
   break;
  case '.':
   doType(VK_PERIOD);
   break;
  case '>':
   doType(VK_SHIFT, VK_PERIOD);
   break;
  case '/':
   doType(VK_SLASH);
   break;
  case '?':
   doType(VK_SHIFT, VK_SLASH);
   break;
  case ' ':
   doType(VK_SPACE);
   break;
  default:
   throw new IllegalArgumentException("Cannot type character " + character);
  }
}
private void doType(int... keyCodes) {
  doType(keyCodes, 0, keyCodes.length);
}
private void doType(int[] keyCodes, int offset, int length) {
  if (length == 0) {
   return;
  }
  robot.keyPress(keyCodes[offset]);
  doType(keyCodes, offset + 1, length - 1);
  robot.keyRelease(keyCodes[offset]);
}

public void clickPos(int x,int y){
  robot.mouseMove(x, y);
  robot.mousePress(BUTTON1_MASK);
  robot.mouseRelease(BUTTON1_MASK);
  robot.delay(100);
}

//取得マウスの位置(x,y)
public void printMousePostion(){
  int x = 0;
  int y = 0;
  int loopCnt = 0;
  while (true) {
   robot.delay(100);
   if (x == MouseInfo.getPointerInfo().getLocation().x && y == MouseInfo.getPointerInfo().getLocation().y) {
    loopCnt++;
   } else {
    x = MouseInfo.getPointerInfo().getLocation().x;
    y = MouseInfo.getPointerInfo().getLocation().y;
   }
   if (loopCnt > 30) {
    System.out.println("(" + x + ", " + y + ")");
    loopCnt=0;
   }
  }
}
}

2016年7月20日星期三

VBA`ファイル操作

Sub doExcel(ByRef pathFile As String)

    Dim wb As Workbook
    Dim sheet_ZP, sheet_Work As Worksheet

    Dim lineNo, midLongth, i, No, maxLine, typeCol, lengthCol, resultCol, startLine, logLine As Integer
    Dim length, NX, retStr, startStr, endStr, midStr, itemName, MID_STR, NoStr As String

    typeCol = 4
    lengthCol = 16
    resultCol = 20
    startLine = 8
    itemNameCol = 2

    On Error Resume Next
    Set wb = Workbooks.Open(Filename:=pathFile)
    Set sheet_ZP = wb.Worksheets("挔昜崁栚")
    Set sheet_Work = wb.Worksheets("WORK")
    'sheet_Work.Columns("I:I").NumberFormatLocal = "@"
    'max line num of sheet
    'maxLine = sheet_ZP.Range("b65536").End(xlUp).Row
    maxLine = 400
    logLine = 1
    No = 0

     For lineNo = startLine To maxLine
        itemName = Trim(sheet_ZP.Cells(lineNo, itemNameCol).Value)
        If itemName = "" And sheet_ZP.Cells(lineNo, itemNameCol).MergeCells Then
            GoTo Continue
        ElseIf Not sheet_ZP.Cells(lineNo, itemNameCol).Borders(xlEdgeBottom).LineStyle = xlContinuous Then
            Exit For
        End If

        NX = getNX(sheet_ZP, sheet_Work, itemName)

        If NX = "NNNXXX" Then
            Worksheets("log").Cells(logLine, 1) = pathFile
            logLine = logLine + 1
        End If

        length = Trim(sheet_ZP.Cells(lineNo, lengthCol).Value)

        No = No + 1


        retStr = getRetStr(CStr(No), length, itemName, NX)

        sheet_ZP.Cells(lineNo, resultCol) = retStr
Continue:
     Next
     wb.Save
     wb.Close
End Sub
Sub test()
    Dim file As String
    file = "C:\1\挔昜愝寁彂_WAP0001_埗柤擖椡妋擣昜.xlsx"
    Call doExcel(file)
End Sub
Sub main()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim i As Integer
    Dim pf As String

    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Get the folder object
    'Set objFolder = objFSO.GetFolder("C:\1\zp_work")

    Set objFolder = objFSO.GetFolder("C:\1\t")
    'Set objFolder = objFSO.GetFolder("C:\1\wode")
    'loops through each file in the directory and prints their names and path
    For Each objFile In objFolder.Files
        If InStr(1, objFile.Path, "~", 1) = False Then
        pf = objFile.Path
             Call doExcel(pf)
        End If
    Next objFile
End Sub
Sub copyWork()
    Dim objFSO As Object
    Dim bkFolder, desFolder As Object
    Dim bkFile, desFile As Object
    Dim i As Integer
    Dim pf As String

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Set bkFolder = objFSO.GetFolder("C:\1\715\bk")
    Set desFolder = objFSO.GetFolder("C:\1\715\des")
    For Each desFile In desFolder.Files
        For Each bkFile In bkFolder.Files

            If Replace(bkFile.name, "bk_", "") = desFile.name Then
                 Workbooks(bkFile.Path).Worksheets(5).Copy After:=Workbooks(desFile.Path).Worksheets(6)
            End If
        Next bkFile
    Next desFile
End Sub
'C:\棲嶌嬈\pdf\0720
Sub copyWorkNG()
    Dim objFSO As Object
    Dim bkFolder, desFolder As Object
    Dim bkFile, desFile As Object
    Dim i As Integer
    Dim pf As String

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Set bkFolder = objFSO.GetFolder("C:\棲嶌嬈\pdf\0720")
    Set desFolder = objFSO.GetFolder("C:\1\720")
    For Each desFile In desFolder.Files
        If InStr(1, desFile.Path, "~", 1) = False Then
            'Workbooks("C:\1\720\2\WFP0310_幮夛暉巸朄恖尭柶妋擣徹_僠僃僢僋儕僗僩.xlsx").Worksheets("強尒0719").Copy After:=Workbooks(desFile.Path).Worksheets("WORK")
            Workbooks(desFile.Path).Worksheets(2).Copy After:=Workbooks(desFile.Path).Worksheets(3)
        End If
    Next desFile
End Sub

Sub countWork()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim i As Integer
    Dim pf As String
    Dim workCnt, zpCnt, iWB As Integer
    Dim itemName As String

    Dim wb As Workbook
    Dim sheet_ZP, sheet_Work As Worksheet

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder("C:\1\BK_20160715")
    iWB = 1
    For Each objFile In objFolder.Files

        If InStr(objFile.Path, "~") > 0 Then
            GoTo conobj
        End If
        On Error Resume Next
        Set wb = Workbooks.Open(Filename:=objFile.Path)
        Application.AskToUpdateLinks = False
        'ThisWorkbook.UpdateLinks = xlUpdateLinksAlways
        Set sheet_ZP = wb.Worksheets("挔昜崁栚")
        Set sheet_Work = wb.Worksheets("WORK")


        workCnt = 0
        zpCnt = 0

        For lineNo = 8 To 400
             itemName = Trim(sheet_ZP.Cells(lineNo, 2).Value)
             If itemName = "" And sheet_ZP.Cells(lineNo, 2).MergeCells Then
                 GoTo ContinueZP
             ElseIf Not sheet_ZP.Cells(lineNo, 2).Borders(xlEdgeBottom).LineStyle = xlContinuous Then
                 Exit For
             End If
             zpCnt = zpCnt + 1
ContinueZP:
        Next
        lineNo = 0
        For lineNo = 4 To 400
             itemName = Trim(sheet_Work.Cells(lineNo, 1).Value)
             If itemName = "" Then
                 Exit For
             End If
             workCnt = workCnt + 1
        Next
        iWB = iWB + 1

        Workbooks("vba_0001.xlsm").Worksheets("log").Cells(iWB, 1) = objFile.name
        Workbooks("vba_0001.xlsm").Worksheets("log").Cells(iWB, 2) = zpCnt
        Workbooks("vba_0001.xlsm").Worksheets("log").Cells(iWB, 3) = workCnt
        wb.Close savechanges:=False
conobj:
    Next objFile
End Sub
Sub skeduleSheet()
    Dim wb As Workbook
    Dim sheet_ZP, sheet_Work As Worksheet

    Dim lineNo, midLongth, i, No, maxLine, typeCol, lengthCol, resultCol, startLine, logLine, lineD, lineS As Integer
    Dim length, NX, retStr, startStr, endStr, midStr, itemName, MID_STR, NoStr, pathFile, sName, dName As String

    typeCol = 4
    lengthCol = 16
    resultCol = 20
    startLine = 8
    itemNameCol = 2
    pathFile = ""

    On Error Resume Next
    Set wb = Workbooks.Open(Filename:="C:\1\挔昜崁栚悢.xlsx")
    Set sheet_d = wb.Worksheets("d")
    Set sheet_s = wb.Worksheets("s")
    'sheet_Work.Columns("I:I").NumberFormatLocal = "@"
    'max line num of sheet
    'maxLine = sheet_ZP.Range("b65536").End(xlUp).Row
    maxLine = 400
    logLine = 1
    No = 0

    For lineD = 1 To maxLine
        dName = Trim(sheet_d.Cells(lineD, 2).Value)


        For lineS = 2 To maxLine
            sName = Trim(sheet_s.Cells(lineS, 1).Value)

             If InStr(sName, dName) > 0 Then
                  sheet_d.Cells(lineD, 7) = sheet_s.Cells(lineS, 2).Value
                  sheet_d.Cells(lineD, 8) = sheet_s.Cells(lineS, 3).Value
              End If

        Next

     Next




End Sub
'
Function getNX(sheet_ZP, sheet_Work As Worksheet, ByVal val As String) As String

    If val = "埗柤廧強丂奜帤" Then
        MsgBox "埗柤廧強丂奜帤侾"
    End If


    Dim maxLine_sheet_ZP, maxLine_sheet_Work, startZp, startWork, iZP, iWork, iIsX As Integer
    Dim itemName_Work, ret, zhaiYaoNX As String
    maxLine_sheet_Work = sheet_Work.Range("b65536").End(xlUp).Row
    startZp = 8
    startWork = 4

    val = removeDig(val)

    getNX = "N"

    If InStr(1, val, "揈梫", 1) Then

        For iWork = startZp To maxLine_sheet_Work
            itemName_Work = Trim(sheet_Work.Cells(iWork, 1).Value)
            itemName_Work = removeDig(itemName_Work)
            If InStr(1, itemName_Work, "揈梫", 1) Then
               zhaiYaoNX = zhaiYaoNX + Trim(sheet_Work.Cells(iWork, 4).Value)
            End If
        Next

        If InStr(1, zhaiYaoNX, "N", 1) And InStr(1, zhaiYaoNX, "X", 1) Then
            getNX = "NNNXXX"
        ElseIf InStr(1, zhaiYaoNX, "X", 1) Then
            getNX = "X"
        ElseIf InStr(1, zhaiYaoNX, "N", 1) Then
            getNX = "N"
        End If

    Else

        For iWork = startZp To maxLine_sheet_Work
            itemName_Work = Trim(sheet_Work.Cells(iWork, 1).Value)
            itemName_Work = removeDig(itemName_Work)
            If InStr(1, val, itemName_Work, 1) Or InStr(1, itemName_Work, val, 1) Then
                NX = Trim(sheet_Work.Cells(iWork, 4).Value)
                If NX = "X" Then
                    getNX = "X"
                    Exit For
                End If
            End If
        Next
    End If

End Function
'暥帤楍偵悢帤傪嶍彍偡傞
Function removeDig(ByVal val As String) As String
    Dim re
    Set re = CreateObject("VBScript.RegExp")

    ' 惓婯昞尰僷僞乕儞傪僙僢僩
    re.pattern = "[侽-俋|0-9]+"
    ' 戞堦堷悢偑抲姺懳徾偺暥帤楍丄戞擇堷悢偑抲姺暥帤楍
    removeDig = re.Replace(val, "")
End Function
Function getRetStr(ByVal No As String, ByVal length As String, ByVal itemName As String, ByVal NX As String) As String

    Dim NoStr, retStr, midStr, endStr, MID_STR As String

    NoStr = Trim(str(No))
    retStr = ""
    endStr = ">"
    midStr = ""
    MID_STR = "?

    midLongth = length - 1 - Len(No)

    For i = 1 To midLongth
        midStr = midStr & MID_STR
    Next

    If midLongth < 0 Then
        endStr = ""
    End If

    retStr = NoStr & midStr & endStr

    If itemName = "埗柤廧強丂奜帤" Then
        MsgBox "埗柤廧強丂奜帤"
    End If

    If (midLongth < 0 And CInt(length) <= Len(NoStr)) Then
        If NX = "N" Then
            retStr = Left(itemName, length)
        Else
            retStr = Left("构构构", length)
        End If
    End If
    If NX = "N" Then
        retStr = StrConv(retStr, vbWide)
    End If

    getRetStr = retStr
End Function

订阅：博文 (Atom)