0
\$\begingroup\$

I am making a tool to read 3 input files in CSV and then filling an XLSX file with the information from the CSV. I want to know if I am repeating myself and if I can make it better and faster. Currently the entire migration time of the files is 2:30 minutes approximately. I will shorten the code to be easier to review.

I created an class to each input CSV file. The three files are "SddtRtu.cs", "SddtPtoestse.cs" and "SddtPtodadSe.cs". I will just show the "SddtRtu.cs" because the other two have the same pattern.

using System;
using System.IO;
using System.Text;

namespace ScadaDataMigrationTool.InputClasses.SDDT
{
    class SddtRtu
    {
        private static int _totalLines;// Variable created to store the total number of lines
        private static int _lineNum;// Variable created to mark the row that is being read
        //The following variables serve the purpose to store the column number of the field, since the file may change in further development
        private static int _utr_numCol;
        private static int _mnem_seCol;
        private static int _nome_seCol;
        private static int _ordemCol;
        private static int _tiporem_numCol;
        private static int _tipocomuCol;
        private static int _dnpCol;
        private static int _protocoloCol;
        private static int _commexptimeoutCol;
        private static int _request_timeoutCol;
        private static int _tempocongCol;
        private static int _ind_comissionamentoCol;
        private static int _fabricanteCol;
        private static int _modeloCol;

        //Variables created to store the data from the columns previously identified 
        private static string[] _utr_num;
        private static string[] _mnem_se;
        private static string[] _nome_se;
        private static string[] _ordem;
        private static string[] _tiporem_num;
        private static string[] _tipocomu;
        private static string[] _dnp;
        private static string[] _protocolo;
        private static string[] _commexptimeout;
        private static string[] _request_timeout;
        private static string[] _tempocong;
        private static string[] _ind_comissionamento;
        private static string[] _fabricante;
        private static string[] _modelo;

        //Variables created to identify the protocol of each equipment (ROW) 
        public static int[] _protDNP;//Variable to store the number of the row of that has an equipment with Protocol DNP3
        public static int _dnpLine;//Variable that helps to count how many rows with that protocol exists 
        public static int[] _protICCP;//Variable to store the number of the row of that has an equipment with Protocol ICCP
        public static int _iccpLine;//Variable that helps to count how many rows with that protocol exists 
        public static int[] _protMODBUS;//Variable to store the number of the row of that has an equipment with Protocol MODBUS
        public static int _modLine;//Variable that helps to count how many rows with that protocol exists 
        public static int[] _protIEC104;//Variable to store the number of the row of that has an equipment with Protocol IEC 101
        public static int _104Line;//Variable that helps to count how many rows with that protocol exists 
        private static bool _doNotRepeadRtuHeader;// Variable created to not repeat the reading of the header

        public static int TotalLines
        {
            get
            {
                return _totalLines;
            }
            set
            {
                if (value == 1)
                {

                    StreamReader _lineReader = new StreamReader(path: SddtRtu.Folder, encoding: Encoding.Default);//Inicializador da leitura de dados
                    while (_lineReader.ReadLine() != null) { SddtRtu._totalLines++; }
                }
            }
        }

        public static bool Header
        {
            get
            {
                if (_doNotRepeadRtuHeader == true)
                {
                    return true;
                }

                else
                {
                    return false;
                }
            }

            set
            {
                if (value == true)
                {
                    //Variable used to help the counting of the cloumns
                    int _headerCol = 0;
                    StreamReader _headerReader = new StreamReader(path: SddtRtu.Folder, encoding: Encoding.Default);
                    // It reads the first line and identify the name of each header
                    var _header = _headerReader.ReadLine().Split(',');
                    foreach (string _content in _header)
                    {
                        switch (_header[_headerCol])
                        {
                            case "UTR_NUM":
                                SddtRtu._utr_numCol = _headerCol;
                                break;

                            case "MNEM_SE":
                                SddtRtu._mnem_seCol = _headerCol;
                                break;

                            case "NOME_SE":
                                SddtRtu._nome_seCol = _headerCol;
                                break;

                            case "ORDEM":
                                SddtRtu._ordemCol = _headerCol;
                                break;

                            case "TIPOREM_NUM":
                                SddtRtu._tiporem_numCol = _headerCol;
                                break;

                            case "TIPOCOMU":
                                SddtRtu._tipocomuCol = _headerCol;
                                break;

                            case "DNP":
                                SddtRtu._dnpCol = _headerCol;
                                break;

                            case "PROTOCOLO":
                                SddtRtu._protocoloCol = _headerCol;
                                break;

                            case "COMMEXPTIMEOUT":
                                SddtRtu._commexptimeoutCol = _headerCol;
                                break;

                            case "REQUEST_TIMEOUT":
                                SddtRtu._request_timeoutCol = _headerCol;
                                break;

                            case "TEMPOCONG":
                                SddtRtu._tempocongCol = _headerCol;
                                break;

                            case "IND_COMISSIONAMENTO":
                                SddtRtu._ind_comissionamentoCol = _headerCol;
                                break;

                            case "FABRICANTE":
                                SddtRtu._fabricanteCol = _headerCol;
                                break;

                            case "MODELO":
                                SddtRtu._modeloCol = _headerCol;
                                break;
                        }
                        _headerCol++;
                    }
                    // After the first read the variable below shows that is already read
                    _doNotRepeadRtuHeader = true;
                }
            }
        }

        // It reads the rest of the file
        public static bool Read
        {
            set
            {
                if (value == true)
                {
                    SddtRtu._protDNP = new int[_totalLines];
                    SddtRtu._protICCP = new int[_totalLines];
                    SddtRtu._protMODBUS = new int[_totalLines];
                    SddtRtu._protIEC104 = new int[_totalLines];
                    SddtRtu._utr_num = new string[_totalLines];
                    SddtRtu._mnem_se = new string[_totalLines];
                    SddtRtu._nome_se = new string[_totalLines];
                    SddtRtu._ordem = new string[_totalLines];//ORDEM
                    SddtRtu._tiporem_num = new string[_totalLines];
                    SddtRtu._tipocomu = new string[_totalLines];
                    SddtRtu._dnp = new string[_totalLines];
                    SddtRtu._protocolo = new string[_totalLines];
                    SddtRtu._commexptimeout = new string[_totalLines];
                    SddtRtu._request_timeout = new string[_totalLines];
                    SddtRtu._tempocong = new string[_totalLines];
                    SddtRtu._ind_comissionamento = new string[_totalLines];
                    SddtRtu._fabricante = new string[_totalLines];
                    SddtRtu._modelo = new string[_totalLines];

                    //Inicializador da leitura de dados
                    StreamReader _reader = new StreamReader(path: SddtRtu.Folder, encoding: Encoding.Default);
                    _reader.ReadLine();// Reads the header (first line) before reading the data

                    _dnpLine = 0;
                    _iccpLine = 0;
                    _104Line = 0;
                    _modLine = 0;
                    _lineNum = 1;

                    while (!_reader.EndOfStream)
                    {
                        var line = _reader.ReadLine();
                        var values = line.Split(',');
                        SddtRtu._utr_num[_lineNum] = values[SddtRtu._utr_numCol];
                        SddtRtu._mnem_se[_lineNum] = values[SddtRtu._mnem_seCol];
                        SddtRtu._nome_se[_lineNum] = values[SddtRtu._nome_seCol];
                        SddtRtu._ordem[_lineNum] = values[SddtRtu._ordemCol];
                        SddtRtu._tiporem_num[_lineNum] = values[SddtRtu._tiporem_numCol];
                        SddtRtu._tipocomu[_lineNum] = values[SddtRtu._tipocomuCol];
                        SddtRtu._dnp[_lineNum] = values[SddtRtu._dnpCol];
                        SddtRtu._protocolo[_lineNum] = values[SddtRtu._protocoloCol];
                        //Identification of the type of protocol of the line
                        switch (SddtRtu._protocolo[_lineNum])
                        {
                            case "DNP IP":
                                SddtRtu._protDNP[_dnpLine] = _lineNum;
                                _dnpLine++;
                                break;

                            case "ICCP":
                                SddtRtu._protICCP[_iccpLine] = _lineNum;
                                _iccpLine++;
                                break;

                            case "MODBUS":
                                SddtRtu._protMODBUS[_modLine] = _lineNum;
                                _modLine++;
                                break;

                            case "IEC104":
                                SddtRtu._protIEC104[_104Line] = _lineNum;
                                _modLine++;
                                break;
                        }
                        SddtRtu._commexptimeout[_lineNum] = values[SddtRtu._commexptimeoutCol];
                        SddtRtu._request_timeout[_lineNum] = values[SddtRtu._request_timeoutCol];
                        SddtRtu._tempocong[_lineNum] = values[SddtRtu._tempocongCol];
                        SddtRtu._ind_comissionamento[_lineNum] = values[SddtRtu._ind_comissionamentoCol];
                        SddtRtu._fabricante[_lineNum] = values[SddtRtu._fabricanteCol];
                        SddtRtu._modelo[_lineNum] = values[SddtRtu._modeloCol];
                    }
                    // The lines below will remove every value 0 stored in the protocol variables
                    _protDNP = Array.FindAll(_protDNP, n => n != 0);
                    _protICCP = Array.FindAll(_protICCP, n => n != 0);
                    _protMODBUS = Array.FindAll(_protMODBUS, n => n != 0);
                    _protIEC104 = Array.FindAll(_protIEC104, n => n != 0);
                }
            }
        }

        // The methods below have the purpose of returning the values of an specific line 
        public static int Utr_num(int _line)
        {
            return int.Parse(_utr_num[_line]);
        }

        public static string Mnem_se(int _line)
        {
            return _mnem_se[_line];
        }

        public static string Nome_se(int _line)
        {
            return _nome_se[_line];
        }

        public static string Ordem(int _line)
        {
            return _ordem[_line];
        }

        public static string Tiporem_num(int _line)
        {
            return _tiporem_num[_line];
        }

        public static string Tipocomu(int _line)
        {
            return _tipocomu[_line];
        }

        public static string Dnp(int _line)
        {
            return _dnp[_line];
        }

        public static string Protocolo(int _line)
        {
            return _protocolo[_line];
        }

        public static string Commexptimeout(int _line)
        {
            return _commexptimeout[_line];
        }

        public static string Request_timeout(int _line)
        {
            return _request_timeout[_line];
        }

        public static int Tempocong(int _line)
        {
            if (_line < TotalLines)
            {
                return Convert.ToInt32(_tempocong[_line]);
            }

            return 0;
        }

        public static string Ind_comissionamento(int _line)
        {
            return _ind_comissionamento[_line];
        }

        public static string Fabricante(int _line)
        {
            return _fabricante[_line];
        }

        public static string Modelo(int _line)
        {
            return _modelo[_line];
        }
    }
}

After the input class I created an output class with all protocols that will be migrated. I will show "Dnp3.cs" because the other two follow the same pattern and I will shorten the code to avoid being too big, it follows the same pattern. This class is created using mostly the EPPlus library to handle excel.

Image of the DNP3_RTUs worksheet of the output DNP3 file

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Table;

namespace ScadaDataMigrationTool.Template
{
    public class Dnp3
    {
        private static ExcelPackage _dnp3Package;// Variable created to store the Excel package
        private static ExcelWorksheet _worksheet1;// Variable created to store the worksheet DNP3_RTUs
        private static ExcelWorksheet _worksheet2; // Variable created to store the worksheet DNP3_ScanGroups
        private static FileInfo _templateInfo;
        private static FileInfo _newDnp3FileInfo;
        private static string _template;
        private static string _newDnp3File;
        private static int _column; // Variable created to make the count of columns of the property Header

        public static string NewFile
        {
            get
            {
                if (_newDnp3FileInfo.Exists == true)
                {
                    return "true";
                }


                else if (_newDnp3FileInfo.Exists == false)
                {
                    return "false";
                }

                return "";
            }

            set
            {
                string _path;
                _path = Directory.GetCurrentDirectory(); 
                _template = Path.Combine(_path, "TemplateFiles", "TDT", "TDT_DNP3_CPFL.xlsx");
                _templateInfo = new FileInfo(_template);

                _newDnp3File = value;
                _newDnp3FileInfo = new FileInfo(_newDnp3File); 

                if (_newDnp3FileInfo.Exists == false)
                {                      
                    _dnp3Package = new ExcelPackage(_newDnp3FileInfo, _templateInfo);
                    _worksheetInfo = Dnp3._dnp3Package.Workbook.Worksheets["Info"];
                    _worksheet1 = Dnp3._dnp3Package.Workbook.Worksheets["DNP3_RTUs"];
                    _worksheet2 = Dnp3._dnp3Package.Workbook.Worksheets["DNP3_ScanGroups"];                
                }
            }
        }

        public static bool Save
        {
            set
            {
                if (value == true)
                {
                    int _line = 1;
                    var _range1 = _worksheet1.Dimension;
                    // It makes the count of every not empty row on the worksheet
                    while ((_worksheet1.Cells[Row: _line, Col: 1].Value != null) && (_worksheet1.Cells[Row: _line, Col: 1].Text != ""))
                    {
                        ++_line;
                    }
                    // it deletes the worksheet empty rows
                    if (_line > 5)
                    {
                        _worksheet1.DeleteRow(_line, 1 + _range1.End.Row - _line);
                    }


                    _line = 1;
                    var _range2 = _worksheet2.Dimension;

                    while ((_worksheet2.Cells[Row: _line, Col: 1].Value != null) && (_worksheet2.Cells[Row: _line, Col: 1].Text != ""))
                    {
                        ++_line;
                    }

                    if (_line > 5)
                    {
                        _worksheet2.DeleteRow(_line, 1 + _range2.End.Row - _line);
                    }

                    // Method to save the package
                    Dnp3._dnp3Package.Save();
                }
            }
        }

        public class DNP3_RTUs : Dnp3
        {
            private static int _idobj_nameCol;
            private static int _idobj_aliasCol;
            private static int _idobj_aorgroupCol;
            private static int _psr_locationCol;
            private static int _equipment_contCol;
            private static int _rtu_typeCol;
            private static int _rtu_timezoneCol;
            private static int _rtu_usedstCol;
            private static int _rtu_parentremoteCol;
            private static int _remoteterminalunit_listenonlymodeCol;
            private static int _rtu_initialpollCol;
            private static int _rtu_cmdexpirationtimeoutCol;
            private static int _rtu_enablecmdqueueingCol;
            private static bool _doNotRepeatDnp3Header1;

            public static bool Header
            {
                get
                {
                    if (_doNotRepeatDnp3Header1 == true)
                    {
                        return true;
                    }

                    else
                    {
                        return false;
                    }
                }

                set
                {
                    _column = 1;
                    _idobj_nameCol = 1;
                    _idobj_aliasCol = 1;
                    _idobj_aorgroupCol = 1;
                    _psr_locationCol = 1;
                    _equipment_contCol = 1;
                    _rtu_typeCol = 1;
                    _rtu_timezoneCol = 1;
                    _rtu_usedstCol = 1;
                    _rtu_parentremoteCol = 1;
                    _remoteterminalunit_listenonlymodeCol = 1;
                    _rtu_initialpollCol = 1;
                    _rtu_cmdexpirationtimeoutCol = 1;
                    _rtu_enablecmdqueueingCol = 1;

                    while (_worksheet1.Cells[Row: 3, Col: _column].Value != null)
                    {
                        switch (_worksheet1.Cells[Row: 3, Col: _column].Value)
                        {
                            case "IDOBJ_NAME":
                                _idobj_nameCol = _column;
                                break;

                            case "IDOBJ_ALIAS":
                                _idobj_aliasCol = _column;
                                break;

                            case "IDOBJ_AORGROUP":
                                _idobj_aorgroupCol = _column;
                                break;

                            case "PSR_LOCATION":
                                _psr_locationCol = _column;
                                break;

                            case "EQUIPMENT_CONT":
                                _equipment_contCol = _column;
                                break;

                            case "RTU_TYPE":
                                _rtu_typeCol = _column;
                                break;

                            case "RTU_TIMEZONE":
                                _rtu_timezoneCol = _column;
                                break;

                            case "RTU_USEDST":
                                _rtu_usedstCol = _column;
                                break;

                            case "RTU_PARENTREMOTE":
                                _rtu_parentremoteCol = _column;
                                break;

                            case "REMOTETERMINALUNIT_LISTENONLYMODE":
                                _remoteterminalunit_listenonlymodeCol = _column;
                                break;

                            case "RTU_INITIALPOLL":
                                _rtu_initialpollCol = _column;
                                break;

                            case "RTU_CMDEXPIRATIONTIMEOUT":
                                _rtu_cmdexpirationtimeoutCol = _column;
                                break;

                            case "RTU_ENABLECMDQUEUEING":
                                _rtu_enablecmdqueueingCol = _column;
                                break;
                        }
                        _column++;
                    }

                    _doNotRepeatDnp3Header1 = true;
                }
            }

            public static string IDOBJ_NAME(int line, string data)
            {
                line = line + 5; //The row starts on 5 because of the headers above
                // The "InsertRow" method is to keep the validations from the previous line, it exists just in the first column
                if (line != 5)
                {
                    _worksheet1.InsertRow(line, 1, 5);
                }
                // The property below inserts the data on the desired cell
                _worksheet1.Cells[line, _idobj_nameCol].Value = data;
                // The properties below change the font and the font size
                _worksheet1.Cells[line, _idobj_nameCol].Style.Font.Size = 11; 
                _worksheet1.Cells[line, _idobj_nameCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _idobj_nameCol].Value.ToString();
            }

            public static string IDOBJ_ALIAS(int line, string data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _idobj_aliasCol].Value = data;
                _worksheet1.Cells[line, _idobj_aliasCol].Style.Font.Size = 11;
                _worksheet1.Cells[line, _idobj_aliasCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _idobj_aliasCol].Value.ToString();
            }

            public static string IDOBJ_AORGROUP(int line, string data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _idobj_aorgroupCol].Value = data;
                _worksheet1.Cells[line, _idobj_aorgroupCol].Style.Font.Size = 11;
                _worksheet1.Cells[line, _idobj_aorgroupCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _idobj_aorgroupCol].Value.ToString();
            }

            public static string PSR_LOCATION(int line, string data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _psr_locationCol].Value = data;
                _worksheet1.Cells[line, _psr_locationCol].Style.Font.Size = 11;
                _worksheet1.Cells[line, _psr_locationCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _psr_locationCol].Value.ToString();
            }

            public static string EQUIPMENT_CONT(int line, string data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _equipment_contCol].Value = data;
                _worksheet1.Cells[line, _equipment_contCol].Style.Font.Size = 11;
                _worksheet1.Cells[line, _equipment_contCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _equipment_contCol].Value.ToString();
            }

            public static string RTU_TYPE(int line, string data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _rtu_typeCol].Value = data;
                _worksheet1.Cells[line, _rtu_typeCol].Style.Font.Size = 11;
                _worksheet1.Cells[line, _rtu_typeCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _rtu_typeCol].Value.ToString();
            }

            public static string RTU_TIMEZONE(int line, string data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _rtu_timezoneCol].Value = data;
                _worksheet1.Cells[line, _rtu_timezoneCol].Style.Font.Size = 11; // Altera o tamanho da fonte
                _worksheet1.Cells[line, _rtu_timezoneCol].Style.Font.Name = "Calibri"; // Altera a fonte
                return _worksheet1.Cells[line, _rtu_timezoneCol].Value.ToString();
            }

            public static string RTU_USEDST(int line, bool data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _rtu_usedstCol].Value = data;
                _worksheet1.Cells[line, _rtu_usedstCol].Style.Font.Size = 11; 
                _worksheet1.Cells[line, _rtu_usedstCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _rtu_usedstCol].Value.ToString();
            }

            public static string RTU_PARENTREMOTE(int line, string data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _rtu_parentremoteCol].Value = data;
                _worksheet1.Cells[line, _rtu_parentremoteCol].Style.Font.Size = 11;
                _worksheet1.Cells[line, _rtu_parentremoteCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _rtu_parentremoteCol].Value.ToString();
            }

            public static string REMOTETERMINALUNIT_LISTENONLYMODE(int line, bool data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _remoteterminalunit_listenonlymodeCol].Value = data;
                _worksheet1.Cells[line, _remoteterminalunit_listenonlymodeCol].Style.Font.Size = 11;
                _worksheet1.Cells[line, _remoteterminalunit_listenonlymodeCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _remoteterminalunit_listenonlymodeCol].Value.ToString();
            }

            public static string RTU_INITIALPOLL(int line, bool data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _rtu_initialpollCol].Value = data;
                _worksheet1.Cells[line, _rtu_initialpollCol].Style.Font.Size = 11;
                _worksheet1.Cells[line, _rtu_initialpollCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _rtu_initialpollCol].Value.ToString();
            }

            public static string RTU_CMDEXPIRATIONTIMEOUT(int line, int data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _rtu_cmdexpirationtimeoutCol].Value = data;
                _worksheet1.Cells[line, _rtu_cmdexpirationtimeoutCol].Style.Font.Size = 11;
                _worksheet1.Cells[line, _rtu_cmdexpirationtimeoutCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _rtu_cmdexpirationtimeoutCol].Value.ToString();
            }

            public static string RTU_ENABLECMDQUEUEING(int line, bool data)
            {
                line = line + 5;
                _worksheet1.Cells[line, _rtu_enablecmdqueueingCol].Value = data;
                _worksheet1.Cells[line, _rtu_enablecmdqueueingCol].Style.Font.Size = 11;
                _worksheet1.Cells[line, _rtu_enablecmdqueueingCol].Style.Font.Name = "Calibri";
                return _worksheet1.Cells[line, _rtu_enablecmdqueueingCol].Value.ToString();
            }
        }

        public class DNP3_ScanGroups : Dnp3
        {
            private static int _scangroupdnp3_rtuCol;
            private static int _scangroupdnp3_pollcycleCol;
            private static int _scangroupdnp3_objgroupCol;
            private static int _scangroupdnp3_objvariationCol;
            private static int _scangroupdnp3_allpointsCol;
            private static int _scangroupdnp3_startcoordCol;
            private static int _scangroupdnp3_endcoordCol;
            private static bool _doNotRepeatDnp3Header2;

            public static bool Header
            {
                get
                {
                    if (_doNotRepeatDnp3Header2 == true)
                    {
                        return true;
                    }

                    else
                    {
                        return false;
                    }
                }

                set
                {
                    _column = 1;
                    _scangroupdnp3_rtuCol = 1;
                    _scangroupdnp3_pollcycleCol = 1;
                    _scangroupdnp3_objgroupCol = 1;
                    _scangroupdnp3_objvariationCol = 1;
                    _scangroupdnp3_allpointsCol = 1;
                    _scangroupdnp3_startcoordCol = 1;
                    _scangroupdnp3_endcoordCol = 1;

                    while (_worksheet2.Cells[Row: 3, Col: _column].Value != null)
                    {
                        switch (_worksheet2.Cells[Row: 3, Col: _column].Value)
                        {
                            case "SCANGROUPDNP3_RTU":
                                _scangroupdnp3_rtuCol = _column;
                                break;

                            case "SCANGROUPDNP3_POLLCYCLE":
                                _scangroupdnp3_pollcycleCol = _column;
                                break;

                            case "SCANGROUPDNP3_OBJGROUP":
                                _scangroupdnp3_objgroupCol = _column;
                                break;

                            case "SCANGROUPDNP3_OBJVARIATION":
                                _scangroupdnp3_objvariationCol = _column;
                                break;

                            case "SCANGROUPDNP3_ALLPOINTS":
                                _scangroupdnp3_allpointsCol = _column;
                                break;

                            case "SCANGROUPDNP3_STARTCOORD":
                                _scangroupdnp3_startcoordCol = _column;
                                break;

                            case "SCANGROUPDNP3_ENDCOORD":
                                _scangroupdnp3_endcoordCol = _column;
                                break;

                                }
                                break;
                        }
                        _column++;
                    }

                    _doNotRepeatDnp3Header2 = true;
                }
            }

            public static string SCANGROUPDNP3_RTU(int line, string data)
            {
                line = line + 5;
                if (line != 5)
                {
                    _worksheet2.InsertRow(line, 1, 5);
                }
                _worksheet2.Cells[line, _scangroupdnp3_rtuCol].Value = data; 
                _worksheet2.Cells[line, _scangroupdnp3_rtuCol].Style.Font.Size = 11;
                _worksheet2.Cells[line, _scangroupdnp3_rtuCol].Style.Font.Name = "Calibri";
                return _worksheet2.Cells[line, _scangroupdnp3_rtuCol].Value.ToString();
            }


            public static string SCANGROUPDNP3_POLLCYCLE(int line, string data)
            {
                line = line + 5;
                _worksheet2.Cells[line, _scangroupdnp3_pollcycleCol].Value = data;
                _worksheet2.Cells[line, _scangroupdnp3_pollcycleCol].Style.Font.Size = 11;
                _worksheet2.Cells[line, _scangroupdnp3_pollcycleCol].Style.Font.Name = "Calibri";
                return _worksheet2.Cells[line, _scangroupdnp3_pollcycleCol].Value.ToString();
            }

            public static string SCANGROUPDNP3_OBJGROUP(int line, string data)
            {
                line = line + 5;
                _worksheet2.Cells[line, _scangroupdnp3_objgroupCol].Value = data;
                _worksheet2.Cells[line, _scangroupdnp3_objgroupCol].Style.Font.Size = 11; // Altera o tamanho da fonte
                _worksheet2.Cells[line, _scangroupdnp3_objgroupCol].Style.Font.Name = "Calibri"; // Altera a fonte
                return _worksheet2.Cells[line, _scangroupdnp3_objgroupCol].Value.ToString();
            }

            public static string SCANGROUPDNP3_OBJVARIATION(int line, int data)
            {
                line = line + 5;
                _worksheet2.Cells[line, _scangroupdnp3_objvariationCol].Value = data;
                _worksheet2.Cells[line, _scangroupdnp3_objvariationCol].Style.Font.Size = 11;
                _worksheet2.Cells[line, _scangroupdnp3_objvariationCol].Style.Font.Name = "Calibri";
                return _worksheet2.Cells[line, _scangroupdnp3_objvariationCol].Value.ToString();
            }

            public static string SCANGROUPDNP3_ALLPOINTS(int line, bool data)
            {
                line = line + 5;
                _worksheet2.Cells[line, _scangroupdnp3_allpointsCol].Value = data;
                _worksheet2.Cells[line, _scangroupdnp3_allpointsCol].Style.Font.Size = 11;
                _worksheet2.Cells[line, _scangroupdnp3_allpointsCol].Style.Font.Name = "Calibri";
                return _worksheet2.Cells[line, _scangroupdnp3_allpointsCol].Value.ToString();
            }

            public static string SCANGROUPDNP3_STARTCOORD(int line, string data)
            {
                line = line + 5; // A entrada de dados começa na linha 5
                _worksheet2.Cells[line, _scangroupdnp3_startcoordCol].Value = data;
                _worksheet2.Cells[line, _scangroupdnp3_startcoordCol].Style.Font.Size = 11;
                _worksheet2.Cells[line, _scangroupdnp3_startcoordCol].Style.Font.Name = "Calibri";
                return _worksheet2.Cells[line, _scangroupdnp3_startcoordCol].Value.ToString();
            }

            public static string SCANGROUPDNP3_ENDCOORD(int line, string data)
            {
                line = line + 5;
                _worksheet2.Cells[line, _scangroupdnp3_endcoordCol].Value = data;
                _worksheet2.Cells[line, _scangroupdnp3_endcoordCol].Style.Font.Size = 11;
                _worksheet2.Cells[line, _scangroupdnp3_endcoordCol].Style.Font.Name = "Calibri";
                return _worksheet2.Cells[line, _scangroupdnp3_endcoordCol].Value.ToString();
            }
        }
    }

The next class I use is to relate every input data to the output data.

using ScadaDataMigrationTool.InputClasses.SDDT;
using ScadaDataMigrationTool.Template;

namespace ScadaDataMigrationTool.InputClasses
{
    class Mapping
    {
        // Variable to define the row on the input file
        private static int _inputCount;
        // Variable to define the row on the output file
        private static int _outputCount;

        public static string Input { get; set; }

        // Mapping of the DNP3 protocol
        public class TDT_DNP3_Mapping : Mapping
        {
            // Property to map the worksheet DNP3_RTUs
            public static int DNP3_RTUs
            {
                set
                {
                    // The value 0 resets the counting 
                    if (value == 0)
                    {
                        // if the identification of the header is not done then it will start
                        if (Dnp3.DNP3_RTUs.Header == false) Dnp3.DNP3_RTUs.Header = true;
                        _outputCount = 0;
                        _inputCount = 0;
                    }

                    if (value > 0)
                    {
                        _inputCount = value;// Value to start the counting

                        // If the type of input file is "RTU" then the mapping starts
                        if (Input == "RTU")// Subestação DNP3
                        {
                            // Preenchimento dos campos segundo o documento de mapeamento
                            Dnp3.DNP3_RTUs.IDOBJ_NAME(_outputCount, "UTR_" + SddtRtu.Mnem_se(_inputCount) + "_" + SddtRtu.Ordem(_inputCount));

                            Dnp3.DNP3_RTUs.IDOBJ_ALIAS(_outputCount, SddtRtu.Nome_se(_inputCount) + "_" + SddtRtu.Ordem(_inputCount));

                            Dnp3.DNP3_RTUs.PSR_LOCATION(_outputCount, SddtRtu.Fabricante(_inputCount) + " - " + SddtRtu.Modelo(_inputCount));

                            Dnp3.DNP3_RTUs.EQUIPMENT_CONT(_outputCount, SddtRtu.Nome_se(_inputCount));

                            Dnp3.DNP3_RTUs.RTU_TYPE(_outputCount, "RTU");

                            Dnp3.DNP3_RTUs.RTU_TIMEZONE(_outputCount, "(UTC-03:00) Brasilia");

                            Dnp3.DNP3_RTUs.RTU_USEDST(_outputCount, false);

               Dnp3.DNP3_RTUs.REMOTETERMINALUNIT_LISTENONLYMODE(_outputCount, false);

                            Dnp3.DNP3_RTUs.RTU_INITIALPOLL(_outputCount, true);

                            Dnp3.DNP3_RTUs.RTU_CMDEXPIRATIONTIMEOUT(_outputCount, 20);

                            Dnp3.DNP3_RTUs.RTU_ENABLECMDQUEUEING(_outputCount, false);

                            _outputCount++;
                        }
                    }
                }
            }

            // Property to map the worksheet DNP3_ScanGroups
            public static int DNP3_ScanGroups
            {
                set
                {
                    if (value == 0)
                    {
                        if (Dnp3.DNP3_ScanGroups.Header == false) Dnp3.DNP3_ScanGroups.Header = true;
                        _outputCount = 0;
                        _inputCount = 0;
                    }

                    if (value > 0)
                    {
                        _inputCount = value;// Valor para iniciar o mapeamento

                        if (Input == "RTU")
                        {
                            Dnp3.DNP3_ScanGroups.SCANGROUPDNP3_RTU(_outputCount, "UTR_" + SddtRtu.Mnem_se(_inputCount) + "_" + SddtRtu.Ordem(_inputCount));

                            Dnp3.DNP3_ScanGroups.SCANGROUPDNP3_POLLCYCLE(_outputCount, "3600");

                            Dnp3.DNP3_ScanGroups.SCANGROUPDNP3_OBJGROUP(_outputCount, "BinaryInput");

                            Dnp3.DNP3_ScanGroups.SCANGROUPDNP3_OBJVARIATION(_outputCount, 2);

                            Dnp3.DNP3_ScanGroups.SCANGROUPDNP3_ALLPOINTS(_outputCount, true);

                            _outputCount++;
                        }
                    }
                }
            }
        }
    }
}

The Class "Classification.cs" server the purpose of store call the number of rows (equipments) with certain protocols.

using ScadaDataMigrationTool.InputClasses.SDDT;

namespace ScadaDataMigrationTool.InputClasses
{
    class Classification
    {
        public static int Dnp3
        {
            get
            {
                switch (Mapping.Input)
                {
                    case "RTU":
                        return SddtRtuOriginal._dnpLine;

                    case "ptoestse":
                        return SddtPtoestse._dnpLine;

                    case "ptodadse":
                        return SddtPtodadse._dnpLine;
                }
            }
            set
            {
                switch (Mapping.Input)
                {
                    case "RTU":
                        SddtRtuOriginal._dnpLine = SddtRtuOriginal._protDNP[value];
                        break;

                    case "ptoestse":
                        SddtPtoestse._dnpLine = SddtPtoestse._protDNP[value];
                        break;

                    case "ptodadse":
                        SddtPtodadse._dnpLine = SddtPtodadse._protDNP[value];
                        break;
                }
            }
        }

        public static int DnpCount
        {
            get
            {
                switch (Mapping.Input)
                {
                    case "RTU":
                        return SddtRtuOriginal._protDNP.Length;

                    case "ptoestse":
                        return SddtPtoestse._protDNP.Length;

                    case "ptodadse":
                        return SddtPtodadse._protDNP.Length;
                }
            }
        }
    }
}

After the mapping the I use a Backgroundworker to apply the mapping to the files. On the form (Image below) I enter 3 files already cited above. The variable _index have the purpose of select which file will be read (First the "SddtRtu", second the "SddtPtoestse" then "SddtPtodadse").

enter image description here

private void _backgroundWorkerSddtSubstation_DoWork(object sender, System.ComponentModel.DoWorkEventArgs a)
{
    int _index = 0;

    SignalsExplanation.TempFile = true;
    Mapping.Input = "RTU";
    SddtRtu.TotalLines = 1;
    if (SddtRtu.Header == false) SddtRtu.Header = true;
    SddtRtu.Read = true;

        if (Classification.DnpCount != 0)
        {
        // Esta linha serve para criar o nome do arquivo de saída e dividir seu caminho em uma array 
        string[] arrayPathDnp3 = { FolderBrowserDialog.SelectedPath, "TDT_DNP3_" + NewFileNameRTU + ".xlsx" };
        // Esta linha serve para combinar os itens do array para criar um path
        string filePathDnp3 = Path.Combine(arrayPathDnp3);
        // Manda o caminho do arquivo de entrada para a property de criação do novo arquivo
        Dnp3.NewFile = filePathDnp3;
        // Variavel criada para fazer a contagem de versões existentes do arquivo (Caso exista)
        int _dnp3VersionCount = 0;
        // Faz a contagem das versões existentes do arquivo caso existam
        while (Dnp3.NewFile == "true")
        {
            _dnp3VersionCount++;
            filePathDnp3 = Path.Combine(FolderBrowserDialog.SelectedPath, "TDT_DNP3_" + NewFileNameRTU + _dnp3VersionCount + ".xlsx");
            Dnp3.NewFile = filePathDnp3;
        }

    while (_index < 3)
    {
        if (_index == 0)
        {
            Mapping.Input = "RTU";
            SddtRtu.TotalLines = 1;
            if (SddtRtu.Header == false) SddtRtu.Header = true;
            SddtRtu.Read = true;
        }

        else if (_index == 1)
        {
            SignalsExplanation.Sddt.Header = true;
            Mapping.Input = "ptoestse";
            SddtPtoestse.TotalLines = 1;
            if (SddtPtoestse.Header == false) SddtPtoestse.Header = true;
            SddtPtoestse.Read = true;
        }

        else if (_index == 2)
        {
            SignalsExplanation.Sddt.Header = true;
            Mapping.Input = "ptodadse";
            SddtPtodadse.TotalLines = 1;
            if (SddtPtodadse.Header == false) SddtPtodadse.Header = true;
            SddtPtodadse.Read = true;
        }

        if (Classification.DnpCount != 0)
        {
            // Variável criada para fazer a contagem de linhas de itens DNP3
            int _dnp3Linecount;

            //Valor inserido para reiniciar os contadores de linha
            Mapping.TDT_DNP3_Mapping.DNP3_RTUs = 0;
            // Preenchimento da worksheet DNP3_RTUs
            for (_dnp3Linecount = 0; _dnp3Linecount < Classification.DnpCount; ++_dnp3Linecount)
            {
                // Esta linha serve para identificar a linha de cada equipamento com protocolo DNP3
                Classification.Dnp3 = _dnp3Linecount;
                // Esta linha serve para fazer o mapeamento da linha e preencher o arquivo
                Mapping.TDT_DNP3_Mapping.DNP3_RTUs = Classification.Dnp3;
            }

            //Valor inserido para reiniciar os contadores de linha
            Mapping.TDT_DNP3_Mapping.DNP3_ScanGroups = 0;
            // Preenchimento da worksheet DNP3_ScanGroups
            for (_dnp3Linecount = 0; _dnp3Linecount < Classification.DnpCount; ++_dnp3Linecount)
            {
                // Esta linha serve para identificar a linha de cada equipamento com protocolo DNP3
                Classification.Dnp3 = _dnp3Linecount;
                // Esta linha serve para fazer o mapeamento da linha e preencher o arquivo
                Mapping.TDT_DNP3_Mapping.DNP3_ScanGroups = Classification.Dnp3;
            }

            //Valor inserido para reiniciar os contadores de linha
            Mapping.TDT_DNP3_Mapping.DNP3_CommLinks = 0;
            // Preenchimento da worksheet DNP3_CommLinks
            for (_dnp3Linecount = 0; _dnp3Linecount < Classification.DnpCount; ++_dnp3Linecount)
            {
                // Esta linha serve para identificar a linha de cada equipamento com protocolo DNP3
                Classification.Dnp3 = _dnp3Linecount;
                // Esta linha serve para fazer o mapeamento da linha e preencher o arquivo
                Mapping.TDT_DNP3_Mapping.DNP3_CommLinks = Classification.Dnp3;
            }

            //Valor inserido para reiniciar os contadores de linha
            Mapping.TDT_DNP3_Mapping.DNP3_DiscreteSignals = 0;
            // Preenchimento da worksheet DNP3_DiscreteSignals
            for (_dnp3Linecount = 0; _dnp3Linecount < Classification.DnpCount; ++_dnp3Linecount)
            {
                // Esta linha serve para identificar a linha de cada equipamento com protocolo DNP3
                Classification.Dnp3 = _dnp3Linecount;
                // Esta linha serve para fazer o mapeamento da linha e preencher o arquivo
                Mapping.TDT_DNP3_Mapping.DNP3_DiscreteSignals = Classification.Dnp3;
            }

            //Valor inserido para reiniciar os contadores de linha
            Mapping.TDT_DNP3_Mapping.DNP3_AnalogSignals = 0;
            // Preenchimento da worksheet DNP3_AnalogSignals
            for (_dnp3Linecount = 0; _dnp3Linecount < Classification.DnpCount; ++_dnp3Linecount)
            {
                // Esta linha serve para identificar a linha de cada equipamento com protocolo DNP3
                Classification.Dnp3 = _dnp3Linecount;
                // Esta linha serve para fazer o mapeamento da linha e preencher o arquivo
                Mapping.TDT_DNP3_Mapping.DNP3_AnalogSignals = Classification.Dnp3;
            }
        }
        _index++;
    }

    if (Classification.DnpCount != 0)
    {
        Dnp3.Save = true;
    }

}

Sorry about, the long question. Please let me know if I can improve the question.

\$\endgroup\$
  • 1
    \$\begingroup\$ Samples of each of the .csv files and what the corresponding .xlsx file would look like, will make it easier to judge the efficiency of your approach. \$\endgroup\$ – tinstaafl Jun 24 at 19:35
  • 2
    \$\begingroup\$ I will shorten the code to be easier to review. - on the contrary; the code is easier to review when it's complete so please update your question and add any parts you may have removed. Shortened code is off-topic. I will retract my close-vote when you can confirm that the code is unchanged. \$\endgroup\$ – t3chb0t Jun 25 at 7:38
  • \$\begingroup\$ Actually the size of the text is limited. I shortened the code to fit here. There is another way to post show my code? \$\endgroup\$ – Lucas Apolinário Jun 25 at 11:19
  • 1
    \$\begingroup\$ On Code Review we have a character limit of ~64k \$\endgroup\$ – Heslacher Jun 25 at 12:11
1
\$\begingroup\$
      int _headerCol = 0;

In C# the convention is to only use the underscore prefix _variableName on fields on objects, while local variables in methods are just named variableName. The idea of the underscore is to distinguish local variables from members, so having them on all variables makes them redundant. Many don't use the underscore at all.

In general, you should be more careful about names:

Dnp3.DNP3_RTUs

This doesn't tell me much, and I doubt it will tell you anything when you have to revise you code in 3 years. And besides that, your naming style with a lot of UPPERCASE names and alternating use of abbreviated and very long names makes your code hard to read, and the overall impression is chaos and complexity - more than it actually is.

Use descriptive and self explaining names for your objects, methods, properties and variables, and in C# the convention is to use PascalCase for object names (MyClass) and methods and properties, and camelCase for fields and local variables.


Is seems that all the methods, properties and fields on your classes are static. That may be a design descision, but be aware that you can't use these classes in parallel. I would consider to define these classes non static so I'll have to instantiate them, when used. In my world static members are only for "libraries" of often used objects (like System.Drawing.Colors), class specific instance-invariant properties, minor helper functions and extensions - and factory methods for convenience. But others have other opinions about that.


public static int TotalLines
{
  get
  {
    return _totalLines;
  }
  set
  {
    if (value == 1)
    {
      StreamReader _lineReader = new StreamReader(path: SddtRtu.Folder, encoding: Encoding.Default);//Inicializador da leitura de dados
      while (_lineReader.ReadLine() != null) { SddtRtu._totalLines++; }
    }
  }
}

IMO the getters and setters of properties should be without heavy computation. Depending on the size of the file you try to read here, the above can result in unexpected slow behavior - seen from the client. I would let the client do the work instead or set the property elsewhere when initializing the object.

Further: What if someone calls TotalLines {get;} before TotalLines { set; } = 1 is called? You'll have to rethink this approach.


// It reads the rest of the file
public static bool Read
{
  set
  {
    if (value == true)
    {
      SddtRtu._protDNP = new int[_totalLines];
      SddtRtu._protICCP = new int[_totalLines];
      SddtRtu._protMODBUS = new int[_totalLines];

As above you do a lot in the setter of a property. This should not be a property a all but a method. And properties without a getter is very rare. I don't think I've ever created on, and I don't see it justified here either.


      StreamReader _headerReader = new StreamReader(path: SddtRtu.Folder, encoding: Encoding.Default);
      // It reads the first line and identify the name of each header
      var _header = _headerReader.ReadLine().Split(',');
      foreach (string _content in _header)
      {
        switch (_header[_headerCol])

Here you split the header string into _headers and define an index variable _headerCol and then you use a foreach loop on the _header with _content representing each string in _header. You're here mixing two for-styles.

Either you should do:

foreach (string content in _header)
{
   switch (content)
   ...

or

for (int headerCol = 0; headerCol < _header.Length; headerCol++)
{
   switch (_header[headerCol])
   ...

and again: _header is the name of an array of headers, so call i headers.


StreamReader _reader = new StreamReader(path: SddtRtu.Folder, encoding: Encoding.Default);

In general you should clean up objects that implement IDisposable in order to release their resources - especially unmanaged resources, so wrap them in using statements:

using (StreamReader _reader = new StreamReader(path: SddtRtu.Folder, encoding: Encoding.Default))
{
  ...
}

 StreamReader _reader = new StreamReader(path: SddtRtu.Folder, encoding: Encoding.Default);
  _reader.ReadLine();// Reads the header (first line) before reading the data

  _dnpLine = 0;
  _iccpLine = 0;
  _104Line = 0;
  _modLine = 0;
  _lineNum = 1;

  while (!_reader.EndOfStream)
  {
    var line = _reader.ReadLine();

Instead of using reader.EndOfStream it is more concise to do:

string line;

while ((line = reader.ReadLine()) != null)
{
  ...
}

  line = line + 5; //The row starts on 5 because of the headers above

You repeatedly use 5 as a magic number. That calls for a named constant field instead of using the literal in place:

private const int rowStart = 5;


line = line + rowStart;

public static string IDOBJ_ALIAS(int line, string data)
{
  line = line + 5;
  _worksheet1.Cells[line, _idobj_aliasCol].Value = data;
  _worksheet1.Cells[line, _idobj_aliasCol].Style.Font.Size = 11;
  _worksheet1.Cells[line, _idobj_aliasCol].Style.Font.Name = "Calibri";
  return _worksheet1.Cells[line, _idobj_aliasCol].Value.ToString();
}

You have a lot of almost identical methods like the above. You should make one method, that takes the variables as input:

private static string SetCells(int line, int column, string data)
{
    line = line + rowStart;
    _worksheet1.Cells[line, column].Value = data;
    _worksheet1.Cells[line, column].Style.Font.Size = 11;
    _worksheet1.Cells[line, column].Style.Font.Name = "Calibri";
    return _worksheet1.Cells[line, column].Value.ToString();
}

and then call that from your public api.

And here you also use the same font size and name a lot of times, so create a field or a property for them. It is much more readable and maintainable.


So all in all your code appears more complex than it really is. You can clean it up and make it more readable and maintainable only by paying a little more attention to naming and avoiding repetitive code (DRY principle).

That said, being able to test your code would probably reveal a lot more to be changed, but this is as far as I can go for now?


When it comes to performance, I'm unable to give any advise from the provided code. But using OfficeOpenXml is normally a better choice than COM - performancewise.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.