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