public XLWorkbook(XLEventTracking eventTracking)
{
EventTracking = eventTracking;
DefaultRowHeight = 15;
DefaultColumnWidth = 8.43;
Style = new XLStyle(null, DefaultStyle);
RowHeight = DefaultRowHeight;
ColumnWidth = DefaultColumnWidth;
PageOptions = DefaultPageOptions;
Outline = DefaultOutline;
Properties = new XLWorkbookProperties();
CalculateMode = XLCalculateMode.Default;
ReferenceStyle = XLReferenceStyle.Default;
InitializeTheme();
ShowFormulas = DefaultShowFormulas;
ShowGridLines = DefaultShowGridLines;
ShowOutlineSymbols = DefaultShowOutlineSymbols;
ShowRowColHeaders = DefaultShowRowColHeaders;
ShowRuler = DefaultShowRuler;
ShowWhiteSpace = DefaultShowWhiteSpace;
ShowZeros = DefaultShowZeros;
RightToLeft = DefaultRightToLeft;
WorksheetsInternal = new XLWorksheets(this);
NamedRanges = new XLNamedRanges(this);
CustomProperties = new XLCustomProperties(this);
ShapeIdManager = new XLIdManager();
Author = Environment.UserName;
}
public XLWorkbook(XLEventTracking eventTracking)
{
EventTracking = eventTracking;
DefaultRowHeight = 15;
DefaultColumnWidth = 8.43;
Style = new XLStyle(null, DefaultStyle);
RowHeight = DefaultRowHeight;
ColumnWidth = DefaultColumnWidth;
PageOptions = DefaultPageOptions;
Outline = DefaultOutline;
Properties = new XLWorkbookProperties();
CalculateMode = XLCalculateMode.Default;
ReferenceStyle = XLReferenceStyle.Default;
InitializeTheme();
ShowFormulas = DefaultShowFormulas;
ShowGridLines = DefaultShowGridLines;
ShowOutlineSymbols = DefaultShowOutlineSymbols;
ShowRowColHeaders = DefaultShowRowColHeaders;
ShowRuler = DefaultShowRuler;
ShowWhiteSpace = DefaultShowWhiteSpace;
ShowZeros = DefaultShowZeros;
RightToLeft = DefaultRightToLeft;
WorksheetsInternal = new XLWorksheets(this);
NamedRanges = new XLNamedRanges(this);
CustomProperties = new XLCustomProperties(this);
ShapeIdManager = new XLIdManager();
Author = Environment.UserName;
}
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;
}
private void SetStyle(IXLStyle style, bool propagate = false)
{
if (style is XLStyle xlStyle)
{
SetStyle(xlStyle.Value, propagate);
}
else
{
SetStyle(XLStyleValue.FromKey(XLStyle.GenerateKey(style)), propagate);
}
}
internal Int32 GetStyleId(IXLStyle style)
{
Int32 cached;
if (_stylesByStyle.TryGetValue(style, out cached))
{
return(cached);
}
var count = _stylesByStyle.Count;
var styleToUse = new XLStyle(null, style);
_stylesByStyle.Add(styleToUse, count);
_stylesById.Add(count, styleToUse);
return(count);
}
public XLConditionalFormat(XLConditionalFormat other)
{
Range = other.Range;
Style = new XLStyle(this, other.Style);
Values = new XLDictionary <XLFormula>(other.Values);
Colors = new XLDictionary <XLColor>(other.Colors);
ContentTypes = new XLDictionary <XLCFContentType>(other.ContentTypes);
IconSetOperators = new XLDictionary <XLCFIconSetOperator>(other.IconSetOperators);
ConditionalFormatType = other.ConditionalFormatType;
TimePeriod = other.TimePeriod;
IconSetStyle = other.IconSetStyle;
Operator = other.Operator;
Bottom = other.Bottom;
Percent = other.Percent;
ReverseIconOrder = other.ReverseIconOrder;
ShowIconOnly = other.ShowIconOnly;
ShowBarOnly = other.ShowBarOnly;
}
public XLConditionalFormat(XLConditionalFormat conditionalFormat)
{
Id = Guid.NewGuid();
Range = conditionalFormat.Range;
Style = new XLStyle(this, conditionalFormat.Style);
Values = new XLDictionary <XLFormula>(conditionalFormat.Values);
Colors = new XLDictionary <XLColor>(conditionalFormat.Colors);
ContentTypes = new XLDictionary <XLCFContentType>(conditionalFormat.ContentTypes);
IconSetOperators = new XLDictionary <XLCFIconSetOperator>(conditionalFormat.IconSetOperators);
ConditionalFormatType = conditionalFormat.ConditionalFormatType;
TimePeriod = conditionalFormat.TimePeriod;
IconSetStyle = conditionalFormat.IconSetStyle;
Operator = conditionalFormat.Operator;
Bottom = conditionalFormat.Bottom;
Percent = conditionalFormat.Percent;
ReverseIconOrder = conditionalFormat.ReverseIconOrder;
ShowIconOnly = conditionalFormat.ShowIconOnly;
ShowBarOnly = conditionalFormat.ShowBarOnly;
StopIfTrueInternal = OpenXmlHelper.GetBooleanValueAsBool(conditionalFormat.StopIfTrueInternal, true);
}
public XLNumberFormat(XLStyle style = null, IXLNumberFormat d = null) : this(style, GenerateKey(d))
{
}
private void FillDifferentialFormatsCollection(DifferentialFormats differentialFormats,
Dictionary<IXLStyle, int> dictionary)
{
dictionary.Clear();
var id = 0;
foreach (var df in differentialFormats.Elements<DifferentialFormat>())
{
var style = new XLStyle(new XLStylizedEmpty(DefaultStyle), DefaultStyle);
LoadFont(df.Font, style.Font);
LoadBorder(df.Border, style.Border);
LoadNumberFormat(df.NumberingFormat, style.NumberFormat);
LoadFill(df.Fill, style.Fill);
if (!dictionary.ContainsKey(style))
dictionary.Add(style, ++id);
}
}
public IXLColumn AdjustToContents(Int32 startRow, Int32 endRow, Double minWidth, Double maxWidth)
{
var fontCache = new Dictionary <IXLFontBase, Font>();
Double colMaxWidth = minWidth;
List <Int32> autoFilterRows = new List <Int32>();
if (this.Worksheet.AutoFilter != null && this.Worksheet.AutoFilter.Range != null)
{
autoFilterRows.Add(this.Worksheet.AutoFilter.Range.FirstRow().RowNumber());
}
autoFilterRows.AddRange(Worksheet.Tables.Where(t =>
t.AutoFilter != null &&
t.AutoFilter.Range != null &&
!autoFilterRows.Contains(t.AutoFilter.Range.FirstRow().RowNumber()))
.Select(t => t.AutoFilter.Range.FirstRow().RowNumber()));
XLStyle cellStyle = null;
foreach (var c in Column(startRow, endRow).CellsUsed().Cast <XLCell>())
{
if (c.IsMerged())
{
continue;
}
if (cellStyle == null || cellStyle.Value != c.StyleValue)
{
cellStyle = c.Style as XLStyle;
}
Double thisWidthMax = 0;
Int32 textRotation = cellStyle.Alignment.TextRotation;
if (c.HasRichText || textRotation != 0 || c.InnerText.Contains(Environment.NewLine))
{
var kpList = new List <KeyValuePair <IXLFontBase, string> >();
#region if (c.HasRichText)
if (c.HasRichText)
{
foreach (IXLRichString rt in c.RichText)
{
String formattedString = rt.Text;
var arr = formattedString.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
Int32 arrCount = arr.Count();
for (Int32 i = 0; i < arrCount; i++)
{
String s = arr[i];
if (i < arrCount - 1)
{
s += Environment.NewLine;
}
kpList.Add(new KeyValuePair <IXLFontBase, String>(rt, s));
}
}
}
else
{
String formattedString = c.GetFormattedString();
var arr = formattedString.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
Int32 arrCount = arr.Count();
for (Int32 i = 0; i < arrCount; i++)
{
String s = arr[i];
if (i < arrCount - 1)
{
s += Environment.NewLine;
}
kpList.Add(new KeyValuePair <IXLFontBase, String>(cellStyle.Font, s));
}
}
#endregion if (c.HasRichText)
#region foreach (var kp in kpList)
Double runningWidth = 0;
Boolean rotated = false;
Double maxLineWidth = 0;
Int32 lineCount = 1;
foreach (KeyValuePair <IXLFontBase, string> kp in kpList)
{
var f = kp.Key;
String formattedString = kp.Value;
Int32 newLinePosition = formattedString.IndexOf(Environment.NewLine);
if (textRotation == 0)
{
#region if (newLinePosition >= 0)
if (newLinePosition >= 0)
{
if (newLinePosition > 0)
{
runningWidth += f.GetWidth(formattedString.Substring(0, newLinePosition), fontCache);
}
if (runningWidth > thisWidthMax)
{
thisWidthMax = runningWidth;
}
runningWidth = newLinePosition < formattedString.Length - 2
? f.GetWidth(formattedString.Substring(newLinePosition + 2), fontCache)
: 0;
}
else
{
runningWidth += f.GetWidth(formattedString, fontCache);
}
#endregion if (newLinePosition >= 0)
}
else
{
#region if (textRotation == 255)
if (textRotation == 255)
{
if (runningWidth <= 0)
{
runningWidth = f.GetWidth("X", fontCache);
}
if (newLinePosition >= 0)
{
runningWidth += f.GetWidth("X", fontCache);
}
}
else
{
rotated = true;
Double vWidth = f.GetWidth("X", fontCache);
if (vWidth > maxLineWidth)
{
maxLineWidth = vWidth;
}
if (newLinePosition >= 0)
{
lineCount++;
if (newLinePosition > 0)
{
runningWidth += f.GetWidth(formattedString.Substring(0, newLinePosition), fontCache);
}
if (runningWidth > thisWidthMax)
{
thisWidthMax = runningWidth;
}
runningWidth = newLinePosition < formattedString.Length - 2
? f.GetWidth(formattedString.Substring(newLinePosition + 2), fontCache)
: 0;
}
else
{
runningWidth += f.GetWidth(formattedString, fontCache);
}
}
#endregion if (textRotation == 255)
}
}
#endregion foreach (var kp in kpList)
if (runningWidth > thisWidthMax)
{
thisWidthMax = runningWidth;
}
#region if (rotated)
if (rotated)
{
Int32 rotation;
if (textRotation == 90 || textRotation == 180 || textRotation == 255)
{
rotation = 90;
}
else
{
rotation = textRotation % 90;
}
Double r = DegreeToRadian(rotation);
thisWidthMax = (thisWidthMax * Math.Cos(r)) + (maxLineWidth * lineCount);
}
#endregion if (rotated)
}
else
{
thisWidthMax = cellStyle.Font.GetWidth(c.GetFormattedString(), fontCache);
}
if (autoFilterRows.Contains(c.Address.RowNumber))
{
thisWidthMax += 2.7148; // Allow room for arrow icon in autofilter
}
if (thisWidthMax >= maxWidth)
{
colMaxWidth = maxWidth;
break;
}
if (thisWidthMax > colMaxWidth)
{
colMaxWidth = thisWidthMax + 1;
}
}
if (colMaxWidth <= 0)
{
colMaxWidth = Worksheet.ColumnWidth;
}
Width = colMaxWidth;
foreach (IDisposable font in fontCache.Values)
{
font.Dispose();
}
return(this);
}
public XLAlignment(XLStyle style, XLAlignmentKey key) : this(style, XLAlignmentValue.FromKey(ref key))
{
}
public XLBorder(IXLStylized container, XLStyle style = null, IXLBorder d = null) : this(container, style, GenerateKey(d))
{
}
public XLStylizedEmpty(IXLStyle defaultStyle)
{
Style = new XLStyle(this, defaultStyle);
}
public XLFill(XLStyle style = null, IXLFill d = null) : this(style, GenerateKey(d))
{
}
public XLFill(XLStyle style, XLFillKey key) : this(style, XLFillValue.FromKey(key))
{
}
/// <summary>
/// Create an instance of XLFill initializing it with the specified value.
/// </summary>
/// <param name="style">Style to attach the new instance to.</param>
/// <param name="value">Style value to use.</param>
public XLFill(XLStyle style, XLFillValue value)
{
_style = style ?? XLStyle.CreateEmptyStyle();
_value = value;
}
/// <summary>
/// Create an instance of XLBorder initializing it with the specified value.
/// </summary>
/// <param name="container">Container the border is applied to.</param>
/// <param name="style">Style to attach the new instance to.</param>
/// <param name="value">Style value to use.</param>
public XLBorder(IXLStylized container, XLStyle style, XLBorderValue value)
{
_container = container;
_style = style ?? _container.Style as XLStyle ?? XLStyle.CreateEmptyStyle();
_value = value;
}
public XLBorder(IXLStylized container, XLStyle style, XLBorderKey key) : this(container, style, XLBorderValue.FromKey(key))
{
}
public XLFont(XLStyle style, XLFontKey key) : this(style, XLFontValue.FromKey(key))
{
}
/// <summary>
/// Create an instance of XLAlignment initializing it with the specified value.
/// </summary>
/// <param name="style">Style to attach the new instance to.</param>
/// <param name="value">Style value to use.</param>
public XLAlignment(XLStyle style, XLAlignmentValue value)
{
_style = style ?? XLStyle.CreateEmptyStyle();
_value = value;
}
/// <summary>
/// Create an instance of XLProtection initializing it with the specified value.
/// </summary>
/// <param name="style">Style to attach the new instance to.</param>
/// <param name="value">Style value to use.</param>
public XLProtection(XLStyle style, XLProtectionValue value)
{
_style = style ?? XLStyle.CreateEmptyStyle();
_value = value;
}
public XLAlignment(XLStyle style = null, IXLAlignment d = null) : this(style, GenerateKey(d))
{
}
public XLProtection(XLStyle style, XLProtectionKey key) : this(style, XLProtectionValue.FromKey(key))
{
}
private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart, SaveContext context)
{
var defaultStyle = new XLStyle(null, DefaultStyle);
var defaultStyleId = GetStyleId(defaultStyle);
if (!context.SharedFonts.ContainsKey(defaultStyle.Font))
context.SharedFonts.Add(defaultStyle.Font, new FontInfo {FontId = 0, Font = defaultStyle.Font as XLFont});
var sharedFills = new Dictionary<IXLFill, FillInfo>
{{defaultStyle.Fill, new FillInfo {FillId = 2, Fill = defaultStyle.Fill as XLFill}}};
var sharedBorders = new Dictionary<IXLBorder, BorderInfo>
{{defaultStyle.Border, new BorderInfo {BorderId = 0, Border = defaultStyle.Border as XLBorder}}};
var sharedNumberFormats = new Dictionary<IXLNumberFormat, NumberFormatInfo>
{
{
defaultStyle.NumberFormat,
new NumberFormatInfo
{NumberFormatId = 0, NumberFormat = defaultStyle.NumberFormat}
}
};
//Dictionary<String, AlignmentInfo> sharedAlignments = new Dictionary<String, AlignmentInfo>();
//sharedAlignments.Add(defaultStyle.Alignment.ToString(), new AlignmentInfo() { AlignmentId = 0, Alignment = defaultStyle.Alignment });
if (workbookStylesPart.Stylesheet == null)
workbookStylesPart.Stylesheet = new Stylesheet();
// Cell styles = Named styles
if (workbookStylesPart.Stylesheet.CellStyles == null)
workbookStylesPart.Stylesheet.CellStyles = new CellStyles();
UInt32 defaultFormatId;
if (workbookStylesPart.Stylesheet.CellStyles.Elements<CellStyle>().Any(c => c.Name == "Normal"))
{
defaultFormatId =
workbookStylesPart.Stylesheet.CellStyles.Elements<CellStyle>().Single(c => c.Name == "Normal").FormatId.Value;
}
else if (workbookStylesPart.Stylesheet.CellStyles.Elements<CellStyle>().Any())
{
defaultFormatId =
workbookStylesPart.Stylesheet.CellStyles.Elements<CellStyle>().Max(c => c.FormatId.Value) + 1;
}
else
defaultFormatId = 0;
context.SharedStyles.Add(defaultStyleId,
new StyleInfo
{
StyleId = defaultFormatId,
Style = defaultStyle,
FontId = 0,
FillId = 0,
BorderId = 0,
NumberFormatId = 0
//AlignmentId = 0
});
UInt32 styleCount = 1;
UInt32 fontCount = 1;
UInt32 fillCount = 3;
UInt32 borderCount = 1;
var numberFormatCount = 1;
var xlStyles = new HashSet<Int32>();
foreach (var worksheet in WorksheetsInternal)
{
foreach (var s in worksheet.GetStyleIds().Where(s => !xlStyles.Contains(s)))
xlStyles.Add(s);
foreach (
var s in
worksheet.Internals.ColumnsCollection.Select(kp => kp.Value.GetStyleId()).Where(
s => !xlStyles.Contains(s)))
xlStyles.Add(s);
foreach (
var s in
worksheet.Internals.RowsCollection.Select(kp => kp.Value.GetStyleId()).Where(
s => !xlStyles.Contains(s))
)
xlStyles.Add(s);
}
foreach (var xlStyle in xlStyles.Select(GetStyleById))
{
if (!context.SharedFonts.ContainsKey(xlStyle.Font))
context.SharedFonts.Add(xlStyle.Font,
new FontInfo {FontId = fontCount++, Font = xlStyle.Font as XLFont});
if (!sharedFills.ContainsKey(xlStyle.Fill))
sharedFills.Add(xlStyle.Fill, new FillInfo {FillId = fillCount++, Fill = xlStyle.Fill as XLFill});
if (!sharedBorders.ContainsKey(xlStyle.Border))
sharedBorders.Add(xlStyle.Border,
new BorderInfo {BorderId = borderCount++, Border = xlStyle.Border as XLBorder});
if (xlStyle.NumberFormat.NumberFormatId != -1
|| sharedNumberFormats.ContainsKey(xlStyle.NumberFormat))
continue;
sharedNumberFormats.Add(xlStyle.NumberFormat,
new NumberFormatInfo
{
NumberFormatId = numberFormatCount + 164,
NumberFormat = xlStyle.NumberFormat
});
numberFormatCount++;
}
var allSharedNumberFormats = ResolveNumberFormats(workbookStylesPart, sharedNumberFormats);
ResolveFonts(workbookStylesPart, context);
var allSharedFills = ResolveFills(workbookStylesPart, sharedFills);
var allSharedBorders = ResolveBorders(workbookStylesPart, sharedBorders);
foreach (var id in xlStyles)
{
var xlStyle = GetStyleById(id);
if (context.SharedStyles.ContainsKey(id)) continue;
var numberFormatId = xlStyle.NumberFormat.NumberFormatId >= 0
? xlStyle.NumberFormat.NumberFormatId
: allSharedNumberFormats[xlStyle.NumberFormat].NumberFormatId;
context.SharedStyles.Add(id,
new StyleInfo
{
StyleId = styleCount++,
Style = xlStyle,
FontId = context.SharedFonts[xlStyle.Font].FontId,
FillId = allSharedFills[xlStyle.Fill].FillId,
BorderId = allSharedBorders[xlStyle.Border].BorderId,
NumberFormatId = numberFormatId
});
}
ResolveCellStyleFormats(workbookStylesPart, context);
ResolveRest(workbookStylesPart, context);
if (workbookStylesPart.Stylesheet.CellStyles.Elements<CellStyle>().All(c => c.Name != "Normal"))
{
//var defaultFormatId = context.SharedStyles.Values.Where(s => s.Style.Equals(DefaultStyle)).Single().StyleId;
var cellStyle1 = new CellStyle {Name = "Normal", FormatId = defaultFormatId, BuiltinId = 0U};
workbookStylesPart.Stylesheet.CellStyles.AppendChild(cellStyle1);
}
workbookStylesPart.Stylesheet.CellStyles.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyles.Count();
var newSharedStyles = new Dictionary<Int32, StyleInfo>();
foreach (var ss in context.SharedStyles)
{
var styleId = -1;
foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats)
{
styleId++;
if (CellFormatsAreEqual(f, ss.Value))
break;
}
if (styleId == -1)
styleId = 0;
var si = ss.Value;
si.StyleId = (UInt32)styleId;
newSharedStyles.Add(ss.Key, si);
}
context.SharedStyles.Clear();
newSharedStyles.ForEach(kp => context.SharedStyles.Add(kp.Key, kp.Value));
AddDifferentialFormats(workbookStylesPart, context);
}
public XLProtection(XLStyle style = null, IXLProtection d = null) : this(style, GenerateKey(d))
{
}
public XLStylizedEmpty(IXLStyle defaultStyle)
{
Style = new XLStyle(this, defaultStyle);
}
internal Int32 GetStyleId(IXLStyle style)
{
Int32 cached;
if (_stylesByStyle.TryGetValue(style, out cached))
return cached;
var count = _stylesByStyle.Count;
var styleToUse = new XLStyle(null, style);
_stylesByStyle.Add(styleToUse, count);
_stylesById.Add(count, styleToUse);
return count;
}
/// <summary>
/// Create an instance of XLNumberFormat initializing it with the specified value.
/// </summary>
/// <param name="style">Style to attach the new instance to.</param>
/// <param name="value">Style value to use.</param>
public XLNumberFormat(XLStyle style, XLNumberFormatValue value)
{
_style = style ?? XLStyle.CreateEmptyStyle();
_value = value;
}
public XLNumberFormat(XLStyle style, XLNumberFormatKey key) : this(style, XLNumberFormatValue.FromKey(key))
{
}