タグ: Excel

ちーたんタッチボードを使ってExcelで現在日時を1キーで入力する

onodera_sf コメントをする

image ちーたんタッチボードでボードをカスタマイズされている方はもうご存知かもしれませんが、ちーたんタッチボードではキー１つに対して複数のキー押下を割り当てることができます。設定の仕方とか説明すると長くなってしまいますのでホームページのほうを見ていただければと思いますが、キーのパラメータの「Processes」に値を設定することによって、その動作を行うようになります。 image で、Excel のセルに現在日時を１発入力させるには以下のパラメータを設定することによって可能です。Processes はあくまでもキーの同時処理がメインなので、順番に処理させるのは得意ではないのですが、少し裏技的な設定で可能です。

[{"KeyType": "LeftCtrl"},{"KeyType": "OemSemicolon"},{"KeyType": "LeftCtrl", "ExecuteTiming": "Pressed"},{"KeyType": "Space"},{"KeyType": "LeftCtrl"},{"KeyType": "Colon"},{"KeyType": "LeftCtrl", "ExecuteTiming": "Pressed"},{"KeyType": "Enter"},{"KeyType": "Up"}]
ここでミソなのが、セミコロンを入力した後に再度 Ctrl キーを押すようにしています。パラメータ「ExecuteTiming」に「Pressed」を指定することによって押下した直後にリリースさせることができます。一番最初の「Ctrl」は実際にキーを指から離すまでずっと押下が有効になってしまうため、後ろのスペースキーにも影響してしまいますが、その前に Pressed で離す動作を入れることによって Ctrl キーを強制的に無効にしています。 後はショートカットキーの流れで実行しているので現在日時が入力されるというわけです。後ろ２つで Enter と Up を指定していますが、現在日時を入力しただけではセルの入力が確定していないので、Enter キーで確定させ、選択が１セル分下に移動するので↑キーで戻すようにしています。この辺りは必要であれば入れたり外したりしてください。 ちなみにこのパラメータを指定すると、キーボード的には下のような流れの動作になります。
  1. Ctrlを押す
  2. セミコロンを押す
  3. Ctrlを押す
  4. Ctrlを離す
  5. スペースを押す
  6. Ctrlを押す
  7. コロンを押す
  8. Ctrlを押す
  9. Ctrlを離す
  10. Enterを押す
  11. ↑を押す
Ctrlキーを２回押すという物理的にはあり得ない動作になっていますが、そのあとのCtrlを離すできちんと離したことになるみたいです。 あと、このキーを押しっぱなしにするとセミコロンとコロンが入力されてしまうという動作になってしまいますので、押すのは１回だけにしてください。 image ※問い合わせていただいたサンプルキーを使っています。]]>

テンキー Ver 0.1 を考えてみた [ちーたんタッチボード]

onodera_sf コメントをする

個人的にはテンキーはめったに使わないのでどういったレイアウトが使いやすいかがあまりわからないのですが、Ver 0.1 を考えてみました。(現在配布されているのは Ver 0.01)

まず１つめ。

2014-04-12 16_07_34-AllProject_2013 - Microsoft Visual Studio

Ver 0.01 とほとんど変わっていないのですが、カンマだった部分を 00 にしてみました。テンキーについていろいろ調べてみると、00 の部分が大きく２パターンになっていて、00 のキーか 0 のキーが２マス分となっているようでした。カンマについては電卓では自動的につくから使わないし、Excel でも書式設定すればカンマは自動でつくのでわざわざ入力することはないってことなんでしょうね。

さて、テンキーを見ると必ずといっていいほど存在するキーとして「NumLock」があります。一応 NumLock キーは定義してあるのですが、おそらくほとんどの環境では押してもなんの効果もないと思われます。たぶんですが、NumLock は ON のときに物理キーボードのテンキーの送信コードを切り替えて送っているため、ソフトウェア上で押したところで他のキーの送信コードが変わるわけではないのでなんの意味を持たないのではないかと思います。

ということで、NumLock の代わりにボードを切り替えて NumLock を押しているかのように見せかけるようなボードを考えてみました。

2014-04-12 16_07_40-AllProject_2013 - Microsoft Visual Studio

まあ、大体は NumLock を押したときのキー配置と同じようにしてます。00 のところはメインボードから外したカンマを、上の方は Excel でよく使う入力キャンセルの Esc, セル横移動の Tab, イコール、右側には「元に戻す」「やりなおし」を配置してみました。ちなみに真ん中の「5」はスペースにする予定です。

現在配布されているちーたんタッチボードにはボードの切り替え(前後)のほかに指定した識別名を持つボードへのジャンプ切り替え機能もあるので、NumLock キーをそれに置き換えるという方法もあるのですが、NumLock キー本体の価値が 0 なのかどうかまだわからない状態なので、とりあえず今までどおり通常のボード切り替えとして配布する予定です。

あと、Ver 0.01 ではテンキーは右手用だけでしたが、Ver 0.1 では左手用も配布します。

]]>

Tips 追加

onodera_sf コメントをする

sorceryforce.net の方で Tips 追加しました。少し Tips が増えてきたので少しグルーピングしようかなーと思ってますが、まだ実装できていないのでもうしばらくしたらやります。Tips は一部ブログから書き直して転載しているものもあります。

]]>

[SSIS] Excel から型情報を意識せずに確実に読み込む方法

onodera_sf コメントをする

環境

SQL Server 2005, 2008, 2008 R2

発生する問題

SSIS (SQL Server Integration Services) の データクローから「Excel ソース」を使用すると Excel ファイルからデータを取り込むことができます。Excel の中身がヘッダ１行と２行目以降がデータという一般的なテーブルレイアウトであれば、そのままデータベースに取り込めるので結構便利です。

しかし、実際に使ったことがある方は経験されていることが多いと思いますが、Excel のデータの中身が変わると、SSIS デザイナ上でなぜか型が一致しないという警告が出ることがあります。一度詳細を開いて更新すればもとに戻りますが、Excel の中のデータが変わるとまた警告がでる、の繰り返しになります。

また、場合によっては Excel の中にはきちんとデータがあるにも関わらず、実際に読み込んでみると null で読み込まれる場合があります。

これらの現象は SSIS の問題ではなく、SSIS が Excel の読み込みコンポーネントとして使用している「Excel ISAM ドライバ」の仕様の問題で発生します。これは「Microsoft Jet 4.0 OLE DB Provider」でも「Microsoft Office 12.0 Access Database Engine OLE DB Provider」でも同様です。

このドライバが何をしているかというと、データを読み込む際にわざわざ型指定して読み込まなくても、Excel の中身を見て自動的に型を判別してくれるという便利そうな処理をしていますが、その代り、データの中身の型がころころ変わると読み込む際の型情報も変わってしまいます。そのため、SSIS 上のデザイナでも、事前に型を決定しているにもかかわらず、Excel の中身の型が変わってしまうことによって型が不一致であるという警告が表示されるのです。

このあたりの使用は下記のサイトをみるとわかりやすいかと思います。

簡単にまとめると、Excel の上位８行を読み込み、その列で最も多い型を読み込む型として使用するようになっています。例えば８行内に、文字列が３つ、数値が５つあればその列は「数値型」になります。逆に文字列が６つ、数値が２つであればその列は「テキスト型」になります。同じ個数の場合は「数値型」が優先されます。

ちなみに SSIS で説明していますが、ISAM ドライバーの問題なので、これを使うプログラムはすべて同じ問題を抱えています。

対応方法

ではこれを確実に回避するにはどうすればいいかというと、少し手を込んだことをしなければいけません。

まず、今回の対応方法としては前提条件としてすべての列を文字列として読み込みます。もともと数値の列も文字列として読み込むので、読み込んだ後は数値に変換する必要があります。これは臨機応変に対応してください。

ISAMドライバーで読み込む際、「IMEX」というパラメータを指定することができます。これに「1」を指定すると「インポート モード」になり、読み込むすべてのデータがテキストになります。ただし、Excel 内の指定した列のデータがすべて数値の場合は数値になってしまうので注意してください。一つでも文字列があれば読み込む型は文字列になります。

IMEX パラメータは「接続文字列」で指定します。SSIS デザイナで接続文字列を編集する場合は、接続マネージャーで作成した Exce ソースを右クリックし、プロパティを開きます。プロパティエリアに「ConnectionString」という項目がありますので、その文字列を直接編集します。GUI で設定するパターンもありますが、こちらは IMEX が設定できないので注意してください。

IMEX パラメータは下記のように設定します。一番後ろにある「Extended Properties」内にセミコロン区切りで「IMEX=1」を追加します。

  • Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:Test.xlsx; Extended Properties="Excel 12.0; HDR=YES; IMEX=1;”

接続文字列については以前まとめましたのでこちらのページを参照してください。

さて、これですべての列は文字列として読み込める「はず」なのですが、前述したとおり、Excel にすべて数値が入力されていた場合、または何も入力されていない場合はどうしても列の型が数値として読み込まれてしまいます。ですので、Excel の１行目か２行目に「１行すべて文字列だけを入力した行」を入れるようにします。こうすればほぼ間違いなく文字列として読み込めるようになります。読み込みもとの Excel ファイルにもよりますが、テキスト行を作ってその行だけ非表示にすれば編集上は問題ないかと思います。

 

まあ、個人的には Excel で読み込むのではなく、型を意識しない CSV ファイルのほうがよかったりもするんですけどね。Excel 上で編集すると「0000」とテキストとして貼り付けたつもりが数値として 0 になってしまってて気づかなかったりとかあるので。実は CSV も CSV でダブルクォーテーションのエスケープが考慮されてなかったりとかいろいろ問題はありますが (^^;)

]]>

Excel でグループ化の＋の向きを反転させる

onodera_sf 2件のコメント

image 別に+-をクリックしなくても縦ラインをクリックすれば折り畳み、展開は可能なのですが、ツリービューなんかをよく使っている人だとなんとなく違和感があるかと思います。それにこの形だと選択した範囲よりも１行下に+-が位置されてしまいます。 これを反転させるには以下の手順で設定します。

  1. メニューの「データ」タブから「アウトライン」不ループのプロパティを開く image
  2. 「詳細データの下」のチェックを外す。列の場合は「詳細データの右」のチェックを外します。
すると+-の向きが反対になります。 image この設定は選択していたグループではなくブック全体に影響することに注意してください。]]>

Excel で改ページのラインに自動的に枠線を設定する方法 (条件付き書式 編)

onodera_sf コメントをする

Excel で作成した印刷用の資料で各ページの上下左右に枠線を表示させたい場合があると思います。上部、左右については改ページされていても常に枠線を設定しておくことが可能ですが (行挿入しても左右の線は自動的に設定される)、各ページの下部に枠線を入れるには各ページごとに改ページのライン位置に枠線を設定しなくてはなりません。ですが、実際にやってみるといろいろと面倒なことがわかります。

  • ページが増えるごとに枠線を設定しないといけない
  • 改ページプレビューだと枠線がきちんと設定されているか見えにくい
  • 上のページで行がずれたりするとそれ以降のページの枠線をすべて直さないといけない
  • 印刷の余白や印刷幅を変更するとすべての枠線を修正しなければいけない
  • マクロでもできるけど、Excel 2007 以降の形式だとファイルの拡張子を変更しないといけない

ここでは下部の枠線を意識せずに自働的に枠線を設定する方法を、条件付き書式を使う方法で設定手順を説明します。

【環境】

  • Excel 2010

【条件】

  • すべての行の高さが完全に一致している必要があります。

【手順】

  1. 印刷プレビュー、改ページプレビューなどで１ページあたりの印刷行数を調べる (ページヘッダーがある場合は１ページ目と２ページ目以降で行数が異なるので２ページ目以降を参考にすること)
  2. 「A1」のセルを選択
  3. 「ホーム」タブから「条件付き書式」の「ルールの管理」を選択。
  4. 「新規ルール」ボタンをクリック。
  5. ルールから「数式を使用して、書式設定するセルを決定」を選択。
  6. 数式欄に「=MOD(ROW()-<ヘッダの行数>,<１ページ当たりの行数>)=0」と入力。(<>の箇所を任意の数値に設定)
  7. 右下の「書式」ボタンをクリックして、「罫線」タブから下線を設定。OKボタンをクリック
  8. さらにOKボタンをクリックして新しいルールを決定する。
  9. 作成したルールの適用先を「=A1:<シートの左下のセル>」に変更。(<>の箇所を任意の値に設定)
  10. 「OK」ボタンをクリックして印刷プレビューを実行。各ページの下に下線が表示されているか確認
]]>

HTTP経由でキューブにアクセスする方法

onodera_sf コメントをする

ネットワークが特殊な環境で2383ポートが使用できず、80(または443)ポートが使用可能な場合、HTTP経由でキューブにアクセスする方法があります。

詳しくは公式サイトに載っていますので手順通りに設定すれば問題ないかと思います。

※SQL Server 2012 へのリンクページですが、デフォルトのページが一部英語であったため、すべて日本語になっている上記のリンクに飛ばしています。

 

上記のリンク先の説明で気をつける点があります。

まず「msmdpump.dll」「msmdpump.ini」「Resources」の配置先が「C:inetpubwwwrootolap」と「C:inetpubwwwrootolapisapi」の２パターンが書いてありますが、isapiフォルダはなくても問題ありません。接続先のURLを短くしたいのであれば olap フォルダの直下に上記の３つを入れてしまった方がいいでしょう。

もう一点「msmdpump.ini」ファイル内の「ServerName」のサーバー名は「IISから見たキューブ(Anarysis Services)があるサーバーの名前」です。ですので、IISとキューブが同じサーバーにある場合はそのまま「localhost」で問題ありません。勘違いして外から見たIISサーバー名を指定するとつながらなくなってしまう場合があります。私はこれで結構はまってしまいました。

]]>