public async Task <JsonResult> AddItem(ItemModel item) { IItemServices itemServices = new ItemServices(); item.ItemName = Regex.Replace(item.ItemName, "[^a-zA-Z0-9]", "", RegexOptions.Compiled); try { if (itemServices.DoesTableExists(item.ItemName)) { await itemServices.DropExistingTable(item.ItemName); } var tableName = await itemServices.CreateItemTable(item); IExcelServices excelServices = new ExcelServices(new MemoryStream(item.ItemContent)); var list = await excelServices.ParseExcelFile(); var ret = await itemServices.WriteLine(list, item.ItemName); return(new JsonResult { Data = ret, JsonRequestBehavior = JsonRequestBehavior.AllowGet }); } catch (Exception ex) { throw ex; } }
public void UpdateTableValuesActualTest() { Dictionary <string, string> CategoriesAddress = null; var streamFile = TestsHelper.GetAssemblyFile("Budget Cashflow.xlsx"); using (var cashflowExcelPkg = new ExcelPackage(streamFile)) { IEnumerable <string> categoryList = TestsHelper.GetCategoryList(); var categoryListWithTotals = Helpers.AddItemsToIenumeration(categoryList, new List <string>() { "Sub Total", "Total" }); var ExpensesWSheet = cashflowExcelPkg.Workbook.Worksheets["Expenses details"]; var yearBudgetTable = ExpensesWSheet.Tables["Table_2018"]; if (yearBudgetTable != null) { CategoriesAddress = ExcelHelpers.GetNamesAdress(categoryListWithTotals, yearBudgetTable); } var yearWSheet = cashflowExcelPkg.Workbook.Worksheets["Year summary"]; var excelTable = yearWSheet.Tables["tblOperatingExpenses"]; string keyCellValue = null; if (CategoriesAddress != null) { if (CategoriesAddress.TryGetValue("Total", out keyCellValue)) { ExcelServices.UpdateTableValues(excelTable, "ACTUAL", keyCellValue); } } var saveExcel = TestsHelper.SaveExcrlPackage(cashflowExcelPkg, "Update-Test2"); saveExcel.Should().BeTrue(); } }
private static Dictionary <string, string[]> GetTablesIndex(ExcelTable table, string inputs, string outputs) { var inputsIsNumber = int.TryParse(inputs, out var inputsColumnsCount); var outputsIsNumber = int.TryParse(outputs, out var outputsColumnsCount); Dictionary <string, string[]> dictionary = null; if (inputsIsNumber && outputsIsNumber) { dictionary = ExcelServices.GetColumnRagngeInLeters(table, inputsColumnsCount, outputsColumnsCount); } else { var inputsIndex = inputs.Split(',').ToArray(); var outputsIndex = outputs.Split(',').ToArray(); if (inputsIndex.Any() && outputsIndex.Any()) { dictionary = new Dictionary <string, string[]>() { { "inputsIndex", inputsIndex }, { "outputsIndex", outputsIndex } }; } } return(dictionary); }
public void getSubMovementsModelFromExcel() { var resourceFileNema = "Transactions.xlxs"; var resourcePath = $"ExcelClient.Tests.TestData.{resourceFileNema}"; var assembly = Assembly.GetExecutingAssembly(); Stream resourceAsStream = assembly.GetManifestResourceStream(resourcePath); string filename; FileStream fs = resourceAsStream as FileStream; if (fs != null) { filename = fs.Name; } ExcelPackage ep = new ExcelPackage(resourceAsStream); ExcelWorksheet workSheet = ep.Workbook.Worksheets["Felles"]; var json = ExcelServices.GetJsonFromTable(workSheet); var jarray = JArray.Parse(json); List <AccountMovement> acountsMovments = new List <AccountMovement>(); foreach (var movment in jarray) { acountsMovments.Add(new ModelClassServices().JsonToAccountMovement(movment)); } var table = workSheet.Tables.FirstOrDefault(); json.Should().NotBeNull(); }
public void UpdateClassesTableValues() { var streamFile = TestsHelper.GetAssemblyFile("Budget Cashflow.xlsx"); using (var cashflowExcelPkg = new ExcelPackage(streamFile)) { IEnumerable <string> categoryList = TestsHelper.GetCategoryList(); var expensesWSheet = cashflowExcelPkg.Workbook.Worksheets["Expenses details"]; var yearBudgetTable = expensesWSheet.Tables["Year_budget"]; var yearExpensesTable = expensesWSheet.Tables["Table_2018"]; var monthWSheet = cashflowExcelPkg.Workbook.Worksheets["Monthly summary"]; var tblOperatingExpenses7Table = monthWSheet.Tables["tblOperatingExpenses7"]; Dictionary <string, string> monthBudgetCategoriesAddress = null; Dictionary <string, string> monthExpensesCategoriesAddress = null; // get address to Month budget table var categoriesWithoutIncome = Helpers.DeleteItemsfromIenumeration(categoryList, new List <string>() { "Åse", "Matias" }); monthBudgetCategoriesAddress = ExcelServices.GetColumnsNameAdress(categoriesWithoutIncome, yearBudgetTable); monthExpensesCategoriesAddress = ExcelServices.GetColumnsNameAdress(categoriesWithoutIncome, yearExpensesTable); ExcelServices.UpdateClassesTableValues(monthBudgetCategoriesAddress, monthExpensesCategoriesAddress, tblOperatingExpenses7Table); var saveExcel = TestsHelper.SaveExcrlPackage(cashflowExcelPkg, "UpdateClassesTableValues-Test"); saveExcel.Should().BeTrue(); } }
public static Tuple <SanofiNota, SanofiRetorno> ReadSanofi(string path) { if (string.IsNullOrEmpty(path)) { throw new ArgumentException("Invalid path."); } var files = Directory.GetFiles(path).ToList(); var config = new MapperConfiguration(cfg => { cfg.CreateMap <BaseDomain, SanofiNota>(); cfg.CreateMap <BaseDomain, SanofiRetorno>(); }); var mapper = config.CreateMapper(); SanofiNota sanofiNota = null; SanofiRetorno sanofiRetorno = null; files.ForEach(file => { if (file.Contains("nota.xlsx")) { sanofiNota = mapper.Map <SanofiNota>(ExcelServices.ReadData(file, DateNotaColumnLetter)); } if (file.Contains("retorno.xlsx")) { sanofiRetorno = mapper.Map <SanofiRetorno>(ExcelServices.ReadData(file, DateRetornoColumnLetter)); } }); return(new Tuple <SanofiNota, SanofiRetorno>(sanofiNota, sanofiRetorno)); }
public void getSubCategoryModelFromExcel() { //var resourcePath = "ExcelClient.Tests.TestData.Transactions.xlxs"; var resourcePath = "ExcelClient.Tests.TestData.Categories.xlsx"; var assembly = Assembly.GetExecutingAssembly(); Stream resourceAsStream = assembly.GetManifestResourceStream(resourcePath); //var file = "Transactions.xlxs"; var file = "Categories.xlsx"; var savedFilePath = Path.Combine(Directory.GetCurrentDirectory() + @"..\..\..\..\TestData\", file); var name = Path.GetFileNameWithoutExtension(savedFilePath); var fi = new FileInfo(savedFilePath); ExcelPackage ep = new ExcelPackage(new FileInfo(savedFilePath)); ExcelPackage ep1 = new ExcelPackage(resourceAsStream); //ExcelPackage ep = new ExcelPackage(new FileInfo(resourcePath)); ExcelWorksheet workSheet = ep.Workbook.Worksheets.FirstOrDefault(); ExcelWorksheet workSheet1 = ep1.Workbook.Worksheets.FirstOrDefault(); var json = ExcelServices.GetJsonFromTable(workSheet); var json1 = ExcelServices.GetJsonFromTable(workSheet1); var jarray = JArray.Parse(json1); List <SubCategory> subcategories = new List <SubCategory>(); foreach (var subCategory in jarray) { subcategories.Add(JsonServices.GetSubCategory(subCategory)); } var table = workSheet.Tables.FirstOrDefault(); json.Should().NotBeNull(); }
private string createSummary(bool asHtml, IEnumerable <int> fields) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(Path.Combine(Directory.GetCurrentDirectory(), "Tabla.xlsx")); Excel.Worksheet xlWorksheet = xlWorkBook.Worksheets["Summary"]; var tempFolder = Path.Combine(Directory.GetCurrentDirectory(), "temp"); if (!Directory.Exists(tempFolder)) { Directory.CreateDirectory(tempFolder); } else { removeFilesFrom(tempFolder); } string htmlFile = asHtml ? Path.Combine(tempFolder, $"{Guid.NewGuid()}.html") : Path.Combine(tempFolder, $"{Guid.NewGuid()}.xlsx"); xlWorkBook.Saved = true; ExcelServices.PrepareSummary(_components, xlWorksheet, fields); xlWorkBook.SaveAs(htmlFile, asHtml ? Excel.XlFileFormat.xlHtml : Excel.XlFileFormat.xlOpenXMLWorkbook); xlWorkBook.Saved = true; xlWorkBook.Close(); xlApp.Quit(); release(xlApp); release(xlWorkBook); release(xlWorksheet); return(htmlFile); }
public void CreateExcelMonthSummaryTableFromMovementsViewModelIncomeTest() { IEnumerable <string> categoryList = TestsHelper.GetCategoryList(); var movementsViewModels = TestsHelper.GetMovementsViewModels(); using (var stream = new MemoryStream()) using (var package = new ExcelPackage(stream)) { package.Workbook.Worksheets.Add("Sheet1"); var categoriesAverageWSheet = package.Workbook.Worksheets["Sheet1"]; var excelTable = ExcelServices.CreateCategoriesMonthsAveragetest(categoriesAverageWSheet, 13, movementsViewModels, categoryList, 2019, false); var table = package.Workbook.Worksheets.FirstOrDefault().Tables.FirstOrDefault(); var start = table.Address.Start.Address; var end = table.Address.End.Address; var noko = table.WorkSheet.Cells["B6"].Value = ""; //now test that it generated properly, such as: //var sheeDefault = package.Workbook.Worksheets.FirstOrDefault(); //if (sheeDefault != null) sheeDefault.Cells["D57"].Value.Should().Be(-55); var saveExcel = TestsHelper.SaveExcrlPackage(package, "Incomes-test"); saveExcel.Should().BeTrue(); } }
public R_DetalleVentaVM() { _reporteDV = new ObservableCollection <Models._RDetalleVenta>(); RefreshCommand = new Command(CmdRefresh); ExportToExcelCommand = new Command(async() => await ExportToExcel()); excelService = new ExcelServices(); }
public void Test1() { var resourcePath = "dmnClient.Test.TestData.dmnTest1.xlsx"; var assembly = Assembly.GetExecutingAssembly(); ExcelPackage ep; var name = string.Empty; using (Stream resourceAsStream = assembly.GetManifestResourceStream(resourcePath)) { ep = new ExcelPackage(resourceAsStream); } //var file = "dmnTest1.xlsx"; //var savedFilePath = Path.Combine(Directory.GetCurrentDirectory() + @"..\..\..\..\TestData\", file); ////var savedFilePath = @"C:\Users\MatiasGonzalezTognon\Dropbox (Arkitektum AS)\Ark_prosjekter\DIBK\DigiTek\Brannteknisk prosjektering\DMN\Trinn1\ExcelFiles\13_OverflateKledning.xlsx"; //var name = Path.GetFileNameWithoutExtension(savedFilePath); //var fi = new FileInfo(savedFilePath); //ExcelPackage ep = new ExcelPackage(new FileInfo(savedFilePath)); ExcelWorksheet workSheet = ep.Workbook.Worksheets.FirstOrDefault(); var table = ep.Workbook.Worksheets.FirstOrDefault().Tables.FirstOrDefault(); var columnsDictionary = ExcelServices.GetColumnRagngeInLeters(table, 4, 3); columnsDictionary.TryGetValue("outputsIndex", out var outputsIndex); columnsDictionary.TryGetValue("inputsIndex", out var inputsIndex); var outputsRulesDictionary = new ExcelServices().GetRulesFromExcel(workSheet, outputsIndex, true); var inputsRulesDictionary = new ExcelServices().GetRulesFromExcel(workSheet, inputsIndex, true); var annotationRulesDictionary = new ExcelServices().GetAnnotationsRulesFromExcel(workSheet, inputsIndex, outputsIndex, true); var outputsRulesTypes = new ExcelServices().GetRulesTypes(workSheet, outputsIndex, true); var inputsRulesTypes = new ExcelServices().GetRulesTypes(workSheet, inputsIndex, true); var inputsDictionary = new ExcelServices().GetExcelHeaderName(workSheet, inputsIndex, true); var outputsDictionary = new ExcelServices().GetExcelHeaderName(workSheet, outputsIndex, true); var dmnInfo = new ExcelServices().GetDmnInfo(workSheet).FirstOrDefault(); var dmnName = dmnInfo.Value; var dmnId = dmnInfo.Key; var newDmn = new DmnV1Builder() .AddDefinitionsInfo("Excel2Dmn_" + DateTime.Now.ToString("dd-mm-yy"), name) .AddDecision(dmnId, dmnName, "decisionTable") .AddInputsToDecisionTable(inputsDictionary, inputsRulesTypes) .AddOutputsToDecisionTable(outputsDictionary, outputsRulesTypes) .AddDecisionRules(inputsRulesDictionary, outputsRulesDictionary, annotationRulesDictionary) .Build(); var dmnFile = string.Concat(@"c:\temp\", name, "_", ".dmn"); XmlSerializer xs = new XmlSerializer(typeof(tDefinitions)); TextWriter tw = new StreamWriter(dmnFile); xs.Serialize(tw, newDmn); File.Exists(dmnFile).Should().BeTrue(); }
public void createMonthYearSummaryExcel() { ExcelWorksheet workSheet; ExcelWorksheet workSheet2; using (Stream AccountMovmentStream = GetAssemblyFile("Transactions.xlsx")) { workSheet = ExcelServices.GetExcelWorksheet(AccountMovmentStream, "Felles"); } using (Stream SubCategoriesStream = GetAssemblyFile("Categories.xlsx")) { workSheet2 = ExcelServices.GetExcelWorksheet(SubCategoriesStream); } var subCategoriesjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet2)); var accountMovmentjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet)); List <SubCategory> categorisModel = ModelClassServices.GetSubCategoriesFromJarray(subCategoriesjArray); IEnumerable <string> categoryList = categorisModel.Select(cat => cat.Category).Distinct(); List <AccountMovement> accountMovements = ModelClassServices.GetAccountMovmentsFromJarray(accountMovmentjArray); var modementsViewModels = ModelClassServices.CreateMovementsViewModels(accountMovements, categorisModel, "Felles"); var excelPkg = new ExcelPackage(); try { //Add excel sheet ExcelWorksheet wsSheet = excelPkg.Workbook.Worksheets.Add("MonthResume"); //Add Table Title ExcelServices.AddSheetHeading(wsSheet, "TableName"); // Add "input" and "output" headet to Excel table //ExcelServices.AddTableHeadings(wsSheet, new[] { "col1", "col2", "col3" }, subCategoriesjArray.Count+ accountMovmentjArray.Count); //Add transactions to excel Sheet ExcelServices.CreateExcelMonthSummaryTableFromMovementsViewModel(modementsViewModels, wsSheet, categoryList, 0, null, true); //ExcelServices.CreateExcelTableFromMovementsViewModel(modementsViewModels, wsSheet, "TableName", categoryList); } catch (Exception e) { var noko = e.Message; } var filename = "MonthResumeTests"; var path = string.Concat(@"h:\temp\"); Directory.CreateDirectory(path); var filePath = Path.Combine(path, string.Concat(filename, ".xlsx")); excelPkg?.SaveAs(new FileInfo(filePath)); File.Exists(filePath).Should().BeTrue(); }
public static Tuple <HyperaRunningNota, HyperaRunningRetorno> ReadHyperaRunning(string path) { if (string.IsNullOrEmpty(path)) { throw new ArgumentException("Invalid path."); } var hyperaRunningNota = new HyperaRunningNota(); var hyperaRunningRetorno = new HyperaRunningRetorno(); var file = Directory.GetFiles(path).ToList().FirstOrDefault(); var listData = new List <string>(); listData.AddRange(ExcelServices.CreateLines(file)); if (!listData.Any()) { return(new Tuple <HyperaRunningNota, HyperaRunningRetorno>(new HyperaRunningNota { QuantidadePendencias = "-", DataPrimeiraPendencia = "-" }, new HyperaRunningRetorno { QuantidadePendencias = "-", DataPrimeiraPendencia = "-" })); } var listaDataRetorno = new List <DateTime>(); var listaDataNota = new List <DateTime>(); foreach (var linha in listData) { var arrayDados = linha.Split(";"); if (arrayDados[0].ToUpper().Contains("ESPELHO")) { listaDataNota.Add(DateTime.Parse(arrayDados[7]).Date); } if (arrayDados[0].ToUpper().Contains("RETORNO")) { listaDataRetorno.Add(DateTime.Parse(arrayDados[7]).Date); } } hyperaRunningNota.QuantidadePendencias = listaDataNota.Count.ToString(); hyperaRunningRetorno.QuantidadePendencias = listaDataRetorno.Count.ToString(); hyperaRunningNota.DataPrimeiraPendencia = listaDataNota.Min().Date.ToString(Execute.CulturaPtBr); hyperaRunningRetorno.DataPrimeiraPendencia = listaDataRetorno.Min().Date.ToString(Execute.CulturaPtBr); return(new Tuple <HyperaRunningNota, HyperaRunningRetorno>(hyperaRunningNota, hyperaRunningRetorno)); }
public FileResult Exportar([FromForm] ReportesViewModel rep) { var qResult = ReportesServices.ObtenerReporteXFechas(_context, rep.fechaFin, rep.fechaFin); string sFileName = @"ReportePublicacionesUD.xlsx"; var dt = ExcelServices.ToDataTable <Reportes>(qResult); byte[] reportDocument = ExcelServices.RenderDataTableToExcel(dt).ToArray(); return(File(reportDocument, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName)); //string sWebRootFolder = _hostingEnvironment.WebRootPath; //FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); }
public void Test1() { var resourcePath = "dmnClient.Test.TestData.DataDictionaryFromModels.xlsx"; var assembly = Assembly.GetExecutingAssembly(); ExcelPackage ep; var name = string.Empty; using (Stream resourceAsStream = assembly.GetManifestResourceStream(resourcePath)) { ep = new ExcelPackage(resourceAsStream); } var jsonList = new Dictionary <string, string>(); var dmnTEK = new ExcelServices().ExcelToJsonObject(ep, "DmnTEK"); if (!string.IsNullOrEmpty(dmnTEK)) { jsonList.Add("JsonDmn2TEK", dmnTEK); } var variables = new ExcelServices().ExcelToJsonObject(ep, "Variables"); if (!string.IsNullOrEmpty(variables)) { jsonList.Add("JsonDmnVariablesNames", variables); } var dmnPlusVariables = new ExcelServices().ExcelToJsonObject(ep, "Dmn+Variables"); if (!string.IsNullOrEmpty(dmnPlusVariables)) { jsonList.Add("JsonTable2Variables", dmnPlusVariables); } foreach (var json in jsonList) { var jsonFile = string.Concat(@"c:\temp\", json.Key, ".json"); File.WriteAllText(jsonFile, json.Value, Encoding.UTF8); File.Exists(jsonFile).Should().BeTrue(); } //var dmnFile = string.Concat(@"c:\temp\", name, "_", ".dmn"); //XmlSerializer xs = new XmlSerializer(typeof(tDefinitions)); //TextWriter tw = new StreamWriter(dmnFile); //xs.Serialize(tw, newDmn); //File.Exists(dmnFile).Should().BeTrue(); }
public void Test1() { var file = "dmnTest1.dmn"; string ifcDataFile = Path.Combine(Directory.GetCurrentDirectory() + @"..\..\..\..\TestData\", file); tDefinitions dmn; using (Stream dmnStream = File.Open(ifcDataFile, FileMode.Open)) { dmn = new DmnServices().DeserializeStreamDmnFile(dmnStream); } var Items = dmn.Items; var decision = Items.Where(t => t.GetType() == typeof(tDecision)); var excelPkg = new ExcelPackage(); foreach (var tdecision in decision) { tDecisionTable decisionTable = null; try { var dt = ((tDecision)tdecision).Item; decisionTable = (tDecisionTable)Convert.ChangeType(dt, typeof(tDecisionTable)); ExcelWorksheet wsSheet = excelPkg.Workbook.Worksheets.Add(tdecision.id); //Add Table Title ExcelServices.AddTableTitle(tdecision.name, wsSheet, decisionTable, tdecision.id); // Add "input" and "output" headet to Excel table ExcelServices.AddTableInputOutputTitle(wsSheet, decisionTable); //Add DMN Table to excel Sheet ExcelServices.CreateExcelTableFromDecisionTable(decisionTable, wsSheet, tdecision.id); } catch { // } } var filename = Path.GetFileNameWithoutExtension(ifcDataFile); var path = string.Concat(@"c:\temp\"); Directory.CreateDirectory(path); var filePath = Path.Combine(path, string.Concat(filename, ".xlsx")); excelPkg?.SaveAs(new FileInfo(filePath)); File.Exists(filePath).Should().BeTrue(); }
public void createMovementsExcel() { Stream SubCategoriesStream = GetAssemblyFile("Categories.xlsx"); Stream AccountMovmentStream = GetAssemblyFile("Transactions.xlsx"); ExcelWorksheet workSheet = ExcelServices.GetExcelWorksheet(AccountMovmentStream, "Felles"); ExcelWorksheet workSheet2 = ExcelServices.GetExcelWorksheet(SubCategoriesStream); var subCategoriesjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet2)); var accountMovmentjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet)); List <SubCategory> subCategories = ModelClassServices.GetSubCategoriesFromJarray(subCategoriesjArray); List <AccountMovement> accountMovements = ModelClassServices.GetAccountMovmentsFromJarray(accountMovmentjArray); var modementsViewModels = ModelClassServices.CreateMovementsViewModels(accountMovements, subCategories, "Felles"); var excelPkg = new ExcelPackage(); try { ExcelWorksheet wsSheet = excelPkg.Workbook.Worksheets.Add("fellesResum"); //Add Table Title new ExcelServices(); ExcelServices.AddSheetHeading(wsSheet, "TableName"); // Add "input" and "output" headet to Excel table //ExcelServices.AddTableHeadings(wsSheet, new[] { "col1", "col2", "col3" }, subCategoriesjArray.Count+ accountMovmentjArray.Count); //Add DMN Table to excel Sheet ExcelServices.CreateExcelTableFromMovementsViewModel(modementsViewModels, wsSheet, "TableName"); } catch (Exception e) { var noko = e.Message; } var filename = "MovementsTests"; var path = string.Concat(@"h:\temp\"); Directory.CreateDirectory(path); var filePath = Path.Combine(path, string.Concat(filename, ".xlsx")); excelPkg?.SaveAs(new FileInfo(filePath)); File.Exists(filePath).Should().BeTrue(); }
public void CreateExcelTableFromMovementsViewModelTest() { var jsonArray = TestsHelper.GetJonsArrayFromFile("TransactionsArray.json"); List <AccountMovement> movementsModels = ModelConverter.GetAccountMovmentsFromJarray(jsonArray); movementsModels.Count.Should().Be(122); jsonArray = TestsHelper.GetJonsArrayFromFile("CategoriesArray.json"); List <SubCategory> categorisModel = ModelConverter.GetCategoriesFromJarray(jsonArray); categorisModel.Count.Should().Be(105); IEnumerable <string> categoryList = categorisModel.Select(cat => cat.Category).Distinct(); var movementsViewModels = ModelConverter.CreateMovementsViewModels(movementsModels, categorisModel, "Felles"); movementsViewModels[0].Category.Should().BeEquivalentTo("Altibox"); ExcelWorksheet wsSheet; IEnumerable <string> categories; ExcelPackage excelPackage = new ExcelPackage(); using (var stream = new MemoryStream()) using (var package = new ExcelPackage(stream)) { package.Workbook.Worksheets.Add("Sheet1"); wsSheet = package.Workbook.Worksheets["Sheet1"]; ExcelServices.CreateExcelTableFromMovementsViewModel(movementsViewModels, wsSheet, "Transactions"); //now test that it generated properly, such as: var sheeDefault = package.Workbook.Worksheets.FirstOrDefault(); if (sheeDefault != null) { sheeDefault.Cells["D57"].Value.Should().Be(-55); } var saveExcel = TestsHelper.SaveExcrlPackage(package, "test-Temp"); saveExcel.Should().BeTrue(); } }
public void ImportData() { var fileUrl = @"D:\OneDrive\桌面\excels\离港旅客名单数据导入模板0424(新).xlsx"; var errorMsg = new StringBuilder(); // 错误信息 var dic = new Dictionary <string, string>() { { "PassengerName", "姓名" }, { "PassengerEngName", "英文名" }, { "PassengerType", "旅客类型" }, { "Sex", "性别" }, { "Telphone", "联系电话" }, { "CertType", "证件类型" }, { "CertNumber", "证件号" }, { "Class", "舱位" }, { "TicketNumber", "票号" } }; var list = ExcelServices.ExcelToList <ImportTestModel>(dic, fileUrl, out errorMsg); Console.WriteLine(JsonConvert.SerializeObject(list)); }
public static Tuple <BiolabNota, BiolabRetorno> ReadBiolab(string path) { if (string.IsNullOrEmpty(path)) { throw new ArgumentException("Invalid path."); } var config = new MapperConfiguration(cfg => { cfg.CreateMap <BaseDomain, BiolabNota>(); cfg.CreateMap <BaseDomain, BiolabRetorno>(); }); var mapper = config.CreateMapper(); var file = Directory.GetFiles(path).FirstOrDefault(); var biolabNota = mapper.Map <BiolabNota>(ExcelServices.ReadData(file, DateColumnLetter, "Nota")); var biolabRetorno = mapper.Map <BiolabRetorno>(ExcelServices.ReadData(file, DateColumnLetter, "Retorno")); return(new Tuple <BiolabNota, BiolabRetorno>(biolabNota, biolabRetorno)); }
public void CreateAverageForYearMonthDayTest() { IEnumerable <string> categoryList = TestsHelper.GetCategoryList(); var movementsViewModels = TestsHelper.GetMovementsViewModels(); using (var stream = new MemoryStream()) using (var package = new ExcelPackage(stream)) { package.Workbook.Worksheets.Add("Sheet1"); var categoriesAverageWSheet = package.Workbook.Worksheets["Sheet1"]; ExcelServices.CreateAverageForYearMonthDay(movementsViewModels, categoriesAverageWSheet, categoryList, 0, 0, true); //now test that it generated properly, such as: //var sheeDefault = package.Workbook.Worksheets.FirstOrDefault(); //if (sheeDefault != null) sheeDefault.Cells["D57"].Value.Should().Be(-55); var saveExcel = TestsHelper.SaveExcrlPackage(package, "CreateAverageForYearMonthDay-test"); saveExcel.Should().BeTrue(); } }
public void GetSubCategoryTest() { JArray JsonmodementsViewModels; Encoding encoding = Encoding.GetEncoding(28591); using (StreamReader stream = new StreamReader(GetAssemblyFile("TransactionViewModelArray.json"), encoding, true)) { JsonmodementsViewModels = JArray.Parse(stream.ReadToEnd()); } Stream SubCategoriesStream = GetAssemblyFile("Categories.xlsx"); Stream AccountMovmentStream = GetAssemblyFile("Transactions.xlsx"); ExcelWorksheet workSheet = ExcelServices.GetExcelWorksheet(AccountMovmentStream, "Felles"); ExcelWorksheet workSheet2 = ExcelServices.GetExcelWorksheet(SubCategoriesStream); var subCategoriesjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet2)); var accountMovmentjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet)); List <SubCategory> subCategories = ModelClassServices.GetSubCategoriesFromJarray(subCategoriesjArray); var Modelviews = new ModelViewBuilder().AddTextToMovemnt(new List <string> { "arg tur", "arg tur", "argentina tur", "tur argentina", "argentina tur" }); var movementsViewModels = new List <MovementsViewModel>(); foreach (var item in JsonmodementsViewModels) { movementsViewModels.Add(new ModelClassServices().JsonToMovementsViewModels(item)); } List <MovementsViewModel> newList = new List <MovementsViewModel>(); foreach (var movment in Modelviews) { newList.Add(ModelClassServices.UpdateMovementViewModelWithSubCategory(subCategories, movment)); } newList.All(mv => !string.IsNullOrEmpty(mv.Category)).Should().BeTrue(); }
/// <summary> /// /// </summary> /// <param name="year"></param> /// <param name="transactionUpdatePackage"></param> /// <param name="movementsViewModels"></param> /// <param name="categoriesArray"></param> private static void AddCategoriesAverage(int year, ExcelPackage transactionUpdatePackage, List <MovementsViewModel> movementsViewModels, string[] categoriesArray) { ExcelWorksheet categoriesAverageWSheet = transactionUpdatePackage.Workbook.Worksheets["Categories Average"]; if (categoriesAverageWSheet != null) { //ExcelHelpers.DeldeteExcelTablesFromWorkSheet(categoriesAverageWSheet); transactionUpdatePackage.Workbook.Worksheets.Delete("Categories Average"); categoriesAverageWSheet = transactionUpdatePackage.Workbook.Worksheets.Add("Categories Average new"); } else { categoriesAverageWSheet = transactionUpdatePackage.Workbook.Worksheets.Add("Categories Average"); } ExcelServices.AddSheetHeading(categoriesAverageWSheet, "Transactions and Categories"); var yearMonthTable = ExcelServices.CreateAverageForYearMonthDay(movementsViewModels, categoriesAverageWSheet, categoriesArray, year, 0, true); var endTableRow = yearMonthTable.Address.End.Row; var categoryMonthAvgTable = ExcelServices.CreateCategoriesMonthsAveragetest(categoriesAverageWSheet, endTableRow, movementsViewModels, categoriesArray, year, true); }
static void Main(string[] args) { DirectoryInfo diretorio = new DirectoryInfo(@"Input"); FileInfo[] arquivos = diretorio.GetFiles("*", SearchOption.AllDirectories); ArquivoServices arquivoServices = new ArquivoServices(); arquivoServices.Listar(arquivos); Console.ReadKey(); Task[] tasks = new Task[arquivos.Length]; for (int i = 0; i < arquivos.Length; i++) { ExcelServices excel = new ExcelServices(); tasks[i] = new Task(() => excel.Exportar(typeof(Cotacao), arquivos[i].Name)); tasks[i].Start(); tasks[i].Wait(); } }
public IActionResult PostModelsToExcelDataDictionary() { var httpRequest = HttpContext.Request; HttpResponseMessage response = null; string okResponsText = null; var httpFiles = httpRequest.Form.Files; var httpForms = httpRequest.Form.Keys; var okDictionary = new Dictionary <string, string>(); var ErrorDictionary = new Dictionary <string, string>(); var dmnDataDictionaryModels = new List <DmnDataDictionaryModel>(); var bpmnDataDictionaryModels = new List <BpmnDataDictionaryModel>(); var dataDictionaryModels = new List <DataDictionaryModel>(); if (httpFiles == null && !httpFiles.Any()) { return(NotFound("Can't find any file")); } for (var i = 0; i < httpFiles.Count; i++) { string errorResponsText = null; string errorTemp = string.Empty; var file = httpFiles[i]; tDefinitions dmn = null; var fileExtention = Path.GetExtension(file.FileName); if (fileExtention == ".dmn") { //Deserialize DMN file if (file != null) { using (Stream dmnfile = httpFiles[i].OpenReadStream()) { dmn = new DmnServices().DeserializeStreamDmnFile(dmnfile); } } if (dmn == null) { ErrorDictionary.Add(file.FileName, "Can't validate Shema"); continue; } // check if DMN have desicion table var items = dmn.Items; var decision = items.Where(t => t.GetType() == typeof(tDecision)); var tDrgElements = decision as tDRGElement[] ?? decision.ToArray(); if (!tDrgElements.Any()) { ErrorDictionary.Add(file.FileName, "Dmn file have non decision"); continue; } foreach (tDecision tdecision in decision) { tDecisionTable decisionTable = null; try { DmnServices.GetDecisionsVariables(tdecision, Path.GetFileNameWithoutExtension(file.FileName), ref dmnDataDictionaryModels); } catch { ErrorDictionary.Add(file.FileName, "Can't add serialize info from DMN"); } } } if (fileExtention == ".bpmn") { XDocument bpmnXml = null; try { using (Stream dmnfile = httpFiles[i].OpenReadStream()) { bpmnXml = XDocument.Load(dmnfile); } } catch { ErrorDictionary.Add(file.FileName, "Can't add serialize bpmn to xml"); } if (bpmnXml != null) { try { DmnServices.GetDmnInfoFromBpmnModel(bpmnXml, ref bpmnDataDictionaryModels); } catch { ErrorDictionary.Add(file.FileName, "Can't add serialize bpmn to Data Model Dictionary"); } } } } foreach (var dmnDataInfo in dmnDataDictionaryModels) { var submodel = new BpmnDataDictionaryModel(); try { submodel = bpmnDataDictionaryModels.Single(b => b.DmnId == dmnDataInfo.DmnId); } catch { } dataDictionaryModels.Add(new DataDictionaryModel() { BpmnData = submodel, DmnData = dmnDataInfo }); } // create Excel Package ExcelPackage excelPkg = null; var fileName = "DataDictionaryFromModels"; try { excelPkg = new ExcelPackage(); ExcelWorksheet wsSheet = excelPkg.Workbook.Worksheets.Add("DmnTEK"); var dmnIds = dmnDataDictionaryModels.GroupBy(x => x.DmnId).Select(y => y.First()); var objectPropertyNames = new[] { "DmnId", "DmnNavn", "TekKapitel", "TekLedd", "TekTabell", "TekForskriften", "TekWebLink" }; ExcelServices.CreateDmnExcelTableDataDictionary(dmnIds, wsSheet, "dmnTek", objectPropertyNames); ExcelWorksheet wsSheet1 = excelPkg.Workbook.Worksheets.Add("Variables"); var dmnVariablesIds = dmnDataDictionaryModels.GroupBy(x => x.VariabelId).Select(y => y.First()); var dmnVariablesIdstPropertyNames = new[] { "VariabelId", "VariabelNavn", "VariabelBeskrivelse" }; ExcelServices.CreateDmnExcelTableDataDictionary(dmnVariablesIds, wsSheet1, "Variables", dmnVariablesIdstPropertyNames); ExcelWorksheet wsSheet2 = excelPkg.Workbook.Worksheets.Add("Dmn+Variables"); var objectPropertyNames1 = new[] { "DmnId", "VariabelId", "Type" }; ExcelServices.CreateDmnExcelTableDataDictionary(dmnDataDictionaryModels, wsSheet2, "Dmn+Variables", objectPropertyNames1); ExcelWorksheet wsSheet3 = excelPkg.Workbook.Worksheets.Add("summary"); var summaryPropertyNames = new[] { "DmnData.FilNavn", "BpmnData.BpmnId", "DmnData.DmnId", "DmnData.VariabelId", "DmnData.VariabelType", "DmnData.Type", "DmnData.Kilde" }; ExcelServices.CreateSummaryExcelTableDataDictionary(dataDictionaryModels, wsSheet3, "summary", summaryPropertyNames); } catch { ErrorDictionary.Add("Error", "Can't create Excel file"); } // Save Excel Package try { var path = Path.Combine(@"C:\", "DmnToExcel"); Directory.CreateDirectory(path); excelPkg.SaveAs(new FileInfo(Path.Combine(path, string.Concat(fileName, ".xlsx")))); okDictionary.Add(fileName, "Created in:" + path); } catch { ErrorDictionary.Add(fileName, "Can't be saved"); } if (ErrorDictionary.Any()) { if (okDictionary.Any()) { List <Dictionary <string, string> > dictionaries = new List <Dictionary <string, string> >(); dictionaries.Add(okDictionary); dictionaries.Add(ErrorDictionary); var result = dictionaries.SelectMany(dict => dict) .ToLookup(pair => pair.Key, pair => pair.Value) .ToDictionary(group => group.Key, group => group.First()); return(Ok(result)); } return(BadRequest(ErrorDictionary)); } return(Ok(okDictionary)); }
private void UpdateBudgetCashFlow(ExcelPackage excelPackage, List <MovementsViewModel> movementsViewModels, List <string> categoriesArray, int year) { ExcelTable yearBudgetTable = null; ExcelTable yearExpensesTable = null; if (year == 0) { year = DateTime.Today.Year; } // Create Cashflow var expensesWSheet = excelPackage.Workbook.Worksheets["Expenses details"]; // add year categoiers Table try { var yearExpensesTables = ExcelServices.CreateExcelMonthSummaryTableFromMovementsViewModel(expensesWSheet, movementsViewModels, categoriesArray, year, "YearExpenses", true, "B38"); yearExpensesTable = yearExpensesTables.FirstOrDefault(); } catch (Exception e) { throw new Exception("Creating Year expensesTable Sheet. Error message : " + e.Message); } // add year incoms categoiers try { var yearIncomsTables = ExcelServices.CreateExcelMonthSummaryTableFromMovementsViewModel(expensesWSheet, movementsViewModels, categoriesArray, year, "YearIncoms", false, "B54"); } catch (Exception e) { throw new Exception("Problem Creating Year IncomsTable Sheet. Error message : " + e.Message); } // update Year table Dictionary <string, string> categoriesAddressWithTotals = null; Dictionary <string, string> categoriesAddress = null; //Add sub total and total to list to get them from budget table try { var categoryListWithTotals = Helpers.AddItemsToIenumeration(categoriesArray, new List <string>() { "Sub Total", "Total" }); yearBudgetTable = expensesWSheet.Tables["Year_budget"]; var listWithTotals = categoryListWithTotals as string[] ?? categoryListWithTotals.ToArray(); if (yearBudgetTable != null) { categoriesAddressWithTotals = ExcelHelpers.GetNamesAdress(listWithTotals, yearBudgetTable); } //Get address to expenses table categoriesAddress = ExcelServices.GetColumnsNameAdress(listWithTotals, yearExpensesTable); } catch (Exception e) { throw new Exception("Cant get Info from table from 'Expenses details' sheet. Error message : " + e.Message); } //Update year excel table try { var yearWSheet = excelPackage.Workbook.Worksheets["Year summary"]; var tblOperatingExpensesTable = yearWSheet.Tables["tblOperatingExpenses"]; string keyCellValue = null; if (categoriesAddressWithTotals != null) { if (categoriesAddressWithTotals.TryGetValue("Total", out keyCellValue)) { ExcelServices.UpdateTableValues(tblOperatingExpensesTable, "BUDGET", keyCellValue); } } if (categoriesAddress != null) { if (categoriesAddress.TryGetValue("Total", out keyCellValue)) { ExcelServices.UpdateTableValues(tblOperatingExpensesTable, "ACTUAL", keyCellValue); } } } catch (Exception e) { throw new Exception("Cant tables in 'Year summary' Table values. Error message : " + e.Message); } Dictionary <string, string> monthBudgetCategoriesAddress = null; Dictionary <string, string> monthExpensesCategoriesAddress = null; try { // get address to Month budget table var categoriesWithoutIncome = Helpers.DeleteItemsfromIenumeration(categoriesArray, new List <string>() { "Åse", "Matias" }); monthBudgetCategoriesAddress = ExcelServices.GetColumnsNameAdress(categoriesWithoutIncome, yearBudgetTable); monthExpensesCategoriesAddress = ExcelServices.GetColumnsNameAdress(categoriesWithoutIncome, yearExpensesTable); } catch (Exception e) { throw new Exception("Cant get tables info from 'Expenses details' sheet to update Class table. Error message : " + e.Message); } //update month Table with the categories summary try { var monthWSheet = excelPackage.Workbook.Worksheets["Monthly summary"]; var tblOperatingExpenses7Table = monthWSheet.Tables["tblOperatingExpenses7"]; ExcelServices.UpdateClassesTableValues(monthBudgetCategoriesAddress, monthExpensesCategoriesAddress, tblOperatingExpenses7Table); } catch (Exception e) { throw new Exception("Cant update tblOperatingExpenses7 in 'Monthly summary' sheet. Error message : " + e.Message); } //return excelPackage; }
public async Task <IActionResult> Post(IFormFile transactions, IFormFile categories, int year = 0) { var filePathTemp = Path.GetTempFileName(); var filePath1 = Path.GetTempFileName(); var responseDictionary = new Dictionary <string, string>(); if (!IsFileValid(transactions) || !IsFileValid(categories)) { return(BadRequest()); } ExcelWorksheet transactionsWorkSheet; ExcelWorksheet categoriesWorkSheet; //Read Excel Files try { transactionsWorkSheet = await ExcelHelpers.GetExcelWorkSheet(transactions, filePathTemp); categoriesWorkSheet = await ExcelHelpers.GetExcelWorkSheet(categories, filePath1); } catch (Exception ex) { return(BadRequest("Can't read excel files")); } var transactionsTable = transactionsWorkSheet.Tables.FirstOrDefault(); var categoriestabTable = categoriesWorkSheet.Tables.FirstOrDefault(); //Get excel data in Json format easier to serialize to class var accountMovmentjArray = ExcelConverter.GetJsonFromTable(transactionsTable); var subCategoriesjArray = ExcelConverter.GetJsonFromTable(categoriestabTable); // serialize Json to Class List <AccountMovement> accountMovements = ModelConverter.GetAccountMovmentsFromJarray(accountMovmentjArray); List <SubCategory> categorisModel = ModelConverter.GetCategoriesFromJarray(subCategoriesjArray); IEnumerable <string> categoryList = categorisModel.Select(cat => cat.Category).Distinct(); //TODO Get acount Name from Excel or Input variable var movementsViewModels = ModelConverter.CreateMovementsViewModels(accountMovements, categorisModel, "Felles"); //Create excel Sheet with the transaction updated with the keewords, categories, and subproject (is exists) var categoriesArray = categoryList as string[] ?? categoryList.ToArray(); using (var stream = new MemoryStream()) using (var transactionUpdatePackage = new ExcelPackage(stream)) { try { //Add Table Title var transactionSheet = transactionUpdatePackage.Workbook.Worksheets.Add("Transactions"); ExcelServices.AddSheetHeading(transactionSheet, "Transactions and Categories"); //Add transactions to excel Sheet var movementsViewExcelTable = ExcelServices.CreateExcelTableFromMovementsViewModel(movementsViewModels, transactionSheet, "Transactions"); } catch (Exception e) { return(BadRequest("Error Creating transaction sheet. Error message : " + e.Message)); } // Add Categories Average to excel try { AddCategoriesAverage(year, transactionUpdatePackage, movementsViewModels, categoriesArray); } catch (Exception e) { return(BadRequest(" Error Creating Average sheet. Error message : " + e.Message)); } // add month summaries to excel try { var monthSummariesSheet = transactionUpdatePackage.Workbook.Worksheets.Add("MonthSummaries"); ExcelServices.CreateExcelMonthSummaryTableFromMovementsViewModel(monthSummariesSheet, movementsViewModels, categoriesArray, 0, null, true); } catch (Exception e) { return(BadRequest("Creating MonthSummary Sheet. Error message : " + e.Message)); } try { var filename = "Transactions Update With Categories"; string contentRootPath = _hostingEnvironment.ContentRootPath; var fullPath = Path.Combine(contentRootPath, "DataTemp", $"{filename}.xlsx"); transactionUpdatePackage.SaveAs(new FileInfo(fullPath)); } catch (Exception e) { return(BadRequest("Transactions Update With Categories Can't be saved :" + e.Message)); } } //Next Excel File More details and cashflow + Chars using (var cashflowExcelPkg = new ExcelPackage(GetAssemblyFile("Budget Cashflow.xlsx"))) { try { UpdateBudgetCashFlow(cashflowExcelPkg, movementsViewModels, categoriesArray.ToList(), year); } catch (Exception) { return(BadRequest("Cant creat Cashflow Excel File")); } // Save Excel Package try { var filename = $"Budget Cashflow ({year})"; string contentRootPath = _hostingEnvironment.ContentRootPath; var fullPath = Path.Combine(contentRootPath, "DataTemp", $"{filename}.xlsx"); cashflowExcelPkg.SaveAs(new FileInfo(fullPath)); } catch { return(BadRequest("Can't be saved")); } } return(Ok(responseDictionary)); }
public async Task <IActionResult> PostTransaction(IFormFile transactions, int year = 0) { var filePathTemp = Path.GetTempFileName(); if (!IsFileValid(transactions)) { return(BadRequest("Can't read transactions excel file")); } using (var stream = new FileStream(filePathTemp, FileMode.Create)) { await transactions.CopyToAsync(stream); List <MovementsViewModel> movementsViewModels = null; List <string> categoryList; using (var transacationAndCategories = new ExcelPackage(stream)) { JArray jsonFromTable = null; var worksheet = transacationAndCategories.Workbook.Worksheets.FirstOrDefault(); if (worksheet != null) { var excelTable = worksheet.Tables.FirstOrDefault(); jsonFromTable = ExcelConverter.GetJsonFromTable(excelTable); } if (jsonFromTable != null) { movementsViewModels = jsonFromTable.ToObject <List <MovementsViewModel> >(); //movementsViewModels = JsonConvert.DeserializeObject<List<MovementsViewModel>>(jsonFromTable?.ToString(), JsonServices.GetJsonSerializerSettings()); } categoryList = ModelConverter.GetListOfCategories(movementsViewModels); // Add Categories Average to excel try { AddCategoriesAverage(year, transacationAndCategories, movementsViewModels, categoryList.ToArray()); } catch (Exception e) { return(BadRequest(" Error Creating Average sheet. Error message : " + e.Message)); } // add month summaries to excel try { ExcelWorksheet monthSummariesSheet = transacationAndCategories.Workbook.Worksheets["MonthSummaries"]; if (monthSummariesSheet != null) { transacationAndCategories.Workbook.Worksheets.Delete("MonthSummaries"); monthSummariesSheet = transacationAndCategories.Workbook.Worksheets.Add("Month Summaries New"); } else { monthSummariesSheet = transacationAndCategories.Workbook.Worksheets.Add("MonthSummaries"); } ExcelServices.CreateExcelMonthSummaryTableFromMovementsViewModel(monthSummariesSheet, movementsViewModels, categoryList.ToArray(), 0, null, true); } catch (Exception e) { return(BadRequest("Creating MonthSummary Sheet. Error message : " + e.Message)); } try { var filename = "Transactions Update With Categories (1)"; string contentRootPath = _hostingEnvironment.ContentRootPath; var fullPath = Path.Combine(contentRootPath, "DataTemp", $"{filename}.xlsx"); transacationAndCategories.SaveAs(new FileInfo(fullPath)); } catch (Exception e) { return(BadRequest("Transactions Update With Categories Can't be saved :" + e.Message)); } } using (var cashflowExcelPkg = new ExcelPackage(GetAssemblyFile("Budget Cashflow.xlsx"))) { try { UpdateBudgetCashFlow(cashflowExcelPkg, movementsViewModels, categoryList, year); } catch (Exception) { return(BadRequest("Cant creat Cashflow Excel File")); } // Save Excel Package try { var filename = "Budget Cashflow (1)"; string contentRootPath = _hostingEnvironment.ContentRootPath; var fullPath = Path.Combine(contentRootPath, "DataTemp", $"{filename}.xlsx"); cashflowExcelPkg.SaveAs(new FileInfo(fullPath)); return(Ok("File " + filename + "cretated in :" + fullPath)); } catch (Exception e) { return(BadRequest("Can't be saved :" + e.Message)); } } } }
public static async Task RunTask() { var init = DateTime.Now; var folders = Directory.GetDirectories(Path); if (folders.Length != 8) { throw new ArgumentException("Invalid number of folders, 8 folders are required"); } Tuple <AcheNota, AcheRetorno> ache = null; Tuple <BiolabNota, BiolabRetorno> biolab = null; Tuple <SandozNota, SandozRetorno> sandoz = null; Tuple <SanofiNota, SanofiRetorno> sanofi = null; Tuple <BoehringerNota, BoehringerRetorno> boehringer = null; Tuple <AstrazenecaNota, AstrazenecaRetorno> astrazeneca = null; Tuple <HyperaPharmaNota, HyperaPharmaRetorno> hyperaPharma = null; Tuple <HyperaRunningNota, HyperaRunningRetorno> hyperaRunning = null; foreach (var folder in folders) { if (folder.Contains("sandoz")) { sandoz = Sandoz.ReadSandoz(folder); continue; } if (folder.Contains("ache")) { ache = Ache.ReadAche(folder); continue; } if (folder.Contains("biolab")) { biolab = Biolab.ReadBiolab(folder); continue; } if (folder.Contains("astrazeneca")) { astrazeneca = Astrazeneca.ReadAztrazeneca(folder); continue; } if (folder.Contains("boehringer")) { boehringer = Boehringer.ReadBoehringer(folder); continue; } if (folder.Contains("hypera_plk")) { hyperaPharma = HyperaPharma.ReadHyperaPharma(folder); continue;; } if (folder.Contains("hypera_rng")) { hyperaRunning = HyperaRunning.ReadHyperaRunning(folder); continue; } if (folder.Contains("sanofi")) { sanofi = Sanofi.ReadSanofi(folder); } } ; ExcelServices.CreateControle(ache, biolab, sandoz, sanofi, boehringer, astrazeneca, hyperaPharma, hyperaRunning); var end = DateTime.Now; Console.WriteLine($"Inicio: {init}"); Console.WriteLine($"Fim: {end}"); Console.WriteLine($"Tempo: {end - init}"); }
public ActionResult <string> PostExcelToDmn(string inputs, string outputs, bool haveId) { var httpRequest = HttpContext.Request; var responsDictionary = new Dictionary <string, string>(); HttpResponseMessage response = null; if (httpRequest.Form.Files.Count != 1) { return(BadRequest(new Dictionary <string, string>() { { "Error:", "Not file fount" } })); } var file = httpRequest.Form.Files[0]; var file1 = httpRequest.Form.Files.FirstOrDefault(); ExcelPackage ep = null; ExcelWorksheet workSheet = null; ExcelTable table = null; if (file != null) { try { //Open Excel file using (Stream excelFile = file.OpenReadStream()) { ep = new ExcelPackage(excelFile); } workSheet = ep.Workbook.Worksheets.FirstOrDefault(); table = workSheet.Tables.FirstOrDefault(); } catch (Exception e) { return(BadRequest(new Dictionary <string, string>() { { "Error:", "Can't Open Excel File" } })); } if (table != null) { Dictionary <int, Dictionary <string, object> > outputsRulesFromExcel = null; Dictionary <int, Dictionary <string, object> > inputsRulsFromExcel = null; Dictionary <int, string> annotationsRulesDictionary = null; Dictionary <int, string> outputsRulesTypes = null; Dictionary <int, string> inputsRulesTypes = null; Dictionary <string, Dictionary <string, string> > inputsDictionary = null; Dictionary <string, Dictionary <string, string> > outputsDictionary = null; var dmnName = string.Empty; var dmnId = string.Empty; var columnsDictionary = GetTablesIndex(table, inputs, outputs); string[] outputsIndex = null; string[] inputsIndex = null; if (columnsDictionary != null) { columnsDictionary.TryGetValue("outputsIndex", out outputsIndex); columnsDictionary.TryGetValue("inputsIndex", out inputsIndex); } if (inputsIndex == null && outputsIndex == null) { return(BadRequest(new Dictionary <string, string>() { { "Error", "Can't get inputs/output rows" } })); } try { outputsRulesFromExcel = new ExcelServices().GetRulesFromExcel(workSheet, outputsIndex, haveId); inputsRulsFromExcel = new ExcelServices().GetRulesFromExcel(workSheet, inputsIndex, haveId); annotationsRulesDictionary = new ExcelServices().GetAnnotationsRulesFromExcel(workSheet, inputsIndex, outputsIndex, haveId); outputsRulesTypes = new ExcelServices().GetRulesTypes(workSheet, outputsIndex, haveId); inputsRulesTypes = new ExcelServices().GetRulesTypes(workSheet, inputsIndex, haveId); inputsDictionary = new ExcelServices().GetExcelHeaderName(workSheet, inputsIndex, haveId); outputsDictionary = new ExcelServices().GetExcelHeaderName(workSheet, outputsIndex, haveId); var dmnInfo = new ExcelServices().GetDmnInfo(workSheet).FirstOrDefault(); dmnName = dmnInfo.Value; dmnId = dmnInfo.Key; if (!outputsRulesFromExcel.Any() || !inputsRulsFromExcel.Any() || !outputsRulesTypes.Any() || !inputsRulesTypes.Any() || !inputsDictionary.Any() || !outputsDictionary.Any()) { return(BadRequest(new Dictionary <string, string>() { { "Error:", "Wrong information to create DMN from Excel" } })); } } catch (Exception) { return(BadRequest(new Dictionary <string, string>() { { "Error:", "Can't Get Excel info" } })); } var filename = Path.GetFileNameWithoutExtension(file.FileName); var newDmn = new DmnV1Builder() .AddDefinitionsInfo("Excel2Dmn_" + DateTime.Now.ToString("dd-mm-yy"), filename) .AddDecision(dmnId, dmnName, "decisionTable") .AddInputsToDecisionTable(inputsDictionary, inputsRulesTypes) .AddOutputsToDecisionTable(outputsDictionary, outputsRulesTypes) .AddDecisionRules(inputsRulsFromExcel, outputsRulesFromExcel, annotationsRulesDictionary) .Build(); // Save DMN try { var path = Path.Combine(@"C:\", "ExcelToDmn"); Directory.CreateDirectory(path); //var dmnFile = string.Concat(path, filename, "_Exc2Dmn", ".dmn"); XmlSerializer xs = new XmlSerializer(typeof(DecisionModelNotation.Shema.tDefinitions)); var combine = Path.Combine(path, string.Concat(filename, ".dmn")); using (TextWriter tw = new StreamWriter(combine)) { xs.Serialize(tw, newDmn); } return(Ok(new Dictionary <string, string>() { { filename + ".dmn", "Created" }, { "Path", combine } })); } catch (Exception e) { return(BadRequest(new Dictionary <string, string>() { { filename + ".dmn", "Can't be safe" } })); } } return(BadRequest(new Dictionary <string, string>() { { file.FileName, "Excel file don't have table" } })); } return(Ok(responsDictionary)); }