2013年5月10日金曜日

エクセルからmysqlに接続

excel の使い所は種々あるだろうが、その多くは各種DBやログを利用した解析やレポート作成ではないだろうか。それには最初に何らかの方法でデータベースからデータをエクセルまで運んでこなければならない。テキストやCSVをデータベースに見立ててODBC接続する方法なども考えられるが、出来れば直接データベースにアクセスして各種条件で抽出したデータをレポートとして利用したい。そこで、非常に普及している mysql に ado 接続してみることにした。

ubuntu 上の mysql を他のwindows から参照可能にする。
  1. my.cnf のローカルのみ接続を解除する。
    #bind-address = 127.0.0.1
    
      
  2. 接続許可ユーザとマシンのIPを登録、確認。
    mysql> grant all privileges on *.* to ユーザ名@外部ホストIP identified by 'パスワード' with grant option;
    mysql> flush privileges;
    mysql> select user from mysql.user
    
      
  3.  mysql のリスタート
    $ sudo /etc/init.d/mysql restart
    
      
windows 上で mysql 接続の準備をする。
  1.  ドライバを以下よりダウンロードする。
    Download Connector/ODBC

  2.  ユーザDSNに接続定義する。
mysql と excel の型を比較する。
  1.  以下のコードで接続する。(エラーを起こす型を取り除いた)
    Sub test01()
        Dim con As ADODB.Connection
        Dim rs  As ADODB.Recordset
        
        Dim cntString As String
        Dim sqlStr As String
        
        Dim rowNo As Integer
        Dim colno As Integer
        Dim item As Variant
        
        cntString = "Driver={MySQL ODBC 5.1 DRIVER};" _
                & "SERVER=192.168.11.100;" _
                & "DATABASE=naoki;" _
                & "USER=naoki;" _
                & "PASSWORD=naoki;"
        
        Set con = New ADODB.Connection
        
        On Error GoTo Err
        
        con.Open cntString
        
        sqlStr = "select col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col15,col16,col17,col21,col23,col25,col27,col28,col29 from test01"
        
        Set rs = con.Execute(sqlStr)
        
        Worksheets("Sheet2").Cells.Clear
        
        rowNo = 1
        colno = 1
        
        Do While rs.EOF = False
            For Each item In rs.Fields
                Worksheets("Sheet2").Cells(rowNo, colno).Value = item.Value
                colno = colno + 1
            Next
            colno = 1
            rowNo = rowNo + 1
            
            rs.MoveNext
        Loop
        
        con.Close
        Set rs = Nothing
        Set con = Nothing
        
    Exit Sub
    
    Err:
        Set rs = Nothing
        Set con = Nothing
        MsgBox (Err.Description)
    End Sub
      
  2.  データベース設定値と取込み結果の比較

    タイプ DB設定値 エクセル取込値
    BIT(8) 1 ×
    TINYINT 127 127
    BOOL 1 1
    SMALLINT 32767 32767
    MEDIUMINT 8388607 8388607
    INT 2147483647 2147483647
    BIGINT 9223372036854775807 9.22337E+18
    FLOAT 3.402823466E+38 3.40282E+38
    DOUBLE 1.7976931348623157E+308 1.7977E+308
    DECIMAL 1 1
    DATE '9999-12-31' 9999/12/31
    DATETIME '9999-12-3123:59:59' 9999/12/3123:59
    TIMESTAMP '2037-12-3123:59:59' 2037/12/3123:59
    TIME '838:59:59' ×
    YEAR 2155 2155
    CHAR 'a' a
    VARCHAR(255) 'ab1' ab1
    BINARY(255),@ 'ab2' ×
    VARBINARY(255) 'ab3' ×
    TINYBLOB 'ab4' ×
    TINYTEXT 'ab5' ab5
    BLOB(255) 'ab6' ×
    TEXT(255) 'ab7' ab7
    MEDIUMBLOB 'ab8' ×
    MEDIUMTEXT 'ab9' ab9
    LONGBLOB 'abA' ×
    LONGTEXT 'abB' abB
    ENUM('a','b','c'), 'c' c
    SET('e','f','g') 'ef' e,f

2013年5月9日木曜日

エクセルでもクラス風味

エクセルVBAの本はどうして分厚くて、高くて、役に立たないのだろう。よんでも、例文を入力して実行してみても一向にわかった気がしないし、アプリが作れるような気にもならない。

まず、なぜオブジェクト指向っぽい体系なのにそれがあまり語られないのであろう。また、例文やクラスの利用方法を示していても、全てを網羅しているわけでもないのにオブジェクトブラウザとヘルプなどでの当たりの付け方などに言及されないのはなぜなのだろう。エクセルはパソコンを職業とする人以外の人たちも多数利用し、プログラマでもないのに結構使いこなしている方を見かけるのだが、よほど先入観がないか、優れた人達なのではないだろうか。

そんな一般な人たちに脅威を抱きつつ、テキスト貼り付けマクロのコードの見直しとクラス風味への変更を行なってみた。本と首っ引きで作成したのが前回のもので、今回はオブジェクトブラウザとヘルプ、及び、マクロの記録を使ってコードを書きなおしてみた。かなり短くなってしまったが、やりたかったイメージはこちらの方が近いと思う。クラス風味にすることで、他のエクセルのライブラリへの理解も深まろうというものです。しかし、作るのに時間がかかるなぁ。頭悪すぎを実感します。

呼び出しマクロ
Sub test()
    Dim List As New TextListing
    
    With List
        .PathName = ThisWorkbook.Path
        .TextListing
    End With
End Sub
クラス風味のコード
Public PathName As String

Sub TextListing()
    Dim temp As Workbook
    Dim fname As String

    'テキストファイルにマッチングするよう指定
    fname = Dir(PathName & "\*.txt")
    Do While fname <> ""
        '指定のファイルを新しいブックに読み込む
        Set temp = Workbooks.Open(Filename:=PathName & "\" & fname,Format:=5)
        
        'フォントサイズを9ポイント
        temp.Worksheets(1).Cells.Font.Size = 9
        
        'フォントを HGゴシックE に変更
        temp.Worksheets(1).Cells.Font.Name = "HGゴシックE"
        
        '枠線の消去
        ActiveWindow.DisplayGridlines = False
        
        '読み込んだシートをマクロを起動したブックに移動
        temp.Worksheets(1).Move Before:=ThisWorkbook.Sheets(1)
        
        '次のファイル名を取得
        fname = Dir
    Loop
End Sub

2013年5月6日月曜日

エクセルのテキスト貼り付けマクロ

テキストのテストエビデンスなどをパソコンではオフィスなどしか触ったことのない人に見せる場合、エクセルに貼り付けて送付したりするという事がよくあった。大量にある場合はエクセルの得意な方に、テキストをシートに貼り付けるマクロを書いてもらって対処したりしていたが、環境が変わるたびに作ってもらえる人を探すのも不毛なので自分で作ってみることにした。

unix 側で取得したテキストエビデンスをPCに移動し、そのエビデンスのフォルダにマクロを仕掛けたエクセルファイルを放り込んでマクロを起動すると、全テキストファイルをファイル毎に1シートにして一つのブックにするものである。

今回は、java のソースのsdiff の結果を sjis にコード変換したのちタブをスペースに置き換えて利用した。
$ sdiff test01.java test02.java | nkf -s | expand -8 > ../excel/test01-02.txt
$ sdiff test02.java test03.java | nkf -s | expand -8 > ../excel/test02-03.txt
$ sdiff test03.java test04.java | nkf -s | expand -8 > ../excel/test03-04.txt

マクロを起動結果。


ソースコード
Sub Macro1()
    Dim myPath As String
    Dim myFileName As String
   
    '新規ブックの作成
    Dim myWb As Workbook
    Set myWb = Workbooks.Add
  
    '指定ディレクトリのファイルを順番にシートに貼り付け
    myPath = ThisWorkbook.Path & "\"
    myFileName = Dir(myPath, 0)
    Do While Len(myFileName) > 0
        ' テキストファイルの場合シート作成
        If Right(myFileName, 3) = "txt" Then
            MakeSheet file:=myFileName, sheet:=myWb
        End If
        Debug.Print myPath & myFileName
        myFileName = Dir()
    Loop
End Sub

Sub MakeSheet(file As String, sheet As Workbook)
    Dim myFso As Object
    Dim myFileName As String
    Dim myStr As String
    Dim cnt As Long
    Dim mySht As Worksheet
    Set myFso = CreateObject("Scripting.FileSystemObject")
    '
    ' 新しいシートを追加
    Set mySht = Worksheets.Add(after:=Sheets(Sheets.Count))
    mySht.Name = file
    
    'シート全体を選択してフォントを設定
    Cells.Select
    mySht.Cells.Font.Size = 10
    mySht.Cells.Font.Name = "HGゴシックM"
    
    '罫線を削除
    Cells.Select
    ActiveWindow.DisplayGridlines = False
    
    'ファイル名を設定
    myFileName = ThisWorkbook.Path & "\" & file
    
    '一行ずつ読み込み、シートに文字列として貼り付け
    cnt = 1
    With myFso.OpenTextFile(myFileName, 1)
        Do Until .AtEndOfStream
            myStr = .ReadLine
            Debug.Print myStr
            With Cells(cnt, 1)
                .NumberFormatLocal = "@"
                .Value = myStr
            End With
            cnt = cnt + 1
        Loop
        .Close
    End With
    
    ' ワークシートを指定ブックに移動
    mySht.Move before:=sheet.Sheets(1)
    
    Set mytxt = Nothing
    Set myFso = Nothing
    Set mySht = Nothing
End Sub

2013年4月29日月曜日

java のエラーと例外

ざっと java の概要を拾い読みして、特に java のエラーと例外について以下のような疑問をもった。
  • 例外が想定外のエラーなの?
  • エラーと例外を全部catchしないといけないの?
  • catchした例外情報を最上位まで返せないの?
  • catchするけど最上位までスタックトレース情報を返せないの?
  • エラーが起きたときって次行にすすまないの?
  1. 例外が想定外のエラーなのか?
  2. 例外っていうと想定外の事態で、エラーっていいうと一応想定内な事態な印象を持つけれどOOPSでは逆なんですね。例外は通常起こりえるエラーを指し、エラーは致命的なエラーを指しているのですね。で、例外処理で相手にしているのは通常起こりえるエラーの方で、致命的なエラーは java 本体のエラー補足にまかせて異常終了というのが大筋なんですね。

  3. エラーと例外を全部catchしないといけないの
  4. 例外の説明では例外の補足方法について説明されるけれど、返す可能性のある例外は全部 catch しないといけないのか、それとも、必要な例外のみを補足すればよいのかよくわからない場合が多い。catch、throw、throws は密接に関係しているので最後に全部セットにしたエラー処理のサンプルのようなものが欲しいと思う。結論からいえば、catch で全部の例外を補足する必要はなく、返す可能性のあるエラーは全て throws に列挙すれば上位の関数に例外がそのまま伝わるらしい。

  5. catchした例外情報を最上位まで返せないの
  6. 一旦キャッチしたエラーは、throw で自分で例外を上位に投げなければそこでそのエラーは無くなってしまう。一旦 catch して何か処理施した後、上位にエラーを解したい場合は「throw エラー」として上位関数に自らエラーは投げなければならない。もちろん、キャッチしなかったエラーは、throws に定義した通り自動的に上位関数に引き継がれる。

  7. catchするけど最上位までスタックトレース情報を返せないの
  8. catch したエラー情報に含まれれ、e.printStackTrace()で出力できるスタックトレース情報は、catch してただ throw するだけだとそれまでのスタックトレース情報が無くなってしまう。それまでのスタックトーレス情報を引き継ぎながらthrow するには、catch した例外情報 e を throw のパラメータとして上位に投げると最上位までスタックトレース情報が引き継がれる。

  9. 例外時のリソースの解放って終了すれば解放されるの
  10. 例外時にリソースの解放処理を上手に仕組ないとリソースの使い切りに繋がりますという説明をよく見かけるけど、常駐プロセスとして動く場合の注意点だという注意書きが欲しい気がする。オブジェクト指向のクラスとは、再利用性をよく考慮された関数群のようなものだから、再利用を考えればリソースの使いきりの可能性があるコードは確かによくないけれど、利用機会の限られたツールなどを作る場合には絶対に守らなければならないルールではないと思う。

  11. エラーが起きたときって次行にすすまないの
  12. 致命的なエラーが起きたときの処理内容を確かめるのに、致命的なエラーの発生方法がわからないと実験のしようがないので、正しい処理コードの記述方法もわかりません。たとえば、リソースの解放処理とか、次行に進まないための考慮とか必要ないのかなど思い悩みます。そんな時に便利な「int[] arr = new int[0x7fffffff]」である。これでエラー「java.lang.OutOfMemoryError:」が発生させられる。通常はエラーの場合は、try などせず致命的エラーとして java のお世話になれば良いのだと思う。
    import java.io.*;
    public class test03 {
      public static void main(String[] args)
      {
        try {
           int[] arr = new int[0x7fffffff];
           System.out.println("次の処理だ");
        }
        catch(OutOfMemoryError e) {
           System.out.println("メモリエラーだ");
        }
        finally {
           System.out.println("うーーむ");
        }
      }
    }
    
    $ java test03
    メモリエラーだ
    うーーむ
    

強制断捨離(ubuntu13.04)の季節

うかうかとうららかな日々を送っていたら、もう ubuntu13.04 の季節である。 ubuntu マガジンの 12.10 の設定特集の内容にもロクに目を通していないのに困ったものである。暫く Mint14 shinamon に浮気していたが、あえてここは ubuntu への強制送還とあいなった。

使ってみての第一印象は、謳い文句どおり画面の描画が若干はやくなっているように思われた。ただ、いつもの強制断捨離で行う設定の際にいくつかの不具合にであった。(脳バグがほとんどだが)

  • samba の設定画面の立ち上げ方法を忘れた(脳バグ)
  • chrome がインストールできない。(chromiumはOK)
  • chrom のリモートデスクトップのマスス操作が聞かない(chrome待ち?)
  • wine で通常の方法だと使えなくなってしまったアプリがでた
  • skype の設定も記録してなかったので変なものを入れてしまった(脳バグ)
  • wine 上のアプリのランチャー登録方法がわからい(脳バグかさえ不明)
  • lisp と slime のインストールも忘れてたので記載しておこう

  1.  固定IPアドレスの設定

  2.  DropBox と chrome のインストール

    普段の勉強ネタや設定ネタは $HOME/work -> $HOME/dropbox/work にリンクして、強制断捨離をしても失わないように配慮している。ブックマークも、hatena のブックマークを定期的に chrome のブックマークに設定している。これは、超絶必要なブックマークを携帯や他のパソコンでも共有したいためである。しかし、chrome を google のサイトよりダウンロードしても展開で失敗してしまった。仕方がないので chromium を導入してブックマーク共有などは概ね満足であるが、リモートデスクトップはキー入力とマウス入力が伝わらなかった。本家が対応されるのをじっと待とう。

  3. Mozc のインストール

    $ sudo apt-get install ibus-mozc mozc-server mozc-utils-gui
    

  4. Samba のインストール

    $ sudo system-config-samba 
    

  5. DDNS の定期通知の設定

    こちらを参照

  6. apache のインストール

    $ sudo apt-get install apache2
    

  7. mysql のインストール

    naoki@naoki-iMac:~$ mysql -u root -p
    Enter password: 
    mysql> CREATE DATABASE wordpress CHARACTER SET utf8;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> GRANT ALL PRIVILEGES ON wordpress.*  
        -> TO naoki@localhost IDENTIFIED BY 'naoki';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    

  8. php と wordpress のインストール

    $ sudo apt-get install php5 php5-mysql
    $ unziip wordpress-3.4.2-ja.zip
    $ chown -R www-data:www-data wordpress
    

  9. wine と各種アプリのインストール

    ubuntu12.10 と wine1.4 の組み合わせで動いていたアプリの幾つかが動かなくなったため、試しに wine 1.5と ps を導入してみたがこれも正常にインストールできるものの2回目以降起動が出来なくなった。試しに indesign を導入したあと ps を導入してみると問題が解消した。なんか、wine 上のアプリは、wine のバージョンなのか、os バージョンなのか、アプリ間の相性なのかまったくわからない。動かしたいソフトが動いたらラッキーという姿勢が大事かもしれない。

  10. virtualbox のインストール

    インストール中に usb にアクセスできないので、vboxuser グループに利用ユーザを追加しろというメッセージがでるが、groupadd なんかしたあほな記憶を思い出した。(こちらを参照)

  11. skype のインストール

    $ sudo apt-get update
    $ sudo apt-get install skype
    

  12. emacs のインストール

    $ sudo apt-get install emacs24
    

  13. eclipse

    emacs と eclipse はドーモ世界が違いすぎるのだけれど、eclipse も強要される場合も多いので導入して置かないと。 まずは、java のインストール
    $ sudo apt-get install openjdk-7-jre
    
    ここよりeclipseをダウンロードして$HOME/eclipse に展開(Eclipse IDE for Java Developers)
    $ cd $HOME
    $ tar xvfz ダウンロード/eclipse-java-juno-SR2-linux-gtk-x86_64.tar.gz
    
    ここよりpleiadesの日本語パッケージをダウンロードして展開後インストール
    $ cd $HOME/eclipse
    $ unzip ../ダウンロード/pleiades_1.4.0.zip
    
    eclipse.ini に以下の定義を追加(フルパス)
    -javaagent:/home/naoki/eclipse/plugins/jp.sourceforge.mergedoc.pleiades/pleiades.jar
    
    ランチャー登録用の定義ファイル作成
    $ cd $HOME/.local/share/applications
    $ cat < eclipse.desktop
    > [Desktop Entry]
    > Version=1.0
    > Type=Application
    > Terminal=false
    > StartupNotify=true
    > Icon=/home/naoki/eclipse/icon.xpm
    > Name=Eclipse Juno
    > Comment=Eclipse Juno
    > Exec=/home/naoki/eclipse/eclipse
    > Categories=Development;
    > EOF
    
    作成した定義ファイルのプロパティファイルの実行権限を付与
    あとは実行中にランチャー登録して登録完了

  14. 言語系(common lisp と slime)

    $ sudo apt-get install clisp
    $ sudo apt-get install slime
    

  15. 言語系(ruby )

    $ sudo apt-get install ruby
    

2013年4月23日火曜日

clisp の cadadr ってなんだ?

「Land of Lisp」という本を買った。3章の最後にあっさり出てくる cadadr は car と cdr の組み合わせだというのは解るのだが、どのように組み合わされたものなのか解読に苦労してしまった。c*r の * の a は car の略称、d は cdr の略称なのは想像がついたのだが・・・ そこで少しググってみて納得がいきました。頭悪いなと実感!

 (defun caar (x) (car (car x)))
 (defun cadr (x) (car (cdr x)))
 (defun cdar (x) (cdr (car x)))
 (defun cddr (x) (cdr (cdr x)))
 (defun caaar (x) (car (car (car x))))
 (defun caadr (x) (car (car (cdr x))))
 (defun cadar (x) (car (cdr (car x))))
 (defun caddr (x) (car (cdr (cdr x))))
 (defun cdaar (x) (cdr (car (car x))))
 (defun cdadr (x) (cdr (car (cdr x))))
 (defun cddar (x) (cdr (cdr (car x))))
 (defun cdddr (x) (cdr (cdr (cdr x))))
 (defun caaaar (x) (car (car (car (car x)))))
 (defun caaadr (x) (car (car (car (cdr x)))))
 (defun caadar (x) (car (car (cdr (car x)))))
 (defun caaddr (x) (car (car (cdr (cdr x)))))
 (defun cadaar (x) (car (cdr (car (car x)))))
 (defun cadadr (x) (car (cdr (car (cdr x)))))
 (defun caddar (x) (car (cdr (cdr (car x)))))
 (defun cadddr (x) (car (cdr (cdr (cdr x)))))
 (defun cdaaar (x) (cdr (car (car (car x)))))
 (defun cdaadr (x) (cdr (car (car (cdr x)))))
 (defun cdadar (x) (cdr (car (cdr (car x)))))
 (defun cdaddr (x) (cdr (car (cdr (cdr x)))))
 (defun cddaar (x) (cdr (cdr (car (car x)))))
 (defun cddadr (x) (cdr (cdr (car (cdr x)))))
 (defun cdddar (x) (cdr (cdr (cdr (car x)))))
 (defun cddddr (x) (cdr (cdr (cdr (cdr x)))))

2013年4月22日月曜日

java でファイルダンプ(クラス風味+)

今回は、16進数の出力の前後に文字列を指定できるクラス(dhex)とキャラクタの前後に文字列を指定できるクラス(dchr)に分けて、ダンプのフォーマットを知っているクラス(dump)でdhexとdchrを使ってダンプするプログラムにしてみた。こうしたことで dump2 の新しいレイアウトダンプフォーマットを書く気力が沸いたので、そう言う意味の再利用性の向上があったと思える。クラスにするのであれば再利用性のあるクラスか、組み立て方をしっているクラスにわけるべきなのだろう。dhex、chex は、最近気になってる clisp の影響で再帰処理にしてみた。さらに、結城先生のデザインパターンのアダプタなどを模してみるとよいのかもしれないが、この内容のプログラムではその有りがたさがまだわからない。
import java.io.*;
import java.util.*;

class dhex {
    String fpad = "";
    String lpad = "";

    public dhex(String f, String l) {
        fpad = f;  
        lpad = l;  
    }

    public String hex(byte buf[],int no,int size) {
        no--;
        if (no>=0) {
            return(this.hex(buf,no,size) 
                 + (no > size-1 ? String.format("%s%2s%s",fpad,"",lpad) 
                       : String.format("%s%02x%s",fpad,buf[no],lpad)));
        }
        return("");
    }
}

class dchr {
    String fpad = "";
    String lpad = "";

    public dchr(String f, String l) {
        fpad = f;  
        lpad = l;  
    }

    public byte trpchr(byte c) {
        if ((0x00<=c && c<=0x1f) || 0x7f<=(c&0xff)) {
            return('.');
        }
        return(c);
    }

    public String chr(byte buf[],int no,int size) {
        no--;
        if (no>=0) {
            return(this.chr(buf,no,size) 
                 + (no > size-1 ? String.format("%s%s%s",fpad," ",lpad) 
                       : String.format("%s%c%s",fpad,trpchr(buf[no]),lpad)));
        }
        return("");
    }
}

class Dump {
    String fname;
    int    max;

    public Dump(String name) {
        fname = name;
        max   = 16;
    }

    public String fhalf(String line) {
        return(line.substring(0,3*(max/2)));
    }

    public String lhalf(String line) {
        return(line.substring(3*(max/2),3*max));
    }

    public void normalp() throws FileNotFoundException,IOException {
        byte   buf[] = new byte[16];
        int    size,cnt = 0;
        FileInputStream ifs = null;

        try {
            ifs = new FileInputStream(fname);
            dhex h = new dhex(""," ");
            dchr c = new dchr("","");

            while(ifs.available()>0) {
                size = ifs.read(buf);

                System.out.print(String.format("%08x : ",cnt*16));
                String line = h.hex(buf,16,size);

                System.out.print(fhalf(line));
                System.out.print("- ");
                System.out.print(lhalf(line));
                System.out.print("|");
                System.out.print(c.chr(buf,16,size));
                System.out.printf("|\n");
                cnt++;
            }
        }
        finally {
            if (ifs != null) {
                ifs.close(); 
            }
        }
    }
}

class Dump2 extends Dump {
    public Dump2(String name) {
        super(name);
    }

    public void normalp() throws FileNotFoundException,IOException {
        byte   buf[] = new byte[16];
        int    size,cnt = 0;
        FileInputStream ifs = null;

        try {
            ifs = new FileInputStream(fname);
            dhex h = new dhex(""," ");
            dchr c = new dchr(" "," ");

            while(ifs.available()>0) {
                size = ifs.read(buf);

                System.out.print(String.format("%08x : ",cnt*16));
                System.out.print(h.hex(buf,16,size));
                System.out.printf("\n");
                System.out.print(String.format("         : "));
                System.out.print(c.chr(buf,16,size));
                System.out.printf("\n");
                cnt++;
            }
        }
        finally {
            if (ifs != null) {
                ifs.close(); 
            }
        }
    }
}

class test05 {
    public static void main(String args[]) {
        Dump d = new Dump(args[0]);
        try {
            d.normalp();
        }
        catch(FileNotFoundException e) {
            System.out.println("ファイルオープンエラー");
        }
        catch(Exception e) {
            System.out.println("異常終了");
            e.printStackTrace();
        }
    }
}