Hi Amjad,
I’m working on a requirement and need to get your supports/suggestions. Pls refer to my screenshot in attached file for more detail and help me to answer/ clarify questions as below:
1. Look at to the left side you will see a rectangle with label is 1.
My ques: How to set background image for a cell/ range of cells?
2. Look at to the right side you will see a rectangle with label is 2.
My ques: How to combine chart with table?
3. How to calculate the Average of each section/ group data in the table then showing chart for each section in Average column?
The above is my questions and need to get your suggestions. Please help me to answer them if possible. Dont forget to keep my screenshot secret.
Thank you for your support.
Hi,
1) I think you may insert the picture into the merged cell. All you need to already have the corresponding image with specific size, now you may merge the cells in a column accordingly, finally insert the picture into that cell (merged cell).
See the document on how to merge cells:
See the document on how to insert a picture into the cells:
2) Well, you may create charts along with source data, you have to specify the chart’s location and size accordingly and create the chart based on the source data range. You need to specify the proper values for the parameters e.g
upperLeftRow, upperLeftColumn, lowerRightRow, lowerRightColumn etc.
See the line of code for it, we have provided specific numeric values for these parameters:
int chartIndex = worksheet.Charts.Add(ChartType.BarStacked, 3, 5, 25, 12);
See the topics on creating the charts:
3) To show percentage values for the source range data for the chart bars, you may use DataLabels.ShowPercentage boolean attribute, see the sample code segment below:
//..........
Aspose.Cells.Charts.DataLabels datalabels;
for (int i = 0; i < chart.NSeries.Count; i++)
{
datalabels = chart.NSeries[i].DataLabels;
datalabels.ShowPercentage = true;
datalabels.NumberFormat = "0%";
}
We also request you to kindly create your desired chart in MS Excel manually with your desired labels on based on your source data range, save the file and post it here, we will check it on how to do it using Aspose.Cells APIs.
Thank you.
Hi,
I created an Excel file manually and attached to here. Please check and help me answer some more questions in my screenshot.
Note:
- Manually.png : Captured from my manual excel file.
- My_Requirement.png: Captured from my requirement file.
Thank you.
Hi,
1) You may merge the cells area i.e. A6:B18 to make it A6 and then insert image into it. Currently you have merged A6:A18 where B6:B18 range is blank and extra which is not merged to it.
2) You may apply Thick border’s line style to the cells in the columns e.g E, F, G and so on, etc.
See the sample code below to use your template Excel file.
e.g
Sample code:
Workbook workbook = new Workbook(“e:\test2\Table_WithChart_789.xls”);
Cells cells = workbook.Worksheets[0].Cells;
//apply thick border style to a range of cells.
Range range = cells.CreateRange(“E1”, “K42”);
Style stl = workbook.Styles[workbook.Styles.Add()];
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.LeftBorder].Color = Color.White;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.RightBorder].Color = Color.White;
StyleFlag flg = new StyleFlag();
flg.Borders = true;
range.ApplyStyle(stl, flg);
workbook.Save(“e:\test2\out1_Table_WithChart_789.xls”);
3) You can move the chart to downwards accordingly or you have to specify the chart location when you are creating the chart in the sheet, there is no defined rule in MS Excel for it as there is one chart with multiple data series. See the sample code below on how to move the chart accordingly.
e.g
Sample code:
Workbook workbook = new Workbook(“e:\test2\Table_WithChart_789.xls”);
Cells cells = workbook.Worksheets[0].Cells;
Chart chart = workbook.Worksheets[0].Charts[0];
chart.Move(4,11,45,14);
workbook.Save(“e:\test2\out1_Table_WithChart_789.xls”);
Thank you.
Hi,
Thanks for your quick reply. I think we’re not on the same page about item #1. I only want to merge the cells area A6:A18 and then insert the image into it. The column B is leaved blank.
When using the APIs to generate the excel file, I cannot fit the image into the area A6:A18. Please see my attached picture for more detail.
Item #2 and #3: I will work on it asap and let you know the result.
Thank you.
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
Once, you insert image into any particular cell, please adjust its width and height to make it fit using worksheet.Cells.SetColumnWidth and worksheet.Cells.SetRowHeight methods.