becklighter

I am building an excel file from scratch. this includes charts of data. In this particular case I need to remove the borders from the chart, but it doesn't appear to work correctly.

// The next three lines are an attempt to remove the border, the fourth shows that I am controlling the chart
((Excel.ChartObject)oSheet.ChartObjects("Chart 1")).Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
((Excel.ChartObject)oSheet.ChartObjects("Chart 1")).Interior.ColorIndex = 2;
((Excel.ChartObject)oSheet.ChartObjects("Chart 1")).Border.ColorIndex = 2;
// After attempting above I use the following code which works to round the corners
((Excel.ChartObject)oSheet.ChartObjects("Chart 1")).RoundedCorners = true;

I don't want to round the corners, I want to remove the border. While the code compiles, when I open the generated excel file the border remains.

If anyone knows how to remove the border please let me know.

Thank you in advance.


Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

Dennis Wallentin

Hi,

If You change the following


((Excel.ChartObject)oSheet.ChartObjects("Chart 1")).Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;

to:

((Excel.ChartObject)oSheet.ChartObjects("Chart 1")).Border.LineStyle = 0;

will that solve the issue

That solution works as expected in VB as the following example shows (see the sub Create_Chart_Report: http://www.excelkb.com/article.aspx id=10218







Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

becklighter

Thank you for the advice, but I have tried that to no effect. It still does not work properly.




Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

Cindy Meister

Which version of Excel are you working with




Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

becklighter

Sorry, I know that the version is important. The version of Excel that I am using is 2003.




Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

Cindy Meister

Hi Becklighter

I think the problem comes from applying a ColorIndex to the border after setting the line style to be "none". It seems doing that makes Excel think there is a border, even if it has no line. Remove that statement and things may behave differently






Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

becklighter

Thanks for the suggestion, but that did not do it. I still have black borders.




Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

Cindy Meister

Hi Becklighter

Well, the next logical thing to check would be whether the lines you're trying to remove are really ChartObject.Border lines or something else.

If you open the Excel file directly in Excel, to work with it manually, can you remove the borders What commands do you use






Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

becklighter

This is the macro that I recorded to do the same thing. This works. I have trimmed it down to two lines that seem to need to be there. I have to activate the sheet, and then set the LineStyle to zero.

Sub Macro1()
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 1
.LineStyle = 0
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = 1
End With
End Sub




Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

Dennis Wallentin

Hi,

Just for the record. It works as expected in VBA, VB.NET and with VB.NET & VSTO. Unfortunately my C# knowledge is not good so I can't give any further input.






Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

Cindy Meister

<<Just for the record. It works as expected in VBA, VB.NET and with VB.NET & VSTO. Unfortunately my C# knowledge is not good so I can't give any further input.>>

I did my testing yesterday in C#, so I know it does work in C# (pasted my code below). And that's also where I noticed that, if I set a ColorIndex after changing the line style (especially to "none"), Excel won't change the setting anymore.

That's why I wanted to be sure Becklighter was actually addressing the correct borders, when he says changing this made no difference.

Since, beyond that, I can't reproduce the problem, I'm out of ideas...

private void ucbtnTest_Click(object sender, EventArgs e)
{
//Format chart with a border
object objChart = "Chart 7";
Excel.Worksheet sheet = Globals.Sheet1.InnerObject;
Excel.ChartObject oChart = sheet.ChartObjects(objChart) as Excel.ChartObject;
//For some odd reason, the PIA is passing the result xlContinuous (1) as a double (1.0)
//So I do this to make sure the comparision will always work
object oLineStyle = oChart.Border.LineStyle;
int lineStyle;
string s = oLineStyle.ToString();
Int32.TryParse(s, out lineStyle);
if (lineStyle == (int) Excel.XlLineStyle.xlContinuous)
{
oChart.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
//oChart.Border.ColorIndex = 2;
oChart.RoundedCorners = false;
}
else
{
oChart.Border.LineStyle = Excel.XlLineStyle.xlContinuous;
oChart.Border.ColorIndex = 1;
oChart.RoundedCorners = true;
}
}






Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

becklighter

I am going to look at your code and see if I can get it to work with what I am doing. If not I will post an example of the code I am using to create my chart.

Thanks for all the help.




Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

becklighter

Sorry it has taken me so long to get back to you, other higher priority projects came up.

I have tried adapting my code to use a similar approach to what you are doing, but it doesn't appear to help.

here is how I create the chart:
// Create chart
oChart = (Excel._Chart)oWB.Charts.Add( Missing.Value, Missing.Value, Missing.Value, Missing.Value );

oRng = oSheet.get_Range("S2", "U13");
oChart.ChartWizard( oRng, Excel.XlChartType.xlLine, Missing.Value, Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value );
oSeries = (Excel.Series)oChart.SeriesCollection(1);
oSeries.XValues = oSheet.get_Range("R2", "R13");
for(int iRet = 1; iRet <= 3; iRet++){
oSeries = (Excel.Series)oChart.SeriesCollection(iRet);
if(iRet == 1)
oSeries.Name = "=\"title 1\"";
if(iRet == 2)
oSeries.Name = "=\"title 2\"";
if(iRet == 3)
oSeries.Name = "=\"title 3\"";
}

oChart.Activate();
oChart.PlotArea.Height = 1000;
oChart.PlotArea.Width = 898;
oChart.PlotArea.Top = -100;
oChart.PlotArea.Left = 5;
oChart.PlotArea.Interior.ColorIndex = 2;
oChart.ChartArea.Font.Name = "Arial";
oChart.ChartArea.Font.Size = 8;
oChart.HasTitle = true;
oChart.ChartTitle.Caption = "Chart Title";
oChart.ChartTitle.Font.Size = 8;
oChart.ChartTitle.Font.Name = "Times New Roman";
oChart.ChartTitle.Font.Bold = true;
oChart.ChartTitle.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
oChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
oChart.Legend.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
oChart.ChartArea.AutoScaleFont = Microsoft.Office.Core.MsoTriState.msoFalse;
oChart.ChartArea.Interior.ColorIndex = 2;
oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oSheet.Name );
oRng = oSheet.get_Range("D31","D31");
oSheet.Shapes.Item("Chart 1").ScaleWidth(.80f, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
oSheet.Shapes.Item("Chart 1").ScaleHeight(.65f, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
oSheet.Shapes.Item("Chart 1").Top = (float)((double)oRng.Top + 3.0);
oSheet.Shapes.Item("Chart 1").Left = (float)((double)oRng.Left + 36.0);
((Excel.ChartObject)oSheet.ChartObjects("Chart 1")).Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
// End create chart

I really need to eliminate the borders, so if I need to change the way I do things I will thanks.




Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

Cindy Meister

Hi becklighter

Just letting you know I've seen this and will pick it up again when I get a moment later this week if you still haven't made any progress.






Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

becklighter

Thanks, I would appreciate it.