/// <summary>
/// This function will be used for creating a sheet to be added in the Excel. When the sheet is created then the report title
/// and the creation date are also added to the report sheet.
/// </summary>
/// <param name="sReportName">The name of the report</param>
/// <param name="sFormattedCreationDate">The report creation date in format DD/MMM/YYYY</param>
public void GenerateSheet(string sReportName, string sFormattedCreationDate)
{
int nReportNameLbl = 1, nReportNameValue = 2, nCreatedDateLabel = 3, nCreatedDateValue = 4;
try
{
m_objCommonOpenXMLObject.OpenFile(this.m_sFileName, true);
m_objCommonOpenXMLObject.OpenXMLObjectCreation();
m_objCommonOpenXMLObject.OpenXMLCreateWorkSheetPartSheetData();
m_objCommonOpenXMLObject.CreateNewRow(m_nRowCount);
m_objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[nReportNameLbl], "Report Name", m_nRowCount,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.ARIAL_NARROW_10_TEXT_CUSTOM_GRAY_BACKGROUND_BOLD_WITH_BORDERS);
m_objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[nReportNameValue], sReportName, m_nRowCount,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.ARIAL_NARROW_10_TEXT_PLAIN_BACKGROUND_NORMAL_WITH_BORDERS);
m_objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[nCreatedDateLabel], "Created On", m_nRowCount,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.ARIAL_NARROW_10_TEXT_CUSTOM_GRAY_BACKGROUND_BOLD_WITH_BORDERS);
m_objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[nCreatedDateValue], sFormattedCreationDate, m_nRowCount,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.ARIAL_NARROW_10_TEXT_PLAIN_BACKGROUND_NORMAL_WITH_BORDERS);
m_objCommonOpenXMLObject.AddToSheetData();
m_nRowCount++;//Add blank rows after the report name row
}
catch (Exception exp)
{
}
}
/// <summary>
/// This function will be used for downloading the excel containing the list of Employees with status if Period End is completed or not
/// </summary>
/// <returns></returns>
public ActionResult DownloadPeriodEndPerformedExcel(int acid)
{
string[] m_sExcelColumnNames = new string[] { "", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string sTmpFileName = "ExcelDownload";
string i_sExportDocumentFolderPath = ConfigurationManager.AppSettings["Document"];
string MASSUPLOAD_ERROR_EXCEL_SHEET_NAME = "EmployeePeriodEnd";
string ERROR_EXCEL_COLUMN1_HEADER = "UserInfoId";
string ERROR_EXCEL_COLUMN2_HEADER = "LoginId";
string ERROR_EXCEL_COLUMN3_HEADER = "Has Performed Period End";
string ERROR_EXCEL_COLUMN4_HEADER = "EmployeeName";
System.Collections.ArrayList arrColumnWidth = new System.Collections.ArrayList();
int nExcelRowCounter = 1;
InsiderTrading.SL.UserInfoSL objUserInfoSL = null;
MassUploadSL objMassUploadSL = null;
InsiderTradingExcelWriter.ExcelFacade.CommonOpenXMLObject objCommonOpenXMLObject;
LoginUserDetails objLoginUserDetails = (LoginUserDetails)InsiderTrading.Common.Common.GetSessionValue((string)Common.ConstEnum.SessionValue.UserDetails);
string sCompanyConnectionString = objLoginUserDetails.CompanyDBConnectionString;
int nPeriodEndMassUploadId = 6;
string sFilePathToDownload = null;
try
{
using (objMassUploadSL = new MassUploadSL())
{
MassUploadDTO objMassUploadDTO = objMassUploadSL.GetSingleMassUploadDetails(sCompanyConnectionString, nPeriodEndMassUploadId);
sTmpFileName = objMassUploadDTO.TemplateFileName;
MASSUPLOAD_ERROR_EXCEL_SHEET_NAME = objMassUploadDTO.SheetName;
}
string[] sFilesfromExportFolder = Directory.GetFiles(i_sExportDocumentFolderPath);
//Delete the files which are created 5 min before from the Document folder
foreach (string sOldExportFile in sFilesfromExportFolder)
{
FileInfo fi = new FileInfo(sOldExportFile);
if (fi.LastAccessTime < DateTime.Now.AddMinutes(-5))
{
fi.Delete();
}
}
objCommonOpenXMLObject = new InsiderTradingExcelWriter.ExcelFacade.CommonOpenXMLObject();
objCommonOpenXMLObject.OpenFile(i_sExportDocumentFolderPath + sTmpFileName + ".xlsx", true);
objCommonOpenXMLObject.OpenXMLObjectCreation();
objCommonOpenXMLObject.OpenXMLCreateWorkSheetPartSheetData();
arrColumnWidth.Add("1:40:25");
objCommonOpenXMLObject.AssignColumnWidth(arrColumnWidth);
objCommonOpenXMLObject.CreateNewRow(nExcelRowCounter);
objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[1], ERROR_EXCEL_COLUMN1_HEADER, nExcelRowCounter,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.ARIAL_NARROW_10_TEXT_PLAIN_BACKGROUND_BOLD_WITH_NO_BORDER);
objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[2], ERROR_EXCEL_COLUMN2_HEADER, nExcelRowCounter,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.ARIAL_NARROW_10_TEXT_PLAIN_BACKGROUND_BOLD_WITH_NO_BORDER);
objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[3], ERROR_EXCEL_COLUMN3_HEADER, nExcelRowCounter,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.ARIAL_NARROW_10_TEXT_PLAIN_BACKGROUND_BOLD_WITH_NO_BORDER);
objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[4], ERROR_EXCEL_COLUMN4_HEADER, nExcelRowCounter,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.ARIAL_NARROW_10_TEXT_PLAIN_BACKGROUND_BOLD_WITH_NO_BORDER);
nExcelRowCounter++;
objCommonOpenXMLObject.AddToSheetData();
GC.Collect();
GC.WaitForFullGCComplete();
using (objUserInfoSL = new SL.UserInfoSL())
{
List <UserInfoDTO> objUserInfoList = objUserInfoSL.GetPeriodEndPerformedUserInfoList(sCompanyConnectionString);
int nCounter = 1;
foreach (UserInfoDTO objUserInfoDTO in objUserInfoList)
{
objCommonOpenXMLObject.CreateNewRow(nExcelRowCounter);
objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[1], nCounter.ToString(), nExcelRowCounter,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.DEFAULT_NO_STYLE);
objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[2], objUserInfoDTO.LoginID, nExcelRowCounter,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.DEFAULT_NO_STYLE);
objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[3], objUserInfoDTO.PeriodEndPerformed, nExcelRowCounter,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.DEFAULT_NO_STYLE);
objCommonOpenXMLObject.CreateTextCell(m_sExcelColumnNames[4], objUserInfoDTO.EmployeeName, nExcelRowCounter,
(int)InsiderTradingExcelWriter.ExcelFacade.ConstantEnum.OpenXMLStyleIndex.DEFAULT_NO_STYLE);
nExcelRowCounter++;
objCommonOpenXMLObject.AddToSheetData();
nCounter++;
}
}
objCommonOpenXMLObject.OpenXMLWorksheetAssignment(MASSUPLOAD_ERROR_EXCEL_SHEET_NAME, "", false);
objCommonOpenXMLObject.SaveWorkSheet();
objCommonOpenXMLObject.CloseSpreadSheet();
sFilePathToDownload = i_sExportDocumentFolderPath + sTmpFileName + ".xlsx";
if (!System.IO.File.Exists(sFilePathToDownload))
{
sFilePathToDownload = null;
}
}
catch (Exception exp)
{
string sErrorMessage = exp.Message;
return(RedirectToAction("AllMassUpload", "MassUpload",
new { acid = Common.ConstEnum.UserActions.MASSUPLOAD_LIST }).
Warning(HttpUtility.UrlEncode("Error occurred when downloading the template with data, please contact the Administrator.Error occurred is:" + sErrorMessage)));
}
finally
{
objCommonOpenXMLObject = null;
}
if (sFilePathToDownload != null && sFilePathToDownload != "")
{
return(File(new FileStream(sFilePathToDownload, FileMode.Open), ".xlsx", sTmpFileName + ".xlsx"));
}
else
{
return(RedirectToAction("AllMassUpload", "MassUpload",
new { acid = Common.ConstEnum.UserActions.MASSUPLOAD_LIST }).
Warning(HttpUtility.UrlEncode("Error occurred when downloading the template with data, please contact the Administrator.Error occurred is: File not created.")));
}
}