Knowledge Base Nr: 00266 excelgen.cs - http://www.swe-kaiser.de

Downloads: Screenshot ExcelExcel Sheet

C#: VBA: C#-Code aus Excel-Tabelle mit Visual Basic generieren

  
das beispiel ist sehr stark gekürzt und ergibt keinen sinnvollen code mehr - aber es zeigt das prinzip.

############## visual basic macro #######################

Sub GenerateCode()

Dim filename As String
Dim cRow, cCell, cColumn As Range
Dim fileID As Integer
Dim content, csvLine As String
Dim startSpalte As Integer
Dim variablenNamenSpalte As Integer
Dim GEN_COLUMN_INFO_row, GEN_CHI_C_START_row As Integer
Dim GEN_CHI_C_STOP_row, GEN_CHI_P_START_row, GEN_CHI_P_STOP_row, GEN_INIT_START_row, GEN_INIT_STOP_row As Integer
Dim GEN_REG_START_row, GEN_REG_STOP_row As Integer
Dim GEN_CONV_START_row, GEN_CONV_STOP_row As Integer
Dim GEN_COLUMN_INFO_clmn, GEN_CLMN_REG_NAME_clmn, GEN_CLMN_REG_ADR_clmn, GEN_CLMN_COMMENT_clmn, GEN_CLMN_CALC_clmn, GEN_CLMN_DEFAULT_clmn As Integer
Dim GEN_CLMN_MINVAL_clmn, GEN_CLMN_MAXVAL_clmn As Integer
Dim genStartMarker, genStopMarker As String
Dim gen_CHECKMVR, gen_CHECKMVRNAME As String
Dim value, comment As String

'**********************************************************************************************************
'init section
'**********************************************************************************************************
fileID = FreeFile
startSpalte = 2
variablenNamenSpalte = 1

'**********************************************************************************************************
'parse config section - find keywords
'**********************************************************************************************************
For Each cRow In ActiveSheet.UsedRange.Rows
content = cRow.Cells(1, variablenNamenSpalte)
If (content = "GEN_COLUMN_INFO") Then
GEN_COLUMN_INFO_row = cRow.Row
End If
If (content = "GEN_OUTPUT_FILE") Then
filename = cRow.Cells(1, variablenNamenSpalte + 1)
End If
If (content = "GEN_START_MARKER") Then
genStartMarker = cRow.Cells(1, variablenNamenSpalte + 1)
End If
If (content = "GEN_STOP_MARKER") Then
genStopMarker = cRow.Cells(1, variablenNamenSpalte + 1)
End If
If (content = "GEN_CHI_C_START") Then
GEN_CHI_C_START_row = cRow.Row
End If
If (content = "GEN_CHI_C_STOP") Then
GEN_CHI_C_STOP_row = cRow.Row
End If
If (content = "GEN_CONV_START") Then
GEN_CONV_START_row = cRow.Row
End If
If (content = "GEN_CONV_STOP") Then
GEN_CONV_STOP_row = cRow.Row
End If
If (content = "GEN_CHI_P_START") Then
GEN_CHI_P_START_row = cRow.Row
End If
If (content = "GEN_COLUMN_INFO_") Then
GEN_COLUMN_INFO__row = cRow.Row
End If
If (content = "GEN_CHI_P_STOP") Then
GEN_CHI_P_STOP_row = cRow.Row
End If
If (content = "GEN_INIT_START") Then
GEN_INIT_START_row = cRow.Row
End If
If (content = "GEN_INIT_STOP") Then
GEN_INIT_STOP_row = cRow.Row
End If
If (content = "GEN_REG_START") Then
GEN_REG_START_row = cRow.Row
End If
If (content = "GEN_REG_STOP") Then
GEN_REG_STOP_row = cRow.Row
End If
Next

For Each cColumn In ActiveSheet.UsedRange.Columns
content = cColumn.Cells(GEN_COLUMN_INFO_row, 1)
If (content = "GEN_COLUMN_INFO") Then
GEN_COLUMN_INFO_clmn = cColumn.Column
End If
If (content = "GEN_CLMN_REG_NAME") Then
GEN_CLMN_REG_NAME_clmn = cColumn.Column
End If
If (content = "GEN_CLMN_REG_ADR") Then
GEN_CLMN_REG_ADR_clmn = cColumn.Column
End If
If (content = "GEN_CLMN_COMMENT") Then
GEN_CLMN_COMMENT_clmn = cColumn.Column
End If
If (content = "GEN_CLMN_CALC") Then
GEN_CLMN_CALC_clmn = cColumn.Column
End If
If (content = "GEN_CLMN_DEFAULT") Then
GEN_CLMN_DEFAULT_clmn = cColumn.Column
End If
If (content = "GEN_CLMN_MINVAL") Then
GEN_CLMN_MINVAL_clmn = cColumn.Column
End If
If (content = "GEN_CLMN_MAXVAL") Then
GEN_CLMN_MAXVAL_clmn = cColumn.Column
End If
Next


'**********************************************************************************************************
'Generate Output File
'**********************************************************************************************************
Open filename For Output As #fileID
Print #fileID, " #region Generated Code" + vbNewLine

'######### constants

csvLine = genStartMarker + vbNewLine
Print #fileID, csvLine

For Each cRow In ActiveSheet.UsedRange.Rows
If ((cRow.Row >= GEN_CHI_C_START_row) And (cRow.Row <= GEN_CHI_C_STOP_row)) Then
If (cRow.Cells(1, GEN_CLMN_REG_NAME_clmn) <> "") Then
csvLine = "/// <remarks>" + CStr(cRow.Cells(1, GEN_CLMN_COMMENT_clmn)) + "</remarks>"
Print #fileID, csvLine
value = CStr(cRow.Cells(1, GEN_CLMN_REG_ADR_clmn))
value = Replace(value, ",", ".")
csvLine = "private const " + CStr(cRow.Cells(1, GEN_CLMN_REG_NAME_clmn)) + " = " + value + ";"
Print #fileID, csvLine + vbNewLine
End If
End If
Next

csvLine = vbNewLine + genStopMarker + vbNewLine + vbNewLine + vbNewLine + vbNewLine + vbNewLine
Print #fileID, csvLine

'######### Generate() function

csvLine = genStartMarker + vbNewLine
Print #fileID, csvLine
csvLine = "public bool Generate(CLUSTERTYPE2 cluster, CONTROLLERTYPE1 controller, KChiGenParams parameters, ref StringCollection messages)" + vbNewLine + "{" + vbNewLine + " UInt16 reg;" + vbNewLine + " UInt16 value;" + vbNewLine + " int warnings = 0;" + vbNewLine
Print #fileID, csvLine

csvLine = "genparameters = parameters;"
Print #fileID, " " + csvLine + vbNewLine

For Each cRow In ActiveSheet.UsedRange.Rows
If ((cRow.Row > GEN_REG_START_row) And (cRow.Row < GEN_REG_STOP_row)) Then
If (cRow.Cells(1, GEN_COLUMN_INFO_clmn) > 0) Then
If (cRow.Cells(1, GEN_CLMN_CALC_clmn) > 0) Then
comment = "(calculated)"
value = cRow.Cells(1, GEN_CLMN_CALC_clmn)
ElseIf (cRow.Cells(1, GEN_CLMN_DEFAULT_clmn) > 0) Then
comment = "(default)"
value = cRow.Cells(1, GEN_CLMN_DEFAULT_clmn)
End If

If (cRow.Cells(1, GEN_CLMN_CALC_clmn) > 0) Or (cRow.Cells(1, GEN_CLMN_DEFAULT_clmn) > 0) Then
csvLine = "reg = " + CStr(cRow.Cells(1, GEN_CLMN_REG_ADR_clmn)) + "; value = (UInt16)" + value + ";"
Print #fileID, " " + csvLine
If ((cRow.Cells(1, GEN_CLMN_MINVAL_clmn) >= 0) And (cRow.Cells(1, GEN_CLMN_MAXVAL_clmn) > 0)) Then
csvLine = "if (CheckRange(ref messages, """ + CStr(cRow.Cells(1, GEN_CLMN_REG_NAME_clmn)) + """, value, " + CStr(cRow.Cells(1, GEN_CLMN_MINVAL_clmn)) + ", " + CStr(cRow.Cells(1, GEN_CLMN_MAXVAL_clmn)) + ") == false) warnings++;"
Print #fileID, " " + csvLine
End If
csvLine = "Add(CMDTYPE.WRITE16, ""Register " + CStr(cRow.Cells(1, GEN_CLMN_REG_NAME_clmn)) + " " + comment + """, reg, value);"
Print #fileID, " " + csvLine + vbNewLine
End If

End If
End If
Next

csvLine = " return true; //(warnings == 0);" + vbNewLine + "}"
Print #fileID, csvLine
csvLine = vbNewLine + genStopMarker + vbNewLine
Print #fileID, csvLine

Print #fileID, " #endregion" + vbNewLine

Close #fileID

End Sub

############## generierter C# code #######################

#region Generated Code

/// <remarks></remarks>
private const double CC_CLK_TIME = 0.000000025;

/// <remarks>aus KFlexRay/defines.h</remarks>
private const double FR_PD_MICROTICK = 0.025;

/// <remarks>aus KFlexRay/defines.h</remarks>
private const double FR_PD_MICROTICK_RECIPROCAL = 40;

public bool Generate(CLUSTERTYPE2 cluster, CONTROLLERTYPE1 controller, KChiGenParams parameters, ref StringCollection messages)
{
UInt16 reg;
UInt16 value;
int warnings = 0;

genparameters = parameters;

reg = 0x8E; value = (UInt16)cluster.NUMBEROFSTATICSLOTS;
if (CheckRange(ref messages, "NSSR", value, 2, 1023) == false) warnings++;
Add(CMDTYPE.WRITE16, "Register NSSR (calculated)", reg, value);

reg = 0x94; value = (UInt16)this.regSYNCHR;
Add(CMDTYPE.WRITE16, "Register SYNCFR (calculated)", reg, value);

reg = 0x96; value = (UInt16)0x0000;
Add(CMDTYPE.WRITE16, "Register SYNCHR (default)", reg, value);

reg = 0x9A; value = (UInt16)this.regHIPDSR;
Add(CMDTYPE.WRITE16, "Register HIPDSR (calculated)", reg, value);

reg = 0x9C; value = (UInt16)this.regPLPDSR;
Add(CMDTYPE.WRITE16, "Register PLPDSR (calculated)", reg, value);

reg = 0xA8; value = (UInt16)(UInt16)(cluster.BIT / lengthOfOneMicrotick);
Add(CMDTYPE.WRITE16, "Register BDR (calculated)", reg, value);

reg = 0xAA; value = (UInt16)cluster.DYNAMICSLOTIDLEPHASE;
Add(CMDTYPE.WRITE16, "Register IDLR (calculated)", reg, value);

reg = 0xB2; value = (UInt16)cluster.MACROPERCYCLE;
Add(CMDTYPE.WRITE16, "Register CLR (calculated)", reg, value);

reg = 0xDC; value = (UInt16)controller.RATECORRECTIONOUT;
Add(CMDTYPE.WRITE16, "Register MRCR (calculated)", reg, value);

reg = 0xE0; value = (UInt16)cluster.OFFSETCORRECTIONSTART;
Add(CMDTYPE.WRITE16, "Register SOCCTR (calculated)", reg, value);

return true; //(warnings == 0);
}

#endregion