protected override IExternalExcelSheet AddOrGetInternal(string sheetName)
{
if (_obj == null)
{
return(null);
}
XLWorksheet ws = null;
if (!_write)
{
foreach (var ws1 in _obj.Worksheets.OfType <XLWorksheet>())
{
try
{
if (ws1.Name.IgnoreCaseCompare(sheetName))
{
ws = ws1;
break;
}
}
catch { }
}
}
if (ws == null)
{
ws = _obj.Worksheets.Add(sheetName) as XLWorksheet;
}
return(new CloseXMLExcelSheet(ws));
}
/// <summary>
/// The direct contructor should only be used in <see cref="XLWorksheet.RangeFactory"/>.
/// </summary>
public XLRow(XLWorksheet worksheet, Int32 row)
: base(XLRangeAddress.EntireRow(worksheet, row), worksheet.StyleValue)
{
SetRowNumber(row);
_height = worksheet.RowHeight;
}
public XLHeaderFooter(XLWorksheet worksheet)
{
Left = new XLHFItem(worksheet);
Right = new XLHFItem(worksheet);
Center = new XLHFItem(worksheet);
SetAsInitial();
}
/// <summary>
/// The direct contructor should only be used in <see cref="XLWorksheet.RangeFactory"/>.
/// </summary>
public XLColumn(XLWorksheet worksheet, Int32 column)
: base(XLRangeAddress.EntireColumn(worksheet, column), worksheet.StyleValue)
{
SetColumnNumber(column);
Width = worksheet.ColumnWidth;
}
public XLPageSetup(XLPageSetup defaultPageOptions, XLWorksheet worksheet)
{
if (defaultPageOptions != null)
{
PrintAreas = new XLPrintAreas(defaultPageOptions.PrintAreas as XLPrintAreas, worksheet);
CenterHorizontally = defaultPageOptions.CenterHorizontally;
CenterVertically = defaultPageOptions.CenterVertically;
FirstPageNumber = defaultPageOptions.FirstPageNumber;
HorizontalDpi = defaultPageOptions.HorizontalDpi;
PageOrientation = defaultPageOptions.PageOrientation;
VerticalDpi = defaultPageOptions.VerticalDpi;
FirstRowToRepeatAtTop = defaultPageOptions.FirstRowToRepeatAtTop;
LastRowToRepeatAtTop = defaultPageOptions.LastRowToRepeatAtTop;
FirstColumnToRepeatAtLeft = defaultPageOptions.FirstColumnToRepeatAtLeft;
LastColumnToRepeatAtLeft = defaultPageOptions.LastColumnToRepeatAtLeft;
ShowComments = defaultPageOptions.ShowComments;
PaperSize = defaultPageOptions.PaperSize;
_pagesTall = defaultPageOptions.PagesTall;
_pagesWide = defaultPageOptions.PagesWide;
_scale = defaultPageOptions.Scale;
if (defaultPageOptions.Margins != null)
{
Margins = new XLMargins
{
Top = defaultPageOptions.Margins.Top,
Bottom = defaultPageOptions.Margins.Bottom,
Left = defaultPageOptions.Margins.Left,
Right = defaultPageOptions.Margins.Right,
Header = defaultPageOptions.Margins.Header,
Footer = defaultPageOptions.Margins.Footer
};
}
AlignHFWithMargins = defaultPageOptions.AlignHFWithMargins;
ScaleHFWithDocument = defaultPageOptions.ScaleHFWithDocument;
ShowGridlines = defaultPageOptions.ShowGridlines;
ShowRowAndColumnHeadings = defaultPageOptions.ShowRowAndColumnHeadings;
BlackAndWhite = defaultPageOptions.BlackAndWhite;
DraftQuality = defaultPageOptions.DraftQuality;
PageOrder = defaultPageOptions.PageOrder;
ColumnBreaks = defaultPageOptions.ColumnBreaks.ToList();
RowBreaks = defaultPageOptions.RowBreaks.ToList();
Header = new XLHeaderFooter(defaultPageOptions.Header as XLHeaderFooter, worksheet);
Footer = new XLHeaderFooter(defaultPageOptions.Footer as XLHeaderFooter, worksheet);
PrintErrorValue = defaultPageOptions.PrintErrorValue;
}
else
{
PrintAreas = new XLPrintAreas(worksheet);
Header = new XLHeaderFooter(worksheet);
Footer = new XLHeaderFooter(worksheet);
ColumnBreaks = new List <Int32>();
RowBreaks = new List <Int32>();
}
}
public XLHeaderFooter(XLWorksheet worksheet)
{
this.Worksheet = worksheet;
Left = new XLHFItem(this);
Right = new XLHFItem(this);
Center = new XLHFItem(this);
SetAsInitial();
}
public XLSheetView(XLWorksheet worksheet, XLSheetView sheetView)
: this(worksheet)
{
this.SplitRow = sheetView.SplitRow;
this.SplitColumn = sheetView.SplitColumn;
this.FreezePanes = sheetView.FreezePanes;
this.TopLeftCellAddress = new XLAddress(this.Worksheet, sheetView.TopLeftCellAddress.RowNumber, sheetView.TopLeftCellAddress.ColumnNumber, sheetView.TopLeftCellAddress.FixedRow, sheetView.TopLeftCellAddress.FixedColumn);
}
public XLHeaderFooter(XLHeaderFooter defaultHF, XLWorksheet worksheet)
{
defaultHF.innerTexts.ForEach(kp => innerTexts.Add(kp.Key, kp.Value));
Left = new XLHFItem(defaultHF.Left as XLHFItem, worksheet);
Center = new XLHFItem(defaultHF.Center as XLHFItem, worksheet);
Right = new XLHFItem(defaultHF.Right as XLHFItem, worksheet);
SetAsInitial();
}
public XLRangeFactory(XLWorksheet worksheet)
{
if (worksheet == null)
{
throw new ArgumentNullException(nameof(worksheet));
}
Worksheet = worksheet;
}
public XLSheetView(XLWorksheet worksheet)
{
Worksheet = worksheet;
View = XLSheetViewOptions.Normal;
ZoomScale = 100;
ZoomScaleNormal = 100;
ZoomScalePageLayoutView = 100;
ZoomScaleSheetLayoutView = 100;
}
/// <summary>
/// Initializes a new <see cref = "XLAddress" /> struct using R1C1 notation.
/// </summary>
/// <param name = "worksheet"></param>
/// <param name = "rowNumber">The row number of the cell address.</param>
/// <param name = "columnNumber">The column number of the cell address.</param>
/// <param name = "fixedRow"></param>
/// <param name = "fixedColumn"></param>
public XLAddress(XLWorksheet worksheet, int rowNumber, int columnNumber, bool fixedRow, bool fixedColumn) : this()
{
Worksheet = worksheet;
_rowNumber = rowNumber;
_columnNumber = columnNumber;
_fixedColumn = fixedColumn;
_fixedRow = fixedRow;
}
public static XLAddress Create(XLWorksheet worksheet, string cellAddressString)
{
var fixedColumn = cellAddressString[0] == '$';
Int32 startPos;
if (fixedColumn)
{
startPos = 1;
}
else
{
startPos = 0;
}
int rowPos = startPos;
while (cellAddressString[rowPos] > '9')
{
rowPos++;
}
var fixedRow = cellAddressString[rowPos] == '$';
string columnLetter;
int rowNumber;
if (fixedRow)
{
if (fixedColumn)
{
columnLetter = cellAddressString.Substring(startPos, rowPos - 1);
}
else
{
columnLetter = cellAddressString.Substring(startPos, rowPos);
}
rowNumber = int.Parse(cellAddressString.Substring(rowPos + 1), XLHelper.NumberFormatForParse);
}
else
{
if (fixedColumn)
{
columnLetter = cellAddressString.Substring(startPos, rowPos - 1);
}
else
{
columnLetter = cellAddressString.Substring(startPos, rowPos);
}
rowNumber = Int32.Parse(cellAddressString.Substring(rowPos), XLHelper.NumberFormatForParse);
}
return(new XLAddress(worksheet, rowNumber, columnLetter, fixedRow, fixedColumn));
}
internal IXLPicture CopyTo(XLWorksheet targetSheet)
{
if (targetSheet == null)
{
targetSheet = Worksheet as XLWorksheet;
}
IXLPicture newPicture;
if (targetSheet == Worksheet)
{
newPicture = targetSheet.AddPicture(ImageStream, Format);
}
else
{
newPicture = targetSheet.AddPicture(ImageStream, Format, Name);
}
newPicture = newPicture
.WithPlacement(XLPicturePlacement.FreeFloating)
.WithSize(Width, Height)
.WithPlacement(Placement);
switch (Placement)
{
case XLPicturePlacement.FreeFloating:
newPicture.MoveTo(Left, Top);
break;
case XLPicturePlacement.Move:
var newAddress = new XLAddress(targetSheet, TopLeftCellAddress.RowNumber,
TopLeftCellAddress.ColumnNumber, false, false);
newPicture.MoveTo(newAddress, GetOffset(XLMarkerPosition.TopLeft));
break;
case XLPicturePlacement.MoveAndSize:
var newFromAddress = new XLAddress(targetSheet, TopLeftCellAddress.RowNumber,
TopLeftCellAddress.ColumnNumber, false, false);
var newToAddress = new XLAddress(targetSheet, BottomRightCellAddress.RowNumber,
BottomRightCellAddress.ColumnNumber, false, false);
newPicture.MoveTo(newFromAddress, GetOffset(XLMarkerPosition.TopLeft), newToAddress,
GetOffset(XLMarkerPosition.BottomRight));
break;
}
return(newPicture);
}
public XLChart(XLWorksheet worksheet)
{
Container = this;
this.worksheet = worksheet;
Int32 zOrder;
if (worksheet.Charts.Any())
{
zOrder = worksheet.Charts.Max(c => c.ZOrder) + 1;
}
else
{
zOrder = 1;
}
ZOrder = zOrder;
ShapeId = worksheet.Workbook.ShapeIdManager.GetNext();
RightAngleAxes = true;
}
public XLRangeAddress(XLWorksheet worksheet, String rangeAddress)
{
string addressToUse = rangeAddress.Contains("!")
? rangeAddress.Substring(rangeAddress.IndexOf("!") + 1)
: rangeAddress;
string firstPart;
string secondPart;
if (addressToUse.Contains(':'))
{
var arrRange = addressToUse.Split(':');
firstPart = arrRange[0];
secondPart = arrRange[1];
}
else
{
firstPart = addressToUse;
secondPart = addressToUse;
}
if (XLHelper.IsValidA1Address(firstPart))
{
FirstAddress = XLAddress.Create(worksheet, firstPart);
LastAddress = XLAddress.Create(worksheet, secondPart);
}
else
{
firstPart = firstPart.Replace("$", String.Empty);
secondPart = secondPart.Replace("$", String.Empty);
if (char.IsDigit(firstPart[0]))
{
FirstAddress = XLAddress.Create(worksheet, "A" + firstPart);
LastAddress = XLAddress.Create(worksheet, XLHelper.MaxColumnLetter + secondPart);
}
else
{
FirstAddress = XLAddress.Create(worksheet, firstPart + "1");
LastAddress = XLAddress.Create(worksheet, secondPart + XLHelper.MaxRowNumber.ToInvariantString());
}
}
Worksheet = worksheet;
}
internal IXLTable CopyTo(XLWorksheet targetSheet, bool copyData = true)
{
if (targetSheet == Worksheet)
{
throw new InvalidOperationException("Cannot copy table to the worksheet it already belongs to.");
}
var targetRange = targetSheet.Range(RangeAddress.WithoutWorksheet());
if (copyData)
{
RangeUsed().CopyTo(targetRange);
}
else
{
HeadersRow().CopyTo(targetRange.FirstRow());
}
String tableName = Name;
var newTable = (XLTable)targetSheet.Table(targetRange, tableName, true);
newTable.RelId = RelId;
newTable.EmphasizeFirstColumn = EmphasizeFirstColumn;
newTable.EmphasizeLastColumn = EmphasizeLastColumn;
newTable.ShowRowStripes = ShowRowStripes;
newTable.ShowColumnStripes = ShowColumnStripes;
newTable.ShowAutoFilter = ShowAutoFilter;
newTable.Theme = Theme;
newTable._showTotalsRow = ShowTotalsRow;
Int32 fieldCount = ColumnCount();
for (Int32 f = 0; f < fieldCount; f++)
{
var tableField = newTable.Field(f) as XLTableField;
var tField = Field(f) as XLTableField;
tableField.Index = tField.Index;
tableField.Name = tField.Name;
tableField.totalsRowLabel = tField.totalsRowLabel;
tableField.totalsRowFunction = tField.totalsRowFunction;
}
return(newTable);
}
internal IXLPicture CopyTo(XLWorksheet targetSheet)
{
if (targetSheet == null)
{
targetSheet = Worksheet as XLWorksheet;
}
IXLPicture newPicture;
if (targetSheet == Worksheet)
{
newPicture = targetSheet.AddPicture(ImageStream, Format);
}
else
{
newPicture = targetSheet.AddPicture(ImageStream, Format, Name);
}
newPicture = newPicture
.WithPlacement(XLPicturePlacement.FreeFloating)
.WithSize(Width, Height)
.WithPlacement(Placement);
switch (Placement)
{
case XLPicturePlacement.FreeFloating:
newPicture.MoveTo(Left, Top);
break;
case XLPicturePlacement.Move:
newPicture.MoveTo(targetSheet.Cell(TopLeftCell.Address), GetOffset(XLMarkerPosition.TopLeft));
break;
case XLPicturePlacement.MoveAndSize:
newPicture.MoveTo(targetSheet.Cell(TopLeftCell.Address), GetOffset(XLMarkerPosition.TopLeft), targetSheet.Cell(BottomRightCell.Address),
GetOffset(XLMarkerPosition.BottomRight));
break;
}
return(newPicture);
}
internal void SwapRanges(XLSheetRange sheetRange1, XLSheetRange sheetRange2, XLWorksheet worksheet)
{
Int32 rowCount = sheetRange1.LastPoint.Row - sheetRange1.FirstPoint.Row + 1;
Int32 columnCount = sheetRange1.LastPoint.Column - sheetRange1.FirstPoint.Column + 1;
for (int row = 0; row < rowCount; row++)
{
for (int column = 0; column < columnCount; column++)
{
var sp1 = new XLSheetPoint(sheetRange1.FirstPoint.Row + row, sheetRange1.FirstPoint.Column + column);
var sp2 = new XLSheetPoint(sheetRange2.FirstPoint.Row + row, sheetRange2.FirstPoint.Column + column);
var cell1 = GetCell(sp1);
var cell2 = GetCell(sp2);
if (cell1 == null)
{
cell1 = worksheet.Cell(sp1.Row, sp1.Column);
}
if (cell2 == null)
{
cell2 = worksheet.Cell(sp2.Row, sp2.Column);
}
//if (cell1 != null)
//{
cell1.Address = new XLAddress(cell1.Worksheet, sp2.Row, sp2.Column, false, false);
Remove(sp1);
//if (cell2 != null)
Add(sp1, cell2);
//}
//if (cell2 == null) continue;
cell2.Address = new XLAddress(cell2.Worksheet, sp1.Row, sp1.Column, false, false);
Remove(sp2);
//if (cell1 != null)
Add(sp2, cell1);
}
}
}
private IEnumerable <XLSheetPoint> GetUsedCellsCandidates(XLWorksheet worksheet)
{
var candidates = Enumerable.Empty <XLSheetPoint>();
if (_options.HasFlag(XLCellsUsedOptions.MergedRanges))
{
candidates = candidates.Union(
worksheet.Internals.MergedRanges.SelectMany(r => GetAllCellsInRange(r.RangeAddress)));
}
if (_options.HasFlag(XLCellsUsedOptions.ConditionalFormats))
{
candidates = candidates.Union(
worksheet.ConditionalFormats.SelectMany(cf => cf.Ranges.SelectMany(r => GetAllCellsInRange(r.RangeAddress))));
}
if (_options.HasFlag(XLCellsUsedOptions.DataValidation))
{
candidates = candidates.Union(
worksheet.DataValidations.SelectMany(dv => dv.Ranges.SelectMany(r => GetAllCellsInRange(r.RangeAddress))));
}
return(candidates.Distinct());
}
public XLRows(XLWorksheet worksheet)
{
_worksheet = worksheet;
style = new XLStyle(this, XLWorkbook.DefaultStyle);
}
public XLColumnParameters(XLWorksheet worksheet, Int32 defaultStyleId, Boolean isReference)
{
Worksheet = worksheet;
DefaultStyleId = defaultStyleId;
IsReference = isReference;
}
private XLDataValidation(XLWorksheet worksheet)
{
_worksheet = worksheet ?? throw new ArgumentNullException(nameof(worksheet));
_ranges = new XLRanges();
Initialize();
}
public XLPrintAreas(XLWorksheet worksheet)
{
this.worksheet = worksheet;
}
public XLColumnParameters(XLWorksheet worksheet, IXLStyle defaultStyle)
{
Worksheet = worksheet;
DefaultStyle = defaultStyle;
//IsReference = isReference;
}
public XLDataValidation(IXLDataValidation dataValidation, XLWorksheet worksheet)
: this(worksheet)
{
_worksheet = worksheet;
CopyFrom(dataValidation);
}
public XLNamedRanges(XLWorksheet worksheet)
: this(worksheet.Workbook)
{
Worksheet = worksheet;
}
/// <summary>
/// Initializes a new <see cref = "XLAddress" /> struct using a mixed notation.
/// </summary>
/// <param name = "worksheet"></param>
/// <param name = "rowNumber">The row number of the cell address.</param>
/// <param name = "columnLetter">The column letter of the cell address.</param>
/// <param name = "fixedRow"></param>
/// <param name = "fixedColumn"></param>
public XLAddress(XLWorksheet worksheet, int rowNumber, string columnLetter, bool fixedRow, bool fixedColumn)
: this(worksheet, rowNumber, XLHelper.GetColumnNumberFromLetter(columnLetter), fixedRow, fixedColumn)
{
_columnLetter = columnLetter;
}
public XLPictures(XLWorksheet worksheet)
{
_worksheet = worksheet;
Deleted = new HashSet <String>();
}
public XLHFItem(XLWorksheet worksheet)
{
_worksheet = worksheet;
}
public XLHFItem(XLHFItem defaultHFItem, XLWorksheet worksheet)
: this(worksheet)
{
defaultHFItem.texts.ForEach(kp => texts.Add(kp.Key, kp.Value));
}