.NETプログラミング

ExcelのVBA

参考ページ

Excel出力サンプル

 public class OutputExcel : Output 
 {
   public String m_path = "";
   Application oXL = null;
   Workbooks oWBs = null;
   _Workbook oWB = null;
   Sheets oWSs = null;
   _Worksheet oSheet = null;
   int CurRow = 1;
 
   public override int Open() 
   {
     oXL = new Application();
     oXL.Visible = false;
     oWBs = oXL.Workbooks;
     oWB = oWBs.Add(Type.Missing);
     oWSs = oWB.Worksheets;
     oSheet = (_Worksheet)oWB.ActiveSheet;
     oXL.ScreenUpdating = false; // 画面更新をしない(最初から隠してるけど)
 
     return rc;
   }
 
   public override int Write( OutRow r ) // OutRowはArrayListから派生
   {
     for( int i = 0; i < r.Count; i++ ) 
     {
       Cell c = (Cell)r[i]; // Cellは独自定義。一つのセルに入れる情報
       String s = c.GetValue();
       oSheet.Cells[CurRow,i+1] = s;
     }
     CurRow++; // 行数カウント
 
     // 列幅の最適化
     Range oRng = oSheet.get_Range( "A1", "Z256");
     oRng.EntireColumn.AutoFit();
     ReleaseCom(oRng);
     oRng = null;
 
     return 0;
   }
 
   public override int Close() 
   {
     oXL.ScreenUpdating = true;
     ReleaseCom(oSheet);
     oSheet = null;
     ReleaseCom(oWSs );
     oWSs = null;
     oXL.DisplayAlerts = false; // 「上書きしてもいいですか?」の問い合わせを抑制
     oWB.SaveAs(m_path,Type.Missing,
           Type.Missing,Type.Missing,
           Type.Missing,Type.Missing,XlSaveAsAccessMode.xlExclusive,
           Type.Missing,Type.Missing,Type.Missing,Type.Missing );
     oWB.Close(false,Type.Missing, Type.Missing);
     ReleaseCom(oWB);
     oWB = null;
     ReleaseCom(oWBs);
     oWBs = null;
     oXL.Quit();
     ReleaseCom(oXL);
     oXL = null;
     
     GC.Collect();		//ガーベジコレクト起動
     return 0;
   }
 
   void ReleaseCom(Object o) 
   {
     System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
   }
 }

WorkSheetなどの特定範囲を参照する

  • MSのKB
  • 例)指定された範囲の行の選択範囲を得る
    // VBAの Rows("XX:XX")に相当
    Range rng = oSheet.Rows[ st + ":" + end, Type.Missing] as Range
    または
    Range rng = oSheet.get_Range( st + ":" + end, Type.Missing);

VBAからの翻訳のコツ

  • Selection は ExcelオブジェクトのSelectionなどが使える
  • 例:
    oSheet.Select(Type.Missing);
    Debug.Assert((oXL.Selection as Range) !=null);
    (oXL.Selection as Range).Font.ColorIndex = 0;

遅延バインディング

  • http://support.microsoft.com/default.aspx?scid=kb;JA;302902
    • Office2000のExcelに事前バインディング(Excel9.olbを参照)するとOffice 2003を入れている開発環境でコンパイルが通らなくなる。これを解決するには遅延バインディングにすればよいのだが、開発の手間が格段にかかってしまうのであまり現実的ではない。Excel制御の部分だけ別プロジェクトにしてCOMと相性のいいVB.NETでやった方がいいかもしれない。

Excelのプロセスが残る問題

  • オブジェクトの使用が終了したら、System.Runtime.InteropServices.Marshal.ReleaseComObject を使用して開放する。
  • このメソッドによって RCW の参照カウントを減らします。あとGC.Collect()も呼んだ方が良いらしい。
  • Excelのプロセスが残らないようにするポイントは以下の通り。
    • 各オブジェクト(Application,Workbooks,_Workbook, Sheets, _Worksheet, Rangeなど)をすべて変数で受けて、ReleaseComObject()で一つずつ明示的にリリースする。
    • Application のQuit()メソッドは他のオブジェクトをすべてリリースし終えてから呼ぶ。
    • Cellsなんかも見落としがちだが、必ず個別に受けてリリースしないといけないので激しく注意!
      • 良い例
        //Cells と Cell は別であることを激しく注意せよ!
        Range cells = oSheet.Cells;
        Range cell = cells[1,1] as Range;
        cell.Select();
        ReleaseCom(cell);
        cell = null;
        ReleaseCom(cells);
        cells = null;
      • ダメな例
        Range cell = oSheet.Cells[1,1] as Range; // <--ここがダメ!!
        cell.Select();
        ReleaseCom(cell);
        cell = null;
  • とにかく原則としてピリオドでオブジェクトを2つ以上つないではいけないと思ったほうがよい
  • よく「↑のようなことはちゃんとやってるのにプロセスが残ってしまう」と言ってくる人がいるが、たいていソースを見るとちゃんとやってない。そのくらい、わかっていてもついうっかりやってしまうほどこれは難しいということ。細心の注意が必要。

枠線を引く

  • サンプル
    void DrawFrameLines()
    {
        try
        {
            oSheet.Activate();
            Range ac = oXL.ActiveCell;
            Range lastcell = ac.SpecialCells(XlCellType.xlCellTypeLastCell,Type.Missing);
            Range all = oSheet.get_Range("A1:A1", lastcell);
    
            int i = 0;
            Border[] bdr = new Border[6];
            bdr[i++] = all.Borders[XlBordersIndex.xlEdgeTop];
            bdr[i++] = all.Borders[XlBordersIndex.xlEdgeBottom];
            bdr[i++] = all.Borders[XlBordersIndex.xlEdgeLeft];
            bdr[i++] = all.Borders[XlBordersIndex.xlEdgeRight];
            bdr[i++] = all.Borders[XlBordersIndex.xlInsideHorizontal];
            bdr[i++] = all.Borders[XlBordersIndex.xlInsideVertical];
    
            foreach(Border b in bdr) 
            {
                // 列や行が1つのときにxlInsideHorizontal,xlInsideVerticalのボーダーがエラーとなるので
                if( (int)b.LineStyle < 10 ) { //これでいいのかどうかもうひとつわからないが
                  b.LineStyle = XlLineStyle.xlContinuous;
                  b.Weight = XlBorderWeight.xlThin;
                  b.ColorIndex = XlColorIndex.xlColorIndexAutomatic;
                }
            }
    
            for(int j = bdr.Length-1; j >= 0; j--) 
            {
                ReleaseCom(bdr[j]);
            }
            ReleaseCom(all);
            ReleaseCom(lastcell);
            ReleaseCom(ac);
        }
        catch(Exception ex)
        {
            Debug.Assert(false, ex.Message);
        }
    }

1行目をタイトル行として背景色をつける

  • サンプル
    // タイトル行に背景色をつける
    void ColorTitleLine() 
    {
        try
        {
            oSheet.Activate();
            Range a1 = oSheet.get_Range("A1:A1", Type.Missing);
            a1.Select();
            Range end = a1.get_End(XlDirection.xlToRight);
            a1.Select();
            Range lastcell = a1.SpecialCells(XlCellType.xlCellTypeLastCell,Type.Missing);
            int col = lastcell.Column;
            
            //データが1列しかない場合、こうしないと256列扱いになってしまう。もっとスマートな方法ないかな?
            Range line;
            if( col == 1 ) 
                line = a1;
            else
                line = oSheet.get_Range(a1, end );
            Interior i = line.Interior;
            i.ColorIndex = 34;
            i.Pattern = XlPattern.xlPatternSolid;
    
            ReleaseCom(i);
            ReleaseCom(line);
            ReleaseCom(end);
            ReleaseCom(lastcell);
            ReleaseCom(a1);
        }
        catch(Exception ex)
        {
            Debug.Assert(false, ex.Message);
        }
    }

Excelのワークシート名に使えない記号の処理

  • ワークシート名に使えない文字がいくつかあるので注意したい
  • 以下はあらかじめ安全な名前に変換する例
     String MakeSafeName(String Name)
     {
       //禁止されてる文字はピリオドに変換する
       Char[] ErrChar = { ':', '\\', '/', '?', '*', '[',']'};
       foreach(Char c in ErrChar)
       {
         Name = Name.Replace(c, '.');
       }
       Name = Name.Trim();
    
       //長すぎる名前はダメ
       if( Name.Length > 31 )
       {
         Name = Name.Substring(0, 31);
       }
       return Name;
     }

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2017-07-17 (月) 12:44:52 (1391d)