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 : "");
}