private void CreateTimStarImportFile()
{
CTsImportFile tsimport = new CTsImportFile();
CTimesheet timesheet = new CTimesheet();
CCheckDate checkdate = new CCheckDate();
DateTime dPeriod = checkdate.CheckPeriod();
int sPeriodEndDay;
if (dPeriod.Day == 1)
{
sPeriodEndDay = 15;
}
else
{
sPeriodEndDay = DateTime.DaysInMonth(dPeriod.Year, dPeriod.Month);
}
string sImportXlsx = @"..\..\..\..\TimeImport.xlsx";
string sFieldGlassXls = @"..\..\..\..\CAC_Time_Sheet_Combine_Report_01Posterior.xls";
string sTEListXls = @"\\sv11\Data\06_TimeSheet\TE Timesheet 2015\TE List for Timesheet.xlsx";
string sTimesheetFolder = @"\\sv11\Data\06_TimeSheet\TE Timesheet 2015\" + dPeriod.ToString("MM") + sPeriodEndDay;
string[] saTimesheetXls = Directory.GetFiles(sTimesheetFolder, "*.xlsx");
foreach (string sTimesheetXls in saTimesheetXls)
{
timesheet.CheckTimesheet(sImportXlsx, sTimesheetXls, sTEListXls);
}
tsimport.CreateImportFileFromFieldglassData(sImportXlsx, sFieldGlassXls, sTEListXls);
}
public bool CreateImportFileFromFieldglassData(string sImportXlsx, string sFieldglassXls, string sTEListXls)
{
if (!System.IO.File.Exists(sFieldglassXls))
{
return(false); //Exit if Fieldglass file is not exist
}
CCheckDate checkdate = new CCheckDate();
dPeriod = checkdate.CheckPeriod(); //Get period of this time
if (dPeriod.Day == 1)
{
sFileTitle = dPeriod.Year + "-" + dPeriod.ToString("MM") + "-Anterior";
}
else
{
sFileTitle = dPeriod.Year + "-" + dPeriod.ToString("MM") + "-Posterior";
}
Excel.Application app = new Excel.Application();
object misValue = System.Reflection.Missing.Value;
Excel.Workbook wbkImport;
//Open Fieldglass excel file
FileInfo fiD = new FileInfo(sFieldglassXls); //Get Fieldglass file info
string sImportFullPath = fiD.DirectoryName;
//Open this month's import excel file
string sSaveImportFullPath = sImportFullPath + "\\" + sFileTitle + ".xlsx";
if (File.Exists(sSaveImportFullPath))
{
wbkImport = app.Workbooks.Open(sSaveImportFullPath); //Open saved import excel file if it exists
}
else if (File.Exists(sImportXlsx))
{
wbkImport = app.Workbooks.Open(sImportXlsx); //Open template excel file if saved import excel file deosn't exist
}
else
{
wbkImport = app.Workbooks.Add(); //Open new excel file if nothing exists
}
Excel.Worksheet wshImport = wbkImport.Worksheets[1]; //Open first sheet
FileInfo fiS = new FileInfo(sFieldglassXls);
Excel.Workbook wbkFieldglass = app.Workbooks.Open(fiS.FullName);
Excel.Worksheet wshFGTimesheet = wbkFieldglass.Worksheets[1]; //Open Timesheet sheet
Excel.Worksheet wshFGAbsence = wbkFieldglass.Worksheets[2]; //Open Absence sheet
//Open TE List excel file
FileInfo fiT = new FileInfo(sTEListXls);
Excel.Workbook wbkTEList = app.Workbooks.Open(fiT.FullName);
Excel.Worksheet wshTEList = wbkTEList.Worksheets["TE"]; //Open first sheet
bool bState = CheckFieldglassDataContents(wshFGTimesheet, wshFGAbsence);
if (bState == true)
{
bState = CreateTimeStarImportXlsx(wshImport, wshFGTimesheet, wshFGAbsence, wshTEList);
}
wbkFieldglass.Close(false, misValue, misValue); //Close Fieldglass excel file
if (bState == true)
{
wbkImport.Worksheets[1].Activate();
if (File.Exists(sSaveImportFullPath))
{
wbkImport.Save(); //Overwrite saved import excel file if it already exists
}
else
{
wbkImport.SaveAs(sSaveImportFullPath); //Save as import excel file if not exists
}
}
wbkImport.Close(false, misValue, misValue);
app.Quit();
return(bState);
}
public bool CheckTimesheet(string sImportXlsx, string sTimesheetXls, string sTEListXls)
{
Excel.Workbook wbkTimesheet; //timesheet excel file
Excel.Worksheet wshTimesheet; //its month's sheet of timesheet
string sSheetName = null;
if (!System.IO.File.Exists(sTimesheetXls))
{
return(false); //Exit if timesheet file is not exist
}
CCheckDate checkdate = new CCheckDate();
dPeriod = checkdate.CheckPeriod(); //Get period of this time
if (dPeriod.Day == 1)
{
sFileTitle = dPeriod.Year + "-" + dPeriod.ToString("MM") + "-Anterior";
}
else
{
sFileTitle = dPeriod.Year + "-" + dPeriod.ToString("MM") + "-Posterior";
}
Excel.Application app = new Excel.Application();
object misValue = System.Reflection.Missing.Value;
//Open timesheet excel file
FileInfo fiS = new FileInfo(sTimesheetXls); //Get timesheet file info
if (fiS.Name.StartsWith("~$"))
{
return(false); //Exit if timesheet file is temp file
}
try
{
wbkTimesheet = app.Workbooks.Open(fiS.FullName,
0,
Type.Missing, Type.Missing, "", "", //Enter empty password
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing); //Open timesheet excel file
sSheetName = dPeriod.ToString("MMM") + ". " + dPeriod.ToString("yyyy"); //Get sheet name of timesheet
wshTimesheet = wbkTimesheet.Worksheets[sSheetName]; //Open its month's sheet
}
catch (COMException)
{
return(false);
}
//Open template excel file
FileInfo fiD = new FileInfo(sImportXlsx);
string sImportFullPath = fiD.DirectoryName;
string sSaveImportFullPath = sImportFullPath + "\\" + sFileTitle + ".xlsx";
//Open saved import excel file
FileInfo fiV = new FileInfo(sSaveImportFullPath);
Excel.Workbook wbkImport;
if (File.Exists(fiV.FullName))
{
wbkImport = app.Workbooks.Open(fiV.FullName); //Open saved import excel file if it exists
}
else if (File.Exists(sImportXlsx))
{
wbkImport = app.Workbooks.Open(fiD.FullName); //Open template excel file if saved import excel file deosn't exist
}
else
{
wbkImport = app.Workbooks.Add(); //Open new excel file if nothing exists
}
Excel.Worksheet wshImport = wbkImport.Worksheets[1]; //Open first sheet
//Open TE List excel file
FileInfo fiT = new FileInfo(sTEListXls);
Excel.Workbook wbkTEList = app.Workbooks.Open(fiT.FullName);
Excel.Worksheet wshTEList = wbkTEList.Worksheets["TE"]; //Open first sheet
bool bState = CheckTimesheetFormat(wshTimesheet);
iId = CheckEmployeeID(wshTEList);
if (bState == true)
{
bState = CreateTimeStarImportXlsx(wshImport, wshTimesheet);
}
wbkTimesheet.Close(false, misValue, misValue); //Close timesheet excel file
if (bState == true)
{
wbkImport.Worksheets[1].Activate();
if (File.Exists(fiV.FullName))
{
wbkImport.Save(); //Overwrite saved import excel file if it already exists
}
else
{
wbkImport.SaveAs(fiV.FullName); //Save as import excel file if not exists
}
}
wbkImport.Close(false, misValue, misValue);
app.Quit();
return(bState);
}