private void SalvaFileApri()
{
var newfile = Path.Combine(System.IO.Path.GetTempPath(), DateTime.Now.Ticks.ToString() + "_Fatt.xlsx");
_excel.SaveAs(newfile);
if (_excel.Worksheets.Count() > 1)
{
var totRowPage = _excel.Worksheet(1).PageSetup.PrintAreas.First().RowCount();
for (int i = 2; i <= _excel.Worksheets.Count(); i++)
{
var range = _excel.Worksheet(i).Range("A1", "I" + totRowPage.ToString());
var sheetBase = _excel.Worksheet(1);
range.CopyTo(_excel.Worksheet(1).Cell(totRowPage * (i - 1) + 1, 1));
}
_excel.Worksheet(1).PageSetup.PrintAreas.Clear();
_excel.Worksheet(1).PageSetup.PrintAreas.Add(1, 1, totRowPage * _excel.Worksheets.Count(), 9);
for (int i = 2; i <= _excel.Worksheets.Count(); i++)
{
_excel.Worksheet(1).PageSetup.AddHorizontalPageBreak(totRowPage * (i - 1));
}
_excel.Save();
}
Process.Start(newfile);
}
private void SavePokemonData_Click(object sender, EventArgs e)
{
TempPokemon.Character = SeikakuList.Text;
TempPokemon.Ability = Tokusei_Set.Text;
TempPokemon.Item = Items.usrItem;
TempPokemon.Weapon = Weapon_Set.Text.Split('\n');
var book = new ClosedXML.Excel.XLWorkbook(Parameter_BD.PreSet.DBLocation + "SaveData.xlsx");
int next = 1;
while (book.Worksheet(1).Cell(next, 1).Value.ToString() != "")
{
next++;
}
if (SaveSequence(book.Worksheet(1), next))
{
book.Save();
//Init
isDialogue(false);
isDialogue(true);
InputName.Text = "";
}
}
static public void MakeReport(DateTime dt1, DateTime dt2)
{
string fileName = Path.GetFileNameWithoutExtension(Path.GetTempFileName()) + ".xlsx";
FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite, FileShare.None);
fs.Write(Tasks.Properties.Resources.blank_Report, 0, Tasks.Properties.Resources.blank_Report.Length);
fs.Close();
ClosedXML.Excel.XLWorkbook xL = new ClosedXML.Excel.XLWorkbook(fileName);
string[,] list = Network.Task_List_Report(dt1.Ticks, dt2.Ticks);
string[] directions = Network.User_Directions();
if (list.Length == 0 || directions.Length == 0)
{
System.Windows.Forms.MessageBox.Show("Нет задач для отображения в отчете за указанный период", "Формирование отчета", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return;
}
for (int d = 2; d < directions.Length + 2; d++)
{
int line = 7;
int count = 1, tmpcount = 0;
xL.Worksheet(1).CopyTo("Направление " + directions[d - 2]);
xL.Worksheet(d).Cell(1, 1).Value = "Отчет по рабочему плану за " + Tools.DateTimeToString(dt1, "MMMM YYYY").ToUpper() + " года";
xL.Worksheet(d).Cell(2, 1).Value = Config.user_Fio + " (" + (directions[d - 2] == "" ? "Основное направление" : "Направление: " + directions[d - 2]) + ")";
xL.Worksheet(d).Cell(3, 1).Value = Config.user_Post;
// Основные задачи
for (int i = 0; i < list.Length / 12; i++)
{
long taskA = Convert.ToInt64(list[i, 3]);
if (taskA >= dt1.Ticks || list[i, 10] != directions[d - 2] || list[i, 11] != "0")
{
continue;
}
if (count != 1)
{
xL.Worksheet(d).Row(line - 1).InsertRowsBelow(1);
for (int j = 1; j <= 6; j++)
{
xL.Worksheet(d).Cell(line, j).Style = xL.Worksheet(d).Cell(7, j).Style;
}
}
xL.Worksheet(d).Cell(line, 1).Value = count.ToString();
xL.Worksheet(d).Cell(line, 2).Value = list[i, 0].Replace("%newline%", "\r\n");
xL.Worksheet(d).Cell(line, 3).Value = "с " + Tools.DateToString(Convert.ToInt64(list[i, 1]), "D MMMMm") + "\r\nпо " + Tools.DateToString(Convert.ToInt64(list[i, 2]), "D MMMMm");
xL.Worksheet(d).Cell(line, 5).Value = list[i, 5];
string progress = "", progressCom = "";
long taskE = Convert.ToInt64(list[i, 2]), taskF = Convert.ToInt64(list[i, 4]);
if (taskF != 0)
{
progress = "Выполнено" + "\r\n" + Tools.DateToString(taskF, "D MMMMm");
}
else if (list[i, 6] != "0")
{
progress = "Завершено на " + list[i, 6] + "%";
progressCom = "Выполнено:\r\n" + list[i, 7].Replace("%ns%", "\r\n") + "\r\n\r\nВ работе:\r\n" + list[i, 8].Replace("%ns%", "\r\n");
}
else if (taskE > DateTime.Now.Ticks)
{
progress = "В работе";
}
else
{
progress = "Не выполнено";
}
xL.Worksheet(d).Cell(line, 4).Value = progress;
if (progressCom != "")
{
xL.Worksheet(d).Cell(line, 4).Comment.AddText(progressCom);
xL.Worksheet(d).Cell(line, 4).Comment.Style.Alignment.SetAutomaticSize();
}
if (list[i, 9] != "")
{
int k = list[i, 9].LastIndexOf("%nm%");
if (k == -1)
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9];
}
else
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9].Substring(k + 4);
xL.Worksheet(d).Cell(line, 6).Comment.AddText(list[i, 9].Replace("%nm%", "\r\n\r\n"));
xL.Worksheet(d).Cell(line, 6).Comment.Style.Alignment.SetAutomaticSize();
}
}
count++;
line++;
}
if (count != 1)
{
xL.Worksheet(d).Row(line).InsertRowsBelow(2);
xL.Worksheet(d).Row(5).CopyTo(xL.Worksheet(d).Row(line)); line++;
xL.Worksheet(d).Row(7).CopyTo(xL.Worksheet(d).Row(line));
for (int i = 0; i < 6; i++)
{
xL.Worksheet(d).Cell(line, i + 1).Value = "";
}
xL.Worksheet(d).Cell(line - 1, 1).Value = "Дополнительные";
}
else
{
xL.Worksheet(d).Cell(line - 1, 1).Value = "Дополнительные";
}
tmpcount = count;
// Дополнительные задачи
for (int i = 0; i < list.Length / 12; i++)
{
long taskA = Convert.ToInt64(list[i, 3]);
if (taskA < dt1.Ticks || list[i, 10] != directions[d - 2] || list[i, 11] != "0")
{
continue;
}
if (count != tmpcount)
{
xL.Worksheet(d).Row(line).InsertRowsBelow(1);
for (int j = 1; j <= 6; j++)
{
xL.Worksheet(d).Cell(line, j).Style = xL.Worksheet(d).Cell(7, j).Style;
}
}
xL.Worksheet(d).Cell(line, 1).Value = count.ToString();
xL.Worksheet(d).Cell(line, 2).Value = list[i, 0].Replace("%newline%", "\r\n");
xL.Worksheet(d).Cell(line, 3).Value = "с " + Tools.DateToString(Convert.ToInt64(list[i, 1]), "D MMMMm") + "\r\nпо " + Tools.DateToString(Convert.ToInt64(list[i, 2]), "D MMMMm");
xL.Worksheet(d).Cell(line, 5).Value = list[i, 5];
string progress = "", progressCom = "";
long taskE = Convert.ToInt64(list[i, 2]), taskF = Convert.ToInt64(list[i, 4]);
if (taskF != 0)
{
progress = "Выполнено" + "\r\n" + Tools.DateToString(taskF, "D MMMMm");
}
else if (list[i, 6] != "0")
{
progress = "Завершено на " + list[i, 6] + "%";
progressCom = "Выполнено:\r\n" + list[i, 7].Replace("%ns%", "\r\n") + "\r\n\r\nВ работе:\r\n" + list[i, 8].Replace("%ns%", "\r\n");
}
else
{
progress = "Не выполнено";
}
xL.Worksheet(d).Cell(line, 4).Value = progress;
if (progressCom != "")
{
xL.Worksheet(d).Cell(line, 4).Comment.AddText(progressCom);
xL.Worksheet(d).Cell(line, 4).Comment.Style.Alignment.SetAutomaticSize();
}
if (list[i, 9] != "")
{
int k = list[i, 9].LastIndexOf("%nm%");
if (k == -1)
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9];
}
else
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9].Substring(k + 4);
xL.Worksheet(d).Cell(line, 6).Comment.AddText(list[i, 9].Replace("%nm%", "\r\n\r\n"));
xL.Worksheet(d).Cell(line, 6).Comment.Style.Alignment.SetAutomaticSize();
}
}
line++;
count++;
}
if (count != tmpcount)
{
xL.Worksheet(d).Row(line).InsertRowsBelow(2);
xL.Worksheet(d).Row(5).CopyTo(xL.Worksheet(d).Row(line)); line++;
xL.Worksheet(d).Row(7).CopyTo(xL.Worksheet(d).Row(line));
for (int i = 0; i < 6; i++)
{
xL.Worksheet(d).Cell(line, i + 1).Value = "";
}
xL.Worksheet(d).Cell(line - 1, 1).Value = "Контроль";
}
else if (count == 1)
{
xL.Worksheet(d).Cell(line - 2, 1).Value = "Контроль";
}
else
{
xL.Worksheet(d).Cell(line - 1, 1).Value = "Контроль";
}
tmpcount = count;
// Контроль
for (int i = 0; i < list.Length / 12; i++)
{
if (list[i, 10] != directions[d - 2] || list[i, 11] != "1")
{
continue;
}
if (count != tmpcount)
{
xL.Worksheet(d).Row(line).InsertRowsBelow(1);
for (int j = 1; j <= 6; j++)
{
xL.Worksheet(d).Cell(line, j).Style = xL.Worksheet(d).Cell(7, j).Style;
}
}
xL.Worksheet(d).Cell(line, 1).Value = count.ToString();
xL.Worksheet(d).Cell(line, 2).Value = list[i, 0].Replace("%newline%", "\r\n");
xL.Worksheet(d).Cell(line, 3).Value = "с " + Tools.DateToString(Convert.ToInt64(list[i, 1]), "D MMMMm") + "\r\nпо " + Tools.DateToString(Convert.ToInt64(list[i, 2]), "D MMMMm");
xL.Worksheet(d).Cell(line, 5).Value = list[i, 5];
string progress = "", progressCom = "";
long taskE = Convert.ToInt64(list[i, 2]), taskF = Convert.ToInt64(list[i, 4]);
if (taskF != 0)
{
progress = "Выполнено" + "\r\n" + Tools.DateToString(taskF, "D MMMMm");
}
else if (list[i, 6] != "0")
{
progress = "Завершено на " + list[i, 6] + "%";
progressCom = "Выполнено:\r\n" + list[i, 7].Replace("%ns%", "\r\n") + "\r\n\r\nВ работе:\r\n" + list[i, 8].Replace("%ns%", "\r\n");
}
else
{
progress = "Не выполнено";
}
xL.Worksheet(d).Cell(line, 4).Value = progress;
if (progressCom != "")
{
xL.Worksheet(d).Cell(line, 4).Comment.AddText(progressCom);
xL.Worksheet(d).Cell(line, 4).Comment.Style.Alignment.SetAutomaticSize();
}
if (list[i, 9] != "")
{
int k = list[i, 9].LastIndexOf("%nm%");
if (k == -1)
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9];
}
else
{
xL.Worksheet(d).Cell(line, 6).Value = list[i, 9].Substring(k + 4);
xL.Worksheet(d).Cell(line, 6).Comment.AddText(list[i, 9].Replace("%nm%", "\r\n\r\n"));
xL.Worksheet(d).Cell(line, 6).Comment.Style.Alignment.SetAutomaticSize();
}
}
line++;
count++;
}
if (count == tmpcount)
{
xL.Worksheet(d).Row(line - 1).Delete();
xL.Worksheet(d).Row(line - 1).Delete();
line -= 2;
}
line += 2;
xL.Worksheet(d).Cell(line, 1).Value = Tools.FioToShort(Config.user_Fio) + " ____________________ ";
}
xL.Worksheet(1).Delete();
xL.Worksheet(1).Name = "Основное направление";
xL.Save();
System.Diagnostics.Process p = new System.Diagnostics.Process();
p.StartInfo.FileName = fileName;
p.StartInfo.UseShellExecute = true;
p.Start();
System.Threading.Thread.Sleep(1000);
}
static public void MakePlan(DateTime dt1, DateTime dt2)
{
//DateTime dt0 = new DateTime(dt1.Year, dt1.Month, dt1.Day);
//dt0 = dt0.AddTicks(-1);
string fileName = Path.GetFileNameWithoutExtension(Path.GetTempFileName()) + ".xlsx";
FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite, FileShare.None);
fs.Write(Tasks.Properties.Resources.blank_Plan, 0, Tasks.Properties.Resources.blank_Plan.Length);
fs.Close();
ClosedXML.Excel.XLWorkbook xL = new ClosedXML.Excel.XLWorkbook(fileName);
string[,] list = Network.Task_List_Plan(dt1.Ticks, dt2.Ticks);
string[] directions = Network.User_Directions();
if (list.Length == 0 || directions.Length == 0)
{
System.Windows.Forms.MessageBox.Show("Нет задач для отображения в плане за указанный период", "Формирование плана", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
return;
}
xL.Worksheet(1).Cell(1, 1).Value = "Рабочий план на " + Tools.DateTimeToString(dt1, "MMMM YYYY").ToUpper() + " года";
xL.Worksheet(1).Cell(2, 1).Value = Config.user_Fio;
xL.Worksheet(1).Cell(3, 1).Value = Config.user_Post;
for (int d = 2; d < directions.Length + 2; d++)
{
int line = 7;
int count = 1, tmpcount = 0;
xL.Worksheet(1).CopyTo("Направление " + directions[d - 2]);
xL.Worksheet(d).Cell(1, 1).Value = "Рабочий план на " + Tools.DateTimeToString(dt1, "MMMM YYYY").ToUpper() + " года";
xL.Worksheet(d).Cell(2, 1).Value = Config.user_Fio + " (" + (directions[d - 2] == "" ? "Основное направление" : "Направление: " + directions[d - 2]) + ")";
xL.Worksheet(d).Cell(3, 1).Value = Config.user_Post;
for (int i = 0; i < list.Length / 6; i++)
{
if (list[i, 4] != directions[d - 2] || list[i, 5] != "0")
{
continue;
}
if (count != 1)
{
xL.Worksheet(d).Row(line - 1).InsertRowsBelow(1);
for (int j = 1; j <= 4; j++)
{
xL.Worksheet(d).Cell(line, j).Style = xL.Worksheet(d).Cell(7, j).Style;
}
}
xL.Worksheet(d).Cell(line, 1).Value = count.ToString();
xL.Worksheet(d).Cell(line, 2).Value = list[i, 0].Replace("%newline%", "\r\n");
xL.Worksheet(d).Cell(line, 3).Value = "с " + Tools.DateToString(Convert.ToInt64(list[i, 1]), "D MMMMm") + "\r\nпо " + Tools.DateToString(Convert.ToInt64(list[i, 2]), "D MMMMm");
xL.Worksheet(d).Cell(line, 4).Value = list[i, 3];
count++;
line++;
}
if (count != 1)
{
xL.Worksheet(d).Row(line).InsertRowsBelow(2);
xL.Worksheet(d).Row(5).CopyTo(xL.Worksheet(d).Row(line)); line++;
xL.Worksheet(d).Row(7).CopyTo(xL.Worksheet(d).Row(line));
for (int i = 0; i < 4; i++)
{
xL.Worksheet(d).Cell(line, i + 1).Value = "";
}
xL.Worksheet(d).Cell(line - 1, 1).Value = "Контроль";
}
else
{
xL.Worksheet(d).Cell(line - 2, 1).Value = "Контроль";
}
tmpcount = count;
for (int i = 0; i < list.Length / 6; i++)
{
if (list[i, 4] != directions[d - 2] || list[i, 5] != "1")
{
continue;
}
if (tmpcount != count)
{
xL.Worksheet(d).Row(line - 1).InsertRowsBelow(1);
for (int j = 1; j <= 4; j++)
{
xL.Worksheet(d).Cell(line, j).Style = xL.Worksheet(d).Cell(7, j).Style;
}
}
xL.Worksheet(d).Cell(line, 1).Value = count.ToString();
xL.Worksheet(d).Cell(line, 2).Value = list[i, 0].Replace("%newline%", "\r\n");
xL.Worksheet(d).Cell(line, 3).Value = "с " + Tools.DateToString(Convert.ToInt64(list[i, 1]), "D MMMMm") + "\r\nпо " + Tools.DateToString(Convert.ToInt64(list[i, 2]), "D MMMMm");
xL.Worksheet(d).Cell(line, 4).Value = list[i, 3];
line++;
count++;
}
if (count == tmpcount)
{
xL.Worksheet(d).Row(line - 1).Delete();
xL.Worksheet(d).Row(line - 1).Delete();
line -= 2;
}
line += 2;
xL.Worksheet(d).Cell(line, 1).Value = Tools.FioToShort(Config.user_Fio) + " ____________________ ";
}
xL.Worksheet(1).Delete();
xL.Worksheet(1).Name = "Основное направление";
xL.Save();
System.Diagnostics.Process p = new System.Diagnostics.Process();
p.StartInfo.FileName = fileName;
p.StartInfo.UseShellExecute = true;
p.Start();
System.Threading.Thread.Sleep(1000);
}
public void Elabora()
{
var dataOutput = new List <RowExcel>();
using (var excelRaccoltaDati = new ClosedXML.Excel.XLWorkbook(_impostazioni.RaccoltaDati.File))
{
using (var excelDiario = new ClosedXML.Excel.XLWorkbook(_impostazioni.DiarioCantiere.File))
{
var rowDiarioLast = excelDiario.Worksheet(1).Range("B:B").LastCellUsed().Address.RowNumber;
var list = new List <OreCantiere>();
ClosedXML.Excel.IXLWorksheet meseSheet = excelRaccoltaDati.Worksheet(2);
var listDate = DatePeriodo();
for (int i = 6; i <= rowDiarioLast; i++)
{
var dataDiario = DateTime.Parse(excelDiario.Worksheet(1).Cell(i, 2).Value.ToString()).Date;
if (listDate.Contains(dataDiario))
{
var diarioItem =
(new OreCantiere()
{
Commessa = excelDiario.Worksheet(1).Cell(i, 3).Value.ToString(),
Data = dataDiario,
Ore = decimal.Parse(excelDiario.Worksheet(1).Cell(i, 7).Value.ToString()),
Cantiere = excelDiario.Worksheet(1).Cell(i, 5).Value.ToString() == "C",
});
list.Add(diarioItem);
}
}
var listGR = list.GroupBy(a => new { a.Commessa, a.Cantiere, a.Data }).ToList();
var listOre = listGR.Select(a => new { a.Key.Cantiere, a.Key.Commessa, a.Key.Data, SommaOre = a.Sum(b => b.Ore) }).ToList();
dataOutput = listOre.Select(a => new RowExcel()
{
Data = a.Data, Commessa = a.Commessa, NumeroOre = a.SommaOre, Cantiere = a.Cantiere
}).ToList();
/*aggiungo le date mancanti del periodo selezionato*/
foreach (var itemData in listDate)
{
if (dataOutput.Where(a => a.Data == itemData).Count() == 0)
{
var itemRow = (new RowExcel()
{
Data = itemData
});
dataOutput.Add(itemRow);
}
}
foreach (var itemOut in dataOutput.Where(a => a.Cantiere || _impostazioni.TrasfertaEstera || !string.IsNullOrEmpty(a.Commessa)).ToList())
{
string opt = "";
if (string.IsNullOrEmpty(itemOut.Commessa) && _impostazioni.TrasfertaEstera)
{
opt = "Giorno non lavorativo cantiere Estero";
}
else if (itemOut.Cantiere)
{
if (_impostazioni.TrasfertaEstera)
{
opt = "Cantiere UE";
}
else
{
opt = "Cantiere Italia";
}
}
else if (!string.IsNullOrEmpty(itemOut.Commessa))
{
opt = "Sede System o telework";
}
if (_impostazioni.TrasfertaEstera || itemOut.Cantiere)
{
itemOut.Pasto = true;
}
itemOut.SedeLavoro = opt;
}
RipulisciFoglio(meseSheet);
ScriviOre(meseSheet, dataOutput);
excelRaccoltaDati.Save();
}
}
var psi = new System.Diagnostics.ProcessStartInfo
{
FileName = _impostazioni.RaccoltaDati.File,
UseShellExecute = true
};
System.Diagnostics.Process.Start(psi);
MessageBox.Show("Operazione conclusa con successo", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}