Excel ADOを使ったデータベースへの接続とデータ取得

ExcelでADOを使った接続とデータ取得の方法です。

メモ:xlsファイルをODBCへ登録する場合は、データの範囲を「名前の定義」で登録しておく。

'変数宣言の強制
Option Explicit

'ODBC接続文字列（※ODBCは事前に登録しておくこと）
Const strConnectDB As String = "DSN=MYDB;UID=***;PWD=***;"

'データ貼り付け先頭セル
Const topCell As String = "A4"

'==================================================
'ADOを使ったデータベースへの接続とデータ取得
'［参照設定］ダイアログで「Microsoft ActiveX Data Objects x.x Library」をOnにする。
'
'引数
'    (1)sourceTable 取得元テーブル名
'    (2)targetSheet （参照渡し）データ挿入先シート
'==================================================
'呼び出し元記述例
'Private Sub CommandButton1_Click()
'    Dim sheet As Worksheet
'    Set sheet = ThisWorkbook.Sheets("AAAA出力先")
'    sheet.Cells.NumberFormatLocal = "@"
'    'データの取得とシートへの貼り付け
'    Call getTable("AAAA_TABLE", sheet)
'End Sub
'==================================================
Public Sub getTable(ByVal sourceTable As String, ByRef targetSheet As Worksheet)

    On Error GoTo ErrorRoutine

    'ADOオブジェクトを作成(参照設定：Microsoft ActiveX Data Object2.x Library)
    Dim ADO As New ADODB.Connection
    Dim RS As New ADODB.Recordset

    'データベースへ接続
    Call ADO.Open(strConnectDB, , , adConnectUnspecified)
    'テーブルのレコードを取得
    Call RS.Open(sourceTable, ADO, adOpenForwardOnly, adLockReadOnly, adCmdTable)

    'レコード取得
    If Not RS.EOF Then

        '(1)一括で張り付ける方法
        'targetSheet.Range(topCell).CopyFromRecordset RS

        '(2) (1)でエラーとなる場合は一件ずつ挿入する方法を採用
        Do Until RS.EOF
            Dim row As Integer
            Dim clm As Integer
            For clm = 0 To RS.Fields.count - 1
                Dim curCell As Range
                Set curCell = targetSheet.Range(topCell).Offset(row, clm)
                '日付型は書式を指定
                If RS.Fields(clm).Type = adDBTimeStamp Then
                    curCell.Value = Format(RS.Fields(clm).Value, "yyyy/mm/dd hh:nn:ss")
                ElseIf RS.Fields(clm).Type = adDate Or RS.Fields(clm).Type = adDBDate Then
                    curCell.Value = Format(RS.Fields(clm).Value, "yyyy/mm/dd")
                ElseIf RS.Fields(clm).Type = adDBTime Then
                    curCell.Value = Format(RS.Fields(clm).Value, "hh:nn:ss")
                Else
                    curCell.Value = RS.Fields(clm).Value
                End If
            Next clm
            RS.MoveNext
            row = row + 1
        Loop
    End If

    'データベースから切断
    ADO.Close
    Set RS = Nothing
    Set ADO = Nothing

    '呼び出しも度へ戻る
    Exit Sub

    '例外処理
ErrorRoutine:
    Dim msg As String
    msg = "ADO接続でエラーが発生しました" & vbNewLine & vbNewLine
    msg = msg & "エラーコード : " & Err.Number & vbNewLine
    msg = msg & "エラーメッセージ : " & Err.Description

    Call MsgBox(msg, vbOKOnly + vbExclamation, "エラー")
    End
End Sub

続きを読む

Java SuperCSVの使い方

SuperCSVの使用例です。

Windowsで「�@」などののいわゆる機種依存文字を扱うにはMS932の指定が必須です。

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.supercsv.io.CsvListReader;
import org.supercsv.io.CsvListWriter;
import org.supercsv.io.ICsvListReader;
import org.supercsv.io.ICsvListWriter;
import org.supercsv.prefs.CsvPreference;

public class MyClass {

    // 文字コード
    private static final String CHARCODE = "MS932";

    public static void main(String... args) {

        // 入力元ファイル
        File inFile = new File("C:\\フォルダ\\input.txt");
        List<List<String>> res;
        // 読み込み処理
        res = readCSV(inFile);
        // データ表示
        System.out.println(res);

        // データ準備
        List<List<String>> csvDate = new ArrayList<List<String>>();
        csvDate.add(Arrays.asList(new String[] { "aaa", "bbb", "ccc" }));
        csvDate.add(Arrays.asList(new String[] { "100", "200", "300" }));
        csvDate.add(Arrays.asList(new String[] { "xxx", "yyy", "zzz" }));

        // 書き出し先ファイル
        File outFile = new File("C:\\フォルダ\\output.txt");
        // 書き出し処理
        writeCSV(csvDate, outFile);
    }

    /**
     * CSV読み込み処理
     * @param file 読み込み元ファイル
     * @return 取得データ
     */
    private static List<List<String>> readCSV(File file) {

        // 読み込んだデータを保持するList
        List<List<String>> csvList = new ArrayList<List<String>>();

        // CSVファイルを開く
        try (FileInputStream fis = new FileInputStream(file);
                InputStreamReader isr = new InputStreamReader(fis, CHARCODE);
                ICsvListReader listReader = new CsvListReader(isr, CsvPreference.EXCEL_PREFERENCE)) {

            List<String> line;
            // 一行ずつ読み込み
            while ((line = listReader.read()) != null) {
                // nullを空文字に置き換える
                for (int i = 0; i < line.size(); i++)
                    if (line.get(i) == null)
                        line.set(i, "");

                csvList.add(new ArrayList<String>(line));
            }
            // CSVファイルを閉じる
            listReader.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

        // 呼び出し元へ返す
        return csvList;
    }

    /**
     * CSV書き出し処理
     * @param date 書き出しデータ
     * @param file 書き出し先ファイル
     */
    public static void writeCSV(List<List<String>> date, File file) {

        // CSVファイルを開く
        try (FileOutputStream fos = new FileOutputStream(file.getPath());
                OutputStreamWriter osw = new OutputStreamWriter(fos, CHARCODE);
                ICsvListWriter listWriter = new CsvListWriter(osw, CsvPreference.EXCEL_PREFERENCE)) {

            // 1行ずつ書き出す
            for (List<String> row : date)
                if (row.size() > 0)
                    listWriter.write(row);

            // CSVファイルを閉じる
            listWriter.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Excel フォーム上のすべてのチェックボックスのON/OFFを切り替える

サンプルのコードでは、フォーム"FormMain"の中の、フレーム"fraMain"の中にあるすべてのチェックボックスのON/OFFを切り替えています。

TypeOf c Is のとき、比較対象のクラスは MSForms.CheckBox とライブラリ名から記述しないと動きませんでした。

'フォーム上のすべてのチェックボックスのON/OFFを切り替える
Private Sub changeCB(val As Boolean)
    Dim c As MSForms.Control
    For Each c In FormMain.fraMain.Controls
        'これでもOK
        'If TypeName(c) = "CheckBox" Then
        '   c.Value = val
        'End If
        If TypeOf c Is MSForms.CheckBox Then ' MSFormsの記述が必須
            c.Value = val
        End If
    Next
End Sub