Click here to Skip to main content
15,890,845 members
Search within:


Articles / Web Development / ASP.NET
 
Article

GBVB - Converting VB.NET code to C#

Rate me:
Please Sign up or sign in to vote.
2.38/5 (95 votes)
27 Apr 200310 min read 557.2K   17.2K   95   104
A tool and a method for VB.NET to C# source code conversion

GBVB - Converting VB.NET code to C#

“GBVB (Goodbye VB) is an amazing tool that flawlessly converts any portion of VB.NET code to C# code in a fraction of seconds.” I would love to be able to say this. If you are looking for such a tool, forget about it. You will not be able to find it, and I will explain why later in this article.

Why convert?

Isn’t .NET all about language interoperability? Why would someone ever need to convert VB.NET code to C#? Well, even though the languages are fully interoperable, there are some good reasons for this conversion:

  1. The VB.NET code is still not strongly type-checked. It still makes some type conversions that may be dangerous. Try this code, with Option Strict On, for an example (both are true):
    VB.NET
    Dim x As String
If x Is Nothing Then
    Console.WriteLine("X is Nothing")
End If
If x = "" Then
    Console.WriteLine("X is an empty string")
End If
  2. Most C# programmers find VB.NET syntax ugly and cumbersome. I know this is a matter of personal taste, but it is a good reason for code migration. If you do not feel comfortable using a language, it may be better learn to either love it, or get rid of it.
  3. C# features several compile-time code checking, besides strong typing. Access uninitialized variable, assign values to a variable and never user it, or declare a variable and do not use it, create unreachable code, and you will get an error or a warning from the compiler. VB.NET silently compiles this kind of code.
  4. Since it is easier writing a C# parser (I will write about this later in this article), there are and there will always be more for C# code analyzing and rewriting, while VB.NET will receive much less attention in this area.
  5. Because of some VB.NET ambiguities, sometimes VB.NET code can be slower than the C# equivalent.

Not Possible to Write a Perfect VB.NET to C# Converter

It is not possible to write such a perfect tool because VB.NET syntax allows a programmer to write ambiguous code. VB.NET syntax ambiguities follow in two categories:

  • A parser needs type information for disambiguation, e.g., when you see the code ambiguous(3), you need type information to know if this is a function call or an array access. When translating to C#, you’ll need to either use ambiguous(3) or ambiguous[3]. Actually, this does not make it impossible to write such a tool, but it makes it harder.
  • Some code using the Object data type can only be disambiguated at runtime, e.g., ambiguous(3) will only be resolved on runtime. Actually, it can change from one call to other. This kind of code is impossible to translate. One could use some heuristics to determine the runtime data type at parsing time, but it would be hard and yet not 100% effective.

Approaches for Code Conversion

  • Anakrino! Well, this is a reasonable idea, until you really try it. Just a tip: do you know the VB compiler does not put your comments on the final assembly? There are funnier things, too, but I will let you discover them by yourself.
  • Regular expressions. Come on, you can come up with something better than this. Again, a tip: try to write a recursive regular expression.
  • Write a parser. That is what I did. It is not so much work as it appears, if you keep yourself writing a simplified LL(1) grammar and if you use a nice tool, such as COCO/R for C#.
  • Do everything by hand. I have done that, and I can tell you: GBVB will prevent RSI like no other tool.

Goals and Restrictions

With the previous knowledge in mind, I decided to make a tool that converts some “well-behaved” VB.NET code to C#. Let us define some goals for GBVB, sorted by priority:

  1. Garbage in, Garbage out: if you give the converter some garbage code, you will have more garbage.
  2. This is a typing-saving tool: GBVB will do repeatable most hard and repeatable work, but you still will need to do “brains” part. Some code will not be translated and sometimes you will get an error on the output: if needed, comment the affected code. The converted code does not even need to compile, but it should be faithful to the original VB.NET code.
  3. It should be very useful for converting my own code: if it is useful for you too, hey, it is your lucky day! Nah, I am just joking: actually, GBVB could translate nearly 90% of my code, so you will be very unlucky if GBVB do not for you.
  4. This should be an easy-to-code tool. Fast to code, too. As such, I hope you do not find that my coding style produces excessive commenting on the source code at a point that makes it hard to read.
  5. This is not a pretty printer (it is more like an ugly printer): it does generate indented code, but it does not generate highly organized code. By goal #2, it is up to you to organize it as you wish. Pressing Ctrl-K F on the generated code sometimes helps.
  6. I explicitly decided not to use heuristics, because of goal #2 and #4.
  7. No VB runtime functions will be converted to the .NET Framework “equivalent” ones, e.g., Mid(x) will not be converted to x.Substring. Although easy, as I show you later, this would introduce several bugs and would need revision anyway, without the compiler errors to help you. Don’t you believe me? Run this code, just for fun:
    VB.NET
    Dim x As String
If x Is Nothing Then
    Console.WriteLine("X is Nothing")
End If
If x = "" Then
    Console.WriteLine("X is an empty string")
End If
If Mid(x, 1, 1) = "" Then
    Console.WriteLine("Mid X is an empty string")
End If
If Len(x) = 0 Then
    Console.WriteLine("Len(X) == 0")
End If
Console.WriteLine(x.Substring(0, 1))
  8. On Error Goto / On Error Resume Next will only be part of GBVB over my dead body. See Goal #1.
  9. The Goto Statement: See Goal #8.
  10. The REM is an abomination used only by some distorted minds. There is the one char line commenting, did you know?
  11. Most array code will have trouble while being translated.
  12. Some VB.NET syntax keywords and features are not supported, like Handles, inline array declarations. Goals #2, #3, #4, and pure laziness.
  13. I made it using Visual Studio .NET 2003. I do not even have Visual Studio .NET 2002 on my machine anymore.

Using the tool

Image 1

Look at the screen shot: if you cannot figure out by yourself how to run and use this tool, you should not be programming. Try something easier, there may be some exciting jobs for you on the food market. Alternatively, keep with VB.

Converting from VB6 code

A direct conversion is sometimes possible, but the VB Upgrade Wizard will do a much better job. Therefore, I do strongly recommend you to upgrade the code to VB.NET, run it, test it, fix it, and only then convert it to C# code.

Some advices before migrating

  • Only migrate solid, working code. Create a rigorous unit testing set (you already have this, right?) and only then migrate your code. This way, you can be sure your code runs the way it is supposed to run.
  • You will need to change some things before migrating, because some features are not support by either C# or GBVB. While still working on the VB.NET code, I recommend you to change the following things:
    • Get rid of your optional parameters.
    • Use Option Strict On and Option Compare Binary.
    • Classic On Error error handling is not supported in C#. Change this code to exception handling.
    • Modules are not supported, but Friend classes that have only Shared methods can easily substitute them. Bear in mind that often code on Modules have global variables and is not thread safe, especially if migrated from VB6 (VB6 code ran in STA, so it was not susceptible to this kind of problem), so you can see this as a good opportunity to break it in smaller classes.
    • Change Select Case with conditions and exception filters, if you use them.
    • Finally, remove all the VB runtime functions and use only the .NET framework equivalent ones. Do you remember the VB.NET weirdness I mentioned? Well, having a sound unit testing set will ease things to you.
    • If GBVB has trouble to migrate some statement, comment it, and migrate it manually. You will notice the trouble because GBVB will often stop the code migration on a specific statement.
    • After migrating, do some code cleaning. Normally, C# will give you hundreds of healthy warnings about your “perfect” VB.NET.

Improvements

  • It could be an add-in for VS.NET. For now, I recommend you to add it to the Tools menu.
  • GBVB is only able to migrate “full” code files. Code snippets do not migrate nicely.
  • Do not complain of my commenting style, I know that so many comments like I did can make the code hard to read! Besides this, there is a mix of Portuguese and English words on the identifiers, which makes looking at the sources a rather “globalized” experience. But, if you are experienced with COCO/R, the code is plain obvious and easy to follow.
  • GBVB do not support the “one line” If Then, nor multiple statements on the same line.
  • Remove the LL(1) warnings, and the “Not Expression” annoying messages.
  • Create an option for the brace position. It is easy to change the Util.OpenBlock and the Util.CloseBlock methods for this.
  • It is very easy to add some automatic translation of VB.NET runtime functions if you like to live in danger. In a future version, this will be implemented as optional features. On the VBNET.ATG file, change the Expression compiler production and add specific translation for your code. Like the sample below, where I convert a Len(expression) to Expression.Length (if you didn’t use it before, see the power of COCO/R!): 
    Expression<out string exp> =
(
    "Len"
    ParentExp<out exp>    (.  exp += ".Length"; .)
    |
    "True"                (.  exp = "true"; .)
    |

Acknowledgments

This tool was only possible because of the C# version of COCO/R, by Hanspeter Moessenboeck.

I used the version modified by Pat Terry, available here.

Actually, I slightly changed the version, because all the parsers I write need to be thread safe and deal with accented chars. This specific parser needed also a delegate on the commenting parser, because COCO/R by default ignores comments, which is a good thing, but not in this case. Therefore, everything that is working ok has to be credited to Pat & Hanspeter, and every bug may have been introduced by me.

I did not put my COCO/R changed sources on the ZIP files because I cannot assure you if my modified version is fully compatible with the original, only that it suits my needs. If you are interested on the sources, mail me and I will send you.

License

You can use all the code wrote by me in this article (everything but COCO/R, which is subject to its own licensing) and distribute it freely, as soon as you keep the Copyright notice. If you create some derived work, put a link back to this article, citing me as the author of the original work, as I have did on the Acknowledgments section. You cannot sell nor license this code without my written authorization, but you can make sell or license the code you converted using it.

The Standard Disclaimer

As I said before, I tested this on my machine and it works fine. Use it at your own risk: if you use it, you can lose data, profit, have hardware problems, cause radioactive contamination and start a nuclear world war. However, for me, it works fine and never had such a problem.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Daniel Turini
CEO
Brazil Brazil
I develop software since I was 11. In the past 20 years, I developed software and used very different machines and languages, since Z80 based ones (ZX81, MSX) to mainframe computers. I still have passion for ASM, though no use for it anymore.
Professionally, I developed systems for managing very large databases, mainly on Sybase and SQL Server. Most of the solutions I write are for the financial market, focused on credit systems.
To date, I learned about 20 computer languages. As the moment, I'm in love with C# and the .NET framework, although I only can say I’m very proficient at C#, VB.NET(I’m not proud of this), T/SQL, C++ and libraries like ATL and STL.
I hate doing user interfaces, whether Web based or not, and I’m quite good at doing server side work and reusable components.
I’m the technical architect and one of the authors of Crivo, the most successful automated credit and risk assessment system available in Brazil.

Comments and Discussions

 
FirstPrevNext
GeneralRe: Very nice article but the tool does not work Pin
Daniel Grunwald15-Mar-06 8:46
Daniel Grunwald15-Mar-06 8:46 
Generalthis not work Pin
kerbero17-Jan-06 21:42
kerbero17-Jan-06 21:42 
GeneralRe: this not work Pin
Andy *M*11-Jun-07 0:04
Andy *M*11-Jun-07 0:04 
GeneralRe: this not work Pin
Destiny7774-Nov-08 15:09
Destiny7774-Nov-08 15:09 
GeneralDoes it work at all Pin
Darthcoder29-Sep-05 21:32
Darthcoder29-Sep-05 21:32 
GeneralDid no convert even the Msg( x ) to Message Pin
somebody_tr30-Aug-05 11:58
somebody_tr30-Aug-05 11:58 
GeneralDoes not work. Pin
CDBuffet8-Dec-04 8:08
CDBuffet8-Dec-04 8:08 
GeneralRe: Does not work. Pin
Dave Doknjas6-Jan-05 5:59
Dave Doknjas6-Jan-05 5:59 
Our Instant C# VB.NET to C# converter produces the following:

using System.Data;

using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

public abstract class WorksheetBase
{
protected const int CHART_BORDER_LINE_STYLE = 0;
protected const int CHART_WALL_FORECOLOR = 37;
protected const int CHART_WALL_BACKCOLOR = 2;
protected const int BARCHART_SERIES_COLOR = 46;
protected const int BARCHART_SHOW_ALL_TICK_LABELS = 1;

// Number of data rows
protected int myRowCount;
// Are we in the processing of populating the display - disables on worksheet change handling
protected bool myIsDisplaying;
// Currently selected range
protected Excel.Range myActiveRange;
protected Excel.Worksheet myWorksheet;

// Range spans all display columns on the worksheet
protected Excel.Range columnSpanRange;

//TODO: INSTANT C# TODO TASK: Insert the following converted event handlers at the end of the 'InitializeComponent' method for forms or into a constructor for other classes:
myWorksheet.SelectionChange += new System.EventHandler(Worksheet_SelectionChange);

protected WorksheetBase(string worksheetName)
{
myWorksheet = Globals.ExcelApplication.Worksheets[worksheetName];
if (myWorksheet == null)
{
throw new ApplicationException("Worksheet name not found: " + worksheetName);
}
}

// Create a single range spanning all column ranges
protected void SetColumnSpanRange(Excel.Range[] rangeList)
{
switch (rangeList.Length)
{
case 0:
throw new ApplicationException("Error creating spanRange - no columns specified");
break;
case 1:
columnSpanRange = rangeList[0];
break;
default:
columnSpanRange = Globals.ExcelApplication.Union(rangeList[0], rangeList[1]);
//INSTANT C# NOTE: The ending condition of VB 'For' loops is tested only on entry to the loop. Instant C# has created a temporary variable in order to use the initial value of rangeList.Length - 1 for every iteration:
int ForTemp1 = rangeList.Length - 1;
for (int i = 2; i <= ForTemp1; i++)
{
columnSpanRange = Globals.ExcelApplication.Union(columnSpanRange, rangeList[i]);
}
break;
}

}

// Number of data rows containing data
public int RowCount
{
get
{
return myRowCount;
}

set
{
myRowCount = value;
}
}

// First column of report area.
public int FirstColumn
{
get
{
return columnSpanRange.Column;
}
}

// Last column of report area.
public int LastColumn
{
get
{
return FirstColumn + columnSpanRange.Count - 1;
}
}

// First row of report area
public int FirstRow
{
get
{
return columnSpanRange.Row;
}
}

// Last row of report area
public int LastRow
{
get
{
return FirstRow + RowCount - 1;
}
}

// Are we in the processing of populating the display
public bool IsDisplaying
{
get
{
return myIsDisplaying;
}

set
{
myIsDisplaying = value;
}
}

// Currently selected range
public Excel.Range ActiveRange
{
get
{
return myActiveRange;
}
}

// Worksheet managed by the specific worksheet class
public Excel.Worksheet ExcelWorksheet
{
get
{
return myWorksheet;
}
}

// Activate the current sheet insuring that it is visible and that grid lines are not displayed
public void ActivateSheet()
{
ExcelWorksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
ExcelWorksheet.Activate();
Globals.ExcelApplication.ActiveWindow.DisplayGridlines = false;
}

// Create a range over the cell specified by the column and row values on the specified worksheet
public Excel.Range CreateRange(Excel.Worksheet ws, string column, int row)
{
string rangeString = column + row.ToString();
return ws.Range(rangeString);
}

// Create a range over the cell specified by the column and row values on this worksheet
public Excel.Range CreateRange(string column, int row)
{
return CreateRange(ExcelWorksheet, column, row);
}

// Create a range using two columns specified as integers spanning several rows.
// Must convert the range from R1C1 format to A1 format to create range instance
public Excel.Range CreateRange(int startColumn, int startRow, int endColumn, int endRow)
{
string rangeR1C1 = string.Format("R{0}C{1}:R{2}C{3}", startRow, startColumn, endRow, endColumn);
string rangeA1 = Globals.ExcelApplication.ConvertFormula(rangeR1C1, Excel.XlReferenceStyle.xlR1C1, Excel.XlReferenceStyle.xlA1);
return ExcelWorksheet.Range(rangeA1);
}


// Create a range using a single column specified as an integer spanning several rows.
// Must convert the range from R1C1 format to A1 format to create range instance
public Excel.Range CreateRange(int column, int startRow, int endRow)
{
string rangeR1C1 = string.Format("R{0}C{1}:R{2}C{1}", startRow, column, endRow);
string rangeA1 = Globals.ExcelApplication.ConvertFormula(rangeR1C1, Excel.XlReferenceStyle.xlR1C1, Excel.XlReferenceStyle.xlA1);
return ExcelWorksheet.Range(rangeA1);
}

// Create a range using a single column specified as an integer and a single rows.
// Must convert the range from R1C1 format to A1 format to create range instance
public Excel.Range CreateRange(int column, int row)
{
string rangeR1C1 = string.Format("R{0}C{1}", row, column);
string rangeA1 = Globals.ExcelApplication.ConvertFormula(rangeR1C1, Excel.XlReferenceStyle.xlR1C1, Excel.XlReferenceStyle.xlA1);
return ExcelWorksheet.Range(rangeA1);
}

// Create a named range over a single column / row with the column specified as a string
public Excel.Range CreateNamedRange(string rangeName, string column, int row)
{
string rangeFormula = "=" + column + row;
return CreateNamedRange(rangeName, rangeFormula);
}

// Create a named range over the specified cells
// Prepends required "=" if not included
public Excel.Range CreateNamedRange(string rangeName, string rangeFormula)
{
// If range formula does not begin with the required "=" then add it.
string tempRangeFormula = null;
//INSTANT C# WARNING: C# only evaluates the one required value of the '?' operator, while VB.NET always evaluates both values of an 'IIf' statement.
tempRangeFormula = ((rangeFormula.StartsWith("=")) ? rangeFormula : "=" + rangeFormula);

// Create the named range and return a reference
Excel.Name tempName = ExcelWorksheet.Names.Add(rangeName, tempRangeFormula);
return ExcelWorksheet.Range(rangeName);
}

// Handle SelectionChange event
// Store selected range in myActiveRange making it available to the ActiveRange property
//INSTANT C# NOTE: This method is an event handler
//ORIGINAL LINE: Protected Sub Worksheet_SelectionChange(ByVal target As Excel.Range) Handles myWorksheet.SelectionChange
protected void Worksheet_SelectionChange(Excel.Range target)
{
myActiveRange = target;
DoWorksheetSelectionChangeWork(target);
}

// Give derived class an opportunity to do additional work
protected virtual void DoWorksheetSelectionChangeWork(Excel.Range target)
{
// Default is to do nothing extra
}

// Sets the formula in the English (United States) locale. This enables code that executes
// correctly in different locales. For example, formula "=SUM (1.5, 3.0)"
// is correct in English (United States), but would fail in French (France),
// which would expect "=SOMME(1,5;3,0)". We cannot automatically translate formulas;
// instead, we are using reflection to specify the En-US locale.
public static void SetFormula(Excel.Range range, string formula)
{
// en-US culture info used to set locale independent formulas
System.Globalization.CultureInfo enUSCulture = new System.Globalization.CultureInfo(1033);

// Type descripter for Excel.Range - allows us to set the Formula property using reflection
Type rangeType = typeof(Excel.Range);

// Set the range's Formula property indicating that the formula being assigned is formatted as en-US culture
// Runtime will automatically translate the formula to the actual culture
rangeType.InvokeMember("Formula", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.SetProperty, null, range, new object[] {formula}, enUSCulture);
}


// Each worksheet provides their own implementation as determining the selected symbol is different for each
// This method is called when initiating a new Stock Allocation while anther sheet is active. If the sheet returns a
// non-empty string, the Stock Allocation sheet will automatically select the corresponding symbol
public virtual string GetActiveStockSymbol()
{
return string.Empty;
}

// The workshet specific mechanism for redrawing the worksheet contents
// Used primarily when some event has changed the contents of the DataSet such as a price update
// or an applied allocation.
public abstract void Refresh();

// Format the borders around the data
protected void FormatDataBorders(bool displayHorizontal)
{
// Create range over data area
// Dim rangeString As String = String.Format("{0}{1}:{2}{3}", startingColumn, startingRow, endingColumn, myEndingRow)
Excel.Range rng = CreateRange(FirstColumn, FirstRow, LastColumn, LastRow);

// Format horozontal borders within the range
if (RowCount > 1)
{
if (displayHorizontal)
{
rng.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders(Excel.XlBordersIndex.xlInsideHorizontal).Weight = Excel.XlBorderWeight.xlThin;
rng.Borders(Excel.XlBordersIndex.xlInsideHorizontal).ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
}
else
{
rng.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
}

// Format vertical borders within the range
rng.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders(Excel.XlBordersIndex.xlInsideVertical).Weight = Excel.XlBorderWeight.xlThin;
rng.Borders(Excel.XlBordersIndex.xlInsideVertical).ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
}

// Format bottom border
rng.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium;
rng.Borders(Excel.XlBordersIndex.xlEdgeBottom).ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

// Format left border
rng.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlMedium;
rng.Borders(Excel.XlBordersIndex.xlEdgeLeft).ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

// Format right border
rng.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlMedium;
rng.Borders(Excel.XlBordersIndex.xlEdgeRight).ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

// Format top border
rng.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
rng.Borders(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlMedium;
rng.Borders(Excel.XlBordersIndex.xlEdgeTop).ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
}

// Removes the contents of all cells within the specified range
// Function is used primarily to delete the existing data contents of a sheet so that it can
// be repopulated.
protected void ClearContents()
{
// Protected Sub ClearContents(ByVal startingRow As Integer, ByVal startingColumn As String, ByVal endingColumn As String, ByVal resetBorders As Boolean)
if (RowCount > 0)
{
// Create range over data rows
Excel.Range rng = CreateRange(FirstColumn, FirstRow, LastColumn, LastRow);
// Remove data from all cells in range
rng.ClearContents();

// Delete all rows except the first so as to not lose cell formatting
if (RowCount > 1)
{
rng = CreateRange(FirstColumn, FirstRow + 1, LastColumn, LastRow);
//rng.Select()
//Globals.ExcelApplication.Selection.Delete(Excel.XlDirection.xlUp)
rng.Delete(Excel.XlDirection.xlUp);
}

// Delete all charts
ExcelWorksheet.ChartObjects.Delete();

// Reset row count
RowCount = 0;
}
}

// Converts the chart name returned from the ActiveChart.Name property to the format
// expected by the ActiveSheet members such as ActiveSheet.DrawingObjects and
// ActiveSheet.Shapes. The ActiveChart.Name property returns the name with the
// containing sheet name prepended (ex: "Account Summary Chart 1") but the ActiveSheet
// members do not expect the sheet name to be prepended (ex: "Chart 1") so this function
// strips the name down to the non-sheet-name portion
public string GetActiveChartName()
{
string chartName = Globals.ExcelApplication.ActiveChart.Name;
return chartName.Substring(chartName.IndexOf("Chart"));
}

// Details of creating chart members
public Excel.Shape CreateChart(string sheetName, Excel.XlChartType chartType, Excel.Range rng, string title, int foreColor, int backColor, int top, int left, int height, int width)
{
Excel.Chart excelChart = Globals.ExcelApplication.Charts.Add();
excelChart.ChartType = chartType;
excelChart.SetSourceData(rng, Excel.XlRowCol.xlColumns);
excelChart.HasTitle = true;
excelChart.ChartTitle.Text = title;
excelChart.HasLegend = false;

if (chartType == Excel.XlChartType.xl3DPieExploded)
{
excelChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowLabel);
excelChart.PlotArea.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;
excelChart.PlotArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
}

excelChart.Location(Excel.XlChartLocation.xlLocationAsObject, sheetName);
Globals.ExcelApplication.Selection.Border.LineStyle = CHART_BORDER_LINE_STYLE;
FillSelectionWithGradient(Office.MsoGradientStyle.msoGradientDiagonalUp, foreColor, backColor);

Globals.ExcelApplication.ActiveSheet.DrawingObjects(GetActiveChartName()).Shadow = true;

if (chartType == Excel.XlChartType.xl3DBarClustered)
{
Globals.ExcelApplication.ActiveChart.Walls.Select();
FillSelectionWithGradient(Office.MsoGradientStyle.msoGradientHorizontal, CHART_WALL_FORECOLOR, CHART_WALL_BACKCOLOR);

Globals.ExcelApplication.ActiveChart.SeriesCollection(1).Select();
Globals.ExcelApplication.Selection.Interior.ColorIndex = BARCHART_SERIES_COLOR;
Globals.ExcelApplication.ActiveChart.Axes(Excel.XlAxisType.xlCategory).TickLabelSpacing = BARCHART_SHOW_ALL_TICK_LABELS;
}

Excel.Shape excelShape = Globals.ExcelApplication.ActiveSheet.Shapes(GetActiveChartName());
excelShape.Top = top;
excelShape.Left = left;
excelShape.Height = height;
excelShape.Width = width;

SetActiveChartFonts();

return excelShape;
}

// Create range for charts using the ranges specifying the lable and value columns
// Charts are a special case as they actually span two sets of columns
public Excel.Range CreateChartRange(Excel.Range label, Excel.Range value)
{
string rangeR1C1 = string.Format("R{0}C{1}:R{2}C{1},R{3}C{4}:R{5}C{4}", label.Row, label.Column, label.Row + RowCount - 1, value.Row, value.Column, value.Row + RowCount - 1);
string rangeA1 = Globals.ExcelApplication.ConvertFormula(rangeR1C1, Excel.XlReferenceStyle.xlR1C1, Excel.XlReferenceStyle.xlA1);
return ExcelWorksheet.Range(rangeA1);
}

// Handles details of setting the current selection to a compound color gradient
protected void FillSelectionWithGradient(Office.MsoGradientStyle gradientStyle, int foreColor, int backColor)
{
Globals.ExcelApplication.Selection.Fill.TwoColorGradient(gradientStyle, 1);
Globals.ExcelApplication.Selection.Fill.Visible = true;
Globals.ExcelApplication.Selection.Fill.ForeColor.SchemeColor = foreColor;
Globals.ExcelApplication.Selection.Fill.BackColor.SchemeColor = backColor;
}

// Setup chart fonts
protected void SetActiveChartFonts()
{
Globals.ExcelApplication.ActiveChart.ChartTitle.Font.Name = "Arial";
Globals.ExcelApplication.ActiveChart.ChartTitle.Font.FontStyle = "Bold";
Globals.ExcelApplication.ActiveChart.ChartTitle.Font.Size = 11;

if (Globals.ExcelApplication.ActiveChart.ChartType == Excel.XlChartType.xl3DBarClustered)
{
if (Globals.ExcelApplication.ActiveChart.HasAxis(Excel.XlAxisType.xlCategory))
{
Globals.ExcelApplication.ActiveChart.Axes(Excel.XlAxisType.xlCategory).Select();
Globals.ExcelApplication.Selection.TickLabels.AutoScaleFont = false;
Globals.ExcelApplication.Selection.TickLabels.Font.Name = "Arial";
Globals.ExcelApplication.Selection.TickLabels.Font.FontStyle = "Bold";
Globals.ExcelApplication.Selection.TickLabels.Font.Size = 8;
}

if (Globals.ExcelApplication.ActiveChart.HasAxis(Excel.XlAxisType.xlValue))
{
Globals.ExcelApplication.ActiveChart.Axes(Excel.XlAxisType.xlValue).Select();
Globals.ExcelApplication.Selection.TickLabels.AutoScaleFont = false;
Globals.ExcelApplication.Selection.TickLabels.Font.Name = "Arial";
Globals.ExcelApplication.Selection.TickLabels.Font.FontStyle = "Regular";
Globals.ExcelApplication.Selection.TickLabels.Font.Size = 8;
}
}
else if (Globals.ExcelApplication.ActiveChart.ChartType == Excel.XlChartType.xl3DPieExploded)
{
Globals.ExcelApplication.ActiveChart.SeriesCollection(1).DataLabels.Select();
Globals.ExcelApplication.Selection.AutoScaleFont = false;
Globals.ExcelApplication.Selection.Font.Name = "Arial";
Globals.ExcelApplication.Selection.Font.FontStyle = "Bold";
Globals.ExcelApplication.Selection.Font.Size = 8;
}
}

}


David Anton
Tangible Software Solutions
www.tangiblesoftwaresolutions.com
Home of the Instant C# VB.NET to C# Converter and the Instant VB C# to VB.NET Converter
GeneralCould not compile Pin
RonBurd15-Apr-04 10:15
RonBurd15-Apr-04 10:15 
GeneralMy Hacks Pin
OmegaSupreme13-Apr-04 6:52
OmegaSupreme13-Apr-04 6:52 
GeneralThanks Pin
SteveSchofield20-Dec-03 21:02
SteveSchofield20-Dec-03 21:02 
GeneralVB &quot;Handles&quot; statement Pin
ppadilla12-Nov-03 8:45
ppadilla12-Nov-03 8:45 
GeneralVery Good Pin
raygilbert9-Nov-03 17:25
raygilbert9-Nov-03 17:25 
GeneralRe: Very Good Pin
OmegaSupreme13-Feb-04 4:36
OmegaSupreme13-Feb-04 4:36 
GeneralRe: Very Good Pin
OmegaSupreme15-Feb-04 7:15
OmegaSupreme15-Feb-04 7:15 
GeneralRe: Very Good - not good Pin
vietht14-Nov-06 21:23
vietht14-Nov-06 21:23 
GeneralParsing Error Pin
Jeffrey Scott Flesher17-Oct-03 15:05
Jeffrey Scott Flesher17-Oct-03 15:05 
QuestionWhy not anakrino? Pin
soeren7611-Sep-03 0:04
soeren7611-Sep-03 0:04 
AnswerRe: Why not anakrino? Pin
soeren7611-Sep-03 0:09
soeren7611-Sep-03 0:09 
Generaldon't download!! Pin
cyg27-Jul-03 3:50
cyg27-Jul-03 3:50 
Questionputting code comments into the final assembly? Pin
CodeFriendly29-Jun-03 11:58
CodeFriendly29-Jun-03 11:58 
AnswerRe: putting code comments into the final assembly? Pin
Daniel Turini29-Jun-03 13:15
Daniel Turini29-Jun-03 13:15 
GeneralRe: putting code comments into the final assembly? Pin
CodeFriendly29-Jun-03 14:14
CodeFriendly29-Jun-03 14:14 
GeneralInterest in your modified Coco/R Pin
spi18-Jun-03 6:37
professionalspi18-Jun-03 6:37 
GeneralFew more links Pin
Kant3-Jun-03 17:23
Kant3-Jun-03 17:23 
Last Visit: 31-Dec-99 18:00     Last Update: 3-May-24 4:30Refreshᐊ Prev12345 Next ᐅ

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 26 Apr 2003

Article Copyright 2003 by Daniel Turini
Everything else Copyright © CodeProject, 1999-2024

Web03 2.8:2024-04-02:1