Add TextBox to chart in Excel spreadsheet using AddTextBoxInChart

I want to place a text box in a chart. The chart has two axis, the x-axis represents days of the month and the y-axis represents temperatures. When the temperature of a particular day goes over a certain value, I want to insert a text box above the point in the chart. How do I tell the AddTextBoxInChart function where to be located? Is there a way to get the coordinates of the data point and then send that to AddTextBoxInChart? Thanks.
This message was posted using Aspose.Live 2 Forum

Hi,

Well, there is no such API available to determine the coordinates of a datapoints in a chart. You may have to do some manual coding to set the textbox at a specific position. Please see the following thread for sample code which may help you in your desired operation.

https://forum.aspose.com/t/66169

Thank you & Best Regards,

Thank you for the reply. I am having some trouble with the AddTextBoxInChart method.

From what I read in the API Reference, the parameters of AddTextBoxInChart are top, left, height and width. However, changes to the "left" parameter are affecting the height of the text box in my chart.

For example, if the AddTextBoxInChart parameters in my code are (0, 0, 100, 1000), I see a text box that is located in the upper left corner and is approximately 1.5 inches tall by 3 inches wide. Now, if I change the parameters to (0, 500, 100, 1000) -- so the only thing I have changed is the "left" parameter -- then the text box height grows to 2". The width is still about 3".

Why would changing the "left" parameter of AddTextBoxInChart affect the height of the text box?

Hi,

We have found your mentioned issue after an initial test. We will look into it and get back to you soon. Your issue has been registered in our internal issue tracking system with issue id CELLSNET-14475.

Thank You & Best Regards,

I am writing to let you know that I believe I have uncovered another bug that is likely related to this issue.

I tried the following code, hoping that it would position the text boxes in the graph in the proper locations:

Dim myTB As Aspose.Cells.TextBox = myChart.Shapes.AddTextBoxInChart(0, 0, 0, 0)

myTB.Text = sText

myTB.Left = dLeft

myTB.Top = dTop

myTB.TextFrame.AutoSize = True

The problem is with the horizontal positioning of the text boxes. The text boxes cannot be positioned beyond the mid-point of the graph.

Do you see the same behavior?

Hi,

Please use LeftInShape & TopInShape properties of the textbox as per your requirement. Please see the following sample code in this regard:

Dim workbook As New Workbook()

Dim cells As Cells = workbook.Worksheets(0).Cells

cells("A1").PutValue(1)

cells("A2").PutValue(2)

cells("A3").PutValue(3)

Dim charts As Charts = workbook.Worksheets(0).Charts

Dim index As Integer = charts.Add(ChartType.Column, 5, 0, 15, 5)

Dim chart As Chart = charts(index)

chart.NSeries.Add("A1:A3", True)

chart.NSeries(0).DataLabels.IsValueShown = True

Dim myTB As Aspose.Cells.TextBox = chart.Shapes.AddTextBoxInChart(0, 0, 100, 100)

myTB.Text = "My Text Box"

myTB.LeftInShape = 3500

myTB.TopInShape = 2500

myTB.TextFrame.AutoSize = True

workbook.Save("C:\dest.xls")


Thank You & Best Regards,

Thank you for your response. The code you suggested does a better job of handling the text box size. But now there is a problem with positioning. It appears that it is impossible to place a text box beyond the midpoint of the graph. (See attached.)

If I understand correctly, if I set the text box Left at 4000 pixels, it should be located at the far right of the graph. That is: myTB.LeftInShape=4000. However, what actually happens is that the text box is placed in the middle of the graph. If I try to set LeftInShape or TopInShape to a value greater than 4000, I receive an error.

Hi,

Well, I tried the following sample code and it works fine (resultant file attached). Please share your code to reproduce the issue, we will check it soon.

Dim workbook As New Workbook()

Dim cells As Cells = workbook.Worksheets(0).Cells

cells("A1").PutValue(1)

cells("A2").PutValue(2)

cells("A3").PutValue(3)

Dim charts As Charts = workbook.Worksheets(0).Charts

Dim index As Integer = charts.Add(ChartType.Column, 5, 0, 15, 5)

Dim chart As Chart = charts(index)

chart.NSeries.Add("A1:A3", True)

chart.NSeries(0).DataLabels.IsValueShown = True

Dim myTB As Aspose.Cells.TextBox = chart.Shapes.AddTextBoxInChart(0, 0, 100, 100)

myTB.Text = "My Text Box"

myTB.LeftInShape = 3800

myTB.TopInShape = 3800

myTB.TextFrame.AutoSize = True

workbook.Save("C:\dest.xls")

Thank You & Best Regards,

I used your code and got different results. (See attached.) Is it possible that the code produces different results in different versions of Excel?

I am using Excel 2002 SP3.

It appears that different versions of Excel display these text boxes differently.

I opened the same dest.xls file that I created with your code using both Excel 2002 and Excel 2007. The attached pictures show the difference in how they display the text boxes.

I need to be able to open the spreadsheet in Excel 2002 with the proper text box rendering.

Thanks.

I have found a "work around" for my code. I have found that if I set the Height and Width properties after setting AutoSize, the text box appears normally (even in Excel 2002).

So, applying my work around to your code, it would look like this:

Dim workbook As New Workbook()

Dim cells As Cells = workbook.Worksheets(0).Cells

cells("A1").PutValue(1)

cells("A2").PutValue(2)

cells("A3").PutValue(3)

Dim charts As Charts = workbook.Worksheets(0).Charts

Dim index As Integer = charts.Add(ChartType.Column, 5, 0, 15, 5)

Dim chart As Chart = charts(index)

chart.NSeries.Add("A1:A3", True)

chart.NSeries(0).DataLabels.IsValueShown = True

Dim myTB As Aspose.Cells.TextBox = chart.Shapes.AddTextBoxInChart(0, 0, 100, 100)

myTB.Text = "My Text Box"

myTB.LeftInShape = 3800

myTB.TopInShape = 3800

myTB.TextFrame.AutoSize = True

myTB.Height = 100

myTB.Width = 100

workbook.Save("C:\dest.xls")