Spread Windows Forms 18 Product Documentation

Creating Enhanced Camera Shape

Camera shape, as the name suggests, is a mirror image of a referenced area in a spreadsheet. It is a dynamic image, meaning that any change in the referenced region is reflected in the image as well. You can create a camera shape by referring to Creating Camera Shapes topic.

Additionally, Spread for Winforms provides an enhanced camera shape that inherits all the features of the enhanced shape engine. For example, the enhanced camera shape can be moved, resized, rotated, and supported for Excel I/O. They can also be grouped or ungrouped with other shapes and copy-pasted from one sheet to another.

The enhanced camera shape displays the contents of a cell range by linking the cell range to a shape. On selecting the camera shape, it displays the source cell range in the formula bar. You can edit this cell range or defined name (of any cell range) to dynamically switch the camera shape's source data.

Adding Camera Shape

You can add the enhanced camera shape using the IPicture.Paste method. It accepts parameters such as the destination in the sheet and whether to establish a link to the source of the pasted picture. The RichClipboard property needs to be true to use this method.

You can also convert a picture to a camera shape by using IShape.Formula property to set the shape formula.

The implementation of the new camera shape takes effect if EnhancedShapeEngine is true.

// Get workbook and activesheet
 GrapeCity.Spreadsheet.IWorkbook TestWorkBook = fpSpread1.AsWorkbook();
 GrapeCity.Spreadsheet.IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
// Set values in worksheet
 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;
// Adding camera shape by using IPictures.Paste method
TestActiveSheet.Cells["A1:E7"].Copy(true);      // Have to Copy with bool showUI = true
 TestActiveSheet.Pictures.Paste("D11", true);    // CameraShape refers to Sheet1!$A$1:$E$7 is created
 TestActiveSheet.Pictures.Paste("F11", false);   // A picture that snap content of A1:E7 is created
// Set size for picture without autosize behavior
 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";
// Add by Pictures.Paste to have auto-size behavior
 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";

' Get workbook and activesheet
 Dim TestWorkBook As GrapeCity.Spreadsheet.IWorkbook = fpSpread1.AsWorkbook()
 Dim TestActiveSheet As GrapeCity.Spreadsheet.IWorksheet = fpSpread1.AsWorkbook().ActiveSheet
' Set values in worksheet
 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
' Adding camera shape by using IPictures.Paste method
TestActiveSheet.Cells("A1:E7").Copy(True)       ' Have to Copy with bool showUI = true
 TestActiveSheet.Pictures.Paste("D11", True)    ' CameraShape refers to Sheet1!$A$1:$E$7 is created
 TestActiveSheet.Pictures.Paste("F11", False)   ' A picture that snap content of A1:E7 is created
' Set size for picture without autosize behavior
 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"
' Add by Pictures.Paste to have auto-size behavior
 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"

!type=note
Note: Set fill doesn't have an effect on the enhanced camera shape.

Recursive Painting in Camera Shape

An enhanced camera shape is capable of being displayed inside another camera shape. Unlike Excel, camera shapes in Spread for Winforms do not display inside itself or another camera shapes if they create a recursive painting.

You can observe the different behaviors in the examples below:

Camera shape do not display inside itself if they create a recursive painting.

Show Code

// Camera shape creating recursive painting over itself
// Without autosize behavior
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 * 10);
picture.Formula = "Sheet1!$A$1:$E$10";
// With autosize behavior
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";

' Camera shape creating recursive painting over itself
' Without autosize behavior
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 * 10)
picture.Formula = "Sheet1!$A$1:$E$10"
' With autosize behavior
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"

Camera shape do not display inside another camera shape if they create a recursive painting.

Show Code

// Camera shape creating recursive painting over another camera shape
// Without autosize behavior
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 * 4, rowHeight * 10);
picture.Formula = "Sheet1!$A$1:$D$10";
IShape picture1 = TestActiveSheet.Shapes.AddPictureToCell(null, true, true, 0, 2, colWidth * 2, rowHeight * 10);
picture1.Formula = "Sheet1!$F$1:$G$10";
TestActiveSheet.Shapes.Range(new int[] { 0, 0 }).Line.ForeColor.ARGB = System.Drawing.Color.Black.ToArgb();
// With autosize behavior
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();

' Camera shape creating recursive painting over another camera shape
' Without autosize behavior
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 * 4, rowHeight * 10)
picture.Formula = "Sheet1!$A$1:$D$10"
Dim picture1 As IShape = TestActiveSheet.Shapes.AddPictureToCell(Nothing, True, True, 0, 2, colWidth * 2, rowHeight * 10)
picture1.Formula = "Sheet1!$F$1:$G$10"
TestActiveSheet.Shapes.Range((New Integer() {0, 0})).Line.ForeColor.ARGB = Drawing.Color.Black.ToArgb()
' With autosize behavior
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()

Camera shape displays inside another camera shape if they do not create a recursive painting.

Show Code

// Camera shape displays inside another camera shape without creating recursive painting
// Without autosize behavior
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 * 4, rowHeight * 3);
picture.Formula = "Sheet1!$A$1:$D$3";
IShape picture1 = TestActiveSheet.Shapes.AddPictureToCell(null, true, true, 6, 7, colWidth * 5, rowHeight * 10);
picture1.Formula = "Sheet1!$A$1:$E$10";
// With autosize behavior
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";

' Camera shape displays inside another camera shape without creating recursive painting
' Without autosize behavior
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 * 4, rowHeight * 3)
picture.Formula = "Sheet1!$A$1:$D$3"
Dim picture1 As IShape = TestActiveSheet.Shapes.AddPictureToCell(Nothing, True, True, 6, 7, colWidth * 5, rowHeight * 10)
picture1.Formula = "Sheet1!$A$1:$E$10"
' With autosize behavior
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"

Camera Shape with ExcelIO

The enhanced camera shape is imported if EnhancedShapeEngine is true. Otherwise, the old camera shape is imported.

When exporting camera shapes to Excel:

If the workbook contains only the enhanced camera shape, it is exported adequately.
If the workbook contains only the old camera shape, it is exported irrespective of the EnhancedShapeEngine property.
If the workbook contains both the old and enhanced camera shape, with EnhancedShapeEngine set to true:
- The enhanced camera shape is exported.
- The old camera shape is exported if the Exchangeable flag is used otherwise, it is exported as a normal picture.

Usage Scenario

Consider a scenario where the sales data of different products across a supermarket is maintained to analyze the sales trends. The data for different product categories like Fruits, Vegetables, Bakery, Meat, etc., is managed in worksheets of a spreadsheet.

You can display the summarized monthly sales data on a consolidated 'Dashboard' worksheet which shows camera shapes for the products' sales across different product categories. Any change made to the sales data is reflected in the 'Dashboard' sheet as well.

Show Code

// Set sheet count
fpSpread1.Sheets.Count = 5;

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

// Get the sheets
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();

// Set sheet names
sheetDashboard.SheetName = "Dashboard";
sheet1.SheetName = "Fruits";
sheet2.SheetName = "Vegetables";
sheet3.SheetName = "Meat";
sheet4.SheetName = "Bakery";

// Hide column & row headers
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;

// Hide gridlines
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);

// Set column widths
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;

// Set row heights
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;

}

// Create and set data arrays for different sheets
worksheet1.SetValue(0, 0, new object[,]
{
    {"Fruits", "Monthly Sales(%)", "Trend", "Week 1(Kg)", "Week 2(Kg)", "Week 3(Kg)", "Week 4(Kg)", "Total"},
    {"Apple",null, null , 1031, 927, 1287, 1484,null},
    {"Avacado",null , null, 923, 1468, 791, 981, null},
    {"Banana",null , null, 789, 571, 827, 671, null},
    {"Grapes", null,null , 782, 871, 900, 1100,null},
    {"Mango",null ,null , 829, 450, 837, 671,null},
    {"Strawberry",null ,null , 1500, 1817, 1981, 1383,null},
    {"Watermelon",null ,null , 980, 1011, 956, 817,null}
});
worksheet2.SetValue(0, 0, new object[,]
{
    {"Vegetables", "Monthly Sales(%)", "Trend", "Week 1(Kg)", "Week 2(Kg)", "Week 3(Kg)", "Week 4(Kg)", "Total"},
    {"Carrot",null , null, 782, 490, 1012, 659,null},
    {"Onion",null , null, 1274, 1290, 721, 671,null},
    {"Potato",null , null, 2001, 2301, 1987, 2401,null},
    {"Pumpkin",null , null,582, 771, 861, 491,null},
    {"Spinach",null , null, 302, 233, 251, 292,null},
    {"Tomato",null , null,938, 1002, 1139, 1039,null}
});
worksheet3.SetValue(0, 0, new object[,]
{
    {"Meat", "Monthly Sales(%)", "Trend", "Week 1(Kg)", "Week 2(Kg)", "Week 3(Kg)", "Week 4(Kg)", "Total"},
    {"Beaf",null , null,5711, 4567, 4519, 5698, null},
    {"Chicken",null , null,6261, 5627, 3987, 4238, null},
    {"Lamb",null , null,4789, 4571, 5827, 4671, null},
    {"Pork",null , null,6561, 5871, 5900, 5119, null},
    {"Mutton",null , null,5501, 4817, 5981, 6383, null}
});

worksheet4.SetValue(0, 0, new object[,]
{
    {"Bakery", "Monthly Sales(%)", "Trend", "Week 1(Kg)", "Week 2(Kg)", "Week 3(Kg)", "Week 4(Kg)", "Total"},
    {"Bread", null , null, 1031, 927, 1287, 1484, null},
    {"Brownie", null , null , 923, 1468, 791, 981, null},
    {"Cake", null , null , 789, 571, 827, 671, null},
    {"Cookie", null , null, 782, 871, 900, 1100, null},
    {"Pastry",null , null , 1500, 1817, 1981, 1383, null},
    {"Pie",null , null , 1360, 1328, 1238, 1238, null},
    {"Tarte",null , null , 1671, 1782, 2019, 1983, null}
});

// Set style for title row of sheet1, sheet2, sheet 3, sheet4
// Get range
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;

// Set backcolor to first row of sheet1, sheet2, sheet3 and 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));

// Set formulas for 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)";
// set backcolor for 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));

// Set formulas for 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)";
// set backcolor for 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));

// Set formulas for 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)";
// set backcolor for 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));

// Set formulas for 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)";
// set backcolor for 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%";

// Set header data in row 0 of Dashboard sheet and its setting
sheetDashboard.Cells[0, 0].Text = "Monthly Trend Analysis";
sheetDashboard.AddSpanCell(0, 0, 1, 14);
// Set style for header text
// Get range
range = worksheet0.Cells["A1:A14"];
// Apply style to range
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));

// Add camera shapes in Dashboard sheet
worksheet1.Cells["A1:C8"].Copy(true);   // Have to Copy with bool showUI = true
worksheet0.Pictures.Paste("A3", true);  // CameraShape refers to worksheet1!$A$1:$C$8 is created

worksheet2.Cells["A1:C7"].Copy(true);   // Have to Copy with bool showUI = true
worksheet0.Pictures.Paste("H3", true);  // CameraShape refers to worksheet2!$A$1:$C$7 is created

worksheet3.Cells["A1:C6"].Copy(true);   // Have to Copy with bool showUI = true
worksheet0.Pictures.Paste("A17", true); // CameraShape refers to worksheet2!$A$1:$C$5 is created

worksheet4.Cells["A1:C8"].Copy(true);   // Have to Copy with bool showUI = true
worksheet0.Pictures.Paste("H17", true); // CameraShape refers to worksheet2!$A$1:$C$8 is created

// Set activesheet to dashboard sheet
fpSpread1.ActiveSheetIndex = 0;

'Set sheet count
FpSpread1.Sheets.Count = 5

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

'Get the sheets
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()

'Set sheet names
sheetDashboard.SheetName = "Dashboard"
sheet1.SheetName = "Fruits"
sheet2.SheetName = "Vegetables"
sheet3.SheetName = "Meat"
sheet4.SheetName = "Bakery"

'Hide column & row headers
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

'Hide gridlines
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)

'Set column widths
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

'Set row heights
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

'Create and set data arrays for different sheets
worksheet1.SetValue(0, 0, New Object(,) {
    {"Fruits", "Monthly Sales(%)", "Trend", "Week 1(Kg)", "Week 2(Kg)", "Week 3(Kg)", "Week 4(Kg)", "Total"},
    {"Apple", Nothing, Nothing, 1031, 927, 1287, 1484, Nothing},
    {"Avacado", Nothing, Nothing, 923, 1468, 791, 981, Nothing},
    {"Banana", Nothing, Nothing, 789, 571, 827, 671, Nothing},
    {"Grapes", Nothing, Nothing, 782, 871, 900, 1100, Nothing},
    {"Mango", Nothing, Nothing, 829, 450, 837, 671, Nothing},
    {"Strawberry", Nothing, Nothing, 1500, 1817, 1981, 1383, Nothing},
    {"Watermelon", Nothing, Nothing, 980, 1011, 956, 817, Nothing}
})
worksheet2.SetValue(0, 0, New Object(,) {
    {"Vegetables", "Monthly Sales(%)", "Trend", "Week 1(Kg)", "Week 2(Kg)", "Week 3(Kg)", "Week 4(Kg)", "Total"},
    {"Carrot", Nothing, Nothing, 782, 490, 1012, 659, Nothing},
    {"Onion", Nothing, Nothing, 1274, 1290, 721, 671, Nothing},
    {"Potato", Nothing, Nothing, 2001, 2301, 1987, 2401, Nothing},
    {"Pumpkin", Nothing, Nothing, 582, 771, 861, 491, Nothing},
    {"Spinach", Nothing, Nothing, 302, 233, 251, 292, Nothing},
    {"Tomato", Nothing, Nothing, 938, 1002, 1139, 1039, Nothing}
})
worksheet3.SetValue(0, 0, New Object(,) {
    {"Meat", "Monthly Sales(%)", "Trend", "Week 1(Kg)", "Week 2(Kg)", "Week 3(Kg)", "Week 4(Kg)", "Total"},
    {"Beaf", Nothing, Nothing, 5711, 4567, 4519, 5698, Nothing},
    {"Chicken", Nothing, Nothing, 6261, 5627, 3987, 4238, Nothing},
    {"Lamb", Nothing, Nothing, 4789, 4571, 5827, 4671, Nothing},
    {"Pork", Nothing, Nothing, 6561, 5871, 5900, 5119, Nothing},
    {"Mutton", Nothing, Nothing, 5501, 4817, 5981, 6383, Nothing}
})
worksheet4.SetValue(0, 0, New Object(,) {
    {"Bakery", "Monthly Sales(%)", "Trend", "Week 1(Kg)", "Week 2(Kg)", "Week 3(Kg)", "Week 4(Kg)", "Total"},
    {"Bread", Nothing, Nothing, 1031, 927, 1287, 1484, Nothing},
    {"Brownie", Nothing, Nothing, 923, 1468, 791, 981, Nothing},
    {"Cake", Nothing, Nothing, 789, 571, 827, 671, Nothing},
    {"Cookie", Nothing, Nothing, 782, 871, 900, 1100, Nothing},
    {"Pastry", Nothing, Nothing, 1500, 1817, 1981, 1383, Nothing},
    {"Pie", Nothing, Nothing, 1360, 1328, 1238, 1238, Nothing},
    {"Tarte", Nothing, Nothing, 1671, 1782, 2019, 1983, Nothing}
})

'Set style for title row of sheet1, sheet2, sheet3, 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

'Set backcolor to first row of sheet1, sheet2, sheet3, and 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)

'Set formulas for 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)"
'Set backcolor for 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)

'Set formulas for 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)"
'Set backcolor for 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)

'Set formulas for 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)"
'Set backcolor for 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)

'Set formulas for 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)"
'Set backcolor for 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%"

'Set header data in row 0 of Dashboard sheet and its setting
sheetDashboard.Cells(0, 0).Text = "Monthly Trend Analysis"
sheetDashboard.AddSpanCell(0, 0, 1, 14)
'Set style for header text
'Get range
range = worksheet0.Cells("A1:A14")
'Apply style to range
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)

'Add camera shapes in Dashboard sheet
worksheet1.Cells("A1:C8").Copy(True)    'Have to Copy with bool showUI = true
worksheet0.Pictures.Paste("A3", True)   'CameraShape refers to worksheet1!$A$1:$C$8 is created

worksheet2.Cells("A1:C7").Copy(True)    'Have to Copy with bool showUI = true
worksheet0.Pictures.Paste("H3", True)   'CameraShape refers to worksheet1!$A$1:$C$7 is created

worksheet3.Cells("A1:C6").Copy(True)    'Have to Copy with bool showUI = true
worksheet0.Pictures.Paste("A17", True)  'CameraShape refers to worksheet1!$A$1:$C$5 is created

worksheet4.Cells("A1:C8").Copy(True)    'Have to Copy with bool showUI = true
worksheet0.Pictures.Paste("H17", True)  'CameraShape refers to worksheet1!$A$1:$C$8 is created

'Set activesheet to dashboard sheet
FpSpread1.ActiveSheetIndex = 0

!type=note
Note: You must update camera shape manually if the source data is modified using the FarPoint namespace. The following APIs can be used to refresh the enhanced camera shape:
IWorksheet.Pictures.Refresh - Re-paints all camera shape in the worksheet.
CalculationEngine.NotifyVisualChanged - Notifies the calculation engine that the visuals are repainted.

Creating a Custom Camera

Spread for Winforms provides the ICamera interface that represents the visual of a cell range in a single cell. You can use the following ICamera members:

Formula property gets or sets the formula that indicates source cell range of the camera.
Image property gets the image brush of the camera object.
ICamera.Delete method deletes the camera.

The following GIF illustrates a custom CAMERA function that you can apply in cells. The function is used to show a cell range inside a cell.

Show Code

You can create the CAMERA function shown above by following the code below.

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

Using the Spread Designer

Enable EnhancedShapeEngine from the Spread property side pane.
Select a block of cells in the designer.
Select the Insert menu.
Select the camera shape icon.
Click on the shape to move it.
The Shape Format menu with additional options is displayed.
From the File menu choose Apply and Exit to apply your changes to the component and exit Spread Designer.

Adding Camera Shape
Recursive Painting in Camera Shape
Camera Shape with ExcelIO
Usage Scenario
Creating a Custom Camera
Using the Spread Designer