1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;


namespace Converter
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {

        const string fileName = @"N:\Dokumente\Bachelorarbeit\Dateien\Converter - Kopie\Converter\bin\Debug\Testdatei.xls";

        public MainWindow()
        {
            InitializeComponent();
            
            //Read xml and fill dict
            XmlTextReader reader = new XmlTextReader("variablegroups.xml");
            string string_xml = "";
            string str1 = "ecatSource";
            string str2 = "name";
            bool inout = false;
                        
            while (reader.Read())
            {
                switch (reader.NodeType)
                {
                    case XmlNodeType.Element:
                        string_xml = reader.Name;
                        break;

                    case XmlNodeType.Text:
                        if (string_xml.Equals(str1))
                        {
                            inout = true;
                        }

                        if ((string_xml.Equals(str1) || string_xml.Equals(str2)) && inout)
                        {
                            Console.Write(reader.Value); //write value
                            Console.Write("\n");
                            if (string_xml.Equals(str2))
                            {
                                inout = false;
                            }
                        }
                        break;
                }
            }
            Console.ReadLine();


            //Example for variables from XML
            //VarSymbolicDic.Add("-184K1 (CPX-FB38 64Byte).Outputs.QB2.Bit07","Run In Turntable Brake-High pressure");
            //VarSymbolicDic.Add("-184K1 (CPX-FB38 64Byte).Outputs.QB2.Bit08","Run In Turntable Brake-High pressure 2");

            //Read comment from excel
            string feecomment = "TIID.Device1.EtherCAT Simulation.-184K1 (CPX-FB38 64Byte).Outputs.QB2.Bit07";
          
            var result = feecomment.Replace("TIID.Device1.EtherCAT Simulation.", "");
            List<string> allcomments = new List<string>();
            allcomments.Add(result);

            foreach (string comment in allcomments)
            {
                if (VarSymbolicDic.ContainsKey(comment))
                {
                    //replace symbolic name in excel
                }
            }
            //write to excel again
            
        }
        
        //Read Excel
        public void ReadExcel(string fileName)
        {
            using (var file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {

                var wb = WorkbookFactory.Create(file);
                var sheet = wb.GetSheetAt(wb.ActiveSheetIndex);

                MessageBox.Show("Hallo1");

                //Variables.Clear();
                foreach (IRow row in sheet)
                {

                    //Check if it is a TwinCat Excel File
                    var tag = row.GetCell(0)?.ToString() ?? "";
                    var address = row.GetCell(1)?.ToString() ?? "";
                    var vartype = row.GetCell(2)?.ToString() ?? "";
                    var varfullName = row.GetCell(4)?.ToString() ?? "";
                    var usage = row.GetCell(5)?.ToString() ?? "";
                    var comment = row.GetCell(6)?.ToString() ?? "";

                    
                    //Tag = tag,
                    //        Address = address,
                    //        Type = type,
                    //        Path = varfullName,
                    //        Usage  = usageMode,
                 
                }
                wb.Close();
            }
        }
        

        //Write result in excel
        public void WriteExcel(string fileName)
        {
            //Create new Excel Workbook
            var workbook = new HSSFWorkbook();

            //Create new Excel Sheet
            var sheet = workbook.CreateSheet("New Sheet");
            
            //Create a header row
            var headerRow = sheet.CreateRow(0);
            headerRow.CreateCell(0).SetCellValue("Symbol");
            headerRow.CreateCell(1).SetCellValue("Adress");
            headerRow.CreateCell(2).SetCellValue("Type");
            headerRow.CreateCell(3).SetCellValue("Comment");
            headerRow.CreateCell(4).SetCellValue("VarFullName");
            headerRow.CreateCell(5).SetCellValue("Usage");

            //(Optional) freeze the header row so it is not scrolled
            sheet.CreateFreezePane(0, 1, 0, 1);

            int rowNumber = 1;
            //Populate the sheet with values from the grid data
         
                //Create a new Row
                var row = sheet.CreateRow(rowNumber++);

                //Set the Values for Cells
                row.CreateCell(0).SetCellValue("");
                row.CreateCell(1).SetCellValue(""); //Address);
                row.CreateCell(2).SetCellValue(""); //Type.ToString());
                row.CreateCell(3).SetCellValue(""); //Comment);
                row.CreateCell(4).SetCellValue(""); //.Path));
                row.CreateCell(5).SetCellValue(""); //.Usage.ToString()));
         
            //Write the Workbook to a memory stream


            try
            {
                FileStream fileOut = new FileStream(fileName, FileMode.Create);
                workbook.Write(fileOut);
                fileOut.Flush();
                fileOut.Close();
                workbook.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
        
        Dictionary<string,string> VarSymbolicDic = new  Dictionary<string, string>(); 

    }
}