Illustrations in Spreadsheet control
22 Jun 202324 minutes to read
Illustrations helps you to insert a image, shapes and graphic objects in the Essential JS 2 spreadsheet.
Image
Adding images to a spreadsheet can enhance the visual appeal and help convey information more clearly.
NOTE
- The default value for
allowImageproperty istrue.
Insert Image
You can insert the image by using one of the following ways,
- Selecting the Insert tab in the Ribbon toolbar, and then choose the Image tab.
- Use the
insertImage()method programmatically.
The available parameters in insertImage() method are,
| Parameter | Type | Description |
|---|---|---|
| images | ImageModel |
Specifies the options to insert image in spreadsheet. |
| range(optional) | string |
Specifies the range in spreadsheet. |
The available arguments in ImageModel are:
- src: Specifies the image source.
- id: Specifies image element id.
- height: Specifies the height of the image.
- width: Specifies the width of the image.
- top: Specifies the height of the image.
- left: Specifies the width of the image.
NOTE
- In spreadsheet, you can add many types of image files, including IMAGE, JPG, PNG, GIF and JPEG files.
Delete Image
- If you want to delete the image, just select the image firstly, and then press the Delete key.
- Use the
deleteImage()method programmatically.
The available parameters in deleteImage() method are,
| Parameter | Type | Description |
|---|---|---|
| id | string |
Specifies the id of the image element to be deleted. |
| range(optional) | string |
Specifies the range in spreadsheet. |
Image Customization
Image feature allows you to view and insert a image in a spreadsheet and you can change the height and width of the image by resizing and move it to another position.
Height and Width
- You can change the height and width of the image by resizing.
- Use the
heightandwidthproperty in theinsertImage()method programmatically.
Top and Left
- You can change the position of the image by drag and drop.
- Use the
topandleftproperty in theinsertImage()method programmatically.
<ejs-spreadsheet id="spreadsheet" created="created" showRibbon="false" showFormulaBar="false">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet name="Employee Deatils" selectedRange="B2" showGridLines="false">
<e-spreadsheet-rows>
<e-spreadsheet-row index="1" height="30">
<e-spreadsheet-cells>
<e-spreadsheet-cell value=" Mark"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="2" height="40">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Id">
<e-spreadsheet-cellstyle verticalAlign="Bottom"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": 1001">
<e-spreadsheet-cellstyle verticalAlign="Bottom"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="3">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Gender"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": Male"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="4">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Contact Preference"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": Email"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="5">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Email"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": mark@gmail.com"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="6">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Date of Birth"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": Jan 3, 1985"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="7">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Department"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": IT"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="8" height="40">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="IsActive">
<e-spreadsheet-cellstyle verticalAlign="Top"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": True">
<e-spreadsheet-cellstyle verticalAlign="Top"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="10" height="30">
<e-spreadsheet-cells>
<e-spreadsheet-cell value=" Mary"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="11" height="40">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Id">
<e-spreadsheet-cellstyle verticalAlign="Bottom"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": 1002">
<e-spreadsheet-cellstyle verticalAlign="Bottom"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="12">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Gender"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": Female"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="13">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Contact Preference"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": Phone"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="14">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Email"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": mary@gmail.com"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="15">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Date of Birth"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": Jan 3, 1985"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="16">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Department"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": HR"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="17" height="40">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="IsActive">
<e-spreadsheet-cellstyle verticalAlign="Top"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": True">
<e-spreadsheet-cellstyle verticalAlign="Top"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="19" height="30">
<e-spreadsheet-cells>
<e-spreadsheet-cell value=" Nashi"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="20" height="40">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Id">
<e-spreadsheet-cellstyle verticalAlign="Bottom"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": 1003">
<e-spreadsheet-cellstyle verticalAlign="Bottom"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="21">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Gender"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": Female"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="22">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Contact Preference"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": Email"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="23">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Email"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": nashi@gmail.com"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="24">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Date of Birth"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": Apr 11, 1986"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="25">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="Department"></e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": IT"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index="26" height="40">
<e-spreadsheet-cells>
<e-spreadsheet-cell index="2" value="IsActive">
<e-spreadsheet-cellstyle verticalAlign="Top"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
<e-spreadsheet-cell index="3" value=": True">
<e-spreadsheet-cellstyle verticalAlign="Top"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-columns>
<e-spreadsheet-column width="20"></e-spreadsheet-column>
<e-spreadsheet-column width="280"></e-spreadsheet-column>
<e-spreadsheet-column width="172"></e-spreadsheet-column>
<e-spreadsheet-column width="160"></e-spreadsheet-column>
</e-spreadsheet-columns>
</e-spreadsheet-rows>
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<script>
function created() {
this.merge('B2:D2');
this.merge('B11:D11');
this.merge('B20:D20');
this.cellFormat({ fontWeight: 'bold', verticalAlign: 'middle', backgroundColor: '#1167b1', color: '#ffffff' }, 'B2');
this.cellFormat({ fontWeight: 'bold', verticalAlign: 'middle', backgroundColor: '#1167b1', color: '#ffffff' }, 'B11');
this.cellFormat({ fontWeight: 'bold', verticalAlign: 'middle', backgroundColor: '#1167b1', color: '#ffffff' }, 'B20');
this.cellFormat({ fontWeight: 'bold' }, 'C3:C9');
this.cellFormat({ fontWeight: 'bold' }, 'C12:C18');
this.cellFormat({ fontWeight: 'bold' }, 'C21:C27');
this.setBorder({ border: '1px solid #1167b1' }, 'B2:D9', 'Outer');
this.setBorder({ border: '1px solid #1167b1' }, 'B11:D18', 'Outer');
this.setBorder({ border: '1px solid #1167b1' }, 'B20:D27', 'Outer');
}
</script>public IActionResult Index()
{
return View();
}Limitations of Image
The following features have some limitations in Image:
- Corner resizing option in the image element.
- Copy and paste the external image.
Chart
A chart is a graphical representation of data, that organizes and represents a set of numerical or qualitative data. It mostly displays the selected range of data in terms of x-axis and y-axis. You can use the allowChart property to enable or disable the chart functionality.
NOTE
- The default value for the
allowChartproperty istrue.
Types of chart
The following types of charts are available in the Spreadsheet.
NOTE
- Column Chart
* Bar Chart
* Area Chart
* Line Chart
* Pie Chart
* Scatter Chart
Insert Chart
You can insert the chart by using one of the following ways,
- Select the chart icon in the Ribbon toolbar under the Insert Tab.
- Use the
insertChart()method programmatically.
The available parameter in the insertChart() method is,
| Parameter | Type | Description |
|---|---|---|
| chart | ChartModel |
Specifies the options to insert a chart in the spreadsheet. |
The available arguments in the ChartModel are:
- type: Specifies the type of chart.
- theme: Specifies the theme of a chart.
- isSeriesInRows: Specifies to switch the row or a column.
- range: Specifies the selected range or specified range.
- id: Specifies the chart element id.
- markerSettings: Specifies the marker settings. The marker is used to provide information about the data points in the series and is currently only applicable to the line chart.
<ejs-spreadsheet id="spreadsheet" created="created">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet name="Book Sales">
<e-spreadsheet-rows>
<e-spreadsheet-row height=30>
<e-spreadsheet-cells>
<e-spreadsheet-cell value="Book Sales 2016-2020">
<e-spreadsheet-cellstyle backgroundColor="#357cd2" color="#fff" fontWeight="Bold" textAlign="Center" verticalAlign="Middle"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row>
<e-spreadsheet-cells>
<e-spreadsheet-cell index=7>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
</e-spreadsheet-rows>
<e-spreadsheet-ranges>
<e-spreadsheet-range dataSource="ViewBag.DefaultData" startCell="A3"></e-spreadsheet-range>
</e-spreadsheet-ranges>
<e-spreadsheet-columns>
<e-spreadsheet-column width="110"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
</e-spreadsheet-columns>
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<script>
function created() {
this.merge('A1:F1');
this.cellFormat({ backgroundColor: '#357cd2', color: '#fff', fontWeight: 'bold', textAlign: 'center' }, 'A3:F3');
this.numberFormat('$#,##0.00', 'B4:F8');
//Render Column chart
this.insertChart([{type: 'Column', theme: 'Bootstrap5Dark', range: 'A3:B6', id: 'column-chart' }]);
//Render Line chart with Marker
this.insertChart([{type: 'Line', range: 'A3:B6', markerSettings: {visible: true, shape: 'Circle', isFilled: false, size: 10, border: {width: 2, color: '#3cb371'}}, id: 'line-chart'}]);
}
</script>public IActionResult Index()
{
List<object> chartData = new List<object>()
{
new { Book= "Classics", Year 2016= "19033", Year 2017= "78453", Year 2018= "24354", Year 2019= "18757", Year 2020= "34343" },
new { Book= "Mystery", Year 2016= "50400", Year 2017= "82311", Year 2018= "131003", Year 2019= "19899", Year 2020= "42200" },
new { Book= "Romance", Year 2016= "18002", Year 2017= "49529", Year 2018= "79567", Year 2019= "12302", Year 2020= "21277" },
new { Book= "Sci-Fi & Fantasy", Year 2016= "10033", Year 2017= "51200", Year 2018= "66211", Year 2019= "12899", Year 2020= "18779" },
new { Book= "Horror", Year 2016= "23454", Year 2017= "78665", Year 2018= "81232", Year 2019= "19888", Year 2020= "20986" }
};
ViewBag.DefaultData = data;
return View();
}Delete Chart
- If you want to delete the chart, just select the chart, and then press the Delete key.
- Use the
deleteChart()method programmatically.
The available parameter in the deleteChart() method is,
| Parameter | Type | Description |
|---|---|---|
| id | string |
Specifies the id of the chart element to be deleted. |
Chart Customization
Chart feature allows you to view and insert a chart in a spreadsheet, and you can change the height and width of the chart by resizing and moving it to another position.
-
You can change the height and width of the chart by resizing.
-
You can change the position of the chart by drag and drop.
<ejs-spreadsheet id="spreadsheet" created="created">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet name="Book Sales">
<e-spreadsheet-rows>
<e-spreadsheet-row height=30>
<e-spreadsheet-cells>
<e-spreadsheet-cell value="Book Sales 2016-2020">
<e-spreadsheet-cellstyle backgroundColor="#357cd2" color="#fff" fontWeight="Bold" textAlign="Center" verticalAlign="Middle"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row>
<e-spreadsheet-cells>
<e-spreadsheet-cell index=7>
<e-cell-charts><e-cell-chart type="Column" range="A3:F8"></e-cell-chart></e-cell-charts>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
</e-spreadsheet-rows>
<e-spreadsheet-ranges>
<e-spreadsheet-range dataSource="ViewBag.DefaultData" startCell="A3"></e-spreadsheet-range>
</e-spreadsheet-ranges>
<e-spreadsheet-columns>
<e-spreadsheet-column width="110"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
</e-spreadsheet-columns>
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<script>
function created() {
this.merge('A1:F1');
this.cellFormat({ backgroundColor: '#357cd2', color: '#fff', fontWeight: 'bold', textAlign: 'center' }, 'A3:F3');
this.numberFormat('$#,##0.00', 'B4:F8');
}
</script>public IActionResult Index()
{
List<object> chartData = new List<object>()
{
new { Book= "Classics", Year 2016= "19033", Year 2017= "78453", Year 2018= "24354", Year 2019= "18757", Year 2020= "34343" },
new { Book= "Mystery", Year 2016= "50400", Year 2017= "82311", Year 2018= "131003", Year 2019= "19899", Year 2020= "42200" },
new { Book= "Romance", Year 2016= "18002", Year 2017= "49529", Year 2018= "79567", Year 2019= "12302", Year 2020= "21277" },
new { Book= "Sci-Fi & Fantasy", Year 2016= "10033", Year 2017= "51200", Year 2018= "66211", Year 2019= "12899", Year 2020= "18779" },
new { Book= "Horror", Year 2016= "23454", Year 2017= "78665", Year 2018= "81232", Year 2019= "19888", Year 2020= "20986" }
};
ViewBag.DefaultData = data;
return View();
}Customization of line chart markers
Using the actionBegin event, you can change the shape, size, fill color, and border of the line chart marker. In the following example, you can see the modified marker appearance, such as shape and size, while creating the line chart with UI interaction.
<ejs-spreadsheet id="spreadsheet" created="created" actionBegin="onActionBegin">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet name="Book Sales">
<e-spreadsheet-rows>
<e-spreadsheet-row height=30>
<e-spreadsheet-cells>
<e-spreadsheet-cell value="Book Sales 2016-2020">
<e-spreadsheet-cellstyle backgroundColor="#357cd2" color="#fff" fontWeight="Bold" textAlign="Center" verticalAlign="Middle"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row>
<e-spreadsheet-cells>
<e-spreadsheet-cell index=7>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
</e-spreadsheet-rows>
<e-spreadsheet-ranges>
<e-spreadsheet-range dataSource="ViewBag.DefaultData" startCell="A3"></e-spreadsheet-range>
</e-spreadsheet-ranges>
<e-spreadsheet-columns>
<e-spreadsheet-column width="110"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
</e-spreadsheet-columns>
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<script>
function created() {
this.merge('A1:F1');
this.cellFormat({ backgroundColor: '#357cd2', color: '#fff', fontWeight: 'bold', textAlign: 'center' }, 'A3:F3');
this.numberFormat('$#,##0.00', 'B4:F8');
}
function onActionBegin(args) {
if (args.action === 'beforeInsertChart' && args.args.eventArgs.type.includes('Line')) {
args.args.eventArgs.markerSettings.shape = 'Triangle';
args.args.eventArgs.markerSettings.isFilled = false;
args.args.eventArgs.markerSettings.size = 10;
}
}
</script>public IActionResult Index()
{
List<object> chartData = new List<object>()
{
new { Book= "Classics", Year 2016= "19033", Year 2017= "78453", Year 2018= "24354", Year 2019= "18757", Year 2020= "34343" },
new { Book= "Mystery", Year 2016= "50400", Year 2017= "82311", Year 2018= "131003", Year 2019= "19899", Year 2020= "42200" },
new { Book= "Romance", Year 2016= "18002", Year 2017= "49529", Year 2018= "79567", Year 2019= "12302", Year 2020= "21277" },
new { Book= "Sci-Fi & Fantasy", Year 2016= "10033", Year 2017= "51200", Year 2018= "66211", Year 2019= "12899", Year 2020= "18779" },
new { Book= "Horror", Year 2016= "23454", Year 2017= "78665", Year 2018= "81232", Year 2019= "19888", Year 2020= "20986" }
};
ViewBag.DefaultData = data;
return View();
}Limitations of Chart
The following features have some limitations in the Chart:
- Insert row/delete row between the chart data source will not reflect the chart.
- Copy/paste into the chart data source will not reflect the chart.
- Corner resizing option in chart element.