Working with charts using various operations28 Jul 202224 minutes to read Show
Essential XlsIO has support for creating and modifying Excel charts inside a workbook or as a chart worksheet. Creating a ChartThe IChartShape interface represents the chart in a worksheet. A chart can be created either through the existing data in the worksheet, directly entering series or by adding series one by one. The following code example illustrates how to create a chart through the existing data in the worksheet.
A complete working example to create a chart in C# is present on this GitHub page. Creating a Chart from directly entered ValuesA chart in XlsIO can also be created from directly entered values. The Following code snippets illustrate how to create a chart from directly entered values.
A complete working example to create a chart from scratch in C# is present on this GitHub page. Creating a Chart by adding SeriesA chart can also be created by adding series one by one. The following code illustrates how to create a chart through series.
A complete working example to create a chart through series in C# is present on this GitHub page. Creating a chart SheetThe following code snippet shows how to create a chart sheet (separate sheet).
A complete working example to create a chart worksheet in C# is present on this GitHub page. Creating Custom ChartsA custom chart can be created by using different types of charts for different data series. For example, you can use a column chart for the first data series and a line chart for the second series. As a result you will have a column chart, combined with a line chart. This sample also explains different chart properties like Set Data Range to Chart
Name the Chart and Set Chart Title
Different Primary Value Axis Properties
Different Secondary Value Axis Properties
Different Secondary Category Axis Properties
Different Chart Series Fill Properties
Different Marker Properties
Different Legend Properties
The complete code snippet illustrating the above options along with creating custom charts is shown below.
A complete working example to create a custom chart in C# is present on this GitHub page. Remove a chartThe following code snippet shows how to remove the chart from the worksheet using Remove method.
A complete working example to remove chart in C# is present on this GitHub page. Chart Appearance SettingsThe appearance of a chart can be modified according to the convenience and usage. Elements of ChartThe following screen shot shows the elements of chart.
Chart Area AppearanceThe following code snippet shows how to modify the appearance of the chart area.
Plot Area AppearanceThe following code snippet shows how to modify the appearance of the plot area.
Data Labels AppearanceThe following code snippet illustrates how to modify the appearance of data labels.
Series AppearanceThe following code snippet illustrates how to modify the appearance of chart series.
The complete code snippet illustrating the above options is shown below.
A complete working example for chart appearance in C# is present on this GitHub page. Font settings for chart legend and data labelsEssential XlsIO allows you to set the desired font to legend and series data labels for legend through TextArea in IChartLegend. Similarly, desired font for data labels of chart series can be set through DataLabels in IChartDataPoints. The font style includes font name, font size and font color which are set through FontName, Size and Color properties respectively. Refer the following complete code snippets.
A complete working example for chart font settings in C# is present on this GitHub page. Border Style for Chart SeriesA unique border style like line color, line weight, and line pattern can be set for each chart series. Also, these settings can be made for each data point in the chart series. Refer the following complete code snippets.
A complete working example for chart series border settings in C# is present on this GitHub page. Adjust space between chart barsSpaces between chart bars are of two types.
Essential XlsIO allows you to adjust the space between chart bars using Overlap and GapWidth properties of IChartFormat interface. Refer the following complete code snippets.
A complete working example for adjusting space between chart bars in C# is present on this GitHub page. Hide Chart GridlinesExcel chart consists of two types of gridlines such as major gridlines and minor gridlines. Major gridlines represent the main values in the axis and minor gridlines represent possible values between two adjacent axis values. You can show or hide these gridlines using HasMajorGridlines and HasMinorGridlines of IChartAxis interface. Essential XlsIO supports formatting of gridlines as well through the MajorGridlines and MinorGridlines of IChartAxis. Refer the following complete code snippets.
A complete working example to hide chart gridlines in C# is present on this GitHub page. Add High-Low LinesHigh-low lines are used in Excel line charts and stock charts that connect the highest and lowest points of a category. The following code snippet shows how to add High-low lines in a stock chart.
A complete working example to show high low lines of chart in C# is present on this GitHub page. The following screen shot shows the high-low lines in the line chart. Add Drop LinesDrop lines are used in Excel area and line charts that helps viewers to determine the data point down to the horizontal axis. The following code snippet shows how to add Drop lines in a stock chart.
A complete working example to add drop lines of chart in C# is present on this GitHub page. The following screen shot shows the drop lines in the line chart. Add Series LinesSeries lines are used in Excel stacked bar and column charts that create lines from one bar to
another that connect every data point in a series. The following code snippet shows how to add series lines in a pie chart.
A complete working example to add series lines of chart in C# is present on this GitHub page. The following screen shot shows the series lines in the stacker bar chart. Fill Chart Elements with PictureChart elements helps in modifying the chart appearance. The different chart elements are plot area, chart area, axes, titles, data points, legend, and data labels. Fill plot area with picturePlot area holds the data series of a chart. This plot area can be filled with solid colors, texture, picture, and pattern. Essential XlsIO allows you to fill plot area with picture using the UserPicture of IFill interface. Refer to the following complete code snippets.
A complete working example to fill plot area with picture in C# is present on this GitHub page. Fill chart area with pictureChart area holds plot area, legend, axes, data table, and so on. This chart area can be filled with solid colors, texture, picture, and pattern. Similar to plot area, chart area can be filled with picture using UserPicture of IFill interface. Refer to the following complete code snippets.
A complete working example to fill chart area with picture in C# is present on this GitHub page. Applying 3D FormatsThe following code example explains how to apply 3D settings such as rotation, side wall, back wall, and floor settings.
A complete working example for 3D chart formats in C# is present on this GitHub page. Customizing chart and Chart ElementsPositioning ChartChart can be positioned by specifying row and column indexes. The following code samples illustrates how to position a chart in a worksheet.
Positioning Chart ElementsThe following code examples illustrate how to position the chart elements.
Resizing ChartThe following code sample illustrates how to resize a chart in a worksheet.
Resizing Chart ElementsThe following code examples illustrate how to resize chart elements such as plot area and legend.
Chart with transparent backgroundThe following code example explains how to apply transparency to chart area.
The complete code snippet illustrating the above options is shown below.
A complete working example explaining different chart elements in C# is present on this GitHub page.
Explode a Pie ChartEssential XlsIO allows you to explode either all data points at a single explosion value or each data point at different explosion using Percent of IChartSerieDataFormat interface. You can either create a pie chart and then explode it or directly create an exploded pie chart using XlsIO. Selecting Refer the following complete code snippets.
A complete working example to explode a pie chart in C# is present on this GitHub page. Add Picture to Chart and assign HyperlinkEssential XlsIO supports assigning hyperlink to the picture added in a chart in the Excel workbook. To achieve this, create a chart in workbook and add picture to the chart using AddPicture of IPictures interface. You can assign hyperlink to the picture using Add property of IHyperlinks interface. Refer to the following complete code snippets.
A complete working example for picture hyperlink in chart in C# is present on this GitHub page.
Add DataTable to ChartData table beneath the chart clearly represents the chart content in table format. While creating a chart, the data table is hidden, and the option should be manually enabled to view it. Essential XlsIO supports adding data table using HasDataTable of IChart interface. Enabling this property adds the data table beneath the chart. Refer the following complete code snippets.
A complete working example to add data table in chart in C# is present on this GitHub page. SparklineSparkline is a small chart in a worksheet cell that provides a visual representation of data. Sparkline Creation Using XlsIOXlsIO provides support for creation, modification and removal of Sparklines.
Following code example illustrates how to create Sparklines by using XlsIO.
A complete working example to create sparklines in C# is present on this GitHub page. Modifying an existing spark lineXlsIO provides an option to edit the data of existing Sparklines. The following code snippet shows how to achieve this.
A complete working example to edit sparklines in C# is present on this GitHub page. Removing SparklinesXlsIO provides an API to remove sparklines from the sparkline group and also the sparkline group from the worksheet. This is illustrated in the following code.
A complete working example to remove sparklines in C# is present on this GitHub page.
Excel 2016 ChartsEssential XlsIO supports creating and manipulating new and modern chart types such as waterfall, histogram, pareto, box and whisker, tree map, and sunburst, all of which are introduced in Microsoft Excel 2016. FunnelFunnel charts show values across multiple stages in a process. Following code example illustrates how to create Funnel chart.
The input template can be downloaded here. A complete working example to create funnel chart in C# is present on this GitHub page. The following screen shot shows the output of above code. Box and WhiskerBox and Whisker chart shows distribution of data into quartiles, highlighting the mean and outliers. Box and Whisker charts are most commonly used in statistical analysis. Following code example illustrates how to create Box and Whisker chart.
The input template can be downloaded here. A complete working example to create box and whisker chart in C# is present on this GitHub page. The following screen shot shows the output of above code. WaterfallWaterfall chart helps to quickly understand the finances of business owners by viewing profit and loss statements. With a Waterfall chart, you can quickly illustrate the line items in your financial data and get a clear picture of how each item is impacting your bottom line. Following code example illustrates how to create Waterfall chart.
The input template can be downloaded here. A complete working example to create waterfall chart in C# is present on this GitHub page. The following screen shot shows the output of above code. HistogramHistogram shows the frequencies within a distribution. Each column of the chart is called a bin, which can be changed further to analyze the data. Following code example illustrates how to create Histogram.
The input template can be downloaded here. A complete working example to create histogram chart in C# is present on this GitHub page. The following screen shot shows the output of above code. ParetoPareto is a sorted histogram where columns sorted in descending order and a line representing the cumulative total percentage. Following code example illustrates how to create Pareto chart.
The input template can be downloaded here. A complete working example to create pareto chart in C# is present on this GitHub page. The following screen shot shows the output of above code. TreemapTreemap provides a hierarchical view of data as clustered rectangle with a specific weighted attribute determining the size of the rectangle. Following code example illustrates how to create Treemap chart.
The input template can be downloaded here. A complete working example to create treemap chart in C# is present on this GitHub page. The following screen shot shows the output of above code. SunburstSunburst provides a hierarchical view of data where each level of the hierarchy is represented by one ring or circle with the innermost circle as the top of the hierarchy. Following code example illustrates how to create Sunburst chart.
The input template can be downloaded here. A complete working example to create sunburst chart in C# is present on this GitHub page. The following screen shot shows the output of above code.
Supported Chart TypesThe following chart types are supported in XlsIO.
|