private void WriteToExcel() { UpdateTextBox("Выгрузка данных в Excel", false, true); Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { UpdateTextBox("Не удалось открыть приложение Excel", true); return; } xlApp.Visible = true; Excel.Workbook xlWb = xlApp.Workbooks.Open(Environment.CurrentDirectory + "\\Шаблон итоговой таблицы.xlsx", ReadOnly: true); if (xlWb == null) { UpdateTextBox("Не удалось открыть файл с шаблоном итоговой таблицы", true); return; } Excel.Worksheet xlWs = xlWb.Sheets["Лист1"]; if (xlWs == null) { UpdateTextBox("Не удалось найти лист1 в шаблоне итоговой таблицы", true); return; } try { List <string> keys = employees.Keys.ToList(); keys.Sort(); int row = 6; string title = xlWs.Range["A1"].Value; title = title.Replace("*month*", dateTimePeriod.ToString("MMMMM")).Replace("*year*", dateTimePeriod.ToString("yyyy")); xlWs.Range["A1"].Value = title; string subtitle = xlWs.Range["A2"].Value; subtitle = subtitle.Replace("*month*", dateTimePeriod.ToString("MMMMM")).Replace("*year*", dateTimePeriod.ToString("yyyy")); xlWs.Range["A2"].Value = subtitle; foreach (string key in keys) { Employee employee = employees[key]; xlWs.Range["A" + row].Value = row - 5; xlWs.Range["B" + row].Value = employee.FullName; xlWs.Range["C" + row].Value = employee.Position; xlWs.Range["D" + row].Value = employee.Rate; xlWs.Range["E" + row].Value = employee.Pfr; xlWs.Range["F" + row].Value = employee.Salary; xlWs.Range["G" + row].Value = employee.GetSpentTime(); xlWs.Range["H" + row].Value = employee.GetQualityResult(); xlWs.Range["I" + row].Value = employee.GetQualityCoefficient(); if (!employee.HasNightHours) { xlWs.Range["J" + row].Value = employee.GetWorktimeCongestion(); xlWs.Range["K" + row].Value = employee.GetWorktimeCoefficient(); } else { xlWs.Range["L" + row].Value = employee.GetAcceptedAndMissedResult(); xlWs.Range["M" + row].Value = employee.GetAcceptedAndMissedCoefficient(); } xlWs.Range["N" + row].Formula = "=I$+K$+M$".Replace("$", row.ToString()); xlWs.Range["O" + row].Formula = "=F$*N$*G$".Replace("$", row.ToString()); row++; } string savePath = Environment.CurrentDirectory + "\\Результаты\\Итоговая таблица " + DateTime.Now.ToString("yyyyMMdd HHmmss") + ".xlsx"; xlWb.SaveAs(savePath); UpdateTextBox("Результат сохранен в файл: " + savePath); } catch (Exception e) { UpdateTextBox(e.Message, true); return; } UpdateTextBox("Выгружено успешно"); }
private string GetEmployeeKeyByName(string name, bool isShortName) { name = Employee.TrimWhitespacesFromString(name); if (employees.ContainsKey(name)) { return(name); } string loweredFamily = name.ToLower(); if (loweredFamily.Contains(" ")) { loweredFamily = loweredFamily.Split(' ')[0]; } bool isKeyUnique = true; string employeeKey = ""; foreach (string key in employees.Keys) { if (!key.ToLower().Contains(loweredFamily)) { continue; } if (string.IsNullOrEmpty(employeeKey)) { employeeKey = key; } else { isKeyUnique = false; } } if (!isShortName && (string.IsNullOrEmpty(employeeKey) || !isKeyUnique)) { isKeyUnique = true; employeeKey = ""; foreach (string key in employees.Keys) { name = name.ToLower(); if (!employees[key].FullName.ToLower().Equals(name)) { continue; } if (string.IsNullOrEmpty(employeeKey)) { employeeKey = key; } else { isKeyUnique = false; } } } return(isKeyUnique ? employeeKey : ""); }