// ワークブックとアクティブシートを取得します。
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のコンテンツをスナップする画像

// 画像をカメラシェイプに変更します。
            
IShape picture = TestActiveSheet.Shapes.AddPictureToCell(null, true, true, 1, 3);
picture.Formula = "A1:E7";
TestWorkBook.Names.Add("_name", "$A$1:$B$1");
IShape picture1 = TestActiveSheet.Shapes.AddPictureToCell(null, true, true, 6, 0);
picture1.Formula = "_name";

// シート数を設定します。
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;