internal void LoadDTRs(string Filename)
{
if (File.Exists(Filename))
{
DTRs = new SortedList <int, EmployeeDTR>();
using (ExcelEngine E = new ExcelEngine()) {
IWorkbook wb = E.Excel.Workbooks.Open(Filename);
IWorksheet ws;
wb.Version = ExcelVersion.Excel2013;
// get the month and year from the first sheet data
DateTime dat = DateTime.Parse(wb.Worksheets[0].Range["B2"].Text.Split(new [] { " ~ " }, StringSplitOptions.RemoveEmptyEntries)[0]);
_ds.month = dat.ToString("MMMMM");
_ds.year = dat.Year.ToString();
// get per employee dtr and store to dtrs list
for (int i = 4; i <= wb.Worksheets.Count - 1; i++)
{
ws = wb.Worksheets[i];
// there are three employee dtrs per worksheet
EmployeeDTR emp1 = new EmployeeDTR();
EmployeeDTR emp2 = new EmployeeDTR();
EmployeeDTR emp3 = new EmployeeDTR();
// instantiate departments
emp1.Department = new Department();
emp2.Department = new Department();
emp3.Department = new Department();
// first dtr/employee in a worksheet if not null
if (ws.Range["J4"].Text != null && ws.Range["J4"].Text != "0")
{
// get id and employee name
emp1.Id = int.Parse(ws.Range["J4"].Text);
emp1.Name = ws.Range["J3"].Text;
// get duties per day in a month
// 12 => first row in excel sheet
// 42 => last row in excel sheet (31st day)
for (int rw = 12; rw <= 43; rw++)
{
string strrw = rw.ToString();
// get on off duties from morning to afternoon
// add new workday object and get id
if (ws.Range['A' + strrw].Text != null)
{
emp1.AddWorkDay(ws.Range['A' + strrw].Text);
}
if (ws.Range['B' + strrw].Text != "Absent")
{
if (string.IsNullOrEmpty(ws.Range['K' + strrw].Text))
{
emp1.nwdset_amtimein(ws.Range['B' + strrw].Text);
emp1.nwdset_amtimeout(ws.Range['D' + strrw].Text);
emp1.nwdset_pmtimein(ws.Range['G' + strrw].Text);
emp1.nwdset_pmtimeout(ws.Range['I' + strrw].Text);
}
else
{
emp1.nwdset_amtimein(ws.Range['K' + strrw].Text);
emp1.nwdset_pmtimeout(ws.Range['M' + strrw].Text);
}
}
}
//MessageBox.Show(ws.Range["B39"].Text);
}
// second dtr/employee in a worksheet if not null
if (ws.Range["Y4"].Text != null && ws.Range["Y4"].Text != "0")
{
emp2.Id = int.Parse(ws.Range["Y4"].Text);
emp2.Name = ws.Range["Y3"].Text;
// get duties per day in a month
for (int rw = 12; rw <= 42; rw++)
{
string strrw = rw.ToString();
// get on off duties from morning to afternoon
if (ws.Range['P' + strrw].Text != null)
{
emp2.AddWorkDay(ws.Range['P' + strrw].Text);
}
if (ws.Range['Q' + strrw].Text != "Absent")
{
if (ws.Range['Z' + strrw].Text == null)
{
emp2.nwdset_amtimein(ws.Range['Q' + strrw].Text);
emp2.nwdset_amtimeout(ws.Range['S' + strrw].Text);
emp2.nwdset_pmtimein(ws.Range['V' + strrw].Text);
emp2.nwdset_pmtimeout(ws.Range['X' + strrw].Text);
}
else
{
emp2.nwdset_amtimein(ws.Range['Z' + strrw].Text);
emp2.nwdset_pmtimeout(ws.Range["AB" + strrw].Text);
}
}
}
}
// third dtr/employee in a worksheet if not null
if (ws.Range["AN4"].Text != null && ws.Range["AN4"].Text != "0")
{
emp3.Id = int.Parse(ws.Range["AN4"].Text);
emp3.Name = ws.Range["AN3"].Text;
// get duties per day in a month
for (int rw = 12; rw <= 42; rw++)
{
string strrw = rw.ToString();
// get on off duties from morning to afternoon
if (ws.Range["AE" + strrw].Text != null)
{
emp3.AddWorkDay(ws.Range["AE" + strrw].Text);
}
if (ws.Range["AF" + strrw].Text != "Absent")
{
if (ws.Range["AO" + strrw].Text == null)
{
emp3.nwdset_amtimein(ws.Range["AF" + strrw].Text);
emp3.nwdset_amtimeout(ws.Range["AH" + strrw].Text);
emp3.nwdset_pmtimein(ws.Range["AK" + strrw].Text);
emp3.nwdset_pmtimeout(ws.Range["AM" + strrw].Text);
}
else
{
emp3.nwdset_amtimein(ws.Range["AO" + strrw].Text);
emp3.nwdset_pmtimeout(ws.Range["AQ" + strrw].Text);
}
}
}
}
// ===============================================================
// FIX BUG. ADD THE LAST WORKDAYS FOR EACH THREE EMPLOYEE DTRS
// ===============================================================
// temporary container
TimeSpan tinam, toutam, tinpm, toutpm;
TimeSpan tinam2, toutam2, tinpm2, toutpm2;
TimeSpan tinam3, toutam3, tinpm3, toutpm3;
// get last row from excel dtr record
int row = 11 + emp1.Workdays.Count;
// convert last row to string
string strrw2 = row.ToString();
// fix bug employee 1
if (emp1 != null && emp1.Workdays.Count != 0)
{
TimeSpan.TryParse(ws.Range["B" + strrw2].Text, out tinam);
TimeSpan.TryParse(ws.Range["D" + strrw2].Text, out toutam);
TimeSpan.TryParse(ws.Range["G" + strrw2].Text, out tinpm);
TimeSpan.TryParse(ws.Range["I" + strrw2].Text, out toutpm);
emp1.Workdays.Last().Value.TimeIn_AM = tinam;
emp1.Workdays.Last().Value.TimeOut_AM = toutam;
emp1.Workdays.Last().Value.TimeIn_PM = tinpm;
emp1.Workdays.Last().Value.TimeOut_PM = toutpm;
}
// fix bug employee 2
if (emp2 != null && emp2.Workdays.Count != 0)
{
TimeSpan.TryParse(ws.Range["Q" + strrw2].Text, out tinam2);
TimeSpan.TryParse(ws.Range["S" + strrw2].Text, out toutam2);
TimeSpan.TryParse(ws.Range["V" + strrw2].Text, out tinpm2);
TimeSpan.TryParse(ws.Range["X" + strrw2].Text, out toutpm2);
emp2.Workdays.Last().Value.TimeIn_AM = tinam2;
emp2.Workdays.Last().Value.TimeOut_AM = toutam2;
emp2.Workdays.Last().Value.TimeIn_PM = tinpm2;
emp2.Workdays.Last().Value.TimeOut_PM = toutpm2;
}
// fix bug employee 3
if (emp3 != null && emp3.Workdays.Count != 0)
{
TimeSpan.TryParse(ws.Range["AF" + strrw2].Text, out tinam3);
TimeSpan.TryParse(ws.Range["AH" + strrw2].Text, out toutam3);
TimeSpan.TryParse(ws.Range["AK" + strrw2].Text, out tinpm3);
TimeSpan.TryParse(ws.Range["AM" + strrw2].Text, out toutpm3);
emp3.Workdays.Last().Value.TimeIn_AM = tinam3;
emp3.Workdays.Last().Value.TimeOut_AM = toutam3;
emp3.Workdays.Last().Value.TimeIn_PM = tinpm3;
emp3.Workdays.Last().Value.TimeOut_PM = toutpm3;
}
// ===============================================================
// fix bug: add the last workdays for each three employee dtrs
// add the new three dtrs
if (!this.DTRs.ContainsKey(emp1.Id))
{
this.DTRs.Add(emp1.Id, emp1);
}
if (!this.DTRs.ContainsKey(emp2.Id))
{
this.DTRs.Add(emp2.Id, emp2);
}
if (!this.DTRs.ContainsKey(emp3.Id))
{
this.DTRs.Add(emp3.Id, emp3);
}
}
wb.Close();
}
}
else
{
MessageBox.Show("File does not exist.\nPlease select another report file to load.", "File not exists", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
}
}
public PdfPage ConvertOne(int Id)
{
EmployeeDTR emp = _ds.M.DTRs[Id];
using (ExcelEngine E = new ExcelEngine()) {
string filename = "template.xlsx";
IWorkbook wb = E.Excel.Workbooks.Open(filename);
IWorksheet ws;
wb.Version = ExcelVersion.Excel2013;
ws = wb.Worksheets["template"];
ws.Unprotect("0xc00000190132424343");
ws.Range["A14:Q44"].Clear();
// set name
ws.Range["A6"].Text = emp.Name;
ws.Range["J6"].Text = emp.Name;
int rw = 14;
string rw2 = rw.ToString();
foreach (WorkDay wd in _ds.M.DTRs[Id].Workdays.Values)
{
//names
ws.Range['A' + rw2].Text = wd.Id;
ws.Range['J' + rw2].Text = wd.Id;
// am time in
ws.Range['B' + rw2].Text = wd.TimeIn_AM.ToString() != "00:00:00" ? wd.TimeIn_AM.ToString(@"hh\:mm") : "";
ws.Range['K' + rw2].Text = wd.TimeIn_AM.ToString() != "00:00:00" ? wd.TimeIn_AM.ToString(@"hh\:mm") : "";
// am time out
ws.Range['C' + rw2].Text = wd.TimeOut_AM.ToString() != "00:00:00" ? wd.TimeOut_AM.ToString(@"hh\:mm") : "";
ws.Range['L' + rw2].Text = wd.TimeOut_AM.ToString() != "00:00:00" ? wd.TimeOut_AM.ToString(@"hh\:mm") : "";
// pm time in
ws.Range['E' + rw2].Text = wd.TimeIn_PM.ToString() != "00:00:00" ? wd.TimeIn_PM.ToString(@"hh\:mm") : "";
ws.Range['N' + rw2].Text = wd.TimeIn_PM.ToString() != "00:00:00" ? wd.TimeIn_PM.ToString(@"hh\:mm") : "";
// pm time out
ws.Range['F' + rw2].Text = wd.TimeOut_PM.ToString() != "00:00:00" ? wd.TimeOut_PM.ToString(@"hh\:mm") : "";
ws.Range['O' + rw2].Text = wd.TimeOut_PM.ToString() != "00:00:00" ? wd.TimeOut_PM.ToString(@"hh\:mm") : "";
}
ws.Protect("0xc00000190132424343");
wb.Save();
//save as pdf
string _reportfilename = "dtr.pdf";
//remove existing one to avoid errors
if (File.Exists(_reportfilename))
{
File.Delete(_reportfilename);
}
// initialize pdf
PdfDocument _pdfdoc = new PdfDocument();
PdfPage _pdfpage = new PdfPage();
// pdf converter object
ExcelToPdfConverter _converter = new ExcelToPdfConverter(wb);
// pdf settings
ExcelToPdfConverterSettings _settings = new ExcelToPdfConverterSettings();
_settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
_settings.TemplateDocument = _pdfdoc;
_settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
_pdfdoc.PageSettings.Orientation = PdfPageOrientation.Landscape;
_pdfdoc.PageSettings.Size = new PdfPageSettings().Size = PdfPageSize.Letter;
// convert now
_pdfdoc = _converter.Convert(_settings);
return(_pdfdoc.Pages[0]);
}
}
