用EPPlus填充Excel表

Question

我正在制作一个工具来读取CSV中的3个输入文件，然后用CSV中的信息填充XLSX文件。我想知道我是否在重复自己，我是否能使它变得更好和更快。目前，文件的整个迁移时间约为2:30分钟。我将缩短代码，以便于审查。

我为每个输入的CSV文件创建了一个类。这三个文件是"SddtRtu.cs“、"SddtPtoestse.cs”和"SddtPtodadSe.cs“。我将只显示"SddtRtu.cs“，因为其他两个具有相同的模式。

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];
        }
    }
}

在输入类之后，我创建了一个包含将要迁移的所有协议的输出类。我将显示"Dnp3.cs“，因为其他两个遵循相同的模式，我将缩短代码以避免太大，它遵循相同的模式。This类主要使用EPPlus库来处理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();
            }
        }
    }

我使用的下一个类是将每个输入数据与输出数据关联起来。

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++;
                        }
                    }
                }
            }
        }
    }
}

类"Classification.cs“服务器存储的目的是用一定的协议调用行数(设备)。

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;
                }
            }
        }
    }
}

在映射之后，我使用背景工作者将映射应用于文件。在表格(下图)上，我输入了上面引用的3个文件。变量_index的目的是选择将读取哪个文件(首先是"SddtRtu"，其次是"SddtPtoestse“，然后是"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;
    }

}

抱歉，这个问题太长了。如果我能改进这个问题，请告诉我。

Answer 1

int \_headerCol = 0;

在C#中，惯例是只对对象上的字段使用下划线前缀_variableName，而方法中的局部变量仅命名为variableName。下划线的思想是将局部变量与成员区分开来，因此在所有变量上使用它们就会使它们变得多余。很多人根本不使用下划线。

一般来说，您应该更加小心名称：

Dnp3.DNP3_RTUs

这并不能告诉我多少，我怀疑当你必须在3年内修改你的代码时，它会告诉你什么。此外，您的命名风格有很多UPPERCASE名称，并且交替使用缩写名称和非常长的名称，这会使您的代码难以阅读，总体印象是混乱和复杂--比实际要复杂得多。

对对象、方法、属性和变量使用描述性名称和自解释名称，在C#中，约定是对对象名称(MyClass)和方法和属性使用PascalCase，对字段和局部变量使用camelCase。

似乎类中的所有方法、属性和字段都是静态的。这可能是一个设计简约，但请注意，您不能并行使用这些类。我将考虑非静态地定义这些类，因此在使用时必须实例化它们。在我的世界中，静态成员只适用于经常使用的对象(如System.Drawing.Colors)、类特定实例(不变量属性、次要辅助函数和扩展)以及工厂方法的“库”。但其他人对此有不同的看法。

公共静态int TotalLines { get {返回_totalLines；} set { if (值== 1) { StreamReader _lineReader =新StreamReader(路径: SddtRtu.Folder，编码: Encoding.Default);//Inicializador da leitura de dados而(_lineReader.ReadLine() = null) { SddtRtu._totalLines++；}

属性的获取者和设置者应该不需要大量的计算。根据您在这里试图读取的文件的大小，上面的内容可能会导致意外的缓慢行为--从客户端看到。在初始化对象时，我会让客户端代替该工作，或者在其他地方设置属性。

更进一步:如果有人在调用TotalLines {get;}之前调用TotalLines { set; } = 1，该怎么办？你得重新考虑这个方法。

//它读取文件的其余部分:公共静态bool读取{ set { if (值== true) { SddtRtu._protDNP =新int；SddtRtu._protICCP =新int；SddtRtu._protMODBUS =新int；

如上所示，您在地产的设置者中做了很多事情。这不应该是一个属性，而是一个方法。没有吸气剂的特性是非常罕见的。我认为我从来没有创造过，我也不认为这是有道理的。

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])

在这里，将头字符串拆分为_headers，并定义索引变量_headerCol，然后在_header上使用foreach循环，_content表示_header中的每个字符串。你在这里混合了两个for-styles。

要么你应该做：

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

或

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

再说一遍：_header是一个标题数组的名称，所以请调用I headers。

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

通常，您应该清理实现IDisposable的对象，以便释放它们的资源--特别是非托管资源，因此将它们封装在using语句中：

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

StreamReader _reader =新StreamReader(path: SddtRtu.Folder，编码: Encoding.Default)；_reader.ReadLine();//读取数据头(第一行)，然后读取数据_dnpLine = 0；_iccpLine = 0；_104Line = 0；_modLine = 0；_lineNum = 1；while (!_reader.EndOfStream) { var line = _reader.ReadLine()；

与其使用reader.EndOfStream，不如使用更简洁的方法：

string line;

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

行=行+ 5；//行从5开始，因为上面的标题

您反复使用5作为一个神奇的数字。它调用一个命名常量字段，而不是使用就地的文字：

private const int rowStart = 5;


line = line + rowStart;

公共静态字符串IDOBJ_ALIAS(int行，字符串数据){ line = line + 5；_worksheet1.Cells.Value = data；_worksheet1.Cells.Style.Font.Size = 11；_worksheet1.Cells.Style.Font.Name =“_worksheet1.Cells.Value.ToString()”；}

您有许多几乎相同的方法，如上面所述。您应该创建一个方法，它将变量作为输入：

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();
}

然后从您的公共api中调用它。

在这里，您也使用相同的字体大小和名称很多次，所以为他们创建一个字段或属性。它更具可读性和可维护性。

总之，您的代码看起来比实际的要复杂得多。您可以清理它，使它更加可读性和可维护性，只需更多地注意命名和避免重复的代码( and原则)。

尽管如此，能够测试您的代码可能会显示出更多需要更改的内容，但这是我目前所能做的事情吗？

在性能方面，我无法从提供的代码中给出任何建议。但是使用OfficeOpenXml通常是比COM性能更好的选择。