Macro/投稿/157 のバックアップ(No.7)

  0
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125

//excelPaste.js
//Excel等で表範囲をコピーした際のタブ区切りクリップボードデータをSQLPLU風に整形して挿入
//１行目をヘッダ行として、２行目との間にセパレート行を追加。
//セル結合は無視。セル内改行は半角ブランクに置換。
//http://mixi.jp/view_diary.pl?id=293214702&owner_id=6649650
 
var arrMaxWidth = new Array();
var arrHeadLine = new Array();
var arrDataLines = new Array();
 
//bytelength関数
var stdModule = 1;
var func = "Function LenMbcs(ByVal str as String)\r\n" +
            "LenMbcs = LenB(StrConv(str, vbFromUnicode))\r\n" +
           "End Function\r\n";
var objExl = new ActiveXObject('Excel.Application');
var objBok = objExl.Workbooks.Add();
var objMod = objBok.VBProject.VBComponents.Add(stdModule);
    objMod.codemodule.addfromstring(func);
 
var lineCount = 1;
 
main();//main
function main() {
    var arrSplitLine = getClipbrd();
    if (arrSplitLine) {
        seprateTab(arrSplitLine);
        var insText = setEachLines();
        insert_exit(insText);
    }
}
 
function getClipbrd() {
    var ie = new ActiveXObject("InternetExplorer.Application");
        ie.Navigate("about:blank");
    var clip = ie.Document.parentWindow.clipboardData.getData("Text");
        ie = null;
 
    if (clip) {
        return(clip.split("\r\n"));
    } else {
        return(false);
    }
}
 
function seprateTab(arr) {
    var re = /\n/g;
    for (i in arr) {
        var strLine = arr[i];
        if (strLine) {
            strLine = strLine.replace(re, " ");
            splitTab(strLine);
        }
        lineCount++;
    }
}
 
function splitTab(str) {
    if (lineCount == 1) {
        arrHeadLine = str.split("	");
        compareLength(arrHeadLine, 0);
    } else {
        var dataLine = str.split("	");
        compareLength(dataLine, 1);
        arrDataLines[lineCount] = dataLine;
    }
}
 
function compareLength(arr, f) {
    if (f == 0) {
        for (i in arr) {
            arrMaxWidth[i] = objExl.Run("LenMbcs", arr[i]);
        }
    } else {
        var ln = new Array();
        for (i in arr) {
            ln[i] = objExl.Run("LenMbcs", arr[i]);
            if (arrMaxWidth[i] < ln[i]) {
                arrMaxWidth[i] = ln[i];
            }
        }
    }
}
 
function setEachLines() {
    var hline = "", dlines = "", sline = "";
 
    hline = attachWidth(arrHeadLine).join("|") +"\r\n";
    for (i in arrDataLines) {
        dlines += attachWidth(arrDataLines[i]).join("|") +"\r\n";
    }
    sline += setSeprateLine(arrMaxWidth).join("|") +"\r\n";
 
    return(hline + sline + dlines);
}
 
function attachWidth(arrColumn) {
    for (i in arrMaxWidth) {
        if (objExl.Run("LenMbcs", arrColumn[i]) < arrMaxWidth[i]) {
            var strBlank = "";
            for (j=0; j < (arrMaxWidth[i] - objExl.Run("LenMbcs", arrColumn[i])); j++) {
                strBlank += " ";
            }
            arrColumn[i] += strBlank;
        }
    }
    return(arrColumn);
}
 
function setSeprateLine(arrColumn) {
    var arrDash = new Array();
    for (i in arrColumn) {
        var strDash = "";
        for (j=0; j < arrMaxWidth[i]; j++) {
            strDash += "-";
        }
        arrDash[i] = strDash;
    }
    return(arrDash);
}
 
function insert_exit(s) {
    objBok.close(false);
    objExl.quit();
    Editor.InsText(s);
}