private void GenerateQuartersReconciliationFactorsTemplate() { var headers = new List <string> { "F0", "F1", "F2", "F3" }; var elements = new List <string> { "Ore", "%CuT", "Cu Fines", "Ore", "%CuT", "Cu Fines", "Ore", "%CuT", "Cu Fines", "Cu Fines" }; var quarters = new List <string> { "Mill", "Q1", "Q2", "Q3", "Q4", "OL", "Q1", "Q2", "Q3", "Q4", "SL", "Q1", "Q2", "Q3", "Q4" }; var excelPackage = new ExcelPackage(); excelPackage.Workbook.Properties.Author = "BHP"; excelPackage.Workbook.Properties.Title = QuartersReconciliationFactorsConstants.QuartersReconciliationFactorsWorksheetTitle; excelPackage.Workbook.Properties.Company = "BHP"; var worksheet = excelPackage.Workbook.Worksheets.Add(QuartersReconciliationFactorsConstants.QuartersReconciliationFactorsWorksheet); worksheet.Column(2).Width = 11; int[] rows1 = { 7, 18, 29 }; int[] rows2 = { 10, 21, 32 }; var _date = TemplateDates.ConvertDateToFiscalYearString(MyMonthlyDate); for (var i = rows1.GetLowerBound(0); i <= rows1.GetUpperBound(0); i++) { worksheet.Cells[$"B{rows1[i]}:B{rows2[i]}"].Merge = true; worksheet.Cells[rows1[i], 2].Style.Font.Bold = true; worksheet.Cells[rows1[i], 2].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[rows1[i], 2].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.DarkGrayBackgroundMineCompliance)); worksheet.Cells[rows1[i], 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[rows1[i], 2].Style.VerticalAlignment = ExcelVerticalAlignment.Center; worksheet.Cells[$"B{rows1[i]}"].Value = _date; worksheet.Cells[$"B{rows1[i]}:B{rows1[i] + 3}"].Style.Border.Left.Style = ExcelBorderStyle.Thick; worksheet.Cells[$"B{rows1[i]}"].Style.Border.Top.Style = ExcelBorderStyle.Thick; } worksheet.Column(3).Width = 13; worksheet.Column(3).Style.Font.Bold = true; worksheet.Column(3).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Column(3).Style.VerticalAlignment = ExcelVerticalAlignment.Center; worksheet.Cells["B10:M10"].Style.Border.Bottom.Style = ExcelBorderStyle.Thick; worksheet.Cells["B21:L21"].Style.Border.Bottom.Style = ExcelBorderStyle.Thick; worksheet.Cells["B32:I32"].Style.Border.Bottom.Style = ExcelBorderStyle.Thick; worksheet.Cells["C3:M3"].Style.Border.Top.Style = ExcelBorderStyle.Thick; worksheet.Cells["C14:L14"].Style.Border.Top.Style = ExcelBorderStyle.Thick; worksheet.Cells["C25:I25"].Style.Border.Top.Style = ExcelBorderStyle.Thick; int[] rows3 = { 3, 14, 25 }; int[] rows4 = { 6, 17, 28 }; for (var i = rows3.GetLowerBound(0); i <= rows3.GetUpperBound(0); i++) { worksheet.Cells[$"C{rows3[i]}:C{rows4[i]}"].Merge = true; worksheet.Cells[rows3[i], 3].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[rows3[i], 3].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.DarkGrayBackgroundMineCompliance)); worksheet.Cells[$"D{7 + i}:M{7 + i}"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells[$"D{18 + i}:L{18 + i}"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells[$"D{29 + i}:I{29 + i}"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; } worksheet.Cells["C3"].Value = quarters[0]; worksheet.Cells["C14"].Value = quarters[5]; worksheet.Cells["C25"].Value = quarters[10]; string[] columns1 = { "D", "E", "F", "G", "H" }; for (var i = 0; i < 5; i++) { worksheet.Cells[6 + i, 3].Value = quarters[i]; worksheet.Cells[17 + i, 3].Value = quarters[i + 5]; worksheet.Cells[28 + i, 3].Value = quarters[i + 10]; worksheet.Cells[$"{columns1[i]}25:{columns1[i]}32"].Style.Border.Right.Style = ExcelBorderStyle.Thin; } string[] columns2 = { "D", "E", "F", "G", "H", "I", "J", "K" }; for (var i = columns2.GetLowerBound(0); i <= columns2.GetUpperBound(0); i++) { worksheet.Cells[$"{columns2[i]}14:{columns2[i]}21"].Style.Border.Right.Style = ExcelBorderStyle.Thin; } string[] columns3 = { "B", "C" }; for (var i = columns3.GetLowerBound(0); i <= columns3.GetUpperBound(0); i++) { worksheet.Cells[$"{columns3[i]}3:{columns3[i]}10"].Style.Border.Right.Style = ExcelBorderStyle.Thick; worksheet.Cells[$"{columns3[i]}14:{columns3[i]}21"].Style.Border.Right.Style = ExcelBorderStyle.Thick; worksheet.Cells[$"{columns3[i]}25:{columns3[i]}32"].Style.Border.Right.Style = ExcelBorderStyle.Thick; worksheet.Cells[$"D{25 + i}:F{25 + i}"].Merge = true; worksheet.Cells[$"G{25 + i}:I{25 + i}"].Merge = true; worksheet.Cells[5 + i, 13].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[5 + i, 13].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.LightOrangeBackgroundMineCompliance)); worksheet.Cells[$"J{5 + i}:L{5 + i}"].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[$"J{5 + i}:L{5 + i}"].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.LightGreenBackgroundMineCompliance)); worksheet.Cells[$"J{16 + i}:L{16 + i}"].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[$"J{16 + i}:L{16 + i}"].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.LightGreenBackgroundMineCompliance)); } worksheet.Cells["M3:M10"].Style.Border.Right.Style = ExcelBorderStyle.Thick; worksheet.Cells["L14:L21"].Style.Border.Right.Style = ExcelBorderStyle.Thick; worksheet.Cells["I25:I32"].Style.Border.Right.Style = ExcelBorderStyle.Thick; worksheet.Cells["C7:C10"].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells["C7:C10"].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.GrayBackgroundMineCompliance)); worksheet.Cells["C18:C21"].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells["C18:C21"].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.GrayBackgroundMineCompliance)); worksheet.Cells["C29:C32"].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells["C29:C32"].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.GrayBackgroundMineCompliance)); worksheet.Cells["C6:C9"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells["C17:C20"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells["C28:C31"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; int[] rows5 = { 3, 4, 14, 15 }; for (var i = rows5.GetLowerBound(0); i <= rows5.GetUpperBound(0); i++) { worksheet.Cells[$"D{rows5[i]}:F{rows5[i]}"].Merge = true; worksheet.Cells[$"G{rows5[i]}:I{rows5[i]}"].Merge = true; worksheet.Cells[$"J{rows5[i]}:L{rows5[i]}"].Merge = true; worksheet.Cells[$"D{3 + i}:M{3 + i}"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells[$"D{14 + i}:L{14 + i}"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells[$"D{25 + i}:I{25 + i}"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; } int[] rows6 = { 5, 6, 16, 17, 27, 28 }; for (var i = rows6.GetLowerBound(0); i <= rows6.GetUpperBound(0); i++) { worksheet.Cells[$"D{rows6[i]}:F{rows6[i]}"].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[$"D{rows6[i]}:F{rows6[i]}"].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.LightGreenBackgroundMineCompliance)); worksheet.Cells[$"G{rows6[i]}:I{rows6[i]}"].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[$"G{rows6[i]}:I{rows6[i]}"].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.LightOrangeBackgroundMineCompliance)); worksheet.Cells[27, 4 + i].Value = elements[i]; worksheet.Cells[27, 4 + i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[28, 4 + i].Value = "%"; worksheet.Cells[28, 4 + i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; } string[] cells = { "D3", "G3", "J3", "M3", "D14", "G14", "J14", "G25", "D25" }; string[] cells2 = { "D4", "G4", "J4", "M4", "D15", "G15", "J15", "G26", "D26" }; string[] columns4 = { "D", "E", "F", "G", "H", "I", "J", "K", "L" }; for (var i = columns4.GetLowerBound(0); i <= columns4.GetUpperBound(0); i++) { worksheet.Cells[$"{columns4[i]}3:{columns4[i]}10"].Style.Border.Right.Style = ExcelBorderStyle.Thin; } for (var i = cells.GetLowerBound(0); i <= cells.GetUpperBound(0); i++) { worksheet.Cells[16, 4 + i].Value = elements[i]; worksheet.Cells[17, 4 + i].Value = "%"; worksheet.Cells[16, 4 + i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[17, 4 + i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; if (i % 2 != 0) { worksheet.Cells[cells[i]].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[cells[i]].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.DarkOrangeBackgroundMineCompliance)); worksheet.Cells[cells2[i]].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[cells2[i]].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.OrangeBackgroundMineCompliance)); } else if (i % 2 == 0) { worksheet.Cells[cells[i]].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[cells[i]].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.DarkGreenBackgroundMineCompliance)); worksheet.Cells[cells2[i]].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[cells2[i]].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.GreenBackgroundMineCompliance)); } } string[] cells3 = { "D3", "G3", "J3", "M3" }; string[] cells4 = { "D4", "G4", "J4", "M4" }; for (var i = cells3.GetLowerBound(0); i <= cells3.GetUpperBound(0); i++) { worksheet.Cells[$"{cells3[i]}"].Value = headers[i]; worksheet.Cells[$"{cells3[i]}"].Style.Font.Color.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.WhiteFontMineCompliance)); worksheet.Cells[$"{cells3[i]}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[$"{cells4[i]}"].Value = "Quarter"; worksheet.Cells[$"{cells4[i]}"].Style.Font.Color.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.WhiteFontMineCompliance)); worksheet.Cells[$"{cells4[i]}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; } for (var i = 0; i < 10; i++) { worksheet.Cells[5, 4 + i].Value = elements[i]; worksheet.Cells[5, 4 + i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[6, 4 + i].Value = "%"; worksheet.Cells[6, 4 + i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Column(4 + i).Width = 11; } string[] cells5 = { "D14", "G14", "J14" }; string[] cells6 = { "D15", "G15", "J15" }; for (var i = cells5.GetLowerBound(0); i <= cells5.GetUpperBound(0); i++) { worksheet.Cells[$"{cells5[i]}"].Value = headers[i]; worksheet.Cells[$"{cells5[i]}"].Style.Font.Color.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.WhiteFontMineCompliance)); worksheet.Cells[$"{cells5[i]}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[$"{cells6[i]}"].Value = "Month/Year"; worksheet.Cells[$"{cells6[i]}"].Style.Font.Color.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.WhiteFontMineCompliance)); worksheet.Cells[$"{cells6[i]}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; } string[] cells7 = { "D25", "G25" }; string[] cells8 = { "D26", "G26" }; for (var i = cells7.GetLowerBound(0); i <= cells7.GetUpperBound(0); i++) { worksheet.Cells[$"{cells7[i]}"].Value = headers[i]; worksheet.Cells[$"{cells7[i]}"].Style.Font.Color.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.WhiteFontMineCompliance)); worksheet.Cells[$"{cells7[i]}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[$"{cells8[i]}"].Value = "Month/Year"; worksheet.Cells[$"{cells8[i]}"].Style.Font.Color.SetColor(ColorTranslator.FromHtml(QuartersReconciliationFactorsTemplateColors.WhiteFontMineCompliance)); worksheet.Cells[$"{cells8[i]}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; } byte[] fileText = excelPackage.GetAsByteArray(); var dialog = new SaveFileDialog() { FileName = QuartersReconciliationFactorsConstants.QuartersReconciliationFactorExcelFileName, Filter = "Excel Worksheets (*.xlsx)|*.xlsx" }; try { var fileStream = File.OpenWrite(dialog.FileName); fileStream.Close(); if (dialog.ShowDialog() == true) { File.WriteAllBytes(dialog.FileName, fileText); IsEnabledLoadMonthlyValues = true; } } catch (Exception ex) { MessageBox.Show(ex.Message, StringResources.UploadError); } }
private void LoadBudgetFreightTemplate() { _budgetFreights.Clear(); var openFileDialog = new OpenFileDialog { Title = StringResources.SelectFile, Filter = "Excel Worksheets (*.xlsx)|*.xlsx" }; if (openFileDialog.ShowDialog() == true) { var openFilePath = new FileInfo(openFileDialog.FileName); var excelPackage = new ExcelPackage(openFilePath); var templateWorksheet = excelPackage.Workbook.Worksheets[ConcentrateQualityConstants.BudgetFreightWorksheet]; // TODO: Llevar a un archivo de constantes if (openFilePath.FullName.Substring(openFilePath.FullName.Length - ConcentrateQualityConstants.BudgetFreightExcelFileName.Length) == ConcentrateQualityConstants.BudgetFreightExcelFileName) { try { var fileStream = File.OpenWrite(openFileDialog.FileName); fileStream.Close(); var _date = DateTime.Now; for (var i = 0; i < 12; i++) { var _month = DateTime.ParseExact(templateWorksheet.Cells[3, 4 + i].Value.ToString(), "MMMM", CultureInfo.InvariantCulture).Month; _date = TemplateDates.ConvertDateToFiscalYearDate(i, MyFiscalYear, _month); for (var j = 0; j < 17; j++) { if (templateWorksheet.Cells[4 + j, 4 + i].Value == null) { templateWorksheet.Cells[4 + j, 4 + i].Value = -99; } } _budgetFreights.Add(new ConcentrateQualityBudgetFreight() { Date = _date, Au = double.Parse(templateWorksheet.Cells[4, 4 + i].Value.ToString()), Ag = double.Parse(templateWorksheet.Cells[5, 4 + i].Value.ToString()), Mo = double.Parse(templateWorksheet.Cells[6, 4 + i].Value.ToString()) / 1000000, As = double.Parse(templateWorksheet.Cells[7, 4 + i].Value.ToString()), Cd = double.Parse(templateWorksheet.Cells[8, 4 + i].Value.ToString()), Pb = double.Parse(templateWorksheet.Cells[9, 4 + i].Value.ToString()), Zn = double.Parse(templateWorksheet.Cells[10, 4 + i].Value.ToString()), Bi = double.Parse(templateWorksheet.Cells[11, 4 + i].Value.ToString()), Sb = double.Parse(templateWorksheet.Cells[12, 4 + i].Value.ToString()), FeConc = double.Parse(templateWorksheet.Cells[13, 4 + i].Value.ToString()) / 100, Fe = double.Parse(templateWorksheet.Cells[14, 4 + i].Value.ToString()) / 100, PyConc = double.Parse(templateWorksheet.Cells[15, 4 + i].Value.ToString()) / 100, Py = double.Parse(templateWorksheet.Cells[16, 4 + i].Value.ToString()) / 100, S2 = double.Parse(templateWorksheet.Cells[17, 4 + i].Value.ToString()) / 100, ConcentrateGrade = double.Parse(templateWorksheet.Cells[18, 4 + i].Value.ToString()) / 100 }); } excelPackage.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message, StringResources.UploadError); } } else { var wrongFileMessage = $"{StringResources.WrongUploadedFile} {openFilePath.FullName} {StringResources.IsTheRightOne}"; MessageBox.Show(wrongFileMessage, StringResources.UploadError); } var loadFilePath = BhpAssetComplianceWpfOneDesktop.Resources.FilePaths.Default.ConcentrateQualityExcelFilePath; var loadFileInfo = new FileInfo(loadFilePath); if (loadFileInfo.Exists) { var package = new ExcelPackage(loadFileInfo); var worksheet = package.Workbook.Worksheets[ConcentrateQualityConstants.BudgetFreightWorksheet]; if (worksheet != null) { try { var openWriteCheck = File.OpenWrite(loadFilePath); openWriteCheck.Close(); var lastRow = worksheet.Dimension.End.Row + 1; for (var i = 0; i < _budgetFreights.Count; i++) { worksheet.Cells[i + lastRow, 1].Value = _budgetFreights[i].Date; worksheet.Cells[i + lastRow, 1].Style.Numberformat.Format = "yyyy-MM-dd"; worksheet.Cells[i + lastRow, 2].Value = _budgetFreights[i].Au; worksheet.Cells[i + lastRow, 3].Value = _budgetFreights[i].Ag; worksheet.Cells[i + lastRow, 4].Value = _budgetFreights[i].Mo; worksheet.Cells[i + lastRow, 5].Value = _budgetFreights[i].As; worksheet.Cells[i + lastRow, 6].Value = _budgetFreights[i].Cd; worksheet.Cells[i + lastRow, 7].Value = _budgetFreights[i].Pb; worksheet.Cells[i + lastRow, 8].Value = _budgetFreights[i].Zn; worksheet.Cells[i + lastRow, 9].Value = _budgetFreights[i].Bi; worksheet.Cells[i + lastRow, 10].Value = _budgetFreights[i].Sb; worksheet.Cells[i + lastRow, 11].Value = _budgetFreights[i].FeConc; worksheet.Cells[i + lastRow, 12].Value = _budgetFreights[i].Fe; worksheet.Cells[i + lastRow, 13].Value = _budgetFreights[i].PyConc; worksheet.Cells[i + lastRow, 14].Value = _budgetFreights[i].Py; worksheet.Cells[i + lastRow, 15].Value = _budgetFreights[i].S2; worksheet.Cells[i + lastRow, 16].Value = _budgetFreights[i].ConcentrateGrade; } byte[] fileText2 = package.GetAsByteArray(); File.WriteAllBytes(loadFilePath, fileText2); MyLastDateRefreshBudgetValues = $"{StringResources.Updated}: {DateTime.Now}"; } catch (Exception ex) { MessageBox.Show(ex.Message, StringResources.UploadError); } } else { var wrongFileMessage = $"{StringResources.WorksheetNotExist} {loadFilePath} {StringResources.IsTheRightOne}"; MessageBox.Show(wrongFileMessage, StringResources.UploadError); } } else { var wrongFileMessage = $"{StringResources.WorksheetNotExist} {loadFilePath} {StringResources.ExistsOrNotSelect}"; MessageBox.Show(wrongFileMessage, StringResources.UploadError); } } }