/// <summary> /// This function will save the report by closing the worksheet. /// </summary> public void SaveReport() { m_objCommonOpenXMLObject.CellMerging(m_lstMergeColumns); m_objCommonOpenXMLObject.SaveWorkSheet(); m_objCommonOpenXMLObject.CloseSpreadSheet(); m_objCommonOpenXMLObject = null; }
public ReportsSL(int i_nGridType, string i_sOverrideGridType, string i_sFileName) { this.m_nGridType = i_nGridType; this.m_sFileName = i_sFileName; m_objCommonOpenXMLObject = new InsiderTradingExcelWriter.ExcelFacade.CommonOpenXMLObject(); m_objLoginUserDetails = (LoginUserDetails)Common.Common.GetSessionValue(ConstEnum.SessionValue.UserDetails); m_nRowCount = 1; this.m_sOverrideGridType = i_sOverrideGridType; }
/// <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."))); } }