/// <summary> /// /// </summary> /// <param name="cell"></param> /// <param name="values"></param> private void ReplaceCell(ExcelRangeBase cell, Dictionary <string, object> values) { if (IsCannotRepace(cell)) { return; } var replaceTemplates = GetReplaceTemplates(cell).ToList(); replaceTemplates.ForEach(f => ReplaceTemplate(cell, f, values)); }
public static ExcelRangeBase LoadFromCollectionFiltered <T>(this ExcelRangeBase @this, IEnumerable <T> collection) where T : class { MemberInfo[] membersToInclude = typeof(T) .GetProperties(BindingFlags.Instance | BindingFlags.Public) .Where(p => !Attribute.IsDefined(p, typeof(EpplusIgnore))) .ToArray(); return(@this.LoadFromCollection <T>(collection, false, OfficeOpenXml.Table.TableStyles.None, BindingFlags.Instance | BindingFlags.Public, membersToInclude)); }
private static void CopyMergedCells(ExcelRangeBase Destination, Dictionary <int, ExcelAddress> copiedMergedCells) { //Add merged cells foreach (var m in copiedMergedCells.Values) { if (m != null) { Destination._worksheet.MergedCells.Add(m, true); } } }
private void HeaderStyle(ExcelRangeBase cell, string text) { cell.Value = text; cell.Style.Font.Size = 12; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Beige); // set border color cell.Style.Fill.PatternType = ExcelFillStyle.Solid; cell.Style.Fill.BackgroundColor.SetColor(Color.RoyalBlue); // set background color cell.Style.Font.Color.SetColor(Color.White); // set color font }
/// <summary> /// Get the Original recommendation of the POAM /// </summary> /// <param name="cell"></param> /// <returns></returns> private string GetOriginalRecommendation(ExcelRangeBase cell) { if (cell.Value == null) { return(string.Empty); } var index = cell.Value.ToString().IndexOf("Risk:"); return((index > 0) ? cell.Value.ToString().Substring(0, index) : string.Empty); }
/// <summary> /// Get the 'Risk' of the poam /// </summary> /// <param name="cell"></param> /// <returns></returns> private string GetRisk(ExcelRangeBase cell) { if (cell.Value == null) { return(string.Empty); } var index = cell.Value.ToString().IndexOf("Risk:"); return((index > 0) ? cell.Value.ToString().Substring(index, cell.Value.ToString().Length - index) : string.Empty); }
/// <summary> /// Get the date value from the excel cell provided /// </summary> /// <param name="cell"></param> /// <returns></returns> private DateTime?GetDateFromCell(ExcelRangeBase cell) { if (cell.Value == null) { return(default(DateTime?)); } return(Int64.TryParse(cell.Value.ToString(), out long exceldate) ? DateTime.FromOADate(exceldate) : default(DateTime?)); }
public void CombineFiles(string command) { var parts = command.Split(','); if (parts.Length != 3) //needs to be changed to 4 when ready for the type argument { Console.WriteLine("Command not valid, Combine requires the name of both reports (including name and file extension) and the type of report for formatting."); return; } var file1 = parts[1]; var file2 = parts[2]; //var type = parts[3].ToLower(); //not needed yet var files = new string[] { file1, file2 }; var resultFile = @"C:\Users\elusk\Desktop\Combined_Report_" + DateTime.Now.ToString("yyyyMMddhhssmmm") + ".xlsx"; ExcelPackage masterPackage = new ExcelPackage(new FileInfo(resultFile)); var ws = masterPackage.Workbook.Worksheets.Add("Sheet1"); //possibly not set properly ExcelRangeBase rangeBase = ws.Cells[1, 1]; foreach (var file in files) { ExcelPackage pckg = new ExcelPackage(new FileInfo(file)); foreach (var sheet in pckg.Workbook.Worksheets) //-this section puts the worksheets into a single .xlsx file { //check name of worksheet, in case that worksheet with same name already exist exception will be thrown by EPPlus string workSheetName = sheet.Name; foreach (var masterSheet in masterPackage.Workbook.Worksheets) { int rowcount = sheet.Dimension.End.Row; int colcount = sheet.Dimension.End.Column; sheet.Cells[2, 1, rowcount, colcount].Copy(rangeBase); //possibly not set properly } //add new sheet if possible //try //{ // masterPackage.Workbook.Worksheets.Add(workSheetName, sheet); //} //catch (InvalidOperationException e) //{ // Console.WriteLine("Exception: " + e ); //} } } masterPackage.SaveAs(new FileInfo(resultFile)); Console.WriteLine("File created"); }
internal static void FixMergedCells(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeInsert shift) { if (shift == eShiftTypeInsert.Down) { FixMergedCellsRow(ws, range._fromRow, range.Rows, false, range._fromCol, range._toCol); } else { FixMergedCellsColumn(ws, range._fromCol, range.Columns, false, range._fromRow, range._toRow); } }
private static void AdjustDrawings(ExcelRangeBase range, eShiftTypeDelete shift) { if (shift == eShiftTypeDelete.Up) { WorksheetRangeHelper.AdjustDrawingsRow(range.Worksheet, range._fromRow, -range.Rows, range._fromCol, range._toCol); } else { WorksheetRangeHelper.AdjustDrawingsColumn(range.Worksheet, range._fromCol, -range.Columns, range._fromRow, range._toRow); } }
/// <summary> /// Get the table object from a range. /// </summary> /// <param name="Range">The range</param> /// <returns>The table. Null if no range matches</returns> public ExcelTable GetFromRange(ExcelRangeBase Range) { foreach (var tbl in Range.Worksheet.Tables) { if (tbl.Address._address == Range._address) { return(tbl); } } return(null); }
internal static void FixMergedCells(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeDelete shift) { if (shift == eShiftTypeDelete.Up) { FixMergedCellsRow(ws, range._fromRow, range.Rows, true, range._fromCol, range._toCol); } else { FixMergedCellsColumn(ws, range._fromCol, range.Columns, true, range._fromRow, range._toRow); } }
private static void SetEmailAsHyperlink(ExcelRangeBase range) { for (int row = 1; row <= range.Rows; row++) { var cell = range.Offset(row, 2, 1, 1); if (cell.Value != null) { cell.Hyperlink = new Uri($"mailto:{cell.Value}"); } } }
public static void Table(ExcelRangeBase range) { var headings = range.Offset( 0, 0, 1, range.End.Column - range.Start.Column + 1); Heading(headings); AllBorders(range); }
public static int[] GetRangeIndex(this ExcelRangeBase range) { #region deprecated // var add = range.Address; // var start = add.Split(':')[0].AddressToNumber(); // var end = add.Split(':')[1].AddressToNumber(); // return new[] { start[0], start[1], end[0], end[1] }; // RowST, ColST, RowED, ColED #endregion return(new[] { range.Start.Row, range.Start.Column, range.End.Row, range.End.Column }); }
private static void ClearDestination(ExcelRangeBase Destination, int rows, int cols) { Destination._worksheet.MergedCells.Clear(new ExcelAddressBase(Destination._fromRow, Destination._fromCol, Destination._fromRow + rows - 1, Destination._fromCol + cols - 1)); Destination._worksheet._values.Clear(Destination._fromRow, Destination._fromCol, rows, cols); Destination._worksheet._formulas.Clear(Destination._fromRow, Destination._fromCol, rows, cols); Destination._worksheet._hyperLinks.Clear(Destination._fromRow, Destination._fromCol, rows, cols); Destination._worksheet._flags.Clear(Destination._fromRow, Destination._fromCol, rows, cols); Destination._worksheet._commentsStore.Clear(Destination._fromRow, Destination._fromCol, rows, cols); Destination._worksheet._threadedCommentsStore.Clear(Destination._fromRow, Destination._fromCol, rows, cols); }
internal static TemplateSettingRangeGrid Create(ExcelRangeBase cell) { var entity = new TemplateSettingRangeGrid(); entity.Fields = new List <TemplateSettingField>(); entity.UnderGrid = new List <TemplateSettingRangeGrid>(); entity.Content = cell.Value?.ToString() ?? ""; entity.CurrentCell = cell; entity.AnalyseSetting(); return(entity); }
internal static void FormatAsTable(ExcelRangeBase range, TableStyles tableStyle, string tableName, bool autoFitColumns = true) { //format the table var table = range.Worksheet.Tables.Add(range, tableName); table.TableStyle = tableStyle; if (autoFitColumns) { range.AutoFitColumns(); } }
private XmlNode AddDrawing(ExcelRangeBase cell) { int row = cell.Start.Row, col = cell.Start.Column; var node = VmlDrawingXml.CreateElement("v", "shape", ExcelPackage.schemaMicrosoftVml); var id = ExcelCellBase.GetCellID(cell.Worksheet.SheetID, cell._fromRow, cell._fromCol); var ix = _drawings.IndexOf(id); if (ix < 0 && (~ix < _drawings.Count)) { ix = ~ix; var prevDraw = _drawings[ix] as ExcelVmlDrawingBase; var parentNode = prevDraw.TopNode.ParentNode; if (parentNode != null) { parentNode.InsertBefore(node, prevDraw.TopNode); } else { VmlDrawingXml.DocumentElement.AppendChild(node); } } else { VmlDrawingXml.DocumentElement.AppendChild(node); } node.SetAttribute("id", GetNewId()); node.SetAttribute("type", "#_x0000_t202"); node.SetAttribute("style", "position:absolute;z-index:1; visibility:hidden"); //node.SetAttribute("style", "position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:1; visibility:hidden"); node.SetAttribute("fillcolor", "#ffffe1"); node.SetAttribute("insetmode", ExcelPackage.schemaMicrosoftOffice, "auto"); string vml = "<v:fill color2=\"#ffffe1\" />"; vml += "<v:shadow on=\"t\" color=\"black\" obscured=\"t\" />"; vml += "<v:path o:connecttype=\"none\" />"; vml += "<v:textbox style=\"mso-direction-alt:auto\">"; vml += "<div style=\"text-align:left\" />"; vml += "</v:textbox>"; vml += "<x:ClientData ObjectType=\"Note\">"; vml += "<x:MoveWithCells />"; vml += "<x:SizeWithCells />"; vml += string.Format("<x:Anchor>{0}, 15, {1}, 2, {2}, 31, {3}, 1</x:Anchor>", col, row - 1, col + 2, row + 3); vml += "<x:AutoFill>False</x:AutoFill>"; vml += string.Format("<x:Row>{0}</x:Row>", row - 1);; vml += string.Format("<x:Column>{0}</x:Column>", col - 1); vml += "</x:ClientData>"; node.InnerXml = vml; return(node); }
private static string getCellText(ExcelRangeBase cell) { var txt = cell.Text; if (string.IsNullOrEmpty(txt)) { return(string.Empty); } var ty = cell.Value.GetType(); if (ty == typeof(string)) { return(cell.Text); } else if (ty == typeof(bool)) { return(cell.Value.ToString()); } else if (ty == typeof(DateTime)) { var style = DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeLocal; //DateTime result; //TimeSpan timespan; if (DateTime.TryParse(cell.Value.ToString(), CultureInfo.CurrentCulture, style, out var result)) { return(result.ToString(CultureInfo.CurrentCulture)); } else if (TimeSpan.TryParse(txt, out var timespan)) { return(DateTime.Now.Add(timespan).ToString(CultureInfo.CurrentCulture)); } else { return(DateTime.MinValue.ToString(CultureInfo.CurrentCulture)); } } else if (ty == typeof(decimal) || ty == typeof(double) || ty == typeof(float)) { if (decimal.TryParse(txt, out var num)) { return(num.ToString()); } else { return(txt); } } else { return(txt); } }
private static void FormatCell(ExcelRangeBase cell, DataColumn column) { //if (column.DataType == typeof(DateTime)) //{ // cell.Style.Numberformat.Format = System.Globalization.DateTimeFormatInfo.CurrentInfo.LongDatePattern; // return; //} //if (column.DataType.IsValueType) // == typeof(Decimal) || column.DataType == typeof(Double) || column.DataType == typeof(Single)) //{ // cell.Style.Numberformat.Format = "#,##0.00"; //} }
private static void FormatCell(ExcelRangeBase cell, DataColumn column) { if (column.DataType == typeof(DateTime)) { cell.Style.Numberformat.Format = System.Globalization.DateTimeFormatInfo.CurrentInfo.LongDatePattern; return; } if (column.DataType == typeof(Decimal) || column.DataType == typeof(Double) || column.DataType == typeof(Single)) { cell.Style.Numberformat.Format = "$#,###0.00"; } }
private ExcelRangeBase applyStyleDefault(ExcelRangeBase excelCell) { ExcelStyle style = excelCell.Style; style.Locked = true; //por defecto todas la columnas bloqueadas a edicion style.Font.Size = 11; style.Border.Top.Style = ExcelBorderStyle.Hair; style.ShrinkToFit = true; style.HorizontalAlignment = ExcelHorizontalAlignment.Center; setCellColor(style, Color.DimGray, ColorTranslator.FromHtml("#eaeded")); return(excelCell); }
/// <summary> /// Update the <see cref="CacheItem"/>s. /// </summary> /// <param name="sourceDataRange">The source range of the data without header row.</param> /// <param name="logger">The logger to use to log method calls.</param> public void UpdateRecords(ExcelRangeBase sourceDataRange, IFormulaParserLogger logger) { logger?.LogFunction(nameof(this.UpdateRecords)); // Remove extra records. if (sourceDataRange.Rows < this.Records.Count) { for (int i = this.Records.Count - 1; i >= sourceDataRange.Rows; i--) { this.Records[i].Remove(base.TopNode); } var count = this.Records.Count - sourceDataRange.Rows; if (count > 0) { this.Records.RemoveRange(sourceDataRange.Rows, count); } else { this.Records.RemoveAt(sourceDataRange.Rows); } } for (int row = sourceDataRange.Start.Row; row < sourceDataRange.Rows + sourceDataRange.Start.Row; row++) { int recordIndex = row - sourceDataRange.Start.Row; var rowCells = new List <object>(); int cacheFieldIndex = 0; for (int column = sourceDataRange.Start.Column; column < sourceDataRange.End.Column + 1; column++) { var cacheField = this.CacheDefinition.CacheFields[cacheFieldIndex]; var cell = sourceDataRange.Worksheet.Cells[row, column]; // If the cell value is a DateTime, convert it to an date. if (cacheField.SharedItems.ContainsDate == true && cell.Value is double) { rowCells.Add(DateTime.FromOADate((double)cell.Value)); } else { rowCells.Add(cell.Value); } cacheFieldIndex++; } // If the row is within the existing range of cacheRecords, update that cacheRecord. Otherwise, add a new record. if (recordIndex < this.Records.Count) { this.Records[recordIndex].Update(rowCells, this.CacheDefinition); } else { this.Records.Add(new CacheRecordNode(this.NameSpaceManager, base.TopNode, rowCells, this.CacheDefinition)); } } this.Count = this.Records.Count; }
private void BaseDeDatos(string filePath, ExcelPackage excelPackage, ExcelRangeBase excelRangeBase, ExcelWorksheet excelWorksheet) { //int nextRow = excelRangeBase.End.Row + 1; //TODO: el idioma... excelPackage.Workbook.Worksheets["MATRIZ"].Cells["K1"].Value = DateTime.Now.AddDays(-1).ToString("MMMM", CultureInfo.GetCultureInfo("es-PE")); excelWorksheet.Cells[1, excelRangeBase.End.Column + 1].Value = "CONCATENADO"; excelWorksheet.Cells[1, excelRangeBase.End.Column + 2].Value = "CODIGO"; excelWorksheet.Cells[2, excelRangeBase.End.Column + 1, excelRangeBase.End.Row + 1, excelRangeBase.End.Column + 1].Formula = "G2&H2&I2"; excelWorksheet.Cells[2, excelRangeBase.End.Column + 1, excelRangeBase.End.Row + 1, excelRangeBase.End.Column + +1].Calculate(); //El separador de parámetros de fórmula es ',' por que se usa la notación en inglés, igualmente con las formulas. excelWorksheet.Cells[2, excelRangeBase.End.Column + 2, excelRangeBase.End.Row + 1, excelRangeBase.End.Column + 2].Formula = "VLOOKUP(U2,CONCATENADO!$D$2:$E$304,2,0)"; //excelWorksheet.Cells[2, excelRangeBase.End.Column + 2, excelRangeBase.End.Row + 1, excelRangeBase.End.Column + 2].FormulaR1C1 = "VLOOKUP(U2,CONCATENADO!$D$2:$E$304,2,0)"; excelWorksheet.Cells[2, excelRangeBase.End.Column + 2, excelRangeBase.End.Row + 1, excelRangeBase.End.Column + 2].Calculate(); //TODO: take a look in future for performance decrease excelRangeBase.AutoFitColumns(); excelPackage.Save(); DataTable sheetData = GetDataExcel(filePath, "SELECT CODIGO,CANT_OPERACIONES FROM (SELECT CODIGO,SUM(CANT_OPERACIONES) AS CANT_OPERACIONES " + "FROM [BASE$] GROUP BY CODIGO) A " + "RIGHT JOIN [MATRIZ$] B " + "ON A.CODIGO = B.JEDOX"); excelPackage.Workbook.Worksheets["MATRIZ"].Cells[sheetData.Rows.Count + 3, 10].Value = "TOTAL:"; for (int i = 0; i <= sheetData.Rows.Count - 1; i++) { //if(sheetData.Rows[i]["CANT_OPERACIONES"] == DBNull.Value ? 0: Convert.ToDecimal(sheetData.Rows[i]["CANT_OPERACIONES"]) // 9 excelPackage.Workbook.Worksheets["MATRIZ"].Cells["K" + (i + 2)].Value = sheetData.Rows[i]["CANT_OPERACIONES"] == DBNull.Value ? 0 : Convert.ToDecimal(sheetData.Rows[i]["CANT_OPERACIONES"]); //sheetData.Rows[i]["CANT_OPERACIONES"]; } excelPackage.Workbook.Worksheets["MATRIZ"].Cells[sheetData.Rows.Count + 3, 11].Formula = "SUM(K2:K" + (sheetData.Rows.Count + 1) + ")"; excelPackage.Workbook.Worksheets["MATRIZ"].Cells[sheetData.Rows.Count + 3, 11].Calculate(); //excelWorksheet.Cells[nextRow, 4, nextRow, column].Formula = "SUM(E2:E10)"; //excelWorksheet.Cells[nextRow, 5, nextRow, column].Calculate(); excelPackage.Save(); //return sheetData; //TOTAL 18344 18344 }
public static T TryGetValue <T>(this ExcelRangeBase excelRangeBase, string catalog, ILogger <ExcelReaderService> logger) { try { return(excelRangeBase.GetValue <T>()); } catch (Exception) { logger.LogError($"Impossible de caster la case {excelRangeBase.Address} de {catalog} à {typeof(T)}"); return(default(T)); } }
private static async Task DynamicDateAugustFilter(string connectionString, ExcelPackage p) { var ws = p.Workbook.Worksheets.Add("DynamicAugustFilter"); ExcelRangeBase range = await LoadFromDatabase(connectionString, ws); range.AutoFilter = true; var colDynamic = ws.AutoFilter.Columns.AddDynamicFilterColumn(5); colDynamic.Type = eDynamicFilterType.M8; ws.AutoFilter.ApplyFilter(); range.AutoFitColumns(0); }
public static ExcelWorksheet SetURL(DataTable dt, ExcelWorksheet ws) { for (int i = 0; i < dt.Columns.Count; i++) { if (Convert.ToString(((object[, ])ws.Cells.Value)[0, i]) == "Lab") { for (int j = 1; j < dt.Rows.Count; j++) { if (((object[, ])ws.Cells.Value)[0, i] != null) { var a = ((object[, ])ws.Cells.Value)[j, i].ToString().Split(','); ExcelRangeBase objBase = ws.Cells[j + 1, i + 1]; objBase.Hyperlink = new Uri(a[0], UriKind.Absolute); objBase.Value = a[1]; } } } if (Convert.ToString(((object[, ])ws.Cells.Value)[0, i]) == "v360url") { for (int j = 1; j < dt.Rows.Count; j++) { if (((object[, ])ws.Cells.Value)[j, i] != null) { var a = ((object[, ])ws.Cells.Value)[j, i].ToString(); ExcelRangeBase objBase = ws.Cells[j + 1, i + 1]; if (a.Contains("http")) { objBase.Hyperlink = new Uri(a, UriKind.Absolute); objBase.Value = "360"; } } } } if (Convert.ToString(((object[, ])ws.Cells.Value)[0, i]) == "Video") { for (int j = 1; j < dt.Rows.Count; j++) { if (((object[, ])ws.Cells.Value)[0, i] != null) { var a = ((object[, ])ws.Cells.Value)[j, i].ToString(); ExcelRangeBase objBase = ws.Cells[j + 1, i + 1]; if (a.Contains("http")) { objBase.Hyperlink = new Uri(a, UriKind.Absolute); objBase.Value = "DNA"; } } } } } return(ws); }
/// <summary> /// Deletes a column from worksheet by using column header text /// </summary> /// <param name="worksheet"></param> /// <param name="headerText"></param> /// <returns></returns> public static ExcelWorksheet DeleteColumn(this ExcelWorksheet worksheet, string headerText) { ExcelAddressBase valuedDimension = worksheet.GetValuedDimension(); ExcelRangeBase headerColumn = worksheet.Cells[valuedDimension.Start.Row, valuedDimension.Start.Column, valuedDimension.Start.Row, valuedDimension.End.Column].FirstOrDefault(x => x.Text.Equals(headerText, StringComparison.InvariantCultureIgnoreCase)); if (headerColumn != null) { worksheet.DeleteColumn(headerColumn.Start.Column); } return(worksheet); }
private static async Task ValueFilter(string connectionString, ExcelPackage p) { var ws = p.Workbook.Worksheets.Add("ValueFilter"); ExcelRangeBase range = await LoadFromDatabase(connectionString, ws); range.AutoFilter = true; var colCompany = ws.AutoFilter.Columns.AddValueFilterColumn(0); colCompany.Filters.Add("Walsh LLC"); colCompany.Filters.Add("Harber-Goldner"); ws.AutoFilter.ApplyFilter(); range.AutoFitColumns(0); }
internal ExcelComment(XmlNamespaceManager ns, XmlNode commentTopNode, ExcelRangeBase cell) : base(null, cell, cell.Worksheet.VmlDrawingsComments.NameSpaceManager) { //_commentHelper = new XmlHelper(ns, commentTopNode); _commentHelper = XmlHelperFactory.Create(ns, commentTopNode); var textElem=commentTopNode.SelectSingleNode("d:text", ns); if (textElem == null) { textElem = commentTopNode.OwnerDocument.CreateElement("text", ExcelPackage.schemaMain); commentTopNode.AppendChild(textElem); } if (!cell.Worksheet._vmlDrawings.ContainsKey(ExcelAddress.GetCellID(cell.Worksheet.SheetID, cell.Start.Row, cell.Start.Column))) { cell.Worksheet._vmlDrawings.Add(cell); } TopNode = cell.Worksheet.VmlDrawingsComments[ExcelCellBase.GetCellID(cell.Worksheet.SheetID, cell.Start.Row, cell.Start.Column)].TopNode; RichText = new ExcelRichTextCollection(ns,textElem); }
internal ExcelPivotCacheDefinition(XmlNamespaceManager ns, ExcelPivotTable pivotTable) : base(ns, null) { foreach (var r in pivotTable.Part.GetRelationshipsByType(ExcelPackage.schemaRelationships + "/pivotCacheDefinition")) { Relationship = r; } CacheDefinitionUri = PackUriHelper.ResolvePartUri(Relationship.SourceUri, Relationship.TargetUri); var pck = pivotTable.WorkSheet.xlPackage.Package; Part = pck.GetPart(CacheDefinitionUri); CacheDefinitionXml = new XmlDocument(); CacheDefinitionXml.Load(Part.GetStream()); TopNode = CacheDefinitionXml.DocumentElement; PivotTable = pivotTable; if (CacheSource == eSourceType.Worksheet) { _sourceRange = pivotTable.WorkSheet.Workbook.Worksheets[GetXmlNodeString(_sourceWorksheetPath)].Cells[GetXmlNodeString(_sourceAddressPath)]; } }
private string GetStartXml(ExcelRangeBase sourceAddress) { string xml="<pivotCacheDefinition xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" r:id=\"\" refreshOnLoad=\"1\" refreshedBy=\"SomeUser\" refreshedDate=\"40504.582403125001\" createdVersion=\"1\" refreshedVersion=\"3\" recordCount=\"5\" upgradeOnRefresh=\"1\">"; xml += "<cacheSource type=\"worksheet\">"; xml += string.Format("<worksheetSource ref=\"{0}\" sheet=\"{1}\" /> ", sourceAddress.Address, sourceAddress.WorkSheet); xml += "</cacheSource>"; xml += string.Format("<cacheFields count=\"{0}\">", sourceAddress._toCol - sourceAddress._fromCol + 1); var sourceWorksheet = PivotTable.WorkSheet.Workbook.Worksheets[sourceAddress.WorkSheet]; for (int col = sourceAddress._fromCol; col <= sourceAddress._toCol; col++) { if (sourceWorksheet == null || sourceWorksheet.Cell(sourceAddress._fromRow, col).Value == null || sourceWorksheet.Cell(sourceAddress._fromRow, col).Value.ToString().Trim() == "") { xml += string.Format("<cacheField name=\"Column{0}\" numFmtId=\"0\">", col - sourceAddress._fromCol + 1); } else { xml += string.Format("<cacheField name=\"{0}\" numFmtId=\"0\">", sourceWorksheet.Cell(sourceAddress._fromRow, col).Value); } //xml += "<sharedItems containsNonDate=\"0\" containsString=\"0\" containsBlank=\"1\" /> "; xml += "<sharedItems containsBlank=\"1\" /> "; xml += "</cacheField>"; } xml += "</cacheFields>"; xml += "</pivotCacheDefinition>"; return xml; }
private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelRangeBase Range, ExcelCalculationOption options) { var ws = Range.Worksheet; var fs = new CellsStoreEnumerator<object>(ws._formulas, Range.Start.Row, Range.Start.Column, Range.End.Row, Range.End.Column); while (fs.Next()) { if (fs.Value == null || fs.Value.ToString().Trim() == "") continue; var id = ExcelCellBase.GetCellID(ws.SheetID, fs.Row, fs.Column); if (!depChain.index.ContainsKey(id)) { var f = new FormulaCell() { SheetID = ws.SheetID, Row = fs.Row, Column = fs.Column }; if (fs.Value is int) { f.Formula = ws._sharedFormulas[(int)fs.Value].GetFormula(fs.Row, fs.Column, ws.Name); } else { f.Formula = fs.Value.ToString(); } if (!string.IsNullOrEmpty(f.Formula)) { f.Tokens = lexer.Tokenize(f.Formula, Range.Worksheet.Name).ToList(); ws._formulaTokens.SetValue(fs.Row, fs.Column, f.Tokens); depChain.Add(f); FollowChain(depChain, lexer, ws.Workbook, ws, f, options); } } } }
internal ExcelVmlDrawingComment(XmlNode topNode, ExcelRangeBase range, XmlNamespaceManager ns) : base(topNode, ns) { Range = range; SchemaNodeOrder = new string[] { "fill", "stroke", "shadow", "path", "textbox", "ClientData", "MoveWithCells", "SizeWithCells", "Anchor", "Locked", "AutoFill", "LockText", "TextHAlign", "TextVAlign", "Row", "Column", "Visible" }; }
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"); } } }
/// <summary> /// Add a new serie to the chart. Do not apply to pivotcharts. /// </summary> /// <param name="Serie">The Y-Axis range</param> /// <param name="XSerie">The X-Axis range</param> /// <returns></returns> public ExcelChartSerie Add(ExcelRangeBase Serie, ExcelRangeBase XSerie) { if (_chart.PivotTableSource != null) { throw (new InvalidOperationException("Can't add a serie to a pivotchart")); } return AddSeries(Serie.FullAddressAbsolute, XSerie.FullAddressAbsolute); }
/// <summary> /// Add a new pivottable /// </summary> /// <param name="sheet">The worksheet</param> /// <param name="address">the address of the pivottable</param> /// <param name="sourceAddress">The address of the Source data</param> /// <param name="name"></param> /// <param name="tblId"></param> internal ExcelPivotTable(ExcelWorksheet sheet, ExcelAddressBase address,ExcelRangeBase sourceAddress, string name, int tblId) : base(sheet.NameSpaceManager) { WorkSheet = sheet; Address = address; var pck = sheet._package.Package; PivotTableXml = new XmlDocument(); LoadXmlSafe(PivotTableXml, GetStartXml(name, tblId, address, sourceAddress), Encoding.UTF8); TopNode = PivotTableXml.DocumentElement; PivotTableUri = GetNewUri(pck, "/xl/pivotTables/pivotTable{0}.xml", ref tblId); init(); Part = pck.CreatePart(PivotTableUri, ExcelPackage.schemaPivotTable); PivotTableXml.Save(Part.GetStream()); //Worksheet-Pivottable relationship Relationship = sheet.Part.CreateRelationship(UriHelper.ResolvePartUri(sheet.WorksheetUri, PivotTableUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotTable"); _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this, sourceAddress, tblId); _cacheDefinition.Relationship=Part.CreateRelationship(UriHelper.ResolvePartUri(PivotTableUri, _cacheDefinition.CacheDefinitionUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotCacheDefinition"); sheet.Workbook.AddPivotTable(CacheID.ToString(), _cacheDefinition.CacheDefinitionUri); LoadFields(); using (var r=sheet.Cells[address.Address]) { r.Clear(); } }
public ExcelChartSerie Add(ExcelRangeBase Serie, ExcelRangeBase XSerie) { return AddSeries(Serie.FullAddressAbsolute, XSerie.FullAddressAbsolute); }
internal ExcelVmlDrawingComment Add(ExcelRangeBase cell) { XmlNode node = AddDrawing(cell); var draw = new ExcelVmlDrawingComment(node, cell, NameSpaceManager); _drawings.Add(draw); return draw; }
internal ExcelVmlDrawing Add(ExcelRangeBase cell) { XmlNode node = AddDrawing(cell.Start.Row, cell.Start.Column); var draw = new ExcelVmlDrawing(node, cell, NameSpaceManager); _drawings.Add(draw); return draw; }
private XmlNode AddDrawing(ExcelRangeBase cell) { int row = cell.Start.Row, col = cell.Start.Column; var node = VmlDrawingXml.CreateElement("v", "shape", ExcelPackage.schemaMicrosoftVml); var id = ExcelCellBase.GetCellID(cell.Worksheet.SheetID, cell._fromRow, cell._fromCol); var ix = _drawings.IndexOf(id); if (ix < 0 && (~ix < _drawings.Count)) { ix = ~ix; var prevDraw = _drawings[ix] as ExcelVmlDrawingBase; prevDraw.TopNode.ParentNode.InsertBefore(node, prevDraw.TopNode); } else { VmlDrawingXml.DocumentElement.AppendChild(node); } node.SetAttribute("id", GetNewId()); node.SetAttribute("type", "#_x0000_t202"); node.SetAttribute("style", "position:absolute;z-index:1; visibility:hidden"); //node.SetAttribute("style", "position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:1; visibility:hidden"); node.SetAttribute("fillcolor", "#ffffe1"); node.SetAttribute("insetmode",ExcelPackage.schemaMicrosoftOffice,"auto"); string vml = "<v:fill color2=\"#ffffe1\" />"; vml += "<v:shadow on=\"t\" color=\"black\" obscured=\"t\" />"; vml += "<v:path o:connecttype=\"none\" />"; vml += "<v:textbox style=\"mso-direction-alt:auto\">"; vml += "<div style=\"text-align:left\" />"; vml += "</v:textbox>"; vml += "<x:ClientData ObjectType=\"Note\">"; vml += "<x:MoveWithCells />"; vml += "<x:SizeWithCells />"; vml += string.Format("<x:Anchor>{0}, 15, {1}, 2, {2}, 31, {3}, 1</x:Anchor>", col, row - 1, col + 2, row + 3); vml += "<x:AutoFill>False</x:AutoFill>"; vml += string.Format("<x:Row>{0}</x:Row>", row - 1); ; vml += string.Format("<x:Column>{0}</x:Column>", col - 1); vml += "</x:ClientData>"; node.InnerXml = vml; return node; }
/// <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; }
internal ExcelPivotCacheDefinition(XmlNamespaceManager ns, ExcelPivotTable pivotTable, ExcelRangeBase sourceAddress, int tblId) : base(ns, null) { PivotTable = pivotTable; var pck = pivotTable.WorkSheet.xlPackage.Package; //CacheDefinition CacheDefinitionXml = new XmlDocument(); CacheDefinitionXml.LoadXml(GetStartXml(sourceAddress)); CacheDefinitionUri=new Uri(string.Format("/xl/pivotCache/pivotCacheDefinition{0}.xml", tblId), UriKind.Relative); Part = pck.CreatePart(CacheDefinitionUri, ExcelPackage.schemaPivotCacheDefinition); TopNode = CacheDefinitionXml.DocumentElement; //CacheRecord. Create an empty one. CacheRecordUri = new Uri(string.Format("/xl/pivotCache/pivotCacheRecords{0}.xml", tblId), UriKind.Relative); var cacheRecord = new XmlDocument(); cacheRecord.LoadXml("<pivotCacheRecords xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" count=\"0\" />"); var recPart = pck.CreatePart(CacheRecordUri, ExcelPackage.schemaPivotCacheRecords); cacheRecord.Save(recPart.GetStream()); RecordRelationship = Part.CreateRelationship(PackUriHelper.ResolvePartUri(CacheDefinitionUri, CacheRecordUri), TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotCacheRecords"); RecordRelationshipID = RecordRelationship.Id; CacheDefinitionXml.Save(Part.GetStream()); }
internal static DependencyChain Create(ExcelRangeBase range, ExcelCalculationOption options) { var depChain = new DependencyChain(); GetChain(depChain, range.Worksheet.Workbook.FormulaParser.Lexer, range, options); return depChain; }
public ExcelChartSerie Add(ExcelRangeBase Serie, ExcelRangeBase XSerie, ExcelRangeBase BubbleSize) { return base.AddSeries(Serie.FullAddressAbsolute, XSerie.FullAddressAbsolute, BubbleSize.FullAddressAbsolute); }