Adding checkbox with legends in chart in Excel worksheet in .NET

Aspose.Cells Product Family
1

Hi,

I am using aspose to generate charts in excel. My requirement is that the legend should have a check box next to it to show hide some of the graphs…

Please suggest how to go about it.

Thanks
Arihant
2

Hi,


I am not sure about your requirements. If you need dynamic interactive charts based on some conditions, please see the simple example on how to create an interactive chart by using CheckBox. I have also attached the input and output files for your reference here.

Sample code:
Workbook workbook = new Workbook(“e:\test2\Template_ChartByCheckBox.xls”);

Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;
//Add Checkboxes
CheckBoxCollection checkboxes = sheet.CheckBoxes;
int index = checkboxes.Add(3, 1, 16, 16);
Aspose.Cells.Drawing.CheckBox checkbox = checkboxes[index];
checkbox.LinkedCell = “B3”;
checkbox.Value = true;

index = checkboxes.Add(3, 2, 16, 16);
Aspose.Cells.Drawing.CheckBox checkbox2 = checkboxes[index];
checkbox2.LinkedCell = “C3”;
checkbox2.Value = true;

index = checkboxes.Add(3, 3, 16, 16);
Aspose.Cells.Drawing.CheckBox checkbox3 = checkboxes[index];
checkbox3.LinkedCell = “D3”;
checkbox3.Value = true;

//Set formulas
Aspose.Cells.Cell cell = cells[“E5”];
cell.Formula = “=IF(B$3,B5,”")";
cells[“E6”].SetSharedFormula("=IF(B$3,B6,NA())", 4, 1);
cell = cells[“F5”];
cell.Formula = “=IF(C$3,C5,”")";
cells[“F6”].SetSharedFormula("=IF(C$3,C6,NA())", 4, 1);

cell = cells[“G5”];
cell.Formula = “=IF(D$3,D5,”")";
cells[“G6”].SetSharedFormula("=IF(D$3,D6,NA())", 4, 1);

//Calculate formulas in the sheet(s)
workbook.CalculateFormula();
int chartIndex = sheet.Charts.Add(ChartType.LineWithDataMarkers, 10, 0, 27, 9);
Chart chart = sheet.Charts[chartIndex];
//Set Chart’s MajorGridLines invisib;e
chart.CategoryAxis.MajorGridLines.IsVisible = false;
chart.PlotArea.Border.IsVisible = false;
//Set Properties of chart title
chart.Title.Text = “Chart by Checkbox”;
chart.Title.TextFont.Color = Color.Red;
chart.Title.TextFont.IsBold = true;
chart.Title.TextFont.Size = 12;

//Set properies to nseries
chart.NSeries.CategoryData = “A6:A9”;

//Set NSeries Data
chart.NSeries.Add("‘Chart By Check Box’!E6:G9", true);

//Set Nseries color varience to True
chart.NSeries.IsColorVaried = false;
chart.ShowLegend = true;


workbook.Save(“e:\test2\Output_ChartByCheckBox.xls”);


Please get reference from the above example and this you may write your own code accordingly for your desired charts.


If you still have any confusion, kindly create a chart in MS Excel manually and save the file, attach the Excel file here to show us your needs, we will check it soon.


Thanks for your understanding!
3

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please provide us your simple expected output xls/xlsx file which you can create manually using Ms-Excel.

We will look into it and provide you a sample code how to achieve your expected output.

Please also see how to add a check box from the following article for more help.


4

Thanks for the code. but what i am looking for is a way to add the checkbox within the graph along with the legend… This creates legend in a cell and not within the chart


Attached sample
5

Hi,

Thanks for your image file.

Please also provide the actual excel file of which you have taken the image.

We need your file so that we could load it using Aspose.Cells and analyze its various objects and properties and come up with a sample code that could achieve the same output using Aspose.Cells API(s).

6

Attached sample excel…

7

Hi,


Thank you for providing the supporting material.

After analyzing the excel file, we have found that the Checkboxes are not part of the Chart. You can observe this if you resize the chart object; the position of checkboxes remain unchanged, which means that these are not part of the Chart, but chart sheet. You can observe this from the attached screenshot.

I tried to create such a chart with Checkboxes, as part of it, manually using MS Excel, but couldn’t get success.
8

Thanks for the update, I am able to add that the checbox, howeverm wanted to check if it is possible to define the width of the checkbox automatically based on the text it contains.


Please suggest
9

Also, how do i set the back color of the check box same as the legend color?

10

Hi,

Please use the following properties to control the color and dimensions of Checkbox control.

  1. Checkbox.FillFormat.ForeColor

  2. Checkbox.Width

C#

Checkbox.FillFormat.ForeColor = Color.Blue;

Checkbox.Width = 200;


11

Hi,


Thank you for using Aspose.Cells.

To change the fill color of a combo box as the legend color, please see the following lines of code:

<span style=“font-size:
10.0pt;font-family:“Courier New”;mso-no-proof:yes”>checkbox.IsFilled = true;

<span style=“font-size:
10.0pt;font-family:“Courier New”;color:#2B91AF;mso-no-proof:yes”>MsoFillFormatfillformat
= checkbox.FillFormat;

<span style=“font-size:
10.0pt;font-family:“Courier New”;mso-no-proof:yes”>fillformat.ForeColor =
chart.Legend.Area.ForegroundColor;


12

Please find attached the excel files. What is needed here is the output shown in modified.xlsx while what I am getting is original.xlsx.

So basically the check box are generated based on the different legend of the graph. So need a way out to auto size the graph to show all the check boxes, and auto size the check box as well.

Also, need a way to back fill the checkbox cell with the same color as the respective chart line legend.

Code below.

 int sheetIndex = 0;

Worksheet workingWorksheet;

int chartIndex = 0;

                        <span style="color:green;">//Adding a new worksheet to the Excel object</span>
                        sheetIndex = workingExcelFile.Worksheets.Add();

                        <span style="color:green;">//Obtaining the reference of the newly added worksheet by passing its sheet index</span>
                        workingWorksheet = workingExcelFile.Worksheets[sheetIndex];

                        <span style="color:green;">//Adding a chart to the worksheet</span>
                        <span style="color:green;">//Since this OVER VIEW Graph, it's assumed to be Line chart</span>
                        chartIndex = workingWorksheet.Charts.Add(<span style="color:#2b91af;">ChartType</span>.Line, 1, 1, 35, 15);

                        workingWorksheet.Name = max.WorkSheetName;

                        <span style="color:#2b91af;">Console</span>.WriteLine(<span style="color:#a31515;">"Added a chart -- "</span> + workingWorksheet.Name);

                        <span style="color:green;">//Accessing the instance of the newly added chart</span>
                        <span style="color:#2b91af;">Chart</span> chart = workingWorksheet.Charts[chartIndex];</pre></div><div><pre style="font-family: Consolas; font-size: 13px; background-color: white; background-position: initial initial; background-repeat: initial initial; "><span style="color:blue;">int</span> upperLeftRow = 34;
                        <span style="color:blue;">int</span> upperLeftColumn = 4;

                        <span style="color:blue;">foreach</span> (<span style="color:blue;">var</span> range <span style="color:blue;">in</span> max.RangeToDraw) {
                            <span style="color:blue;">int</span> nSeriesIndex = chart.NSeries.Add(range.Range, <span style="color:blue;">true</span>);
                            <span style="color:blue;">if</span> (range.ChartTypeToDraw.Equals(<span style="color:#2b91af;">ChartType</span>.Line)) {
                                <span style="color:blue;">if</span> (!range.IsSecondaryAxis) {
                                    <span style="color:green;">//At present Graph Type is always Histogram</span>
                                    <span style="color:blue;">if</span> (!<span style="color:blue;">string</span>.IsNullOrEmpty(max.GraphType)) {
                                        chart.NSeries[nSeriesIndex].Type = !range.IsSecondaryAxis ? <span style="color:#2b91af;">ChartType</span>.AreaStacked : <span style="color:#2b91af;">ChartType</span>.ScatterConnectedByLinesWithoutDataMarker;
                                        chart.NSeries[nSeriesIndex].XValues = range.XaxisValues;
                                        chart.NSeries[nSeriesIndex].Line.Weight = <span style="color:#2b91af;">WeightType</span>.HairLine;
                                        chart.Style = 26;
                                    } <span style="color:blue;">else</span> {
                                        chart.NSeries[nSeriesIndex].Type = <span style="color:#2b91af;">ChartType</span>.ScatterConnectedByLinesWithoutDataMarker;
                                        chart.NSeries[nSeriesIndex].XValues = range.XaxisValues;
                                        chart.NSeries[nSeriesIndex].Line.Weight = <span style="color:#2b91af;">WeightType</span>.HairLine;
                                    }
                                } <span style="color:blue;">else</span> {
                                    chart.NSeries[nSeriesIndex].Type = <span style="color:#2b91af;">ChartXMLProcessing</span>.FilePrefixInUse.Equals(<span style="color:#a31515;">"storage"</span>) ? <span style="color:#2b91af;">ChartType</span>.Line : <span style="color:#2b91af;">ChartType</span>.ScatterConnectedByLinesWithoutDataMarker;
                                    chart.NSeries[nSeriesIndex].PlotOnSecondAxis = <span style="color:blue;">true</span>;
                                    chart.NSeries[nSeriesIndex].Line.IsVisible = <span style="color:blue;">true</span>;
                                    chart.NSeries[nSeriesIndex].Line.Weight = <span style="color:#2b91af;">WeightType</span>.HairLine;
                                    chart.NSeries[nSeriesIndex].Line.Color = <span style="color:#2b91af;">Color</span>.LightGreen;
                                    chart.NSeries[nSeriesIndex].XValues = range.XaxisValues;
                                }
                                chart.NSeries[nSeriesIndex].Name = <span style="color:blue;">string</span>.IsNullOrEmpty(max.CounterName) ? range.InstanceName : max.CounterName;
                                <span style="color:green;">// Adding CheckBox</span>
                                <span style="color:blue;">if</span> (<span style="color:#2b91af;">ChartConstants</span>.IsAnalysisCharts) {
                                    AddCheckBoxToWorkSheet(chart, workingWorksheet, max, range, upperLeftRow, upperLeftColumn);
                                    upperLeftColumn++;
                                    <span style="color:blue;">if</span> (upperLeftColumn > 10) {
                                        upperLeftColumn = 4;
                                        upperLeftRow++;
                                        <span style="color:green;">// chart.ChartObject.UpperLeftRow = 1;</span>
                                        <span style="color:green;">// chart.ChartObject.LowerRightRow++;</span>

                                    }
                                }
                            }
                        }</pre></div><div><br></div><div><pre style="font-family: Consolas; font-size: 13px; background-color: white; background-position: initial initial; background-repeat: initial initial; "><span style="color:blue;">private</span> <span style="color:blue;">static</span> <span style="color:blue;">void</span> AddCheckBoxToWorkSheet(<span style="color:#2b91af;">Chart</span> chart, <span style="color:#2b91af;">Worksheet</span> workingWorksheet, <span style="color:#2b91af;">FilterDataRangeToDraw</span> max, <span style="color:#2b91af;">InstanceRange</span> item, <span style="color:blue;">int</span> upperLeftRow, <span style="color:blue;">int</span> upperLeftColumn) {
        <span style="color:blue;">string</span> name = <span style="color:blue;">string</span>.IsNullOrEmpty(max.CounterName) ? item.InstanceName : max.CounterName;
        
        <span style="color:blue;">int</span> index = workingWorksheet.CheckBoxes.Add(upperLeftRow, upperLeftColumn, 25, 200);

        <span style="color:green;">//Get the checkbox object.</span>
        <span style="color:#2b91af;">CheckBox</span> checkbox = workingWorksheet.CheckBoxes[index];

        <span style="color:green;">//Set its text string.</span>
        checkbox.Text = name;
        checkbox.CheckedValue = <span style="color:#2b91af;">CheckValueType</span>.Checked;
        checkbox.Font.Name = <span style="color:#a31515;">"Calibri"</span>;
        checkbox.Font.Size = 10;

        <span style="color:green;">//Set cell as a linked cell for the checkbox. </span>
        <span style="color:blue;">string</span> sheetToRefer = item.Range.Substring(0, (item.Range.IndexOf(<span style="color:#a31515;">'!'</span>) + 1));
        checkbox.LinkedCell = sheetToRefer + item.ColNameinSheet + (max.RowCount + 2); <span style="color:green;">//_aggreate!B171</span>
    }</pre></div>
13

Hi,

Thanks for your sample code and showing us the actual and expected output files.

I have looked into your files. You are doing great.

In order to increase the width of the check box, I found you need to set the column width, for example, when you increase the width of the column F, your checkbox width and spacing also gets increased.

You can set the column width using

worksheet.Cells.SetColumnWidth() and related methods

Also, you need to adjust your Chart and Plot area, I found, you need to shrink your plot area and enlarge the chart area. You can control these using the following properties.

//Enlarge the chart area
chart.ChartArea.Width;
chart.ChartArea.Height;

//Shrink the plot area
chart.PlotArea.Width;
chart.PlotArea.Height;

14

Thanks made some progress, but couple of further questions:


1. how do i auto set the size of the checkbox column based on the text it contains.
2. How do i set the column width based on the checkbox width
3. Able to increase the chart area, but how to auto set the width based on the content
15
aryhant:
Thanks made some progress, but couple of further questions:

1. how do i auto set the size of the checkbox column based on the text it contains.
2. How do i set the column width based on the checkbox width
3. Able to increase the chart area, but how to auto set the width based on the content
Hi,

1. You cannot autoset it, but what you can do is that you insert some text in a column and then autosize the column using Worksheet.AutoFitColumn() method, then get the width of the column using Cells.GetColumnWidth() and set then set the width of your check box accordingly.

2. Please make use of Cells.Get/SetColumnWidth() methods and CheckBox.Width property

3. You need to calculate how much space your contents will take. Only then you can set the width, you cannot autoset the width. This feature is also not available in Ms-Excel.