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.
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").
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.
.csvfiles and what the corresponding.xlsxfile would look like, will make it easier to judge the efficiency of your approach. \$\endgroup\$