Grapecity SpreadをExcel出力する件
先日よりGrapecity Spread For Winを使い始めて、今までDataGridView使ってシコシコやってたのをSpreadに置き換えたりなんかしてるわけだが…。
当然ながら（？）「Excelに書出したい！」という要望はやってくる。
前の記事にも書いたけど、標準のファンクションであるから楽勝！ と思ってたんだが、ちょっといろいろ巧くいかない状況が続いた。
問題点は大きく2つ
①ヘッダーが保存されない。
②Excel出力したものを印刷しようとすると行列番号が出力されてしまう。
結果的にはどちらも大したことしなくてもできることだったのだが、そこにたどり着くまでが長かった…。
①ヘッダー保存
DBからデータ引っ張ってきて「客先」だの「客先コード」だのと表示させているのだが、Excel出力するとこのヘッダーなしで保存されてしまう。
SaveExcelのオプションでなんとかなるのだろうとは思うのだが、ネットで調べても答えが見つからない。
やむなく取った手段は力業。
一旦仮のスプレッドシートを作って、現状スプレッドシートからシート毎にコピー → シート毎にヘッダ作るのに必要な行追加してヘッダ部分をセルに複写
しかし、これだとAddUnboundRowsした行が巧く再現できてなかったりする。
まぁ、そこも力業でなんとか再現した上でSaveExcel。
これでヘッダーがセル上に再現されてExcel出力できた…と。
今のところの最終的な解決策としては…。
やっぱりSaveExcelのオプションでなんとかなることがわかりまして。
Dim fpSD1 AS FarPoint.Win.Spread.FpSpread=FpSpread1
fpSd1.SaveExcel(fileName, FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders)
事前準備として、ヘッダに明示的に文字列を渡しておく必要あり。
つまり
For i As Integer = 0 To fpSd1.ActiveSheet.ColumnCount - 1
fpSd1.ActiveSheet.ColumnHeader.Cells(0, i).Value = fpSd1.ActiveSheet.Columns(i).Label
Next
としておく。
これでOK。
なんと間抜けなことをしていたのやら…。
注意事項としてはこのオプション、SaveBothCustomRowAndColumnHeadersというのもありまして、まぁ、行ヘッダのみ、列ヘッダのみ、どちらもを選べるようになっているわけで、行ヘッダが不要な場合は上記のように列ヘッダのみ書出のオプションを選んでやらないと、シートの左端に余計な空白列ができてしまうこと。
②Excel出力したものを印刷しようとすると行列番号が一緒に印刷されてしまう件
こんな感じ…。
Excelのページ設定で「シート」タブ内の「印刷」「行列番号」のチェックを外してやれば解決なので、「印刷前にはチェック外してね」と情けないことを伝えていたのだが…。
正直この不細工な状況だと、OpenXML使ってExcel出力してるほうが数倍精神的には健康的かとも思ったり。
Grapecityのサイトでは「Excel出力前に印刷設定してやればよい」旨書いてあるので、そこに書かれていたとおり（言うてもSpread for Windows Forms 5.0Jの情報だが…）、
Dim pi As FarPoint.Win.Spread.PrintInfo = New FarPoint.Win.Spread.PrintInfo
pi.ShowRowHeader = False
pi.ShowColumnHeader = False
pi.ShowGrid = False
For Each sh As FarPoint.Win.Spread.SheetView In fpSd1.Sheets
sh.PrintInfo = pi
Next
とやるのだが、どうにも行列番号が印刷されてしまう。
しかし、ShowGridオプションはしっかり効いているようで、Falseだと罫線印刷はなくなる。…ってことは、指定するべきオプションが違ってるのか、或いは値の渡し方が間違っているのか？
結論としては…。
Grapecityのサイト、ナレッジベース内PowerTools Spread for Windows8.0Jの印刷オプションの解説を参考にして、
Dim pi As FarPoint.Win.Spread.PrintInfo = New FarPoint.Win.Spread.PrintInfo
pi.ShowRowHeader = FarPoint.Win.Spread.PrintHeader.Hide
pi.ShowColumnHeader = FarPoint.Win.Spread.PrintHeader.Hide
pi.ShowGrid = False
For Each sh As FarPoint.Win.Spread.SheetView In fpSd1.Sheets
sh.PrintInfo = pi
Next
で無事解決。
いちいち言い訳がましく「チェック外してね」などと言わなくてもよいようになりました。
うーん。Grapecityのツールにはしっかりそれなりのボリュームのドキュメントがついているのだけれども、痒いところに手が届かないというか。
使用者が少ないのか、ネット上でも情報が少ないし、あってもバージョンが違うとか、他のツールのものだったりだとか、サクッと的中する感じがないのですよね。
もっと使用者増えて情報量増えてきたら、もっとハードル低くなると思うのだけれども。
Excelマクロを参照設定なしで動かす
「データベース上にある担当者リストをExcelで使いたい」という声があったので、「難しくないよ」と安請け合い。
VBAエディタの参照設定から「Microsoft ActiveX Data Objects 2.8 Library」にチェック入れて、ADODB.ConnectionとADODB.Recordsetを宣言して……と、ここまでは過去の経験通り。
RecordCount取得しようとしたらどんなに条件文変えても「-1」が返されて少し焦ったが……。
まぁ、これはレコードセットをNewした後にCursorLocationをADODB.adUseClientにしてやることで解消できましたが……。今までもこんなだったっけ？ 記憶にないなぁ。
なんかMoveLast→MoveFirstした後でないと正しいRecordCountが取れないということはあったけど、それはDAOのときか？
テストも完了して、要望通りのものできた！ とちゃちゃっとメールして他のことしてたら、
「早々にありがとうございます。でも動きません」と。
「コンパイルエラー云々」と出るそうだ。
すぐに参照設定の問題とは気づいたが、チェック入れた状態で渡したはず。
今まで何度となく同じようなことやってきたけど、何が違うのか？
ネット上では「Excelのバージョン違い云々」が原因とあるが、会社はOffice365を使っており、そこは私のPCと依頼者のPCに違いはない。しかも「Microsoft ActiveX Data Objects」ってバージョン6.1までがリストに出てくる状態なので、過去のバージョンであろう2.8なら今のExcelで動くのではないの？ 古すぎて依頼者のPCには入ってない？
うーん。
とりあえず6.1にチェック入れて渡そうとしたが、「まだ動きませんよ」と突っ込まれるのも嫌なので、バージョン指定しなくても済む方法はないかと検索。
あるね。
ADODB.Connection、ADODB.Recordsetをそれぞれ明示せずにObjectとして宣言。いつもならNewするところで「CreateObject」するとよいらしい。
Dim dbCon As Object
Dim dbRs As Object
'サーバへの接続文字列
conStr = "Provider=SQLOLEDB;Data Source=" & strDBSever _
& ";Initial Catalog=" & strDBName _
& ";..........
Set dbCon = CreateObject("ADODB.Connection")
dbCon.Open conStr
Set dbRs = CreateObject("ADODB.Recordset")
これで接続できました。めでたしめでたし……。でもない！
RecordCount取得するための
dbRs.CursorLocation = ADODB.adUseClient
でエラーになる。VBAにしてみりゃ参照設定外してるので、いきなりADODB言われてもわからんわな。
上のCreateObject("ADODB.xxxxx”）に倣って
Dim adClient As Object
Set adClient = CreateObject("ADODB.adUseClient")
dbRs.CursorLocation = adClient
等とやってみるが、「オブジェクトを生成できません」と。まぁ、そうだろうとも。
ここでふと。
dbRs.RecordCountがダメなら、1個1個数えりゃいいやん！
原点に返るというと聞こえは良いが、VBAでデータベース弄り始めた頃のベタなやり方。
dbRs.movefirst
dbCount = 0
Do Until (dbRs.EOF)
dbCount = dbCount + 1
dbRs.movenext
Loop
まぁ、レコードが何万件もある訳でなし、使用上問題ないかと。
で、改めて依頼者PCで実行してもらったところ、無事動作しましたとの報告。
よかったよかった。
この方法なら参照設定触らなくてよいし、環境依存少なそうでよいね。
但し、自動メンバって言うの？ あれ、使えません。
そりゃそうですよね。dbRsとか言ってもコード上はただのObjectなんで。
Grapecity Spread for Windows Forms 12.0Jを使ってみる
「Grapecity Spread for Windows Forms 12.0J」を買ってもらったので、使ってみる。
今までDatagridviewで苦労してたExcelライクな表示や機能が最初から実装されててすげー楽！ とちょっと喜んだのだけれども、ネット上にドキュメント少なくて結構苦労してたりする。
フォームに貼り付けて、SQLサーバやExcelファイルから引っ張ってきたデータを表示させるところまでは楽ちん。
Dim dt AS DataTable=______________
FpSpread1.ActiveSheet.DataSource=dt
でOK。
Excelファイルからの読み込みは
Dim myFile AS String=______________
FpSpread1.OpenExcel(myFile)
でOK。
.xlsであろうが、xlsmであろうが、.xlsxであろうが開いてくれる。
行幅や列幅、罫線、文字修飾などExcelで割り付けている装飾もほぼ再現されている様子。
シェイプはちょっと表示位置が怪しいかな？
ちなみにExcel形式での保存も可能なのだが、.xlsx形式での保存をしたい場合はSaveFlagsというものを設定してやる必要があるらしい。
付属のサンプルコード集に
データグリッドビューでいろいろやる
社内の製造進捗管理ツール的なものを作ってみる。
ガントチャート…とまではいかないにしても、タスクがあって、それがどんなタイミングで行われるかカレンダー的表示は欲しい。
試しにグレープシティのGanttViewを使ってみたが、探し方が下手くそなのか、これ、案外文献ないのな。
なんとかタスク登録はできた。カレンダー部分の表示は簡単…だが、見た目の変更はややこしそう。タスク表示部の文字位置の変更なんかも結局巧くできず。
プロパティ見る限りではその辺りの設定が見つからなかった…。
もう少し時間かければいろいろ見えてくることもあるだろうが、工数減らすためのコンポーネントを理解するために時間をかけるってのもちょっと。
見た目とかタスクの考え方を参考にさせて頂き、データグリッドビューでそれっぽいものを作ることにする。
そもそも、作業実施のタイミングをカレンダー表示できればそれでよいレベル。作業と作業の繋がりとかは現時点で必要ない。
こんな感じでどうかと。
ぼかし入れ倒しなのでわかりにくいが…。
予定を示す緑色の棒はデータグリッドビュー上にパネルコントロールを貼りつけて表示させている。各緑棒の説明はテキストボックス貼りつけ。データグリッドビューのスクロールに合わせて上下左右に追従するようにしている。
ポイントは下地のデータグリッドビューだが、単純にひとつのコントロールで済ませているわけではない。まぁ、見る人が見ればわかるだろうからもったいつけてもしょうがないが…。
実は3つのデータグリッドビューを並べて構成している。
左側のいわゆるタスク表示しているところ、グラフを表示しているところ、上のSSでは見えていないが右端にスクロールバー表示担当のデータグリッドビューの3つ。
この3つを連動して表示させることで、緑棒が左端や右端にいったときの処理をあんまり気にしないで済むようにしている。
ちゃんと緑棒を左右にドラッグして日程変更することもできるし、画面サイズの変更でもグラフ部の見た目が崩れることはない。自分的にはなかなかよくできた画面になったかなと。
問題はタスクが増えれば処理が重くなる…というところ。
1,000件ぐらいのタスクになるとそれだけグラフ用のパネルコントロールが増えるので、スクロール時なんかは画面がちらつきまくる。
なので、大量タスク用にキャラクター表示版も作成。
こんな感じ。
流石にドラッグしてグラフ部を動かす…といったことはさせてないが、まぁ表示としては十分かと。
で、Excelでの操作に慣れている人に使ってもらうには抽出の機能が要るなと。
SQLサーバからデータ引っ張ってくる時点でWHERE文で条件入れればよい話なのだが、SQLサーバへのSELECT文で結構ややこしいことしているので面倒くさそう＆抽出の度に時間かかるな…と思っていたら、ちょっとお手軽な方法を見つけた。
データテーブルからSelectでRowdata()を作るやり方。
Dim HyojiDT As Datatable=New Datatable()
HyojiDT=TaskDT.Clone()
Dim seekstr As string=String.Format("{0}='{1}'","加工機","XXX")
Dim rows As Datarow()
rows=TaskDT.Select(seekstr)
For Each row As DataRow In rows
HyojiDT.ImportRow(row)
Next
Datagridview1.DataSource=HyojiDT
…みたいにすれば、SQLサーバからちょい複雑気味のSELECT文で引っ張ってきたデータを維持したまま後追い抽出みたいなことが可能。
AND、ORで複数の条件をつなぐことも可能。
元のデータテーブルを保持したままなのでメモリは多少食うだろうが、今のところ動作に支障が出るような状況はない。
処理的にもいろいろ応用が利きそうなので、過去のプログラムも見直してみることにする。
文書管理的なものが作れないか…のテスト
ファイル検索のツール作りながら、「文書管理的なツール、作れんかな？」と。
フォルダ階層で管理（？）されているファイルサーバがあるのだが、先達が決めたルールでフォルダ作ったり、ファイル配置したりしているもんで、どうにも馴染めない。「客先ー設備」括りで構成されている部分等は、あちこちのフォルダに「仕様書」であったりだとか「検査証」であったりだとか、一様のファイルを探すのに苦労してしまう。全文検索もできないし…。
文書管理システムを導入すればそのあたりは解消できるのだろうけれども…。
まぁ、文書管理システム導入の提案は追々するとして、自力で「そのようなもの」を作れないかと。
フォルダ階層はそのまま保持し、フォルダやファイルにタグのようなものを割り当てて、例えば機種別ツリー表示や文書タイプ別ツリー表示ができればいいかと。もちろんキーワード含む文書の検索は全文検索できて、かつ指定範囲からの検索…なんてのができれば…。
全文検索はともかく、ツリー表示のところは時間かければ何とかなりそう…と高を括る。
で、まぁ、文書管理と言えば「プレビュー表示」でしょう。
これができんとタグ付けも面倒だし。
PDFは実績あるので問題なし。大量にあるOffice文書をプレビューで表示したい、というのが今回の課題。
で、調べてみた。
環境はVisual Studio7、Officeは概ね365。
「VB.net Office プレビュー」とか「VB.net Excel 表示」とかでググってみると、同じようなことを考えているらしい質問はちらほら。しかし、コレ！ といった回答がない。
OLEとかの時代だとデスクトップアプリにインラインでExcel文書を表示させるみたいなこともできてたみたいなんだが、どうもよくわからない。
「WebBrowserコントロールを使えば…」みたいなことが書いてあったので試してみた。
うーん。指定したWord文書を開くかどうかのダイアログが出て、「開く」をクリックすると別でWordが立ち上がる。
確かに指定したファイルが開かれていることには違いないが、思っているのとは違う。
上のページを読み進めていくと、
”2007の Office プログラムは Web ブラウザーで Office ドキュメントを開かないようになっています。 この動作は、WebBrowser コントロールにも影響します。”
とのこと。
WebBrowserコントロールの代わりにカスタムActiveXドキュメントコンテナーを使え、と。
早速ググるも有益な情報がない。
レジストリ弄ればWebBrowserコントロールでもインラインでOfficeファイルを開けるようになるらしいことが記述されていたので、リンクに飛ぼうとするも404エラー。
「インターネットエクスプローラー Office 表示」等で検索し、レジストリのどこを弄ればよいかの情報を漁る。
これかな？
http://niyodiary.cocolog-nifty.com/blog/2009/08/excelwordpowerp.html
情報は決して新しくはなさそうだが…。
試しにレジストリエディタで[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Word.DocumentMacroEnabled.12]
だけ弄り、IEにWordファイル（.docx）をドラッグドロップしてみる。
…と、「このファイルを開きますか？」的なダイアログは相変わらずだが、IE上でインライン表示するのを確認。
そのままVisual Studioでさっきのプロジェクトを試してみる。
画面左側にPanel1（Dock=Left）、右側上部Panel2（Dock=Top）、右側下部Panel3（Dock=Fill）の構成。WebBrowserコントロールはPanel3上に貼り付け。サイズ調整していないが、勝手にPanel3全域に貼り付けられた状態（？）
うーん。まぁ、一応Word文書は表示されてはいるが…。
Button2クリックでWebBrowserコントロールのNavigateに指定ファイル（コード直書き）を割り当ててるだけだが、やっぱり「開く？」のダイアログ出るし、「開く」を選択してからこの状態になるまで２、３秒待たされる感じだし…（ちなみに当該Wordファイルのサイズは20KB）。
何よりメニュー要らないし！
表示エリアの問題はなんとかなりそうだけれども…。
単なるプレビューでいいんだけどなぁ…。
今のところはインラインで開けただけでヨシとしておこう。
時間あれば改めてテストしていくことにする。
ファイルパスの分解で躓く
「ファイルがあちこちのフォルダに複写されており、原本変更したときにどのフォルダに複写されているか確認する必要がある。なんとかならないか？」という問合せあり。
文書管理としてどうかとは思うが、そこは片目つぶって.netでツールを作ったのだが、途中ファイルパスから文字列抽出するところで躓いた。
こんなツール
①ファイルを指定→②検索したいフォルダのルートを指定→③当該ファイルが格納されているフォルダをリストで表示
一応、一括または指定したものに対して上書き複写できるようにはしたいが、まずはリスト表示まで。
仕様として…
ア）原本ファイルがあるフォルダについては、検索時もしくはリスト表示時に除外することにする。
イ）検索は基本的に前方一致とする（複写先ファイルで「……(1).xls」や「……コピー.xls」等が存在するため）。
ウ）パスにおいて、ファイル名のひとつ上の階層は機種名、更にひとつ上の階層は客先名なので、リストに「客先」「機種」のカラムを作成して表示する。
ファイル検索は「System.IO.Directory.EnumerateFiles」を使って実行。
参考URL
https://dobon.net/vb/dotnet/file/getfiles.html
https://www.atmarkit.co.jp/fdotnet/dotnettips/1018enumfiles/enumfiles.html
第一引数の検索対象フォルダの中から第二引数の文字列を含むファイルをすべて取得してくれるので、ここまではらくちん。
戻ってきた文字列配列を分解してDatatableに格納していけばよいだけ。
実際ローカルで単純にファイルのフルパスをテーブルに格納して表示させるテストではまったく問題なかった。
問題が起きたのはこのあと。
ネットワークフォルダから検索するテストをする直前に仕様ウを実装しようとしたのだが、ファイル名、機種、客先の分解をInstrRev(Path, "￥"）←（「￥」のところは円マーク半角、以下同様）で単純に分解していくだけでは芸がないと、わざわざFunctionにしてしまったわけだ。
ファイル名ならInstrRev1回、機種なら2回、客先なら3回…引数にフルパス渡して、取り出したい文字列のタイプを指定して…取り出した文字列はFunction内で宣言しているRtnStr変数に格納し、Returnする……といった感じ。
すんなり巧くいくと思ってたら…。
客先も機種もファイル名も空っぽ。
デバッグで確認してみたらFunctionに渡されているフルパスの「￥」が「\」になっているっぽい（実際はそう見えてるだけなんだろうが…）。
Function内InstrRevの第二引数は「￥」。区切り文字が見つからず、RtnStrに文字列入れないままFunctionを抜けているんだろうか？ と。
InstrRevの戻り値が「0」だったのでたぶんそういうことなんだろう。
Visual Studioのエディタでは「\」が入れられないので、CHR使ってみたりもしたんだが、InstrRevの戻り値は「0」のまま。
ちなみにFunction実装前にファイル名部分だけを取り出す処理をダイレクトに記述していたのだが、そのときはInstrRev(FilePath, "￥")できちんと分解できてたはず（Path.GetFileName()は使っていなかった）…。
Functionに引数としてフルパス渡すときに何か変化が起きているんだろうか？
で、Function使用をやめ、元プロシージャ内に直接コード記述してみると…。
おや？ ちゃんとInstrRevが値を返してくる。
結果的にはプロシージャ内処理で求めている処理はできてしまったのだが、なんとも釈然としない。
ByValとかByRefは関係ないよね？ それって元の値を変えてしまうかどうかだよね？
Sytem.IO.Directory.EnumerateFilesで取得した文字列配列をFor eachで分解して文字列変数に格納、そのままFunctionに渡しているだけなんだけどなぁ…。
まぁ、Functionさえ使わなければ巧くいっている（？）ので、この件突き詰めることはしないけれども…。
そういうことがあった…という覚書。
WordでのMS IMEの挙動が嫌
未解決事項です。
MS Wordで文書打ってたらどうにもMS IMEの挙動がおかしい。
スペース打って変換→Enterキーで確定…しているはずなのに、変換中文字列の下に破線が残る。
このSSは変換中のものですが、この「つのだ」の下のところの破線がそのまま残る感じ。
ちなみに
OS：Windows10Pro（18362.418）KB4523786
Word：Microsoft Ofiice 365 Business（1909 12026.20344）
特別Office関係で設定変えたりしている箇所なし。
すっごくイライラするし、入力・変換効率確実に落ちてるのが実感できる。
いつ頃からだろう？
ほぼ毎日Word使っているけど、先週こんなにイライラした覚えないからなぁ…。
グーグル先生に尋ねてみると、「Officeを修復インストールすれば改善された」云々の記事が。
で、実際にやってみる。
「インストールは20分程度」とのことだったが、なんだかんだ1時間ぐらい待たされた。
インストール後PC自体を再起動させた後、Word作業へ。
（実際は認証とれてない云々のメッセージが消えず、ここでも時間とられたが…）
うむ。Enterキーで確定させるとちゃんと変換中文字列の下線は消えている模様。
改善されたと判断し、Wordでゴリゴリ文書作成に戻る。
が…しかし。
どうもゴリゴリやってると症状再発する模様。
Wordを一旦終了させて再度文書編集し始めると最初は症状出ないが、暫くするとまた破線が出てくる。これの繰り返し…。
キー入力せずに暫く放置しておいたら破線がすっと消えることがあったので、バックグラウンドで何かやってくれてる可能性も。
「自動保存」が悪さしているのかも、と、これを「オフ」にするも状況変わらず…。
しょうがないのでイライラしつつも症状が出たらWord再起動させつつ文書作成を継続。
結局A4サイズ3ページ単語数2,567の文書作成の間にWordの再起動5、6回という、ね。効率悪いにも程があるやね。
で、変換後文字列の下に破線が出てるところをSS撮って上げようと思ったら、これが再発しないのですよね（冒頭のSS）。
一応、体感的にはこの症状はWordでだけ起こっている模様（他でも起きてるかもだけど、認識していない）。
バージョン違いかもしれないけど、先のグーグル先生が教えてくれた記事でも「Wordだけで…」という記述あり。
ちなみに先の記事では「Windows Updateの影響…」云々の記載あり。
先週末Windows Update来てたよなぁ…。タイミング的にもそれかな？
…ってことで、まったく解決してないけど、とりあえず様子見します。
次のUpdateで変化あるかどうか？
なかったらOpenOfficeに移行しちゃうぞ。
