public ExcelDatabaseCriteria(ExcelDataProvider dataProvider, string range) { _dataProvider = dataProvider; var address = new ExcelAddressBase(range); _fromCol = address._fromCol; _toCol = address._toCol; _worksheet = address.WorkSheet; _fieldRow = address._fromRow; Initialize(); }
public RangeInfo(ExcelWorksheet ws, int fromRow, int fromCol, int toRow, int toCol) { _ws = ws; _fromRow = fromRow; _fromCol = fromCol; _toRow = toRow; _toCol = toCol; _address = new ExcelAddressBase(_fromRow, _fromCol, _toRow, _toCol); _address._ws = ws.Name; _values = new CellsStoreEnumerator<object>(ws._values, _fromRow, _fromCol, _toRow, _toCol); _cell = new CellInfo(_ws, _values); }
/// <summary> /// 渲染多个单元格(一列数据) /// </summary> /// <param name="target"></param> /// <param name="parameters"></param> /// <param name="sheet"></param> /// <param name="insertRows"></param> /// <param name="tableKey"></param> /// <param name="rowCount"></param> /// <param name="cellString"></param> /// <param name="address"></param> private void RenderCells(Interpreter target, Parameter[] parameters, ExcelWorksheet sheet, int insertRows, string tableKey, int rowCount, string cellString, ExcelAddressBase address) { var dataVar = "\" + data." + tableKey + "[index]."; //渲染一列单元格 for (var i = 0; i < rowCount; i++) { var rowIndex = address.Start.Row + i + insertRows; var targetAddress = new ExcelAddress(rowIndex, address.Start.Column, rowIndex, address.Start.Column); RenderCell(target, sheet, cellString, targetAddress.Address, dataVar, null, parameters, i); } }
private static ExcelAddressBase DeleteSplitAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeDelete shift) { if (address.Addresses == null) { return(DeleteSplitIndividualAddress(address, range, effectedAddress, shift)); } else { var newAddress = ""; foreach (var a in address.Addresses) { var na = DeleteSplitIndividualAddress(a, range, effectedAddress, shift); if (na != null) { newAddress += na.Address + ","; } } if (string.IsNullOrEmpty(newAddress)) { return(null); } else { return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1))); } } }
internal ExcelTable(PackageRelationship rel, ExcelWorksheet sheet) : base(sheet.NameSpaceManager) { WorkSheet = sheet; TableUri = PackUriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri); RelationshipID = rel.Id; var pck = sheet._package.Package; Part=pck.GetPart(TableUri); TableXml = new XmlDocument(); LoadXmlSafe(TableXml, Part.GetStream()); init(); Address = new ExcelAddressBase(GetXmlNodeString("@ref")); }
private static void AdjustFormulasInsert(ExcelRangeBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift) { //Adjust formulas foreach (var ws in range._workbook.Worksheets) { var workSheetName = range.Worksheet.Name; var rowFrom = range._fromRow; var columnFrom = range._fromCol; var rows = range.Rows; foreach (var f in ws._sharedFormulas.Values) { if (workSheetName == ws.Name) { var a = new ExcelAddressBase(f.Address); var c = effectedAddress.Collide(a); if (c == ExcelAddressBase.eAddressCollition.Partly) { throw new Exception("Invalid shared formula"); //This should never happend! } if (f.StartCol >= columnFrom && c != ExcelAddressBase.eAddressCollition.No) { if (f.StartRow >= rowFrom) { f.StartRow += rows; } if (a._fromRow >= rowFrom) { a._fromRow += rows; a._toRow += rows; } else if (a._toRow >= rowFrom) { a._toRow += rows; } f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol); f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, range, effectedAddress, shift, ws.Name, workSheetName); } } else if (f.Formula.Contains(workSheetName)) { f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, range, effectedAddress, shift, ws.Name, workSheetName); } } var cse = new CellStoreEnumerator <object>(ws._formulas); while (cse.Next()) { if (cse.Value is string v) { if (workSheetName == ws.Name) { cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedAddress, shift, ws.Name, workSheetName); } else if (v.Contains(workSheetName)) { cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedAddress, shift, ws.Name, workSheetName); } } } } }
private static void DeleteConditionalFormatting(ExcelRangeBase range, eShiftTypeDelete shift, ExcelWorksheet ws, ExcelAddressBase effectedAddress) { //Update Conditional formatting references var deletedCF = new List <IExcelConditionalFormattingRule>(); foreach (var cf in ws.ConditionalFormatting) { var address = DeleteSplitAddress(cf.Address, range, effectedAddress, shift); if (address == null) { deletedCF.Add(cf); } else { ((ExcelConditionalFormattingRule)cf).Address = new ExcelAddress(address.Address); } } deletedCF.ForEach(cf => ws.ConditionalFormatting.Remove(cf)); }
private static void InsertSparkLinesAddress(ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress) { foreach (var slg in range.Worksheet.SparklineGroups) { if (slg.DateAxisRange != null && effectedAddress.Collide(slg.DateAxisRange) >= ExcelAddressBase.eAddressCollition.Inside) { string address; if (shift == eShiftTypeInsert.Down) { address = slg.DateAxisRange.AddRow(range._fromRow, range.Rows).Address; } else { address = slg.DateAxisRange.AddColumn(range._fromCol, range.Columns).Address; } slg.DateAxisRange = range.Worksheet.Cells[address]; } foreach (var sl in slg.Sparklines) { if (shift == eShiftTypeInsert.Down) { if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside || range.CollideFullRow(sl.RangeAddress._fromRow, sl.RangeAddress._toRow)) { sl.RangeAddress = sl.RangeAddress.AddRow(range._fromRow, range.Rows); } if (sl.Cell.Row >= range._fromRow && sl.Cell.Column >= range._fromCol && sl.Cell.Column <= range._toCol) { sl.Cell = new ExcelCellAddress(sl.Cell.Row + range.Rows, sl.Cell.Column); } } else { if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside || range.CollideFullColumn(sl.RangeAddress._fromCol, sl.RangeAddress._toCol)) { sl.RangeAddress = sl.RangeAddress.AddColumn(range._fromCol, range.Columns); } if (sl.Cell.Column >= range._fromCol && sl.Cell.Row >= range._fromRow && sl.Cell.Row <= range._toRow) { sl.Cell = new ExcelCellAddress(sl.Cell.Row, sl.Cell.Column + range.Columns); } } } } }
private static void InsertPivottableAddress(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress) { foreach (var ptbl in ws.PivotTables) { if (shift == eShiftTypeInsert.Down) { if (ptbl.Address._fromCol >= range._fromCol && ptbl.Address._toCol <= range._toCol) { ptbl.Address = ptbl.Address.AddRow(range._fromRow, range.Rows); } } else { if (ptbl.Address._fromRow >= range._fromRow && ptbl.Address._toRow <= range._toRow) { ptbl.Address = ptbl.Address.AddColumn(range._fromCol, range.Columns); } } if (ptbl.CacheDefinition.SourceRange.Worksheet == ws) { var address = ptbl.CacheDefinition.SourceRange; if (shift == eShiftTypeInsert.Down) { if (address._fromCol >= range._fromCol && address._toCol <= range._toCol) { ptbl.CacheDefinition.SourceRange = ws.Cells[address.AddRow(range._fromRow, range.Rows).Address]; } } else { if (address._fromRow >= range._fromRow && address._toRow <= range._toRow) { ptbl.CacheDefinition.SourceRange = ws.Cells[address.AddColumn(range._fromCol, range.Columns).Address]; } } } } }
internal ExcelPivotTable(Packaging.ZipPackageRelationship rel, ExcelWorksheet sheet) : base(sheet.NameSpaceManager) { WorkSheet = sheet; PivotTableUri = UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri); Relationship = rel; var pck = sheet._package.Package; Part=pck.GetPart(PivotTableUri); PivotTableXml = new XmlDocument(); LoadXmlSafe(PivotTableXml, Part.GetStream()); init(); TopNode = PivotTableXml.DocumentElement; Address = new ExcelAddressBase(GetXmlNodeString("d:location/@ref")); _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this); LoadFields(); //Add row fields. foreach (XmlElement rowElem in TopNode.SelectNodes("d:rowFields/d:field", NameSpaceManager)) { int x; if (int.TryParse(rowElem.GetAttribute("x"), out x) && x >= 0) { RowFields.AddInternal(Fields[x]); } else { rowElem.ParentNode.RemoveChild(rowElem); } } ////Add column fields. foreach (XmlElement colElem in TopNode.SelectNodes("d:colFields/d:field", NameSpaceManager)) { int x; if(int.TryParse(colElem.GetAttribute("x"),out x) && x >= 0) { ColumnFields.AddInternal(Fields[x]); } else { colElem.ParentNode.RemoveChild(colElem); } } //Add Page elements //int index = 0; foreach (XmlElement pageElem in TopNode.SelectNodes("d:pageFields/d:pageField", NameSpaceManager)) { int fld; if (int.TryParse(pageElem.GetAttribute("fld"), out fld) && fld >= 0) { var field = Fields[fld]; field._pageFieldSettings = new ExcelPivotTablePageFieldSettings(NameSpaceManager, pageElem, field, fld); PageFields.AddInternal(field); } } //Add data elements //index = 0; foreach (XmlElement dataElem in TopNode.SelectNodes("d:dataFields/d:dataField", NameSpaceManager)) { int fld; if (int.TryParse(dataElem.GetAttribute("fld"), out fld) && fld >= 0) { var field = Fields[fld]; var dataField = new ExcelPivotTableDataField(NameSpaceManager, dataElem, field); DataFields.AddInternal(dataField); } } }
private static void DeleteTableAddresses(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeDelete shift, ExcelAddressBase effectedAddress) { var deletedTbl = new List <ExcelTable>(); foreach (var tbl in ws.Tables) { if (shift == eShiftTypeDelete.Up) { if (tbl.Address._fromCol >= range._fromCol && tbl.Address._toCol <= range._toCol) { tbl.Address = tbl.Address.DeleteRow(range._fromRow, range.Rows); } } else { if (tbl.Address._fromRow >= range._fromRow && tbl.Address._toRow <= range._toRow) { tbl.Address = tbl.Address.DeleteColumn(range._fromCol, range.Columns); } } if (tbl.Address == null) { deletedTbl.Add(tbl); } } deletedTbl.ForEach(x => ws.Tables.Delete(x)); }
private void SetStyleAddress(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe) { if (address.Start.Column == 0 || address.Start.Row == 0) { throw new Exception("error address"); } //Columns if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows) { ExcelColumn column; int col = address.Start.Column, row = 0; bool isNew; //Get the startcolumn object o = null; if (!ws.ExistsValueInner(0, address.Start.Column, ref o)) { column = ws.Column(address.Start.Column); isNew = true; } else { //column = (ExcelColumn)ws.GetValueInner(0, address.Start.Column); column = (ExcelColumn)o; isNew = false; } var prevColumnMax = column.ColumnMax; while (column.ColumnMin <= address.End.Column) { if (column.ColumnMin > prevColumnMax + 1) { ExcelColumn newColumn = ws.Column(prevColumnMax + 1); newColumn.ColumnMax = column.ColumnMin - 1; AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID); } if (column.ColumnMax > address.End.Column) { ExcelColumn newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax); column.ColumnMax = address.End.Column; } var s = ws.GetStyleInner(0, column.ColumnMin); AddNewStyleColumn(sender, e, ws, styleCashe, column, s); //index++; prevColumnMax = column.ColumnMax; if (!ws._values.NextCell(ref row, ref col) || row > 0) { if (column._columnMax == address.End.Column) { break; } if (isNew) { column._columnMax = address.End.Column; } else { ExcelColumn newColumn = ws.Column(column._columnMax + 1); newColumn.ColumnMax = address.End.Column; AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID); column = newColumn; } break; } column = ws.GetValueInner(0, col) as ExcelColumn; } if (column._columnMax < address.End.Column) { ExcelColumn newCol = ws.Column(column._columnMax + 1); newCol._columnMax = address.End.Column; var s = ws.GetStyleInner(0, column.ColumnMin); if (styleCashe.ContainsKey(s)) { ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]); } else { ExcelXfs st = CellXfs[s]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(s, newId); ws.SetStyleInner(0, column.ColumnMin, newId); } column._columnMax = address.End.Column; } //Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first. var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, address._fromCol, address._toRow, address._toCol); while (cse.Next()) { if (cse.Column >= address.Start.Column && cse.Column <= address.End.Column && cse.Value._styleId != 0) { if (styleCashe.ContainsKey(cse.Value._styleId)) { ws.SetStyleInner(cse.Row, cse.Column, styleCashe[cse.Value._styleId]); } else { ExcelXfs st = CellXfs[cse.Value._styleId]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(cse.Value._styleId, newId); ws.SetStyleInner(cse.Row, cse.Column, newId); } } } if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns)) { //Update cells with styled columns cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, 0, address._toRow, 0); while (cse.Next()) { if (cse.Value._styleId == 0) { continue; } for (var c = address._fromCol; c <= address._toCol; c++) { if (!ws.ExistsStyleInner(cse.Row, c)) { if (styleCashe.ContainsKey(cse.Value._styleId)) { ws.SetStyleInner(cse.Row, c, styleCashe[cse.Value._styleId]); } else { ExcelXfs st = CellXfs[cse.Value._styleId]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(cse.Value._styleId, newId); ws.SetStyleInner(cse.Row, c, newId); } } } } } } //Rows else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns) { for (var rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++) { var s = ws.GetStyleInner(rowNum, 0); if (s == 0) { //iterate all columns and set the row to the style of the last column var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, ExcelPackage.MaxColumns); while (cse.Next()) { s = cse.Value._styleId; if (s == 0) { continue; } var c = ws.GetValueInner(cse.Row, cse.Column) as ExcelColumn; if (c != null && c.ColumnMax < ExcelPackage.MaxColumns) { for (var col = c.ColumnMin; col < c.ColumnMax; col++) { if (!ws.ExistsStyleInner(rowNum, col)) { ws.SetStyleInner(rowNum, col, s); } } } } ws.SetStyleInner(rowNum, 0, s); cse.Dispose(); } if (styleCashe.ContainsKey(s)) { ws.SetStyleInner(rowNum, 0, styleCashe[s]); } else { ExcelXfs st = CellXfs[s]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(s, newId); ws.SetStyleInner(rowNum, 0, newId); } } //Update individual cells var cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, address._fromRow, address._fromCol, address._toRow, address._toCol); while (cse2.Next()) { var s = cse2.Value._styleId; if (s == 0) { continue; } if (styleCashe.ContainsKey(s)) { ws.SetStyleInner(cse2.Row, cse2.Column, styleCashe[s]); } else { ExcelXfs st = CellXfs[s]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(s, newId); ws.SetStyleInner(cse2.Row, cse2.Column, newId); } } //Update cells with styled rows cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, address._toCol); while (cse2.Next()) { if (cse2.Value._styleId == 0) { continue; } for (var r = address._fromRow; r <= address._toRow; r++) { if (!ws.ExistsStyleInner(r, cse2.Column)) { var s = cse2.Value._styleId; if (styleCashe.ContainsKey(s)) { ws.SetStyleInner(r, cse2.Column, styleCashe[s]); } else { ExcelXfs st = CellXfs[s]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(s, newId); ws.SetStyleInner(r, cse2.Column, newId); } } } } } else //Cell range { var tmpCache = styleCashe; var rowCache = new Dictionary <int, int>(address.End.Row - address.Start.Row + 1); var colCache = new Dictionary <int, ExcelCoreValue>(address.End.Column - address.Start.Column + 1); ws._values.SetRangeValueSpecial(address.Start.Row, address.Start.Column, address.End.Row, address.End.Column, (list, index, row, column, args) => { // Optimized GetStyleID var s = list[index]._styleId; if (s == 0 && !ws.ExistsStyleInner(row, 0, ref s)) { // get row styleId with cache if (!rowCache.ContainsKey(row)) { rowCache.Add(row, ws._values.GetValue(row, 0)._styleId); } s = rowCache[row]; if (s == 0) { // get column styleId with cache if (!colCache.ContainsKey(column)) { colCache.Add(column, ws._values.GetValue(0, column)); } s = colCache[column]._styleId; if (s == 0) { int r = 0, c = column; if (ws._values.PrevCell(ref r, ref c)) { //var val = ws._values.GetValue(0, c); if (!colCache.ContainsKey(c)) { colCache.Add(c, ws._values.GetValue(0, c)); } ExcelCoreValue val = colCache[c]; var colObj = (ExcelColumn)val._value; if (colObj != null && colObj.ColumnMax >= column) //Fixes issue 15174 { s = val._styleId; } } } } } if (tmpCache.ContainsKey(s)) { //ws.SetStyleInner(row, column, tmpCache[s]); list[index] = new ExcelCoreValue { _value = list[index]._value, _styleId = tmpCache[s] }; } else { ExcelXfs st = CellXfs[s]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); tmpCache.Add(s, newId); //ws.SetStyleInner(row, column, newId); list[index] = new ExcelCoreValue { _value = list[index]._value, _styleId = newId }; } }, e); } }
/// <summary> /// 渲染表格单元格 /// </summary> /// <param name="target"></param> /// <param name="tbParameters"></param> /// <param name="sheet"></param> /// <param name="insertRows"></param> /// <param name="tableKey"></param> /// <param name="rowCount"></param> /// <param name="writer"></param> /// <param name="address"></param> private void RenderTableCells(Interpreter target, Parameter[] tbParameters, ExcelWorksheet sheet, int insertRows, string tableKey, int rowCount, IWriter writer, ExcelAddressBase address) { var cellString = writer.CellString; if (cellString.Contains("{{Table>>")) { //{{ Table >> BookInfo | RowNo}} cellString = "{{" + cellString.Split('|')[1].Trim(); } else if (cellString.Contains(">>Table}}")) { //{{Remark|>>Table}} cellString = cellString.Split('|')[0].Trim() + "}}"; } RenderCells(target, tbParameters, sheet, insertRows, tableKey, rowCount, cellString, address); }
public ExcelFileReader(IDictionary <int, ExcelFilePropertySetter> propertySetters, ExcelAddressBase columnHeaderRange, ExcelAddressBase dataRange, DataOrientation datasetOrientation) { PropertySetters = propertySetters; ColumnHeaderRange = columnHeaderRange; DataRange = dataRange; DatasetOrientation = datasetOrientation; }
/// <summary> /// Checks whether given range empty or not /// </summary> /// <param name="address">Excel cell range</param> /// <param name="hasHeaderRow">'false' as default</param> /// <returns>'true' or 'false'</returns> public static bool IsEmptyRange(this ExcelAddressBase address, bool hasHeaderRow = false) => !hasHeaderRow ? address.Start.Row == 0 : address.Start.Row == address.End.Row;
private static void CopyDrawing(ExcelPackage pck, XmlNamespaceManager nsm, ExcelWorksheet Copy, ExcelWorksheet added) { //First copy the drawing XML string xml = Copy.Drawings.DrawingXml.OuterXml; var uriDraw = new Uri(string.Format("/xl/drawings/drawing{0}.xml", added.SheetId), UriKind.Relative); var part = pck.ZipPackage.CreatePart(uriDraw, "application/vnd.openxmlformats-officedocument.drawing+xml", pck.Compression); StreamWriter streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write)); streamDrawing.Write(xml); streamDrawing.Flush(); XmlDocument drawXml = new XmlDocument(); drawXml.LoadXml(xml); //Add the relationship ID to the worksheet xml. var drawRelation = added.Part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, uriDraw), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/drawing"); XmlElement e = added.WorksheetXml.SelectSingleNode("//d:drawing", nsm) as XmlElement; e.SetAttribute("id", ExcelPackage.schemaRelationships, drawRelation.Id); for (int i = 0; i < Copy.Drawings.Count; i++) { ExcelDrawing draw = Copy.Drawings[i]; //draw.AdjustPositionAndSize(); //Adjust position for any change in normal style font/row size etc. if (draw is ExcelChart chart) { xml = chart.ChartXml.InnerXml; var UriChart = XmlHelper.GetNewUri(pck.ZipPackage, "/xl/charts/chart{0}.xml"); var chartPart = pck.ZipPackage.CreatePart(UriChart, ContentTypes.contentTypeChart, pck.Compression); StreamWriter streamChart = new StreamWriter(chartPart.GetStream(FileMode.Create, FileAccess.Write)); streamChart.Write(xml); streamChart.Flush(); //Now create the new relationship to the copied chart xml var prevRelID = draw.TopNode.SelectSingleNode("xdr:graphicFrame/a:graphic/a:graphicData/c:chart/@r:id", Copy.Drawings.NameSpaceManager).Value; var rel = part.CreateRelationship(UriHelper.GetRelativeUri(uriDraw, UriChart), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/chart"); XmlAttribute relAtt = drawXml.SelectSingleNode(string.Format("//c:chart/@r:id[.='{0}']", prevRelID), Copy.Drawings.NameSpaceManager) as XmlAttribute; relAtt.Value = rel.Id; } else if (draw is ExcelPicture pic) { IPictureContainer container = pic; var uri = container.UriPic; var img = PictureStore.ImageToByteArray(pic.Image); var ii = added.Workbook._package.PictureStore.AddImage(img, null, pic.ContentType); var rel = part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, ii.Uri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/image"); //Fixes problem with invalid image when the same image is used more than once. XmlNode relAtt = drawXml.SelectSingleNode( string.Format( "//xdr:pic/xdr:nvPicPr/xdr:cNvPr/@name[.='{0}']/../../../xdr:blipFill/a:blip/@r:embed", pic.Name), Copy.Drawings.NameSpaceManager); if (relAtt != null) { relAtt.Value = rel.Id; } } else if (draw is ExcelTableSlicer slicer) { var name = pck.Workbook.GetSlicerName(slicer.Name); var newSlicer = added.Drawings[i] as ExcelTableSlicer; newSlicer.Name = name; newSlicer.SlicerName = name; //The slicer still reference the copied slicers cache. We need to create a new cache for the copied slicer. newSlicer.CreateNewCache(); } else if (draw is ExcelPivotTableSlicer ptSlicer) { var name = pck.Workbook.GetSlicerName(ptSlicer.Name); var newSlicer = added.Drawings[i] as ExcelPivotTableSlicer; newSlicer.Name = name; newSlicer.SlicerName = name; //The slicer still reference the copied slicers cache. We need to create a new cache for the copied slicer. newSlicer.CreateNewCache(ptSlicer.Cache._field); } else if (draw is ExcelControl ctrl) { var UriCtrl = XmlHelper.GetNewUri(pck.ZipPackage, "/xl/ctrlProps/ctrlProp{0}.xml"); var ctrlPart = pck.ZipPackage.CreatePart(UriCtrl, ContentTypes.contentTypeControlProperties, pck.Compression); StreamWriter streamChart = new StreamWriter(ctrlPart.GetStream(FileMode.Create, FileAccess.Write)); streamChart.Write(ctrl.ControlPropertiesXml.OuterXml); streamChart.Flush(); var prevRelID = ctrl._control.RelationshipId; var rel = added.Part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, UriCtrl), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/ctrlProp"); XmlAttribute relAtt = added.WorksheetXml.SelectSingleNode(string.Format("//d:control/@r:id[.='{0}']", prevRelID), added.NameSpaceManager) as XmlAttribute; relAtt.Value = rel.Id; } } //rewrite the drawing xml with the new relID's streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write)); streamDrawing.Write(drawXml.OuterXml); streamDrawing.Flush(); //Copy the size variables to the copy. for (int i = 0; i < Copy.Drawings.Count; i++) { var draw = Copy.Drawings[i]; var c = added.Drawings[i]; if (c != null) { c._left = draw._left; c._top = draw._top; c._height = draw._height; c._width = draw._width; } if (c is ExcelChart chart) { for (int j = 0; i < chart.Series.Count; i++) { var s = chart.Series[j]; var a = new ExcelAddressBase(s.Series); if (a.WorkSheetName.Equals(Copy.Name)) { s.Series = ExcelAddressBase.GetFullAddress(added.Name, a.LocalAddress); } a = new ExcelAddressBase(s.XSeries); if (a.WorkSheetName.Equals(Copy.Name)) { s.XSeries = ExcelAddressBase.GetFullAddress(added.Name, a.LocalAddress); } } } } }
private static void FixFormulasDelete(ExcelRangeBase range, ExcelAddressBase effectedRange, eShiftTypeDelete shift) { foreach (var ws in range.Worksheet.Workbook.Worksheets) { var workSheetName = range.WorkSheetName; var rowFrom = range._fromRow; var rows = range.Rows; var delSF = new List <int>(); foreach (var sf in ws._sharedFormulas.Values) { if (workSheetName == ws.Name) { if (effectedRange.Collide(new ExcelAddressBase(sf.Address)) != ExcelAddressBase.eAddressCollition.No) { ExcelAddressBase a; if (shift == eShiftTypeDelete.Up) { a = new ExcelAddress(sf.Address).DeleteRow(range._fromRow, rows); } else { a = new ExcelAddress(sf.Address).DeleteColumn(range._fromRow, rows); } if (a == null) { delSF.Add(sf.Index); } else { sf.Address = a.Address; sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, range, effectedRange, shift, ws.Name, workSheetName); if (sf.StartRow >= rowFrom) { var r = Math.Max(rowFrom, sf.StartRow - rows); sf.StartRow = r; } } } } else if (sf.Formula.Contains(workSheetName)) { sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, -rows, 0, rowFrom, 0, ws.Name, workSheetName); } } foreach (var ix in delSF) { ws._sharedFormulas.Remove(ix); } var cse = new CellStoreEnumerator <object>(ws._formulas, 1, 1, ExcelPackage.MaxRows, ExcelPackage.MaxColumns); while (cse.Next()) { if (cse.Value is string v) { if (workSheetName == ws.Name || v.IndexOf(workSheetName, StringComparison.CurrentCultureIgnoreCase) >= 0) { cse.Value = ExcelCellBase.UpdateFormulaReferences(v, range, effectedRange, shift, ws.Name, workSheetName); } } } } }
internal static void DeleteColumn(ExcelWorksheet ws, int columnFrom, int columns) { ValidateColumn(columnFrom, columns); lock (ws) { AdjustColumnMinMaxDelete(ws, columnFrom, columns); var delRange = new ExcelAddressBase(1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1); WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, delRange); DeleteCellStores(ws, 0, columnFrom, 0, columns); foreach (var wsToUpdate in ws.Workbook.Worksheets) { FixFormulasDeleteColumn(wsToUpdate, columnFrom, columns, ws.Name); } WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, true); foreach (var tbl in ws.Tables) { if (columnFrom >= tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column) { var node = tbl.Columns[0].TopNode.ParentNode; var ix = columnFrom - tbl.Address.Start.Column; for (int i = 0; i < columns; i++) { if (node.ChildNodes.Count > ix) { node.RemoveChild(node.ChildNodes[ix]); } } tbl._cols = new ExcelTableColumnCollection(tbl); } tbl.Address = tbl.Address.DeleteColumn(columnFrom, columns); } foreach (var ptbl in ws.PivotTables) { if (ptbl.Address.Start.Column >= columnFrom + columns) { ptbl.Address = ptbl.Address.DeleteColumn(columnFrom, columns); } if (ptbl.CacheDefinition.SourceRange.Start.Column > columnFrom + columns) { ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.DeleteColumn(columnFrom, columns).Address; } } var range = ws.Cells[1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1]; var effectedAddress = GetEffectedRange(range, eShiftTypeDelete.Left); DeleteDataValidations(range, eShiftTypeDelete.Left, ws, effectedAddress); DeleteConditionalFormatting(range, eShiftTypeDelete.Left, ws, effectedAddress); DeleteFilterAddress(range, effectedAddress, eShiftTypeDelete.Left); DeleteSparkLinesAddress(range, eShiftTypeDelete.Left, effectedAddress); //Adjust drawing positions. WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, -columns); } }
public override INameInfo GetName(string worksheet, string name) { ExcelNamedRange nameItem; ulong id; ExcelWorksheet ws; if (string.IsNullOrEmpty(worksheet)) { if (_package._workbook.Names.ContainsKey(name)) { nameItem = _package._workbook.Names[name]; } else { return(null); } ws = null; } else { ws = _package._workbook.Worksheets[worksheet]; if (ws != null && ws.Names.ContainsKey(name)) { nameItem = ws.Names[name]; } else if (_package._workbook.Names.ContainsKey(name)) { nameItem = _package._workbook.Names[name]; } else { return(null); } } id = ExcelAddressBase.GetCellID(nameItem.LocalSheetId, nameItem.Index, 0); if (_names.ContainsKey(id)) { return(_names[id]); } else { var ni = new NameInfo() { Id = id, Name = name, Worksheet = nameItem.Worksheet == null ? nameItem._ws : nameItem.Worksheet.Name, Formula = nameItem.Formula }; if (nameItem._fromRow > 0) { ni.Value = new RangeInfo(nameItem.Worksheet ?? ws, nameItem._fromRow, nameItem._fromCol, nameItem._toRow, nameItem._toCol); } else { ni.Value = nameItem.Value; } _names.Add(id, ni); return(ni); } }
/// <summary> /// This method follows the calculation chain to get the order of the calculation /// Goto (!) is used internally to prevent stackoverflow on extremly larget dependency trees (that is, many recursive formulas). /// </summary> /// <param name="depChain">The dependency chain object</param> /// <param name="lexer">The formula tokenizer</param> /// <param name="wb">The workbook where the formula comes from</param> /// <param name="ws">The worksheet where the formula comes from</param> /// <param name="f">The cell function object</param> /// <param name="options">Calcultaiton options</param> private static void FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options) { Stack <FormulaCell> stack = new Stack <FormulaCell>(); iterateToken: while (f.tokenIx < f.Tokens.Count) { var t = f.Tokens[f.tokenIx]; if (t.TokenType == TokenType.ExcelAddress) { var adr = new ExcelFormulaAddress(t.Value); if (adr.Table != null) { adr.SetRCFromTable(ws._package, new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column)); } if (adr.WorkSheet == null && adr.Collide(new ExcelAddressBase(f.Row, f.Column, f.Row, f.Column)) != ExcelAddressBase.eAddressCollition.No && !options.AllowCirculareReferences) { throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}", ExcelAddressBase.GetAddress(f.Row, f.Column)))); } if (adr._fromRow > 0 && adr._fromCol > 0) { if (string.IsNullOrEmpty(adr.WorkSheet)) { if (f.ws == null) { f.ws = ws; } else if (f.ws.SheetID != f.SheetID) { f.ws = wb.Worksheets.GetBySheetID(f.SheetID); } } else { f.ws = wb.Worksheets[adr.WorkSheet]; } if (f.ws != null) { f.iterator = new CellsStoreEnumerator <object>(f.ws._formulas, adr.Start.Row, adr.Start.Column, adr.End.Row, adr.End.Column); goto iterateCells; } } } else if (t.TokenType == TokenType.NameValue) { string adrWb, adrWs, adrName; ExcelNamedRange name; ExcelAddressBase.SplitAddress(t.Value, out adrWb, out adrWs, out adrName, f.ws == null ? "" : f.ws.Name); if (!string.IsNullOrEmpty(adrWs)) { if (f.ws == null) { f.ws = wb.Worksheets[adrWs]; } if (f.ws.Names.ContainsKey(t.Value)) { name = f.ws.Names[adrName]; } else if (wb.Names.ContainsKey(adrName)) { name = wb.Names[adrName]; } else { name = null; } if (name != null) { f.ws = name.Worksheet; } } else if (wb.Names.ContainsKey(adrName)) { name = wb.Names[t.Value]; if (string.IsNullOrEmpty(adrWs)) { f.ws = name.Worksheet; } } else { name = null; } if (name != null) { if (string.IsNullOrEmpty(name.NameFormula)) { if (name.NameValue == null) { f.iterator = new CellsStoreEnumerator <object>(f.ws._formulas, name.Start.Row, name.Start.Column, name.End.Row, name.End.Column); goto iterateCells; } } else { var id = ExcelAddressBase.GetCellID(name.LocalSheetId, name.Index, 0); if (!depChain.index.ContainsKey(id)) { var rf = new FormulaCell() { SheetID = name.LocalSheetId, Row = name.Index, Column = 0 }; rf.Formula = name.NameFormula; rf.Tokens = name.LocalSheetId == -1 ? lexer.Tokenize(rf.Formula).ToList() : lexer.Tokenize(rf.Formula, wb.Worksheets.GetBySheetID(name.LocalSheetId).Name).ToList(); depChain.Add(rf); stack.Push(f); f = rf; goto iterateToken; } else { if (stack.Count > 0) { //Check for circular references foreach (var par in stack) { if (ExcelAddressBase.GetCellID(par.SheetID, par.Row, par.Column) == id && !options.AllowCirculareReferences) { throw (new CircularReferenceException(string.Format("Circular Reference in name {0}", name.Name))); } } } } } } } f.tokenIx++; } depChain.CalcOrder.Add(f.Index); if (stack.Count > 0) { f = stack.Pop(); goto iterateCells; } return; iterateCells: while (f.iterator != null && f.iterator.Next()) { var v = f.iterator.Value; if (v == null || v.ToString().Trim() == "") { continue; } var id = ExcelAddressBase.GetCellID(f.ws.SheetID, f.iterator.Row, f.iterator.Column); if (!depChain.index.ContainsKey(id)) { var rf = new FormulaCell() { SheetID = f.ws.SheetID, Row = f.iterator.Row, Column = f.iterator.Column }; if (f.iterator.Value is int) { rf.Formula = f.ws._sharedFormulas[(int)v].GetFormula(f.iterator.Row, f.iterator.Column, ws.Name); } else { rf.Formula = v.ToString(); } rf.ws = f.ws; rf.Tokens = lexer.Tokenize(rf.Formula, f.ws.Name).ToList(); ws._formulaTokens.SetValue(rf.Row, rf.Column, rf.Tokens); depChain.Add(rf); stack.Push(f); f = rf; goto iterateToken; } else { if (stack.Count > 0) { //Check for circular references foreach (var par in stack) { if (ExcelAddressBase.GetCellID(par.ws.SheetID, par.iterator.Row, par.iterator.Column) == id) { if (options.AllowCirculareReferences == false) { throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column)))); } else { f = stack.Pop(); goto iterateCells; } } } } } } f.tokenIx++; goto iterateToken; }
private static void InsertDataValidation(ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress, ExcelWorksheet ws) { //Update data validation references foreach (var dv in ws.DataValidations) { ((ExcelDataValidation)dv).SetAddress(InsertSplitAddress(dv.Address, range, effectedAddress, shift).Address); } }
public RangeInfo(ExcelWorksheet ws, ExcelAddressBase address) { _ws = ws; _fromRow = address._fromRow; _fromCol = address._fromCol; _toRow = address._toRow; _toCol = address._toCol; _address = address; _address._ws = ws.Name; _values = new CellsStoreEnumerator<ExcelCoreValue>(ws._values, _fromRow, _fromCol, _toRow, _toCol); _cell = new CellInfo(_ws, _values); }
private static ExcelAddressBase InsertSplitAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeInsert shift) { if (address.Addresses == null) { return(InsertSplitIndividualAddress(address, range, effectedAddress, shift)); } else { var newAddress = ""; foreach (var a in address.Addresses) { newAddress += InsertSplitIndividualAddress(a, range, effectedAddress, shift) + ","; } return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1))); } }
internal void ConvertRichtext() { if (_cells == null) { return; } var isRt = _cells.Worksheet._flags.GetFlagValue(_cells._fromRow, _cells._fromCol, CellFlags.RichText); if (Count == 1 && isRt == false) { _cells.Worksheet._flags.SetFlagValue(_cells._fromRow, _cells._fromCol, true, CellFlags.RichText); var s = _cells.Worksheet.GetStyleInner(_cells._fromRow, _cells._fromCol); //var fnt = cell.Style.Font; var fnt = _cells.Worksheet.Workbook.Styles.GetStyleObject(s, _cells.Worksheet.PositionID, ExcelAddressBase.GetAddress(_cells._fromRow, _cells._fromCol)).Font; this[0].PreserveSpace = true; this[0].Bold = fnt.Bold; this[0].FontName = fnt.Name; this[0].Italic = fnt.Italic; this[0].Size = fnt.Size; this[0].UnderLine = fnt.UnderLine; int hex; if (fnt.Color.Rgb != "" && int.TryParse(fnt.Color.Rgb, NumberStyles.HexNumber, null, out hex)) { this[0].Color = Color.FromArgb(hex); } } }
private static void InsertTableAddress(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress) { foreach (var tbl in ws.Tables) { if (shift == eShiftTypeInsert.Down) { if (tbl.Address._fromCol >= range._fromCol && tbl.Address._toCol <= range._toCol) { tbl.Address = tbl.Address.AddRow(range._fromRow, range.Rows); } } else { if (tbl.Address._fromRow >= range._fromRow && tbl.Address._toRow <= range._toRow) { tbl.Address = tbl.Address.AddColumn(range._fromCol, range.Columns); } } } }
private ExcelSparklineGroup AddGroup(eSparklineType type, ExcelAddressBase locationRange, ExcelAddressBase dataRange, bool isRows) { var group = NewSparklineGroup(); group.Type = type; var row = locationRange._fromRow; var col = locationRange._fromCol; var drFromRow = dataRange._fromRow; var drFromCol = dataRange._fromCol; var drToRow = isRows ? dataRange._fromRow : dataRange._toRow; var drToCol = isRows ? dataRange._toCol : dataRange._fromCol; var cells = (locationRange._fromRow == locationRange._toRow ? locationRange._toCol - locationRange._fromCol: locationRange._toRow - locationRange._fromRow) + 1; var cell = 0; while (cell < cells) { var f = new ExcelCellAddress(row, col); var sqref = new ExcelAddressBase(dataRange.WorkSheet, drFromRow, drFromCol, drToRow, drToCol); group.Sparklines.Add(f, dataRange.WorkSheet, sqref); cell++; if (locationRange._fromRow == locationRange._toRow) { col++; } else { row++; } if (isRows) { drFromRow++; drToRow++; } else { drFromCol++; drToCol++; } } group.ColorSeries.Rgb = "FF376092"; group.ColorNegative.Rgb = "FFD00000"; group.ColorMarkers.Rgb = "FFD00000"; group.ColorAxis.Rgb = "FF000000"; group.ColorFirst.Rgb = "FFD00000"; group.ColorLast.Rgb = "FFD00000"; group.ColorHigh.Rgb = "FFD00000"; group.ColorLow.Rgb = "FFD00000"; _lst.Add(group); return(group); }
private static void DeleteSparkLinesAddress(ExcelRangeBase range, eShiftTypeDelete shift, ExcelAddressBase effectedAddress) { var delSparklineGroups = new List <ExcelSparklineGroup>(); foreach (var slg in range.Worksheet.SparklineGroups) { if (slg.DateAxisRange != null && effectedAddress.Collide(slg.DateAxisRange) >= ExcelAddressBase.eAddressCollition.Inside) { string address; if (shift == eShiftTypeDelete.Up) { address = slg.DateAxisRange.DeleteRow(range._fromRow, range.Rows).Address; } else { address = slg.DateAxisRange.DeleteColumn(range._fromCol, range.Columns).Address; } slg.DateAxisRange = address == null? null : range.Worksheet.Cells[address]; } var delSparklines = new List <ExcelSparkline>(); foreach (var sl in slg.Sparklines) { if (range.Collide(sl.Cell.Row, sl.Cell.Column) >= ExcelAddressBase.eAddressCollition.Inside) { delSparklines.Add(sl); } else if (shift == eShiftTypeDelete.Up) { if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside || range.CollideFullRow(sl.RangeAddress._fromRow, sl.RangeAddress._toRow)) { sl.RangeAddress = sl.RangeAddress.DeleteRow(range._fromRow, range.Rows); } if (sl.Cell.Row >= range._fromRow && sl.Cell.Column >= range._fromCol && sl.Cell.Column <= range._toCol) { sl.Cell = new ExcelCellAddress(sl.Cell.Row - range.Rows, sl.Cell.Column); } } else { if (effectedAddress.Collide(sl.RangeAddress) >= ExcelAddressBase.eAddressCollition.Inside || range.CollideFullColumn(sl.RangeAddress._fromCol, sl.RangeAddress._toCol)) { sl.RangeAddress = sl.RangeAddress.DeleteColumn(range._fromCol, range.Columns); } if (sl.Cell.Column >= range._fromCol && sl.Cell.Row >= range._fromRow && sl.Cell.Row <= range._toRow) { sl.Cell = new ExcelCellAddress(sl.Cell.Row, sl.Cell.Column - range.Columns); } } } delSparklines.ForEach(x => slg.Sparklines.Remove(x)); if (slg.Sparklines.Count == 0) { delSparklineGroups.Add(slg); } } delSparklineGroups.ForEach(x => range.Worksheet.SparklineGroups.Remove(x)); }
/// <summary> /// Adds a new sparklinegroup to the collection /// </summary> /// <param name="type">Type of sparkline</param> /// <param name="locationRange">The location of the sparkline group. The range must have one row or column and must match the number of rows/columns in the datarange</param> /// <param name="dataRange">The data for the sparkline group</param> /// <returns></returns> public ExcelSparklineGroup Add(eSparklineType type, ExcelAddressBase locationRange, ExcelAddressBase dataRange) { if (locationRange.Rows == 1) { if (locationRange.Columns == dataRange.Rows) { return(AddGroup(type, locationRange, dataRange, true)); } else if (locationRange.Columns == dataRange.Columns) { return(AddGroup(type, locationRange, dataRange, false)); } else { throw (new ArgumentException("dataRange is not valid. dataRange columns or rows must match number of rows in locationRange")); } } else if (locationRange.Columns == 1) { if (locationRange.Rows == dataRange.Columns) { return(AddGroup(type, locationRange, dataRange, false)); } else if (locationRange.Rows == dataRange.Rows) { return(AddGroup(type, locationRange, dataRange, true)); } else { throw (new ArgumentException("dataRange is not valid. dataRange columns or rows must match number of columns in locationRange")); } } else { throw (new ArgumentException("locationRange is not valid. Range must be one Column or Row only")); } }
private static void DeleteDataValidations(ExcelRangeBase range, eShiftTypeDelete shift, ExcelWorksheet ws, ExcelAddressBase effectedAddress) { //Update data validation references var deletedDV = new List <IExcelDataValidation>(); foreach (var dv in ws.DataValidations) { var address = DeleteSplitAddress(dv.Address, range, effectedAddress, shift); if (address == null) { deletedDV.Add(dv); } else { ((ExcelDataValidation)dv).SetAddress(address.Address); } } deletedDV.ForEach(dv => ws.DataValidations.Remove(dv)); }
/// <summary> /// /// </summary> /// <param name="worksheetName">will be used if no worksheet name is specified in <paramref name="address"/></param> /// <param name="address">address of a range</param> /// <returns></returns> public RangeAddress Create(string worksheetName, string address) { Require.That(address).Named("range").IsNotNullOrEmpty(); //var addressInfo = ExcelAddressInfo.Parse(address); var adr = new ExcelAddressBase(address); var sheet = string.IsNullOrEmpty(adr.WorkSheet) ? worksheetName : adr.WorkSheet; var dim = _excelDataProvider.GetDimensionEnd(adr.WorkSheet); var rangeAddress = new RangeAddress() { Address = adr.Address, Worksheet = sheet, FromRow = adr._fromRow, FromCol = adr._fromCol, ToRow = (dim != null && adr._toRow > dim.Row) ? dim.Row : adr._toRow, ToCol = adr._toCol }; //if (addressInfo.IsMultipleCells) //{ // HandleMultipleCellAddress(rangeAddress, addressInfo); //} //else //{ // HandleSingleCellAddress(rangeAddress, addressInfo); //} return rangeAddress; }
private static ExcelAddressBase DeleteSplitIndividualAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeDelete shift) { if (address.CollideFullRowOrColumn(range)) { if (range.CollideFullColumn(address._fromCol, address._toCol)) { return(address.DeleteColumn(range._fromCol, range.Columns)); } else { return(address.DeleteRow(range._fromRow, range.Rows)); } } else { var collide = effectedAddress.Collide(address); if (collide == ExcelAddressBase.eAddressCollition.Partly) { var addressToShift = effectedAddress.Intersect(address); var shiftedAddress = ShiftAddress(addressToShift, range, shift); var newAddress = ""; if (address._fromRow < addressToShift._fromRow) { newAddress = ExcelCellBase.GetAddress(address._fromRow, address._fromCol, addressToShift._fromRow - 1, address._toCol) + ","; } if (address._fromCol < addressToShift._fromCol) { var fromRow = Math.Max(address._fromRow, addressToShift._fromRow); newAddress += ExcelCellBase.GetAddress(fromRow, address._fromCol, address._toRow, addressToShift._fromCol - 1) + ","; } if (shiftedAddress != null) { newAddress += $"{shiftedAddress.Address},"; } if (address._toRow > addressToShift._toRow) { newAddress += ExcelCellBase.GetAddress(addressToShift._toRow + 1, address._fromCol, address._toRow, address._toCol) + ","; } if (address._toCol > addressToShift._toCol) { newAddress += ExcelCellBase.GetAddress(address._fromRow, addressToShift._toCol + 1, address._toRow, address._toCol) + ","; } return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1))); } else if (collide != ExcelAddressBase.eAddressCollition.No) { return(ShiftAddress(address, range, shift)); } } return(address); }
private void SetExcelTableDefinition() { var address = new ExcelAddressBase(1, 1, this._collection.Count + 1, this._propertyCollection.Count); this.ExcelWorksheet.Tables.Add(address, this.ExcelWorksheet.Name); }
internal ExcelTable(ExcelWorksheet sheet, ExcelAddressBase address, string name, int tblId) : base(sheet.NameSpaceManager) { WorkSheet = sheet; Address = address; TableXml = new XmlDocument(); LoadXmlSafe(TableXml, GetStartXml(name, tblId)); TopNode = TableXml.DocumentElement; init(); //If the table is just one row we can not have a header. if (address._fromRow == address._toRow) { ShowHeader = false; } }
internal ExcelPivotTable(PackageRelationship rel, ExcelWorksheet sheet) : base(sheet.NameSpaceManager) { WorkSheet = sheet; PivotTableUri = PackUriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri); Relationship = rel; var pck = sheet._package.Package; Part = pck.GetPart(PivotTableUri); PivotTableXml = new XmlDocument(); LoadXmlSafe(PivotTableXml, Part.GetStream()); init(); TopNode = PivotTableXml.DocumentElement; Address = new ExcelAddressBase(GetXmlNodeString("d:location/@ref")); _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this); LoadFields(); //Add row fields. foreach (XmlElement rowElem in TopNode.SelectNodes("d:rowFields/d:field", NameSpaceManager)) { int x; if (int.TryParse(rowElem.GetAttribute("x"), out x) && x >= 0) { RowFields.AddInternal(Fields[x]); } else { rowElem.ParentNode.RemoveChild(rowElem); } } ////Add column fields. foreach (XmlElement colElem in TopNode.SelectNodes("d:colFields/d:field", NameSpaceManager)) { int x; if (int.TryParse(colElem.GetAttribute("x"), out x) && x >= 0) { ColumnFields.AddInternal(Fields[x]); } else { colElem.ParentNode.RemoveChild(colElem); } } //Add Page elements //int index = 0; foreach (XmlElement pageElem in TopNode.SelectNodes("d:pageFields/d:pageField", NameSpaceManager)) { int fld; if (int.TryParse(pageElem.GetAttribute("fld"), out fld) && fld >= 0) { var field = Fields[fld]; field._pageFieldSettings = new ExcelPivotTablePageFieldSettings(NameSpaceManager, pageElem, field, fld); PageFields.AddInternal(field); } } //Add data elements //index = 0; foreach (XmlElement dataElem in TopNode.SelectNodes("d:dataFields/d:dataField", NameSpaceManager)) { int fld; if (int.TryParse(dataElem.GetAttribute("fld"), out fld) && fld >= 0) { var field = Fields[fld]; var dataField = new ExcelPivotTableDataField(NameSpaceManager, dataElem, field); DataFields.AddInternal(dataField); } } }
/// <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(); } }
/// <summary> /// Create a pivottable on the supplied range /// </summary> /// <param name="Range">The range address including header and total row</param> /// <param name="PivotCacheDefinition">A pivot table cache shared with another pivot table</param> /// <param name="Name">The name of the pivottable. Must be unique </param> /// <returns>The pivottable object</returns> public ExcelPivotTable Add(ExcelAddressBase Range, ExcelPivotCacheDefinition PivotCacheDefinition, string Name) { return(Add(new ExcelPivotTable(_ws, Range, PivotCacheDefinition._cacheReference, Name, _ws.Workbook._nextPivotTableID++))); }
private string GetStartXml(string name, int id, ExcelAddressBase address, ExcelAddressBase sourceAddress) { string xml = string.Format("<pivotTableDefinition xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" name=\"{0}\" cacheId=\"{1}\" dataOnRows=\"1\" applyNumberFormats=\"0\" applyBorderFormats=\"0\" applyFontFormats=\"0\" applyPatternFormats=\"0\" applyAlignmentFormats=\"0\" applyWidthHeightFormats=\"1\" dataCaption=\"Data\" createdVersion=\"4\" showMemberPropertyTips=\"0\" useAutoFormatting=\"1\" itemPrintTitles=\"1\" indent=\"0\" compact=\"0\" compactData=\"0\" gridDropZones=\"1\">", name, id); xml += string.Format("<location ref=\"{0}\" firstHeaderRow=\"1\" firstDataRow=\"1\" firstDataCol=\"1\" /> ", address.FirstAddress); xml += string.Format("<pivotFields count=\"{0}\">", sourceAddress._toCol-sourceAddress._fromCol+1); for (int col = sourceAddress._fromCol; col <= sourceAddress._toCol; col++) { xml += "<pivotField showAll=\"0\" />"; //compact=\"0\" outline=\"0\" subtotalTop=\"0\" includeNewItemsInFilter=\"1\" } xml += "</pivotFields>"; xml += "<pivotTableStyleInfo name=\"PivotStyleMedium9\" showRowHeaders=\"1\" showColHeaders=\"1\" showRowStripes=\"0\" showColStripes=\"0\" showLastColumn=\"1\" />"; xml += "</pivotTableDefinition>"; return xml; }
private static void InsertConditionalFormatting(ExcelRangeBase range, eShiftTypeInsert shift, ExcelAddressBase effectedAddress, ExcelWorksheet ws) { //Update Conditional formatting references foreach (var cf in ws.ConditionalFormatting) { ((ExcelConditionalFormattingRule)cf).Address = new ExcelAddress(InsertSplitAddress(cf.Address, range, effectedAddress, shift).Address); } }
public RangeAddress Create(string range) { Require.That(range).Named("range").IsNotNullOrEmpty(); //var addressInfo = ExcelAddressInfo.Parse(range); var adr = new ExcelAddressBase(range); var rangeAddress = new RangeAddress() { Address = adr.Address, Worksheet = adr.WorkSheet ?? "", FromRow = adr._fromRow, FromCol = adr._fromCol, ToRow = adr._toRow, ToCol = adr._toCol }; //if (addressInfo.IsMultipleCells) //{ // HandleMultipleCellAddress(rangeAddress, addressInfo); //} //else //{ // HandleSingleCellAddress(rangeAddress, addressInfo); //} return rangeAddress; }
private static void DeletePivottableAddresses(ExcelWorksheet ws, ExcelRangeBase range, eShiftTypeDelete shift, ExcelAddressBase effectedAddress) { var deletedPt = new List <ExcelPivotTable>(); foreach (var ptbl in ws.PivotTables) { if (shift == eShiftTypeDelete.Up) { if (ptbl.Address._fromCol >= range._fromCol && ptbl.Address._toCol <= range._toCol) { ptbl.Address = ptbl.Address.DeleteRow(range._fromRow, range.Rows); } } else { if (ptbl.Address._fromRow >= range._fromRow && ptbl.Address._toRow <= range._toRow) { ptbl.Address = ptbl.Address.DeleteColumn(range._fromCol, range.Columns); } } if (ptbl.Address == null) { deletedPt.Add(ptbl); } else { foreach (var wsSource in ws.Workbook.Worksheets) { if (ptbl.CacheDefinition.SourceRange.Worksheet == wsSource) { var address = ptbl.CacheDefinition.SourceRange; if (shift == eShiftTypeDelete.Up) { if (address._fromCol >= range._fromCol && address._toCol <= range._toCol) { var deletedRange = ws.Cells[address.DeleteRow(range._fromRow, range.Rows).Address]; if (deletedRange != null) { ptbl.CacheDefinition.SourceRange = deletedRange; } } } else { if (address._fromRow >= range._fromRow && address._toRow <= range._toRow) { var deletedRange = ws.Cells[address.DeleteColumn(range._fromCol, range.Columns).Address]; if (deletedRange != null) { ptbl.CacheDefinition.SourceRange = deletedRange; } } } } } } } deletedPt.ForEach(x => ws.PivotTables.Delete(x)); }