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