Beispiel #1
0
        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;
            }
        }
Beispiel #2
0
 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);
 }
Beispiel #3
0
 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);
        }
Beispiel #5
0
        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();
        }
Beispiel #7
0
 public static IXLRange Value <T>(this IXLRange cell, T value)
 {
     cell.ShareString = false;
     cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
     cell.SetValue(value);
     return(cell);
 }
Beispiel #8
0
        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);
            }
        }
Beispiel #9
0
        private void ProcessRangeRemoved(IXLRange range)
        {
            var entry = _dataValidationIndex.GetIntersectedRanges((XLRangeAddress)range.RangeAddress)
                        .SingleOrDefault(e => Equals(e.RangeAddress, range.RangeAddress));

            _dataValidationIndex.Remove(entry.RangeAddress);
        }
Beispiel #10
0
        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");
        }
Beispiel #13
0
        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");
        }
Beispiel #18
0
        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();
        }
Beispiel #24
0
 public void Remove(IXLRange range)
 {
     if (GetRangeIndex(range.Worksheet).Remove(range))
     {
         Count--;
     }
 }
Beispiel #25
0
        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");
        }
Beispiel #26
0
        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));
        }
Beispiel #30
0
        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;
        }
Beispiel #32
0
        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;
        }
Beispiel #33
0
        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.
                }
            }
        }
Beispiel #34
0
 public CellRangeReference(IXLRange range, XLCalcEngine ce)
 {
     _range = range;
     _ce = ce;
 }
Beispiel #35
0
 public void Remove(IXLRange range)
 {
     _rangeList.Remove(range.ToString());
 }
Beispiel #36
0
 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;
 }
Beispiel #46
0
 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 = "Выберите из списка";
        }
Beispiel #48
-1
        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;
        }