private void okButton_Click(object sender, RoutedEventArgs e) { var ordersCount = Int32.Parse(offerNumberTextbox.Text); FileInfo excelFile = new FileInfo(@"order.xlsx"); var productCountList = new List <int>(); var productCountStringList = productListTextBox.Text.Split(','); foreach (var pCount in productCountStringList) { productCountList.Add(Int32.Parse(pCount.Trim())); } using (ExcelPackage excel = new ExcelPackage()) { for (int z = 0; z < ordersCount; z++) { excel.Workbook.Worksheets.Add("Order " + z); List <string[]> headerRow = new List <string[]>() { new string[] { "TYPE", "DETAIL", "AMOUNT", "STATUS" } }; string headerRange = "B1:E1"; var worksheet = excel.Workbook.Worksheets["Order " + z]; worksheet.Cells[headerRange].LoadFromArrays(headerRow); worksheet.Cells[headerRange].Style.Font.Bold = true; worksheet.Cells[headerRange].Style.Font.Size = 14; worksheet.Cells[headerRange].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[headerRange].Style.Font.Color.SetColor(System.Drawing.Color.Black); string orderRange = "A2:A" + (productCountList[z] + 1); var orderStringArray = new List <string>(); for (int i = 1; i <= productCountList[z]; i++) { orderStringArray.Add("Product " + i); } for (int i = 0; i < productCountList[z]; i++) { var cellAdress = "A" + (i + 2); worksheet.Cells[cellAdress].Value = orderStringArray[i]; worksheet.Cells[cellAdress].Style.Font.Bold = true; worksheet.Cells[cellAdress].Style.Font.Size = 12; worksheet.Cells[headerRange].Style.Font.Color.SetColor(System.Drawing.Color.RoyalBlue); } worksheet.Cells[headerRange].AutoFitColumns(); List <string[]> infoheaderRow = new List <string[]>() { new string[] { "Order Number", "Status", "Client ID", "Client Name", "Client Surname" } }; headerRange = "G1:K1"; worksheet.Cells[headerRange].LoadFromArrays(infoheaderRow); worksheet.Cells[headerRange].AutoFitColumns(); worksheet.Cells[headerRange].Style.Font.Bold = true; worksheet.Cells[headerRange].Style.Font.Size = 12; worksheet.Cells[headerRange].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[headerRange].Style.Font.Color.SetColor(System.Drawing.Color.MediumVioletRed); } excel.SaveAs(excelFile); bool isExcelInstalled = Type.GetTypeFromProgID("Excel.Application") != null ? true : false; if (isExcelInstalled) { var process = System.Diagnostics.Process.Start(excelFile.ToString()); process.WaitForExit(); ExcelManager.ExcelToXML(@"order.xlsx"); } this.Close(); } }
public static void DivideOrders(String xmlPath) { int numberOfDividedFiles = 0; Employee[] employees = new Employee[3]; employees[0] = new Employee(); employees[0].name = "employee2"; employees[0].products = new string[] { "1" }; employees[1] = new Employee(); employees[1].name = "employee3"; employees[1].products = new string[] { "2" }; employees[2] = new Employee(); employees[2].name = "employee4"; employees[2].products = new string[] { "3" }; List <XmlNode> products = new List <XmlNode>(); XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load(xmlPath); XmlReader reader = new XmlNodeReader(xmlDoc); DataSet newDs = new DataSet(); newDs.ReadXml(reader); DataTable dt = newDs.Tables[0]; DataRelationCollection children = dt.ChildRelations; for (int i = 0; i < xmlDoc.DocumentElement.ChildNodes[0].ChildNodes.Count; i++) { String x = xmlDoc.DocumentElement.ChildNodes[0].ChildNodes[i].Name; if (xmlDoc.DocumentElement.ChildNodes[0].ChildNodes[i].Name == "product") { products.Add(xmlDoc.DocumentElement.ChildNodes[0].ChildNodes[i]); } } for (int i = 0; i < employees.Length; i++) { bool ifAnyProductMatchEmployee = false; XmlDocument xmlDocdest = new XmlDocument(); XmlNode rootNode = xmlDocdest.CreateElement("orders"); xmlDocdest.AppendChild(rootNode); XmlNode orderNode = xmlDocdest.CreateElement("order"); rootNode.AppendChild(orderNode); AddOrderInformation(xmlDocdest, orderNode, xmlDoc.DocumentElement.ChildNodes[0].ChildNodes[0].InnerText, xmlDoc.DocumentElement.ChildNodes[0].ChildNodes[1].InnerText, xmlDoc.DocumentElement.ChildNodes[0].ChildNodes[2].ChildNodes[0].InnerText, xmlDoc.DocumentElement.ChildNodes[0].ChildNodes[2].ChildNodes[1].InnerText, xmlDoc.DocumentElement.ChildNodes[0].ChildNodes[2].ChildNodes[2].InnerText); foreach (XmlNode product in products) { if (employees[i].products.Contains(product.ChildNodes[0].InnerText)) { AddProduct(xmlDocdest, orderNode, product); ifAnyProductMatchEmployee = true; } } if (ifAnyProductMatchEmployee) { int employeeNumber = i + 2; String pathToXml = "..\\..\\xmls_result\\divided_xml_employee_" + employeeNumber + ".xml"; String pathToExcel = "..\\..\\excel_result\\divided_excel_employee_" + employeeNumber + ".xlsx"; xmlDocdest.Save(pathToXml); ExcelManager.XmlToExcel(pathToXml, pathToExcel); Mail mail = new Mail(); mail.send(employeeNumber, pathToExcel, 1); numberOfDividedFiles++; } } }