具体的なコーディング方法についてよくある質問

  • SpreadsheetGear で提供される機能 (API) をプログラム内で呼び出す具体的なコーディング方法について、よく寄せられる質問です。

セル範囲

              // Excel ファイルからワークブックを生成
              IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook("myfile.xls");

              // 既存の定義名から DataSet を取得する
              DataSet dataSet = workbook.GetDataSet("mydefinedname",
                  SpreadsheetGear.Data.GetDataFlags.FormattedText);

下記のリンクでサンプルを紹介しています。

 Razor Pages サンプル

下記のリンクでサンプルを紹介しています。

 API サンプル

              // ワークブックを生成して、最初のシートを選択する
              SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
              SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

              // セル A1 にハイパーリンクを追加する
              worksheet.Hyperlinks.Add(worksheet.Cells["A1"],
                  @"https://www.spreadsheetgear.com",
                  null, "My Screen Tip", "My Hyperlink Text");

注意: ハイパーリンクは Web ページ、メール アドレス、ワークブック ファイル、または現在のワークブック内のセル範囲にリンクを設定できます。また、ハイパーリンクは HYPERLINK 関数でも生成できます。

 API サンプル

              // ワークブックの生成
              SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();

              // 最初のワークシートを取得、名前を付ける
              SpreadsheetGear.IWorksheet mySheet = workbook.Worksheets[0];
              mySheet.Name = "MySheet";

              // セル A1 を参照する定義名を生成する
              SpreadsheetGear.INames names = workbook.Names;
              names.Add("MyName", "=MySheet!$A$1");

              // 定義名から IRange を取得する
              SpreadsheetGear.IRange cell = names["MyName"].RefersToRange;

 API サンプル

              // ワークブックの生成と最初のワークシートの取得
              SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
              SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

              // セル範囲を結合
              worksheet.Cells["A1:D2"].Merge();

 API サンプル

SpreadsheetGear では、HTML の HEX カラーを直接指定することができません。そこで、.NET 自身が提供する System.Drawing.ColorTranslator.FromHtml() メソッドにより、それらの色をいったん System.Drawing.Color に変換してから、SpreadsheetGear カラーに再変換します。

              /// 
              /// HTML の HEX ベースのカラー文字列を SpreadsheetGear カラーに変換します。
              /// 注意: サンプルを実装する際には、ColorTranslator.FromHtml() に
              ///       無効なカラーが引き渡されることを想定し、例外がスローされるかどうかを
              ///       チェックすることが推奨されます。
              /// 
              public SpreadsheetGear.Color ConvertColorHexToSG(string hexColor)
              {
                  // HEX カラー文字列を .NET の System.Drawing.Color に変換
                  System.Drawing.Color systemColor = System.Drawing.ColorTranslator.FromHtml(hexColor);

                  // 変換したシステム カラーをさらに SpreadsheetGear カラーに変換
                  SpreadsheetGear.Color sgColor = SpreadsheetGear.Drawing.Color.GetSpreadsheetGearColor(systemColor);

                  return sgColor;
              }

              ...

              worksheet.Cells["A1"].Interior.Color = ConvertColorHexToSG("#ff0000");

 API サンプル

              // ワークブックの生成と最初のワークシートの取得
              SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
              SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

              // セルの底辺に罫線を設定
              SpreadsheetGear.IRange range = worksheet.Cells["A1:D1"];
              SpreadsheetGear.IBorder border = range.Borders[SpreadsheetGear.BordersIndex.EdgeBottom];
              border.LineStyle = SpreadsheetGear.LineStyle.Continous;
              border.Color = SpreadsheetGear.Colors.Blue;
              border.Weight = SpreadsheetGear.BorderWeight.Thick;

 API サンプル

              // ワークブックの生成と最初のワークシートの取得
              SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
              SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

              // 10 行 x 2 列の値の配列を生成する
              object[,] values = new object[10, 2];
              for (int i = 0; i < 10; i++)
              {
                  values[i, 0] = "Row=" + i + " Col=0";
                  values[i, 1] = "Row=" + i + " Col=1";
              }

              // 値をワークシートにセット
              // 注意: セル範囲 "A1:B10" は、配列のサイズと一致させる必要があります。
              worksheet.Cells["A1:B10"].Value = values;

              // ワークシートから値を取得
              object[,] retVals = (object[,])worksheet.Cells["A1:B10"].Value;

 API サンプル

SpreadsheetGear は、基本的にセルへの入力値を適切データ型として解析しようとします。たとえば「001234」という値は、数値として判別し、先頭のゼロを取り除きます。そこで、下記の方法により、セルの値を強制的に文字列として扱うことができます。

              // あらかじめ対象セルの IRange.NumberFormat に "@" を設定しておく
              worksheet.Cells["A1"].NumberFormat = "@";
              worksheet.Cells["A1"].Value = "00123";
              Debug.Assert(worksheet.Cells["A1"].ValueType == SpreadsheetGear.ValueType.Text);

              // 入力値の先頭に単一引用符 (') を付与して、セルに値を指定する
              worksheet.Cells["A1"].Value = "'" + "00123";
              Debug.Assert(worksheet.Cells["A1"].ValueType == SpreadsheetGear.ValueType.Text);

セルの列幅は「文字単位」として相対的な単位で設定されるため、ワークブックのデフォルト フォント (通常は、游ゴシック 11 pt) を使用する場合、「1 文字」は、数字の「0」の文字幅とほぼ同じになります。

下記のコード例は、計測単位を非相対単位となるポイントで列幅を指定する方法です。斜度 (Slope) と斜度切片 (Slope Intercept) の数式を使用して、特定のポイント単位の値を文字単位の値に変換します。ただし、極端に小さい列幅を指定する場合、セル内のスペースの都合で設定は反映されず、1 文字の最小幅となります。また、列幅は最大で 255 文字ですので、これを超える値は指定できません。

              public static class IRangeExtensionMethods
              {
                  // 斜度 (Slope) 数式: (y2 - y1) / (x2 - x1)
                  //   x values == 文字単位
                  //   y values == ポイント
                  // 斜度切片 (Slope Intercept) formula: y = mx + b
                  //   x == x 座標
                  //   y == y 座標
                  //   m == 斜度 (slope)
                  //   b == y 切片 (intercept)
                  public static void SetColumnWidthInPoints(this IRange cell, double widthInPoints)
                  {
                      // x、y 変数を設定して、2 つのポイント単位の x のライン斜度を算出
                      double x1 = 10, x2 = 1;
                      double y1, y2;

                      // x1、x2 (文字単位) の入力値から y1、y2 (ポイント単位) を算出
                      cell.ColumnWidth = x1;
                      y1 = cell.Width;
                      cell.ColumnWidth = x2;
                      y2 = cell.Width;

                      // ライン (m) の斜度を算出
                      double m = (y2 - y1) / (x2 - x1);

                      // 切片(b) を算出
                      //   (y = mx + b) => (b = y - mx)
                      double b = y1 - m * x1;

                      // 算出した m と b により、新しい入力値の "y" (ポイント単位) から "x" (文字単位) を算出。
                      // 新しい "y" として "widthInPoints" パラメーターを使用する。
                      //   (y = mx + b) => (x = (y - b) / m)
                      double y3 = widthInPoints;
                      double x3 = (y3 - b) / m;

                      // 列幅の境界を min / max ColumnWidth で算出
                      x3 = Math.Min(255.0, Math.Max(1.0, x3));

                      // ポイントベースの入力値から文字単位のセル幅を設定
                      cell.ColumnWidth = x3;

                      // 設定値の確認
                      Console.WriteLine($"Inputted Points: {widthInPoints}, Actual Points: {cell.Width}");
                  }
              }

IWorksheet.UsedRange プロパティは、ワークシート内で使用されている範囲を返します。「使用されている」セルには、値が未設定で書式設定されただけのセルも含まれます。たとえば、セル A1:D10 にのみデータが入力されていても、空白セル Z100 にデフォルトとは異なる数値書式 (NumberFormat) が設定されている場合h、ワークシートの使用範囲 (UsedRange) は、A1:D1 ではなく A1:Z100 となります。

下記の IWorksheet の拡張メソッドは、値が入力されているセルのみを UsedRange として検出する方法です。

              public static class IWorksheetExtensionMethods
              {
                  public static IRange GetUsedRange(this IWorksheet worksheet, bool ignoreEmptyCells)
                  {
                      IRange usedRange = worksheet.UsedRange;
                      if (!ignoreEmptyCells)
                          return usedRange;

                      // データを含む最終セルの行を検出
                      IRange foundCell = usedRange.Find("*", usedRange[0, 0], FindLookIn.Formulas,
                          LookAt.Part, SearchOrder.ByRows, SearchDirection.Previous, false);
                      int lastRow = foundCell?.Row ?? 0;

                      // データを含む最終セルの列を検出
                      foundCell = usedRange.Find("*", usedRange[0, 0], FindLookIn.Formulas,
                          LookAt.Part, SearchOrder.ByColumns, SearchDirection.Previous, false);
                      int lastCol = foundCell?.Column ?? 0;

                      // 空白行や列を含まないセルの使用範囲を返す
                      return worksheet.Cells[worksheet.UsedRange.Row, worksheet.UsedRange.Column, lastRow, lastCol];
                  }
              }

上記の処理が正常に動作しない特殊な場合も存在します。ほとんどの場合、このルーチンは、データを含む非表示行、列も、使用範囲として判定します。例外としてワークシート上でオートフィルターが有効な場合、ワークシートは特別な「モード」に切り替わるため IRange.Find(...) でこれらのセルを検出することができません。そのため、オートフィルターが有効な状態 (IWorksheet.AutoFilterMode が true の状態) で、値の含まれるセルの最終行がフィルタリングで除外されている場合は、正確な UsedRange が返されません。

IRange.Find(...) メソッドでは、現在のセル位置から、検索条件に一致する次のセルまたは前のセルを返します。下記のルーチンにより、特定のセル範囲 (IRange) に含まれるすべてのセルのリストを返します。

              public List FindAll(string searchString, IRange searchRange)
              {
                  // FindAll ルーチンを補助する検索結果セルのリストとローカル変数のコレクション定義
                  List ranges = new List();
                  IRange firstRange = null;
                  IRange nextRange = null;

                  // 検索文字列 (searchString) の最初のインスタンスを検出
                  nextRange = searchRange.Find(searchString, null, FindLookIn.Values, LookAt.Part,
                      SearchOrder.ByRows, SearchDirection.Next, false);
                  if (nextRange != null)
                  {
                      // 他のすべてのインスタンスを検出する前に、検索開始位置を格納
                      firstRange = nextRange;
                      while (true)
                      {
                          // 見つかったセルのリストに追加
                          ranges.Add(nextRange);
                          // 検索文字列 (searchString) の他のインスタンスを検索
                          nextRange = searchRange.Find(searchString, nextRange, FindLookIn.Values,
                              LookAt.Part, SearchOrder.ByRows, SearchDirection.Next, false);
                          // 見つかったインスタンスが最初のインスタンスに戻ったらループを抜ける
                          if (nextRange.Address == firstRange.Address)
                              break;
                      }
                  }
                  return ranges;
              }

ページトップへ

ワークブック

              public static IWorkbook OpenWorkbook(System.IO.Stream stream)
              {
                  // ストリームからワークブックを読み込む
                  return SpreadsheetGear.Factory.GetWorkbookSet()
                      .Workbooks.OpenFromStream(stream);
              }

              public static void SaveWorkbook(System.IO.Stream stream)
              {
                  // ワークブックを生成してストリームに書き込む
                  IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
                  workbook.SaveToStream(
                      stream, SpreadsheetGear.FileFormat.Excel8);
              }

 API サンプル

              // ワークブックを生成する
              SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();

              // 最初のデフォルトのワークシートを取得、名前を設定し、数式を追加する
              SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0];
              worksheet.Name = "MyFirstSheet";
              worksheet.Cells["A1"].Value = 123.456;

              // 2 番目のワークシートを追加し、名前を設定し、数式を追加する
              worksheet = workbook.Worksheets.Add();
              worksheet.Name = "MySecondSheet";
              worksheet.Cells["A1"].Formula = "=MyFirstSheet!A1 * 2";

 API サンプル

              // ワークブックの生成と最初のワークシートの PageSetup を取得
              SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
              SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
              SpreadsheetGear.IPageSetup pageSetup = worksheet.PageSetup;

              // 印刷範囲の設定
              pageSetup.PrintArea = "Sheet1!$B$2:$F$20";

              // 印刷タイトルの設定
              pageSetup.PrintTitleRows = "Sheet1!$1:$1";
              pageSetup.PrintTitleColumns = "Sheet1!$A:$A";

 API サンプル

ページトップへ

数式と演算

下記のリンクでサンプルを紹介しています。

 Razor Pages サンプル

下記のリンクでサンプルを紹介しています。

 Razor Pages サンプル

              // ワークブックの生成と最初のワークシートの取得
              SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
              SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

              // IRange の取得と乱数の設定
              SpreadsheetGear.IRange rangeValues = worksheet.Range["A1:A10"];
              rangeValues.Formula = "=RAND() * 10000";

              // IRange の取得と、値の合計を求める数式の追加
              SpreadsheetGear.IRange rangeFormula = worksheet.Range["A11"];
              rangeFormula.Formula = "=SUM(" + rangeValues.Address + ")";

              // 演算結果を出力
              System.Console.WriteLine("Sum = " + rangeFormula.Text);

ワークブックをまたいだセル参照は、それらのワークブックを IWorkbookSet の同一インスタンスに読み込んだタイミングで自動的に更新されます。

              // ワークブック セットを生成
              IWorkbookSet wbs = Factory.GetWorkbookSet();

              // 依存関係にあるすべてのワークブックを同一のワークブック セット内に読み込む
              IWorkbook workbook1 = wbs.Workbooks.Open(@"C:\path\to\workbook1.xlsx");
              IWorkbook workbook2 = wbs.Workbooks.Open(@"C:\path\to\workbook2.xlsx");

              // ワークブック間の参照が検出され、数式や演算結果も自動的に更新される
              // 以降は任意の処理へ

ページトップへ

表示、UI コントロール

下記の GitHub の SpreadsheetGearExplorerSamples リポジトリで、Windows フォームと WPF のサンプルを確認できます。
https://github.com/SpreadsheetGear/SpreadsheetGearExplorerSamples

また、SpreadsheetGear for .NET Framework 製品では、製品とともにインストールされる SpreadsheetGear Explorer を起動すると Windows フォーム アプリケーションのサンプルを確認できます。

Windows フォームの WorkbookView コントロールでは、ContextMenuStrip プロパティに null を設定したり、他の ContextMenuStrip に差し替えることができます。

              // コンテキスト メニューを使用不可にする
              workbookView1.ContextMenuStrip = null;

              // コンテキスト メニューを差し替える
              workbookView1.ContextMenuStrip = myContextMenuStrip;

WPF の場合は、下記のように指定します。

              // コンテキスト メニューを使用不可にする
              workbookView1.ContextMenu = null;

              // コンテキスト メニューを差し替える
              workbookView1.ContextMenu = myContextMenu;

各種 Explorer ダイアログは、ユーザーが WorkbookView 上で変更したいワークシートやセルを選択しながら操作できるように「モードレス」モードで動作するように設計されています。モードレスでダイアログを使用する場合は、シンプルに Explorer をインスタンス化し、Show() メソッドを呼び出す方法になります。

              // モードレスでダイアログを起動
              var rangeExplorer = new SpreadsheetGear.Windows.Forms.RangeExplorer(workbookView.ActiveWorkbookSet);
              rangeExplorer.Show(this);

ダイアログを閉じるまで呼び出し元の親ウィンドウとの対話式操作を禁止する「モーダル」ダイアログもサポートされます。ただし、ダイアログを起動する前に、WorkbookView 上に設定されたすべてのロックは、WorkbookView.UnwindLock() により Unwind (アンワインド: 巻き戻す) 必要があります。

              // この処理の前に呼び出されている可能性のある GetLocks 設定をいったん巻き戻す (Unwind する)。
              // ダイアログを閉じた際に巻き戻したロックを元に戻せる ((Rewind) ように、ロックの数を変数に退避させる。
              int rewindCount = workbookView.UnwindLock();
              try
              {
                  // ダイアログをモーダルで起動する
                  var rangeExplorer = new SpreadsheetGear.Windows.Forms.RangeExplorer(workbookView.ActiveWorkbookSet);
                  rangeExplorer.Show(this);
              }
              finally
              {
                  // ダイアログを開く前に GetLocks で設定されていたロック (この処理の先頭で Unwind されたロック) を再度ロック (Rewind) する
                  workbookView.RewindLock(rewindCount);
              }

ページトップへ

グラフ、シェイプ

下記のリンクでサンプルを紹介しています。

 API サンプル


ページトップへ