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));
}
