Beispiel #1
0
        /// <summary>
        ///
        /// </summary>
        /// <returns></returns>
        public async Task <FileDto> DownLoadTemplate(int coaId)
        {
            var projectTypelist = EnumList.GetProjectTypeList();
            var statuslist      = EnumList.GetProjectStatusList();
            var currencylist    = await _accountUnitAppService.GetTypeOfCurrencyList();

            var jobNumberIsNumeric = false;

            var rollupAccountList = (await _jobUnitAppService.GetRollupAccountList(new AutoSearchInput()
            {
                Value = false
            })).ConvertAll(u => new NameValueDto()
            {
                Value = u.AccountId.ToString(),
                Name  = u.AccountNumber
            });

            var rollupDivisionList = (await _jobUnitAppService.GetDivisionList(new AutoSearchInput()
            {
                Value = true
            })).ConvertAll(u => new NameValueDto()
            {
                Value = u.JobId.ToString(),
                Name  = u.Caption
            });

            var budgetformatList = await(_jobUnitAppService.GetProjectCoaList(new AutoSearchInput()
            {
            }));
            var taxCreditList = (await _jobUnitAppService.GetTaxCreditList(new AutoSearchInput()
            {
            })).ConvertAll(u => new NameValueDto()
            {
                Value = u.Value,
                Name  = u.Name
            });

            return(CreateExcelPackage(
                       "ProjectTemplate_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx",
                       excelPackage =>
            {
                var sheet = excelPackage.Workbook.Worksheets.Add(L("ProjectTemplate"));
                sheet.Protection.IsProtected = true;
                var listDataSheet = excelPackage.Workbook.Worksheets.Add(L("DropDownListInformation"));
                listDataSheet.Hidden = OfficeOpenXml.eWorkSheetHidden.Hidden;

                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, projectTypelist, new KeyValuePair <string, string>(L("ProjectTypeNames"), L("ProjectTypeIds")), new KeyValuePair <string, string>("A", "B"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, statuslist, new KeyValuePair <string, string>(L("StatusNames"), L("StatusIds")), new KeyValuePair <string, string>("C", "D"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, currencylist, new KeyValuePair <string, string>(L("CurrencyNames"), L("CurrencyIds")), new KeyValuePair <string, string>("E", "F"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, rollupAccountList, new KeyValuePair <string, string>(L("RollUpAccountNames"), L("RollUpAccountIds")), new KeyValuePair <string, string>("G", "H"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, rollupDivisionList, new KeyValuePair <string, string>(L("RollUpDivisionNames"), L("RollUpDivisionIds")), new KeyValuePair <string, string>("I", "J"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, budgetformatList, new KeyValuePair <string, string>(L("BudgetFormatNames"), L("BudgetFormatIds")), new KeyValuePair <string, string>("K", "L"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, taxCreditList, new KeyValuePair <string, string>(L("TaxCreditNames"), L("TaxCreditIds")), new KeyValuePair <string, string>("M", "N"));


                //reference list columns to Excel Sheet
                ExcelHelper.AddValidationtoSheet(L("JobNumber"), sheet,
                                                 new Excelproperties
                {
                    ExcelFormula = ExcelHelper.GetMultiValidationString(
                        new List <string>()
                    {
                        ExcelHelper.GetMaxLengthFormula("A2", JobUnit.MaxJobNumberLength),
                        ExcelHelper.GetAllowNumberFormula("A2", jobNumberIsNumeric),
                        ExcelHelper.GetDuplicateCellFormula("A", startRowIndex, endRowIndex)
                    }
                        ),
                    ShowErrorMessage = true,
                    Error = ExcelHelper.ApplyPlaceHolderValues(L("AllowDuplicateVaues") + ", " + (jobNumberIsNumeric ? L("AllowNumbers") + ", " : "") + L("AllowMaxLength"),
                                                               new Dictionary <string, string>()
                    {
                        { "{length}", JobUnit.MaxJobNumberLength.ToString() },
                        { "{type}", "Charcters" }
                    }),
                    ErrorTitle = L("ValidationMessage"),
                    ErrorStyle = ExcelDataValidationWarningStyle.stop
                }, startRowIndex, endRowIndex, 1, "A");

                ExcelHelper.AddValidationtoSheet(L("JobName"), sheet,
                                                 new Excelproperties
                {
                    ExcelFormula = ExcelHelper.GetMultiValidationString(
                        new List <string>()
                    {
                        ExcelHelper.GetMaxLengthFormula("B2", JobUnit.MaxCaptionLength),
                        ExcelHelper.GetDuplicateCellFormula("B", startRowIndex, endRowIndex)
                    }),
                    ShowErrorMessage = true,
                    Error = ExcelHelper.ApplyPlaceHolderValues(L("AllowDuplicateVaues") + ", " + L("AllowMaxLength"), new Dictionary <string, string>()
                    {
                        { "{length}", JobUnit.MaxCaptionLength.ToString() },
                        { "{type}", "Charcters" }
                    }),
                    ErrorTitle = L("ValidationMessage"),
                    ErrorStyle = ExcelDataValidationWarningStyle.stop
                }, startRowIndex, endRowIndex, 2, "B");

                var excelDdlErrorMsgSettings = new Excelproperties
                {
                    ShowErrorMessage = true,
                    Error = L("ExcelDropDownListErrorMsg"),
                    ErrorTitle = L("ValidationMessage"),
                    ErrorStyle = ExcelDataValidationWarningStyle.stop
                };

                ExcelHelper.AddDropDownValidationToSheet(L("ProjectType"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "A", 2, projectTypelist.Count + 1), startRowIndex, endRowIndex, 3, "C", listcount: 1);

                ExcelHelper.AddDropDownValidationToSheet(L("BudgetFormat"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "K", 2, budgetformatList.Count + 1), startRowIndex, endRowIndex, 4, "D", listcount: 1);

                ExcelHelper.AddDropDownValidationToSheet(L("RollUpAccount"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "G", 2, rollupAccountList.Count + 1), startRowIndex, endRowIndex, 5, "E", listcount: rollupAccountList.Count);

                ExcelHelper.AddDropDownValidationToSheet(L("RollUpDivision"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "I", 2, rollupDivisionList.Count + 1), startRowIndex, endRowIndex, 6, "F", listcount: rollupDivisionList.Count);

                ExcelHelper.AddDropDownValidationToSheet(L("TaxCredit"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "M", 2, taxCreditList.Count + 1), startRowIndex, endRowIndex, 7, "G", listcount: taxCreditList.Count);

                ExcelHelper.AddDropDownValidationToSheet(L("Currency"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "E", 2, currencylist.Count + 1), startRowIndex, endRowIndex, 8, "H", listcount: 1);

                ExcelHelper.AddDropDownValidationToSheet(L("Status"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "C", 2, statuslist.Count + 1), startRowIndex, endRowIndex, 9, "I", listcount: statuslist.Count);



                #region dropdown list values add to sheet
                ExcelHelper.AddFormulaToSheet(L("ProjectTypeValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "C2", new string[] { "A", "B" }, 2, projectTypelist.Count + 1),
                                              startRowIndex, endRowIndex, true, 12, "L", true);

                ExcelHelper.AddFormulaToSheet(L("BudgetFormatValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "D2", new string[] { "K", "L" }, 2, budgetformatList.Count + 1),
                                              startRowIndex, endRowIndex, true, 13, "M", locked: true);

                ExcelHelper.AddFormulaToSheet(L("RollUpAccountValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "E2", new string[] { "G", "H" }, 2, rollupAccountList.Count + 1), startRowIndex, endRowIndex, true, 14, "N", locked: true);

                ExcelHelper.AddFormulaToSheet(L("RollUpDivisionValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "F2", new string[] { "I", "J" }, 2, rollupDivisionList.Count + 1), startRowIndex, endRowIndex, true, 15, "O", locked: true);

                ExcelHelper.AddFormulaToSheet(L("TaxCreditValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "G2", new string[] { "M", "N" }, 2, taxCreditList.Count + 1), startRowIndex, endRowIndex, true, 16, "P", locked: true);

                ExcelHelper.AddFormulaToSheet(L("CurrencyValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "H2", new string[] { "E", "F" }, 2, currencylist.Count + 1), startRowIndex, endRowIndex, true, 17, "Q", locked: true);

                ExcelHelper.AddFormulaToSheet(L("StatusValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "I2", new string[] { "C", "D" }, 2, statuslist.Count + 1), startRowIndex, endRowIndex, true, 18, "R", locked: true);
                #endregion
            }));
        }
Beispiel #2
0
        /// <summary>
        ///
        /// </summary>
        /// <returns></returns>
        public async Task <FileDto> DownLoadTemplate(int coaId)
        {
            //Get all List Information
            var classificationlist = await _accountUnitAppService.GetTypeOfAccountList();

            var currencylist = await _accountUnitAppService.GetTypeOfCurrencyList();

            var consolidationList = EnumList.GetTypeofConsolidationList();
            var divisionList      = (await _jobUnitAppService.GetDivisionList(new AutoSearchInput()
            {
            })).ConvertAll(u => new NameValueDto()
            {
                Value = u.JobId.ToString(),
                Name  = u.JobNumber
            });
            var rollUpAccountList = (await _accountUnitAppService.GetRollupAccountsList(new AutoSearchInput()
            {
                Id = coaId
            })).ConvertAll(u => new NameValueDto()
            {
                Value = u.AccountId.ToString(),
                Name  = u.AccountNumber
            });
            var booleanList            = ExcelHelper.GetBooleanList();
            var accountNumberIsNumeric = (await _coaUnitRepository.FirstOrDefaultAsync(coaId)).IsNumeric;


            return(CreateExcelPackage(
                       "LinesTemplate_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx",
                       excelPackage =>
            {
                var sheet = excelPackage.Workbook.Worksheets.Add(L("LinesTemplate"));
                sheet.Protection.IsProtected = true;
                var listDataSheet = excelPackage.Workbook.Worksheets.Add(L("DropDownListInformation"));
                listDataSheet.Hidden = OfficeOpenXml.eWorkSheetHidden.Hidden;

                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, classificationlist, new KeyValuePair <string, string>(L("ClassificationNames"), L("ClassificationIds")), new KeyValuePair <string, string>("A", "B"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, currencylist, new KeyValuePair <string, string>(L("CurrencyNames"), L("CurrencyIds")), new KeyValuePair <string, string>("C", "D"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, consolidationList, new KeyValuePair <string, string>(L("ConsolidationNames"), L("ConsolidationIds")), new KeyValuePair <string, string>("E", "F"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, divisionList, new KeyValuePair <string, string>(L("RollUpDivisionNames"), L("RollUpDivisionIds")), new KeyValuePair <string, string>("G", "H"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, rollUpAccountList, new KeyValuePair <string, string>(L("RollUpAccountNames"), L("RollUpAccountIds")), new KeyValuePair <string, string>("I", "J"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, booleanList, new KeyValuePair <string, string>(L("FlagsNames"), L("FlagsIds")), new KeyValuePair <string, string>("K", "L"));


                //reference list columns to Excel Sheet
                ExcelHelper.AddValidationtoSheet(L("LineNumber"), sheet,
                                                 new Excelproperties
                {
                    ExcelFormula = ExcelHelper.GetMultiValidationString(
                        new List <string>()
                    {
                        ExcelHelper.GetMaxLengthFormula("A2", AccountUnit.MaxAccountSize),
                        ExcelHelper.GetAllowNumberFormula("A2", accountNumberIsNumeric),
                        ExcelHelper.GetDuplicateCellFormula("A", startRowIndex, endRowIndex)
                    }
                        ),
                    ShowErrorMessage = true,
                    Error = ExcelHelper.ApplyPlaceHolderValues((accountNumberIsNumeric ? L("AllowNumbers") + ", " : "") + L("AllowDuplicateVaues") + ", "
                                                               + L("AllowMaxLength"), new Dictionary <string, string>()
                    {
                        { "{length}", AccountUnit.MaxAccountSize.ToString() },
                        { "{type}", "Charcters" }
                    }),
                    ErrorTitle = L("ValidationMessage"),
                    ErrorStyle = ExcelDataValidationWarningStyle.stop
                }
                                                 , startRowIndex, endRowIndex, 1, "A");

                ExcelHelper.AddValidationtoSheet(L("Caption"), sheet,
                                                 new Excelproperties
                {
                    ExcelFormula = ExcelHelper.GetMultiValidationString(
                        new List <string>()
                    {
                        ExcelHelper.GetMaxLengthFormula("B2", AccountUnit.MaxDisplayNameLength),
                        ExcelHelper.GetDuplicateCellFormula("B", startRowIndex, endRowIndex)
                    }),
                    ShowErrorMessage = true,
                    Error = ExcelHelper.ApplyPlaceHolderValues(L("AllowDuplicateVaues") + ", " + L("AllowMaxLength"), new Dictionary <string, string>()
                    {
                        { "{length}", AccountUnit.MaxDisplayNameLength.ToString() },
                        { "{type}", "Charcters" }
                    }),
                    ErrorTitle = L("ValidationMessage"),
                    ErrorStyle = ExcelDataValidationWarningStyle.stop
                }
                                                 , startRowIndex, endRowIndex, 2, "B");


                var excelDdlErrorMsgSettings = new Excelproperties
                {
                    ShowErrorMessage = true,
                    Error = L("ExcelDropDownListErrorMsg"),
                    ErrorTitle = L("ValidationMessage"),
                    ErrorStyle = ExcelDataValidationWarningStyle.stop
                };



                ExcelHelper.AddDropDownValidationToSheet(L("Classification"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "A", 2, classificationlist.Count + 1), startRowIndex, endRowIndex, 3, "C", listcount: classificationlist.Count);

                ExcelHelper.AddDropDownValidationToSheet(L("Consolidation"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "E", 2, consolidationList.Count + 1), startRowIndex, endRowIndex, 4, "D", listcount: 1);

                ExcelHelper.AddDropDownValidationToSheet(L("RollUpAccount"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "I", 2, rollUpAccountList.Count + 1), startRowIndex, endRowIndex, 5, "E", listcount: rollUpAccountList.Count);

                ExcelHelper.AddDropDownValidationToSheet(L("RollUpDivision"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "G", 2, divisionList.Count + 1), startRowIndex, endRowIndex, 6, "F", listcount: divisionList.Count);

                ExcelHelper.AddDropDownValidationToSheet(L("Currency"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "C", 2, currencylist.Count + 1), startRowIndex, endRowIndex, 7, "G", listcount: 1);

                ExcelHelper.AddDropDownValidationToSheet(L("JournalsAllowed"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "K", 2, booleanList.Count + 1), startRowIndex, endRowIndex, 8, "H", listcount: 1);


                #region dropdown list values add to sheet

                ExcelHelper.AddFormulaToSheet(L("ClassificationValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "C2", new string[] { "A", "B" }, 2, classificationlist.Count + 1), startRowIndex, endRowIndex, true, 11, "K", locked: true);

                ExcelHelper.AddFormulaToSheet(L("ConsolidationValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "D2", new string[] { "E", "F" }, 2, consolidationList.Count + 1), startRowIndex, endRowIndex, true, 12, "L", locked: true);

                ExcelHelper.AddFormulaToSheet(L("RollUpAccountValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "E2", new string[] { "I", "J" }, 2, rollUpAccountList.Count + 1), startRowIndex, endRowIndex, true, 13, "M", locked: true);

                ExcelHelper.AddFormulaToSheet(L("RollUpDivisionValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "F2", new string[] { "G", "H" }, 2, divisionList.Count + 1), startRowIndex, endRowIndex, true, 14, "N", locked: true);

                ExcelHelper.AddFormulaToSheet(L("CurrencyValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "G2", new string[] { "C", "D" }, 2, currencylist.Count + 1), startRowIndex, endRowIndex, true, 15, "O", locked: true);


                #endregion
            }));
        }
Beispiel #3
0
        /// <summary>
        ///
        /// </summary>
        /// <returns></returns>
        public async Task <FileDto> DownLoadTemplate(int coaId)
        {
            var currencylist = await _accountUnitAppService.GetTypeOfCurrencyList();

            var jobNumberIsNumeric = false;
            var booleanList        = ExcelHelper.GetBooleanList();

            return(CreateExcelPackage(
                       "DivisionTemplate_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx",
                       excelPackage =>
            {
                var sheet = excelPackage.Workbook.Worksheets.Add(L("DivisionTemplate"));
                sheet.Protection.IsProtected = true;
                var listDataSheet = excelPackage.Workbook.Worksheets.Add(L("DropDownListInformation"));
                listDataSheet.Hidden = OfficeOpenXml.eWorkSheetHidden.Hidden;

                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, currencylist, new KeyValuePair <string, string>(L("CurrencyNames"), L("CurrencyIds")), new KeyValuePair <string, string>("C", "D"));
                ExcelHelper.AddListDataIntoWorkSheet(listDataSheet, booleanList, new KeyValuePair <string, string>(L("FlagsNames"), L("FlagsIds")), new KeyValuePair <string, string>("E", "F"));


                //reference list columns to Excel Sheet
                ExcelHelper.AddValidationtoSheet(L("Number"), sheet,
                                                 new Excelproperties
                {
                    ExcelFormula = ExcelHelper.GetMultiValidationString(
                        new List <string>()
                    {
                        ExcelHelper.GetMaxLengthFormula("A2", JobUnit.MaxJobNumberLength),
                        ExcelHelper.GetAllowNumberFormula("A2", jobNumberIsNumeric),
                        ExcelHelper.GetDuplicateCellFormula("A", startRowIndex, endRowIndex)
                    }
                        ),
                    ShowErrorMessage = true,
                    Error = ExcelHelper.ApplyPlaceHolderValues(L("AllowDuplicateVaues") + ", " + (jobNumberIsNumeric ? L("AllowNumbers") + ", " : "") + L("AllowMaxLength"),
                                                               new Dictionary <string, string>()
                    {
                        { "{length}", JobUnit.MaxJobNumberLength.ToString() },
                        { "{type}", "Charcters" }
                    }),
                    ErrorTitle = L("ValidationMessage"),
                    ErrorStyle = ExcelDataValidationWarningStyle.stop
                }
                                                 , startRowIndex, endRowIndex, 1, "A");

                ExcelHelper.AddValidationtoSheet(L("Description"), sheet,
                                                 new Excelproperties
                {
                    ExcelFormula = ExcelHelper.GetMultiValidationString(
                        new List <string>()
                    {
                        ExcelHelper.GetMaxLengthFormula("B2", JobUnit.MaxCaptionLength),
                        ExcelHelper.GetDuplicateCellFormula("B", startRowIndex, endRowIndex)
                    }),
                    ShowErrorMessage = true,
                    Error = ExcelHelper.ApplyPlaceHolderValues(L("AllowDuplicateVaues") + ", " + L("AllowMaxLength"), new Dictionary <string, string>()
                    {
                        { "{length}", JobUnit.MaxCaptionLength.ToString() },
                        { "{type}", "Charcters" }
                    }),
                    ErrorTitle = L("ValidationMessage"),
                    ErrorStyle = ExcelDataValidationWarningStyle.stop
                }
                                                 , startRowIndex, endRowIndex, 2, "B");

                var excelDdlErrorMsgSettings = new Excelproperties
                {
                    ShowErrorMessage = true,
                    Error = L("ExcelDropDownListErrorMsg"),
                    ErrorTitle = L("ValidationMessage"),
                    ErrorStyle = ExcelDataValidationWarningStyle.stop
                };

                ExcelHelper.AddDropDownValidationToSheet(L("Currency"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "C", 2, currencylist.Count + 1), startRowIndex, endRowIndex, 3, "C", listcount: 1);

                ExcelHelper.AddDropDownValidationToSheet(L("Active"), sheet, excelDdlErrorMsgSettings, ExcelHelper.GetDropDownListFormula(L("DropDownListInformation"), "E", 2, booleanList.Count + 1), startRowIndex, endRowIndex, 4, "D", listcount: 1);

                #region dropdown list values add to sheet
                ExcelHelper.AddFormulaToSheet(L("CurrencyValue"), sheet,
                                              ExcelHelper.GetVLOOKUPFormula(L("DropDownListInformation"), "C2", new string[] { "C", "D" }, 2, currencylist.Count + 1), startRowIndex, endRowIndex, true, 7, "G", locked: true);
                #endregion
            }));
        }