カメラシェイプ、名前が示すように、スプレッドシート内の参照領域の鏡像です。参照領域の変更は画像にも反映され、動的な画像です。カメラシェイプを作成する方法について、カメラシェイプ のトピックを参照してください。

さらに、Spread for WinFormsは、高度なシェイプエンジンの全機能を継承する拡張カメラシェイプを提供します。例えば、拡張カメラシェイプの移動、サイズ変更、回転だけではなく、Excel I/Oも可能です。また、カメラシェイプを他の図形とグループ・グループ解除などしたり、シート間にコピー・ペーストしたりすることもできます。

拡張カメラシェイプはセル範囲をシェイプに繋がって、指定された範囲の中身を表示します。カメラシェイプを選択すると、数式バーにソースのセル範囲が表示されます。数式バーに表示されるセル範囲や定義名を編集することでカメラシェイプのソースデータを動的に変更できます。

カメラシェイプの追加

拡張カメラシェイプを追加するには、IPicture.Pasteメソッドを使用します。シートでの対象セルおよび貼付けた画像をソースにリンクするかどうかをパラメータとして受け入れます。Pasteメソッドを使用するには、RichClipboardプロパティがtrueである必要があります。

IShape.Formulaプロパティでシェイプ数式を設定することで、画像をカメラシェイプに変更することも可能です。

新規のカメラシェイプの実装は、EnhancedShapeEngineプロパティがtrueの場合のみ効果します。

// ワークブックとアクティブシートを取得します。
 GrapeCity.Spreadsheet.IWorkbook TestWorkBook = fpSpread1.AsWorkbook();
 GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
// ワークシートに値を設定します。
 TestActiveSheet.Cells["A1"].Value = 5;
 TestActiveSheet.Cells["B1"].Formula = "=SUM(A1,5)";
 TestActiveSheet.Cells["A2"].Value = "Enhanced";
 TestActiveSheet.Cells["B2"].Value = "Camera";
 TestActiveSheet.Cells["C2"].Value = "Shape";
fpSpread1.Features.EnhancedShapeEngine = true;
 fpSpread1.Features.RichClipboard = true;
// IPictures.Pasteメソッドを使用してカメラシェイプを追加します。
TestActiveSheet.Cells["A1:E7"].Copy(true);      // bool showUI = trueでコピーする必要があります
 TestActiveSheet.Pictures.Paste("D11", true);    // CameraShapeはSheet1!$A$1:$E$7を参照します
 TestActiveSheet.Pictures.Paste("F11", false);   // A1:E7のコンテンツをスナップする画像
// 自動サイズ動作を行わずに画像のサイズを設定します。
 fpSpread1.Features.EnhancedShapeEngine = true;
 int rowHeight = TestActiveSheet.Rows[0].RowHeight;
 int colWidth = TestActiveSheet.Columns[0].ColumnWidth;
 IShape picture = TestActiveSheet.Shapes.AddPictureToCell(null, true, true, 1, 3, colWidth * 5, rowHeight * 7);
 picture.Formula = "A1:E7";
// Pictures.Paste によって追加して、自動サイズ動作を有効にします。
 fpSpread1.Features.EnhancedShapeEngine = true;
 fpSpread1.Features.RichClipboard = true;
 TestActiveSheet.Cells["A1:E5"].Copy(true);
 IPicture picture = TestActiveSheet.Pictures.Paste("D4", true);
 picture.Formula = "A1:E7";

' ワークブックとアクティブシートを取得します。
 Dim TestWorkBook As GrapeCity.Spreadsheet.IWorkbook = fpSpread1.AsWorkbook()
 Dim TestActiveSheet As GrapeCity.Spreadsheet.IWorksheet = fpSpread1.AsWorkbook().ActiveSheet
' ワークシートに値を設定します。
 TestActiveSheet.Cells("A1").Value = 5
 TestActiveSheet.Cells("B1").Formula = "=SUM(A1,5)"
 TestActiveSheet.Cells("A2").Value = "Enhanced"
 TestActiveSheet.Cells("B2").Value = "Camera"
 TestActiveSheet.Cells("C2").Value = "Shape"
fpSpread1.Features.EnhancedShapeEngine = True
 fpSpread1.Features.RichClipboard = True
' IPictures.Pasteメソッドを使用してカメラシェイプを追加します。
TestActiveSheet.Cells("A1:E7").Copy(True)      ' bool showUI = trueでコピーする必要があります
 TestActiveSheet.Pictures.Paste("D11", True)    ' CameraShapeはSheet1!$A$1:$E$7を参照します
 TestActiveSheet.Pictures.Paste("F11", False)   ' A1:E7のコンテンツをスナップする画像
' 自動サイズ動作を行わずに画像のサイズを設定します。
 fpSpread1.Features.EnhancedShapeEngine = True
 Dim rowHeight As Integer = TestActiveSheet.Rows(0).RowHeight
 Dim colWidth As Integer = TestActiveSheet.Columns(0).ColumnWidth
 Dim picture As IShape = TestActiveSheet.Shapes.AddPictureToCell(Nothing, True, True, 1, 3, colWidth * 5, rowHeight * 7)
 picture.Formula = "A1:E7"
' Pictures.Paste によって追加して、自動サイズ動作を有効にします。
 fpSpread1.Features.EnhancedShapeEngine = True
 fpSpread1.Features.RichClipboard = True
 TestActiveSheet.Cells("A1:E5").Copy(True)
 Dim picture As IPicture = TestActiveSheet.Pictures.Paste("D4", True)
 picture.Formula = "A1:E7"

塗りつぶし設定は、拡張カメラシェイプには影響しません。

カメラシェイプでの再帰的なペイント

拡張カメラシェイプは、別のカメラシェイプ内に表示することができます。Excelとは異なりSPREAD for Windows Formsのカメラシェイプは、再帰的なペイントの場合、自体または別のカメラシェイプ内に表示されません。 

次の画像では、動作の違いを観察できます。

• 再帰的なペイントの場合、カメラシェイプは自体内に表示されません。

  

  コードを表示

  C#

  コードのコピー

  // 再帰的なペイントを作成するカメラシェイプ。 // 自動サイズ変更動作なし。 fpSpread1.Features.EnhancedShapeEngine = true; GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet; GrapeCity.Spreadsheet.Color greenColor = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Green); TestActiveSheet.Cells["A1:D6"].Interior.Color = greenColor; TestActiveSheet.Cells[0, 0, 1, 1].Value = 1; int rowHeight = TestActiveSheet.Rows[0].RowHeight; int colWidth = TestActiveSheet.Columns[0].ColumnWidth; IShape picture = TestActiveSheet.Shapes.AddPictureToCell(null, true, true, 5, 2, colWidth * 5, rowHeight * 7); picture.Formula = "Sheet1!$A$1:$E$10"; // 自動サイズ動作あり。 fpSpread1.Features.EnhancedShapeEngine = true; fpSpread1.Features.RichClipboard = true; GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet; GrapeCity.Spreadsheet.Color greenColor = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Green); TestActiveSheet.Cells["A1:D6"].Interior.Color = greenColor; TestActiveSheet.Cells[0, 0, 1, 1].Value = 1; TestActiveSheet.Cells["A1:E5"].Copy(true); IPicture picture = TestActiveSheet.Pictures.Paste("D4", true); picture.Formula = "Sheet1!$A$1:$E$10";

  VB

  コードのコピー

  ' 再帰的なペイントを作成するカメラシェイプ。 ' 自動サイズ変更動作なし。 fpSpread1.Features.EnhancedShapeEngine = True Dim TestActiveSheet As GrapeCity.Spreadsheet.IWorksheet = fpSpread1.AsWorkbook().ActiveSheet Dim greenColor As GrapeCity.Spreadsheet.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Green) TestActiveSheet.Cells("A1:D6").Interior.Color = greenColor TestActiveSheet.Cells(0, 0, 1, 1).Value = 1 Dim rowHeight As Integer = TestActiveSheet.Rows(0).RowHeight Dim colWidth As Integer = TestActiveSheet.Columns(0).ColumnWidth Dim picture As IShape = TestActiveSheet.Shapes.AddPictureToCell(Nothing, True, True, 5, 2, colWidth * 5, rowHeight * 7) picture.Formula = "Sheet1!$A$1:$E$10" ' 自動サイズ動作あり。 fpSpread1.Features.EnhancedShapeEngine = True fpSpread1.Features.RichClipboard = True Dim TestActiveSheet As GrapeCity.Spreadsheet.IWorksheet = fpSpread1.AsWorkbook().ActiveSheet Dim greenColor As GrapeCity.Spreadsheet.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Green) TestActiveSheet.Cells("A1:D6").Interior.Color = greenColor TestActiveSheet.Cells(0, 0, 1, 1).Value = 1 TestActiveSheet.Cells("A1:E5").Copy(True) Dim picture As IPicture = TestActiveSheet.Pictures.Paste("D4", True) picture.Formula = "Sheet1!$A$1:$E$10"

            

• 再帰的なペイントの場合、カメラシェイプは別のカメラシェイプ内に表示されません。

  

  コードを表示

  C#

  コードのコピー

  // 別のカメラシェイプの上に再帰的なペイントを作成するカメラシェイプ。 // 自動サイズ調整動作なし。 fpSpread1.Features.EnhancedShapeEngine = true; GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet; GrapeCity.Spreadsheet.Color yellowColor = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Yellow); GrapeCity.Spreadsheet.Color redColor = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Red); TestActiveSheet.Cells[0, 0, 1, 1].Value = 1; TestActiveSheet.Cells["G1:G10"].Interior.Color = redColor; TestActiveSheet.Cells["F1:F10"].Interior.Color = yellowColor; int rowHeight = TestActiveSheet.Rows[0].RowHeight; int colWidth = TestActiveSheet.Columns[0].ColumnWidth; IShape picture = TestActiveSheet.Shapes.AddPictureToCell(null, true, true, 1, 6, colWidth * 5, rowHeight * 7); picture.Formula = "Sheet1!$A$1:$D$10"; IShape picture1 = TestActiveSheet.Shapes.AddPictureToCell(null, true, true, 0, 2, colWidth * 2, rowHeight * 3); picture1.Formula = "Sheet1!$F$1:$G$10"; TestActiveSheet.Shapes.Range(new int[] { 0, 0 }).Line.ForeColor.ARGB = System.Drawing.Color.Black.ToArgb(); // 自動サイズ動作あり。 fpSpread1.Features.EnhancedShapeEngine = true; fpSpread1.Features.RichClipboard = true; GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet; GrapeCity.Spreadsheet.Color yellowColor = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Yellow);GrapeCity.Spreadsheet.Color redColor = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Red); TestActiveSheet.Cells[0, 0, 1, 1].Value = 1; TestActiveSheet.Cells["G1:G10"].Interior.Color = redColor; TestActiveSheet.Cells["F1:F10"].Interior.Color = yellowColor; TestActiveSheet.Cells["A1:E5"].Copy(true); IPicture picture = TestActiveSheet.Pictures.Paste("D4", true); picture.Formula = "Sheet1!$A$1:$D$10"; IPicture picture1 = TestActiveSheet.Pictures.Paste("C1", true); picture1.Formula = "Sheet1!$F$1:$G$10"; TestActiveSheet.Shapes.Range(new int[] { 0, 0 }).Line.ForeColor.ARGB = System.Drawing.Color.Black.ToArgb();

  VB

  コードのコピー

  ' 別のカメラシェイプの上に再帰的なペイントを作成するカメラシェイプ。 ' 自動サイズ調整動作なし。 fpSpread1.Features.EnhancedShapeEngine = True Dim TestActiveSheet As GrapeCity.Spreadsheet.IWorksheet = fpSpread1.AsWorkbook().ActiveSheet Dim yellowColor As GrapeCity.Spreadsheet.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Yellow) Dim redColor As GrapeCity.Spreadsheet.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Red) TestActiveSheet.Cells(0, 0, 1, 1).Value = 1 TestActiveSheet.Cells("G1:G10").Interior.Color = redColor TestActiveSheet.Cells("F1:F10").Interior.Color = yellowColor Dim rowHeight As Integer = TestActiveSheet.Rows(0).RowHeight Dim colWidth As Integer = TestActiveSheet.Columns(0).ColumnWidth Dim picture As IShape = TestActiveSheet.Shapes.AddPictureToCell(Nothing, True, True, 1, 6, colWidth * 5, rowHeight * 7) picture.Formula = "Sheet1!$A$1:$D$10" Dim picture1 As IShape = TestActiveSheet.Shapes.AddPictureToCell(Nothing, True, True, 0, 2, colWidth * 2, rowHeight * 3) picture1.Formula = "Sheet1!$F$1:$G$10" TestActiveSheet.Shapes.Range((New Integer() {0, 0})).Line.ForeColor.ARGB = Drawing.Color.Black.ToArgb() ' 自動サイズ動作あり。 fpSpread1.Features.EnhancedShapeEngine = True fpSpread1.Features.RichClipboard = True Dim TestActiveSheet As GrapeCity.Spreadsheet.IWorksheet = fpSpread1.AsWorkbook().ActiveSheet Dim yellowColor As GrapeCity.Spreadsheet.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Yellow) Dim redColor As GrapeCity.Spreadsheet.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Red) TestActiveSheet.Cells(0, 0, 1, 1).Value = 1 TestActiveSheet.Cells("G1:G10").Interior.Color = redColor TestActiveSheet.Cells("F1:F10").Interior.Color = yellowColor TestActiveSheet.Cells("A1:E5").Copy(True) Dim picture As IPicture = TestActiveSheet.Pictures.Paste("D4", True) picture.Formula = "Sheet1!$A$1:$D$10" Dim picture1 As IPicture = TestActiveSheet.Pictures.Paste("C1", True) picture1.Formula = "Sheet1!$F$1:$G$10" TestActiveSheet.Shapes.Range((New Integer() {0, 0})).Line.ForeColor.ARGB = Drawing.Color.Black.ToArgb()

• 再帰的なペイントを作成しない場合、カメラシェイプは別のカメラシェイプ内に表示されます。

  

  コードを表示

  C#

  コードのコピー

  // カメラシェイプは、再帰的なペイントを作成せずに、別のカメラシェイプの内に表示されます。 // 自動サイズ調整動作なし。 fpSpread1.Features.EnhancedShapeEngine = true; GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet; GrapeCity.Spreadsheet.Color greenColor = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Green); TestActiveSheet.Cells["A1:D6"].Interior.Color = greenColor; TestActiveSheet.Cells[0, 0, 1, 1].Value = 1; int rowHeight = TestActiveSheet.Rows[0].RowHeight; int colWidth = TestActiveSheet.Columns[0].ColumnWidth; IShape picture = TestActiveSheet.Shapes.AddPictureToCell(null, true, true, 6, 2, colWidth * 5, rowHeight * 7); picture.Formula = "Sheet1!$A$1:$D$3"; IShape picture1 = TestActiveSheet.Shapes.AddPictureToCell(null, true, true, 6, 7, colWidth * 3, rowHeight * 3); picture1.Formula = "Sheet1!$A$1:$E$10"; // 自動サイズ動作あり。 fpSpread1.Features.EnhancedShapeEngine = true; fpSpread1.Features.RichClipboard = true; GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet; GrapeCity.Spreadsheet.Color greenColor = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Green); TestActiveSheet.Cells["A1:D6"].Interior.Color = greenColor; TestActiveSheet.Cells[0, 0, 1, 1].Value = 1; TestActiveSheet.Cells["A1:E5"].Copy(true); IPicture picture = TestActiveSheet.Pictures.Paste("D4", true); picture.Formula = "Sheet1!$A$1:$D$3"; IPicture picture1 = TestActiveSheet.Pictures.Paste("H7", true); picture1.Formula = "Sheet1!$A$1:$E$10";

  VB

  コードのコピー

  ' カメラシェイプは、再帰的なペイントを作成せずに、別のカメラシェイプの内に表示されます。 ' 自動サイズ調整動作なし。 fpSpread1.Features.EnhancedShapeEngine = True Dim TestActiveSheet As GrapeCity.Spreadsheet.IWorksheet = fpSpread1.AsWorkbook().ActiveSheet Dim greenColor As GrapeCity.Spreadsheet.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Green) TestActiveSheet.Cells("A1:D6").Interior.Color = greenColor TestActiveSheet.Cells(0, 0, 1, 1).Value = 1 Dim rowHeight As Integer = TestActiveSheet.Rows(0).RowHeight Dim colWidth As Integer = TestActiveSheet.Columns(0).ColumnWidth Dim picture As IShape = TestActiveSheet.Shapes.AddPictureToCell(Nothing, True, True, 6, 2, colWidth * 5, rowHeight * 7) picture.Formula = "Sheet1!$A$1:$D$3" Dim picture1 As IShape = TestActiveSheet.Shapes.AddPictureToCell(Nothing, True, True, 6, 7, colWidth * 3, rowHeight * 3) picture1.Formula = "Sheet1!$A$1:$E$10" ' 自動サイズ動作あり。 fpSpread1.Features.EnhancedShapeEngine = True fpSpread1.Features.RichClipboard = True Dim TestActiveSheet As GrapeCity.Spreadsheet.IWorksheet = fpSpread1.AsWorkbook().ActiveSheet Dim greenColor As GrapeCity.Spreadsheet.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Green) TestActiveSheet.Cells("A1:D6").Interior.Color = greenColor TestActiveSheet.Cells(0, 0, 1, 1).Value = 1 TestActiveSheet.Cells("A1:E5").Copy(True) Dim picture As IPicture = TestActiveSheet.Pictures.Paste("D4", True) picture.Formula = "Sheet1!$A$1:$D$3" Dim picture1 As IPicture = TestActiveSheet.Pictures.Paste("H7", True) picture1.Formula = "Sheet1!$A$1:$E$10"

カメラシェイプおよびExcelIO

EnhancedShapeEngineプロパティがtrueの場合、拡張カメラシェイプをインポートできます。それ以外の場合、古いカメラシェイプがインポートされます。

カメラシェイプをExcelにエクスポートする場合：

• ワークブック内に拡張カメラシェイプのみが存在する場合、正常にエクスポートされます。
• ワークブック内に古いカメラシェイプのみが存在する場合、EnhancedShapeEngineプロパティと関係なくシェイプがエクスポートされます。
• ワークブック内に拡張カメラシェイプと古いカメラシェイプの両方が存在し、EnhancedShapeEngineプロパティがtrueに設定されている場合、
  • 拡張カメラシェイプがエクスポートされます。
  • Exchangeableフラッグを使用すると、古いカメラシェイプがエクスポートされます。それ以外の場合、通常の画像としてエクスポートされます。

使用例

例として、スーパーマーケット全体のさまざまな製品の販売データを維持して販売傾向を分析するシナリオを考えてみます。果物、野菜、パン屋、肉などのさまざまな製品カテゴリのデータは、スプレッドシートでワークシートごとにで管理されます。

各製品カテゴリにわたる売り上げをカメラシェイプとして統合された「ダッシュボード」ワークシートに表示し、要約された月間売上データを表示します。売上データに発生した変更が「ダッシュボード」シートにも反映されます。

// シート数を設定します。
fpSpread1.Sheets.Count = 5;

fpSpread1.Features.EnhancedShapeEngine = true;
fpSpread1.Features.RichClipboard = true;

// シートを取得します。
var sheetDashboard = fpSpread1.Sheets[0];
var sheet1 = fpSpread1.Sheets[1];
var sheet2 = fpSpread1.Sheets[2];
var sheet3 = fpSpread1.Sheets[3];
var sheet4 = fpSpread1.Sheets[4];
var worksheet0 = sheetDashboard.AsWorksheet();
var worksheet1 = sheet1.AsWorksheet();
var worksheet2 = sheet2.AsWorksheet();
var worksheet3 = sheet3.AsWorksheet();
var worksheet4 = sheet4.AsWorksheet();

// シート名を設定します。
sheetDashboard.SheetName = "ダッシュボード";
sheet1.SheetName = "果物";
sheet2.SheetName = "野菜";
sheet3.SheetName = "肉";
sheet4.SheetName = "パン屋";

// 列と行のヘッダを非表示にします
sheetDashboard.ColumnHeader.Visible = false;
sheetDashboard.RowHeader.Visible = false;

sheet1.ColumnHeader.Visible = false;
sheet1.RowHeader.Visible = false;

sheet2.ColumnHeader.Visible = false;
sheet2.RowHeader.Visible = false;

sheet3.ColumnHeader.Visible = false;
sheet3.RowHeader.Visible = false;

sheet4.ColumnHeader.Visible = false;
sheet4.RowHeader.Visible = false;

// グリッド線を非表示にします。
sheetDashboard.VerticalGridLine = new FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty);
sheetDashboard.HorizontalGridLine = new FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty);

sheet1.VerticalGridLine = new FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty);
sheet1.HorizontalGridLine = new FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty);

sheet2.VerticalGridLine = new FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty);
sheet2.HorizontalGridLine = new FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty);

sheet3.VerticalGridLine = new FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty);
sheet3.HorizontalGridLine = new FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty);

sheet4.VerticalGridLine = new FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty);
sheet4.HorizontalGridLine = new FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty);

// 列幅を設定します。
sheet1.Columns[0].Width = 100;
sheet1.Columns[1].Width = 140;
sheet1.Columns[2].Width = 200;
for (var i = 3; i < 8; i++)
    sheet1.Columns[i].Width = 80;

sheet2.Columns[0].Width = 100;
sheet2.Columns[1].Width = 140;
sheet2.Columns[2].Width = 200;
for (var i = 3; i < 8; i++)
    sheet2.Columns[i].Width = 80;

sheet3.Columns[0].Width = 100;
sheet3.Columns[1].Width = 140;
sheet3.Columns[2].Width = 200;
for (var i = 3; i < 8; i++)
    sheet3.Columns[i].Width = 80;

sheet4.Columns[0].Width = 100;
sheet4.Columns[1].Width = 140;
sheet4.Columns[2].Width = 200;
for (var i = 3; i < 8; i++)
    sheet4.Columns[i].Width = 80;

// 行の高さを設定します。
sheetDashboard.Rows[0].Height = 35;
sheetDashboard.Rows[1].Height = 5;
sheet1.Rows[0].Height = 35;
sheet2.Rows[0].Height = 35;
sheet3.Rows[0].Height = 35;
sheet4.Rows[0].Height = 35;
for (var i = 1; i < 8; i++)
{
    sheet1.Rows[i].Height = 30;
    sheet4.Rows[i].Height = 30;
}
for (var i = 1; i < 7; i++)
{
    sheet2.Rows[i].Height = 30;
}
for (var i = 1; i < 6; i++)
{
    sheet3.Rows[i].Height = 30;

}

// 異なるシートのデータ配列を作成して設定します。
worksheet1.SetValue(0, 0, new object[,]
{
    {"果物", "月間売上(%)", "傾向", "1週目（Kg）", "2週目（Kg）", "3週目（Kg）", "4週目（Kg）", "合計"},
    {"リンゴ",null,null,1031,927,1287,1484,null},
     {"アボカド",null,null,923,1468,791,981,null},
     {"バナナ",null,null,789,571,827,671,null},
     {"ブドウ",null,null,782,871,900,1100,null},
     {"マンゴー",null,null,829,450,837,671,null},
     {"ストロベリー",null,null,1500,1817,1981,1383,null},
     {"スイカ",null,null,980,1011,956,817,null}
});
worksheet2.SetValue(0, 0, new object[,]
{
    {"野菜", "月間売上(%)", "傾向", "1週目（Kg）", "2週目（Kg）", "3週目（Kg）", "4週目（Kg）", "合計"},
    {"にんじん",null,null,782,490,1012,659,null},
     {"タマネギ",null,null,1274,1290,721,671,null},
     {"ポテト",null,null,2001,2301,1987,2401,null},
     {"カボチャ",null,null,582,771,861,491,null},
     {"ほうれん草",null,null,302,233,251,292,null},
     {"トマト",null,null,938,1002,1139,1039,null}
});
worksheet3.SetValue(0, 0, new object[,]
{
    {"肉", "月間売上(%)", "傾向", "1週目（Kg）", "2週目（Kg）", "3週目（Kg）", "4週目（Kg）", "合計"},
    {"ビーフ",null,null,5711,4567,4519,5698,null},
     {"チキン",null,null,6261,5627,3987,4238,null},
     {"ラム",null,null,4789,4571,5827,4671,null},
     {"豚肉",null,null,6561,5871,5900,5119,null},
     {"マトン",null,null,5501,4817,5981,6383,null}
});

worksheet4.SetValue(0, 0, new object[,]
{
    {"パン屋", "月間売上(%)", "傾向", "1週目（Kg）", "2週目（Kg）", "3週目（Kg）", "4週目（Kg）", "合計"},
    {"パン",null,null,1031,927,1287,1484,null},
     {"ブラウニー",null,null,923,1468,791,981,null},
     {"ケーキ",null,null,789,571,827,671,null},
     {"クッキー",null,null,782,871,900,1100,null},
     {"ペストリー",null,null,1500,1817,1981,1383,null},
     {"パイ",null,null,1360,1328,1238,1238,null},
     {"タルト",null,null,1671,1782,2019,1983,null}
});

// sheet1、sheet2、sheet 3、sheet4のタイトル行のスタイルを設定します。
// 範囲を取得します。
var range = worksheet1.Cells["A1:H1"];
range.Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 10, Bold = true });
range.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
range.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center;

range = worksheet2.Cells["A1:H1"];
range.Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 10, Bold = true });
range.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
range.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center;

range = worksheet3.Cells["A1:H1"];
range.Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 10, Bold = true });
range.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
range.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center;

range = worksheet4.Cells["A1:H1"];
range.Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 10, Bold = true });
range.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
range.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center;

// sheet1、sheet2、sheet 3、sheet4の背景色を設定します
worksheet1.Cells["A1:H1"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFE8F6FA));
worksheet2.Cells["A1:H1"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFE8F6FA));
worksheet3.Cells["A1:H1"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFE8F6FA));
worksheet4.Cells["A1:H1"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFE8F6FA));

// sheet1の数式を設定します
sheet1.Cells[1, 7, 7, 7].Formula = "SUM(D2:G2)";
worksheet1.Cells[1, 2].Formula = "PIESPARKLINE(H2:H8,\"#919F81\",\"#D7913E\",\"#CEA722\", \"#D2DD3E\", \"#B58091\",\"#8974A9\",\"#728BAD\")";
sheet1.AddSpanCell(1, 2, 7, 1);
sheet1.AddSpanCell(0, 1, 1, 2);
sheet1.Cells[1, 1, 7, 1].Formula = "H2 / SUM(H2: H8)";
// sheet1の背景色を設定します
worksheet1.Cells[1, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFF919F81));
worksheet1.Cells[2, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFD7913E));
worksheet1.Cells[3, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFCEA722));
worksheet1.Cells[4, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFD2DD3E));
worksheet1.Cells[5, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFB58091));
worksheet1.Cells[6, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFF8974A9));
worksheet1.Cells[7, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFF728BAD));

// sheet2の数式を設定します
sheet2.Cells[1, 7, 6, 7].Formula = "SUM(D2:G2)";
worksheet2.Cells[1, 2].Formula = "PIESPARKLINE(H2:H8,\"#919F81\",\"#D7913E\",\"#CEA722\", \"#D2DD3E\", \"#B58091\",\"#8974A9\")";
sheet2.AddSpanCell(1, 2, 6, 1);
sheet2.AddSpanCell(0, 1, 1, 2);
sheet2.Cells[1, 1, 6, 1].Formula = "H2 / SUM(H2: H8)";
// sheet2の背景色を設定します
worksheet2.Cells[1, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFF919F81));
worksheet2.Cells[2, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFD7913E));
worksheet2.Cells[3, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFCEA722));
worksheet2.Cells[4, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFD2DD3E));
worksheet2.Cells[5, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFB58091));
worksheet2.Cells[6, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFF8974A9));

// sheet3の数式を設定します
sheet3.Cells[1, 7, 5, 7].Formula = "SUM(D2:G2)";
worksheet3.Cells[1, 2].Formula = "PIESPARKLINE(H2:H8,\"#919F81\",\"#D7913E\",\"#CEA722\", \"#D2DD3E\", \"#B58091\")";
sheet3.AddSpanCell(1, 2, 5, 1);
sheet3.AddSpanCell(0, 1, 1, 2);
sheet3.Cells[1, 1, 5, 1].Formula = "H2 / SUM(H2: H8)";
// sheet3の背景色を設定します
worksheet3.Cells[1, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFF919F81));
worksheet3.Cells[2, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFD7913E));
worksheet3.Cells[3, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFCEA722));
worksheet3.Cells[4, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFD2DD3E));
worksheet3.Cells[5, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFB58091));

// sheet4の数式を設定します
sheet4.Cells[1, 7, 7, 7].Formula = "SUM(D2:G2)";
worksheet4.Cells[1, 2].Formula = "PIESPARKLINE(H2:H8,\"#919F81\",\"#D7913E\",\"#CEA722\", \"#D2DD3E\", \"#B58091\",\"#8974A9\",\"#728BAD\")";
sheet4.AddSpanCell(1, 2, 7, 1);
sheet4.AddSpanCell(0, 1, 1, 2);
sheet4.Cells[1, 1, 7, 1].Formula = "H2 / SUM(H2: H8)";
// sheet4の背景色を設定します
worksheet4.Cells[1, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFF919F81));
worksheet4.Cells[2, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFD7913E));
worksheet4.Cells[3, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFCEA722));
worksheet4.Cells[4, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFD2DD3E));
worksheet4.Cells[5, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFB58091));
worksheet4.Cells[6, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFF8974A9));
worksheet4.Cells[7, 1].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFF728BAD));

worksheet1.Range("B2:B8").NumberFormat = "0.00%";
worksheet2.Range("B2:B7").NumberFormat = "0.00%";
worksheet3.Range("B2:B6").NumberFormat = "0.00%";
worksheet4.Range("B2:B8").NumberFormat = "0.00%";

            
// ダッシュボードシートの行0とその設定にヘッダデータを設定します。
sheetDashboard.Cells[0, 0].Text = "毎月傾向分析";
sheetDashboard.AddSpanCell(0, 0, 1, 14);
// ヘッダテキストのスタイルを設定します
// 範囲を取得します
range = worksheet0.Cells["A1:A14"];
// 範囲にスタイルを適用します
range.Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 12, Bold = true });
range.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
range.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center;
worksheet0.Cells[0, 0].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFF8CA4B9));

// 「ダッシュボード」シートにカメラシェイプを追加します
worksheet1.Cells["A1:C8"].Copy(true);   // bool showUI = trueでコピーする必要があります
worksheet0.Pictures.Paste("A3", true);  // CameraShapeはworksheet1!$A$1:$C$8を参照します


worksheet2.Cells["A1:C7"].Copy(true);   // bool showUI = trueでコピーする必要があります
worksheet0.Pictures.Paste("H3", true);  // CameraShapeはworksheet1!$A$1:$C$8を参照します

worksheet3.Cells["A1:C6"].Copy(true);   // bool showUI = trueでコピーする必要があります
worksheet0.Pictures.Paste("A17", true); // CameraShapeはworksheet2!$A$1:$C$5を参照します

worksheet4.Cells["A1:C8"].Copy(true);   // bool showUI = trueでコピーする必要があります
worksheet0.Pictures.Paste("H17", true); // CameraShapeはworksheet2!$A$1:$C$8を参照します

// ダッシュボードシートをアクティブシートとして設定します。
fpSpread1.ActiveSheetIndex = 0;

'シート数を設定します。
FpSpread1.Sheets.Count = 5

FpSpread1.Features.EnhancedShapeEngine = True
FpSpread1.Features.RichClipboard = True

'シートを取得します。
Dim sheetDashboard = FpSpread1.Sheets(0)
Dim sheet1 = FpSpread1.Sheets(1)
Dim sheet2 = FpSpread1.Sheets(2)
Dim sheet3 = FpSpread1.Sheets(3)
Dim sheet4 = FpSpread1.Sheets(4)
Dim worksheet0 = sheetDashboard.AsWorksheet()
Dim worksheet1 = sheet1.AsWorksheet()
Dim worksheet2 = sheet2.AsWorksheet()
Dim worksheet3 = sheet3.AsWorksheet()
Dim worksheet4 = sheet4.AsWorksheet()

'シート名を設定します。
sheetDashboard.SheetName = "ダッシュボード"
sheet1.SheetName = "果物"
sheet2.SheetName = "野菜"
sheet3.SheetName = "肉"
sheet4.SheetName = "パン屋"

'列と行のヘッダを非表示にします
sheetDashboard.ColumnHeader.Visible = False
sheetDashboard.RowHeader.Visible = False

sheet1.ColumnHeader.Visible = False
sheet1.RowHeader.Visible = False

sheet2.ColumnHeader.Visible = False
sheet2.RowHeader.Visible = False

sheet3.ColumnHeader.Visible = False
sheet3.RowHeader.Visible = False

sheet4.ColumnHeader.Visible = False
sheet4.RowHeader.Visible = False

'グリッド線を非表示にします。
sheetDashboard.VerticalGridLine = New FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty)
sheetDashboard.HorizontalGridLine = New FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty)

sheet1.VerticalGridLine = New FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty)
sheet1.HorizontalGridLine = New FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty)

sheet2.VerticalGridLine = New FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty)
sheet2.HorizontalGridLine = New FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty)

sheet3.VerticalGridLine = New FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty)
sheet3.HorizontalGridLine = New FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty)

sheet4.VerticalGridLine = New FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty)
sheet4.HorizontalGridLine = New FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Flat, Color.Empty)

'列幅を設定します。
sheet1.Columns(0).Width = 100
sheet1.Columns(1).Width = 140
sheet1.Columns(2).Width = 200

For i = 3 To 8 - 1
    sheet1.Columns(i).Width = 80
Next

sheet2.Columns(0).Width = 100
sheet2.Columns(1).Width = 140
sheet2.Columns(2).Width = 200

For i = 3 To 8 - 1
    sheet2.Columns(i).Width = 80
Next

sheet3.Columns(0).Width = 100
sheet3.Columns(1).Width = 140
sheet3.Columns(2).Width = 200

For i = 3 To 8 - 1
    sheet3.Columns(i).Width = 80
Next

sheet4.Columns(0).Width = 100
sheet4.Columns(1).Width = 140
sheet4.Columns(2).Width = 200

For i = 3 To 8 - 1
    sheet4.Columns(i).Width = 80
Next

'行の高さを設定します。
sheetDashboard.Rows(0).Height = 35
sheetDashboard.Rows(1).Height = 5
sheet1.Rows(0).Height = 35
sheet2.Rows(0).Height = 35
sheet3.Rows(0).Height = 35
sheet4.Rows(0).Height = 35

For i = 1 To 8 - 1
    sheet1.Rows(i).Height = 30
    sheet4.Rows(i).Height = 30
Next

For i = 1 To 7 - 1
    sheet2.Rows(i).Height = 30
Next

For i = 1 To 6 - 1
    sheet3.Rows(i).Height = 30
Next

'異なるシートのデータ配列を作成して設定します。
worksheet1.SetValue(0, 0, New Object(,) {
    {"果物", "月間売上(%)", "傾向", "1週目（Kg）", "2週目（Kg）", "3週目（Kg）", "4週目（Kg）", "合計"},
    {"リンゴ", Nothing, Nothing, 1031, 927, 1287, 1484, Nothing},
    {"アボカド", Nothing, Nothing, 923, 1468, 791, 981, Nothing},
    {"バナナ", Nothing, Nothing, 789, 571, 827, 671, Nothing},
    {"ブドウ", Nothing, Nothing, 782, 871, 900, 1100, Nothing},
    {"マンゴー", Nothing, Nothing, 829, 450, 837, 671, Nothing},
    {"ストロベリー", Nothing, Nothing, 1500, 1817, 1981, 1383, Nothing},
    {"スイカ", Nothing, Nothing, 980, 1011, 956, 817, Nothing}
})
worksheet2.SetValue(0, 0, New Object(,) {
    {"野菜", "月間売上(%)", "傾向", "1週目（Kg）", "2週目（Kg）", "3週目（Kg）", "4週目（Kg）", "合計"},
    {"にんじん", Nothing, Nothing, 782, 490, 1012, 659, Nothing},
    {"タマネギ", Nothing, Nothing, 1274, 1290, 721, 671, Nothing},
    {"ポテト", Nothing, Nothing, 2001, 2301, 1987, 2401, Nothing},
    {"カボチャ", Nothing, Nothing, 582, 771, 861, 491, Nothing},
    {"ほうれん草", Nothing, Nothing, 302, 233, 251, 292, Nothing},
    {"トマト", Nothing, Nothing, 938, 1002, 1139, 1039, Nothing}
})
worksheet3.SetValue(0, 0, New Object(,) {
    {"肉", "月間売上(%)", "傾向", "1週目（Kg）", "2週目（Kg）", "3週目（Kg）", "4週目（Kg）", "合計"},
    {"ビーフ", Nothing, Nothing, 5711, 4567, 4519, 5698, Nothing},
    {"チキン", Nothing, Nothing, 6261, 5627, 3987, 4238, Nothing},
    {"ラム", Nothing, Nothing, 4789, 4571, 5827, 4671, Nothing},
    {"豚肉", Nothing, Nothing, 6561, 5871, 5900, 5119, Nothing},
    {"マトン", Nothing, Nothing, 5501, 4817, 5981, 6383, Nothing}
})
worksheet4.SetValue(0, 0, New Object(,) {
    {"パン屋", "月間売上(%)", "傾向", "1週目（Kg）", "2週目（Kg）", "3週目（Kg）", "4週目（Kg）", "合計"},
    {"パン", Nothing, Nothing, 1031, 927, 1287, 1484, Nothing},
    {"ブラウニー", Nothing, Nothing, 923, 1468, 791, 981, Nothing},
    {"ケーキ", Nothing, Nothing, 789, 571, 827, 671, Nothing},
    {"クッキー", Nothing, Nothing, 782, 871, 900, 1100, Nothing},
    {"ペストリー", Nothing, Nothing, 1500, 1817, 1981, 1383, Nothing},
    {"パイ", Nothing, Nothing, 1360, 1328, 1238, 1238, Nothing},
    {"タルト", Nothing, Nothing, 1671, 1782, 2019, 1983, Nothing}
})

'sheet1、sheet2、sheet 3、sheet4のタイトル行のスタイルを設定します。
Dim range = worksheet1.Cells("A1:H1")
range.Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
    .Name = "Arial",
    .Size = 10,
    .Bold = True
})
range.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
range.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center

range = worksheet2.Cells("A1:H1")
range.Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
    .Name = "Arial",
    .Size = 10,
    .Bold = True
})
range.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
range.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center

range = worksheet3.Cells("A1:H1")
range.Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
    .Name = "Arial",
    .Size = 10,
    .Bold = True
})
range.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
range.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center

range = worksheet4.Cells("A1:H1")
range.Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
    .Name = "Arial",
    .Size = 10,
    .Bold = True
})
range.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
range.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center

'sheet1、sheet2、sheet 3、sheet4の背景色を設定します
worksheet1.Cells("A1:H1").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFE8F6FA)
worksheet2.Cells("A1:H1").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFE8F6FA)
worksheet3.Cells("A1:H1").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFE8F6FA)
worksheet4.Cells("A1:H1").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFE8F6FA)

'sheet1の数式を設定します
sheet1.Cells(1, 7, 7, 7).Formula = "SUM(D2:G2)"
worksheet1.Cells(1, 2).Formula = "PIESPARKLINE(H2:H8,""#919F81"",""#D7913E"",""#CEA722"", ""#D2DD3E"", ""#B58091"",""#8974A9"",""#728BAD"")"
sheet1.AddSpanCell(1, 2, 7, 1)
sheet1.AddSpanCell(0, 1, 1, 2)
sheet1.Cells(1, 1, 7, 1).Formula = "H2 / SUM(H2: H8)"
'sheet1の背景色を設定します
worksheet1.Cells(1, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFF919F81)
worksheet1.Cells(2, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFD7913E)
worksheet1.Cells(3, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFCEA722)
worksheet1.Cells(4, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFD2DD3E)
worksheet1.Cells(5, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFB58091)
worksheet1.Cells(6, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFF8974A9)
worksheet1.Cells(7, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFF728BAD)

'sheet2の数式を設定します
sheet2.Cells(1, 7, 6, 7).Formula = "SUM(D2:G2)"
worksheet2.Cells(1, 2).Formula = "PIESPARKLINE(H2:H8,""#919F81"",""#D7913E"",""#CEA722"", ""#D2DD3E"", ""#B58091"",""#8974A9"")"
sheet2.AddSpanCell(1, 2, 6, 1)
sheet2.AddSpanCell(0, 1, 1, 2)
sheet2.Cells(1, 1, 6, 1).Formula = "H2 / SUM(H2: H8)"
'sheet2の背景色を設定します
worksheet2.Cells(1, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFF919F81)
worksheet2.Cells(2, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFD7913E)
worksheet2.Cells(3, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFCEA722)
worksheet2.Cells(4, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFD2DD3E)
worksheet2.Cells(5, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFB58091)
worksheet2.Cells(6, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFF8974A9)

'sheet3の数式を設定します
sheet3.Cells(1, 7, 5, 7).Formula = "SUM(D2:G2)"
worksheet3.Cells(1, 2).Formula = "PIESPARKLINE(H2:H8,""#919F81"",""#D7913E"",""#CEA722"", ""#D2DD3E"", ""#B58091"")"
sheet3.AddSpanCell(1, 2, 5, 1)
sheet3.AddSpanCell(0, 1, 1, 2)
sheet3.Cells(1, 1, 5, 1).Formula = "H2 / SUM(H2: H8)"
'sheet3の背景色を設定します
worksheet3.Cells(1, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFF919F81)
worksheet3.Cells(2, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFD7913E)
worksheet3.Cells(3, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFCEA722)
worksheet3.Cells(4, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFD2DD3E)
worksheet3.Cells(5, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFB58091)

'sheet4の数式を設定します
sheet4.Cells(1, 7, 7, 7).Formula = "SUM(D2:G2)"
worksheet4.Cells(1, 2).Formula = "PIESPARKLINE(H2:H8,""#919F81"",""#D7913E"",""#CEA722"", ""#D2DD3E"", ""#B58091"",""#8974A9"",""#728BAD"")"
sheet4.AddSpanCell(1, 2, 7, 1)
sheet4.AddSpanCell(0, 1, 1, 2)
sheet4.Cells(1, 1, 7, 1).Formula = "H2 / SUM(H2: H8)"
'sheet4の背景色を設定します
worksheet4.Cells(1, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFF919F81)
worksheet4.Cells(2, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFD7913E)
worksheet4.Cells(3, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFCEA722)
worksheet4.Cells(4, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFD2DD3E)
worksheet4.Cells(5, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFB58091)
worksheet4.Cells(6, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFF8974A9)
worksheet4.Cells(7, 1).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFF728BAD)

worksheet1.Range("B2:B8").NumberFormat = "0.00%"
worksheet2.Range("B2:B7").NumberFormat = "0.00%"
worksheet3.Range("B2:B6").NumberFormat = "0.00%"
worksheet4.Range("B2:B8").NumberFormat = "0.00%"

        
'ダッシュボードシートの行0とその設定にヘッダデータを設定します。
sheetDashboard.Cells(0, 0).Text = "毎月傾向分析"
sheetDashboard.AddSpanCell(0, 0, 1, 14)
'ヘッダテキストのスタイルを設定します
'範囲を取得します
range = worksheet0.Cells("A1:A14")
'範囲にスタイルを適用します
range.Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
    .Name = "Arial",
    .Size = 12,
    .Bold = True
})
range.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
range.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center
worksheet0.Cells(0, 0).Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFF8CA4B9)

'「ダッシュボード」シートにカメラシェイプを追加します
worksheet1.Cells("A1:C8").Copy(True)    'bool showUI = trueでコピーする必要があります
worksheet0.Pictures.Paste("A3", True)   'CameraShapeはworksheet1!$A$1:$C$8を参照します

worksheet2.Cells("A1:C7").Copy(True)    'bool showUI = trueでコピーする必要があります
worksheet0.Pictures.Paste("H3", True)   'CameraShapeはworksheet1!$A$1:$C$8を参照します

worksheet3.Cells("A1:C6").Copy(True)    'bool showUI = trueでコピーする必要があります
worksheet0.Pictures.Paste("A17", True)  'CameraShapeはworksheet2!$A$1:$C$5を参照します

worksheet4.Cells("A1:C8").Copy(True)    'bool showUI = trueでコピーする必要があります
worksheet0.Pictures.Paste("H17", True)  'CameraShapeはworksheet2!$A$1:$C$8を参照します

'ダッシュボードシートをアクティブシートとして設定します。
FpSpread1.ActiveSheetIndex = 0

FarPoint名前空間でソースデータを編集する場合、カメラシェイプを手動的に更新する必要があります。拡張カメラシェイプは、以下のAPIを使用してリフレッシュできます。 IWorksheet.Pictures.Refresh - ワークシート内のすべてのカメラシェイプを再描画します。 CalculationEngine.NotifyVisualChanged - 計算エンジンにビジュアルが再描画されることを通知します。

カスタムカメラの作成

SPREAD for Windows Formsは、単一セル内にセル範囲のビジュアルを表すICameraインタフェースを提供します。次のICameraメンバーを使用できます。

• Formula プロパティは、カメラのソースセル範囲を示す数式を取得または設定します。
• Imageプロパティは、カメラオブジェクトの画像ブラッシュを取得します。
• ICamera.Deleteメソッドはカメラを削除します。.

次の画像は、セルに適用できるカスタムCAMERA関数の使用を示します。この関数を使用して、セル内にセル範囲を表示します。

コードを表示

public partial class CameraShapeInterface : Form
{
    public CameraShapeInterface()
    {
        InitializeComponent();
    }

    private void CameraShapeInterface_Load(object sender, EventArgs e)
    {
        fpSpread1.AddCustomFunction(new CameraFunction());
        IWorkbook workbook = fpSpread1.AsWorkbook();
        workbook.Worksheets.Add();
        IWorksheet sheet2 = workbook.Worksheets[1];
        sheet2.Cells["B2"].Value = 5;

        IWorksheet sheet1 = workbook.Worksheets[0];
        sheet1.Cells["A1"].ColumnWidth = sheet2.Cells["A1"].ColumnWidth * 2 + 1;
        sheet1.Cells["A1"].RowHeight = sheet2.Cells["A1"].RowHeight * 2 + 1;
        sheet1.Cells["A1"].Formula = "CAMERA(Sheet2!A1:B2)";
    }
}
public class CameraFunction : VisualFunction
{
    public CameraFunction() : base("CAMERA", 1, 1, FunctionAttributes.Variant, (IFunctionVisualizer)CameraVisualizer.Instance, false)
    {

    }

    protected override bool IsArrayParameter(int argIndex)
    {
        return true;
    }

    protected override bool Evaluate(IArguments arguments, IValue result)
    {
        IEvaluationContext context = arguments.EvaluationContext;
        IValue range = arguments[0];
        switch (range.ValueType)
        {
            case GrapeCity.CalcEngine.ValueType.Reference:
            case GrapeCity.CalcEngine.ValueType.AdjustableReference:
                if (range.GetReferenceSource(context) is GrapeCity.Spreadsheet.IWorksheet worksheet)
                {
                    ICamera camera = worksheet.Range(range.GetReference(context)).CreateCamera();
                    VisualizationData data = new VisualizationData(camera);
                    result.SetValue(data, true);
                    break;
                }
                else
                {
                    goto default;
                }
            default:
                context.Error = CalcError.Reference;
                break;
        }

        return true;
    }

    private class CameraVisualizer : FunctionVisualizer
    {
        internal static CameraVisualizer Instance;

        static CameraVisualizer()
        {
            Instance = new CameraVisualizer();
        }

        private CameraVisualizer()
        {

        }

        public override bool IsVisual => true;
        public override bool IsBackgroundSupported => true;

        protected override void PaintCell(IPaintingContext context, Rectangle rect, object cellValue, ref StyleFormat styleFormat)
        {
            if (cellValue is VisualizationData data && data.Value.ValueType == GrapeCity.CalcEngine.ValueType.Object && data.Value.GetObject() is ICamera camera)
            {
                GrapeCity.Drawing.ImageBrush imageBrush = camera.Image;
                if (imageBrush != null)
                {
                    Brush brush = context.ToGdiBrush(imageBrush, rect);
                    context.Graphics.FillRectangle(brush, rect);
                    brush.Dispose();
                }
            }
        }
    }
}

Public Class CameraShapeInterface
    Private Sub CameraShapeInterface_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        FpSpread1.AddCustomFunction(New CameraFunction())
        Dim workbook As IWorkbook = FpSpread1.AsWorkbook()
        workbook.Worksheets.Add()
        Dim sheet2 As IWorksheet = workbook.Worksheets(1)
        sheet2.Cells("B2").Value = 5
        Dim sheet1 As IWorksheet = workbook.Worksheets(0)
        sheet1.Cells("A1").ColumnWidth = sheet2.Cells("A1").ColumnWidth * 2 + 1
        sheet1.Cells("A1").RowHeight = sheet2.Cells("A1").RowHeight * 2 + 1
        sheet1.Cells("A1").Formula = "CAMERA(Sheet2!A1:B2)"

    End Sub
End Class

Public Class CameraFunction
    Inherits VisualFunction

    Public Sub New()
        MyBase.New("CAMERA", 1, 1, FunctionAttributes.[Variant], CType(CameraVisualizer.Instance, IFunctionVisualizer), False)
    End Sub

    Protected Overrides Function IsArrayParameter(ByVal argIndex As Integer) As Boolean
        Return True
    End Function

    <Obsolete>
    Protected Overrides Function Evaluate(ByVal arguments As IArguments, ByVal result As IValue) As Boolean
        Dim context As IEvaluationContext = arguments.EvaluationContext
        Dim range As IValue = arguments(0)
        Dim worksheet As IWorksheet = Nothing

        Select Case range.ValueType
            Case ValueType.Reference, ValueType.AdjustableReference

                If CSharpImpl.__Assign(worksheet, TryCast(range.GetReferenceSource(context), IWorksheet)) IsNot Nothing Then
                    Dim camera As ICamera = worksheet.Range(range.GetReference(context)).CreateCamera()
                    Dim data As VisualizationData = New VisualizationData(camera)
                    result.SetValue(data, True)
                    Exit Select
                Else
                    GoTo _Select0_CaseDefault
                End If

            Case Else
_Select0_CaseDefault:
                context.[Error] = CalcError.Reference
        End Select

        Return True
    End Function

    Private Class CameraVisualizer
        Inherits FunctionVisualizer

        Friend Shared Instance As CameraVisualizer

        Shared Sub New()
            Instance = New CameraVisualizer()
        End Sub

        Private Sub New()
        End Sub

        Public Overrides ReadOnly Property IsVisual As Boolean
            Get
                Return True
            End Get
        End Property

        Public Overrides ReadOnly Property IsBackgroundSupported As Boolean
            Get
                Return True
            End Get
        End Property

        <Obsolete>
        Protected Overrides Sub PaintCell(ByVal context As IPaintingContext, ByVal rect As Rectangle, ByVal cellValue As Object, ByRef styleFormat As StyleFormat)
            Dim data As VisualizationData = Nothing, camera As ICamera = Nothing

            If CSharpImpl.__Assign(data, TryCast(cellValue, VisualizationData)) IsNot Nothing AndAlso data.Value.ValueType = GrapeCity.CalcEngine.ValueType.Object AndAlso CSharpImpl.__Assign(camera, TryCast(data.Value.GetObject(), ICamera)) IsNot Nothing Then
                Dim imageBrush As GrapeCity.Drawing.ImageBrush = camera.Image

                If imageBrush IsNot Nothing Then
                    Dim brush As Brush = context.ToGdiBrush(imageBrush, rect)
                    context.Graphics.FillRectangle(brush, rect)
                    brush.Dispose()
                End If
            End If
        End Sub

        Private Class CSharpImpl
            <Obsolete("Please refactor calling code to use normal Visual Basic assignment")>
            Shared Function __Assign(Of T)(ByRef target As T, value As T) As T
                target = value
                Return value
            End Function
        End Class
    End Class

    Private Class CSharpImpl
        <Obsolete("Please refactor calling code to use normal Visual Basic assignment")>
        Shared Function __Assign(Of T)(ByRef target As T, value As T) As T
            target = value
            Return value
        End Function
    End Class
End Class

SPREADデザイナの使用

1. SPREADのプロパティウィンドウでEnhancedShapeEngineを有効にします
2. デザイナでセル範囲を選択します。
3. ［挿入］メニューを選択します。
4. カメラシェイプのアイコンを選択します。
5. シェイプ上にクリックして、シェイプを移動します。
6. 追加オプションを持つ［描画ツール］メニューが表示されます。
7. ［ファイル］メニューから［適用して終了］を選択し、変更を保存してSPREAD デザイナを終了します。

© MESCIUS inc. All rights reserved.