【NetOffice】Worksheet.UsedRangeプロパティから更に値の有無で絞り込むロジック組んでみた。(1)

  • 2012.12.01 Saturday
  • 20:51
JUGEMテーマ:コンピュータ

------------------------------------
追記
以下でロジックのチューニングを行いました。
【NetOffice】Worksheet.UsedRangeプロパティから更に値の有無で絞り込むロジック組んでみた。(2)
http://pro.art55.jp/?eid=1304132
【NetOffice】Worksheet.UsedRangeプロパティから更に値の有無で絞り込むロジック組んでみた。(3)
http://pro.art55.jp/?eid=1304133
------------------------------------



Source and Project

Worksheet.UsedRangeプロパティで取得したRangeは、現在使用されているセルの範囲(長方形)のRangeになります。この使用されているというのが、Range.Values2に値がなくてもフォントの設定や背景色が変更されていたりしても、同様に使用されていると判定されます。何かのアプリケーションを作成しようとロジックを組む場合、値がない場所は入らないというケースが多いのではないかと思います。UsedRangeだけにかぎった話ではないとは思うのですが、とりあえずコードを作成してみました。

using System.Linq;
using NetOffice.ExcelApi;
using NetOffice.ExcelApi.Enums;

namespace Art55.NetOfficeDemo20121201_001
{
    public  static class WorksheetUtils
    {
        public static Range GetHasContentUsedRange(this Worksheet sheet)
        {
            Range usedRange = sheet.UsedRange;
            if (AnyContais(usedRange))
            {
                return null;
            }

            int top = GetUsedTopIndex(usedRange);
            int tail = GetUsedTailIndex(usedRange);
            int left = GetUsedLeftIndex(usedRange);
            int right = GetUsedRightIndex(usedRange);
            return sheet.Range(sheet.Cells[top, left], sheet.Cells[tail, right]);
        }

        private static bool AnyContais(Range usedRange)
        {
            int end = usedRange.Row + usedRange.Rows.Count - 1;
            return usedRange
                .Rows
                .First()
                .Columns
                .Select(cell => cell.Value2 != null
                                    ? cell.Row
                                    : cell.End(XlDirection.xlDown).Row)
                .All(rowIndex => rowIndex > end);
        }

        private static int GetUsedTopIndex(Range usedRange)
        {
            return usedRange
                .Rows
                .First()
                .Columns
                .Select(cell => cell.Value2 != null
                                    ? cell.Row
                                    : cell.End(XlDirection.xlDown).Row)
                .Concat(new[] { usedRange.Row + usedRange.Rows.Count - 1 })
                .Min();
        }

        private static int GetUsedTailIndex(Range usedRange)
        {
            return usedRange
                .Rows
                .Last()
                .Columns
                .Select(cell => cell.Value2 != null
                                    ? cell.Row
                                    : cell.End(XlDirection.xlUp).Row)
                .Concat(new[] { usedRange.Row })
                .Max();
        }

        private static int GetUsedLeftIndex(Range usedRange)
        {
            return usedRange
                .Columns
                .First()
                .Rows
                .Select(cell => cell.Value2 != null
                                    ? cell.Column
                                    : cell.End(XlDirection.xlToRight).Column)
                .Concat(new[] { usedRange.Column + usedRange.Columns.Count - 1 })
                .Min();
        }

        private static int GetUsedRightIndex(Range usedRange)
        {
            return usedRange
                .Columns
                .Last()
                .Rows
                .Select(cell => cell.Value2 != null
                                    ? cell.Column
                                    : cell.End(XlDirection.xlToLeft).Column)
                .Concat(new[] { usedRange.Column })
                .Max();
        }
    }
}

上記のロジックを絵にすると、たとえばしたの用にシートがあったとします。黄色の範囲がUsedRangeに相当します。そして、今回見つけたい範囲は●がちょうど収まる範囲の長方形のRangeになります。


B3からH3のEnd(xlDirection.xlDown)でUsedRangeの範囲でヒットする行のインデックスを検索していきます。下の場合、D5が行の最小になります。


列の最小を検索している例がしたの図になります。



コードを組んでみましたが、正直、これで大丈夫なのか不安です(笑)Endメソッドの終端の判定とValue2がnullであることと同一して良いのかとか、Endメソッドの判定ってそもそも不明ですし・・・。

とりあえず、動作させてテストしてみまいした。

using System;
using System.Linq;
using NetOffice.ExcelApi;
using NetOffice.ExcelApi.Enums;

namespace Art55.NetOfficeDemo20121201_001
{
    class Program
    {
        static void Main()
        {
            var application = new Application {Visible = true};
            var sheet = application
                .Workbooks
                .Add()
                .Worksheets
                .OfType<Worksheet>()
                .First();

            while ((Console.ReadLine() ?? string.Empty).ToUpper() != "Z")
            {
                Range usedRange = sheet.GetHasContentUsedRange();
                if (usedRange != null)
                {
                    usedRange.Interior.Color = XlRgbColor.rgbSkyBlue;
                }  
            }
        }
    }
}

UsedRangeのうち値がある範囲(長方形)だけ背景色がスカイブルーに色が変わります。





UsedRangeが返してくる範囲は黄色の範囲です、それに対して、値がある範囲が終端となる範囲がスカイブルーになっていることが、わかります。
ただ、今回の実装では、全ての範囲が利用されているようなケースでは恐ろしく時間がかかります。私のパソコンでは、短気な私が待てないほど時間が経過したため途中でやめました。そもそも、今回の判定が上下左右の判定を行っていますが、実際には上下または左右だけで判定できる事は自明なのでコストが2倍かかっており効率がわるいです。
また、VBAは大昔からあるプログラミング言語なので、この辺りの賢いやり方というのがあると思います。そういうのをググって移植してあげれば良いかと思います。

Source and Project

------------------------------------
追記
以下でロジックのチューニングを行いました。
【NetOffice】Worksheet.UsedRangeプロパティから更に値の有無で絞り込むロジック組んでみた。(2)
http://pro.art55.jp/?eid=1304132
【NetOffice】Worksheet.UsedRangeプロパティから更に値の有無で絞り込むロジック組んでみた。(3)
http://pro.art55.jp/?eid=1304133
------------------------------------

--------------------------------------
本投稿は、CodePlexで公開されているNetOfficeを利用していませんが、紹介します。
NetOffice - MS Office in .NET
http://netoffice.codeplex.com/

NetOffice関連の記事は下記にまとめています。
【NetOffice】【Excel】NetOfficeのまとめ
http://pro.art55.jp/?eid=1304102
--------------------------------------

コメント
コメントする








    
この記事のトラックバックURL
トラックバック

calendar

S M T W T F S
   1234
567891011
12131415161718
19202122232425
2627282930  
<< November 2017 >>

あわせて読みたい

あわせて読みたいブログパーツ

selected entries

categories

archives

recent comment

  • 【キーボード】6年前のRealForceを復活させることはできる!?その3
    art55 (05/22)
  • 【キーボード】6年前のRealForceを復活させることはできる!?その3
    分解大好き (05/18)
  • 【.NET Framework 4.5】 IListがIReadOnlyListを継承してない理由。
    art55 (02/04)
  • 【.NET Framework 4.5】 IListがIReadOnlyListを継承してない理由。
    Gen (02/04)
  • 【キーボード】RealForce が壊れて帰ってきた。
    art55 (04/29)
  • 【.NET Framework 4.5】 IListがIReadOnlyListを継承してない理由。
    art55 (02/23)
  • 【.NET Framework 4.5】 IListがIReadOnlyListを継承してない理由。
    かるあ (02/22)
  • 【C#】Dictionaryの実装・データ構造・アルゴリズムを観察する。
    art55 (01/16)
  • 【C#】Dictionaryの実装・データ構造・アルゴリズムを観察する。
    karuakun (01/16)
  • 【NetOffice】【Excel】死なないExcelプロセスをKillする。
    art55 (12/05)

recent trackback

recommend

recommend

recommend

C#プログラマのための.NETアプリケーション最適化技法 (Programmer's SELECTION)
C#プログラマのための.NETアプリケーション最適化技法 (Programmer's SELECTION) (JUGEMレビュー »)
Sasha Goldshtein,Dima Zurbalev,Ido Flatow,サシャ・ゴルドシュタイン,ディマ・ズルバレフ,イド・フラトー

recommend

ろんりと集合
ろんりと集合 (JUGEMレビュー »)
中内 伸光
とてもわかりやすいです。

recommend

recommend

シャノン・ノイマン・ディジタル世界
シャノン・ノイマン・ディジタル世界 (JUGEMレビュー »)
市川 忠男
4章がリレーショナルデータベースな内容になってます。ページ数があまりありませんが、ポイントがものすごく的確にまとまっていて、感動します。

recommend

recommend

東プレ Realforce91UBK-S 静音キーボード 静電容量無接点方式 変荷重 ブラック NG01BS
東プレ Realforce91UBK-S 静音キーボード 静電容量無接点方式 変荷重 ブラック NG01BS (JUGEMレビュー »)

テンキーレス、静音のRealForce91UBK-S。スコスコ感がたまらなく気持ちいいです。家と会社で2台持ってます。

recommend

recommend

プログラミング.NET Framework 第4版 (プログラミングシリーズ)
プログラミング.NET Framework 第4版 (プログラミングシリーズ) (JUGEMレビュー »)
Jeffrey Richter
発売予定美 2013年10月10日。.NET Frameworkとお付き合いする人のバイブルですね。

recommend

recommend

キャット・シッターの君に。
キャット・シッターの君に。 (JUGEMレビュー »)
喜多嶋 隆
私のイラストレータデビュー本です。

recommend

Essential .NET ― 共通言語ランタイムの本質
Essential .NET ― 共通言語ランタイムの本質 (JUGEMレビュー »)
ドン・ボックス,クリス・セルズ,Don Box,Chris Sells,吉松 史彰

links

profile

search this site.

others

mobile

qrcode

powered

無料ブログ作成サービス JUGEM