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