|
|
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:
- 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): 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
- 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.
- 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.
- 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.
- 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:
- Garbage in, Garbage out: if you give the converter some garbage code, you will have more garbage.
- 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.
- 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.
- 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.
- 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.
- I explicitly decided not to use heuristics, because of goal #2 and #4.
- 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: 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))
On Error Goto / On Error Resume Next will only be part of GBVB over my dead body. See Goal #1.
- The
Goto Statement: See Goal #8.
- The
REM is an abomination used only by some distorted minds. There is the one char line commenting, did you know?
- Most array code will have trouble while being translated.
- Some VB.NET syntax keywords and features are not supported, like
Handles, inline array declarations. Goals #2, #3, #4, and pure laziness.
- I made it using Visual Studio .NET 2003. I do not even have Visual Studio .NET 2002 on my machine anymore.
Using the tool
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
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.
| You must Sign In to use this message board. |
|
| | Msgs 1 to 25 of 80 (Total in Forum: 80) (Refresh) | FirstPrevNext |
|
|
 |
|
|
I have objection to the first line of the article:
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.
This is just not true, such a tool exists -- compile the code and then run it though reflector and save the file.
Since compiling to ILM and converting ILM back to code is easy, this problem is easy.
The resulting code would not be readable but it would function right.
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
 |
|
|
Please remove this article as many people found that it's not working and even i tried to convert VB.Net code of 1100 lines but it's not working !!!
sdf
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
Read the introduction you gimboid: “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.
|
| Sign In·View Thread·PermaLink | 2.00/5 (1 vote) |
|
|
|
 |
|
|
For e.g. you have a With block in VB.Net, this executable doesn't convert at all. VB.Net code: Function AddStateList() as ArrayList Dim MyList as ArrayList 'Begin here to add - With MyList .Add("AL") .Add("DE") .Add("NE") .Add("MA") .Add("MN") .Add("MO") .Add("CA") .Add("KS") .Add("WI") .Add("WA") End With 'end here add Return MyList End Function
With this all I get is this C# code:
{ as AddStateList() { ArrayList; as MyList; ArrayList; // Begin here to add -
With; MyList; }
}
Lotus
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
 |
|
|
zecougar wrote: High claims for an extremely foolish tool
What high claims?
Read the introduction!
“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.
It looks like he is claiming that it won't work. So, what are you complaining about? You were told in advance it wasn't going to work!
|
| Sign In·View Thread·PermaLink | 3.67/5 (2 votes) |
|
|
|
 |
|
|
 |
|
|
I pasted my b.net code which is like 500 lines and there comes my c# code which is 2 lines
class xxx
end class
hahaha !!
adi.mr
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
Not only does this not work, its not even close to working, yes I agree its not an easy thing to do but this must be the most pathetic attempt at any problem ive ever seen. I tried a variety of conversions and none were even close to the proper syntax.
Dont waste your time downloading this!
|
| Sign In·View Thread·PermaLink | 1.80/5 (2 votes) |
|
|
|
 |
|
|
Hi to All,
How can i find out the server IP Address? if any body know plzz tel me with code....
thanks
Honey
|
| Sign In·View Thread·PermaLink | 1.00/5 (2 votes) |
|
|
|
 |
|
|
Hello Daniel
I was inspired by your manner of writing - with humor and very clear but unfortunately GBVB tool that I compiled from source does not work well. For long VB.NET source I got only one small method body. Maybe this is because I am using .NET 2.0? Hope this will be fixed at some point.
Thank you Martin Rakhmanov http://jimmers.russia.webmatrixhosting.net/
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
 |
|
|
 |
|
|
Read the introduction you goit!
“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.
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
 |
|
|
Rather than starting to such a cool project from converting c# to Vb; I think conversion from c# to Vb would be much more easier and cooler. The one to know c# has of nothing to the with Vb whereas it is not so for those who deal in Vb. And there would not be any defining problems. As vb tolerates giving numeric and string value without convertion, How can u hope to verify which she or he has choosen. But in c# to vb conversion You know what to convert and convert to what.TAKE iT EASY. indeed for along time I am dealing with those too.
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
Tried it with the following code and it bombed:
Imports System.Data
Imports Office = Microsoft.Office.Core Imports Excel = Microsoft.Office.Interop.Excel
Public MustInherit Class WorksheetBase Protected Const CHART_BORDER_LINE_STYLE As Integer = 0 Protected Const CHART_WALL_FORECOLOR As Integer = 37 Protected Const CHART_WALL_BACKCOLOR As Integer = 2 Protected Const BARCHART_SERIES_COLOR As Integer = 46 Protected Const BARCHART_SHOW_ALL_TICK_LABELS As Integer = 1
' Number of data rows Protected myRowCount As Integer ' Are we in the processing of populating the display - disables on worksheet change handling Protected myIsDisplaying As Boolean ' Currently selected range Protected myActiveRange As Excel.Range Protected WithEvents myWorksheet As Excel.Worksheet
' Range spans all display columns on the worksheet Protected columnSpanRange As Excel.Range
Protected Sub New(ByVal worksheetName As String) myWorksheet = Globals.ExcelApplication.Worksheets.Item(worksheetName) If myWorksheet Is Nothing Then Throw New ApplicationException("Worksheet name not found: " & worksheetName) End If End Sub
' Create a single range spanning all column ranges Protected Sub SetColumnSpanRange(ByVal rangeList As Excel.Range()) Select Case rangeList.Length Case 0 Throw New ApplicationException("Error creating spanRange - no columns specified") Case 1 columnSpanRange = rangeList(0) Case Else columnSpanRange = Globals.ExcelApplication.Union(rangeList(0), rangeList(1)) For i As Integer = 2 To rangeList.Length - 1 columnSpanRange = Globals.ExcelApplication.Union(columnSpanRange, rangeList(i)) Next End Select
End Sub
' Number of data rows containing data Public Property RowCount() As Integer Get Return myRowCount End Get
Set(ByVal value As Integer) myRowCount = value End Set End Property
' First column of report area. Public ReadOnly Property FirstColumn() As Integer Get Return columnSpanRange.Column End Get End Property
' Last column of report area. Public ReadOnly Property LastColumn() As Integer Get Return FirstColumn + columnSpanRange.Count - 1 End Get End Property
' First row of report area Public ReadOnly Property FirstRow() As Integer Get Return columnSpanRange.Row End Get End Property
' Last row of report area Public ReadOnly Property LastRow() As Integer Get Return FirstRow + RowCount - 1 End Get End Property
' Are we in the processing of populating the display Public Property IsDisplaying() As Boolean Get Return myIsDisplaying End Get
Set(ByVal value As Boolean) myIsDisplaying = value End Set End Property
' Currently selected range Public ReadOnly Property ActiveRange() As Excel.Range Get Return myActiveRange End Get End Property
' Worksheet managed by the specific worksheet class Public ReadOnly Property ExcelWorksheet() As Excel.Worksheet Get Return myWorksheet End Get End Property
' Activate the current sheet insuring that it is visible and that grid lines are not displayed Public Sub ActivateSheet() ExcelWorksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible ExcelWorksheet.Activate() Globals.ExcelApplication.ActiveWindow.DisplayGridlines = False End Sub
' Create a range over the cell specified by the column and row values on the specified worksheet Public Function CreateRange(ByVal ws As Excel.Worksheet, ByVal column As String, ByVal row As Integer) As Excel.Range Dim rangeString As String = column & row.ToString() Return ws.Range(rangeString) End Function
' Create a range over the cell specified by the column and row values on this worksheet Public Function CreateRange(ByVal column As String, ByVal row As Integer) As Excel.Range Return CreateRange(ExcelWorksheet, column, row) End Function
' 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 Function CreateRange(ByVal startColumn As Integer, ByVal startRow As Integer, _ ByVal endColumn As Integer, ByVal endRow As Integer) As Excel.Range Dim rangeR1C1 As String = String.Format("R{0}C{1}:R{2}C{3}", startRow, startColumn, endRow, endColumn) Dim rangeA1 As String = Globals.ExcelApplication.ConvertFormula(rangeR1C1, Excel.XlReferenceStyle.xlR1C1, Excel.XlReferenceStyle.xlA1) Return ExcelWorksheet.Range(rangeA1) End Function
' 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 Function CreateRange(ByVal column As Integer, ByVal startRow As Integer, ByVal endRow As Integer) As Excel.Range Dim rangeR1C1 As String = String.Format("R{0}C{1}:R{2}C{1}", startRow, column, endRow) Dim rangeA1 As String = Globals.ExcelApplication.ConvertFormula(rangeR1C1, Excel.XlReferenceStyle.xlR1C1, Excel.XlReferenceStyle.xlA1) Return ExcelWorksheet.Range(rangeA1) End Function
' 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 Function CreateRange(ByVal column As Integer, ByVal row As Integer) As Excel.Range Dim rangeR1C1 As String = String.Format("R{0}C{1}", row, column) Dim rangeA1 As String = Globals.ExcelApplication.ConvertFormula(rangeR1C1, Excel.XlReferenceStyle.xlR1C1, Excel.XlReferenceStyle.xlA1) Return ExcelWorksheet.Range(rangeA1) End Function
' Create a named range over a single column / row with the column specified as a string Public Function CreateNamedRange(ByVal rangeName As String, ByVal column As String, ByVal row As Integer) As Excel.Range Dim rangeFormula As String = "=" & column & row Return CreateNamedRange(rangeName, rangeFormula) End Function
' Create a named range over the specified cells ' Prepends required "=" if not included Public Function CreateNamedRange(ByVal rangeName As String, ByVal rangeFormula As String) As Excel.Range ' If range formula does not begin with the required "=" then add it. Dim tempRangeFormula As String tempRangeFormula = IIf(rangeFormula.StartsWith("="), rangeFormula, "=" & rangeFormula)
' Create the named range and return a reference Dim tempName As Excel.Name = ExcelWorksheet.Names.Add(rangeName, tempRangeFormula) Return ExcelWorksheet.Range(rangeName) End Function
' Handle SelectionChange event ' Store selected range in myActiveRange making it available to the ActiveRange property Protected Sub Worksheet_SelectionChange(ByVal target As Excel.Range) Handles myWorksheet.SelectionChange myActiveRange = target DoWorksheetSelectionChangeWork(target) End Sub
' Give derived class an opportunity to do additional work Protected Overridable Sub DoWorksheetSelectionChangeWork(ByVal target As Excel.Range) ' Default is to do nothing extra End Sub
' 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 Shared Sub SetFormula(ByVal range As Excel.Range, ByVal formula As String) ' en-US culture info used to set locale independent formulas Dim enUSCulture As New System.Globalization.CultureInfo(1033)
' Type descripter for Excel.Range - allows us to set the Formula property using reflection Dim rangeType As Type = GetType(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 Or System.Reflection.BindingFlags.Instance Or System.Reflection.BindingFlags.SetProperty, _ Nothing, _ range, _ New Object() {formula}, _ enUSCulture) End Sub
' 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 Overridable Function GetActiveStockSymbol() As String Return String.Empty End Function
' 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 MustOverride Sub Refresh()
' Format the borders around the data Protected Sub FormatDataBorders(ByVal displayHorizontal As Boolean) ' Create range over data area ' Dim rangeString As String = String.Format("{0}{1}:{2}{3}", startingColumn, startingRow, endingColumn, myEndingRow) Dim rng As Excel.Range = CreateRange(FirstColumn, FirstRow, LastColumn, LastRow)
' Format horozontal borders within the range If RowCount > 1 Then With rng.Borders(Excel.XlBordersIndex.xlInsideHorizontal) If displayHorizontal Then .LineStyle = Excel.XlLineStyle.xlContinuous .Weight = Excel.XlBorderWeight.xlThin .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic Else .LineStyle = Excel.XlLineStyle.xlLineStyleNone End If End With
' Format vertical borders within the range With rng.Borders(Excel.XlBordersIndex.xlInsideVertical) .LineStyle = Excel.XlLineStyle.xlContinuous .Weight = Excel.XlBorderWeight.xlThin .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic End With End If
' Format bottom border With rng.Borders(Excel.XlBordersIndex.xlEdgeBottom) .LineStyle = Excel.XlLineStyle.xlContinuous .Weight = Excel.XlBorderWeight.xlMedium .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic End With
' Format left border With rng.Borders(Excel.XlBordersIndex.xlEdgeLeft) .LineStyle = Excel.XlLineStyle.xlContinuous .Weight = Excel.XlBorderWeight.xlMedium .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic End With
' Format right border With rng.Borders(Excel.XlBordersIndex.xlEdgeRight) .LineStyle = Excel.XlLineStyle.xlContinuous .Weight = Excel.XlBorderWeight.xlMedium .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic End With
' Format top border With rng.Borders(Excel.XlBordersIndex.xlEdgeTop) .LineStyle = Excel.XlLineStyle.xlContinuous .Weight = Excel.XlBorderWeight.xlMedium .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic End With End Sub
' 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 Sub ClearContents() ' Protected Sub ClearContents(ByVal startingRow As Integer, ByVal startingColumn As String, ByVal endingColumn As String, ByVal resetBorders As Boolean) If RowCount > 0 Then ' Create range over data rows Dim rng As Excel.Range = 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 Then rng = CreateRange(FirstColumn, FirstRow + 1, LastColumn, LastRow) 'rng.Select() 'Globals.ExcelApplication.Selection.Delete(Excel.XlDirection.xlUp) rng.Delete(Excel.XlDirection.xlUp) End If
' Delete all charts ExcelWorksheet.ChartObjects.Delete()
' Reset row count RowCount = 0 End If End Sub
' 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 Function GetActiveChartName() As String Dim chartName As String = Globals.ExcelApplication.ActiveChart.Name Return chartName.Substring(chartName.IndexOf("Chart")) End Function
' Details of creating chart members Public Function CreateChart(ByVal sheetName As String, ByVal chartType As Excel.XlChartType, ByVal rng As Excel.Range, ByVal title As String, ByVal foreColor As Integer, ByVal backColor As Integer, ByVal top As Integer, ByVal left As Integer, ByVal height As Integer, ByVal width As Integer) As Excel.Shape Dim excelChart As Excel.Chart = 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 Then excelChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowLabel) excelChart.PlotArea.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone excelChart.PlotArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone End If
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 Then 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 End If
Dim excelShape As Excel.Shape = Globals.ExcelApplication.ActiveSheet.Shapes(GetActiveChartName()) excelShape.Top = top excelShape.Left = left excelShape.Height = height excelShape.Width = width
SetActiveChartFonts()
Return excelShape End Function
' 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 Function CreateChartRange(ByVal label As Excel.Range, ByVal value As Excel.Range) As Excel.Range Dim rangeR1C1 As String = 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) Dim rangeA1 As String = Globals.ExcelApplication.ConvertFormula(rangeR1C1, Excel.XlReferenceStyle.xlR1C1, _ Excel.XlReferenceStyle.xlA1) Return ExcelWorksheet.Range(rangeA1) End Function
' Handles details of setting the current selection to a compound color gradient Protected Sub FillSelectionWithGradient(ByVal gradientStyle As Office.MsoGradientStyle, ByVal foreColor As Integer, ByVal backColor As Integer) With Globals.ExcelApplication.Selection.Fill .TwoColorGradient(gradientStyle, 1) .Visible = True .ForeColor.SchemeColor = foreColor .BackColor.SchemeColor = backColor End With End Sub
' Setup chart fonts Protected Sub SetActiveChartFonts() With Globals.ExcelApplication.ActiveChart.ChartTitle.Font .Name = "Arial" .FontStyle = "Bold" .Size = 11 End With
If Globals.ExcelApplication.ActiveChart.ChartType = Excel.XlChartType.xl3DBarClustered Then If Globals.ExcelApplication.ActiveChart.HasAxis(Excel.XlAxisType.xlCategory) Then Globals.ExcelApplication.ActiveChart.Axes(Excel.XlAxisType.xlCategory).Select() Globals.ExcelApplication.Selection.TickLabels.AutoScaleFont = False With Globals.ExcelApplication.Selection.TickLabels.Font .Name = "Arial" .FontStyle = "Bold" .Size = 8 End With End If
If Globals.ExcelApplication.ActiveChart.HasAxis(Excel.XlAxisType.xlValue) Then Globals.ExcelApplication.ActiveChart.Axes(Excel.XlAxisType.xlValue).Select() Globals.ExcelApplication.Selection.TickLabels.AutoScaleFont = False With Globals.ExcelApplication.Selection.TickLabels.Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 End With End If ElseIf Globals.ExcelApplication.ActiveChart.ChartType = Excel.XlChartType.xl3DPieExploded Then Globals.ExcelApplication.ActiveChart.SeriesCollection(1).DataLabels.Select() Globals.ExcelApplication.Selection.AutoScaleFont = False With Globals.ExcelApplication.Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 8 End With End If End Sub
End Class
CD
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
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 additi | | | | | |