Excelシート内で,URLを含むセルを,一括してハイパーリンクに変換するバッチ
あちこちのセルにURLが散りばめられているExcelシート上で,
URLを含むセルを,全部,ハイパーリンクに変換したい。
その作業を自動化するためのバッチ。
下記のバッチファイルに対して,Excelファイルをドラッグ・ドロップする。
すると,Excelファイル内の最初のシート上で,「URLを含むセル」に,
ハイパーリンク(HYPERLINK関数によるリンク)が自動的に設定される。
ExcelシートのURLを一括してハイパーリンク化.bat
@if(0)==(0) ECHO OFF cscript.exe //nologo //E:JScript "%~f0" %* rem cscript.exe //nologo //E:JScript "%~f0" %* > %0\..\log.txt echo 終了しました。 @pause GOTO :EOF @end /* Excelでは,1セル内に埋め込む事のできるHYPERLINKは1つだけという点に注意。 セル自体がリンクになる。 複数のリンクがある場合はセルを分割すること。 */ // ソースコードの参考: // http://language-and-engineering.hatenablog.jp/entry/20140112/GenerateAccessRankingTableHTMLFromHatenaCounter function log(s){ WScript.Echo(s); } // ---- 設定項目 // 範囲設定 var max_y = 100; var max_x = 30; // URLを表す正規表現 var regObjURL = /(http(s)?:\/\/[a-zA-Z0-9\/%\-=&\?\._\+#]+)/gi; // ---- 引数取得 // 引数があるか if( WScript.Arguments.length == 0 ) { log("同一フォルダ上のExcelファイルをドロップしてください。"); WScript.Quit(); } // ファイルパスを構築 var filename = WScript.Arguments.Unnamed(0); var filepath = filename; var fso = WScript.CreateObject("Scripting.FileSystemObject"); var filedir = fso.GetParentFolderName( filepath ); // ファイルが存在するか if( ! fso.FileExists( filepath ) ) { log( filepath + " は無効なファイルパスです。"); log("同一フォルダ上のExcelファイルをドロップしてください。"); WScript.Quit(); } else { log( filepath + " は有効なファイルです。"); } // ---- Excel起動 var excel = null; try { // Excel(Kingsoft Spreadsheet)を立ち上げる excel = WScript.CreateObject("ET.Application"); } catch(e) { // Excel(MS Office)を立ち上げる excel = WScript.CreateObject("Excel.Application"); } excel.Visible = true; // 対象ブックを開く excel.Workbooks.Open( filepath ); var book = excel.Workbooks( excel.Workbooks.Count ); // 最初のシートを処理の対象とする var target_sheet = book.Worksheets(1); // ---- 全セル内のURL文字列をリンクに変換 // セル内にURLを含んでいるか判定する関数 function includesURL( cell ) { var s = cell.Value; // 中身がない場合 if( ! s ) { return false; } // 中身の一部がURLにマッチする場合 if( (s + "").match( regObjURL ) ) { return true; } return false; } // セル内のURLをハイパーリンクに変換する関数 function modifyCellValue( cell ) { var str_orig = cell.Value + ""; // 単純な代入だけだと参照になってしまうので""を付与 // リンクのラベル文字列を生成 var link_label = str_orig .replace( new RegExp('"', "g"), '"""' ) .replace( /\n/g, "\"&char(10)&\"" ) ; // リンクのラベル内からURLを削除して置換 link_label = link_label .replace( regObjURL, '開く' ) ; // URLを抽出 str_orig.match( regObjURL ); var str_url = RegExp.$1; //var str_url = str_orig // .replace( regObjURL, "$1" ) //; // http://language-and-engineering.hatenablog.jp/entry/20080927/1222508705 // セルの値を生成 // ※1セルにつき1リンクである事に注意。 var str_cell = "=HYPERLINK(\"" + str_url + "\",\"" + link_label + "\")" // diary:この末尾に間違えて"\""を付与したら // 「実行時エラー:セミコロンがありません」になってはまった ; // http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/hyper.htm log( "URLは:" + str_url ); log( "ラベルは:" + link_label ); log( "セルに書き込む数式は:" + str_cell ); //cell.Formula = ""; cell.Formula = str_cell ; } // 行方向のスキャン for(var y = 1; y <= max_y; y ++) { // 列方向のスキャン for(var x = 1; x <= max_x; x ++) { var cell = target_sheet.Cells( y, x ); // 加工すべきセルか if( includesURL( cell ) ) { log( "[" + y + "," + x + "] URLを含むセルです。処理を開始" ) modifyCellValue( cell ); } else { log( "[" + y + "," + x + "] URLなし。スキップします。" ) } } } log("シート内の全セルの検査と加工が完了"); // ---- 終了 // ブックを保存 //excel.DisplayAlerts = false; //book.SaveAs( filepath ); // Excelを閉じて終了 //excel.Quit(); //excel = null; log("全処理が終了");
ソースコードを読むための参考情報:
ExcelのHYPERLINK関数は,セル内で1つだけ記述可能。複数個は無理
http://computer-technology.hateblo.jp/entry/20140115/p2
正規表現による文字列置換 String.replace(); - JavaScript スタイルシートサンプル集
http://javascript123.seesaa.net/artic...
JavaScriptの動かないコード (中級編) 正規表現で同じ文字の連続を検出したい - 置換前パターン中での後方参照
http://language-and-engineering.hatenablog.jp/entry/20080927/1222508705
エクセル「シート保護」をすると、「ハイパーリンク」が動きません | オフィス系ソフトのQ&A【OKWave】
http://okwave.jp/qa/q3426114.html
関連する記事:
Excelブックの「シート目次」を,自動的に作成するバッチ (WSH/JScriptで,各シートへのリンク付きの目次を自動生成)
http://language-and-engineering.hatenablog.jp/entry/20110921/p1
はてなダイアリーに執筆した記事一覧を,表形式に整理するブックマークレット (アーカイブページを,Excelに貼り付けやすく整形加工)
http://language-and-engineering.hatenablog.jp/entry/20140102/p1
Excelシートを読み取って,INSERT文SQLを生成するバッチ (Kingsoft SpreadsheetをWSH/JScriptで自動操作)
http://language-and-engineering.hatenablog.jp/entry/20121218/p1