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