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