Example #1
0
        private void ReadChild(ExcelRangeBase cell, ref int row, int level)
        {
            do
            {
                bool isValueTag = !string.IsNullOrEmpty(cell.Offset(row, 6).Text);

                int depth;
                int.TryParse(cell.Offset(row, 1).Text, out depth);

                if (level != 0 && depth <= level)
                {
                    break;
                }

                string tagName = cell.Offset(row, 2 + depth).Text;

                row += 1;

                if (isValueTag)
                {
                    Console.WriteLine("{2}<{0}>{1}</{0}>", tagName, "ValueTag", new string(' ', depth * 4));
                }
                else
                {
                    Console.WriteLine("{1}<{0}>", tagName, new string(' ', depth * 4));
                    ReadChild(cell, ref row, depth);
                    Console.WriteLine("{1}</{0}>", tagName, new string(' ', depth * 4));
                }

            } while (!string.IsNullOrEmpty(cell.Offset(row, 0).Text));
        }
Example #2
0
        /// <summary>
        /// Adds a new named range to the collection.
        /// </summary>
        /// <param name="name">The name of the named range to add.</param>
        /// <param name="range">The range that the named range references.</param>
        /// <returns>The named range that was added to the collection.</returns>
        public ExcelNamedRange Add(string name, ExcelRangeBase range)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }
            ExcelNamedRange namedRange = new ExcelNamedRange(name, this.Workbook, this.Worksheet, range.FullAddress, this.NamedRanges.Count);

            this.NamedRanges.Add(name, namedRange);
            return(namedRange);
        }
Example #3
0
        /// <summary>
        /// Adds a comment that is styled the same as the specified <paramref name="copyComment"/>.
        /// </summary>
        /// <param name="cell">The cell to which the comment is added.</param>
        /// <param name="copyComment">The comment to copy.</param>
        public void Add(ExcelRangeBase cell, ExcelComment copyComment)
        {
            var element = this.CommentXml.CreateElement("comment", ExcelPackage.schemaMain);
            // Make sure the nodes come in order.
            int nextCommentRow    = cell._fromRow;
            int nextCommentColumn = cell._fromCol;

            if (this.Comments.Count == 0 || !this.Worksheet._commentsStore.NextCell(ref nextCommentRow, ref nextCommentColumn))
            {
                this.CommentXml.SelectSingleNode("d:comments/d:commentList", this.NameSpaceManager).AppendChild(element);
            }
            else
            {
                ExcelComment nextComment = this.Comments[Worksheet._commentsStore.GetValue(nextCommentRow, nextCommentColumn)];
                nextComment.CommentHelper.TopNode.ParentNode.InsertBefore(element, nextComment.CommentHelper.TopNode);
            }
            ExcelComment comment = new ExcelComment(this.NameSpaceManager, element, cell);

            comment.RichText = copyComment.RichText;
            // Copy text styling.
            comment.CommentHelper.TopNode.SelectSingleNode(".//d:text", this.NameSpaceManager).InnerXml = copyComment.CommentHelper.TopNode.SelectSingleNode(".//d:text", this.NameSpaceManager).InnerXml;
            string author = copyComment.Author;

            if (string.IsNullOrEmpty(author))
            {
                author = Thread.CurrentPrincipal.Identity.Name;
            }
            comment.Reference = new ExcelAddress(cell._fromRow, cell._fromCol, cell._fromRow, cell._fromCol).Address;
            comment.Author    = author;
            float rowMarginOffset = 0, columnMarginOffset = 0;
            int   rowDirection = comment.Range._fromRow.CompareTo(copyComment.Range._fromRow);
            var   fromRow      = Math.Min(comment.Range._fromRow, copyComment.Range._fromRow);
            var   toRow        = Math.Max(comment.Range._fromRow, copyComment.Range._fromRow);

            for (int i = fromRow; i < toRow; i++)
            {
                rowMarginOffset += (float)this.Worksheet.Row(i).Height;
            }
            int columnDirection = comment.Range._fromCol.CompareTo(copyComment.Range._fromCol);
            var fromColumn      = Math.Min(comment.Range._fromCol, copyComment.Range._fromCol);
            var toColumn        = Math.Max(comment.Range._fromCol, copyComment.Range._fromCol);

            for (int i = fromColumn; i < toColumn; i++)
            {
                columnMarginOffset += (float)this.Worksheet.Column(i).Width;
            }
            this.Comments.Add(comment);
            this.Worksheet._commentsStore.SetValue(cell._fromRow, cell._fromCol, this.Comments.Count - 1);
            // Check if a value exists otherwise add one so it is saved when the cells collection is iterated.
            if (!this.Worksheet.ExistsValueInner(cell._fromRow, cell._fromCol))
            {
                this.Worksheet.SetValueInner(cell._fromRow, cell._fromCol, null);
            }
        }
        public static List <ExcelRow> RowList(this ExcelRangeBase range)
        {
            var list = new List <ExcelRow>();

            for (var i = 1; i <= range.Rows; i++)
            {
                var row = range.Worksheet.Row(range.Start.Row + i - 1);
                list.Add(row);
            }
            return(list);
        }
        public static List <ExcelColumn> ColumnList(this ExcelRangeBase range)
        {
            var list = new List <ExcelColumn>();

            for (var i = 1; i <= range.Columns; i++)
            {
                var col = range.Worksheet.Column(range.Start.Column + i - 1);
                list.Add(col);
            }
            return(list);
        }
Example #6
0
        internal ExcelComment(XmlNamespaceManager ns, XmlNode commentTopNode, ExcelRangeBase cell)
        {
            this.CommentHelper = XmlHelperFactory.Create(ns, commentTopNode);
            var textElem = commentTopNode.SelectSingleNode("d:text", ns);

            if (textElem == null)
            {
                textElem = commentTopNode.OwnerDocument.CreateElement("text", ExcelPackage.schemaMain);
                commentTopNode.AppendChild(textElem);
            }
            this.Range    = cell;
            this.RichText = new ExcelRichTextCollection(ns, textElem);
        }
Example #7
0
        /// <summary>
        /// Recalculate this <paramref name="range"/> with the specified <paramref name="options"/>.
        /// </summary>
        /// <param name="range">The range to be calculated.</param>
        /// <param name="options">Settings for this calculation.</param>
        /// <param name="setResultStyle">Indicates whether or not to set the cell's style based on the calculation result.</param>
        public static void Calculate(this ExcelRangeBase range, ExcelCalculationOption options, bool setResultStyle = false)
        {
            Init(range.myWorkbook);
            var parser = range.myWorkbook.FormulaParser;

            parser.InitNewCalc();
            if (range.IsName)
            {
                range = AddressUtility.GetFormulaAsCellRange(range.Worksheet.Workbook, range.Worksheet, range.Address);
            }
            var dc = DependencyChainFactory.Create(range, options);

            CalcChain(range.myWorkbook, parser, dc, setResultStyle);
        }
Example #8
0
        /// <summary>
        /// Add a new named range
        /// </summary>
        /// <param name="Name">The name</param>
        /// <param name="Range">The range</param>
        /// <returns></returns>
        public ExcelNamedRange Add(string Name, ExcelRangeBase Range)
        {
            ExcelNamedRange item;

            if (Range.IsName)
            {
                item = new ExcelNamedRange(Name, _wb, _ws);
            }
            else
            {
                item = new ExcelNamedRange(Name, _ws, Range.Worksheet, Range.Address);
            }

            _dic.Add(Name.ToLower(), item);
            return(item);
        }
        /// <summary>
        /// Add a new named range
        /// </summary>
        /// <param name="Name">The name</param>
        /// <param name="Range">The range</param>
        /// <returns></returns>
        public ExcelNamedRange Add(string Name, ExcelRangeBase Range)
        {
            ExcelNamedRange item;

            if (Range.IsName)
            {
                item = new ExcelNamedRange(Name, _wb, _ws, _dic.Count);
            }
            else
            {
                item = new ExcelNamedRange(Name, _ws, Range.Worksheet, Range.Address, _dic.Count);
            }

            AddName(Name, item);

            return(item);
        }
Example #10
0
        internal ExcelComment(XmlNamespaceManager ns, XmlNode commentTopNode, ExcelRangeBase cell)
            : base(null, cell, cell.Worksheet.VmlDrawings.NameSpaceManager)
        {
            _commentHelper = new XmlHelper(ns, commentTopNode);
            var textElem=commentTopNode.SelectSingleNode("d:text", ns);
            if (textElem == null)
            {
                textElem = commentTopNode.OwnerDocument.CreateElement("text", ExcelPackage.schemaMain);
                commentTopNode.AppendChild(textElem);
            }
            if (!cell.Worksheet.VmlDrawings.ContainsKey(ExcelAddress.GetCellID(cell.Worksheet.SheetID, cell.Start.Row, cell.Start.Column)))
            {
                cell.Worksheet.VmlDrawings.Add(cell);
            }

            TopNode = cell.Worksheet.VmlDrawings[ExcelCellBase.GetCellID(cell.Worksheet.SheetID, cell.Start.Row, cell.Start.Column)].TopNode;
            RichText = new ExcelRichTextCollection(ns,textElem);
        }
Example #11
0
        internal ExcelComment(XmlNamespaceManager ns, XmlNode commentTopNode, ExcelRangeBase cell)
            : base(null, cell, cell.Worksheet.VmlDrawingsComments.NameSpaceManager)
        {
            //_commentHelper = new XmlHelper(ns, commentTopNode);
            _commentHelper = XmlHelperFactory.Create(ns, commentTopNode);
            var textElem = commentTopNode.SelectSingleNode("d:text", ns);

            if (textElem == null)
            {
                textElem = commentTopNode.OwnerDocument.CreateElement("text", ExcelPackage.schemaMain);
                commentTopNode.AppendChild(textElem);
            }
            if (!cell.Worksheet._vmlDrawings.ContainsKey(ExcelAddress.GetCellID(cell.Worksheet.SheetID, cell.Start.Row, cell.Start.Column)))
            {
                cell.Worksheet._vmlDrawings.Add(cell);
            }

            TopNode  = cell.Worksheet.VmlDrawingsComments[ExcelCellBase.GetCellID(cell.Worksheet.SheetID, cell.Start.Row, cell.Start.Column)].TopNode;
            RichText = new ExcelRichTextCollection(ns, textElem);
        }
Example #12
0
        /// <summary>
        /// Add a new named range
        /// </summary>
        /// <param name="Name">The name</param>
        /// <param name="Range">The range</param>
        /// <returns></returns>
        public ExcelNamedRange Add(string Name, ExcelRangeBase Range)
        {
            ExcelNamedRange item;

            if (!ExcelAddressUtil.IsValidName(Name))
            {
                throw (new ArgumentException("Name contains invalid characters"));
            }
            if (Range.IsName)
            {
                item = new ExcelNamedRange(Name, _wb, _ws, _dic.Count);
            }
            else
            {
                item = new ExcelNamedRange(Name, _ws, Range.Worksheet, Range.Address, _dic.Count);
            }

            AddName(Name, item);

            return(item);
        }
        /// <summary>
        /// Adds a comment to the top left cell of the range
        /// </summary>
        /// <param name="cell">The cell</param>
        /// <param name="Text">The comment text</param>
        /// <param name="author">Author</param>
        /// <returns>The comment</returns>
        public ExcelComment Add(ExcelRangeBase cell, string Text, string author)
        {
            var elem = CommentXml.CreateElement("comment", ExcelPackage.schemaMain);
            //int ix=_comments.IndexOf(ExcelAddress.GetCellID(Worksheet.SheetID, cell._fromRow, cell._fromCol));
            //Make sure the nodes come on order.
            int          row = cell.Start.Row, column = cell.Start.Column;
            ExcelComment nextComment = null;

            if (Worksheet._commentsStore.NextCell(ref row, ref column))
            {
                nextComment = _list[Worksheet._commentsStore.GetValue(row, column)];
            }
            if (nextComment == null)
            {
                CommentXml.SelectSingleNode("d:comments/d:commentList", NameSpaceManager).AppendChild(elem);
            }
            else
            {
                nextComment._commentHelper.TopNode.ParentNode.InsertBefore(elem, nextComment._commentHelper.TopNode);
            }
            elem.SetAttribute("ref", cell.Start.Address);
            ExcelComment comment = new ExcelComment(NameSpaceManager, elem, cell);

            comment.RichText.Add(Text);
            if (author != "")
            {
                comment.Author = author;
            }
            _listIndex.Add(_list.Count);
            Worksheet._commentsStore.SetValue(cell.Start.Row, cell.Start.Column, _list.Count);
            _list.Add(comment);
            //Check if a value exists otherwise add one so it is saved when the cells collection is iterated
            if (!Worksheet.ExistsValueInner(cell._fromRow, cell._fromCol))
            {
                Worksheet.SetValueInner(cell._fromRow, cell._fromCol, null);
            }
            return(comment);
        }
 public static ExcelRangeBase Cell(this ExcelRangeBase range, int row, int column) => range.Worksheet.Cells[range.Start.Row + row - 1, range.Start.Column + column - 1];
 /// <summary>
 /// Adds a comment to the top left cell of the range
 /// </summary>
 /// <param name="cell">The cell</param>
 /// <param name="Text">The comment text</param>
 /// <param name="author">Author</param>
 /// <returns>The comment</returns>
 public ExcelComment Add(ExcelRangeBase cell, string Text, string author)
 {            
     var elem = CommentXml.CreateElement("comment", ExcelPackage.schemaMain);
     int ix=_comments.IndexOf(ExcelAddress.GetCellID(Worksheet.SheetID, cell._fromRow, cell._fromCol));
     //Make sure the nodes come on order.
     if (ix < 0 && (~ix < _comments.Count))
     {
         ix = ~ix;
         var preComment = _comments[ix] as ExcelComment;
         preComment._commentHelper.TopNode.ParentNode.InsertBefore(elem, preComment._commentHelper.TopNode);
     }
     else
     {
         CommentXml.SelectSingleNode("d:comments/d:commentList", NameSpaceManager).AppendChild(elem);
     }
     elem.SetAttribute("ref", cell.Start.Address);
     ExcelComment comment = new ExcelComment(NameSpaceManager, elem , cell);
     comment.RichText.Add(Text);
     if(author!="") 
     {
         comment.Author=author;
     }
     _comments.Add(comment);
     return comment;
 }
Example #16
0
        /// <summary>
        /// 指定したセルを基準とした定数情報を生成する。
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static DefinedInfo CreateDefinedInfo(ExcelRangeBase cell)
        {
            var result = new DefinedInfo(cell.Offset(0, 0).ValueString()
                                        , cell.Offset(1, 0).ValueString()
                                        , cell.Offset(3, 0).ValueString()
                                        , cell.Offset(2, 0).ValueString());

            return result;
        }
 /// <summary>
 /// Adds a comment to the top left cell of the range
 /// </summary>
 /// <param name="cell">The cell</param>
 /// <param name="Text">The comment text</param>
 /// <param name="author">Author</param>
 /// <returns>The comment</returns>
 public ExcelComment Add(ExcelRangeBase cell, string Text, string author)
 {
     var elem = CommentXml.CreateElement("comment", ExcelPackage.schemaMain);
     //int ix=_comments.IndexOf(ExcelAddress.GetCellID(Worksheet.SheetID, cell._fromRow, cell._fromCol));
     //Make sure the nodes come on order.
     int row=cell.Start.Row, column= cell.Start.Column;
     ExcelComment nextComment = null;
     if (Worksheet._commentsStore.NextCell(ref row, ref column))
     {
         nextComment = _list[Worksheet._commentsStore.GetValue(row, column)];
     }
     if(nextComment==null)
     {
         CommentXml.SelectSingleNode("d:comments/d:commentList", NameSpaceManager).AppendChild(elem);
     }
     else
     {
         nextComment._commentHelper.TopNode.ParentNode.InsertBefore(elem, nextComment._commentHelper.TopNode);
     }
     elem.SetAttribute("ref", cell.Start.Address);
     ExcelComment comment = new ExcelComment(NameSpaceManager, elem , cell);
     comment.RichText.Add(Text);
     if(author!="")
     {
         comment.Author=author;
     }
     _list.Add(comment);
     Worksheet._commentsStore.SetValue(cell.Start.Row, cell.Start.Column, _list.Count-1);
     //Check if a value exists otherwise add one so it is saved when the cells collection is iterated
     if (!Worksheet.ExistsValueInner(cell._fromRow, cell._fromCol))
     {
         Worksheet.SetValueInner(cell._fromRow, cell._fromCol, null);
     }
     return comment;
 }
Example #18
0
        public static ExcelPivotTable AddPivotTable <T>(this ExcelWorksheet ws, string TableName, ExcelRangeBase dataRange)
        {
            var pivotTable = ws.PivotTables.Add(ws.Cells[1, 1], dataRange, TableName);

            pivotTable.MultipleFieldFilters = true;
            pivotTable.RowGrandTotals       = true;
            pivotTable.ColumGrandTotals     = true;
            pivotTable.Compact                 = true;
            pivotTable.CompactData             = true;
            pivotTable.GridDropZones           = false;
            pivotTable.Outline                 = false;
            pivotTable.OutlineData             = false;
            pivotTable.ShowError               = true;
            pivotTable.ErrorCaption            = "[error]";
            pivotTable.ShowHeaders             = true;
            pivotTable.UseAutoFormatting       = true;
            pivotTable.ApplyWidthHeightFormats = true;
            pivotTable.ShowDrill               = true;
            pivotTable.FirstDataCol            = 1;
            pivotTable.DataOnRows              = false;
            pivotTable.RowHeaderCaption        = TableName;

            foreach (var property in typeof(T).GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance))
            {
                var att = (EPPlusColumnFormatAttribute)Attribute.GetCustomAttribute(property, typeof(EPPlusColumnFormatAttribute));
                if (att != null)
                {
                    if (att.pivottable_position != EPPPlusPivotTablePosition.none)
                    {
                        var attribute_display_name = (DisplayNameAttribute)Attribute.GetCustomAttribute(property, typeof(DisplayNameAttribute));
                        var field_name             = attribute_display_name != null ? attribute_display_name.DisplayName : property.Name;
                        var field = pivotTable.Fields.FirstOrDefault(x => x.Name == field_name);
                        if (field != null)
                        {
                            if (att.pivottable_position == EPPPlusPivotTablePosition.dataField)
                            {
                                var f = pivotTable.DataFields.Add(field);
                                f.Function = att.pivottable_function;
                                f.Name     = field_name;
                                f.Format   = getFormat(att);
                            }
                            else if (att.pivottable_position == EPPPlusPivotTablePosition.rowField)
                            {
                                var f = pivotTable.RowFields.Add(field);
                                f.Name = field_name;
                            }
                            //TODO PageFields && ColumnFields
                        }
                    }
                }
            }

            return(pivotTable);
        }
 /// <summary>
 /// Adds a comment to the top left cell of the range
 /// </summary>
 /// <param name="cell">The cell</param>
 /// <param name="Text">The comment text</param>
 /// <param name="author">Author</param>
 /// <returns>The comment</returns>
 public ExcelComment Add(ExcelRangeBase cell, string Text, string author)
 {
     var elem = CommentXml.CreateElement("comment", ExcelPackage.schemaMain);
     CommentXml.SelectSingleNode("d:comments/d:commentList", NameSpaceManager).AppendChild(elem);
     elem.SetAttribute("ref", cell.Start.Address);
     ExcelComment comment = new ExcelComment(NameSpaceManager, elem , cell);
     comment.RichText.Add(Text);
     if(author!="")
     {
         comment.Author=author;
     }
     _comments.Add(comment);
     return comment;
 }
Example #20
0
        /// <summary>
        /// Adds a stock chart sheet to the workbook.
        /// </summary>
        /// <param name="Name">The name of the worksheet</param>
        /// <param name="CategorySerie">The category serie. A serie containing dates or names</param>
        /// <param name="HighSerie">The high price serie</param>
        /// <param name="LowSerie">The low price serie</param>
        /// <param name="CloseSerie">The close price serie containing</param>
        /// <param name="OpenSerie">The opening price serie. Supplying this serie will create a StockOHLC or StockVOHLC chart</param>
        /// <param name="VolumeSerie">The volume represented as a column chart. Supplying this serie will create a StockVHLC or StockVOHLC chart</param>
        /// <returns></returns>
        public ExcelChartsheet AddStockChart(string Name, ExcelRangeBase CategorySerie, ExcelRangeBase HighSerie, ExcelRangeBase LowSerie, ExcelRangeBase CloseSerie, ExcelRangeBase OpenSerie = null, ExcelRangeBase VolumeSerie = null)
        {
            var chartType = ExcelStockChart.GetChartType(OpenSerie, VolumeSerie);
            var sheet     = (ExcelChartsheet)AddSheet(Name, true, chartType, null);
            var chart     = (ExcelStockChart)sheet.Chart;

            ExcelStockChart.SetStockChartSeries(chart, chartType, CategorySerie.FullAddress, HighSerie.FullAddress, LowSerie.FullAddress, CloseSerie.FullAddress, OpenSerie?.FullAddress, VolumeSerie?.FullAddress);
            return(sheet);
        }
 public static ExcelColumn EndColumn(this ExcelRangeBase range) => range.Worksheet.Column(range.End.Column);
 public static ExcelRow StartRow(this ExcelRangeBase range) => range.Worksheet.Row(range.Start.Row);
 public static ExcelColumn StartColumn(this ExcelRangeBase range) => range.Worksheet.Column(range.Start.Column);
        //public static int ColumnNumber(this ExcelRangeBase range, string columnName) => range.First(c => c.Value.ToString() == columnName).Start.Column;

        public static ExcelRangeBase StartCell(this ExcelRangeBase range) => range.Worksheet.Cells[range.Start.Row, range.Start.Column];
 public static ExcelRow Row(this ExcelRangeBase range, int row) => range.Worksheet.Row(range.Start.Row + row - 1);
 public static ExcelColumn Column(this ExcelRangeBase range, int column) => range.Worksheet.Column(range.Start.Column + column - 1);
Example #27
0
        internal void GetDefinedNames()
        {
            XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);

            if (nl != null)
            {
                foreach (XmlElement elem in nl)
                {
                    string fullAddress = elem.InnerText;

                    int            localSheetID;
                    ExcelWorksheet nameWorksheet;
                    if (!int.TryParse(elem.GetAttribute("localSheetId"), out localSheetID))
                    {
                        localSheetID  = -1;
                        nameWorksheet = null;
                    }
                    else
                    {
                        nameWorksheet = Worksheets[localSheetID + 1];
                    }
                    var             addressType = ExcelAddressBase.IsValid(fullAddress);
                    ExcelRangeBase  range;
                    ExcelNamedRange namedRange;

                    if (fullAddress.IndexOf("[") > -1)
                    {
                        int start = fullAddress.IndexOf("[");
                        int end   = fullAddress.IndexOf("]", start);
                        if (start >= 0 && end >= 0)
                        {
                            string externalIndex = fullAddress.Substring(start + 1, end - start - 1);
                            int    index;
                            if (int.TryParse(externalIndex, out index))
                            {
                                if (index > 0 && index <= _externalReferences.Count)
                                {
                                    fullAddress = fullAddress.Substring(0, start) + "[" + _externalReferences[index - 1] + "]" + fullAddress.Substring(end + 1);
                                }
                            }
                        }
                    }

                    if (addressType == ExcelAddressBase.AddressType.Invalid || addressType == ExcelAddressBase.AddressType.InternalName || addressType == ExcelAddressBase.AddressType.ExternalName)                        //A value or a formula
                    {
                        double value;
                        range = new ExcelRangeBase(this, nameWorksheet, elem.GetAttribute("name"), true);
                        if (nameWorksheet == null)
                        {
                            namedRange = _names.Add(elem.GetAttribute("name"), range);
                        }
                        else
                        {
                            namedRange = nameWorksheet.Names.Add(elem.GetAttribute("name"), range);
                        }

                        if (fullAddress.StartsWith("\""))                         //String value
                        {
                            namedRange.NameValue = fullAddress.Substring(1, fullAddress.Length - 2);
                        }
                        else if (double.TryParse(fullAddress, NumberStyles.Any, CultureInfo.InvariantCulture, out value))
                        {
                            namedRange.NameValue = value;
                        }
                        else
                        {
                            namedRange.NameFormula = fullAddress;
                        }
                    }
                    else
                    {
                        ExcelAddress addr = new ExcelAddress(fullAddress);
                        if (localSheetID > -1)
                        {
                            if (string.IsNullOrEmpty(addr._ws))
                            {
                                namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[localSheetID + 1], fullAddress, false));
                            }
                            else
                            {
                                namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[addr._ws], fullAddress, false));
                            }
                        }
                        else
                        {
                            var ws = Worksheets[addr._ws];
                            namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, ws, fullAddress, false));
                        }
                    }
                    if (elem.GetAttribute("hidden") == "1" && namedRange != null)
                    {
                        namedRange.IsNameHidden = true;
                    }
                    if (!string.IsNullOrEmpty(elem.GetAttribute("comment")))
                    {
                        namedRange.NameComment = elem.GetAttribute("comment");
                    }
                }
            }
        }
Example #28
0
        public ExcelWorksheet AddPivotTables(string sheetName,ExcelRangeBase source, char delimiter = '\t')
        {
            ///ProgressForm FormProgr = new ProgressForm();            FormProgr.Stat = sheetName;

            var newSheet = _excelFile.Workbook.Worksheets.Add(sheetName);
            newSheet.Cells.Style.Font.Size = 11;
            newSheet.Cells.Style.Font.Name = "Calibri";

            var format = new ExcelTextFormat();
            format.Delimiter = delimiter;
            format.Culture = new CultureInfo("ru-RU");
            format.Culture.NumberFormat.NumberDecimalSeparator = ",";

            var pivotTable = newSheet.PivotTables.Add(newSheet.Cells["A1"], source, "Pivot "+sheetName);
            pivotTable.RowFields.Add(pivotTable.Fields[0]);

            if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("cpu2"))
            {
                var usrField = pivotTable.DataFields.Add(pivotTable.Fields[1]);
                usrField.Name = source.Worksheet.Cells[1, 2].Text;
                usrField.Function = DataFieldFunctions.Average;

                var sysField = pivotTable.DataFields.Add(pivotTable.Fields[2]);
                sysField.Name = source.Worksheet.Cells[1, 3].Text;
                sysField.Function = DataFieldFunctions.Average;

                var wioField = pivotTable.DataFields.Add(pivotTable.Fields[3]);
                wioField.Name = source.Worksheet.Cells[1, 4].Text;
                wioField.Function = DataFieldFunctions.Average;

                var loadField = pivotTable.DataFields.Add(pivotTable.Fields[4]);
                loadField.Name = source.Worksheet.Cells[1, 5].Text;
                loadField.Function = DataFieldFunctions.Average;

            }
            if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("mem"))
            {
                var dataField = pivotTable.DataFields.Add(pivotTable.Fields[3]);
                dataField.Name = source.Worksheet.Cells[1, 4].Text;
                dataField.Function = DataFieldFunctions.Average;
            }
            if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("disk_io"))
            {
                var dataName = pivotTable.ColumnFields.Add(pivotTable.Fields[1]);
                dataName.Name = source.Worksheet.Cells[1, 2].Text;
                /*/pivotTable.Fields[1].Sort = eSortType.Ascending;
                if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("db"))
                {*/
                    var dataField2 = pivotTable.DataFields.Add(pivotTable.Fields[6]);
                    dataField2.Name = source.Worksheet.Cells[1, 7].Text;
                    dataField2.Function = DataFieldFunctions.Average;
                /*}
                else
                {
                    var dataField2 = pivotTable.DataFields.Add(pivotTable.Fields[4]);
                    dataField2.Name = source.Worksheet.Cells[1, 5].Text;
                    dataField2.Function = DataFieldFunctions.Average;
                }*/
            }
            if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("sar-disk"))
            {
                var dataName = pivotTable.ColumnFields.Add(pivotTable.Fields[1]);
                dataName.Name = source.Worksheet.Cells[1, 2].Text;
                //pivotTable.Fields[1].Sort = eSortType.Ascending;

                var dataField = pivotTable.DataFields.Add(pivotTable.Fields[2]);
                dataField.Name = source.Worksheet.Cells[1, 3].Text;
                dataField.Function = DataFieldFunctions.Average;

            }
            if (_excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("pivot") && _excelFile.Workbook.Worksheets[newSheet.Index].Name.Contains("cpu_"))
            {
                var dataName = pivotTable.ColumnFields.Add(pivotTable.Fields[1]);
                dataName.Name = source.Worksheet.Cells[1, 2].Text;
                pivotTable.Fields[1].Sort = eSortType.Ascending;
                var dataField = pivotTable.DataFields.Add(pivotTable.Fields[5]);
                dataField.Name = source.Worksheet.Cells[1, 6].Text;
                dataField.Function = DataFieldFunctions.Average;
            }

            pivotTable.DataOnRows = false;
            return newSheet;
            }
Example #29
0
 public static void Calculate(this ExcelRangeBase range)
 {
     Calculate(range, new ExcelCalculationOption());
 }
Example #30
0
File: Excel.cs Project: Marksys/CAD
 private string trataValor(ExcelRangeBase excel)
 {
     if (excel.Value != null)
         return excel.Value.ToString();
     else
         return "";
 }
Example #31
0
 private static void SetColor(ExcelRangeBase cell, Color color)
 {
     cell.Style.Fill.PatternType = ExcelFillStyle.Solid;
     cell.Style.Fill.BackgroundColor.SetColor(color);
 }
Example #32
0
        /// <summary>
        /// 入力チェック属性情報を生成する。
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static IEnumerable<CheckAttributeBase> CreateCheckAttributes(ExcelRangeBase cell)
        {
            var results = new List<CheckAttributeBase>();

            string isCheckRequired = cell.Offset(0, 0).ValueString();
            if (string.IsNullOrEmpty(isCheckRequired) == false)
            {
                // 未入力チェック実施
                results.Add(new CheckRequiredAttribute());
            }

            string typeName = cell.Offset(0, -4).ValueString();
            string minimum = cell.Offset(0, 1).ValueString();
            string maximum = cell.Offset(0, 2).ValueString();
            if (string.IsNullOrEmpty(minimum) == false
                && string.IsNullOrEmpty(maximum) == false)
            {
                // 範囲入力チェック実施
                results.Add(new CheckRangeAttribute(typeName, minimum, maximum));
            }

            string minLength = cell.Offset(0, 3).ValueString();
            string maxLength = cell.Offset(0, 4).ValueString();
            if (string.IsNullOrEmpty(minLength) == false
                && string.IsNullOrEmpty(maxLength) == false)
            {
                // 文字数制限チェック実施
                results.Add(new CheckStringLengthAttribute(int.Parse(minLength), int.Parse(maxLength)));
            }

            return results;
        }
Example #33
0
		private static decimal getDecimal(ExcelRangeBase rb)
		{
			decimal val = rb.GetValue<decimal>();
			return val;
		}
Example #34
0
        /// <summary>
        /// 指定したセルを基準としたプロパティ情報を生成する。
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static PropertyInfo CreatePropertyInfo(ExcelRangeBase cell)
        {
            string typeName = cell.Offset(0, 2).ValueString();
            string defaultValue = string.Empty;
            string elementCount = cell.Offset(0, 3).ValueString();
            int count;

            // 配列やコレクションの場合、メンバで初期化する必要がある。
            // それ以外の場合は、初期化は実施しない。
            // ここでは、デフォルト値の設定有無の判定と、型名の変換を行う。
            if (int.TryParse(elementCount, out count) == true)
            {
                if (count > 1)
                {
                    // 配列で定義されている。
                    defaultValue = string.Format("new {0}[{1}]", typeName, count);
                    typeName = string.Format("{0}[]", typeName);
                }
            }
            else if (elementCount == "*")
            {
                // コレクションで定義されている。
                typeName = string.Format("ObservableCollection<{0}>", typeName);
                defaultValue = string.Format("new {0}()", typeName);
            }

            var result = new PropertyInfo(cell.Offset(0, 0).ValueString()
                                        , typeName
                                        , cell.Offset(0, 4).ValueString()
                                        , cell.Offset(0, 1).ValueString()
                                        , cell.Offset(0, 5).ValueString())
                                        {
                                            DefaultValue = defaultValue,
                                        };

            result.CheckAttributes.AddRange(CreateCheckAttributes(cell.Offset(0, 6)));

            return result;
        }
Example #35
0
        private void CleanupText(ExcelRangeBase cell, string input, Dictionary<string, string> replacements)
        {
            foreach (var kvp in replacements)
            {
                input = input.Replace("@" + kvp.Key, kvp.Value);
            }

            new HtmlToRichTextHelper().HtmlToRichText(cell, input);
        }
 public static ExcelRangeBase EndCell(this ExcelRangeBase range) => range.Worksheet.Cells[range.End.Row, range.End.Column];
 private static string GetCellText(ExcelRangeBase cell)
 {
     return cell.Value == null ? string.Empty : cell.Value.ToString();
 }
        internal void GetDefinedNames()
        {
            XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);

            if (nl != null)
            {
                foreach (XmlElement elem in nl)
                {
                    try
                    {
                        string fullAddress = elem.InnerText;

                        int            localSheetID;
                        ExcelWorksheet nameWorksheet;

                        if (!int.TryParse(elem.GetAttribute("localSheetId"), NumberStyles.Number, CultureInfo.InvariantCulture, out localSheetID))
                        {
                            localSheetID  = -1;
                            nameWorksheet = null;
                        }
                        else
                        {
                            nameWorksheet = Worksheets[localSheetID + _package._worksheetAdd];
                        }

                        var             addressType = ExcelAddressBase.IsValid(fullAddress);
                        ExcelRangeBase  range;
                        ExcelNamedRange namedRange;

                        if (fullAddress.IndexOf("[") == 0)
                        {
                            int start = fullAddress.IndexOf("[");
                            int end   = fullAddress.IndexOf("]", start);
                            if (start >= 0 && end >= 0)
                            {
                                string externalIndex = fullAddress.Substring(start + 1, end - start - 1);
                                int    index;
                                if (int.TryParse(externalIndex, NumberStyles.Any, CultureInfo.InvariantCulture, out index))
                                {
                                    if (index > 0 && index <= _externalReferences.Count)
                                    {
                                        fullAddress = fullAddress.Substring(0, start) + "[" + _externalReferences[index - 1] + "]" + fullAddress.Substring(end + 1);
                                    }
                                }
                            }
                        }

                        if (addressType == ExcelAddressBase.AddressType.Invalid || addressType == ExcelAddressBase.AddressType.InternalName || addressType == ExcelAddressBase.AddressType.ExternalName || addressType == ExcelAddressBase.AddressType.Formula || addressType == ExcelAddressBase.AddressType.CellAddress)    //A value or a formula
                        {
                            double value;
                            range = new ExcelRangeBase(this, nameWorksheet, elem.GetAttribute("name"), true);
                            if (nameWorksheet == null)
                            {
                                namedRange = _names.Add(elem.GetAttribute("name"), range);
                            }
                            else
                            {
                                namedRange = nameWorksheet.Names.Add(elem.GetAttribute("name"), range);
                            }

                            if (ConvertUtil._invariantCompareInfo.IsPrefix(fullAddress, "\"")) //String value
                            {
                                namedRange.NameValue = fullAddress.Substring(1, fullAddress.Length - 2);
                            }
                            else if (double.TryParse(fullAddress, NumberStyles.Number, CultureInfo.InvariantCulture, out value))
                            {
                                namedRange.NameValue = value;
                            }
                            else
                            {
                                //if (addressType == ExcelAddressBase.AddressType.ExternalAddress || addressType == ExcelAddressBase.AddressType.ExternalName)
                                //{
                                //    var r = new ExcelAddress(fullAddress);
                                //    namedRange.NameFormula = '\'[' + r._wb
                                //}
                                //else
                                //{
                                namedRange.NameFormula = fullAddress;
                                //}
                            }
                        }
                        else
                        {
                            ExcelAddress addr = new ExcelAddress(fullAddress, _package, null);
                            if (localSheetID > -1)
                            {
                                if (string.IsNullOrEmpty(addr._ws))
                                {
                                    namedRange = Worksheets[localSheetID + _package._worksheetAdd].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[localSheetID + _package._worksheetAdd], fullAddress, false));
                                }
                                else
                                {
                                    namedRange = Worksheets[localSheetID + _package._worksheetAdd].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[addr._ws], fullAddress, false));
                                }
                            }
                            else
                            {
                                var ws = Worksheets[addr._ws];
                                namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, ws, fullAddress, false));
                            }
                        }

                        if (namedRange == null)
                        {
                            continue;
                        }
                        namedRange.RawFormula = elem.InnerText;
                        if (elem.GetAttribute("hidden") == "1")
                        {
                            namedRange.IsNameHidden = true;
                        }
                        if (!string.IsNullOrEmpty(elem.GetAttribute("comment")))
                        {
                            namedRange.NameComment = elem.GetAttribute("comment");
                        }
                    }
                    catch (Exception)
                    {
                        // Quickfix: Hidden named ranges that references worksheets which do not exist will not be added to all defined names.
                    }
                }
            }
        }
Example #39
0
        private static void CalcChain_V2(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc, ExcelRangeBase range = null)
        {
            if (dc.CalcOrder.Count == 0)
            {
                if (range != null && range.Address.ToString().StartsWith("P"))
                {
                }
            }

            int fiColValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN;
            int fiRowValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_ROW;

            int ftpColValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN;
            int ftpRowValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_ROW;

            foreach (var ix in dc.CalcOrder)
            {
                var item = dc.list[ix];
                rows.Add(item.Row);
                sheetId = item.SheetID;

                try
                {
                    var ws = wb.Worksheets.GetBySheetID(item.SheetID);

                    bool goDefault = true;
                    if (ws.Cells[fiRowValidator, fiColValidator] != null &&
                        ws.Cells[fiRowValidator, fiColValidator].Text != "" &&
                        ws.Cells[fiRowValidator, fiColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN_HEADER.ToLower())
                    { // FI Worksheet
                        #region FI WORKSHEETS
                        if (range != null &&
                            item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST &&
                            range.Address.ToString().StartsWith("P") ||
                            item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS)
                        {
                            if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != null &&
                                ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != "" &&
                                ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text.ToLower() == FundingConstants_V2.PROGRAM_TYPE.FTS.ToLower())
                            {
                                decimal x1        = 0;
                                bool    isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Text.Replace("$", ""), out x1);

                                if (isValidX1)
                                {
                                    decimal  grantPercentage  = .5m;
                                    string   nrictype         = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.NRIC_TYPE].Text.ToString();
                                    string   progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.PROG_START_DATE].Text.ToString();
                                    string   scAgeFilterStr   = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.SC_AGE_FILTER].Text.ToString();
                                    bool     isSCAgeFilter    = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes";
                                    DateTime progStartDate    = parseDate(progStartDateStr);

                                    if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC &&
                                        progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 &&
                                        isSCAgeFilter
                                        )
                                    {
                                        grantPercentage = .9m;
                                    }

                                    x1 = x1 * grantPercentage;
                                    x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero);

                                    string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_FTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row);

                                    if (item.Formula == null)
                                    {
                                        x1 = 0;
                                    }
                                    else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
                                    {
                                        x1 = 0;
                                    }
                                    else if (x1 > 2000.00m)
                                    {
                                        x1 = 2000.00m;
                                    }

                                    SetValue(wb, item, x1);
                                    goDefault = false;
                                }
                                else
                                {
                                    SetValue(wb, item, "");
                                    goDefault = false;
                                }
                            }
                            else
                            {
                                SetValue(wb, item, "");
                                goDefault = false;
                            }
                        }
                        else if (range != null &&
                                 item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST &&
                                 range.Address.ToString().StartsWith("P") ||
                                 item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS)
                        {
                            if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != null &&
                                ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != "" &&
                                ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text.ToLower() == FundingConstants_V2.PROGRAM_TYPE.IBF_STS.ToLower())
                            {
                                decimal x1        = 0;
                                bool    isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Text.Replace("$", ""), out x1);

                                //if(isValidX1)
                                //{


                                decimal  grantPercentage  = .7m;
                                string   nrictype         = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.NRIC_TYPE].Text.ToString();
                                string   progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.PROG_START_DATE].Text.ToString();
                                string   scAgeFilterStr   = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.SC_AGE_FILTER].Text.ToString();
                                bool     isSCAgeFilter    = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes";
                                DateTime progStartDate    = parseDate(progStartDateStr);

                                if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC &&
                                    progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 &&
                                    isSCAgeFilter
                                    )
                                {
                                    grantPercentage = .9m;
                                }

                                x1 = x1 * grantPercentage;
                                x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero);

                                string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row);

                                if (item.Formula == null)
                                {
                                    x1 = 0;
                                }
                                else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
                                {
                                    x1 = 0;
                                }
                                else if (x1 > 7000.00m)
                                {
                                    x1 = 7000.00m;
                                }

                                SetValue(wb, item, x1);
                                goDefault = false;
                                //}
                                //else
                                //{
                                //    SetValue(wb, item, "");
                                //    goDefault = false;
                                //}
                            }
                            else
                            {
                                SetValue(wb, item, "");
                                goDefault = false;
                            }
                        }
                        else if (item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST)
                        {
                            //if (ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text != "" ||
                            //    ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text != "")
                            //{
                            decimal x1 = 0;
                            decimal x2 = 0;

                            bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1);
                            bool isValidX2 = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2);

                            string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_INTERNAL_COST, item.Row, item.Row);

                            decimal sum = x1 + x2;

                            if (item.Formula == null)
                            {
                                sum = 0;
                            }
                            else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
                            {
                                sum = 0;
                            }
                            else
                            {
                                SetValue(wb, item, sum);
                                goDefault = false;
                            }
                        }
                        else if (item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST)
                        {
                            //if (ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text != "" &&
                            //    ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text != "")
                            decimal x1a = 0;
                            decimal x2a = 0;

                            bool isValidX1a = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1a);
                            bool isValidX2a = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2a);

                            if ((x1a + x2a) != 0m)
                            {
                                decimal x1 = 0;
                                decimal x2 = 0;

                                bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1);
                                bool isValidX2 = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2);

                                string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, item.Row, item.Row, item.Row);

                                decimal sum = x1 + x2;

                                if (item.Formula == null)
                                {
                                    sum = 0;
                                }
                                else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
                                {
                                    sum = 0;
                                }
                                else
                                {
                                    SetValue(wb, item, sum);
                                    goDefault = false;
                                }
                            }
                            else if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.EXTERNAL_TOTAL].Text != "")
                            {
                                decimal x1 = 0;

                                bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.EXTERNAL_TOTAL].Value + "", out x1);

                                string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, item.Row, item.Row, item.Row);


                                if (item.Formula == null)
                                {
                                    x1 = 0;
                                }
                                else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
                                {
                                    x1 = 0;
                                }
                                else
                                {
                                    SetValue(wb, item, x1);
                                    goDefault = false;
                                }
                            }
                        }
                        #endregion
                    }
                    else if (ws.Cells[ftpRowValidator, ftpColValidator] != null &&
                             ws.Cells[ftpRowValidator, ftpColValidator].Text != "" &&
                             ws.Cells[ftpRowValidator, ftpColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN_HEADER.ToLower())
                    { // FTP Worksheet
                        #region FTP Worksheet
                        if (range != null &&
                            item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS &&
                            range.Address.ToString().StartsWith("M") ||
                            item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS)
                        {
                            decimal x1        = 0;
                            bool    isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.PROGRAMME_FEE].Value.ToString(), out x1);

                            //if(isValidX1)
                            //{

                            decimal  grantPercentage  = .7m;
                            string   nrictype         = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.NRIC_TYPE].Text.ToString();
                            string   progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.PROG_START_DATE].Text.ToString();
                            string   scAgeFilterStr   = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.SC_AGE_FILTER].Text.ToString();
                            bool     isSCAgeFilter    = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes";
                            DateTime progStartDate    = parseDate(progStartDateStr);

                            if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC &&
                                progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 &&
                                isSCAgeFilter
                                )
                            {
                                grantPercentage = .9m;
                            }
                            x1 = x1 * grantPercentage;
                            x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero);


                            //string formula = "IF(EXACT(N"+item.Row+",\"IBF-STS\"),MIN(O"+item.Row+"*0.5,2000),\"\")";
                            // =ROUND(MIN(K16*0.7,7000),2)
                            // string formula = "MIN(K"+item.Row+"*0.7,7000)";
                            string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row);

                            if (item.Formula == null)
                            {
                                x1 = 0;
                            }
                            else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
                            {
                                x1 = 0;
                            }
                            else if (x1 > 7000.00m)
                            {
                                x1 = 7000.00m;
                            }

                            SetValue(wb, item, x1);
                            goDefault = false;
                            //}
                            //else
                            //{
                            //    SetValue(wb, item, "");
                            //    goDefault = false;
                            //}
                        }
                        #endregion
                    }

                    if (goDefault)
                    {
                        var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column);
                        SetValue(wb, item, v);
                    }
                }
                catch (FormatException fe)
                {
                    throw (fe);
                }
                catch (Exception e)
                {
                    var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
                    SetValue(wb, item, error);
                }
            }

            var worksheet = wb.Worksheets.GetBySheetID(sheetId);
            foreach (int row in rows)
            {
                if (worksheet.Cells[fiRowValidator, fiColValidator] != null &&
                    worksheet.Cells[fiRowValidator, fiColValidator].Text != "" &&
                    worksheet.Cells[fiRowValidator, fiColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN_HEADER.ToLower())
                {
                    #region FI WORKSHEETS
                    if (row >= 22)
                    {
                        if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula) ||
                            (!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula) &&
                             worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_FTS, row, row, row, row, row, row, row, row, row, row, row)))
                        {
                            worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS, 0);
                        }

                        if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) ||
                            (!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) &&
                             worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, row, row, row, row, row, row, row, row, row, row, row)))
                        {
                            worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, 0);
                        }

                        if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula) ||
                            (!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula) &&
                             worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_INTERNAL_COST, row, row)))
                        {
                            worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST, 0);
                        }


                        if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula) ||
                            (!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula) &&
                             worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, row, row, row)))
                        {
                            worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST, 0);
                        }
                    }
                    #endregion
                }
                else if (worksheet.Cells[ftpRowValidator, ftpColValidator] != null &&
                         worksheet.Cells[ftpRowValidator, ftpColValidator].Text != "" &&
                         worksheet.Cells[ftpRowValidator, ftpColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN_HEADER.ToLower())
                {
                    #region FTP Worksheets
                    if (row >= 16)
                    {
                        if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) ||
                            (!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) &&
                             worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS, row, row, row, row, row, row, row, row, row, row)))
                        {
                            worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Value = 0;
                        }
                    }
                    #endregion
                }
            }
        }
 public static double VisibleHeight(this ExcelRangeBase range) => (from x in range.RowList() where !x.Hidden select x.Height).Sum();
 public static ExcelRow EndRow(this ExcelRangeBase range) => range.Worksheet.Row(range.End.Row);
Example #42
0
 public static async Task CalculateAsync(this ExcelRangeBase range)
 {
     await Task.Run(() => Calculate(range));
 }
 public static double VisibleWidth(this ExcelRangeBase range) => (from x in range.ColumnList() where !x.Hidden select x.Width).Sum();
Example #44
0
		internal void GetDefinedNames()
		{
			XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);
			if (nl != null)
			{
				foreach (XmlElement elem in nl)
				{ 
					string fullAddress = elem.InnerText;

					int localSheetID;
					ExcelWorksheet nameWorksheet;
					if(!int.TryParse(elem.GetAttribute("localSheetId"), out localSheetID))
					{
						localSheetID = -1;
						nameWorksheet=null;
					}
					else
					{
						nameWorksheet=Worksheets[localSheetID + 1];
					}
					var addressType = ExcelAddressBase.IsValid(fullAddress);
					ExcelRangeBase range;
					ExcelNamedRange namedRange;

					if (fullAddress.IndexOf("[") == 0)
					{
						int start = fullAddress.IndexOf("[");
						int end = fullAddress.IndexOf("]", start);
						if (start >= 0 && end >= 0)
						{

							string externalIndex = fullAddress.Substring(start + 1, end - start - 1);
							int index;
							if (int.TryParse(externalIndex, out index))
							{
								if (index > 0 && index <= _externalReferences.Count)
								{
									fullAddress = fullAddress.Substring(0, start) + "[" + _externalReferences[index - 1] + "]" + fullAddress.Substring(end + 1);
								}
							}
						}
					}

					if (addressType == ExcelAddressBase.AddressType.Invalid || addressType == ExcelAddressBase.AddressType.InternalName || addressType == ExcelAddressBase.AddressType.ExternalName || addressType==ExcelAddressBase.AddressType.Formula || addressType==ExcelAddressBase.AddressType.ExternalAddress)    //A value or a formula
					{
						double value;
						range = new ExcelRangeBase(this, nameWorksheet, elem.GetAttribute("name"), true);
						if (nameWorksheet == null)
						{
							namedRange = _names.Add(elem.GetAttribute("name"), range);
						}
						else
						{
							namedRange = nameWorksheet.Names.Add(elem.GetAttribute("name"), range);
						}
						
						if (fullAddress.StartsWith("\"")) //String value
						{
							namedRange.NameValue = fullAddress.Substring(1,fullAddress.Length-2);
						}
						else if (double.TryParse(fullAddress, NumberStyles.Any, CultureInfo.InvariantCulture, out value))
						{
							namedRange.NameValue = value;
						}
						else
						{
                            //if (addressType == ExcelAddressBase.AddressType.ExternalAddress || addressType == ExcelAddressBase.AddressType.ExternalName)
                            //{
                            //    var r = new ExcelAddress(fullAddress);
                            //    namedRange.NameFormula = '\'[' + r._wb
                            //}
                            //else
                            //{
                                namedRange.NameFormula = fullAddress;
                            //}
						}
					}
					else
					{
						ExcelAddress addr = new ExcelAddress(fullAddress, _package, null);
						if (localSheetID > -1)
						{
							if (string.IsNullOrEmpty(addr._ws))
							{
								namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[localSheetID + 1], fullAddress, false));
							}
							else
							{
								namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[addr._ws], fullAddress, false));
							}
						}
						else
						{
							var ws = Worksheets[addr._ws];
							namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, ws, fullAddress, false));
						}
					}
					if (elem.GetAttribute("hidden") == "1" && namedRange != null) namedRange.IsNameHidden = true;
					if(!string.IsNullOrEmpty(elem.GetAttribute("comment"))) namedRange.NameComment=elem.GetAttribute("comment");
				}
			}
		}
        private void SetCellValue(ExcelTerm excelTerm, ExcelRangeBase cell, int columnIndex)
        {
            var columnLetter = ExcelCellAddress.GetColumnLetter(columnIndex);
            if (columnLetter == _providerSettings.SourceColumn)
            {
                excelTerm.Source = cell.Text;
                excelTerm.SourceCulture = _providerSettings.SourceLanguage;
            }

            if (columnLetter == _providerSettings.TargetColumn)
            {
                excelTerm.Target = cell.Text;
                excelTerm.TargetCulture = _providerSettings.TargetLanguage;
            }

            if (columnLetter == _providerSettings.ApprovedColumn)
            {
                excelTerm.Approved = cell.Text;
            }
        }