private void BtnBrowse_Click(object sender, EventArgs e)
{
openFileDialog1.Filter = "Excel Dosyası |*.xlsx";
DialogResult dialogResult = openFileDialog1.ShowDialog();
if (dialogResult == DialogResult.OK)
{
string filePath = openFileDialog1.FileName;
IXLWorkbook workbook = new XLWorkbook(filePath);
IXLWorksheet worksheet = workbook.Worksheet("Veriler");
IXLRange range = worksheet.RangeUsed();
List <Person> people = new List <Person>();
int lastRow = range.RowCount();
foreach (IXLRangeRow item in range.Rows(2, lastRow))
{
Person person = new Person(
Convert.ToInt32(item.Cell("A").Value),
item.Cell("B").Value.ToString(),
(DateTime)item.Cell("C").Value
);
people.Add(person);
}
DtgPersonList.DataSource = people;
TxtPath.Text = filePath;
}
}
private void SetFormat(IXLRange numbers, double sla)
{
numbers.AddConditionalFormat().WhenGreaterThan(8 * sla).Font.SetFontColor(XLColor.OrangeRed);
numbers.AddConditionalFormat().WhenGreaterThan(4 * sla).Font.SetFontColor(XLColor.MediumRedViolet);
numbers.AddConditionalFormat().WhenGreaterThan(2 * sla).Font.SetFontColor(XLColor.RoyalBlue);
numbers.AddConditionalFormat().WhenEqualOrLessThan(2 * sla).Font.SetFontColor(XLColor.SeaGreen);
}
private void CellBorder(IXLRange cells)
{
foreach (var item in cells.Cells())
{
item.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
}
/// <summary>
/// Selects random answers from the given answer range, skipping the excludedIndex.
/// </summary>
/// <param name="answers">The range to select an answer from.</param>
/// <param name="excludedIndex">The cell index to skip (the correct answer that should not be included in the random ones).</param>
/// <param name="count">The count of answers to select</param>
/// <returns>A list of answer strings.</returns>
public static List <String> SelectRandomAnswers(IXLRange answers, int excludedIndex, int count)
{
var output = new List <string>();
var exclusions = new List <int>()
{
excludedIndex
};
//Get range for random
int min = answers.FirstCellUsed().Address.RowNumber;
int max = answers.LastCellUsed().Address.RowNumber;
//iterate until we have the desired amount of results
while (output.Count < count)
{
int selectedIndex = Random.Next(min, max);
//Skip the correct answer, and avoid duplicate random answers
if (exclusions.Contains(selectedIndex))
{
continue;
}
output.Add(answers.Cell(selectedIndex, 1).GetValue <string>());
exclusions.Add(selectedIndex);
}
return(output);
}
void WriteTableDataCell(Xbrl.Taxonomy.Dts dts, Xbrl.Table.Layout.Cell cell, IXLRange range)
{
var facts = cell.Facts;
if (facts.Count == 0)
{
var constraintSet = cell.ConstraintSet;
var concept = constraintSet.Concept?.Concept as Xbrl.Taxonomy.Item;
if (concept != null && !dts.DimensionalRelationshipSet.IsDimensionallyValid(concept, constraintSet))
{
Style.ApplyDataInvalidCellFormat(range);
}
else
{
Style.ApplyDataEmptyCellFormat(range);
}
}
else
{
if (facts.Count > 1)
{
Console.WriteLine("Warning: Cell contains multiple facts, only first is displayed");
}
var item = facts[0] as Xbrl.Item;
if (item != null && !item.XsiNil)
{
WriteTableDataCell(dts, item, range);
}
}
}
private void ParseTags(IXLRange range)
{
var innerRanges = range.GetContainingNames().ToArray();
var cells = from c in _cells
let value = c.GetString()
where (value.StartsWith("<<") || value.EndsWith(">>")) &&
!innerRanges.Any(nr => { using (var r = nr.Ranges) using (var cr = c.XLCell.AsRange()) return(r.Contains(cr)); })
select c;
foreach (var cell in cells)
{
OptionTag[] tags;
string newValue;
if (cell.CellType == TemplateCellType.Formula)
{
tags = _tagsEvaluator.Parse(cell.Formula, range, cell, out newValue);
cell.Formula = newValue;
}
else
{
tags = _tagsEvaluator.Parse(cell.GetString(), range, cell, out newValue);
cell.Value = newValue;
}
if (cell.Row > _rowCnt)
{
_rangeTags.AddRange(tags);
}
else
{
_tags.AddRange(tags);
}
}
_rangeOption = _rangeTags.GetAll <RangeOptionTag>().FirstOrDefault();
}
public static IXLRange Value <T>(this IXLRange cell, T value)
{
cell.ShareString = false;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
cell.SetValue(value);
return(cell);
}
public void ParseTags(IXLRange range, string rangeName)
{
var innerRanges = range.GetContainingNames().Where(nr => _variables.ContainsKey(nr.Name)).ToArray();
var cellsUsed = range.CellsUsed()
.Where(c => !c.HasFormula && !innerRanges.Any(nr => nr.Ranges.Contains(c.AsRange())))
.ToArray();
var cells = from c in cellsUsed
let value = c.GetString()
where TagExtensions.HasTag(value)
select c;
if (!_tags.ContainsKey(rangeName))
{
_tags.Add(rangeName, new TagsList(_errors));
}
foreach (var cell in cells)
{
string value = cell.GetString();
OptionTag[] tags;
string newValue;
var templateCell = new TemplateCell(cell.Address.RowNumber, cell.Address.ColumnNumber, cell);
if (value.StartsWith("&="))
{
tags = _tagsEvaluator.Parse(value.Substring(2), range, templateCell, out newValue);
cell.FormulaA1 = newValue;
}
else
{
tags = _tagsEvaluator.Parse(value, range, templateCell, out newValue);
cell.Value = newValue;
}
_tags[rangeName].AddRange(tags);
}
}
private void ProcessRangeRemoved(IXLRange range)
{
var entry = _dataValidationIndex.GetIntersectedRanges((XLRangeAddress)range.RangeAddress)
.SingleOrDefault(e => Equals(e.RangeAddress, range.RangeAddress));
_dataValidationIndex.Remove(entry.RangeAddress);
}
private bool VerifyTotal()
{
bool res = true;
int sheet = 1;
IXLRange UsedRange = wb.Worksheet(sheet).RangeUsed();
for (int i = headerin + 1; i <= UsedRange.LastRow().RowNumber(); i++)
{
decimal contrato = Math.Round(Decimal.Parse(wb.Worksheet(sheet).Cell(i, 11).Value.ToString()), 2);
decimal IUE = Math.Round(Decimal.Parse(wb.Worksheet(sheet).Cell(i, 12).Value.ToString()), 2);
decimal IT = Math.Round(Decimal.Parse(wb.Worksheet(sheet).Cell(i, 13).Value.ToString()), 2);
decimal total = Math.Round(Decimal.Parse(wb.Worksheet(sheet).Cell(i, 14).Value.ToString()), 2);
if (contrato - IUE - IT != total)
{
res = false;
paintXY(11, i, XLColor.Red, "Este valor no cuadra (Contrato - IUE - IT != Monto a Pagar)");
}
}
valid = valid && res;
if (!res)
{
addError("ValorNoValido", "Monto a Pagar no cuadra.", false);
}
return(res);
}
public void TestRenderEmptyDataReader()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(2, 2, 2, 6);
range.AddToNamed("TestRange", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{di:Id}";
ws.Cell(2, 3).Value = "{di:Name}";
ws.Cell(2, 4).Value = "{di:IsVip}";
ws.Cell(2, 5).Value = "{di:Description}";
ws.Cell(2, 6).Value = "{di:Type}";
var panel = new ExcelDataSourcePanel("m:DataProvider:GetEmptyDataReader()", ws.NamedRange("TestRange"), report, report.TemplateProcessor);
panel.Render();
Assert.IsNull(panel.ResultRange);
Assert.AreEqual(0, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(0, ws.NamedRanges.Count());
Assert.AreEqual(0, ws.Workbook.NamedRanges.Count());
Assert.AreEqual(1, ws.Workbook.Worksheets.Count);
//report.Workbook.SaveAs("test.xlsx");
}
public void LoadTemplate(string fileTemplate)
{
var fileName = Path.Combine(TestConstants.TemplatesFolder, fileTemplate);
_workbook = new XLWorkbook(fileName);
_rng = _workbook.Range("range1");
}
public void Range1()
{
var wb = new XLWorkbook();
IXLRange range = wb.Range("ABC");
Assert.IsNull(range);
}
public void TestRenderDataReader()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(2, 2, 2, 9);
range.AddToNamed("TestRange", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{di:Id}";
ws.Cell(2, 3).Value = "{di:Name}";
ws.Cell(2, 4).Value = "{di:IsVip}";
ws.Cell(2, 5).Value = "{di:Description}";
ws.Cell(2, 6).Value = "{di:Type}";
ws.Cell(2, 7).FormulaA1 = "=ROW()";
ws.Cell(2, 8).FormulaA1 = "=SUM(B2,F2)";
ws.Cell(2, 9).FormulaA1 = "=SUM(B$2,F$2)";
var panel = new ExcelDataSourcePanel("m:DataProvider:GetAllCustomersDataReader()", ws.NamedRange("TestRange"), report, report.TemplateProcessor);
panel.Render();
Assert.AreEqual(ws.Range(2, 2, 4, 9), panel.ResultRange);
ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(DataSourcePanelDataReaderRenderTest),
nameof(TestRenderDataReader)), ws.Workbook);
//report.Workbook.SaveAs("test.xlsx");
}
public bool ValidateNoNegative(int col, int sheet = 1)
{
bool res = true;
string comment = "Este Valor no puede ser negativo.";
IXLRange UsedRange = wb.Worksheet(sheet).RangeUsed();
var l = UsedRange.LastRow().RowNumber();
for (int i = headerin + 1; i <= UsedRange.LastRow().RowNumber(); i++)
{
double nz = -1;
if (Double.TryParse(wb.Worksheet(sheet).Cell(i, col).Value.ToString(), out nz))
{
if (nz < 0)
{
res = false;
paintXY(col, i, XLColor.Red, comment);
}
}
}
valid = valid && res;
if (!res)
{
addError("Valor negativo", "Existen columnas que no pueden ser negativas, ni iguales a 0");
}
return(res);
}
internal RangeTemplate(string name, IXLRange range, TempSheetBuffer buff, TemplateErrors errors, IDictionary <string, object> globalVariables)
{
_rowRange = range;
_cells = new TemplateCells(this);
_tagsEvaluator = new TagsEvaluator();
var wb = _rowRange.Worksheet.Workbook;
_buff = buff;
_errors = errors;
_globalVariables = globalVariables;
_tags = new TagsList(_errors);
_rangeTags = new TagsList(_errors);
Name = name;
Source = name;
var rangeName = name + "_tpl";
if (range.Worksheet.NamedRanges.TryGetValue(rangeName, out var namedRange) || wb.NamedRanges.TryGetValue(rangeName, out namedRange))
{
namedRange.Add(range);
}
else
{
range.Worksheet.NamedRanges.Add(rangeName, range);
}
_evaluator = new FormulaEvaluator();
}
public void TestPanelWithNoData()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 1, 5);
range.AddToNamed("Test", XLScope.Worksheet);
ws.Cell(1, 1).Value = "Plain text";
ws.Cell(1, 2).Value = "{Sum(di:Sum)}";
ws.Cell(1, 3).Value = "{ Custom(DI:Sum, CustomAggregation, PostAggregation) }";
ws.Cell(1, 4).Value = "{Min(di:Sum)}";
ws.Cell(1, 5).Value = "Text1 {count(di:Name)} Text2 {avg(di:Sum, , PostAggregationRound)} Text3 {Max(di:Sum)}";
var panel = new ExcelTotalsPanel("m:DataProvider:GetEmptyIEnumerable()", ws.NamedRange("Test"), report, report.TemplateProcessor);
panel.Render();
Assert.AreEqual(range, panel.ResultRange);
ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(TotalsPanelRenderTest),
nameof(TestPanelWithNoData)), ws.Workbook);
//report.Workbook.SaveAs("test.xlsx");
}
public IXLRange RenderTotals(IXLRange totalsRange)
{
string rangeName = $"DynamicPanelTotals_{Guid.NewGuid():N}";
totalsRange.AddToNamed(rangeName, XLScope.Worksheet);
if (_data is IDataReader dr && dr.IsClosed)
{
if (_isDataReceivedDirectly)
{
throw new InvalidOperationException("Cannot enumerate IDataReader twice. Cache data and try again.");
}
_data = _templateProcessor.GetValue(_dataSourceTemplate);
}
var totalsPanel = new ExcelTotalsPanel(_data, Range.Worksheet.NamedRange(rangeName), _report, _templateProcessor)
{
ShiftType = ShiftType,
Type = Type,
BeforeRenderMethodName = BeforeTotalsRenderMethodName,
AfterRenderMethodName = AfterTotalsRenderMethodName,
};
totalsPanel.Render();
return(totalsPanel.ResultRange);
}
public void TestPanelRenderEvents()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(2, 2, 2, 3);
range.AddToNamed("TestRange", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{di:Name}";
ws.Cell(2, 3).Value = "{di:Date}";
var panel = new ExcelDataSourcePanel("m:DataProvider:GetIEnumerable()", ws.NamedRange("TestRange"), report, report.TemplateProcessor)
{
BeforeRenderMethodName = "TestExcelDataSourcePanelBeforeRender",
AfterRenderMethodName = "TestExcelDataSourcePanelAfterRender",
BeforeDataItemRenderMethodName = "TestExcelDataItemPanelBeforeRender",
AfterDataItemRenderMethodName = "TestExcelDataItemPanelAfterRender",
};
panel.Render();
Assert.AreEqual(ws.Range(2, 2, 4, 2), panel.ResultRange);
ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(DataSourcePanelIEnumerableRenderTest),
nameof(TestPanelRenderEvents)), ws.Workbook);
//report.Workbook.SaveAs("test.xlsx");
}
public bool ValidateHBIsNotZero(int col, int sheet = 1)
{
bool res = true;
string comment = "Este Valor no puede ser menor o igual 0.";
IXLRange UsedRange = wb.Worksheet(sheet).RangeUsed();
var l = UsedRange.LastRow().RowNumber();
for (int i = headerin + 1; i <= UsedRange.LastRow().RowNumber(); i++)
{
double nz = -1;
// busca en todas las celdas de la columna enviada, y revisa que ninguna sea menor o igual a 0
if (Double.TryParse(wb.Worksheet(sheet).Cell(i, col).Value.ToString(), out nz))
{
if (nz <= 0)
{
// si se cumple la condicion, la pinta y marca el error de la variable comment
res = false;
paintXY(col, i, XLColor.Red, comment);
}
}
}
valid = valid && res;
if (!res)
{
addError("Valor de haber básico menor o igual a 0", "La columna del haber básico no puede ser menor o igual a 0");
}
return(res);
}
public bool ValidatenoZero(int sheet = 1)
{
int tipo = 20;
int nozero = 22;
bool res = true;
string comment = "Este Valor no puede ser cero.";
IXLRange UsedRange = wb.Worksheet(sheet).RangeUsed();
var l = UsedRange.LastRow().RowNumber();
for (int i = headerin + 1; i <= UsedRange.LastRow().RowNumber(); i++)
{
int nz = -1;
if (wb.Worksheet(sheet).Cell(i, tipo).Value.ToString() != "TH" && Int32.TryParse(wb.Worksheet(sheet).Cell(i, nozero).Value.ToString(), out nz))
{
if (nz == 0)
{
res = false;
paintXY(nozero, i, XLColor.Red, comment);
}
}
}
valid = valid && res;
if (!res)
{
addError("Valor cero", "Existen columnas que no pueden ser cero");
}
return(res);
}
public static IXLRange SetStyle(this IXLRange range, StyleConfig styleConfig)
{
if (styleConfig.Bold.HasValue)
{
range.Style.Font.SetBold(styleConfig.Bold.Value);
}
if (styleConfig.FontColor != null)
{
range.Style.Font.SetFontColor(styleConfig.FontColor);
}
if (styleConfig.BackgroundColor != null)
{
range.Style.Fill.SetBackgroundColor(styleConfig.BackgroundColor);
}
if (styleConfig.HorizontalAlignment.HasValue)
{
range.Style.Alignment.SetHorizontal(styleConfig.HorizontalAlignment.Value);
}
if (styleConfig.OutsideBorder.HasValue)
{
range.Style.Border.SetOutsideBorder(styleConfig.OutsideBorder.Value ? XLBorderStyleValues.Thick : XLBorderStyleValues.None);
}
if (styleConfig.InsideBorder.HasValue)
{
range.Style.Border.SetInsideBorder(styleConfig.InsideBorder.Value ? XLBorderStyleValues.Thin : XLBorderStyleValues.None);
}
return(range);
}
private void ParseTags(IXLRange range)
{
var innerRanges = range.GetContainingNames().ToArray();
var cells = from c in _cells
let value = c.GetString()
where TagExtensions.HasTag(value) &&
!innerRanges.Any(nr => nr.Ranges.Contains(c.XLCell.AsRange()))
select c;
foreach (var cell in cells)
{
OptionTag[] tags;
string newValue;
if (cell.CellType == TemplateCellType.Formula)
{
tags = _tagsEvaluator.Parse(cell.Formula, range, cell, out newValue);
cell.Formula = newValue;
}
else
{
tags = _tagsEvaluator.Parse(cell.GetString(), range, cell, out newValue);
cell.Value = newValue;
}
if (cell.Row > 1 && cell.Row == _rowCnt)
{
_rangeTags.AddRange(tags);
}
else
{
_tags.AddRange(tags);
}
}
_rangeOption = _rangeTags.GetAll <RangeOptionTag>().Union(_tags.GetAll <RangeOptionTag>()).FirstOrDefault();
}
public void Remove(IXLRange range)
{
if (GetRangeIndex(range.Worksheet).Remove(range))
{
Count--;
}
}
public void TestHorizontalInVerticalPanelsGrouping()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange parentRange = ws.Range(2, 2, 4, 3);
parentRange.AddToNamed("ParentRange", XLScope.Worksheet);
IXLRange child = ws.Range(2, 3, 4, 3);
child.AddToNamed("ChildRange", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{di:Name}";
ws.Cell(3, 3).Value = "{di:Field1}";
ws.Cell(4, 3).Value = "{di:Field2}";
var parentPanel = new ExcelDataSourcePanel("m:DataProvider:GetIEnumerable()", ws.NamedRange("ParentRange"), report, report.TemplateProcessor);
var childPanel = new ExcelDataSourcePanel("m:DataProvider:GetChildIEnumerable(di:Name)", ws.NamedRange("ChildRange"), report, report.TemplateProcessor)
{
Parent = parentPanel,
Type = PanelType.Horizontal,
};
parentPanel.Children = new[] { childPanel };
parentPanel.Render();
Assert.AreEqual(ws.Range(2, 2, 10, 5), parentPanel.ResultRange);
ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(DataSourcePanelRender_WithGrouping_MixedPanels_Test),
nameof(TestHorizontalInVerticalPanelsGrouping)), ws.Workbook);
//report.Workbook.SaveAs("test.xlsx");
}
public void InsertData1()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLRange range = ws.Cell(2, 2).InsertData(new[] { "a", "b", "c" });
Assert.AreEqual("'Sheet1'!B2:B4", range.ToString());
}
public static void GetCrossRangeStartEndPositions(IXLRange range1, IXLRange range2
, out CellPosition start_pos, out CellPosition end_pos)
{
start_pos = null;
end_pos = null;
var first_column_1 = range1.FirstColumn().ColumnNumber();
var last_column_1 = range1.LastColumn().ColumnNumber();
var first_row_1 = range1.FirstRow().RowNumber();
var last_row_1 = range1.LastRow().RowNumber();
var first_column_2 = range2.FirstColumn().ColumnNumber();
var last_column_2 = range2.LastColumn().ColumnNumber();
var first_row_2 = range2.FirstRow().RowNumber();
var last_row_2 = range2.LastRow().RowNumber();
if ((first_column_1 != last_column_1 && first_row_1 != last_row_1) ||
(first_column_2 != last_column_2 && first_row_2 != last_row_2))
{
throw new Exception("One or two spans are more than 1 cell wide in both directions.");
}
int start_position_column = 0;
int end_position_column = 0;
int start_position_row = 0;
int end_position_row = 0;
if (first_column_1 == last_column_1)
{
start_position_row = first_row_1;
end_position_row = last_row_1;
}
if (first_column_2 == last_column_2)
{
start_position_row = first_row_2;
end_position_row = last_row_2;
}
if (first_row_1 == last_row_1)
{
start_position_column = first_column_1;
end_position_column = last_column_1;
}
if (first_row_2 == last_row_2)
{
start_position_column = first_column_2;
end_position_column = last_column_2;
}
start_pos = new CellPosition()
{
Column = start_position_column, Row = start_position_row
};
end_pos = new CellPosition()
{
Column = end_position_column, Row = end_position_row
};
}
public bool ValidateLiquidoPagable(int sheet = 1)
{
int i1 = 6, i2 = 7, i3 = 8, i4 = 9, i5 = 10, i6 = 11;
int d1 = 14, d2 = 15, d3 = 16;
int errortg = 12, errortd = 17, errorlp = 18;
bool res = true;
IXLRange UsedRange = wb.Worksheet(sheet).RangeUsed();
for (int i = headerin + 1; i <= UsedRange.LastRow().RowNumber(); i++)
{
decimal in1 = 0, in2 = 0, in3 = 0, in4 = 0, in5 = 0, in6 = 0, ti = 0, lp = 0;
decimal de1 = 0, de2 = 0, de3 = 0, td = 0;
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, i1).Value.ToString(), out in1);
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, i2).Value.ToString(), out in2);
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, i3).Value.ToString(), out in3);
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, i4).Value.ToString(), out in4);
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, i5).Value.ToString(), out in5);
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, i6).Value.ToString(), out in6);
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, d1).Value.ToString(), out de1);
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, d2).Value.ToString(), out de2);
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, d3).Value.ToString(), out de3);
var ingresos = Math.Round(in1, 2) + Math.Round(in2, 2) + Math.Round(in3, 2) + Math.Round(in4, 2) + Math.Round(in5, 2) + Math.Round(in6, 2);
var descuentos = Math.Round(de1, 2) + Math.Round(de2, 2) + Math.Round(de3, 2);
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, 12).Value.ToString(), out ti);
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, 17).Value.ToString(), out td);
Decimal.TryParse(wb.Worksheet(sheet).Cell(i, 18).Value.ToString(), out lp);
if (ingresos != ti)
{
res = false;
paintXY(errortg, i, XLColor.Red, "No cuadran Ingresos. la suma sale: " + ingresos);
addError("No cuadran Ingresos", "La suma se calculó: " + ingresos + " se encontró " + ti);
}
if (descuentos != td)
{
res = false;
paintXY(errortd, i, XLColor.Red, "No cuadran Descuentos. la suma sale: " + descuentos);
addError("No cuadran Descuentos", "La suma se calculó: " + descuentos + " se encontró " + td);
}
var dif = ingresos - descuentos;
if (dif != lp)
{
res = false;
paintXY(errorlp, i, XLColor.Red, "No cuadran Liquido Pagable. la suma sale: " + (ingresos - descuentos));
addError("No cuadran Liquido Pagable", "La suma se calculó: " + dif + " se encontró " + lp);
}
}
valid = valid && res;
return(res);
}
public void TestCreateSimplePanel()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
IXLRange range = ws.Range(ws.Cell(1, 1), ws.Cell(2, 2));
range.AddToNamed("s_Test", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("s_Test");
var report = new object();
var templateProcessor = Substitute.For <ITemplateProcessor>();
var parseSettings = new PanelParsingSettings
{
SimplePanelPrefix = "s",
PanelPrefixSeparator = "_",
};
var factory = new ExcelPanelFactory(report, templateProcessor, parseSettings);
var panel = (ExcelPanel)factory.Create(namedRange, new Dictionary <string, string>
{
[nameof(ExcelPanel.Type)] = PanelType.Horizontal.ToString(),
[nameof(ExcelPanel.ShiftType)] = ShiftType.Row.ToString(),
[nameof(ExcelPanel.RenderPriority)] = "5",
[nameof(ExcelPanel.BeforeRenderMethodName)] = "BeforeRenderMethodName",
[nameof(ExcelPanel.AfterRenderMethodName)] = "AfterRenderMethodName",
});
Assert.AreEqual(PanelType.Horizontal, panel.Type);
Assert.AreEqual(ShiftType.Row, panel.ShiftType);
Assert.AreEqual(5, panel.RenderPriority);
Assert.AreEqual("BeforeRenderMethodName", panel.BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethodName", panel.AfterRenderMethodName);
Assert.AreEqual(0, panel.Children.Count);
Assert.IsNull(panel.Parent);
Assert.AreEqual(namedRange.Ranges.First(), panel.Range);
Assert.AreSame(report, panel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(panel));
Assert.AreSame(templateProcessor, panel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(panel));
namedRange.Delete();
range.AddToNamed("SS--Test", XLScope.Workbook);
namedRange = wb.NamedRange("SS--Test");
parseSettings.SimplePanelPrefix = "ss";
parseSettings.PanelPrefixSeparator = "--";
factory = new ExcelPanelFactory(report, templateProcessor, parseSettings);
panel = (ExcelPanel)factory.Create(namedRange, null);
Assert.IsInstanceOf <ExcelPanel>(panel);
Assert.AreEqual(PanelType.Vertical, panel.Type);
Assert.AreEqual(ShiftType.Cells, panel.ShiftType);
Assert.AreEqual(0, panel.RenderPriority);
Assert.AreEqual(0, panel.Children.Count);
Assert.IsNull(panel.BeforeRenderMethodName);
Assert.IsNull(panel.AfterRenderMethodName);
Assert.IsNull(panel.Parent);
Assert.AreEqual(namedRange.Ranges.First(), panel.Range);
Assert.AreSame(report, panel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(panel));
Assert.AreSame(templateProcessor, panel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(panel));
}
private void Process(IXLRange root, GroupTag[] groups, bool summaryAbove, SubtotalSummaryFunc[] funcs, bool disableGrandTotal)
{
var groupRow = root.LastRow();
// DoGroups
var level = 0;
var r = root.Offset(0, 0, root.RowCount() - 1, root.ColumnCount());
using (var subtotal = new Subtotal(r, summaryAbove))
{
if (TotalLabel != null)
{
subtotal.TotalLabel = TotalLabel;
}
if (GrandLabel != null)
{
subtotal.GrandLabel = GrandLabel;
}
if (!disableGrandTotal)
{
var total = subtotal.AddGrandTotal(funcs);
total.SummaryRow.Cell(2).Value = total.SummaryRow.Cell(1).Value;
total.SummaryRow.Cell(1).Value = null;
level++;
}
foreach (var g in groups.OrderBy(x => x.Column))
{
Func <string, string> labFormat = null;
if (!string.IsNullOrEmpty(g.LabelFormat))
{
labFormat = title => string.Format(LabelFormat, title);
}
subtotal.GroupBy(g.Column, g.DisableSubtotals ? new SubtotalSummaryFunc[0] : funcs, g.PageBreaks, labFormat);
g.Level = ++level;
}
foreach (var g in groups.Where(x => x.IsWithHeader).OrderBy(x => x.Column))
{
subtotal.AddHeaders(g.Column);
}
var gr = groups.Union(new[] { new GroupTag {
Column = 1, Level = 1
} })
.ToDictionary(x => x.Level, x => x);
foreach (var subGroup in subtotal.Groups.OrderBy(x => x.Column).Reverse())
{
var groupTag = gr[subGroup.Level];
FormatHeaderFooter(subGroup, groupRow);
GroupRender(subGroup, groupTag);
}
}
// Rem DoDeleteSpecialRow
root.LastRow().Delete(XLShiftDeletedCells.ShiftCellsUp);
}
public Boolean ContainsSingle(IXLRange range)
{
Int32 count = 0;
foreach (var xlDataValidation in _dataValidations.Where(dv => dv.Ranges.Contains(range)))
{
count++;
if (count > 1) return false;
}
return count == 1;
}
private static void SetupBorders(IXLRange range)
{
range.FirstRow().Cell(1).Style.Border.TopBorder = XLBorderStyleValues.None;
range.FirstRow().Cell(2).Style.Border.TopBorder = XLBorderStyleValues.Thick;
range.FirstRow().Cell(3).Style.Border.TopBorder = XLBorderStyleValues.Double;
range.LastRow().Cell(1).Style.Border.BottomBorder = XLBorderStyleValues.None;
range.LastRow().Cell(2).Style.Border.BottomBorder = XLBorderStyleValues.Thick;
range.LastRow().Cell(3).Style.Border.BottomBorder = XLBorderStyleValues.Double;
range.FirstColumn().Cell(1).Style.Border.LeftBorder = XLBorderStyleValues.None;
range.FirstColumn().Cell(2).Style.Border.LeftBorder = XLBorderStyleValues.Thick;
range.FirstColumn().Cell(3).Style.Border.LeftBorder = XLBorderStyleValues.Double;
range.LastColumn().Cell(1).Style.Border.RightBorder = XLBorderStyleValues.None;
range.LastColumn().Cell(2).Style.Border.RightBorder = XLBorderStyleValues.Thick;
range.LastColumn().Cell(3).Style.Border.RightBorder = XLBorderStyleValues.Double;
}
private void prepDashboardSheets(IXLWorksheet sheet)
{
/*.
* can use htis for both types of sheets.
* Regular: List.Count -1 (end of list: should be Month) List.Count -2 (last value: Should be provider name)
* Diabetes: List.Count -1 (end of list: Month) List.Count -3 (blank space between provider name an dmonth on this one)
*/
_worksheet = sheet;
//use this one to keep track of provider name and row number. then send it to metrics to dashboard and do accordignly
Dictionary<String, IXLRangeRow> providerRows = new Dictionary<String, IXLRangeRow>();
if (_worksheet != null)
{
var firstCell = _worksheet.FirstCellUsed();
var lastCell = _worksheet.LastCellUsed();
_worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);
if (_worksheet_range != null)
{
int nRows = _worksheet_range.RowCount();
int nCols = _worksheet_range.ColumnCount();
for (int i = 1; i < nRows + 1; i++)
{
var row = _worksheet_range.Row(i);
var newRow = _worksheet_range.Row(i + 1);
string value = row.Cell(1).Value as string;
if (value != null)
{
foreach (string provider in providers)
{
if (value.Contains(provider))
{
if (_worksheet == _dashboard.Worksheet(2))//add a new row for the depression sheets
{
newRow = _worksheet_range.Row(i + 3);
newRow.InsertRowsBelow(1);
var blankRow = _worksheet_range.Row(i + 4);
blankRow.Style.NumberFormat.NumberFormatId = 0;
providerRows.Add(value, blankRow);
}
else //add a new row for every other sheet in the dashboard: Asthma, Diabetes, Cardiovascular, Preventive
{
newRow = _worksheet_range.Row(i + 2);//this gets us int he right area and then insert the row above
newRow.InsertRowsBelow(1); //try to insert rows after we have metrics and tehn insert metrics into cells then insert row
var blankRow = _worksheet_range.Row(i + 3);
blankRow.Style.NumberFormat.NumberFormatId = 0;
providerRows.Add(value, blankRow);
}
break; //break out of the foreach provider loop, we already found one, we wont find another match, time to go to the next row instead
}
}
}
}
MetricsToDashboard(providerRows, _worksheet);//figure out what we need to send to this method , worksheet, provider / row dict etc.
}
}
}
public CellRangeReference(IXLRange range, XLCalcEngine ce)
{
_range = range;
_ce = ce;
}
public void Remove(IXLRange range)
{
_rangeList.Remove(range.ToString());
}
public IXLRanges Add(IXLRange range)
{
var ranges = new XLRanges {range};
return Add(ranges);
}
public void List(IXLRange range, Boolean inCellDropdown)
{
List(range.RangeAddress.ToStringFixed(XLReferenceStyle.A1, true));
}
public void List(IXLRange range)
{
List(range, true);
}
public IXLNamedRange Add(String rangeName, IXLRange range, String comment)
{
var ranges = new XLRanges {range};
return Add(rangeName, ranges, comment);
}
public IXLNamedRange Add(String rangeName, IXLRange range)
{
return Add(rangeName, range, null);
}
public void Delete(IXLRange range)
{
_dataValidations.RemoveAll(dv => dv.Ranges.Contains(range));
}
public void GreaterThan(IXLRange range)
{
dataValidation.Value = range.RangeAddress.ToStringFixed();
dataValidation.Operator = XLOperator.GreaterThan;
}
public void EqualOrLessThan(IXLRange range)
{
dataValidation.Value = range.RangeAddress.ToStringFixed();
dataValidation.Operator = XLOperator.EqualOrLessThan;
}
public void NotBetween(IXLRange minValue, IXLRange maxValue)
{
dataValidation.MinValue = minValue.RangeAddress.ToStringFixed();
dataValidation.MaxValue = maxValue.RangeAddress.ToStringFixed();
dataValidation.Operator = XLOperator.NotBetween;
}
public void NotEqualTo(IXLRange range)
{
dataValidation.Value = range.RangeAddress.ToStringFixed();
dataValidation.Operator = XLOperator.NotEqualTo;
}
public IXLPivotTable AddNew(string name, IXLCell target, IXLRange source)
{
var pivotTable = new XLPivotTable { Name = name, TargetCell = target, SourceRange = source };
_pivotTables.Add(name, pivotTable);
return pivotTable;
}
public void ExcelDataFormatCalcRow(ref IXLWorksheet workSheet, int row, IXLRange deliveryTimeRange, IXLRange protectFactRange)
{
workSheet.Cell(row, 9).DataType = XLCellValues.Number;
workSheet.Cell(row, 9).DataValidation.Decimal.GreaterThan(0);
workSheet.Cell(row, 9).DataValidation.ErrorTitle = "Введите число";
workSheet.Cell(row, 9).DataValidation.ErrorMessage = "Введите число";
workSheet.Cell(row, 10).DataType = XLCellValues.Number;
workSheet.Cell(row, 10).DataValidation.Decimal.GreaterThan(0);
workSheet.Cell(row, 10).DataValidation.ErrorTitle = "Введите число";
workSheet.Cell(row, 10).DataValidation.ErrorMessage = "Введите число";
workSheet.Cell(row, 11).DataType = XLCellValues.Number;
workSheet.Cell(row, 11).DataValidation.Decimal.GreaterThan(0);
workSheet.Cell(row, 11).DataValidation.ErrorTitle = "Введите число";
workSheet.Cell(row, 11).DataValidation.ErrorMessage = "Введите число";
workSheet.Cell(row, 12).DataType = XLCellValues.Number;
workSheet.Cell(row, 12).DataValidation.Decimal.GreaterThan(0);
workSheet.Cell(row, 12).DataValidation.ErrorTitle = "Введите число";
workSheet.Cell(row, 12).DataValidation.ErrorMessage = "Введите число";
var validation = workSheet.Cell(row, 14).SetDataValidation();
validation.AllowedValues = XLAllowedValues.List;
validation.InCellDropdown = true;
validation.Operator = XLOperator.Between;
validation.List(deliveryTimeRange);
validation.ErrorMessage = "Выберите из списка";
validation = workSheet.Cell(row, 15).SetDataValidation();
validation.AllowedValues = XLAllowedValues.List;
validation.InCellDropdown = true;
validation.Operator = XLOperator.Between;
validation.List(protectFactRange);
validation.ErrorMessage = "Выберите из списка";
}
private List<String> ReturnMetricsRow(IXLWorksheet sheet)
{
List<String> metricNames = new List<string>();
_worksheet = sheet;
if (_worksheet != null)
{
var firstCell = _worksheet.FirstCellUsed();
var lastCell = _worksheet.LastCellUsed();
_worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);
if (_worksheet_range != null)
{
int nRows = _worksheet_range.RowCount();
int nCols = _worksheet_range.ColumnCount();
for (int i = 1; i < nRows + 1; i++)
{
var row = _worksheet_range.Row(i);
var newRow = _worksheet_range.Row(i + 1);
string value = row.Cell(1).Value as string;
if (value == "Month")
{
var metricRow = _worksheet_range.Row(i);
for (int x = 1; x <= metricRow.CellCount(); x++)
{
metricNames.Add(metricRow.Cell(x).Value.ToString());
}
break;
}
}
}
}
return metricNames;
}