表計算シート上のURLリストを,ホワイトリストとブラックリストでフィルタするWSHバッチ


エクセルファイル上のURLのリストに,ブラックリストホワイトリストでフィルタリングをかけるバッチ。

ExcelOOo Calcへの処理を統合できるライブラリを,少し拡張した。

 

URLは一番目のシートのA列に並んでいるとする。

B列には,URLごとのスコア(アクセス解析の訪問者数など)が書かれており,フィルタリング後もこのスコアが保持される。

フィルタ内容はバッチ中で指定する。

フィルタ後の結果は二番目のシートに書き込まれる。

 

下記のバッチに,エクセルファイルをドロップすればよい。

@if(0)==(0) ECHO OFF
cscript.exe //nologo //E:JScript "%~f0" %* 
echo 終了しました。
@pause
GOTO :EOF
@end


// ---- 設定事項


// 自分のサイトのはてなID
var my_hatena_id = "〜〜";


// ホワイトリスト
var reg_patterns_white = [
	/^http/
];

// ブラックリスト(検索エンジンやマッシュアップ,URL加工系のWebサービスなど)
var reg_patterns_black = [
	// 自分のブログ内のリンク(サブブログも含める)
	new RegExp( "d\\.hatena\\.ne\\.jp\\/" + my_hatena_id + "\\+?[^/]*\\/" ),
	
	// 検索エンジンやサーチ系
	/www\.google\./,
	/(nl\.)?search\.yahoo\./,
	/www\.bing\.com\/search/,
	/jp\.ask\.com\//,
	/jp\.hao123\.com\//,
	/websearch\./,
	/cgi\.search\./,
	/nortonsafe\.search\.ask\.com\//,
	/notify\.bluecoat\.com\/notify\-/,
	/(s)?(ksearch)?\.luna\.tv/,
	/wsearch\.ocn\.ne\.jp\//,
	/www\.search\./,
	/.*\.starthome\.jp\//,
	/green\.search\./,
	/image\.search\./,
	/kids\.goo\.ne\.jp\/search/,
	/office\.microsoft\.com\/ja\-jp\/results/,
	/sp\-search\.auone\.jp/,
	/webcache\.googleusercontent\.com/,
	/www\.aolsearch\./,
	/www\.hatena\.ne\.jp\/o\/search/,
	/www\.metasearch\./,
	/www\.pointtown\.com\/ptu\/search/,
	/www\.so\-net\.ne\.jp\/search/,
	/ysearch\./,
	/isearch/,
	/hatenatunnel\.appspot\.com\/language_and_engineering\/searchdiary/,
	/www\.amazon\.(co\.jp)?(com)?\/gp\/bit\/apps\/web\/SERP\/search/,
	/encrypted\.google\.com/,
	/www(.+)\.delta\-search\.com/,
	/www\.searchgol\.com/,
	/www\.mysearchresults\.com/,
	/pex\.jp\/search/,
	/livedoor\-search/,
	/.*\.jword\.jp/,
	/eonet\.excite\.co\.jp\/search/,
	/zapanet\.info\/tundere/,
	/jp\.wow\.com\/search/,
	/life\.pmalls\.net\/search/,
	/poimon\.jp\/search/,
	/.*\.inbox\.com\/search/,

	// URL加工系
	/d\.hatena\.ne\.jp\/notify\-/,
	/t\.co\//,
	/search\./,
	/r\.duckduckgo\.com/,
	
	// Webクリップ系,その他サイト
	/b\.hatena\.ne\.jp\//,
	/hatebu\.net\/entry\/d\.hatena\.ne\.jp\/language_and_engineering/,
	/ceron\.jp\//,
	/getpocket\.com\//,
	/reader\.livedoor\.com/,
	/tophatenar\.com/,
	/hatenablog\.com\/k\/keywordblog/,
	/k\.hatena\.ne\.jp\/keywordblog/,
	/d\.hatena\.ne\.jp\/keyword/,
	/hatebu\-graph\.com/,
	/tweetbuzz\.jp/,
	/translate\.google\./,
	/m\.facebook/,
	/www\.facebook/,
	
	// ほか,はじきたいもの
	/^http(s)?:\/\/[^/]+\/$/, // ドメインのトップページからリンクされている
	/\/\/[\d\.]+\// // ドメイン名を使わずにIPアドレスがそのままになっている
];




function log(s){ WScript.Echo(s); }


// ---- 引数取得


// 引数があるか
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 + " は有効なファイルです。");
}




//今回のためにちょっと改良


// ---- MS ExcelとOOo Calcを共通して取り扱うためのライブラリ ----


// 要件:
// Excelで組んだロジックをOOcで使いまわし,その逆も可としたい。

// 設計方針:
// Decorator・factoryパターンあたりを参考に,
// オフィス製品の差異を内部にコンポジットで隠蔽して切り替え,外側のAPIは統一する。


// 表計算ソフトのラッパーオブジェクト
var IExcel = function(){
	// 初期化
	this.defineExcelType();
};
IExcel.prototype = {

	// 内部で使うオフィス製品のタイプ
	isMS : false,
	isKS : false,
	isOO : false,
	type_code : null,
	
	// どれを使うか調査して決める
	defineExcelType : function(){
		try{

			// MS製のオフィスがインストールされていれば最優先する
			this._excel = WScript.CreateObject("Excel.Application");
				// http://d.hatena.ne.jp/language_and_engineering/20140214/p1

			this.isMS = true;
			this.type_code = "ms";

		}catch(e){ // MSが無かったら

			try{

				// KingSoftがあれば,Excelと同一のAPIなのでこれを使う
				this._excel = WScript.CreateObject("ET.Application");
					// http://d.hatena.ne.jp/language_and_engineering/20121218/p1
				
				this.isKS = true;
				this.type_code = "ks";

			}catch(e2){ // Kingsoftも無かったら

				try{
					// 最後の手段として,OpenOffice.org Calcを使う
					var service_manager = WScript.CreateObject("com.sun.star.ServiceManager");
					this._ooo_desktop = service_manager.createInstance("com.sun.star.frame.Desktop")
						// http://d.hatena.ne.jp/language_and_engineering/20141227/OOoCalcByWSHJScript
					
					this.isOO = true;
					this.type_code = "oo";
					
				}catch(e3){
					WScript.Echo("オフィス製品を何か一つインストールしてください。");
				}

			}

		}
		
		this._books = [];
		
		// 初期化完了
		return;
	}
	,
	
	_excel : null,
	_ooo_desktop : null,
	
	
	// Visible
	setVisible : function( b ){
		if( this.isMS || this.isKS ){
			this._excel.Visible = b;
		}else{
			// TODO:
		}
	}
	,
	
	// 新規ブックを開く
	getNewBook : function(){
		var ibook;

		if( this.isMS || this.isKS ){
			this._excel.Workbooks.Add();
			var book = this._excel.Workbooks( this._excel.Workbooks.Count );
			
			// インタフェースに変換
			ibook = new IExcel.IBook( this, book );
			
			return ibook;
		}else{
			var doc = this._ooo_desktop.loadComponentFromURL(
				"private:factory/scalc", 
				"_blank", 
				0, 
				[]
			);
			
			// インタフェースに変換
			ibook = new IExcel.IBook( this, doc );
			
			return ibook;
		}
	}
	,
	
	// 既存のブックをファイルパスで開く
	openBookByFilePath : function( file_path ){
		var ibook;
		
		if( this.isMS || this.isKS ){
			this._excel.Workbooks.Open( file_path );
			var book = this._excel.Workbooks( this._excel.Workbooks.Count );

			ibook = new IExcel.IBook( this, book );
			return ibook;
		}else{
			var doc = this._ooo_desktop.loadComponentFromURL(
				"file:///" + file_path.replace(/\\/g, "/"),
				"_blank", 
				0, 
				[]
			);
			
			ibook = new IExcel.IBook( this, doc );
			return ibook;
		}
	}

};


// ブックを表すラッパオブジェクト

IExcel.IBook = function( parent, real_book ){
	this._parent = parent;
	this._book = real_book;
	this.type_code = parent.type_code;
	this._sheets = [];
};
IExcel.IBook.prototype = {
	_parent : null,
	_book : null,
	type_code : null,
	
	// ファイルパスを指定して保存
	saveAs : function( file_path ){
		if( this.type_code == "ms" || this.type_code == "ks" ){
			this._parent.DisplayAlerts = false;
			this._book.SaveAs( file_path );
		}else{
			var file_url = "file:///" 
				+ file_path.replace(/\\/g, "/")
			;
			this._book.storeAsURL( file_url, [] );
		}
	},
	
	// 番号でシートを取得(1始まり)
	getSheetByIndex : function( index ){
			log("現在のシートの個数:" + this.getSheetsCount() );
			log("index: " + index);
	
		var sheet, isheet;
		if( this.type_code == "ms" || this.type_code == "ks" ){
			sheet = this._book.Worksheets( index );
			isheet = new IExcel.ISheet( this._parent, sheet );
			return isheet;
		}else{
			sheet = this._book.Sheets.getByIndex( index - 1 ); // 0始まり
				// http://blog.livedoor.jp/addinbox/archives/51243622.html
				// http://itref.fc2web.com/openoffice/basic/calc.html

				//sheet = this._book.Sheets( index - 1 ); // 0始まり
				//REM: これだとシートがうまく取れなかった
				
				log( "取得したシートの名称は" + sheet.Name );
				
			isheet = new IExcel.ISheet( this._parent, sheet );
			return isheet;
		}
	}
	,
	
	// シートの個数
	getSheetsCount : function(){
		if( this.type_code == "ms" || this.type_code == "ks" ){
			return this._book.Sheets.Count;
		}else{
			return this._book.Sheets.getCount();
		}
	}
	
	// TODO:他のブック操作メソッド

};


// シートを表すラッパ

IExcel.ISheet = function( parent, real_sheet ){
	this._parent = parent;
	this._sheet = real_sheet;
	this.type_code = parent.type_code;
};
IExcel.ISheet.prototype = {
	_parent : null,
	_sheet : null,
	type_code : null,
	
	// セル参照(番号は一始まり)
	getCell : function( y, x ){
			log( y + "行" + x + "列目に書き込み"  );

		if( this.type_code == "ms" || this.type_code == "ks" ){
			var cell = this._sheet.Cells( y, x ); // 1始まり

			var icell = new IExcel.ICell( this._parent, cell );
			return icell;
		}else{
			var cell = this._sheet.getCellByPosition( x - 1, y - 1 ); // 0始まりでMSと逆

			var icell = new IExcel.ICell( this._parent, cell );
			return icell;
		}
	}
	
	// TODO:他のシート操作メソッド

};


// セルのラッパ

IExcel.ICell = function( parent, real_cell ){
	this._parent = parent;
	this._cell = real_cell;
	this.type_code = parent.type_code;
};
IExcel.ICell.prototype = {
	_parent : null,
	_cell : null,
	type_code : null,
	
	// 値を書き込み
	setValue : function( v ){
		if( this.type_code == "ms" || this.type_code == "ks" ){
			this._cell.Value = v;
		}else{
			this._cell.String = v;
		}
	}
	,
	
	// 値を取得
	getValue : function(){
		if( this.type_code == "ms" || this.type_code == "ks" ){
			return this._cell.Value;
		}else{
			return this._cell.String;
		}
	}
	
	
	// TODO:他のセル操作メソッド
};






// ---- Excel起動


var excel = new IExcel();
excel.setVisible( true );



// 対象ブックを開く
var book = excel.openBookByFilePath( filepath );


// 最初のシートにURLの生データが列挙されているとする
var sheet_raw = book.getSheetByIndex(1);

// 2番目のシートにURLを整理する
var sheet_urls = book.getSheetByIndex(2);



// ---- URLの生データをフィルタリング


var continue_flag = true;
var y_raw   = 1;
var y_write = 1;
while( continue_flag )
{
	// 該当セルが空でなければ読み取りを継続
	if( sheet_raw.getCell( y_raw, 1 ).getValue() )
	{
		log( y_raw + "行目のURLを検査" );
		var url = sheet_raw.getCell( y_raw, 1 ).getValue() + "";
		var cnt = sheet_raw.getCell( y_raw, 2 ).getValue() + 0;
		var ok_flag = true;
		
		// ホワイトリストに通る?
		for( var i = 0; i < reg_patterns_white.length; i ++ )
		{
			if( ! url.match( reg_patterns_white[i] ) )
			{
				ok_flag = false;
			}
		}
		
		// ブラックリストにひっかからない?
		for( var i = 0; i < reg_patterns_black.length; i ++ )
		{
			if( url.match( reg_patterns_black[i] ) )
			{
				ok_flag = false;
			}
		}
		
		// フィルタを通過した?
		if( ok_flag )
		{
			log("OK");
			sheet_urls.getCell( y_write, 1 ).setValue( url );
			sheet_urls.getCell( y_write, 2 ).setValue( cnt );
			
			y_write ++;
		}
		else
		{
			log("NG");
		}
		
		// 次の行へ
		y_raw ++;
	}
	else
	{
		// 空セルに到達したので読み取りを中断
		continue_flag = false;
	}
}
log("全URLのフィルタリングが完了");



// ---- 終了


// ブックを保存
book.saveAs( filepath );


log("全処理が終了");


今後の課題:

  • 処理のライブラリを分割してwsfで実行。
  • はてなIDもコード外部で指定・認識できるように。
  • フィルタリングルールも,外部テキストに分割して管理できるように。
  • エクセル・OOoの統一ライブラリに,わかりやすい名前をつけたい。MS OfficeExcelと,Kingsoft Spreadsheetsと,OOo calcへのバッチ処理を統一できるAPIをもつライブラリなのだが。
  • IExcelとIBook以下のクラスで,オフィス製品の切り替え判定コードの内容が異なっているため,統一したい。同じ判定コードを持たせたい。