internal void addEIColumn(Excel.Worksheet WS, Excel.ListObject LO, string dataListName)
{
string formula = "";
string newname = rsc.GetString("buildingEnPIColName");
string tbl = LO.Name;
string bsqfcol = rsc.GetString("buildingSQFColName");
string colhat = rsc.GetString("totalAdjValuesColName");
string that; //adjusted current year energy consumption
string bsqf; //building square feet
that = tbl + "[[#This Row]," + ExcelHelpers.CreateValidFormulaName(colhat) + "]";
bsqf = tbl + "[[#This Row]," + ExcelHelpers.CreateValidFormulaName(bsqfcol) + "]";
formula = "=IFERROR(" + that + "/" + bsqf + ",0)";
if (bsqfcol != "")
{
Excel.ListColumn newCol = LO.ListColumns.Add(missing);
newCol.Name = newname;
newCol.DataBodyRange.Value2 = formula;
newCol.DataBodyRange.Style = "Comma";
}
}
public void LoginTest()
{
//Reading input data from Excel
string fileName = CommonHelpers.GetPath() + "TestData\\Login.xlsx";
ExcelHelpers.PopulateInCollection(fileName);
// Enter userid and Password, Click Login
Reporter.LogInfo("Login Page");
LoginPage loginPage = new LoginPage();
loginPage.Login(ExcelHelpers.ReadData(1, "UserName"), ExcelHelpers.ReadData(1, "Password"));
//Application navigates to Client Page
Reporter.LogInfo("Select Client Page ");
ClientSelectPage clientSelectpage = new ClientSelectPage();
WebDriver.ClickButton(clientSelectpage.linkClientName("[Ansira Test Client]"));
}
public void UpdateTableValuesBudgetTest()
{
Dictionary <string, string> CategoriesAddressWithTotals = 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["Year_budget"];
if (yearBudgetTable != null)
{
CategoriesAddressWithTotals = ExcelHelpers.GetNamesAdress(categoryListWithTotals, yearBudgetTable);
}
var yearWSheet = cashflowExcelPkg.Workbook.Worksheets["Year summary"];
var excelTable = yearWSheet.Tables["tblOperatingExpenses"];
string keyCellValue = null;
if (CategoriesAddressWithTotals != null)
{
if (CategoriesAddressWithTotals.TryGetValue("Total", out keyCellValue))
{
ExcelServices.UpdateTableValues(excelTable, "BUDGET", keyCellValue);
}
}
var saveExcel = TestsHelper.SaveExcrlPackage(cashflowExcelPkg, "Update-Test1");
saveExcel.Should().BeTrue();
}
}
public void Test1()
{
ExcelWorksheet workSheet;
ExcelWorksheet workSheet2;
using (Stream AccountMovmentStream = TestsHelper.GetAssemblyFile("Transactions.xlsx"))
{
workSheet = ExcelHelpers.GetExcelWorksheet(AccountMovmentStream, "Felles");
}
using (Stream SubCategoriesStream = TestsHelper.GetAssemblyFile("Categories.xlsx"))
{
workSheet2 = ExcelHelpers.GetExcelWorksheet(SubCategoriesStream);
}
var workSheet2Table = workSheet2.Tables.FirstOrDefault();
var workSheetTable = workSheet.Tables.FirstOrDefault();
var subCategoriesjArray = ExcelConverter.GetJsonFromTable(workSheet2Table);
var accountMovmentjArray = ExcelConverter.GetJsonFromTable(workSheetTable);
List <SubCategory> categorisModel = ModelConverter.GetCategoriesFromJarray(subCategoriesjArray);
IEnumerable <string> categoryList = categorisModel.Select(cat => cat.Category).Distinct();
List <AccountMovement> accountMovements = ModelConverter.GetAccountMovmentsFromJarray(accountMovmentjArray);
var modementsViewModels = ModelConverter.CreateMovementsViewModels(accountMovements, categorisModel, "Felles");
var jsonModementsViewModels = JArray.Parse(JsonConvert.SerializeObject(modementsViewModels));
jsonModementsViewModels.Should().NotBeNullOrEmpty();
// var filename = "Budget Cashflow Temp";
//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 WelcomePage()
{
string fileName = Environment.CurrentDirectory.ToString() + "\\Data\\Login.xlsx";
ExcelHelpers.PopulateInCollection(fileName, "ProjectDetails");
D3_Welcome_Page Welcome_Page = new D3_Welcome_Page();
//verify welcome page diosplayed )checking Deloitte Icon
Thread.Sleep(6000);
if (!(Welcome_Page.NameCredentials.Displayed))
{
Assert.IsTrue(false, "Welcome page NOT Displayed");
}
Actions_Sel actions_Sel = new Selenium_Framework_POC.Base.Actions_Sel();
actions_Sel.MouseHover(Welcome_Page.ProjectAdmin_Link);
Thread.Sleep(2000);
if (!(Welcome_Page.Projects_Link.Displayed))
{
Assert.IsTrue(false, "Projects sub menu not displayed");
}
Welcome_Page.Projects_Link.Click();
}
public void SearchWeatherInYourCountry()
{
String errorMessage = "";
ExcelHelpers.PopulateInCollection(ProjectPath + "\\Data\\KeywordAndOutcome.xlsx");
Homepage homepage = new Homepage(WebDriver);
String keyword = "";
for (int i = 1; i <= ExcelHelpers.GetRows(); i++)
{
keyword = ExcelHelpers.ReadData(i, "Keyword");
homepage.InputSearchKey(keyword);
errorMessage += homepage.VerifySearchResult(i, keyword, ExcelHelpers.ReadData(i, "Expected Outcome"), ExcelHelpers.ReadData(i, "Matching Count"));
}
if (!String.IsNullOrEmpty(errorMessage))
{
throw new Exception(errorMessage);
}
}
public void loginSteps()
{
//launch the url
//driver.Navigate().GoToUrl("http://horse-dev.azurewebsites.net/Account/Login?");
driver.Navigate().GoToUrl(ExcelHelpers.ReadData(2, "url"));
driver.Manage().Window.Maximize();
//Enter valid username
//IWebElement username = driver.FindElement(By.Id("UserName"));
//username.SendKeys("hari");
username.SendKeys(ExcelHelpers.ReadData(2, "username"));
//Enter valid password
//IWebElement password = driver.FindElement(By.Id("Password"));
//password.SendKeys("123123");
password.SendKeys(ExcelHelpers.ReadData(2, "password"));
try
{
//Click on login button
IWebElement loginButton = driver.FindElement(By.XPath("//*[@id='loginForm']/form/div[3]/input[1]"));
loginButton.Click();
//Verify if you are on the homescreen
//identify 'hello hari'
IWebElement helloHomepage = driver.FindElement(By.XPath("//*[@id='logoutForm']/ul/li/a"));
Assert.That(helloHomepage.Text, Is.EqualTo("Hello hari!"));
}
catch (Exception e)
{
Console.WriteLine("error occured during the launch of homepage", e.Message);
}
}
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
FillPalletList();
ExcelHelpers.FillComboWithColumnName(cbName);
ExcelHelpers.FillComboWithColumnName(cbDescription);
ExcelHelpers.FillComboWithColumnName(cbLength);
ExcelHelpers.FillComboWithColumnName(cbWidth);
ExcelHelpers.FillComboWithColumnName(cbHeight);
ExcelHelpers.FillComboWithColumnName(cbWeight);
ExcelHelpers.FillComboWithColumnName(cbOutputStart);
uCtrlMaxPalletHeight.Value = Settings.Default.MaximumPalletHeight;
uCtrlOverhang.ValueX = Settings.Default.OverhangX;
uCtrlOverhang.ValueY = Settings.Default.OverhangY;
LoadSettings();
OnGenerateImagesInFolderChanged(this, null);
OnGenerateReportsInFolderChanged(this, null);
UpdateStatus(this, null);
}
public void EnterBookName()
{
TextSearchFilter.Clear();
TextSearchFilter.SendKeys(ExcelHelpers.GetCellDataList("Sheet1", "BookName")[0]);
}
public async Task A22_PhotoExcelUpdate()
{
var db = await Db.Context();
var podPhoto = db.PhotoContents.Single(x => x.Title == IronwoodPhotoInfo.IronwoodPodContent01.Title);
var treePhoto = db.PhotoContents.Single(x => x.Title == IronwoodPhotoInfo.IronwoodTreeContent01.Title);
var items = new List <object> {
podPhoto, treePhoto
};
var excelFileExport = ExcelHelpers.ContentToExcelFileAsTable(items, "IronwoodTestExport01", false);
var workbook = new XLWorkbook(excelFileExport.FullName);
var worksheet = workbook.Worksheets.First();
var headerRow = worksheet.RangeUsed().Rows(1, 1);
var contentIdSheetColumn = headerRow.Cells().First(x => x.Value.ToString() == "ContentId").WorksheetColumn()
.ColumnNumber();
var slugSheetColumn = headerRow.Cells().First(x => x.Value.ToString() == "Slug").WorksheetColumn()
.ColumnNumber();
var titleSheetColumn = headerRow.Cells().First(x => x.Value.ToString() == "Title").WorksheetColumn()
.ColumnNumber();
var summarySheetColumn = headerRow.Cells().First(x => x.Value.ToString() == "Summary").WorksheetColumn()
.ColumnNumber();
var tagsSheetColumn = headerRow.Cells().First(x => x.Value.ToString() == "Tags").WorksheetColumn()
.ColumnNumber();
var updateNotesSheetColumn = headerRow.Cells().First(x => x.Value.ToString() == "UpdateNotes")
.WorksheetColumn().ColumnNumber();
var updatedBySheetColumn = headerRow.Cells().First(x => x.Value.ToString() == "LastUpdatedBy")
.WorksheetColumn().ColumnNumber();
var lensSheetColumn = headerRow.Cells().First(x => x.Value.ToString() == "Lens").WorksheetColumn()
.ColumnNumber();
var cameraModelSheetColumn = headerRow.Cells().First(x => x.Value.ToString() == "CameraModel")
.WorksheetColumn().ColumnNumber();
var idColumn = worksheet.Column(contentIdSheetColumn).Intersection(worksheet.RangeUsed()).AsRange();
var treeSheetPossibleRow =
idColumn.Cells().FirstOrDefault(x => x.Value.ToString() == treePhoto.ContentId.ToString());
Assert.NotNull(treeSheetPossibleRow, "No Row found for the tree photo in the Excel Import?");
var treeSheetRow = treeSheetPossibleRow.WorksheetRow().RowNumber();
worksheet.Cell(treeSheetRow, slugSheetColumn).Value =
IronwoodPhotoInfo.IronwoodTreeContent02_SlugTitleSummaryTagsUpdateNotesUpdatedBy.Slug;
worksheet.Cell(treeSheetRow, titleSheetColumn).Value = IronwoodPhotoInfo
.IronwoodTreeContent02_SlugTitleSummaryTagsUpdateNotesUpdatedBy.Title;
worksheet.Cell(treeSheetRow, summarySheetColumn).Value = IronwoodPhotoInfo
.IronwoodTreeContent02_SlugTitleSummaryTagsUpdateNotesUpdatedBy.Summary;
worksheet.Cell(treeSheetRow, tagsSheetColumn).Value =
IronwoodPhotoInfo.IronwoodTreeContent02_SlugTitleSummaryTagsUpdateNotesUpdatedBy.Tags;
worksheet.Cell(treeSheetRow, updateNotesSheetColumn).Value = IronwoodPhotoInfo
.IronwoodTreeContent02_SlugTitleSummaryTagsUpdateNotesUpdatedBy.UpdateNotes;
worksheet.Cell(treeSheetRow, updatedBySheetColumn).Value = IronwoodPhotoInfo
.IronwoodTreeContent02_SlugTitleSummaryTagsUpdateNotesUpdatedBy.LastUpdatedBy;
var podSheetRow = idColumn.Cells().First(x => x.Value.ToString() == podPhoto.ContentId.ToString())
.WorksheetRow().RowNumber();
worksheet.Cell(podSheetRow, cameraModelSheetColumn).Value =
IronwoodPhotoInfo.IronwoodPodContent02_CamerModelLensSummary.CameraModel;
worksheet.Cell(podSheetRow, lensSheetColumn).Value =
IronwoodPhotoInfo.IronwoodPodContent02_CamerModelLensSummary.Lens;
worksheet.Cell(podSheetRow, summarySheetColumn).Value =
IronwoodPhotoInfo.IronwoodPodContent02_CamerModelLensSummary.Summary;
worksheet.Cell(podSheetRow, updatedBySheetColumn).Value =
IronwoodPhotoInfo.IronwoodPodContent02_CamerModelLensSummary.LastUpdatedBy;
workbook.Save();
var importResult =
await ExcelContentImports.ImportFromFile(excelFileExport.FullName,
DebugTrackers.DebugProgressTracker());
Assert.False(importResult.HasError, "Unexpected Excel Import Failure");
Assert.AreEqual(2, importResult.ToUpdate.Count, "Unexpected number of rows to update");
var updateSaveResult =
await ExcelContentImports.SaveAndGenerateHtmlFromExcelImport(importResult,
DebugTrackers.DebugProgressTracker());
Assert.False(updateSaveResult.hasError);
var updatedPodPhoto = db.PhotoContents.Single(x =>
x.Title == IronwoodPhotoInfo.IronwoodPodContent02_CamerModelLensSummary.Title);
var updatedTreePhoto = db.PhotoContents.Single(x =>
x.Title == IronwoodPhotoInfo.IronwoodTreeContent02_SlugTitleSummaryTagsUpdateNotesUpdatedBy.Title);
var podReference = IronwoodPhotoInfo.IronwoodPodContent02_CamerModelLensSummary;
podReference.LastUpdatedOn = updatedPodPhoto.LastUpdatedOn;
var updatedPodComparison = IronwoodPhotoInfo.CompareContent(podReference, updatedPodPhoto);
Assert.True(updatedPodComparison.areEqual,
$"Excel Pod Picture Update Issues: {updatedPodComparison.comparisonNotes}");
var treeReference = IronwoodPhotoInfo.IronwoodTreeContent02_SlugTitleSummaryTagsUpdateNotesUpdatedBy;
treeReference.LastUpdatedOn = updatedTreePhoto.LastUpdatedOn;
var updatedTreeComparison = IronwoodPhotoInfo.CompareContent(treeReference, updatedTreePhoto);
Assert.True(updatedTreeComparison.areEqual,
$"Excel Tree Picture Update Issues: {updatedPodComparison.comparisonNotes}");
}
public void TestMethod1()
{
string filenName = Environment.CurrentDirectory.ToString() + "\\Data\\Login.xlsx";
ExcelHelpers.PopulateInCollection(filenName);
OpenBrowser(BrowserType.Chrome);
DriverContext.Browser.GotoUrl(url);
//LoginPage
CurrentPage = GetInstance <LoginPage>();
//HomePage
CurrentPage = CurrentPage.As <LoginPage>().Login(ExcelHelpers.ReadData(1, "UserName"), ExcelHelpers.ReadData(2, "Password"));
//ManagedContactsPage
CurrentPage = CurrentPage.As <HomePage>().ClickManagedContacts();
}
/// <summary>
/// When the user or the application switches the selected sheet in the excel
/// </summary>
/// <param name="range"></param>
protected void UpdateSelectedCell(Range range)
{
_selectedRange = range;
this.selectedRangeTextBox.Text = $"{ExcelHelpers.GetColumnName(range.Column)}{range.Row}";
}
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;
}
private void OnCompute(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
SaveSettings();
StringBuilder sbErrors = new StringBuilder();
try
{
string colName = ColumnLetterName;
string colDescription = ColumnLetterDescription;
string colLength = ColumnLetterLength;
string colWidth = ColumnLetterWidth;
string colHeight = ColumnLetterHeight;
string colWeight = ColumnLetterWeight;
string filePath = InputFilePath;
string outputPath = Path.Combine(Path.GetDirectoryName(InputFilePath), Path.GetFileNameWithoutExtension(InputFilePath) + "_output");
string filePathCopy = Path.ChangeExtension(outputPath, Path.GetExtension(InputFilePath));
File.Copy(filePath, filePathCopy, true);
// get the collection of work sheets
Excel.Application xlApp = new Excel.Application()
{
Visible = true, DisplayAlerts = false
};
Excel.Workbook xlWbk = xlApp.Workbooks.Open(filePathCopy, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlApp.Sheets[SheetName];
Excel.Range range = xlSheet.UsedRange;
int rowCount = range.Rows.Count;
int colStartIndex = ExcelHelpers.ColumnLetterToColumnIndex(ColumnLetterOutputStart) - 1;
int palletColStartIndex = colStartIndex - 1;
// pallet loop
var pallets = SelectedPallets;
foreach (var palletProperties in pallets)
{
int iOutputFieldCount = palletColStartIndex;
int iNoCols = 0;
// ### header : begin
// count
Excel.Range countHeaderCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1];
countHeaderCell.Value = Resources.ID_RESULT_NOCASES;
++iNoCols;
// byLayerCount
Excel.Range layerCountHeader = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1];
layerCountHeader.Value = Resources.ID_RESULT_LAYERCOUNT;
++iNoCols;
// load weight
Excel.Range loadWeightHeaderCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1];
loadWeightHeaderCell.Value = Resources.ID_RESULT_LOADWEIGHT + " (" + UnitsManager.MassUnitString + ")";
++iNoCols;
// total pallet weight
Excel.Range totalPalletWeightHeaderCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1];
totalPalletWeightHeaderCell.Value = Resources.ID_RESULT_TOTALPALLETWEIGHT + " (" + UnitsManager.MassUnitString + ")";
++iNoCols;
// efficiency
Excel.Range efficiencyHeaderCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1];
efficiencyHeaderCell.Value = Resources.ID_RESULT_EFFICIENCY + " (%)";
++iNoCols;
// image
if (GenerateImage)
{
Excel.Range imageHeaderCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + 1];
imageHeaderCell.Value = Resources.ID_RESULT_IMAGE;
++iNoCols;
}
// set bold font for all header row
Excel.Range headerRange = xlSheet.Range["a" + 1, ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + 1];
headerRange.Font.Bold = true;
// modify range for images
if (GenerateImage)
{
Excel.Range dataRange = xlSheet.Range["a" + 2,
ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + rowCount];
dataRange.RowHeight = 128;
Excel.Range imageRange = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + 2,
ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + rowCount];
imageRange.ColumnWidth = 24;
}
// ### header : end
// ### rows : begin
for (var iRow = 2; iRow <= rowCount; ++iRow)
{
try
{
iOutputFieldCount = palletColStartIndex;
// free version should exit after MaxNumberRowFree
if (!Program.IsSubscribed && iRow > MaxNumberRowFree + 1)
{
var cell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow];
cell.Value = string.Format(Resources.ID_MASSEXCEL_FREEVERSIONLIMITEDNUMBER, MaxNumberRowFree);
break;
}
// get name
string name = (xlSheet.Range[colName + iRow, colName + iRow].Value).ToString();
// get description
string description = string.IsNullOrEmpty(colDescription) ? string.Empty : (xlSheet.Range[colDescription + iRow, colDescription + iRow].Value).ToString();
// get length
double length = (double)xlSheet.Range[colLength + iRow, colLength + iRow].Value;
// get width
double width = (double)xlSheet.Range[colWidth + iRow, colWidth + iRow].Value;
// get height
double height = (double)xlSheet.Range[colHeight + iRow, colHeight + iRow].Value;
double maxDimension = Math.Max(Math.Max(length, width), height);
if (maxDimension < LargestDimensionMinimum)
{
continue;
}
// get weight
double?weight = null;
try { weight = (double)xlSheet.Range[colWeight + iRow, colWeight + iRow].Value; } catch (Exception /*ex*/) { }
// compute stacking
int stackCount = 0, layerCount = 0, byLayerCount = 0;
double loadWeight = 0.0, totalPalletWeight = 0.0, stackEfficiency = 0.0;
string stackImagePath = string.Empty;
// generate result
GenerateResult(name, description
, length, width, height, weight
, palletProperties, Overhang
, ref stackCount
, ref layerCount, ref byLayerCount
, ref loadWeight, ref totalPalletWeight
, ref stackEfficiency
, ref stackImagePath);
// insert count
var countCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow];
countCell.Value = stackCount;
// insert layer count
var layerCountCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow];
layerCountCell.Value = $"{layerCount} x {byLayerCount}";
// insert load weight
var loadWeightCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow];
loadWeightCell.Value = loadWeight;
// insert total weight
var totalWeightCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow];
totalWeightCell.Value = totalPalletWeight;
// efficiency
var efficiencyCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow];
efficiencyCell.Value = Math.Round(stackEfficiency, 2);
// insert image
if (GenerateImage)
{
var imageCell = xlSheet.Range[
ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + iRow,
ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow];
xlSheet.Shapes.AddPicture(
stackImagePath,
Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue,
(float)Convert.ToDecimal(imageCell.Left) + 1.0f,
(float)Convert.ToDecimal(imageCell.Top) + 1.0f,
(float)Convert.ToDecimal(imageCell.Width) - 2.0f,
(float)Convert.ToDecimal(imageCell.Height) - 2.0f
);
}
}
catch (OutOfMemoryException ex) { sbErrors.Append(ex.Message); }
catch (EngineException ex) { sbErrors.Append(ex.Message); }
catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException /*ex*/)
{
iOutputFieldCount = ExcelHelpers.ColumnLetterToColumnIndex(ColumnLetterOutputStart) - 1;;
var countCel = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow];
countCel.Value = string.Format($"ERROR : Invalid input data!");
}
catch (Exception ex)
{
throw ex; // rethrow
}
} // loop row
// ### rows : end
// increment palletColStartIndex
palletColStartIndex += iNoCols;
} // loop pallets
}
catch (System.Runtime.InteropServices.COMException ex)
{
switch ((uint)ex.ErrorCode)
{
case 0x800A03EC:
MessageBox.Show("NAME_NOT_FOUND : Could not find cell with given name!");
break;
default:
MessageBox.Show(ex.Message);
break;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
Cursor.Current = Cursors.Arrow;
}
}
public override async Task <RopDocument[]> FillDate()
{
var dataAlegatori = new List <RopData>();
var dataAlegatoriPrezenti = new List <RopData>();
var dd = new DownloadData();
var dataBytes = await dd.Data(rdBase.PathDocument);
var path = Path.Combine(Path.GetTempPath(), Path.GetTempFileName() + ".xls");
File.WriteAllBytes(path, dataBytes);
//var path = "D:\\a.xls";
var dt = new DataTable();
using (var m = new OleDbConnection())
{
m.ConnectionString = ExcelHelpers.BuildExcelConnectionString(path, true);
m.Open();
var query = @"Select * From [SIAEP2014_STAT_Statistica pe lo$]";
using (var cmd = new OleDbCommand(query, m))
{
using (var dr = cmd.ExecuteReaderAsync().Result)
{
dt.Load(dr);
}
}
}
foreach (DataRow row in dt.Rows)
{
var arr = row.ItemArray;
//if (arr[0] == null || string.IsNullOrWhiteSpace(arr[0].ToString()))
if (string.IsNullOrWhiteSpace(arr[0]?.ToString()))
{
continue;
}
var numeJudet = arr[1].ToString().Trim().ToLower();
if (numeJudet == "străinătate")
{
continue;
}
var judet = judetFinder.Find(numeJudet);
var valoare = int.Parse(arr[3].ToString());
var rd = new RopData();
rd.Judet = judet;
rd.Valoare = valoare;
rd.Oras = null;
dataAlegatori.Add(rd);
rd = new RopData();
rd.Judet = judet;
rd.Valoare = int.Parse(arr[6].ToString());
rd.Oras = null;
dataAlegatoriPrezenti.Add(rd);
}
var newRD = new RopDocument(rdBase);
newRD.Name += "Nr Alegatori Liste";
newRD.ID = "BA671A2B-27E8-408D-BABD-59B52661789D";
newRD.Data = dataAlegatori
.GroupBy(it => it.Judet).Select(group =>
new RopData()
{
Judet = group.Key,
Valoare = group.Sum(it => it.Valoare)
}
).ToArray();
var newRDUrne = new RopDocument(rdBase);
newRDUrne.Name += "Nr Alegatori Prezentati La Urne";
newRDUrne.ID = "BBB0ECA2-34BE-4177-B3A2-82BC6B41311E";
newRDUrne.Data = dataAlegatoriPrezenti
.GroupBy(it => it.Judet).Select(group =>
new RopData()
{
Judet = group.Key,
Valoare = group.Sum(it => it.Valoare)
}
).ToArray();
return(new[] { newRD, newRDUrne });
}
public override async Task <RopDocument[]> FillDate()
{
var dataCasatoriti = new List <RopData>();
var dd = new DownloadData();
var dataBytes = await dd.Data(rdBase.PathDocument);
var path = Path.Combine(Path.GetTempPath(), Path.GetTempFileName() + ".xls");
File.WriteAllBytes(path, dataBytes);
//var path = "D:\\a.xls";
var dt = new DataTable();
using (var m = new OleDbConnection())
{
m.ConnectionString = ExcelHelpers.BuildExcel2007ConnectionString(path, true);
m.Open();
var query = @"Select * From [Anul2015$]";
using (var cmd = new OleDbCommand(query, m))
{
using (var dr = cmd.ExecuteReaderAsync().Result)
{
dt.Load(dr);
}
}
}
foreach (DataRow row in dt.Rows)
{
var arr = row.ItemArray;
//if (arr[0] == null || string.IsNullOrWhiteSpace(arr[0].ToString()))
if (string.IsNullOrWhiteSpace(arr[0]?.ToString()))
{
continue;
}
var numeJudet = arr[1].ToString().Trim().ToLower();
var judet = judetFinder.Find(numeJudet);
Func <object, int> retVal = (obj) =>
{
int i;
if (obj == null)
{
return(0);
}
if (int.TryParse(obj.ToString(), out i))
{
return(i);
}
return(0);
};
int valoare = retVal(arr[15]);
var rd = new RopData();
rd.Judet = judet;
rd.Valoare = valoare;
rd.Oras = null;
dataCasatoriti.Add(rd);
}
rdBase.Data = dataCasatoriti
.GroupBy(it => it.Judet).Select(group =>
new RopData()
{
Judet = group.Key,
Valoare = group.Sum(it => it.Valoare)
}
).ToArray();
return(new[] { rdBase });
}
public HomePage()
{
ExcelHelpers.PopulateInCollection();
}
public void TC_8605_2()
{
try
{
string fileName = Environment.CurrentDirectory.ToString() + "\\ExcelData\\LibraryNew\\Login.xlsx";
ExcelHelpers.PopulateInCollection(fileName, "sheet1");
OpenBrowser(BrowserType.Chrome);
//LogHelpers.Write("Opening the Chrome Browser");
DriverContext.Browser.GoToUrl(url);
CurrentPage = GetInstance <LoginPage>();
CurrentPage = CurrentPage.As <LoginPage>().Login(ExcelHelpers.ReadData(1, "UserName"), ExcelHelpers.ReadData(1, "Password"));
CurrentPage.As <LeftMenuBar>().ClickLibrary();
CurrentPage = CurrentPage.As <LeftMenuBar>().ClickExpandTree();
CurrentPage = CurrentPage.As <LibraryLeftPane>().ClickDistrict();
DriverContext.Driver.SwitchTo().Frame("contentFrame");
System.Threading.Thread.Sleep(1500);
CurrentPage.As <DistrictListPage>().ClickFirstCheckBox();
CurrentPage = CurrentPage.As <DistrictListPage>().ClickDistrictEdit();
CurrentPage.As <District>().ClickIsMetro();
CurrentPage = CurrentPage.As <District>().ClickEditSave();
}
catch (Exception e)
{
LogHelpers.Write("Test Failed" + e.Message);
}
}
public void TestLogin()
{
TestInitializeHook.InitializeSettings();
string filename = Environment.CurrentDirectory.ToString() + @"\Data\Testdata.xlsx";
ExcelHelpers.PopulateInCollection(filename);
LogHelpers.WriteLog("Login");
CurrentPage = GetInstance <LoginPage>().Login(ExcelHelpers.ReadData(1, "UserName"), ExcelHelpers.ReadData(1, "Password"));
LogHelpers.WriteLog("Navigate To NewCompany page");
CurrentPage.As <HomePage>().NavigateToNewCompany();
}
private static void ReadData(string file, bool imported, List <Contact> contacts, List <KeyValuePair <string, string> > errors)
{
var sheet = ExcelHelpers.OpenWorkbook(HostingEnvironment.MapPath(file));
for (var i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
try
{
var c_id = ExcelHelpers.ReadIntCell(row, 48);
// check the existance
if (Db.Contacts.Any(a => a.c_Id == c_id))
{
throw new Exception("Already exists");
}
// check and see if we came across it already in this file
var item = contacts.Where(a => a.c_Id == c_id).FirstOrDefault();
if (item != null)
{
errors.Add(new KeyValuePair <string, string>(c_id.ToString(), "duplicate value, replacing old"));
// get the index to remove the first one
contacts.Remove(item);
}
var contact = new Contact();
contact.Badge = ExcelHelpers.ReadCell(row, 0); // a
contact.CourierCity = ExcelHelpers.ReadCell(row, 1); // b
contact.CourierCountry = ExcelHelpers.ReadCell(row, 2); // c
contact.CourierState = ExcelHelpers.ReadCell(row, 3); // d
contact.CourierStreet = ExcelHelpers.ReadCell(row, 4); // e
contact.CourierZip = ExcelHelpers.ReadCell(row, 5); // f
contact.DeliveryCity = ExcelHelpers.ReadCell(row, 6); // g
contact.DeliveryCountry = ExcelHelpers.ReadCell(row, 7); // h
contact.DeliveryState = ExcelHelpers.ReadCell(row, 8); // i
contact.DeliveryStreet = ExcelHelpers.ReadCell(row, 9); // j
contact.DeliveryZip = ExcelHelpers.ReadCell(row, 10); // k
contact.HomeCity = ExcelHelpers.ReadCell(row, 11); // l
contact.HomeCountry = ExcelHelpers.ReadCell(row, 12); // m
contact.HomePreferred = ExcelHelpers.ReadBoolCell(row, 13); // n
contact.HomeState = ExcelHelpers.ReadCell(row, 14); // o
contact.HomeStreet = ExcelHelpers.ReadCell(row, 15); // p
contact.HomeZip = ExcelHelpers.ReadCell(row, 16); // q
contact.ReportCity = ExcelHelpers.ReadCell(row, 17); // r
contact.ReportCountry = ExcelHelpers.ReadCell(row, 18); // s
contact.ReportFirm = ExcelHelpers.ReadCell(row, 19); // t
contact.ReportState = ExcelHelpers.ReadCell(row, 20); // u
contact.ReportStreet = ExcelHelpers.ReadCell(row, 21); // v
contact.ReportZip = ExcelHelpers.ReadCell(row, 22); // w
contact.AssistantEmail = ExcelHelpers.ReadCell(row, 23); // x
contact.AssistantEmailPreferred = ExcelHelpers.ReadBoolCell(row, 24); // y
contact.AssistantEmailPreferredCc = ExcelHelpers.ReadBoolCell(row, 25); // z
contact.AssistantName = ExcelHelpers.ReadCell(row, 26); // aa
contact.AssistantPhone = ExcelHelpers.ReadCell(row, 27); // ab
contact.AssistantExt = ExcelHelpers.ReadCell(row, 28); // ac
contact.Backup = ExcelHelpers.ReadCell(row, 29); // ad
contact.Biography = ExcelHelpers.ReadCell(row, 30); // ae
contact.CellPhone = ExcelHelpers.ReadCell(row, 31); // af
contact.Email = ExcelHelpers.ReadCell(row, 32); // ag
contact.Fax = ExcelHelpers.ReadCell(row, 33); // ah
contact.FaxDelivery = ExcelHelpers.ReadCell(row, 34); // ai
contact.Phone = ExcelHelpers.ReadCell(row, 35); // aj
contact.PhoneDelivery = ExcelHelpers.ReadCell(row, 36); // ak
contact.Ext = ExcelHelpers.ReadCell(row, 37); // al
contact.DateCreated = ExcelHelpers.ReadDateCell(row, 38); // am
contact.CurrentSeminarYear = ExcelHelpers.ReadIntCell(row, 39); // an
contact.CurrentYear = ExcelHelpers.ReadIntCell(row, 40); // ao
contact.DateLastUpdated = ExcelHelpers.ReadDateCell(row, 41); // ap
contact.DateModified = ExcelHelpers.ReadDateCell(row, 42); // aq
contact.EmergencyName = ExcelHelpers.ReadCell(row, 43); // ar
contact.EmergencyPhone = ExcelHelpers.ReadCell(row, 44); // as
contact.EmergencyExt = ExcelHelpers.ReadCell(row, 45); // at
contact.CommodityByFirmId = ExcelHelpers.ReadIntCell(row, 46); // au
contact.CommodityByRContactFirmId = ExcelHelpers.ReadIntCell(row, 47); // av
contact.c_Id = ExcelHelpers.ReadIntCell(row, 48); // aw
contact.LayoutFirmName = ExcelHelpers.ReadCell(row, 49); // ax
contact.MiscCopyAndPaste = ExcelHelpers.ReadCell(row, 50); // ay
contact.FirstName = ExcelHelpers.ReadCell(row, 51); // az
contact.LastName = ExcelHelpers.ReadCell(row, 52); // ba
contact.MI = ExcelHelpers.ReadCell(row, 53); // bb
contact.Salutation = ExcelHelpers.ReadCell(row, 54); // bc
contact.Notes = ExcelHelpers.ReadCell(row, 55); // bd
contact.ArchiveComments = ExcelHelpers.ReadCell(row, 56); // be
contact.CreatedBy = ExcelHelpers.ReadCell(row, 57); // bf
contact.UpdatedBy = ExcelHelpers.ReadCell(row, 58); // bg
contact.ModifiedBy = ExcelHelpers.ReadCell(row, 59); // bh
contact.SpecialPreferences = ExcelHelpers.ReadCell(row, 60); // bi
contact.CurrentYearAccepted = ExcelHelpers.ReadBoolCell(row, 61); // bj
contact.CurrentYearInvitee = ExcelHelpers.ReadBoolCell(row, 62); // bk
contact.HasPhoto = ExcelHelpers.ReadBoolCell(row, 63); // bl
contact.HasPhoto2 = ExcelHelpers.ReadBoolCell(row, 64); // bm
contact.LoginName = ExcelHelpers.ReadCell(row, 65); // bn
contact.LoginName2 = ExcelHelpers.ReadCell(row, 66); // bo
contact.Password = ExcelHelpers.ReadCell(row, 67); // bp
contacts.Add(contact);
}
catch (Exception ex)
{
errors.Add(new KeyValuePair <string, string>(ExcelHelpers.ReadIntCell(row, 48).ToString(), ex.Message));
}
}
if (!imported)
{
foreach (var a in contacts)
{
Db.Contacts.Add(a);
}
Db.SaveChanges();
}
}
public IWebDriver getWebDriver()
{
ChromeOptions options = new ChromeOptions();
InternetExplorerOptions caps = new InternetExplorerOptions();
caps.IgnoreZoomLevel = true;
caps.EnableNativeEvents = false;
caps.InitialBrowserUrl = "http://localhost";
caps.UnhandledPromptBehavior = UnhandledPromptBehavior.Accept;
caps.IntroduceInstabilityByIgnoringProtectedModeSettings = true;
caps.EnablePersistentHover = true;
var opt = new FirefoxOptions();
opt.AcceptInsecureCertificates = true;
string browserType = ExcelHelpers.getParameter("Browser");
switch (browserType)
{
case "Chrome":
options.AddArguments("--disable-notifications");
if (!string.IsNullOrEmpty(browserType))
{
driver = new ChromeDriver(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), options);
}
else
{
driver = new ChromeDriver(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), options);
}
break;
case "IE":
//set capability
driver = new InternetExplorerDriver(caps);
break;
case "Firefox":
driver = new FirefoxDriver(opt);
break;
case "Safari":
driver = new SafariDriver();
break;
case "Headless-Chrome":
//Headless ChromeBrowser
options.AddArguments("--disable-notifications");
options.AddArguments("--headless");
driver = new ChromeDriver(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), options);
break;
default:
Logger.log("No Broswer Found");
break;
}
return(driver);
}
private void addRawDataSheets()
{
Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook;
foreach (Excel.Worksheet ws in WB.Sheets)
{
fromActual = false;
fromEnergyCost = false;
string raw = ExcelHelpers.getWorksheetCustomProperty(ws, Utilities.Constants.WS_ROLLUP) ?? "FALSE";
string flnm = ExcelHelpers.getWorksheetCustomProperty(ws, Utilities.Constants.WS_SRCFILE) ?? "";
numberOfSources = 0;
if (bool.Parse(raw) && ws.ListObjects.Count > 0)
{
//parse through ws to populate numberOfSources, fromActual, hasProd and hasBuildSqFt
if (ws.Name.Contains("EnPI Actual Results"))
{
fromActual = true;
}
int parseRows = ws.ListObjects[1].ListRows.Count;
bool endOfSources = false;
for (int i = 5; i < parseRows + 4; i++)
{
try
{
string cellValue = ws.Range["A" + i.ToString()].Value2.ToString();
if (cellValue.Equals(Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName")))
{
endOfSources = true;
}
if (cellValue.Equals("Total Production Output"))
{
hasProd = true;
}
if (cellValue.Equals(Globals.ThisAddIn.rsc.GetString("unadjustedBuildingColName")))
{
hasBuildSqFt = true;
}
if (cellValue.Contains("Estimated Cost Savings"))
{
fromEnergyCost = true;
}
//This is a work around, as there is a problem in identifying whether the sheet is Actual or from Regression, but the below item is only in actual sheet so
//Considered this is as a differentiator.
if (cellValue.Contains("Total Savings Since Baseline Year (MMBtu/Year)"))
{
fromActual = true;
}
if (!endOfSources)
{
numberOfSources++;
}
}
catch (Exception e)
{
}
}
DetailTable newt = new DetailTable(ws.ListObjects[1], ws.Name, numberOfSources, fromActual, hasProd, hasBuildSqFt, false, fromEnergyCost);
newt.DisplayName = flnm == "" ? ws.Name : ws.Name + " (" + flnm + ")";
if (newt.SQLStatement != null)
{
tables.Add(newt);
}
}
}
}
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));
}
/// <summary>
/// Processes the list of items, adding them to the worksheet
/// </summary>
/// <param name="worksheet"></param>
private void AddRows(Worksheet worksheet)
{
if (items == null || !items.Any())
{
return;
}
var sheetData = new SheetData();
var columns = new List <string>();
Columns exColumns = new Columns();
var headerRow = new Row();
var type = typeof(T);
#region Header
var index = 0;
var properties = type.GetProperties();
#region Order Columns
if (this.order != null) // the column order from settings has priority
{
properties = properties.OrderBy(x =>
{
if (this.order.ContainsKey(x.Name))
{
return(this.order.FirstOrDefault(y => y.Key == x.Name).Value);
}
else
{
return(Int32.MaxValue);
}
}).ToArray();
}
else if (this.dataCells != null && this.dataCells.Any())
{
properties = properties.OrderBy(x =>
{
var cell = this.dataCells.FirstOrDefault(y => y.PropName == x.Name);
if (cell != null)
{
return(cell.Position ?? Int32.MaxValue);
}
else
{
return(Int32.MaxValue);
}
}).ToArray();
}
#endregion
// create header based on DisplayAttribute and BsGridColumnAttribute
foreach (var property in properties)
{
BsGridColumnAttribute columnAttr = null;
if (ReflectionHelpers.TryGetAttribute(property, out columnAttr))
{
if (columnAttr.Usage != Models.BsGridColumnUsage.Html)
{
index++;
#region Value
string displayName = null;
DisplayAttribute displayAttribute = null;
if (ReflectionHelpers.TryGetAttribute(property, out displayAttribute))
{
displayName = displayAttribute.GetName();
}
else
{
displayName = property.Name;
}
#endregion
#region Style
var width = columnAttr.Width;
BsGridExcelStyle style = null;
if (headerCells != null)
{
var headerCell = headerCells.FirstOrDefault(x => string.Compare(x.PropName, property.Name) == 0);
if (headerCell != null)
{
displayName = headerCell.Name;
style = BsGridExcelHelpers.Concat(headerCell.CellStyle, this.headerStyle);
}
}
columns.Add(property.Name);
exColumns.Append(ExcelHelpers.CreateColumn((UInt32)index, (UInt32)index, width * widthUnit));
int formatId;
if (style == null)
{
style = this.headerStyle ?? new BsGridExcelStyle
{
Font = new BsGridExcelFont
{
Bold = true
}
};
}
var cellFormat = GetCellFormat(style, out formatId);
if (cellFormat != null)
{
styleSheet.CellFormats.Append(cellFormat);
}
#endregion
headerRow.AppendChild(ExcelHelpers.CreateTextCell(displayName, (UInt32)formatId));
}
}
}
sheetData.AppendChild(headerRow);
#endregion
#region Rows
// create data table
foreach (var item in items)
{
var row = new Row();
foreach (var column in columns)
{
var property = type.GetProperty(column);
object value;
var cell = dataCells.FirstOrDefault(x => string.Compare(x.PropName, column) == 0);
#region Style
BsGridExcelStyle style = new BsGridExcelStyle();
if (aConfig != null)
{
aConfig(item, style);
}
else if (fConfig != null)
{
style = fConfig(item);
}
if (cell != null)
{
if (cell.CellStyle != null)
{
style = BsGridExcelHelpers.Concat(style, cell.CellStyle);
}
if (cell.StyleFunc != null)
{
var style2 = new BsGridExcelStyle();
cell.StyleFunc(item, style2);
style = BsGridExcelHelpers.Concat(style, style2);
}
}
int formatId;
var cellFormat = GetCellFormat(style, out formatId);
if (cellFormat != null)
{
styleSheet.CellFormats.Append(cellFormat);
}
#endregion
#region Value
if (cell != null && cell.NameFunc != null)
{
value = cell.NameFunc(item);
}
else
{
value = property.GetValue(item);
}
if (value != null)
{
var strValue = value as string;
if (strValue != null)
{
row.AppendChild(ExcelHelpers.CreateTextCell(strValue, (UInt32)formatId));
}
else
{
DateTime dateValue;
int intValue;
long longValue;
double doubleValue;
if (DateTime.TryParse(value.ToString(), out dateValue))
{
// ToOADate => excel representation of DateTime - TODO: format date
row.AppendChild(ExcelHelpers.CreateTextCell(dateValue.ToShortDateString(), (UInt32)formatId));
}
else if (int.TryParse(value.ToString(), out intValue))
{
row.AppendChild(ExcelHelpers.CreateValueCell(intValue, (UInt32)formatId, CellValues.Number));
}
else if (long.TryParse(value.ToString(), out longValue))
{
row.AppendChild(ExcelHelpers.CreateValueCell(longValue, (UInt32)formatId, CellValues.Number));
}
else if (Double.TryParse(value.ToString(), out doubleValue))
{
row.AppendChild(ExcelHelpers.CreateValueCell(doubleValue, (UInt32)formatId, CellValues.Number));
}
else // not supported type
{
throw new Exception(column + " is not of type string");
}
}
}
else
{
row.AppendChild(ExcelHelpers.CreateTextCell(string.Empty, (UInt32)formatId));
}
#endregion
}
sheetData.AppendChild(row);
}
#endregion
worksheet.Append(exColumns);
worksheet.Append(sheetData);
}
public void Test_GetColumnPart(string input, string expected)
{
string actual = ExcelHelpers.GetColumnPart(input);
Assert.That(actual, Is.EqualTo(expected));
}
private void autoyear_Click(object sender, RibbonControlEventArgs e)
{
Excel.Worksheet thisSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
Excel.ListObject thisList = ExcelHelpers.GetListObject(thisSheet);
ExcelHelpers.AutoSetYear(thisList, 12);
}
private static void ReadData(string file, bool imported, List <Firm> firms, List <KeyValuePair <string, string> > errors)
{
var sheet = ExcelHelpers.OpenWorkbook(HostingEnvironment.MapPath(file));
for (var i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
try
{
var f_id = ExcelHelpers.ReadIntCell(row, 13);
// check the existance
if (Db.Firms.Any(a => a.f_id == f_id))
{
throw new Exception("Already exists");
}
// check and see if we came across it already in this file
var item = firms.Where(a => a.f_id == f_id).FirstOrDefault();
if (item != null)
{
errors.Add(new KeyValuePair <string, string>(f_id.ToString(), "duplicate value, replacing old"));
// get the index to remove the first one
firms.Remove(item);
}
var firm = new Firm();
firm.City = ExcelHelpers.ReadCell(row, 0); // a
firm.Country = ExcelHelpers.ReadCell(row, 1); // b
firm.State = ExcelHelpers.ReadCell(row, 2); // c
firm.Address = ExcelHelpers.ReadCell(row, 3); // d
firm.Zip = ExcelHelpers.ReadCell(row, 4); // e
firm.IsPOBox = ExcelHelpers.ReadBoolCell(row, 5); // f
firm.Fax = ExcelHelpers.ReadCell(row, 6); // g
firm.Phone = ExcelHelpers.ReadCell(row, 7); // h
firm.Ext = ExcelHelpers.ReadCell(row, 8); // i
firm.Created = ExcelHelpers.ReadDateCell(row, 9); // j
firm.Modified = ExcelHelpers.ReadDateCell(row, 10); // k
firm.Description = ExcelHelpers.ReadCell(row, 11); // l
firm.Financial = ExcelHelpers.ReadBoolCell(row, 12); // m
firm.f_id = ExcelHelpers.ReadIntCell(row, 13); // n
firm.Name = ExcelHelpers.ReadCell(row, 14); // o
firm.CreatedBy = ExcelHelpers.ReadCell(row, 15); // p
firm.ModifiedBy = ExcelHelpers.ReadCell(row, 16); // q
firm.WebAddress = ExcelHelpers.ReadCell(row, 17); // r
// check the name against one that has already been created
var db = Db.Firms.Where(a => a.Name.Trim().ToLower() == firm.Name.Trim().ToLower()).FirstOrDefault();
var list = firms.Where(a => a.Name.Trim().ToLower() == firm.Name.Trim().ToLower()).FirstOrDefault();
if (db != null)
{
firm.GroupId = db.GroupId;
}
else if (list != null)
{
firm.GroupId = list.GroupId;
}
else
{
firm.GroupId = Guid.NewGuid();
}
firms.Add(firm);
}
catch (Exception ex)
{
errors.Add(new KeyValuePair <string, string>(ExcelHelpers.ReadIntCell(row, 13).ToString(), ex.Message));
}
}
if (!imported)
{
foreach (var a in firms)
{
Db.Firms.Add(a);
}
Db.SaveChanges();
}
}
/// <summary>
/// Creates a basic stylesheet
/// Can be overriden for custom cell styles
/// </summary>
/// <returns></returns>
private Stylesheet AddStylesheet()
{
Stylesheet stylesheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
// create fonts
Fonts fonts = new Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
int fontId;
var simpleFont = GetFont(new BsGridExcelStyle(), out fontId);
var boldFont = GetFont(new BsGridExcelStyle()
{
Font = new BsGridExcelFont()
{
Bold = true
}
}, out fontId);
fonts.Append(simpleFont);
fonts.Append(boldFont);
// create fills
Fills fills = new Fills()
{
Count = (UInt32Value)1U
};
int fillId;
var noneFill = GetFill(new BsGridExcelStyle()
{
FillPattern = PatternValues.None
}, out fillId); //needed, reserved by excel
var greyFill = GetFill(new BsGridExcelStyle()
{
FillPattern = PatternValues.Gray125
}, out fillId); //needed, reserved by excel
fills.Append(noneFill);
fills.Append(greyFill);
// create borders
Borders borders = new Borders()
{
Count = (UInt32Value)1U
};
var border = ExcelHelpers.CreateBorder();
borders.Append(border);
// create cell style formats
CellStyleFormats cellStyleFormats = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats.Append(cellFormat);
CellFormats cellFormats = new CellFormats()
{
Count = (UInt32Value)2U
};
int formatId;
CellFormat headerCellFormat = GetCellFormat(new BsGridExcelStyle
{
FontId = 1,
FillId = 0
}, out formatId);
CellFormat dataCellFormat = GetCellFormat(new BsGridExcelStyle
{
FontId = 0,
FillId = 0
}, out formatId);
cellFormats.Append(headerCellFormat);
cellFormats.Append(dataCellFormat);
CellStyles cellStyles = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles.Append(cellStyle);
// add to stylesheet
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellStyleFormats);
stylesheet.Append(cellFormats);
stylesheet.Append(cellStyles);
return(stylesheet);
}
private void btnImport_Click(object sender, EventArgs e)
{
Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Worksheet activeSheet = (Excel.Worksheet)WB.ActiveSheet;
List <int> deletes = new List <int>();
string connString = Utilities.Constants.EXCEL_CONNSTRING;
for (int i = 0; i < checkedListBox2.Items.Count; i++)
{
if (checkedListBox2.GetItemChecked(i))
{
SourceFile fl = (SourceFile)checkedListBox2.Items[i];
string flnm = fl.FileName.Substring(fl.FileName.LastIndexOf("\\")).Replace("\\", "");
// create new worksheet
string newSheetNm = ExcelHelpers.CreateValidWorksheetName(WB, fl.ShortName, 0);
Excel.Worksheet newSheet = (Excel.Worksheet)WB.Sheets.Add(System.Type.Missing, WB.Sheets[WB.Sheets.Count], System.Type.Missing, System.Type.Missing);
newSheet.Name = newSheetNm;
// add custom properties
ExcelHelpers.addWorksheetCustomProperty(newSheet, Utilities.Constants.WS_ROLLUP, "TRUE");
ExcelHelpers.addWorksheetCustomProperty(newSheet, Utilities.Constants.WS_SRCFILE, flnm);
// import the data
DataTable dt = new DataTable();
connString = connString.Replace("{0}", fl.FileName);
System.Data.OleDb.OleDbConnection aConn = new System.Data.OleDb.OleDbConnection(connString);
aConn.Open();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(fl.SQLStatement, aConn);
try
{
da.Fill(dt);
}
catch
{
MessageBox.Show("An error was encountered while attempting to import sheets from workbook " + this.openFileDialog1.FileNames.GetValue(i).ToString() + ". Please re-run the data in a new workbook before including it in the corporate roll-up.");
break;
}
aConn.Close();
aConn.Dispose();
// copy the data to the new sheet
Excel.Range rng = newSheet.Rows.get_Resize(dt.Rows.Count + 1, dt.Columns.Count + 1).get_Offset(3, 0);
//rng.get_Offset(0, 0).get_Resize(1, 1).Value2 = "Name";
for (int c = 0; c < dt.Columns.Count; c++)
{
rng.get_Offset(0, c).get_Resize(1, 1).Value2 = dt.Columns[c].ColumnName;
}
rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, dt.Columns.Count).Value2 = DataHelper.dataTableArrayObject(dt);
//rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, 1).Formula = fl.ShortName;//"=IFERROR(RIGHT(CELL(\"filename\",$A$1), LEN(CELL(\"filename\",$A$1)) - FIND(\"]\",CELL(\"filename\",$A$1),1)),\"\")";
object[,] tmp1 = (object[, ])rng.Value2;
bool hasProd = false;
bool hasBuildSqFt = false;
fromEnergyCost = false;
foreach (DataRow dr in dt.Rows)
{
if (dr[0].ToString().Contains("Total Production Output") || dr[0].ToString().Contains("Production Energy Intensity (MMBtu/unit production)"))
{
hasProd = true;
}
if (dr[0].ToString().Contains("Building Energy Intensity"))
{
hasBuildSqFt = true;
}
if (dr[0].ToString().Contains("Estimated Cost Savings"))
{
fromEnergyCost = true;
}
}
//-----------------------------------------------
newSheet.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);//.AddEx(Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing, System.Type.Missing);
DetailTable newdt = new DetailTable(newSheet.ListObjects[1], fl.ShortName, fl.numOfSources, fl.fromActual, hasProd, hasBuildSqFt, true, fromEnergyCost);
newdt.DisplayName = newSheetNm + " (" + flnm + ")";
tables.Add(newdt);
}
}
files.Clear();
checkedListBox2.Refresh();
checkedListBox2.Visible = false;
btnImport.Visible = false;
// reload the tables box
AddTablesToList();
activeSheet.Activate();
// // copy the data to the new sheet
// int jslkdf = dt.Rows.Count;
// Excel.Range rng = newSheet.get_Range("A1").get_Resize(((dt.Rows.Count + 1)/2)+1, dt.Columns.Count + 1);
// rng.get_Offset(0, 0).get_Resize(1, 1).Value2 = "Name";
// for (int c = 0; c < dt.Columns.Count; c++)
// {
// rng.get_Offset(0, c + 1).get_Resize(1,1).Value2 = dt.Columns[c].ColumnName;
// }
// rng.get_Offset(1, 1).get_Resize(dt.Rows.Count, dt.Columns.Count).Value2 = DataHelper.dataTableArrayObject(dt);
// rng.get_Offset(1, 0).get_Resize(dt.Rows.Count, 1).Formula = "=IFERROR(RIGHT(CELL(\"filename\",$A$1), LEN(CELL(\"filename\",$A$1)) - FIND(\"]\",CELL(\"filename\",$A$1),1)),\"\")";
// object[,] tmp1 = (object[,])rng.Value2;
// newSheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, rng, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing, System.Type.Missing);
// DetailTable newdt = new DetailTable(newSheet.ListObjects[1], fl.ShortName);
// newdt.DisplayName = newSheetNm + " (" + flnm + ")";
// tables.Add(newdt);
// }
//}
//files.Clear();
//checkedListBox2.Refresh();
//checkedListBox2.Visible = false;
//btnImport.Visible = false;
//// reload the tables box
//AddTablesToList();
//activeSheet.Activate();
}
public static void ReadData(string file, bool imported, List <CommodityLink> commodities, List <KeyValuePair <string, string> > errors)
{
var sheet = ExcelHelpers.OpenWorkbook(HostingEnvironment.MapPath(file));
for (var i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
try
{
var m_id = ExcelHelpers.ReadIntCell(row, 4);
// check the existance
if (Db.CommodityLinks.Any(a => a.m_id == m_id))
{
throw new Exception("Already exists");
}
// check and see if we came across it already in this file
var item = commodities.Where(a => a.m_id == m_id).FirstOrDefault();
if (item != null)
{
errors.Add(new KeyValuePair <string, string>(m_id.ToString(), "duplicate value, replacing old"));
// get the index to remove the first one
commodities.Remove(item);
}
var commodity = new CommodityLink();
commodity.DateCreated = ExcelHelpers.ReadDateCell(row, 0); // a
commodity.DateModified = ExcelHelpers.ReadDateCell(row, 1); // b
commodity.FirmId = ExcelHelpers.ReadIntCell(row, 2); // c
commodity.RContactFirmId = ExcelHelpers.ReadIntCell(row, 3); // d
commodity.m_id = ExcelHelpers.ReadIntCell(row, 4); // e
commodity.Name = ExcelHelpers.ReadCell(row, 5); // f
commodity.CreatedBy = ExcelHelpers.ReadCell(row, 6); // g
commodity.ModifiedBy = ExcelHelpers.ReadCell(row, 7); // h
if (!string.IsNullOrWhiteSpace(commodity.Name))
{
foreach (var a in commodity.Name.Split(','))
{
commodity.AddCommodity(new Commodity()
{
Name = Capitalize(a.Trim())
});
}
}
commodities.Add(commodity);
}
catch (Exception ex)
{
errors.Add(new KeyValuePair <string, string>(ExcelHelpers.ReadIntCell(row, 4).ToString(), ex.Message));
}
}
// save all the instances
if (!imported)
{
foreach (var a in commodities)
{
Db.CommodityLinks.Add(a);
}
Db.SaveChanges();
}
}
