public XLTable(XLRange range, String name, Boolean addToTables, Boolean setAutofilter = true)
: base(new XLRangeParameters(range.RangeAddress, range.Style))
{
InitializeValues(setAutofilter);
Name = name;
AddToTables(range, addToTables);
}
public XLConditionalFormat(XLRange range, Boolean copyDefaultModify = false)
{
Range = range;
Style = new XLStyle(this, range.Worksheet.Style);
Values = new XLDictionary <XLFormula>();
Colors = new XLDictionary <XLColor>();
ContentTypes = new XLDictionary <XLCFContentType>();
IconSetOperators = new XLDictionary <XLCFIconSetOperator>();
CopyDefaultModify = copyDefaultModify;
}
public XLDataValidation(IXLRanges ranges)
{
Ranges = new XLRanges();
ranges.ForEach(r=>
{
var newR =
new XLRange(new XLRangeParameters(r.RangeAddress as XLRangeAddress,
r.Worksheet.Style) {IgnoreEvents = true});
(Ranges as XLRanges).Add(newR);
} );
Initialize();
}
public XLDataValidation(IXLRanges ranges)
{
Ranges = new XLRanges();
ranges.ForEach(r=>
{
var newR =
new XLRange(new XLRangeParameters(r.RangeAddress as XLRangeAddress,
r.Worksheet.Style) {IgnoreEvents = true});
(Ranges as XLRanges).Add(newR);
} );
Initialize();
}
public XLTable(XLRange range, Boolean addToTables, Boolean setAutofilter = true)
: base(new XLRangeParameters(range.RangeAddress, range.Style ))
{
InitializeValues(setAutofilter);
Int32 id = 1;
while (true)
{
string tableName = String.Format("Table{0}", id);
if (!Worksheet.Tables.Any(t => t.Name == tableName))
{
Name = tableName;
AddToTables(range, addToTables);
break;
}
id++;
}
}
public XLTable(XLRange range, Boolean addToTables, Boolean setAutofilter = true)
: base(new XLRangeParameters(range.RangeAddress, range.Style))
{
InitializeValues(setAutofilter);
Int32 id = 1;
while (true)
{
string tableName = String.Concat("Table", id);
if (Worksheet.Tables.All(t => t.Name != tableName))
{
Name = tableName;
AddToTables(range, addToTables);
break;
}
id++;
}
}
private void AddToTables(XLRange range, Boolean addToTables)
{
if (!addToTables)
{
return;
}
_uniqueNames = new HashSet <string>();
Int32 co = 1;
foreach (IXLCell c in range.Row(1).Cells())
{
if (XLHelper.IsNullOrWhiteSpace(((XLCell)c).InnerText))
{
c.Value = GetUniqueName("Column", co, true);
}
_uniqueNames.Add(c.GetString());
co++;
}
Worksheet.Tables.Add(this);
}
public void Add(XLRange range)
{
if (GetRangeIndex(range.Worksheet).Add(range))
{
Count++;
}
if (_styleInitialized)
{
return;
}
var worksheetStyle = range?.Worksheet?.Style;
if (worksheetStyle == null)
{
return;
}
InnerStyle = worksheetStyle;
_styleInitialized = true;
}
public void NotifyRangeShiftedColumns(XLRange range, Int32 columnsShifted)
{
if (RangeShiftedColumns != null)
{
foreach(var item in RangeShiftedColumns)
{
item.Action(range, columnsShifted);
}
}
}
public void Add(XLRange range)
{
Count++;
_ranges.Add(range);
}
internal override void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted)
{
RangeAddress = (XLRangeAddress)ShiftColumns(RangeAddress, range, columnsShifted);
}
internal override void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted)
{
return; // Columns are shifted by XLColumnCollection
}
internal CloseXMLExcelCell(XLRange obj)
{
_obj = obj;
}
internal override void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted)
{
//do nothing
}
private void ShiftConditionalFormattingRows(XLRange range, int rowsShifted)
{
Int32 firstRow = range.RangeAddress.FirstAddress.RowNumber;
if (firstRow == 1) return;
SuspendEvents();
var rangeUsed = range.Worksheet.RangeUsed(true);
IXLRangeAddress usedAddress;
if (rangeUsed == null)
usedAddress = range.RangeAddress;
else
usedAddress = rangeUsed.RangeAddress;
ResumeEvents();
if (firstRow < usedAddress.FirstAddress.RowNumber) firstRow = usedAddress.FirstAddress.RowNumber;
Int32 lastRow = range.RangeAddress.FirstAddress.RowNumber + rowsShifted - 1;
if (lastRow > usedAddress.LastAddress.RowNumber) lastRow = usedAddress.LastAddress.RowNumber;
Int32 firstColumn = range.RangeAddress.FirstAddress.ColumnNumber;
if (firstColumn < usedAddress.FirstAddress.ColumnNumber) firstColumn = usedAddress.FirstAddress.ColumnNumber;
Int32 lastColumn = range.RangeAddress.LastAddress.ColumnNumber;
if (lastColumn > usedAddress.LastAddress.ColumnNumber) lastColumn = usedAddress.LastAddress.ColumnNumber;
var insertedRange = Range(firstRow, firstColumn, lastRow, lastColumn);
var fr = insertedRange.FirstRow();
var model = fr.RowAbove();
Int32 modelFirstColumn = model.RangeAddress.FirstAddress.ColumnNumber;
if (ConditionalFormats.Any(cf=>cf.Range.Intersects(model)))
{
for (Int32 co = firstColumn; co <= lastColumn; co++)
{
var cellModel = model.Cell(co - modelFirstColumn + 1);
foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel.AsRange())).ToList())
{
Range(firstRow, co, lastRow, co).AddConditionalFormat(cf);
}
}
}
insertedRange.Dispose();
model.Dispose();
fr.Dispose();
}
private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted)
{
var newMerge = new XLRanges();
foreach (IXLRange rngMerged in Internals.MergedRanges)
{
if (range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber
&& rngMerged.RangeAddress.FirstAddress.ColumnNumber >= range.RangeAddress.FirstAddress.ColumnNumber
&& rngMerged.RangeAddress.LastAddress.ColumnNumber <= range.RangeAddress.LastAddress.ColumnNumber)
{
var newRng = Range(
rngMerged.RangeAddress.FirstAddress.RowNumber + rowsShifted,
rngMerged.RangeAddress.FirstAddress.ColumnNumber,
rngMerged.RangeAddress.LastAddress.RowNumber + rowsShifted,
rngMerged.RangeAddress.LastAddress.ColumnNumber);
newMerge.Add(newRng);
}
else if (!(range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber
&& range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.LastAddress.ColumnNumber))
newMerge.Add(rngMerged);
}
Internals.MergedRanges = newMerge;
Workbook.Worksheets.ForEach(ws => MoveNamedRangesRows(range, rowsShifted, ws.NamedRanges));
MoveNamedRangesRows(range, rowsShifted, Workbook.NamedRanges);
ShiftConditionalFormattingRows(range, rowsShifted);
}
private void ShiftConditionalFormattingColumns(XLRange range, int columnsShifted)
{
Int32 firstColumn = range.RangeAddress.FirstAddress.ColumnNumber;
if (firstColumn == 1) return;
Int32 lastColumn = range.RangeAddress.FirstAddress.ColumnNumber + columnsShifted - 1;
Int32 firstRow = range.RangeAddress.FirstAddress.RowNumber;
Int32 lastRow = range.RangeAddress.LastAddress.RowNumber;
var insertedRange = Range(firstRow, firstColumn, lastRow, lastColumn);
var fc = insertedRange.FirstColumn();
var model = fc.ColumnLeft();
Int32 modelFirstRow = model.RangeAddress.FirstAddress.RowNumber;
if (ConditionalFormats.Any(cf => cf.Range.Intersects(model)))
{
for (Int32 ro = firstRow; ro <= lastRow; ro++)
{
var cellModel = model.Cell(ro - modelFirstRow + 1);
foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel.AsRange())).ToList())
{
Range(ro, firstColumn, ro, lastColumn).AddConditionalFormat(cf);
}
}
}
insertedRange.Dispose();
model.Dispose();
fc.Dispose();
}
public XLTableRange(XLRange range, XLTable table) : base(range.RangeParameters)
{
_table = table;
_range = range;
}
public XLTableRange(XLRange range, XLTable table)
: base(new XLRangeParameters(range.RangeAddress, range.Style))
{
_table = table;
_range = range;
}
private void MoveNamedRangesColumns(XLRange range, int columnsShifted, IXLNamedRanges namedRanges)
{
foreach (XLNamedRange nr in namedRanges)
{
var newRangeList =
nr.RangeList.Select(r => XLCell.ShiftFormulaColumns(r, this, range, columnsShifted)).Where(
newReference => newReference.Length > 0).ToList();
nr.RangeList = newRangeList;
}
}
public XLTableRange(XLRange range, XLTable table)
: base(range.RangeParameters)
{
_table = table;
_range = range;
}
public void NotifyRangeShiftedRows(XLRange range, Int32 rowsShifted)
{
if (RangeShiftedRows != null)
RangeShiftedRows(range, rowsShifted);
}
internal override void WorksheetRangeShiftedRows(XLRange range, int rowsShifted)
{
return; // rows are shifted by XLRowCollection
}
public void NotifyRangeShiftedColumns(XLRange range, Int32 columnsShifted)
{
if (RangeShiftedColumns != null)
RangeShiftedColumns(range, columnsShifted);
}
internal CloseXMLExcelCell(XLRange obj)
{
_obj = obj;
}
private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted)
{
ShiftRows(RangeAddress, range, rowsShifted);
}
internal override void WorksheetRangeShiftedRows(XLRange range, int rowsShifted)
{
//do nothing
}
private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted)
{
ShiftRows(RangeAddress, range, rowsShifted);
}
internal override void WorksheetRangeShiftedRows(XLRange range, int rowsShifted)
{
RangeAddress = (XLRangeAddress)ShiftRows(RangeAddress, range, rowsShifted);
}
public override void FormatRange(ExternalExcelRangeFormatInfo formatInfo)
{
ModuleProc PROC = new ModuleProc(this.DYN_MODULE_NAME, "Method");
try
{
XLRange range = this.GetRange(formatInfo);
if (range == null)
{
return;
}
// colors
if (formatInfo.ForeColor != Color.Empty)
{
range.Style.Font.FontColor = XLColor.FromColor(formatInfo.ForeColor);
}
if (formatInfo.BackColor != Color.Empty)
{
range.Style.Fill.BackgroundColor = XLColor.FromColor(formatInfo.BackColor);
}
if (formatInfo.BorderColor != Color.Empty)
{
range.Style.Border.OutsideBorderColor = XLColor.FromColor(formatInfo.BorderColor);
}
// font
if (formatInfo.Font != null)
{
range.Style.Font.FontName = formatInfo.Font.Name;
range.Style.Font.Bold = formatInfo.Font.Bold;
range.Style.Font.FontSize = formatInfo.Font.Size;
}
// row height and column width
//if (formatInfo.AutoFitRows) range.Rows().AutoFit();
//if (formatInfo.AutoFitColumns) range.Columns.AutoFit();
if (formatInfo.RowHeight > 0)
{
range.Worksheet.RowHeight = formatInfo.RowHeight;
}
if (formatInfo.ColumnWidth > 0 && formatInfo.ColumnIndex > 0)
{
range.Column(formatInfo.ColumnIndex).WorksheetColumn().Width = formatInfo.ColumnWidth;
}
// text
range.Style.Alignment.WrapText = formatInfo.WrapText;
if (!formatInfo.Text.IsEmpty())
{
range.Value = formatInfo.Text;
}
// merge
if (formatInfo.Merge)
{
range.Merge();
}
// horizontal and vertical alignment
switch (formatInfo.HorizontalAlignment)
{
case ExternalExcelCellHAlign.AlignRight:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
case ExternalExcelCellHAlign.AlignLeft:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
break;
case ExternalExcelCellHAlign.AlignJustify:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Justify;
break;
case ExternalExcelCellHAlign.AlignDistributed:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Distributed;
break;
case ExternalExcelCellHAlign.AlignCenter:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
break;
case ExternalExcelCellHAlign.AlignGeneral:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.General;
break;
case ExternalExcelCellHAlign.AlignFill:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Fill;
break;
case ExternalExcelCellHAlign.AlignCenterAcrossSelection:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
break;
default:
break;
}
switch (formatInfo.VerticalAlignment)
{
case ExternalExcelCellVAlign.AlignTop:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
break;
case ExternalExcelCellVAlign.AlignJustify:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Justify;
break;
case ExternalExcelCellVAlign.AlignDistributed:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Distributed;
break;
case ExternalExcelCellVAlign.AlignCenter:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
break;
case ExternalExcelCellVAlign.AlignBottom:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Bottom;
break;
default:
break;
}
// auto filter
if (formatInfo.AutoFilter)
{
range.SetAutoFilter();
}
// custom format
if (!formatInfo.CustomFormat.IsEmpty())
{
range.Style.NumberFormat.Format = formatInfo.CustomFormat;
}
if (formatInfo.ValidationType != ExternalExcelValidationType.ValidationNone)
{
var validation = range.DataValidation;
validation.Clear();
object formula1 = Type.Missing;
object formula2 = Type.Missing;
if (formatInfo.ValidationFormat.Length > 0)
{
formula1 = formatInfo.ValidationFormat[0];
}
if (formatInfo.ValidationFormat.Length > 1)
{
formula2 = formatInfo.ValidationFormat[1];
}
switch (formatInfo.ValidationType)
{
case ExternalExcelValidationType.ValidateInputOnly:
break;
case ExternalExcelValidationType.ValidateWholeNumber:
break;
case ExternalExcelValidationType.ValidateDecimal:
{
validation.Decimal.Between(Convert.ToDouble(formula1), Convert.ToDouble(formula2));
validation.ErrorMessage = string.Format("Please enter the value between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateList:
//validations.List(formatInfo.ValidationFormat);
break;
case ExternalExcelValidationType.ValidateDate:
{
validation.Date.Between(Convert.ToDateTime(formula1), Convert.ToDateTime(formula2));
validation.ErrorMessage = string.Format("Please enter the date between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateTime:
break;
case ExternalExcelValidationType.ValidateTextLength:
{
validation.TextLength.EqualOrLessThan(Convert.ToInt32(formula1));
validation.ErrorMessage = string.Format("Please enter the value with max length of {0:D}", formula1.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateCustom:
break;
default:
break;
}
validation.ErrorTitle = formatInfo.ColumName;
}
}
catch (Exception ex)
{
Log.Exception(PROC, ex);
}
}
private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted)
{
ShiftColumns(RangeAddress, range, columnsShifted);
}
public void NotifyRangeShiftedRows(XLRange range, Int32 rowsShifted)
{
if (RangeShiftedRows != null)
{
foreach(var item in RangeShiftedRows)
{
item.Action(range, rowsShifted);
}
}
}
private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted)
{
ShiftColumns(RangeAddress, range, columnsShifted);
}
private void AddToTables(XLRange range, Boolean addToTables)
{
if (!addToTables) return;
_uniqueNames = new HashSet<string>();
Int32 co = 1;
foreach (IXLCell c in range.Row(1).Cells())
{
if (XLHelper.IsNullOrWhiteSpace(((XLCell)c).InnerText))
c.Value = GetUniqueName("Column" + co.ToInvariantString());
_uniqueNames.Add(c.GetString());
co++;
}
Worksheet.Tables.Add(this);
}
private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted)
{
if (range.RangeAddress.FirstAddress.RowNumber <= RowNumber())
SetRowNumber(RowNumber() + rowsShifted);
}
private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted)
{
if (range.RangeAddress.FirstAddress.ColumnNumber <= ColumnNumber())
SetColumnNumber(ColumnNumber() + columnsShifted);
}