public void ThenICanGetAllExcelRangeProperties() { //ExcelInteropHelper.GetCellProperties(5, 1); ExcelInteropHelper.ActivateSheet("RiskRegisterWithScope"); var bgColorProperty = ExcelInteropHelper.ReadExcelRangeProperty("H5:S5", ExcelCellProperties.CellBackGroundColor); var fontColorProperty = ExcelInteropHelper.ReadExcelRangeProperty("H5:S5", ExcelCellProperties.CellFontColor); var fontSizeProperty = ExcelInteropHelper.ReadExcelRangeProperty("H5:S5", ExcelCellProperties.CellFontSize); var formulaProperty = ExcelInteropHelper.ReadExcelRangeProperty("H5:S5", ExcelCellProperties.CellFormula); var textProperty = ExcelInteropHelper.ReadExcelRangeProperty("H5:S5", ExcelCellProperties.CellText); int index = 0; foreach (var text in textProperty) { if (Convert.ToInt32(text) < 2) { Assert.AreEqual("ffffff", bgColorProperty[index]); } else if (Convert.ToInt32(text) >= 2 && Convert.ToInt32(text) < 5) { Assert.AreEqual("ffffff", bgColorProperty[index]); } else if (Convert.ToInt32(text) >= 5) { Assert.AreEqual("a31e22", bgColorProperty[index]); } index++; } }
public void ThenICanGetExcelRangeAsList() { var actualValues = ExcelInteropHelper.ReadExcelRangeProperty("A1:A11"); var expectedValues = new List <string>(); expectedValues.AddRange(new[] { "A", "2.001", "5", "ABC", "XYZ", "2.498750625", "₹ 23.75", "12/15/2019", "December 15, 2019", "8:52:48 AM", "76.90%" }); CollectionAssert.AreEquivalent(expectedValues, actualValues); }
public void ThenIShouldGetAllWorkheetNames() { List <string> actualSheetNames = ExcelInteropHelper.GetAllSheetNames(); var expectedSheetNames = new List <string>(); expectedSheetNames.AddRange(new[] { "Test1", "Test2", "IP (1) Process design or execut", "RiskRegisterWithScope" }); CollectionAssert.AreEquivalent(expectedSheetNames, actualSheetNames); }
public void ThenICanGetExcelCellBackgroundColor() { var actualValues = ExcelInteropHelper.ReadExcelRangeProperty("A3", ExcelCellProperties.CellBackGroundColor); var expectedValues = new List <string>(); expectedValues.AddRange(new[] { "ffffff" }); CollectionAssert.AreEquivalent(expectedValues, actualValues); }
public void ThenICanGetExcelRangeCo_Ordinates() { var actualValues = ExcelInteropHelper.GetRangeCoOrdinates("A1:A11"); var expectedValues = new List <int>(); expectedValues.AddRange(new[] { 1, 1, 1, 11 }); CollectionAssert.AreEquivalent(expectedValues, actualValues); }
private async Task GerarRelatorioFluviometrico() { foreach (var item in selectLstBox.Items) { string codigo = item.ToString().Split('-')[0]; Atividade.Text = $"Buscando dados estação {codigo} no Hidroweb-ANA"; var dadosVazaoEstacao = await ServiceANAHelper.DadosFluviometricosVazaoEstacao(Convert.ToInt32(codigo)); var dadosCotasEstacao = await ServiceANAHelper.DadosFluviometricosCotaEstacao(Convert.ToInt32(codigo)); try { if (dadosVazaoEstacao.EhValido && dadosCotasEstacao.EhValido) { Atividade.Text = $"Gerando planilha para estação: {codigo}"; var dadosSerieHistoricaCotas = DataTableParaSerieHistoricaCota(dadosCotasEstacao.Dados); var dadosSerieHistoricaVazao = DataTableParaSerieHistoricaVazao(dadosVazaoEstacao.Dados); var estacao = ListaEstacoesCache.Estacoes.First(c => c.Codigo == Convert.ToInt32(codigo)); _Workbook planilha = ExcelInteropHelper.CriarNovaPlanilhaFluviometrico("item"); planilha = ExcelInteropHelper.CriarAbaEstacaoFluviometrica(planilha, dadosSerieHistoricaVazao, estacao); planilha = ExcelInteropHelper.CriarAbaCotas(planilha, dadosSerieHistoricaCotas, estacao); planilha = ExcelInteropHelper.CriarAbaVazao(planilha, dadosSerieHistoricaVazao, estacao); planilha = ExcelInteropHelper.CriarCotaVazaoDiaria(planilha, dadosSerieHistoricaCotas, dadosSerieHistoricaVazao, estacao); planilha = ExcelInteropHelper.CriarGraficoCotaTempo(planilha, estacao); planilha = ExcelInteropHelper.CriarGraficoCotaVazao(planilha, estacao); Atividade.Text = $"Salvando planilha."; planilha.SaveAs(escolherDiretorio.SelectedPath + $"/{codigo}", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, null, null, null); ExcelInteropHelper.FecharAplicacao(planilha); } else { MessageBox.Show($"Não foi possível encontrar dados para {Convert.ToInt32(codigo)}", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1); } } catch (Exception ex) { boxBusca.Enabled = true; boxSelecao.Enabled = true; Atividade.Text = string.Empty; MessageBox.Show(ex.Message, "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1); } } }
public void ThenICanActivateWorksheetsByNameIndex() { ExcelInteropHelper.ActivateSheet(); Assert.AreEqual("Test1", ExcelInteropHelper.GetActiveSheetName()); ExcelInteropHelper.ActivateSheet(2); Assert.AreEqual("Test2", ExcelInteropHelper.GetActiveSheetName()); ExcelInteropHelper.ActivateSheet("Test1"); Assert.AreEqual("Test1", ExcelInteropHelper.GetActiveSheetName()); }
public void ThenICanAllExcelCellProperties() { ExcelInteropHelper.ActivateSheet("RiskRegisterWithScope"); ExcelInteropHelper.GetCellProperties(5, 8); string bgColor = ExcelCellProperties.BackGroundColor; string fontColor = ExcelCellProperties.FontColor; string fontSize = ExcelCellProperties.FontSize; string formula = ExcelCellProperties.Formula; string text = ExcelCellProperties.Text; ExcelInteropHelper.GetCellProperties("H5"); bgColor = ExcelCellProperties.BackGroundColor; }
public void ThenICanReadExcelCellValuesOfDifferentFormat() { string cellValue = ExcelInteropHelper.ReadExcelCellText(1, 1); Assert.AreEqual("A", cellValue); // Using ColumnName cellValue = ExcelInteropHelper.ReadExcelCellText(1, "A"); Assert.AreEqual("A", cellValue); // Using blank ColumnName cellValue = ExcelInteropHelper.ReadExcelCellText(1); Assert.AreEqual("A", cellValue); cellValue = ExcelInteropHelper.ReadExcelCellText(2, 1); Assert.AreEqual("2.001", cellValue); cellValue = ExcelInteropHelper.ReadExcelCellText(3, 1); Assert.AreEqual("5", cellValue); cellValue = ExcelInteropHelper.ReadExcelCellText(4, 1); Assert.AreEqual("ABC", cellValue); cellValue = ExcelInteropHelper.ReadExcelCellText(5, 1); Assert.AreEqual("XYZ", cellValue); // This is using formula //cellValue = ExcelInteropHelper.ReadExcelCellText(6, 1); //Assert.AreEqual("5", cellValue); cellValue = ExcelInteropHelper.ReadExcelCellText(7, 1); Assert.AreEqual("₹ 23.75", cellValue); cellValue = ExcelInteropHelper.ReadExcelCellText(8, 1); Assert.AreEqual("12/15/2019", cellValue); cellValue = ExcelInteropHelper.ReadExcelCellText(9, 1); Assert.AreEqual("December 15, 2019", cellValue); cellValue = ExcelInteropHelper.ReadExcelCellText(10, 1); Assert.AreEqual("8:52:48 AM", cellValue); cellValue = ExcelInteropHelper.ReadExcelCellText(11, 1); Assert.AreEqual("76.90%", cellValue); cellValue = ExcelInteropHelper.ReadExcelCellText(12, 1); Assert.AreEqual("", cellValue); }
public void ThenICanWriteToExcel() { ExcelInteropHelper.WriteExcel(1, 9, "test"); //ExcelInteropHelper.SaveExcel(); // 1. Single values ExcelInteropHelper.WriteExcel("I2", "test1"); //ExcelInteropHelper.CloseExcel(); // 2. Multidimentional Arrays string[,] rangeValues = new string[3, 3] { { "a", "b", "c" }, { "d", "e", "f" }, { "g", "h", "i" } }; ExcelInteropHelper.WriteExcel("I3:K5", rangeValues); //ExcelInteropHelper.CloseExcel(); // When values are of dynamic range, mention start cell only ExcelInteropHelper.WriteExcel("I6", rangeValues); rangeValues = new string[3, 1] { { "a" }, { "d" }, { "g" } }; // When values are of dynamic range, mention start cell only ExcelInteropHelper.WriteExcel("I9", rangeValues); // 3. One dimensional array (single column) string[] rangeValues1 = new string[3] { "a", "b", "c" }; ExcelInteropHelper.WriteExcel("I12", rangeValues1); // Always use CloseExcel at end of all oprations ExcelInteropHelper.CloseExcel(); }
public void GivenIHaveValidWorkbook() { ExcelInteropHelper.ActivateSheet(); }
public TestExcelFeaturesSteps() { excelInterop = new ExcelInteropHelper(xlWorkbookPath); }
public void ThenICanGetAllExcelCellFormula() { string actualFormula = ExcelInteropHelper.ReadExcelFormula(6, 1); Assert.AreEqual("=A3/A2", actualFormula); }
public void ThenIShouldGetActiveWorkheetName() { string activeSheetName = ExcelInteropHelper.GetActiveSheetName(); AssertHelpers.AssertEquals("Test1", activeSheetName); }
public void ThenICanSetExcelCellBorder() { ExcelInteropHelper.SetBorderAround("A1:A15"); ExcelInteropHelper.CloseExcel(); }
public void ThenICanGetAllExcelCellBoldProperty() { ExcelInteropHelper.SetExcelRangeProperty("A1:A15", ExcelCellProperties.CellFontBold, "Bold"); ExcelInteropHelper.CloseExcel(); }
public void GivenIHaveMacroEnabledWorkbook() { ExcelInteropHelper.ActivateSheet(); }
public ExcelMacroSteps() { excelInterop = new ExcelInteropHelper(xlWorkbookPath); }
public void ThenICanReadChartValues() { ExcelInteropHelper.ActivateSheet("IP (1) Process design or execut"); ExcelInteropHelper.ReadExcelChart(); }
public void ThenICanRunMacro() { ExcelInteropHelper.RunExcelMacro(new Object[] { macroName }); }
public void ThenICanGetAllExcelCellFontColor() { ExcelInteropHelper.SetExcelRangeProperty("A1:A15", ExcelCellProperties.CellFontColor, "#FF0000"); ExcelInteropHelper.CloseExcel(); }