private static Excel.Range _range; // current range of curent excel
#endregion Fields
#region Methods
public static WorkSchedule GetWorkHours(string filePath, string workerName)
{
var xlApp = new Excel.Application();
var xlWorkBook = xlApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Item[1];
_range = xlWorkSheet.UsedRange;
var name = workerName.Trim();
var counter = 0;
var workSchedule = new WorkSchedule
{
Name = name,
//StartDateTime = Convert.ToDateTime(GetCellValue(4, 2)),
//EndDateTime = Convert.ToDateTime(GetCellValue(4, 14))
StartDateTime = DateTime.ParseExact(GetCellValue(4, 2), "d.M.yyyy", CultureInfo.InvariantCulture),
EndDateTime = DateTime.ParseExact(GetCellValue(22, 2), "d.M.yyyy", CultureInfo.InvariantCulture)
};
for (var i = 1; i <= _range.Rows.Count; i++)
{
for (var j = 1; j <= _range.Columns.Count; j++)
{
var cell = GetCellValue(i, j);
#region Old Discount
//if (cell == name)
//{
// counter++;
// string[] start = null, end = null;
// var cellTimes = GetCellValue(i - 1, j);
// if (!string.IsNullOrEmpty(cellTimes) && cellTimes.Contains(":"))
// {
// start = cellTimes.Split(':');
// }
// cellTimes = GetCellValue(i - 1, j + 1);
// if (!string.IsNullOrEmpty(cellTimes) && cellTimes.Contains(":"))
// {
// end = cellTimes.Split(':');
// }
// var date = Convert.ToDateTime(GetCellValue(4, j));
// if (start != null && end != null)
// {
// var reminder = new Reminder
// {
// DayDesc = GetCellValue(3, j),
// Start = date.AddHours(int.Parse(start[0])).AddMinutes(int.Parse(start[1])),
// End = date.AddHours(int.Parse(end[0])).AddMinutes(int.Parse(end[1])),
// JobName = GetCellValue(i - 1, 1),
// };
// // check if end time in next day
// if ((reminder.End - reminder.Start).TotalHours < 0)
// {
// reminder.End = reminder.End.AddDays(1);
// }
// workSchedule.Reminders.Add(reminder);
// }
//}
#endregion
#region CheckPoint
if (cell != null && cell.Contains(name))
{
counter++;
int[] start = null, end = null;
var cellTimes = GetCellValue(i, 3);
if (!string.IsNullOrEmpty(cellTimes))
{
cellTimes = cellTimes.Trim();
if (cellTimes.Equals("בוקר"))
{
start = new [] {07 ,00};
end = new [] {15 ,00};
}
if(cellTimes.Equals("צהריים"))
{
start = new[] { 15, 00 };
end = new[] { 23, 00 };
}
if(cellTimes.Equals("לילה"))
{
start = new[] {23, 00 };
end = new[] { 07, 00 };
}
}
var date = TryGetDate(i, 2);
if (start.IsAny() && end.IsAny())
{
var reminder = new Reminder
{
DayDesc = TryGetDayDesc(i, 2),
Start = date.AddHours(start[0]).AddMinutes(start[1]),
End = date.AddHours(end[0]).AddMinutes(end[1]),
JobName = GetCellValue(2, j)
};
// check if end time in next day
if ((reminder.End - reminder.Start).TotalHours < 0)
{
reminder.End = reminder.End.AddDays(1);
}
workSchedule.Reminders.Add(reminder);
}
}
#endregion
}
}
workSchedule.Reminders = workSchedule.Reminders.OrderBy(r => r.Start).ToList();
//MessageBox.Show(text);
xlWorkBook.Close(true);
xlApp.Quit();
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);
if (counter > 0)
{
var json = JsonConvert.SerializeObject(workSchedule, Formatting.Indented);
var fileName =
$@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\{
workSchedule.StartDateTime.ToString("dd.MM.yy")}-{workSchedule.EndDateTime.ToString("dd.MM.yy")
}_{name}.txt";
if (Utils.IsMishaPc())
{
File.WriteAllText(fileName, json);
}
return workSchedule;
}
else
{
return null;
}
}
private void btnScan_Click(object sender, EventArgs e)
{
if (FormIsValid())
{
if (Utils.IsMishaPc())
{
var filePath = @"C:\Users\Misha\Desktop\04.12.16-10.12.16_קוסטיה.txt";
if (File.Exists(filePath))
{
var json = File.ReadAllText(filePath);
_workSchedule = JsonConvert.DeserializeObject<WorkSchedule>(json);
PrintSchedule();
PrintSummary();
btnAddEvents.Enabled = true;
return;
}
}
btnScan.Enabled = false;
_workSchedule = ExcelApi.GetWorkHours(tbUploadFile.Text, tbWorkerName.Text);
if (_workSchedule.IsNotEmptyObject())
{
MessageBox.Show(@"Success to analize Work Schedule.\nPlease check it well.");
PrintSchedule();
PrintSummary();
btnAddEvents.Enabled = true;
btnScan.Enabled = true;
}
else
{
MessageBox.Show(@"Cann't find worker: " + tbWorkerName.Text);
}
}
}