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

Downloads:

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