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)); }
/// <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); }
/// <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); }
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); }
/// <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); }
/// <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); }
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); }
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); }
/// <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; }
/// <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; }
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; }
/// <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);
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"); } } } }
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; }
public static void Calculate(this ExcelRangeBase range) { Calculate(range, new ExcelCalculationOption()); }
private string trataValor(ExcelRangeBase excel) { if (excel.Value != null) return excel.Value.ToString(); else return ""; }
private static void SetColor(ExcelRangeBase cell, Color color) { cell.Style.Fill.PatternType = ExcelFillStyle.Solid; cell.Style.Fill.BackgroundColor.SetColor(color); }
/// <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; }
private static decimal getDecimal(ExcelRangeBase rb) { decimal val = rb.GetValue<decimal>(); return val; }
/// <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; }
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. } } } }
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);
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();
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; } }