1 | using System;
|
2 | using System.Windows;
|
3 | using System.Windows.Controls;
|
4 | using System.Windows.Data;
|
5 | using System.Xml;
|
6 | using Excel = Microsoft.Office.Interop.Excel;
|
7 | using Microsoft.Win32;
|
8 | using System.IO;
|
9 | using System.Text;
|
10 | using System.Runtime.InteropServices;
|
11 | using System.Collections.Generic;
|
12 | using System.Windows.Documents;
|
13 | using System.Reflection;
|
14 | using static System.Net.Mime.MediaTypeNames;
|
15 | using System.Data.Common;
|
16 | using Microsoft.Office.Interop.Excel;
|
17 | using System.Threading;
|
18 | using System.ComponentModel;
|
19 |
|
20 | namespace LDorado_Reader
|
21 | {
|
22 | public partial class MainWindow : System.Windows.Window
|
23 | {
|
24 | List<List<string>> datenWire = new List<List<string>>();
|
25 | List<List<string>> datenBom = new List<List<string>>();
|
26 |
|
27 | public MainWindow()
|
28 | {
|
29 | InitializeComponent();
|
30 | }
|
31 |
|
32 | private void BtnOpen_Click(object sender, RoutedEventArgs e)
|
33 | {
|
34 | var fileContent = string.Empty;
|
35 | var filePath = string.Empty;
|
36 |
|
37 | OpenFileDialog openFileDialog = new OpenFileDialog();
|
38 |
|
39 | openFileDialog.InitialDirectory = "c:\\";
|
40 | openFileDialog.Filter = "XML files (*.xml)|*.xml";
|
41 | openFileDialog.FilterIndex = 2;
|
42 | openFileDialog.RestoreDirectory = true;
|
43 |
|
44 | Nullable<bool> result = openFileDialog.ShowDialog();
|
45 |
|
46 | if (result == true)
|
47 | {
|
48 | filePath = openFileDialog.FileName;
|
49 | txtPath.Text = filePath;
|
50 | }
|
51 |
|
52 | string Path = txtPath.Text;
|
53 | if (File.Exists(Path))
|
54 | {
|
55 | XmlDocument xdoc = new XmlDocument();
|
56 | xdoc.PreserveWhitespace = true;
|
57 |
|
58 | // XML Dokument laden
|
59 | xdoc.Load(Path);
|
60 | // ComplexTable Tag suchen
|
61 | XmlNodeList cTables = xdoc.GetElementsByTagName("ComplexTable");
|
62 |
|
63 | try
|
64 | {
|
65 | // alle cTables durchlaufen
|
66 | foreach (XmlNode table in cTables)
|
67 | {
|
68 | // tableType in Variable speichern
|
69 | string tableType = table.Attributes["TableType"].Value;
|
70 | // Liste von allen SubTables in der cTable in Variable schreiben
|
71 | XmlNodeList subTables = table.SelectNodes("SubTable");
|
72 |
|
73 | if (tableType == "Wirelist")
|
74 | {
|
75 | // Methode öffnen
|
76 | DataRead(subTables, datenWire);
|
77 | }
|
78 | else if (tableType == "BOM")
|
79 | {
|
80 | // Methode öffnen
|
81 | DataRead(subTables, datenBom);
|
82 | }
|
83 | }
|
84 |
|
85 | // bei erfolgreichen Einlesen, Button aktivieren
|
86 | btnWire.IsEnabled = true;
|
87 | //btnBom.IsEnabled = true;
|
88 | //btnWire_Bom.IsEnabled = true;
|
89 | //btnAEM.IsEnabled = true;
|
90 | }
|
91 | catch (System.IO.FileNotFoundException)
|
92 | {
|
93 | Console.WriteLine("Datei konnte nicht gefunden werden!");
|
94 | }
|
95 | }
|
96 | }
|
97 |
|
98 | private void BtnWire_Click(object sender, RoutedEventArgs e)
|
99 | {
|
100 | int rw = 1; //Row
|
101 | int cl = 2; // Column
|
102 |
|
103 | if (txtPath.Text != "" && txtPath.Text != "XML Dateipfad...")
|
104 | {
|
105 | string filePath = txtPath.Text;
|
106 |
|
107 | string DateiName = Path.GetFileNameWithoutExtension(filePath);
|
108 | string ExcelName = DateiName + "_Wirelist";
|
109 |
|
110 | // Excel Dokument erstellen
|
111 | Excel.Application xlApp = new Excel.Application();
|
112 | Excel.Workbook xlWorkBook;
|
113 | Excel.Worksheet xlWorkSheet;
|
114 |
|
115 | object misValue = System.Reflection.Missing.Value;
|
116 | xlApp.Visible = false;
|
117 | // no update
|
118 | xlApp.ScreenUpdating = false;
|
119 |
|
120 | xlWorkBook = xlApp.Workbooks.Add(misValue);
|
121 | // in Sheet 1 von Excel schreiben
|
122 | xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
|
123 |
|
124 | xlWorkSheet.Name = "Wirelist";
|
125 |
|
126 | foreach (var list in datenWire)
|
127 | {
|
128 | list.ForEach(x => { xlWorkSheet.Cells[rw, cl] = x; cl += 1; });
|
129 |
|
130 | string text = xlWorkSheet.Cells[rw, 2].Value == null ? "-" : xlWorkSheet.Cells[rw, 2].Value.ToString();
|
131 | if (text.Length <= 3)
|
132 | {
|
133 | if (text.Substring(0, 1) == "V")
|
134 | {
|
135 | xlWorkSheet.Cells[rw, 1] = "2";
|
136 | }
|
137 | else
|
138 | {
|
139 | xlWorkSheet.Cells[rw, 1] = "3";
|
140 | }
|
141 | }
|
142 | else
|
143 | {
|
144 | if (text.Substring(0, 4) == "LTGS" || text == "Varianten")
|
145 | {
|
146 | xlWorkSheet.Cells[rw, 1] = "1";
|
147 | }
|
148 | else if (text == "Ltg-Nr.")
|
149 | {
|
150 | xlWorkSheet.Cells[rw, 1] = "0";
|
151 | }
|
152 | else
|
153 | {
|
154 | xlWorkSheet.Cells[rw, 1] = "3";
|
155 | }
|
156 | }
|
157 |
|
158 | rw += 1;
|
159 | cl = 2;
|
160 | }
|
161 |
|
162 | // Excel Dokument speichern
|
163 | xlApp.DisplayAlerts = false;
|
164 | xlWorkBook.Worksheets["Wirelist"].Columns["A:XFD"].Autofit();
|
165 |
|
166 | string fileName = SaveFile(ExcelName, filePath);
|
167 |
|
168 | if (fileName == "cancel")
|
169 | {
|
170 | xlWorkBook.Close(false, misValue, misValue);
|
171 | xlApp.Quit();
|
172 | }
|
173 | else
|
174 | {
|
175 | xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
|
176 | misValue, misValue, misValue, misValue,
|
177 | Excel.XlSaveAsAccessMode.xlExclusive, misValue,
|
178 | misValue, misValue, misValue, misValue);
|
179 |
|
180 | xlWorkBook.Close(true, misValue, misValue);
|
181 | xlApp.Quit();
|
182 |
|
183 | MessageBox.Show("Excel Dokument wurde erstellt!");
|
184 | }
|
185 | Marshal.ReleaseComObject(xlWorkSheet);
|
186 | Marshal.ReleaseComObject(xlWorkBook);
|
187 | Marshal.ReleaseComObject(xlApp);
|
188 | }
|
189 | else
|
190 | {
|
191 | MessageBox.Show("Dateipfad ist nicht korrekt, bitte erneut eingeben.");
|
192 | }
|
193 | }
|
194 |
|
195 | private static void DataRead(XmlNodeList subTables, List<List<string>> daten)
|
196 | {
|
197 | // Subtalbe durchlaufen in ComplexTable mit richtigen TableType
|
198 | foreach (XmlNode subTable in subTables)
|
199 | {
|
200 | // Spalten durchlaufen
|
201 | XmlNodeList rows = subTable.SelectNodes("Row");
|
202 |
|
203 | foreach (XmlNode row in rows)
|
204 | {
|
205 | List<string> Column = new List<string>();
|
206 |
|
207 | // einzelne Cells durchlaufen
|
208 | XmlNodeList cells = row.SelectNodes("Cell");
|
209 | foreach (XmlNode cell in cells)
|
210 | {
|
211 | // einzeltene Celleninhalten ausgeben
|
212 | string text = cell.Attributes["Text"].Value;
|
213 | Column.Add(text);
|
214 | }
|
215 | daten.Add(Column);
|
216 | }
|
217 | }
|
218 | }
|
219 |
|
220 | private void Grid_Drop(object sender, DragEventArgs e)
|
221 | {
|
222 | if (e.Data.GetDataPresent(DataFormats.FileDrop))
|
223 | {
|
224 | string[] files = (string[])e.Data.GetData(DataFormats.FileDrop);
|
225 | // prüft ob Dateityp korrekt ist
|
226 | var fileInfo = new FileInfo(files[0]);
|
227 | if (fileInfo.Extension == ".xml")
|
228 | {
|
229 | txtPath.Text = files[0];
|
230 |
|
231 | string Path = txtPath.Text;
|
232 | if (File.Exists(Path))
|
233 | {
|
234 | XmlDocument xdoc = new XmlDocument();
|
235 | xdoc.PreserveWhitespace = true;
|
236 |
|
237 | // XML Dokument laden
|
238 | xdoc.Load(Path);
|
239 | // ComplexTable Tag suchen
|
240 | XmlNodeList cTables = xdoc.GetElementsByTagName("ComplexTable");
|
241 |
|
242 | try
|
243 | {
|
244 | // alle cTables durchlaufen
|
245 | foreach (XmlNode table in cTables)
|
246 | {
|
247 | // tableType in Variable speichern
|
248 | string tableType = table.Attributes["TableType"].Value;
|
249 | // Liste von allen SubTables in der cTable in Variable schreiben
|
250 | XmlNodeList subTables = table.SelectNodes("SubTable");
|
251 |
|
252 | if (tableType == "Wirelist")
|
253 | {
|
254 | // Methode öffnen
|
255 | DataRead(subTables, datenWire);
|
256 | }
|
257 | else if (tableType == "BOM")
|
258 | {
|
259 | // Methode öffnen
|
260 | DataRead(subTables, datenBom);
|
261 | }
|
262 | }
|
263 |
|
264 | // bei erfolgreichen Einlesen, Butten aktivieren
|
265 | btnWire.IsEnabled = true;
|
266 | btnBom.IsEnabled = true;
|
267 | btnWire_Bom.IsEnabled = true;
|
268 | btnAEM.IsEnabled = true;
|
269 | }
|
270 | catch (System.IO.FileNotFoundException)
|
271 | {
|
272 | Console.WriteLine("Datei konnte nicht gefunden werden!");
|
273 | }
|
274 | }
|
275 | }
|
276 | else
|
277 | {
|
278 | MessageBox.Show("Bitte eine .XML Datei einfügen");
|
279 | }
|
280 | }
|
281 | }
|
282 |
|
283 | static string SaveFile(string ExcelName, string FilePath)
|
284 | {
|
285 | string filename;
|
286 | SaveFileDialog dlg = new SaveFileDialog();
|
287 | // gibt als Ausgangspfad den XML Pfad aus
|
288 | dlg.InitialDirectory = FilePath;
|
289 | // Wiederherstellung letzten Dateipfad ausschalten
|
290 | dlg.RestoreDirectory = false;
|
291 | // Dateinamen festlegen
|
292 | dlg.FileName = ExcelName;
|
293 | dlg.DefaultExt = ".xlsx";
|
294 | dlg.Filter = "Excel documents (.xlsx)|*.xlsx";
|
295 |
|
296 | Nullable<bool> result = dlg.ShowDialog();
|
297 |
|
298 | if (result == true)
|
299 | {
|
300 | filename = dlg.FileName;
|
301 | return filename;
|
302 | }
|
303 | else
|
304 | {
|
305 | return filename = "cancel";
|
306 | }
|
307 | }
|
308 | }
|