Revert "[NWD] Further EPPlus fixes & improvements."
This reverts commit d0678ed0e8a7acc31363e9d08dc7d3899a8f2177.
Reason for revert: Workflow templates for xlsx files modify styling options in some cases.
Change-Id: Ief762b59af79be9071ff89753550b37c0da6bcbd
Reviewed-on: https://gnocchi-internal-review.git.corp.google.com/c/third_party/epplus/+/207762
Reviewed-by: Hughes Hilton <hugheshilton@google.com>
diff --git a/EPPlus/ConditionalFormatting/Contracts/IExcelConditionalFormattingDataBarGroup.cs b/EPPlus/ConditionalFormatting/Contracts/IExcelConditionalFormattingDataBarGroup.cs
index 91abb7d..99a44a1 100644
--- a/EPPlus/ConditionalFormatting/Contracts/IExcelConditionalFormattingDataBarGroup.cs
+++ b/EPPlus/ConditionalFormatting/Contracts/IExcelConditionalFormattingDataBarGroup.cs
@@ -29,6 +29,8 @@
* Eyal Seagull Conditional Formatting Adaption 2012-04-03
*******************************************************************************/
+using System.Drawing;
+
namespace OfficeOpenXml.ConditionalFormatting.Contracts;
/// <summary>
@@ -49,4 +51,9 @@
/// Databar High Value
/// </summary>
ExcelConditionalFormattingIconDataBarValue HighValue { get; }
+
+ /// <summary>
+ /// The color of the databar
+ /// </summary>
+ Color Color { get; set; }
}
diff --git a/EPPlus/ConditionalFormatting/Contracts/IExcelConditionalFormattingIconSetGroup.cs b/EPPlus/ConditionalFormatting/Contracts/IExcelConditionalFormattingIconSetGroup.cs
index 770d75b..700ffd6 100644
--- a/EPPlus/ConditionalFormatting/Contracts/IExcelConditionalFormattingIconSetGroup.cs
+++ b/EPPlus/ConditionalFormatting/Contracts/IExcelConditionalFormattingIconSetGroup.cs
@@ -44,4 +44,9 @@
/// ShowValue
/// </summary>
bool ShowValue { get; set; }
+
+ /// <summary>
+ /// IconSet (3, 4 ou 5 IconSet)
+ /// </summary>
+ T IconSet { get; set; }
}
diff --git a/EPPlus/ConditionalFormatting/Contracts/IRangeConditionalFormatting.cs b/EPPlus/ConditionalFormatting/Contracts/IRangeConditionalFormatting.cs
index bd84006..b2d99bf 100644
--- a/EPPlus/ConditionalFormatting/Contracts/IRangeConditionalFormatting.cs
+++ b/EPPlus/ConditionalFormatting/Contracts/IRangeConditionalFormatting.cs
@@ -29,6 +29,7 @@
* Eyal Seagull Conditional Formatting 2012-04-03
*******************************************************************************/
+using System.Drawing;
using OfficeOpenXml.ConditionalFormatting.Contracts;
namespace OfficeOpenXml.ConditionalFormatting;
@@ -283,4 +284,35 @@
/// </summary>
/// <returns></returns>
IExcelConditionalFormattingTwoColorScale AddTwoColorScale();
+
+ /// <summary>
+ /// Adds a <see cref="IExcelConditionalFormattingThreeIconSet<eExcelconditionalFormatting3IconsSetType>"/> to the range
+ /// </summary>
+ /// <param name="iconSet"></param>
+ /// <returns></returns>
+ IExcelConditionalFormattingThreeIconSet<eExcelconditionalFormatting3IconsSetType> AddThreeIconSet(
+ eExcelconditionalFormatting3IconsSetType iconSet);
+
+ /// <summary>
+ /// Adds a <see cref="IExcelConditionalFormattingFourIconSet<eExcelconditionalFormatting4IconsSetType>"/> to the range
+ /// </summary>
+ /// <param name="iconSet"></param>
+ /// <returns></returns>
+ IExcelConditionalFormattingFourIconSet<eExcelconditionalFormatting4IconsSetType> AddFourIconSet(
+ eExcelconditionalFormatting4IconsSetType iconSet);
+
+ /// <summary>
+ /// Adds a <see cref="IExcelConditionalFormattingFiveIconSet"/> to the range
+ /// </summary>
+ /// <param name="iconSet"></param>
+ /// <returns></returns>
+ IExcelConditionalFormattingFiveIconSet AddFiveIconSet(
+ eExcelconditionalFormatting5IconsSetType iconSet);
+
+ /// <summary>
+ /// Adds a <see cref="IExcelConditionalFormattingDataBarGroup"/> to the range
+ /// </summary>
+ /// <param name="color"></param>
+ /// <returns></returns>
+ IExcelConditionalFormattingDataBarGroup AddDatabar(Color color);
}
diff --git a/EPPlus/ConditionalFormatting/ExcelConditionalFormattingCollection.cs b/EPPlus/ConditionalFormatting/ExcelConditionalFormattingCollection.cs
index 08605aa..8d0ecf1 100644
--- a/EPPlus/ConditionalFormatting/ExcelConditionalFormattingCollection.cs
+++ b/EPPlus/ConditionalFormatting/ExcelConditionalFormattingCollection.cs
@@ -29,8 +29,10 @@
* Eyal Seagull Conditional Formatting 2012-04-03
*******************************************************************************/
+using System;
using System.Collections;
using System.Collections.Generic;
+using System.Drawing;
using System.Xml;
using OfficeOpenXml.ConditionalFormatting.Contracts;
using OfficeOpenXml.Utils;
@@ -775,4 +777,66 @@
eExcelConditionalFormattingRuleType.TwoColorScale,
address);
}
+
+ /// <summary>
+ /// Add ThreeIconSet Rule
+ /// </summary>
+ /// <param name="address">The address</param>
+ /// <param name="iconSet">Type of iconset</param>
+ /// <returns></returns>
+ public IExcelConditionalFormattingThreeIconSet<eExcelconditionalFormatting3IconsSetType> AddThreeIconSet(
+ ExcelAddress address,
+ eExcelconditionalFormatting3IconsSetType iconSet) {
+ var icon =
+ (IExcelConditionalFormattingThreeIconSet<eExcelconditionalFormatting3IconsSetType>)
+ AddRule(eExcelConditionalFormattingRuleType.ThreeIconSet, address);
+ icon.IconSet = iconSet;
+ return icon;
+ }
+
+ /// <summary>
+ /// Adds a FourIconSet rule
+ /// </summary>
+ /// <param name="address"></param>
+ /// <param name="iconSet"></param>
+ /// <returns></returns>
+ public IExcelConditionalFormattingFourIconSet<eExcelconditionalFormatting4IconsSetType> AddFourIconSet(
+ ExcelAddress address,
+ eExcelconditionalFormatting4IconsSetType iconSet) {
+ var icon =
+ (IExcelConditionalFormattingFourIconSet<eExcelconditionalFormatting4IconsSetType>)
+ AddRule(eExcelConditionalFormattingRuleType.FourIconSet, address);
+ icon.IconSet = iconSet;
+ return icon;
+ }
+
+ /// <summary>
+ /// Adds a FiveIconSet rule
+ /// </summary>
+ /// <param name="address"></param>
+ /// <param name="iconSet"></param>
+ /// <returns></returns>
+ public IExcelConditionalFormattingFiveIconSet AddFiveIconSet(
+ ExcelAddress address,
+ eExcelconditionalFormatting5IconsSetType iconSet) {
+ var icon = (IExcelConditionalFormattingFiveIconSet)AddRule(
+ eExcelConditionalFormattingRuleType.FiveIconSet,
+ address);
+ icon.IconSet = iconSet;
+ return icon;
+ }
+
+ /// <summary>
+ /// Adds a databar rule
+ /// </summary>
+ /// <param name="address"></param>
+ /// <param name="color"></param>
+ /// <returns></returns>
+ public IExcelConditionalFormattingDataBarGroup AddDatabar(ExcelAddress address, Color color) {
+ var dataBar = (IExcelConditionalFormattingDataBarGroup)AddRule(
+ eExcelConditionalFormattingRuleType.DataBar,
+ address);
+ dataBar.Color = color;
+ return dataBar;
+ }
}
diff --git a/EPPlus/ConditionalFormatting/ExcelConditionalFormattingColorScaleValue.cs b/EPPlus/ConditionalFormatting/ExcelConditionalFormattingColorScaleValue.cs
index 84d8fb1..a8585f0 100644
--- a/EPPlus/ConditionalFormatting/ExcelConditionalFormattingColorScaleValue.cs
+++ b/EPPlus/ConditionalFormatting/ExcelConditionalFormattingColorScaleValue.cs
@@ -30,6 +30,7 @@
*******************************************************************************/
using System;
+using System.Drawing;
using System.Xml;
using OfficeOpenXml.Utils;
@@ -62,6 +63,7 @@
internal ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition position,
eExcelConditionalFormattingValueObjectType type,
+ Color color,
double value,
string formula,
eExcelConditionalFormattingRuleType ruleType,
@@ -130,6 +132,7 @@
Position = position;
RuleType = ruleType;
Type = type;
+ Color = color;
Value = value;
Formula = formula;
}
@@ -150,6 +153,7 @@
internal ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition position,
eExcelConditionalFormattingValueObjectType type,
+ Color color,
double value,
string formula,
eExcelConditionalFormattingRuleType ruleType,
@@ -160,6 +164,7 @@
: this(
position,
type,
+ color,
value,
formula,
ruleType,
@@ -183,6 +188,7 @@
internal ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition position,
eExcelConditionalFormattingValueObjectType type,
+ Color color,
eExcelConditionalFormattingRuleType ruleType,
ExcelAddress address,
int priority,
@@ -191,6 +197,7 @@
: this(
position,
type,
+ color,
0,
null,
ruleType,
@@ -344,6 +351,32 @@
}
/// <summary>
+ ///
+ /// </summary>
+ public Color Color {
+ get {
+ // Color Code like "FF5B34F2"
+ var colorCode = GetXmlNodeString(
+ string.Format(
+ "{0}[position()={1}]/{2}",
+ // {0}
+ ExcelConditionalFormattingConstants.Paths._color,
+ // {1}
+ GetNodeOrder(),
+ // {2}
+ ExcelConditionalFormattingConstants.Paths._rgbAttribute));
+
+ return ExcelConditionalFormattingHelper.ConvertFromColorCode(colorCode);
+ }
+ set =>
+ // Use the color code to store (Ex. "FF5B35F2")
+ CreateNodeByOrdem(
+ eExcelConditionalFormattingValueObjectNodeType.Color,
+ ExcelConditionalFormattingConstants.Paths._rgbAttribute,
+ value.ToArgb().ToString("x"));
+ }
+
+ /// <summary>
/// Get/Set the 'cfvo' node @val attribute
/// </summary>
public Double Value {
diff --git a/EPPlus/ConditionalFormatting/ExcelConditionalFormattingHelper.cs b/EPPlus/ConditionalFormatting/ExcelConditionalFormattingHelper.cs
index ba97c3d..0ea9a45 100644
--- a/EPPlus/ConditionalFormatting/ExcelConditionalFormattingHelper.cs
+++ b/EPPlus/ConditionalFormatting/ExcelConditionalFormattingHelper.cs
@@ -30,6 +30,7 @@
*******************************************************************************/
using System;
+using System.Drawing;
using System.Globalization;
using System.Text.RegularExpressions;
using System.Xml;
@@ -62,6 +63,20 @@
}
/// <summary>
+ /// Convert a color code to Color Object
+ /// </summary>
+ /// <param name="colorCode">Color Code (Ex. "#FFB43C53" or "FFB43C53")</param>
+ /// <returns></returns>
+ public static Color ConvertFromColorCode(string colorCode) {
+ try {
+ return Color.FromArgb(Int32.Parse(colorCode.Replace("#", ""), NumberStyles.HexNumber));
+ } catch {
+ // Assume white is the default color (instead of giving an error)
+ return Color.White;
+ }
+ }
+
+ /// <summary>
///
/// </summary>
/// <param name="node"></param>
diff --git a/EPPlus/ConditionalFormatting/RangeConditionalFormatting.cs b/EPPlus/ConditionalFormatting/RangeConditionalFormatting.cs
index 6fcc305..debf061 100644
--- a/EPPlus/ConditionalFormatting/RangeConditionalFormatting.cs
+++ b/EPPlus/ConditionalFormatting/RangeConditionalFormatting.cs
@@ -29,6 +29,7 @@
* Eyal Seagull Conditional Formatting 2012-04-03
*******************************************************************************/
+using System.Drawing;
using OfficeOpenXml.ConditionalFormatting.Contracts;
using OfficeOpenXml.Utils;
@@ -377,4 +378,61 @@
eExcelConditionalFormattingRuleType.TwoColorScale,
_address));
}
+
+ /// <summary>
+ /// Adds a ThreeIconSet rule
+ /// </summary>
+ /// <param name="iconSet"></param>
+ /// <returns></returns>
+ public IExcelConditionalFormattingThreeIconSet<eExcelconditionalFormatting3IconsSetType> AddThreeIconSet(
+ eExcelconditionalFormatting3IconsSetType iconSet) {
+ var rule =
+ (IExcelConditionalFormattingThreeIconSet<eExcelconditionalFormatting3IconsSetType>)(_worksheet.ConditionalFormatting.AddRule(
+ eExcelConditionalFormattingRuleType.ThreeIconSet,
+ _address));
+ rule.IconSet = iconSet;
+ return rule;
+ }
+
+ /// <summary>
+ /// Adds a FourIconSet rule
+ /// </summary>
+ /// <param name="iconSet"></param>
+ /// <returns></returns>
+ public IExcelConditionalFormattingFourIconSet<eExcelconditionalFormatting4IconsSetType> AddFourIconSet(
+ eExcelconditionalFormatting4IconsSetType iconSet) {
+ var rule =
+ (IExcelConditionalFormattingFourIconSet<eExcelconditionalFormatting4IconsSetType>)(_worksheet.ConditionalFormatting.AddRule(
+ eExcelConditionalFormattingRuleType.FourIconSet,
+ _address));
+ rule.IconSet = iconSet;
+ return rule;
+ }
+
+ /// <summary>
+ /// Adds a FiveIconSet rule
+ /// </summary>
+ /// <param name="iconSet"></param>
+ /// <returns></returns>
+ public IExcelConditionalFormattingFiveIconSet AddFiveIconSet(
+ eExcelconditionalFormatting5IconsSetType iconSet) {
+ var rule = (IExcelConditionalFormattingFiveIconSet)(_worksheet.ConditionalFormatting.AddRule(
+ eExcelConditionalFormattingRuleType.FiveIconSet,
+ _address));
+ rule.IconSet = iconSet;
+ return rule;
+ }
+
+ /// <summary>
+ /// Adds a Databar rule
+ /// </summary>
+ /// <param name="color">The color of the databar</param>
+ /// <returns></returns>
+ public IExcelConditionalFormattingDataBarGroup AddDatabar(Color color) {
+ var rule = (IExcelConditionalFormattingDataBarGroup)(_worksheet.ConditionalFormatting.AddRule(
+ eExcelConditionalFormattingRuleType.DataBar,
+ _address));
+ rule.Color = color;
+ return rule;
+ }
}
diff --git a/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingDataBar.cs b/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingDataBar.cs
index 8ca4be2..2a80337 100644
--- a/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingDataBar.cs
+++ b/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingDataBar.cs
@@ -29,6 +29,7 @@
* Eyal Seagull Added 2012-04-03
*******************************************************************************/
+using System.Drawing;
using System.Globalization;
using System.Xml;
using OfficeOpenXml.ConditionalFormatting.Contracts;
@@ -152,4 +153,17 @@
public ExcelConditionalFormattingIconDataBarValue LowValue { get; internal set; }
public ExcelConditionalFormattingIconDataBarValue HighValue { get; internal set; }
+
+ private const string _colorPath = "d:dataBar/d:color/@rgb";
+
+ public Color Color {
+ get {
+ var rgb = GetXmlNodeString(_colorPath);
+ if (!string.IsNullOrEmpty(rgb)) {
+ return Color.FromArgb(int.Parse(rgb, NumberStyles.HexNumber));
+ }
+ return Color.White;
+ }
+ set => SetXmlNodeString(_colorPath, value.ToArgb().ToString("X"));
+ }
}
diff --git a/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingThreeColorScale.cs b/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingThreeColorScale.cs
index 5700e10..fa0574e 100644
--- a/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingThreeColorScale.cs
+++ b/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingThreeColorScale.cs
@@ -71,6 +71,7 @@
LowValue = new(
eExcelConditionalFormattingValueObjectPosition.Low,
eExcelConditionalFormattingValueObjectType.Min,
+ ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors._cfvoLowValue),
eExcelConditionalFormattingRuleType.ThreeColorScale,
address,
priority,
@@ -81,6 +82,7 @@
MiddleValue = new(
eExcelConditionalFormattingValueObjectPosition.Middle,
eExcelConditionalFormattingValueObjectType.Percent,
+ ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors._cfvoMiddleValue),
50,
string.Empty,
eExcelConditionalFormattingRuleType.ThreeColorScale,
@@ -93,6 +95,7 @@
HighValue = new(
eExcelConditionalFormattingValueObjectPosition.High,
eExcelConditionalFormattingValueObjectType.Max,
+ ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors._cfvoHighValue),
eExcelConditionalFormattingRuleType.ThreeColorScale,
address,
priority,
diff --git a/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingThreeIconSet.cs b/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingThreeIconSet.cs
index bcf19e1..2b8b27e 100644
--- a/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingThreeIconSet.cs
+++ b/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingThreeIconSet.cs
@@ -223,6 +223,18 @@
private const string _iconSetPath = "d:iconSet/@iconSet";
+ /// <summary>
+ /// Type of iconset
+ /// </summary>
+ public T IconSet {
+ get {
+ var v = GetXmlNodeString(_iconSetPath);
+ v = v.Substring(1); //Skip first icon.
+ return (T)Enum.Parse(typeof(T), v, true);
+ }
+ set => SetXmlNodeString(_iconSetPath, GetIconSetString(value));
+ }
+
private string GetIconSetString(T value) {
if (Type == eExcelConditionalFormattingRuleType.FourIconSet) {
switch (value.ToString()) {
diff --git a/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingTwoColorScale.cs b/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingTwoColorScale.cs
index ad7d79f..69ee8b1 100644
--- a/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingTwoColorScale.cs
+++ b/EPPlus/ConditionalFormatting/Rules/ExcelConditionalFormattingTwoColorScale.cs
@@ -81,6 +81,7 @@
LowValue = new(
eExcelConditionalFormattingValueObjectPosition.Low,
eExcelConditionalFormattingValueObjectType.Min,
+ ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors._cfvoLowValue),
eExcelConditionalFormattingRuleType.TwoColorScale,
address,
priority,
@@ -91,6 +92,7 @@
HighValue = new(
eExcelConditionalFormattingValueObjectPosition.High,
eExcelConditionalFormattingValueObjectType.Max,
+ ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors._cfvoHighValue),
eExcelConditionalFormattingRuleType.TwoColorScale,
address,
priority,
diff --git a/EPPlus/DataValidation/Contracts/IExcelDataValidation.cs b/EPPlus/DataValidation/Contracts/IExcelDataValidation.cs
index 7917008..44b5d3e 100644
--- a/EPPlus/DataValidation/Contracts/IExcelDataValidation.cs
+++ b/EPPlus/DataValidation/Contracts/IExcelDataValidation.cs
@@ -49,7 +49,7 @@
/// <summary>
/// Controls how Excel will handle invalid values.
/// </summary>
- ExcelDataValidationWarningStyle ErrorStyle { get; }
+ ExcelDataValidationWarningStyle ErrorStyle { get; set; }
/// <summary>
/// True if input message should be shown
diff --git a/EPPlus/DataValidation/Contracts/IExcelDataValidationWithOperator.cs b/EPPlus/DataValidation/Contracts/IExcelDataValidationWithOperator.cs
index 2604079..48b06cb 100644
--- a/EPPlus/DataValidation/Contracts/IExcelDataValidationWithOperator.cs
+++ b/EPPlus/DataValidation/Contracts/IExcelDataValidationWithOperator.cs
@@ -39,5 +39,5 @@
/// <summary>
/// Operator type
/// </summary>
- ExcelDataValidationOperator Operator { get; }
+ ExcelDataValidationOperator Operator { get; set; }
}
diff --git a/EPPlus/DataValidation/ExcelDataValidation.cs b/EPPlus/DataValidation/ExcelDataValidation.cs
index 88a60de..78b6259 100644
--- a/EPPlus/DataValidation/ExcelDataValidation.cs
+++ b/EPPlus/DataValidation/ExcelDataValidation.cs
@@ -199,9 +199,18 @@
public ExcelDataValidationOperator Operator {
get {
var operatorString = GetXmlNodeString(_operatorPath);
- return Enum.TryParse<ExcelDataValidationOperator>(operatorString, true, out var op)
- ? op
- : ExcelDataValidationOperator.Any;
+ if (!string.IsNullOrEmpty(operatorString)) {
+ return (ExcelDataValidationOperator)
+ Enum.Parse(typeof(ExcelDataValidationOperator), operatorString);
+ }
+ return default(ExcelDataValidationOperator);
+ }
+ set {
+ if (!ValidationType.AllowOperator) {
+ throw new InvalidOperationException(
+ "The current validation type does not allow operator to be set");
+ }
+ SetXmlNodeString(_operatorPath, value.ToString());
}
}
@@ -211,9 +220,17 @@
public ExcelDataValidationWarningStyle ErrorStyle {
get {
var errorStyleString = GetXmlNodeString(_errorStylePath);
- return Enum.TryParse<ExcelDataValidationWarningStyle>(errorStyleString, true, out var style)
- ? style
- : ExcelDataValidationWarningStyle.Undefined;
+ if (!string.IsNullOrEmpty(errorStyleString)) {
+ return (ExcelDataValidationWarningStyle)
+ Enum.Parse(typeof(ExcelDataValidationWarningStyle), errorStyleString);
+ }
+ return ExcelDataValidationWarningStyle.Undefined;
+ }
+ set {
+ if (value == ExcelDataValidationWarningStyle.Undefined) {
+ DeleteNode(_errorStylePath);
+ }
+ SetXmlNodeString(_errorStylePath, value.ToString());
}
}
diff --git a/EPPlus/DataValidation/ExcelDataValidationType.cs b/EPPlus/DataValidation/ExcelDataValidationType.cs
index cdcc99c..1c7465c 100644
--- a/EPPlus/DataValidation/ExcelDataValidationType.cs
+++ b/EPPlus/DataValidation/ExcelDataValidationType.cs
@@ -116,6 +116,34 @@
/// </summary>
internal bool AllowOperator { get; private set; }
+ /// <summary>
+ /// Returns a validation type by <see cref="eDataValidationType"/>
+ /// </summary>
+ /// <param name="type"></param>
+ /// <returns></returns>
+ internal static ExcelDataValidationType GetByValidationType(eDataValidationType type) {
+ switch (type) {
+ case eDataValidationType.Any:
+ return Any;
+ case eDataValidationType.Whole:
+ return Whole;
+ case eDataValidationType.List:
+ return List;
+ case eDataValidationType.Decimal:
+ return Decimal;
+ case eDataValidationType.TextLength:
+ return TextLength;
+ case eDataValidationType.DateTime:
+ return DateTime;
+ case eDataValidationType.Time:
+ return Time;
+ case eDataValidationType.Custom:
+ return Custom;
+ default:
+ throw new InvalidOperationException("Non supported Validationtype : " + type);
+ }
+ }
+
internal static ExcelDataValidationType GetBySchemaName(string schemaName) {
switch (schemaName) {
case DataValidationSchemaNames.Any:
diff --git a/EPPlus/Drawing/Vml/ExcelVmlDrawingComment.cs b/EPPlus/Drawing/Vml/ExcelVmlDrawingComment.cs
index bd137cd..3392fc7 100644
--- a/EPPlus/Drawing/Vml/ExcelVmlDrawingComment.cs
+++ b/EPPlus/Drawing/Vml/ExcelVmlDrawingComment.cs
@@ -30,6 +30,9 @@
* Jan Källman License changed GPL-->LGPL 2011-12-16
*******************************************************************************/
+using System;
+using System.Drawing;
+using System.Globalization;
using System.Xml;
namespace OfficeOpenXml.Drawing.Vml;
@@ -149,6 +152,179 @@
}
}
+ private const string _backgroundcolorPath = "@fillcolor";
+
+ /// <summary>
+ /// Background color
+ /// </summary>
+ public Color BackgroundColor {
+ get {
+ string col = GetXmlNodeString(_backgroundcolorPath);
+ if (col == "") {
+ return Color.FromArgb(0xff, 0xff, 0xe1);
+ }
+ if (col.StartsWith("#")) {
+ col = col.Substring(1, col.Length - 1);
+ }
+ if (int.TryParse(
+ col,
+ NumberStyles.AllowHexSpecifier,
+ CultureInfo.InvariantCulture,
+ out var res)) {
+ return Color.FromArgb(res);
+ }
+ return Color.Empty;
+ }
+ set {
+ string color = "#" + value.ToArgb().ToString("X").Substring(2, 6);
+ SetXmlNodeString(_backgroundcolorPath, color);
+ //SetXmlNode(BACKGROUNDCOLOR2_PATH, color);
+ }
+ }
+
+ private const string _linestylePath = "v:stroke/@dashstyle";
+ private const string _endcapPath = "v:stroke/@endcap";
+
+ /// <summary>
+ /// Linestyle for border
+ /// </summary>
+ public eLineStyleVml LineStyle {
+ get {
+ string v = GetXmlNodeString(_linestylePath);
+ if (v == "") {
+ return eLineStyleVml.Solid;
+ }
+ if (v == "1 1") {
+ v = GetXmlNodeString(_endcapPath);
+ return (eLineStyleVml)Enum.Parse(typeof(eLineStyleVml), v, true);
+ }
+ return (eLineStyleVml)Enum.Parse(typeof(eLineStyleVml), v, true);
+ }
+ set {
+ if (value == eLineStyleVml.Round || value == eLineStyleVml.Square) {
+ SetXmlNodeString(_linestylePath, "1 1");
+ if (value == eLineStyleVml.Round) {
+ SetXmlNodeString(_endcapPath, "round");
+ } else {
+ DeleteNode(_endcapPath);
+ }
+ } else {
+ string v = value.ToString();
+ v = v.Substring(0, 1).ToLower(CultureInfo.InvariantCulture) + v.Substring(1, v.Length - 1);
+ SetXmlNodeString(_linestylePath, v);
+ DeleteNode(_endcapPath);
+ }
+ }
+ }
+
+ private const string _linecolorPath = "@strokecolor";
+
+ /// <summary>
+ /// Line color
+ /// </summary>
+ public Color LineColor {
+ get {
+ string col = GetXmlNodeString(_linecolorPath);
+ if (col == "") {
+ return Color.Black;
+ }
+ if (col.StartsWith("#")) {
+ col = col.Substring(1, col.Length - 1);
+ }
+ if (int.TryParse(
+ col,
+ NumberStyles.AllowHexSpecifier,
+ CultureInfo.InvariantCulture,
+ out var res)) {
+ return Color.FromArgb(res);
+ }
+ return Color.Empty;
+ }
+ set {
+ string color = "#" + value.ToArgb().ToString("X").Substring(2, 6);
+ SetXmlNodeString(_linecolorPath, color);
+ }
+ }
+
+ private const string _linewidthPath = "@strokeweight";
+
+ /// <summary>
+ /// Width of the border
+ /// </summary>
+ public Single LineWidth {
+ get {
+ string wt = GetXmlNodeString(_linewidthPath);
+ if (wt == "") {
+ return (Single).75;
+ }
+ if (wt.EndsWith("pt")) {
+ wt = wt.Substring(0, wt.Length - 2);
+ }
+
+ if (Single.TryParse(wt, NumberStyles.Any, CultureInfo.InvariantCulture, out var ret)) {
+ return ret;
+ }
+ return 0;
+ }
+ set => SetXmlNodeString(_linewidthPath, value.ToString(CultureInfo.InvariantCulture) + "pt");
+ }
+
+ ///// <summary>
+ ///// Width of the Comment
+ ///// </summary>
+ //public Single Width
+ //{
+ // get
+ // {
+ // string v;
+ // GetStyle("width", out v);
+ // if(v.EndsWith("pt"))
+ // {
+ // v = v.Substring(0, v.Length - 2);
+ // }
+ // short ret;
+ // if (short.TryParse(v,System.Globalization.NumberStyles.Any, CultureInfo.InvariantCulture, out ret))
+ // {
+ // return ret;
+ // }
+ // else
+ // {
+ // return 0;
+ // }
+ // }
+ // set
+ // {
+ // SetStyle("width", value.ToString("N2",CultureInfo.InvariantCulture) + "pt");
+ // }
+ //}
+ ///// <summary>
+ ///// Height of the Comment
+ ///// </summary>
+ //public Single Height
+ //{
+ // get
+ // {
+ // string v;
+ // GetStyle("height", out v);
+ // if (v.EndsWith("pt"))
+ // {
+ // v = v.Substring(0, v.Length - 2);
+ // }
+ // short ret;
+ // if (short.TryParse(v, System.Globalization.NumberStyles.Any, CultureInfo.InvariantCulture, out ret))
+ // {
+ // return ret;
+ // }
+ // else
+ // {
+ // return 0;
+ // }
+ // }
+ // set
+ // {
+ // SetStyle("height", value.ToString("N2", CultureInfo.InvariantCulture) + "pt");
+ // }
+ //}
private const string _textboxStylePath = "v:textbox/@style";
/// <summary>
diff --git a/EPPlus/EPPlusSDK.csproj b/EPPlus/EPPlusSDK.csproj
index 1941081..a26851d 100644
--- a/EPPlus/EPPlusSDK.csproj
+++ b/EPPlus/EPPlusSDK.csproj
@@ -4,6 +4,9 @@
<RootNamespace>OfficeOpenXml</RootNamespace>
<AssemblyName>EPPlus</AssemblyName>
<PackageId>Appsheet.EPPlus</PackageId>
- <Version>1.0.7</Version>
+ <Version>1.0.6</Version>
</PropertyGroup>
+ <ItemGroup>
+ <PackageReference Include="System.Drawing.Common" Version="6.0.0"/>
+ </ItemGroup>
</Project>
diff --git a/EPPlus/ExcelComment.cs b/EPPlus/ExcelComment.cs
index 3930bde..9e7409a 100644
--- a/EPPlus/ExcelComment.cs
+++ b/EPPlus/ExcelComment.cs
@@ -124,6 +124,7 @@
}
return _text;
}
+ set => RichText.Text = value;
}
/// <summary>
diff --git a/EPPlus/ExcelCommentCollection.cs b/EPPlus/ExcelCommentCollection.cs
index 9146df7..b4a60af 100644
--- a/EPPlus/ExcelCommentCollection.cs
+++ b/EPPlus/ExcelCommentCollection.cs
@@ -138,9 +138,79 @@
}
}
+ /// <summary>
+ /// Adds a comment to the top left cell of the range
+ /// </summary>
+ /// <param name="cell">The cell</param>
+ /// <param name="text">The comment text</param>
+ /// <param name="author">Author</param>
+ /// <returns>The comment</returns>
+ public ExcelComment Add(ExcelRangeBase cell, string text, string author) {
+ var elem = CommentXml.CreateElement("comment", ExcelPackage._schemaMain);
+ int ix = _comments.IndexOf(
+ ExcelCellBase.GetCellId(Worksheet.SheetID, cell._fromRow, cell._fromCol));
+ //Make sure the nodes come on order.
+ if (ix < 0 && (~ix < _comments.Count)) {
+ ix = ~ix;
+ var preComment = _comments[ix] as ExcelComment;
+ preComment._commentHelper.TopNode.ParentNode.InsertBefore(
+ elem,
+ preComment._commentHelper.TopNode);
+ } else {
+ CommentXml.SelectSingleNode("d:comments/d:commentList", NameSpaceManager).AppendChild(elem);
+ }
+ elem.SetAttribute("ref", cell.Start.Address);
+ ExcelComment comment = new ExcelComment(NameSpaceManager, elem, cell);
+ comment.RichText.Add(text);
+ if (author != "") {
+ comment.Author = author;
+ }
+ _comments.Add(comment);
+ //Check if a value exists otherwise add one so it is saved when the cells collection is iterated
+ if (!Worksheet._values.Exists(cell._fromRow, cell._fromCol)) {
+ Worksheet._values.SetValue(cell._fromRow, cell._fromCol, null);
+ }
+ return comment;
+ }
+
+ /// <summary>
+ /// Removes the comment
+ /// </summary>
+ /// <param name="comment">The comment to remove</param>
+ public void Remove(ExcelComment comment) {
+ ulong id = ExcelCellBase.GetCellId(
+ Worksheet.SheetID,
+ comment.Range._fromRow,
+ comment.Range._fromCol);
+ int ix = _comments.IndexOf(id);
+ if (ix >= 0 && comment == _comments[ix]) {
+ comment.TopNode.ParentNode.RemoveChild(comment.TopNode); //Remove VML
+ comment._commentHelper.TopNode.ParentNode.RemoveChild(comment._commentHelper.TopNode); //Remove Comment
+
+ Worksheet.VmlDrawingsComments._drawings.Delete(id);
+ _comments.Delete(id);
+ } else {
+ throw (new ArgumentException("Comment does not exist in the worksheet"));
+ }
+ }
+
void IDisposable.Dispose() {}
+ /// <summary>
+ /// Removes the comment at the specified position
+ /// </summary>
+ /// <param name="index">The index</param>
+ public void RemoveAt(int index) {
+ Remove(this[index]);
+ }
+
IEnumerator IEnumerable.GetEnumerator() {
return _comments;
}
+
+ internal void Clear() {
+ while (Count > 0) {
+ RemoveAt(0);
+ }
+ }
}
diff --git a/EPPlus/ExcelPrinterSettings.cs b/EPPlus/ExcelPrinterSettings.cs
new file mode 100644
index 0000000..dcdb541
--- /dev/null
+++ b/EPPlus/ExcelPrinterSettings.cs
@@ -0,0 +1,773 @@
+/*******************************************************************************
+ * You may amend and distribute as you like, but don't remove this header!
+ *
+ * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
+ * See http://www.codeplex.com/EPPlus for details.
+ *
+ * Copyright (C) 2011 Jan Källman
+ *
+ * This library is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 2.1 of the License, or (at your option) any later version.
+
+ * This library is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
+ * See the GNU Lesser General Public License for more details.
+ *
+ * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
+ * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
+ *
+ * All code and executables are provided "as is" with no warranty either express or implied.
+ * The author accepts no liability for any damage or loss of business that this product may cause.
+ *
+ * Code change notes:
+ *
+ * Author Change Date
+ * ******************************************************************************
+ * Jan Källman Initial Release 2009-10-01
+ * Jan Källman License changed GPL-->LGPL 2011-12-27
+ *******************************************************************************/
+
+using System;
+using System.Globalization;
+using System.Xml;
+
+namespace OfficeOpenXml;
+
+/// <summary>
+/// Printer orientation
+/// </summary>
+public enum eOrientation {
+ /// <summary>
+ /// Portrait orientation
+ /// </summary>
+ Portrait,
+
+ /// <summary>
+ /// Landscape orientation
+ /// </summary>
+ Landscape,
+}
+
+/// <summary>
+/// Papersize
+/// </summary>
+public enum ePaperSize {
+ /// <summary>
+ /// Letter paper (8.5 in. by 11 in.)
+ /// </summary>
+ Letter = 1,
+
+ /// <summary>
+ /// Letter small paper (8.5 in. by 11 in.)
+ /// </summary>
+ LetterSmall = 2,
+
+ /// <summary>
+ /// // Tabloid paper (11 in. by 17 in.)
+ /// </summary>
+ Tabloid = 3,
+
+ /// <summary>
+ /// Ledger paper (17 in. by 11 in.)
+ /// </summary>
+ Ledger = 4,
+
+ /// <summary>
+ /// Legal paper (8.5 in. by 14 in.)
+ /// </summary>
+ Legal = 5,
+
+ /// <summary>
+ /// Statement paper (5.5 in. by 8.5 in.)
+ /// </summary>
+ Statement = 6,
+
+ /// <summary>
+ /// Executive paper (7.25 in. by 10.5 in.)
+ /// </summary>
+ Executive = 7,
+
+ /// <summary>
+ /// A3 paper (297 mm by 420 mm)
+ /// </summary>
+ A3 = 8,
+
+ /// <summary>
+ /// A4 paper (210 mm by 297 mm)
+ /// </summary>
+ A4 = 9,
+
+ /// <summary>
+ /// A4 small paper (210 mm by 297 mm)
+ /// </summary>
+ A4Small = 10,
+
+ /// <summary>
+ /// A5 paper (148 mm by 210 mm)
+ /// </summary>
+ A5 = 11,
+
+ /// <summary>
+ /// B4 paper (250 mm by 353 mm)
+ /// </summary>
+ B4 = 12,
+
+ /// <summary>
+ /// B5 paper (176 mm by 250 mm)
+ /// </summary>
+ B5 = 13,
+
+ /// <summary>
+ /// Folio paper (8.5 in. by 13 in.)
+ /// </summary>
+ Folio = 14,
+
+ /// <summary>
+ /// Quarto paper (215 mm by 275 mm)
+ /// </summary>
+ Quarto = 15,
+
+ /// <summary>
+ /// Standard paper (10 in. by 14 in.)
+ /// </summary>
+ Standard1014 = 16,
+
+ /// <summary>
+ /// Standard paper (11 in. by 17 in.)
+ /// </summary>
+ Standard1117 = 17,
+
+ /// <summary>
+ /// Note paper (8.5 in. by 11 in.)
+ /// </summary>
+ Note = 18,
+
+ /// <summary>
+ /// #9 envelope (3.875 in. by 8.875 in.)
+ /// </summary>
+ Envelope9 = 19,
+
+ /// <summary>
+ /// #10 envelope (4.125 in. by 9.5 in.)
+ /// </summary>
+ Envelope10 = 20,
+
+ /// <summary>
+ /// #11 envelope (4.5 in. by 10.375 in.)
+ /// </summary>
+ Envelope11 = 21,
+
+ /// <summary>
+ /// #12 envelope (4.75 in. by 11 in.)
+ /// </summary>
+ Envelope12 = 22,
+
+ /// <summary>
+ /// #14 envelope (5 in. by 11.5 in.)
+ /// </summary>
+ Envelope14 = 23,
+
+ /// <summary>
+ /// C paper (17 in. by 22 in.)
+ /// </summary>
+ C = 24,
+
+ /// <summary>
+ /// D paper (22 in. by 34 in.)
+ /// </summary>
+ D = 25,
+
+ /// <summary>
+ /// E paper (34 in. by 44 in.)
+ /// </summary>
+ E = 26,
+
+ /// <summary>
+ /// DL envelope (110 mm by 220 mm)
+ /// </summary>
+ DlEnvelope = 27,
+
+ /// <summary>
+ /// C5 envelope (162 mm by 229 mm)
+ /// </summary>
+ C5Envelope = 28,
+
+ /// <summary>
+ /// C3 envelope (324 mm by 458 mm)
+ /// </summary>
+ C3Envelope = 29,
+
+ /// <summary>
+ /// C4 envelope (229 mm by 324 mm)
+ /// </summary>
+ C4Envelope = 30,
+
+ /// <summary>
+ /// C6 envelope (114 mm by 162 mm)
+ /// </summary>
+ C6Envelope = 31,
+
+ /// <summary>
+ /// C65 envelope (114 mm by 229 mm)
+ /// </summary>
+ C65Envelope = 32,
+
+ /// <summary>
+ /// B4 envelope (250 mm by 353 mm)
+ /// </summary>
+ B4Envelope = 33,
+
+ /// <summary>
+ /// B5 envelope (176 mm by 250 mm)
+ /// </summary>
+ B5Envelope = 34,
+
+ /// <summary>
+ /// B6 envelope (176 mm by 125 mm)
+ /// </summary>
+ B6Envelope = 35,
+
+ /// <summary>
+ /// Italy envelope (110 mm by 230 mm)
+ /// </summary>
+ ItalyEnvelope = 36,
+
+ /// <summary>
+ /// Monarch envelope (3.875 in. by 7.5 in.).
+ /// </summary>
+ MonarchEnvelope = 37,
+
+ /// <summary>
+ /// 6 3/4 envelope (3.625 in. by 6.5 in.)
+ /// </summary>
+ Six34Envelope = 38,
+
+ /// <summary>
+ /// US standard fanfold (14.875 in. by 11 in.)
+ /// </summary>
+ UsStandard = 39,
+
+ /// <summary>
+ /// German standard fanfold (8.5 in. by 12 in.)
+ /// </summary>
+ GermanStandard = 40,
+
+ /// <summary>
+ /// German legal fanfold (8.5 in. by 13 in.)
+ /// </summary>
+ GermanLegal = 41,
+
+ /// <summary>
+ /// ISO B4 (250 mm by 353 mm)
+ /// </summary>
+ Isob4 = 42,
+
+ /// <summary>
+ /// Japanese double postcard (200 mm by 148 mm)
+ /// </summary>
+ JapaneseDoublePostcard = 43,
+
+ /// <summary>
+ /// Standard paper (9 in. by 11 in.)
+ /// </summary>
+ Standard9 = 44,
+
+ /// <summary>
+ /// Standard paper (10 in. by 11 in.)
+ /// </summary>
+ Standard10 = 45,
+
+ /// <summary>
+ /// Standard paper (15 in. by 11 in.)
+ /// </summary>
+ Standard15 = 46,
+
+ /// <summary>
+ /// Invite envelope (220 mm by 220 mm)
+ /// </summary>
+ InviteEnvelope = 47,
+
+ /// <summary>
+ /// Letter extra paper (9.275 in. by 12 in.)
+ /// </summary>
+ LetterExtra = 50,
+
+ /// <summary>
+ /// Legal extra paper (9.275 in. by 15 in.)
+ /// </summary>
+ LegalExtra = 51,
+
+ /// <summary>
+ /// Tabloid extra paper (11.69 in. by 18 in.)
+ /// </summary>
+ TabloidExtra = 52,
+
+ /// <summary>
+ /// A4 extra paper (236 mm by 322 mm)
+ /// </summary>
+ A4Extra = 53,
+
+ /// <summary>
+ /// Letter transverse paper (8.275 in. by 11 in.)
+ /// </summary>
+ LetterTransverse = 54,
+
+ /// <summary>
+ /// A4 transverse paper (210 mm by 297 mm)
+ /// </summary>
+ A4Transverse = 55,
+
+ /// <summary>
+ /// Letter extra transverse paper (9.275 in. by 12 in.)
+ /// </summary>
+ LetterExtraTransverse = 56,
+
+ /// <summary>
+ /// SuperA/SuperA/A4 paper (227 mm by 356 mm)
+ /// </summary>
+ SuperA = 57,
+
+ /// <summary>
+ /// SuperB/SuperB/A3 paper (305 mm by 487 mm)
+ /// </summary>
+ SuperB = 58,
+
+ /// <summary>
+ /// Letter plus paper (8.5 in. by 12.69 in.)
+ /// </summary>
+ LetterPlus = 59,
+
+ /// <summary>
+ /// A4 plus paper (210 mm by 330 mm)
+ /// </summary>
+ A4Plus = 60,
+
+ /// <summary>
+ /// A5 transverse paper (148 mm by 210 mm)
+ /// </summary>
+ A5Transverse = 61,
+
+ /// <summary>
+ /// JIS B5 transverse paper (182 mm by 257 mm)
+ /// </summary>
+ Jisb5Transverse = 62,
+
+ /// <summary>
+ /// A3 extra paper (322 mm by 445 mm)
+ /// </summary>
+ A3Extra = 63,
+
+ /// <summary>
+ /// A5 extra paper (174 mm by 235 mm)
+ /// </summary>
+ A5Extra = 64,
+
+ /// <summary>
+ /// ISO B5 extra paper (201 mm by 276 mm)
+ /// </summary>
+ Isob5 = 65,
+
+ /// <summary>
+ /// A2 paper (420 mm by 594 mm)
+ /// </summary>
+ A2 = 66,
+
+ /// <summary>
+ /// A3 transverse paper (297 mm by 420 mm)
+ /// </summary>
+ A3Transverse = 67,
+
+ /// <summary>
+ /// A3 extra transverse paper (322 mm by 445 mm*/
+ /// </summary>
+ A3ExtraTransverse = 68,
+}
+
+/// <summary>
+/// Specifies printed page order
+/// </summary>
+public enum ePageOrder {
+ /// <summary>
+ /// Order pages vertically first, then move horizontally.
+ /// </summary>
+ DownThenOver,
+
+ /// <summary>
+ /// Order pages horizontally first, then move vertically
+ /// </summary>
+ OverThenDown,
+}
+
+/// <summary>
+/// Printer settings
+/// </summary>
+public sealed class ExcelPrinterSettings : XmlHelper {
+ private readonly ExcelWorksheet _ws;
+ private bool _marginsCreated;
+
+ internal ExcelPrinterSettings(XmlNamespaceManager ns, XmlNode topNode, ExcelWorksheet ws)
+ : base(ns, topNode) {
+ _ws = ws;
+ SchemaNodeOrder = ws.SchemaNodeOrder;
+ }
+
+ private const string _leftMarginPath = "d:pageMargins/@left";
+
+ /// <summary>
+ /// Left margin in inches
+ /// </summary>
+ public decimal LeftMargin {
+ get => GetXmlNodeDecimal(_leftMarginPath);
+ set {
+ CreateMargins();
+ SetXmlNodeString(_leftMarginPath, value.ToString(CultureInfo.InvariantCulture));
+ }
+ }
+
+ private const string _rightMarginPath = "d:pageMargins/@right";
+
+ /// <summary>
+ /// Right margin in inches
+ /// </summary>
+ public decimal RightMargin {
+ get => GetXmlNodeDecimal(_rightMarginPath);
+ set {
+ CreateMargins();
+ SetXmlNodeString(_rightMarginPath, value.ToString(CultureInfo.InvariantCulture));
+ }
+ }
+
+ private const string _topMarginPath = "d:pageMargins/@top";
+
+ /// <summary>
+ /// Top margin in inches
+ /// </summary>
+ public decimal TopMargin {
+ get => GetXmlNodeDecimal(_topMarginPath);
+ set {
+ CreateMargins();
+ SetXmlNodeString(_topMarginPath, value.ToString(CultureInfo.InvariantCulture));
+ }
+ }
+
+ private const string _bottomMarginPath = "d:pageMargins/@bottom";
+
+ /// <summary>
+ /// Bottom margin in inches
+ /// </summary>
+ public decimal BottomMargin {
+ get => GetXmlNodeDecimal(_bottomMarginPath);
+ set {
+ CreateMargins();
+ SetXmlNodeString(_bottomMarginPath, value.ToString(CultureInfo.InvariantCulture));
+ }
+ }
+
+ private const string _headerMarginPath = "d:pageMargins/@header";
+
+ /// <summary>
+ /// Header margin in inches
+ /// </summary>
+ public decimal HeaderMargin {
+ get => GetXmlNodeDecimal(_headerMarginPath);
+ set {
+ CreateMargins();
+ SetXmlNodeString(_headerMarginPath, value.ToString(CultureInfo.InvariantCulture));
+ }
+ }
+
+ private const string _footerMarginPath = "d:pageMargins/@footer";
+
+ /// <summary>
+ /// Footer margin in inches
+ /// </summary>
+ public decimal FooterMargin {
+ get => GetXmlNodeDecimal(_footerMarginPath);
+ set {
+ CreateMargins();
+ SetXmlNodeString(_footerMarginPath, value.ToString(CultureInfo.InvariantCulture));
+ }
+ }
+
+ private const string _orientationPath = "d:pageSetup/@orientation";
+
+ /// <summary>
+ /// Orientation
+ /// Portrait or Landscape
+ /// </summary>
+ public eOrientation Orientation {
+ get => (eOrientation)Enum.Parse(typeof(eOrientation), GetXmlNodeString(_orientationPath), true);
+ set =>
+ SetXmlNodeString(_orientationPath, value.ToString().ToLower(CultureInfo.InvariantCulture));
+ }
+
+ private const string _fitToWidthPath = "d:pageSetup/@fitToWidth";
+
+ /// <summary>
+ /// Fit to Width in pages.
+ /// Set FitToPage to true when using this one.
+ /// 0 is automatic
+ /// </summary>
+ public int FitToWidth {
+ get => GetXmlNodeInt(_fitToWidthPath);
+ set => SetXmlNodeString(_fitToWidthPath, value.ToString());
+ }
+
+ private const string _fitToHeightPath = "d:pageSetup/@fitToHeight";
+
+ /// <summary>
+ /// Fit to height in pages.
+ /// Set FitToPage to true when using this one.
+ /// 0 is automatic
+ /// </summary>
+ public int FitToHeight {
+ get => GetXmlNodeInt(_fitToHeightPath);
+ set => SetXmlNodeString(_fitToHeightPath, value.ToString());
+ }
+
+ private const string _scalePath = "d:pageSetup/@scale";
+
+ /// <summary>
+ /// Print scale
+ /// </summary>
+ public int Scale {
+ get => GetXmlNodeInt(_scalePath);
+ set => SetXmlNodeString(_scalePath, value.ToString());
+ }
+
+ private const string _fitToPagePath = "d:sheetPr/d:pageSetUpPr/@fitToPage";
+
+ /// <summary>
+ /// Fit To Page.
+ /// </summary>
+ public bool FitToPage {
+ get => GetXmlNodeBool(_fitToPagePath);
+ set => SetXmlNodeString(_fitToPagePath, value ? "1" : "0");
+ }
+
+ private const string _headersPath = "d:printOptions/@headings";
+
+ /// <summary>
+ /// Print headings (column letter and row numbers)
+ /// </summary>
+ public bool ShowHeaders {
+ get => GetXmlNodeBool(_headersPath, false);
+ set => SetXmlNodeBool(_headersPath, value, false);
+ }
+
+ /// <summary>
+ /// Print titles
+ /// Rows to be repeated after each pagebreak.
+ /// The address must be a full row address (ex. 1:1)
+ /// </summary>
+ public ExcelAddress RepeatRows {
+ get {
+ if (_ws.Names.ContainsKey("_xlnm.Print_Titles")) {
+ ExcelRangeBase r = _ws.Names["_xlnm.Print_Titles"];
+ if (r.Start.Column == 1 && r.End.Column == ExcelPackage.MaxColumns) {
+ return new(r.FirstAddress);
+ }
+ if (r._addresses != null
+ && r.Addresses[0].Start.Column == 1
+ && r.Addresses[0].End.Column == ExcelPackage.MaxColumns) {
+ return r._addresses[0];
+ }
+ return null;
+ }
+ return null;
+ }
+ set {
+ //Must span entire columns
+ if (!(value.Start.Column == 1 && value.End.Column == ExcelPackage.MaxColumns)) {
+ throw new InvalidOperationException(
+ "Address must span full columns only (for ex. Address=\"A:A\" for the first column).");
+ }
+
+ var vertAddr = RepeatColumns;
+ string addr;
+ if (vertAddr == null) {
+ addr = value.Address;
+ } else {
+ addr = vertAddr.Address + "," + value.Address;
+ }
+
+ if (_ws.Names.ContainsKey("_xlnm.Print_Titles")) {
+ _ws.Names["_xlnm.Print_Titles"].Address = addr;
+ } else {
+ _ws.Names.Add("_xlnm.Print_Titles", new(_ws, addr));
+ }
+ }
+ }
+
+ /// <summary>
+ /// Print titles
+ /// Columns to be repeated after each pagebreak.
+ /// The address must be a full column address (ex. A:A)
+ /// </summary>
+ public ExcelAddress RepeatColumns {
+ get {
+ if (_ws.Names.ContainsKey("_xlnm.Print_Titles")) {
+ ExcelRangeBase r = _ws.Names["_xlnm.Print_Titles"];
+ if (r.Start.Row == 1 && r.End.Row == ExcelPackage.MaxRows) {
+ return new(r.FirstAddress);
+ }
+ if (r._addresses != null
+ && (r._addresses[0].Start.Row == 1
+ && r._addresses[0].End.Row == ExcelPackage.MaxRows)) {
+ return r._addresses[0];
+ }
+ return null;
+ }
+ return null;
+ }
+ set {
+ //Must span entire rows
+ if (!(value.Start.Row == 1 && value.End.Row == ExcelPackage.MaxRows)) {
+ throw new InvalidOperationException(
+ "Address must span rows only (for ex. Address=\"1:1\" for the first row).");
+ }
+
+ var horAddr = RepeatRows;
+ string addr;
+ if (horAddr == null) {
+ addr = value.Address;
+ } else {
+ addr = value.Address + "," + horAddr.Address;
+ }
+
+ if (_ws.Names.ContainsKey("_xlnm.Print_Titles")) {
+ _ws.Names["_xlnm.Print_Titles"].Address = addr;
+ } else {
+ _ws.Names.Add("_xlnm.Print_Titles", new(_ws, addr));
+ }
+ }
+ }
+
+ /// <summary>
+ /// The printarea.
+ /// Null if no print area is set.
+ /// </summary>
+ public ExcelRangeBase PrintArea {
+ get {
+ if (_ws.Names.ContainsKey("_xlnm.Print_Area")) {
+ return _ws.Names["_xlnm.Print_Area"];
+ }
+ return null;
+ }
+ set {
+ if (value == null) {
+ _ws.Names.Remove("_xlnm.Print_Area");
+ } else if (_ws.Names.ContainsKey("_xlnm.Print_Area")) {
+ _ws.Names["_xlnm.Print_Area"].Address = value.Address;
+ } else {
+ _ws.Names.Add("_xlnm.Print_Area", value);
+ }
+ }
+ }
+
+ private const string _gridLinesPath = "d:printOptions/@gridLines";
+
+ /// <summary>
+ /// Print gridlines
+ /// </summary>
+ public bool ShowGridLines {
+ get => GetXmlNodeBool(_gridLinesPath, false);
+ set => SetXmlNodeBool(_gridLinesPath, value, false);
+ }
+
+ private const string _horizontalCenteredPath = "d:printOptions/@horizontalCentered";
+
+ /// <summary>
+ /// Horizontal centered when printing
+ /// </summary>w
+ public bool HorizontalCentered {
+ get => GetXmlNodeBool(_horizontalCenteredPath, false);
+ set => SetXmlNodeBool(_horizontalCenteredPath, value, false);
+ }
+
+ private const string _verticalCenteredPath = "d:printOptions/@verticalCentered";
+
+ /// <summary>
+ /// Vertical centered when printing
+ /// </summary>
+ public bool VerticalCentered {
+ get => GetXmlNodeBool(_verticalCenteredPath, false);
+ set => SetXmlNodeBool(_verticalCenteredPath, value, false);
+ }
+
+ private const string _pageOrderPath = "d:pageSetup/@pageOrder";
+
+ /// <summary>
+ /// Specifies printed page order
+ /// </summary>
+ public ePageOrder PageOrder {
+ get {
+ if (GetXmlNodeString(_pageOrderPath) == "overThenDown") {
+ return ePageOrder.OverThenDown;
+ }
+ return ePageOrder.DownThenOver;
+ }
+ set {
+ if (value == ePageOrder.OverThenDown) {
+ SetXmlNodeString(_pageOrderPath, "overThenDown");
+ } else {
+ DeleteNode(_pageOrderPath);
+ }
+ }
+ }
+
+ private const string _blackAndWhitePath = "d:pageSetup/@blackAndWhite";
+
+ /// <summary>
+ /// Print black and white
+ /// </summary>
+ public bool BlackAndWhite {
+ get => GetXmlNodeBool(_blackAndWhitePath, false);
+ set => SetXmlNodeBool(_blackAndWhitePath, value, false);
+ }
+
+ private const string _draftPath = "d:pageSetup/@draft";
+
+ /// <summary>
+ /// Print a draft
+ /// </summary>
+ public bool Draft {
+ get => GetXmlNodeBool(_draftPath, false);
+ set => SetXmlNodeBool(_draftPath, value, false);
+ }
+
+ private const string _paperSizePath = "d:pageSetup/@paperSize";
+
+ /// <summary>
+ /// Paper size
+ /// </summary>
+ public ePaperSize PaperSize {
+ get {
+ string s = GetXmlNodeString(_paperSizePath);
+ if (s != "") {
+ return (ePaperSize)int.Parse(s);
+ }
+ return ePaperSize.Letter;
+ }
+ set => SetXmlNodeString(_paperSizePath, ((int)value).ToString());
+ }
+
+ /// <summary>
+ /// All or none of the margin attributes must exist. Create all att ones.
+ /// </summary>
+ private void CreateMargins() {
+ if (_marginsCreated == false
+ && TopNode.SelectSingleNode(_leftMarginPath, NameSpaceManager) == null) {
+ _marginsCreated = true;
+ LeftMargin = 0.7087M;
+ RightMargin = 0.7087M;
+ TopMargin = 0.7480M;
+ BottomMargin = 0.7480M;
+ HeaderMargin = 0.315M;
+ FooterMargin = 0.315M;
+ }
+ }
+}
diff --git a/EPPlus/ExcelRangeBase.cs b/EPPlus/ExcelRangeBase.cs
index 686eb78..b4e48a8 100644
--- a/EPPlus/ExcelRangeBase.cs
+++ b/EPPlus/ExcelRangeBase.cs
@@ -349,6 +349,12 @@
}
}
+ private void Set_Comment(object value, int row, int col) {
+ string[] v = (string[])value;
+ Worksheet.Comments.Add(new(_worksheet, GetAddress(_fromRow, _fromCol)), v[0], v[1]);
+ // _worksheet.Cell(row, col).Comment = comment;
+ }
+
private void SetToSelectedRange() {
if (_worksheet.View.SelectedRange == "") {
Address = "A1";
@@ -1831,6 +1837,339 @@
}
/// <summary>
+ /// Adds a new comment for the range.
+ /// If this range contains more than one cell, the top left comment is returned by the method.
+ /// </summary>
+ /// <param name="text"></param>
+ /// <param name="author"></param>
+ /// <returns>A reference comment of the top left cell</returns>
+ public ExcelComment AddComment(string text, string author) {
+ if (string.IsNullOrEmpty(author)) {
+ author = Thread.CurrentPrincipal.Identity.Name;
+ }
+ //Check if any comments exists in the range and throw an exception
+ _changePropMethod(Exists_Comment, null);
+ //Create the comments
+ _changePropMethod(Set_Comment, new[] { text, author });
+
+ return _worksheet.Comments[new ExcelCellAddress(_fromRow, _fromCol)];
+ }
+
+ ///// <summary>
+ ///// Copies the range of cells to an other range
+ ///// </summary>
+ ///// <param name="Destination">The start cell where the range will be copied.</param>
+ public void Copy(ExcelRangeBase destination) {
+ bool sameWorkbook = destination._worksheet.Workbook == _worksheet.Workbook;
+ ExcelStyles sourceStyles = _worksheet.Workbook.Styles,
+ styles = destination._worksheet.Workbook.Styles;
+ Dictionary<int, int> styleCashe = new Dictionary<int, int>();
+
+ //Clear all existing cells;
+ int toRow = _toRow - _fromRow + 1,
+ toCol = _toCol - _fromCol + 1;
+
+ string s = "";
+ int i = 0;
+ object o = null;
+ byte flag = 0;
+ Uri hl = null;
+ ExcelComment comment = null;
+
+ var cse = new CellsStoreEnumerator<object>(
+ _worksheet._values,
+ _fromRow,
+ _fromCol,
+ _toRow,
+ _toCol);
+ var copiedValue = new List<CopiedCell>();
+ while (cse.Next()) {
+ var row = cse.Row;
+ var col = cse.Column; //Issue 15070
+ var cell = new CopiedCell {
+ Row = destination._fromRow + (row - _fromRow),
+ Column = destination._fromCol + (col - _fromCol),
+ Value = cse.Value,
+ };
+
+ //Destination._worksheet._values.SetValue(row, col, cse.Value);
+
+ if (_worksheet._types.Exists(row, col, ref s)) {
+ //Destination._worksheet._types.SetValue(row, col,s);
+ cell.Type = s;
+ }
+
+ if (_worksheet._formulas.Exists(row, col, ref o)) {
+ if (o is int) {
+ // Destination._worksheet._formulas.SetValue(row, col, _worksheet.GetFormula(cse.Row, cse.Column)); //Shared formulas, set the formula per cell to simplify
+ cell.Formula = _worksheet.GetFormula(cse.Row, cse.Column);
+ } else {
+ //Destination._worksheet._formulas.SetValue(row, col, o);
+ cell.Formula = o;
+ }
+ }
+ if (_worksheet._styles.Exists(row, col, ref i)) {
+ if (sameWorkbook) {
+ //Destination._worksheet._styles.SetValue(row, col, i);
+ cell.StyleID = i;
+ } else {
+ if (styleCashe.ContainsKey(i)) {
+ i = styleCashe[i];
+ } else {
+ var oldStyleId = i;
+ i = styles.CloneStyle(sourceStyles, i);
+ styleCashe.Add(oldStyleId, i);
+ }
+ //Destination._worksheet._styles.SetValue(row, col, i);
+ cell.StyleID = i;
+ }
+ }
+
+ if (_worksheet._hyperLinks.Exists(row, col, ref hl)) {
+ //Destination._worksheet._hyperLinks.SetValue(row, col, hl);
+ cell.HyperLink = hl;
+ }
+
+ if (_worksheet._commentsStore.Exists(row, col, ref comment)) {
+ cell.Comment = comment;
+ }
+
+ if (_worksheet._flags.Exists(row, col, ref flag)) {
+ cell.Flag = flag;
+ }
+ copiedValue.Add(cell);
+ }
+
+ //Copy styles with no cell value
+ var cses = new CellsStoreEnumerator<int>(
+ _worksheet._styles,
+ _fromRow,
+ _fromCol,
+ _toRow,
+ _toCol);
+ while (cses.Next()) {
+ if (!_worksheet._values.Exists(cses.Row, cses.Column)) {
+ var row = destination._fromRow + (cses.Row - _fromRow);
+ var col = destination._fromCol + (cses.Column - _fromCol);
+ var cell = new CopiedCell {
+ Row = row,
+ Column = col,
+ Value = null,
+ };
+
+ i = cses.Value;
+ if (sameWorkbook) {
+ cell.StyleID = i;
+ } else {
+ if (styleCashe.ContainsKey(i)) {
+ i = styleCashe[i];
+ } else {
+ var oldStyleId = i;
+ i = styles.CloneStyle(sourceStyles, i);
+ styleCashe.Add(oldStyleId, i);
+ }
+ //Destination._worksheet._styles.SetValue(row, col, i);
+ cell.StyleID = i;
+ }
+ copiedValue.Add(cell);
+ }
+ }
+ var copiedMergedCells = new Dictionary<int, ExcelAddress>();
+ //Merged cells
+ var csem = new CellsStoreEnumerator<int>(
+ _worksheet.MergedCells._cells,
+ _fromRow,
+ _fromCol,
+ _toRow,
+ _toCol);
+ while (csem.Next()) {
+ if (!copiedMergedCells.ContainsKey(csem.Value)) {
+ var adr = new ExcelAddress(_worksheet.Name, _worksheet.MergedCells.List[csem.Value]);
+ if (Collide(adr) == eAddressCollition.Inside) {
+ copiedMergedCells.Add(
+ csem.Value,
+ new(
+ destination._fromRow + (adr.Start.Row - _fromRow),
+ destination._fromCol + (adr.Start.Column - _fromCol),
+ destination._fromRow + (adr.End.Row - _fromRow),
+ destination._fromCol + (adr.End.Column - _fromCol)));
+ } else {
+ //Partial merge of the address ignore.
+ copiedMergedCells.Add(csem.Value, null);
+ }
+ }
+ }
+
+ destination._worksheet.MergedCells.Clear(
+ new(
+ destination._fromRow,
+ destination._fromCol,
+ destination._fromRow + toRow - 1,
+ destination._fromCol + toCol - 1));
+
+ destination._worksheet._values.Clear(destination._fromRow, destination._fromCol, toRow, toCol);
+ destination._worksheet._formulas.Clear(
+ destination._fromRow,
+ destination._fromCol,
+ toRow,
+ toCol);
+ destination._worksheet._styles.Clear(destination._fromRow, destination._fromCol, toRow, toCol);
+ destination._worksheet._types.Clear(destination._fromRow, destination._fromCol, toRow, toCol);
+ destination._worksheet._hyperLinks.Clear(
+ destination._fromRow,
+ destination._fromCol,
+ toRow,
+ toCol);
+ destination._worksheet._flags.Clear(destination._fromRow, destination._fromCol, toRow, toCol);
+ destination._worksheet._commentsStore.Clear(
+ destination._fromRow,
+ destination._fromCol,
+ toRow,
+ toCol);
+
+ foreach (var cell in copiedValue) {
+ destination._worksheet._values.SetValue(cell.Row, cell.Column, cell.Value);
+
+ if (cell.Type != null) {
+ destination._worksheet._types.SetValue(cell.Row, cell.Column, cell.Type);
+ }
+
+ if (cell.StyleID != null) {
+ destination._worksheet._styles.SetValue(cell.Row, cell.Column, cell.StyleID.Value);
+ }
+
+ if (cell.Formula != null) {
+ cell.Formula = UpdateFormulaReferences(
+ cell.Formula.ToString(),
+ destination._fromRow - _fromRow,
+ destination._fromCol - _fromCol,
+ 0,
+ 0,
+ true);
+ destination._worksheet._formulas.SetValue(cell.Row, cell.Column, cell.Formula);
+ }
+ if (cell.HyperLink != null) {
+ destination._worksheet._hyperLinks.SetValue(cell.Row, cell.Column, cell.HyperLink);
+ }
+
+ if (cell.Comment != null) {
+ //Destination._worksheet._commentsStore.SetValue(cell.Row, cell.Column, cell.Comment);
+ }
+ if (cell.Flag != 0) {
+ destination._worksheet._flags.SetValue(cell.Row, cell.Column, cell.Flag);
+ }
+ }
+
+ //Add merged cells
+ foreach (var m in copiedMergedCells.Values) {
+ if (m != null) {
+ destination._worksheet.MergedCells.Add(m, true);
+ }
+ }
+
+ //Clone the cell
+ //var copiedCell = (_worksheet._cells[GetCellID(_worksheet.SheetID, cell._fromRow, cell.column)] as ExcelCell);
+
+ //var newCell = copiedCell.Clone(Destination._worksheet,
+ // Destination._fromRow + (copiedCell.Row - _fromRow),
+ // Destination.column + (copiedCell.Column - column));
+
+ // newCell.MergeId = _worksheet.GetMergeCellId(copiedCell.Row, copiedCell.Column);
+
+ // if (!string.IsNullOrEmpty(newCell.Formula))
+ // {
+ // newCell.Formula = ExcelCell.UpdateFormulaReferences(newCell.Formula, newCell.Row - copiedCell.Row, (newCell.Column - copiedCell.Column), 1, 1);
+ // }
+
+ // //If its not the same workbook we must copy the styles to the new workbook.
+ // if (!sameWorkbook)
+ // {
+ // if (styleCashe.ContainsKey(cell.StyleID))
+ // {
+ // newCell.StyleID = styleCashe[cell.StyleID];
+ // }
+ // else
+ // {
+ // newCell.StyleID = styles.CloneStyle(sourceStyles, cell.StyleID);
+ // styleCashe.Add(cell.StyleID, newCell.StyleID);
+ // }
+ // }
+ // newCells.Add(newCell);
+ // if (newCell.Merge) mergedCells.Add(newCell.CellID, newCell);
+ // }
+
+ // //Now clear the destination.
+ // Destination.Offset(0, 0, (_toRow - _fromRow) + 1, (_toCol - column) + 1).Clear();
+
+ // //And last add the new cells to the worksheet
+ // foreach (var cell in newCells)
+ // {
+ // Destination.Worksheet._cells.Add(cell);
+ // }
+ // //Add merged cells
+ // if (mergedCells.Count > 0)
+ // {
+ // List<ExcelAddressBase> mergedAddresses = new List<ExcelAddressBase>();
+ // foreach (var cell in mergedCells.Values)
+ // {
+ // if (!IsAdded(cell, mergedAddresses))
+ // {
+ // int startRow = cell.Row, startCol = cell.Column, endRow = cell.Row, endCol = cell.Column + 1;
+ // while (mergedCells.ContainsKey(ExcelCell.GetCellID(Destination.Worksheet.SheetID, endRow, endCol)))
+ // {
+ // ExcelCell next = mergedCells[ExcelCell.GetCellID(Destination.Worksheet.SheetID, endRow, endCol)];
+ // if (cell.MergeId != next.MergeId)
+ // {
+ // break;
+ // }
+ // endCol++;
+ // }
+
+ // while (IsMerged(mergedCells, Destination.Worksheet, endRow, startCol, endCol - 1, cell))
+ // {
+ // endRow++;
+ // }
+
+ // mergedAddresses.Add(new ExcelAddressBase(startRow, startCol, endRow - 1, endCol - 1));
+ // }
+ // }
+ // Destination.Worksheet.MergedCells.List.AddRange((from r in mergedAddresses select r.Address));
+ // }
+ //}
+
+ //private bool IsAdded(ExcelCell cell, List<ExcelAddressBase> mergedAddresses)
+ //{
+ // foreach (var address in mergedAddresses)
+ // {
+ // if (address.Collide(new ExcelAddressBase(cell.CellAddress)) == eAddressCollition.Inside)
+ // {
+ // return true;
+ // }
+ // }
+ // return false;
+ //}
+
+ //private bool IsMerged(Dictionary<ulong, ExcelCell> mergedCells, ExcelWorksheet worksheet, int row, int startCol, int endCol, ExcelCell cell)
+ //{
+ // for (int col = startCol; col <= endCol; col++)
+ // {
+ // if (!mergedCells.ContainsKey(ExcelCell.GetCellID(worksheet.SheetID, row, col)))
+ // {
+ // return false;
+ // }
+ // else
+ // {
+ // ExcelCell next = mergedCells[ExcelCell.GetCellID(worksheet.SheetID, row, col)];
+ // if (cell.MergeId != next.MergeId)
+ // {
+ // return false;
+ // }
+ // }
+ // }
+ // return true;
+ }
+
+ /// <summary>
/// Clear all cells
/// </summary>
public void Clear() {
diff --git a/EPPlus/ExcelStyles.cs b/EPPlus/ExcelStyles.cs
index 0fd1fb3..9f1b283 100644
--- a/EPPlus/ExcelStyles.cs
+++ b/EPPlus/ExcelStyles.cs
@@ -31,6 +31,7 @@
*******************************************************************************/
using System;
+using System.Collections.Generic;
using System.Linq;
using System.Xml;
using OfficeOpenXml.ConditionalFormatting;
@@ -162,7 +163,215 @@
if (id < 0) {
id = 0;
}
- return new(this, positionId, address, id);
+ return new(this, PropertyChange, positionId, address, id);
+ }
+
+ /// <summary>
+ /// Handels changes of properties on the style objects
+ /// </summary>
+ /// <param name="sender"></param>
+ /// <param name="e"></param>
+ /// <returns></returns>
+ internal int PropertyChange(StyleBase sender, StyleChangeEventArgs e) {
+ var address = new ExcelAddressBase(e.Address);
+ var ws = _wb.Worksheets[e.PositionID];
+ Dictionary<int, int> styleCashe = new Dictionary<int, int>();
+ //Set single address
+ lock (ws._styles) {
+ SetStyleAddress(sender, e, address, ws, ref styleCashe);
+ if (address.Addresses != null) {
+ //Handle multiaddresses
+ foreach (var innerAddress in address.Addresses) {
+ SetStyleAddress(sender, e, innerAddress, ws, ref styleCashe);
+ }
+ }
+ }
+ return 0;
+ }
+
+ 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("error address"));
+ }
+ //Columns
+ if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows) {
+ ExcelColumn column;
+ int col = address.Start.Column,
+ row = 0;
+ //Get the startcolumn
+ if (!ws._values.Exists(0, address.Start.Column)) {
+ column = ws.Column(address.Start.Column);
+ } else {
+ column = (ExcelColumn)ws._values.GetValue(0, address.Start.Column);
+ }
+
+ while (column.ColumnMin <= address.End.Column) {
+ if (column.ColumnMax > address.End.Column) {
+ var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
+ column.ColumnMax = address.End.Column;
+ }
+ var s = ws._styles.GetValue(0, column.ColumnMin);
+ if (styleCashe.ContainsKey(s)) {
+ ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
+ } else {
+ ExcelXfs st = CellXfs[s];
+ int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
+ styleCashe.Add(s, newId);
+ ws.SetStyle(0, column.ColumnMin, newId);
+ }
+
+ //index++;
+
+ if (!ws._values.NextCell(ref row, ref col) || row > 0) {
+ column._columnMax = address.End.Column;
+ break;
+ }
+ column = (ws._values.GetValue(0, col) as ExcelColumn);
+ }
+
+ if (column._columnMax < address.End.Column) {
+ var newCol = ws.Column(column._columnMax + 1);
+ newCol._columnMax = address.End.Column;
+
+ var s = ws._styles.GetValue(0, column.ColumnMin);
+ if (styleCashe.ContainsKey(s)) {
+ ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
+ } else {
+ ExcelXfs st = CellXfs[s];
+ int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
+ styleCashe.Add(s, newId);
+ ws.SetStyle(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<int>(
+ ws._styles,
+ 1,
+ address._fromCol,
+ address._toRow,
+ address._toCol);
+ while (cse.Next()) {
+ if (cse.Column >= address.Start.Column && cse.Column <= address.End.Column) {
+ if (styleCashe.ContainsKey(cse.Value)) {
+ ws.SetStyle(cse.Row, cse.Column, styleCashe[cse.Value]);
+ } else {
+ ExcelXfs st = CellXfs[cse.Value];
+ int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
+ styleCashe.Add(cse.Value, newId);
+ cse.Value = newId;
+ //ws.SetStyle(cse.Row, cse.Column, newId);
+ }
+ }
+ }
+
+ //Update cells with styled columns
+ cse = new(ws._styles, 1, 0, address._toRow, 0);
+ while (cse.Next()) {
+ for (int c = address._fromRow; c <= address._toCol; c++) {
+ if (!ws._styles.Exists(cse.Row, c)) {
+ if (styleCashe.ContainsKey(cse.Value)) {
+ ws.SetStyle(cse.Row, c, styleCashe[cse.Value]);
+ } else {
+ ExcelXfs st = CellXfs[cse.Value];
+ int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
+ styleCashe.Add(cse.Value, newId);
+ ws.SetStyle(cse.Row, c, newId);
+ }
+ }
+ }
+ }
+ }
+ //Rows
+ else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns) {
+ for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++) {
+ var s = ws._styles.GetValue(rowNum, 0);
+ if (s == 0) {
+ //iterate all columns and set the row to the style of the last column
+ var cse = new CellsStoreEnumerator<int>(ws._styles, 0, 1, 0, ExcelPackage.MaxColumns);
+ while (cse.Next()) {
+ s = cse.Value;
+ var c = ws._values.GetValue(cse.Row, cse.Column) as ExcelColumn;
+ if (c != null && c.ColumnMax < ExcelPackage.MaxColumns) {
+ for (int col = c.ColumnMin; col < c.ColumnMax; col++) {
+ if (!ws._styles.Exists(rowNum, col)) {
+ ws._styles.SetValue(rowNum, col, s);
+ }
+ }
+ }
+ }
+ ws.SetStyle(rowNum, 0, s);
+ }
+ if (styleCashe.ContainsKey(s)) {
+ ws.SetStyle(rowNum, 0, styleCashe[s]);
+ } else {
+ ExcelXfs st = CellXfs[s];
+ int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
+ styleCashe.Add(s, newId);
+ ws._styles.SetValue(rowNum, 0, newId);
+ ws.SetStyle(rowNum, 0, newId);
+ }
+ }
+
+ //Update individual cells
+ var cse2 = new CellsStoreEnumerator<int>(
+ ws._styles,
+ address._fromRow,
+ address._fromCol,
+ address._toRow,
+ address._toCol);
+ while (cse2.Next()) {
+ var s = cse2.Value;
+ if (styleCashe.ContainsKey(s)) {
+ ws.SetStyle(cse2.Row, cse2.Column, styleCashe[s]);
+ } else {
+ ExcelXfs st = CellXfs[s];
+ int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
+ styleCashe.Add(s, newId);
+ cse2.Value = newId;
+ }
+ }
+
+ //Update cells with styled rows
+ cse2 = new(ws._styles, 0, 1, 0, address._toCol);
+ while (cse2.Next()) {
+ for (int r = address._fromRow; r <= address._toRow; r++) {
+ if (!ws._styles.Exists(r, cse2.Column)) {
+ var s = cse2.Value;
+ if (styleCashe.ContainsKey(s)) {
+ ws.SetStyle(r, cse2.Column, styleCashe[s]);
+ } else {
+ ExcelXfs st = CellXfs[s];
+ int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
+ styleCashe.Add(s, newId);
+ ws.SetStyle(r, cse2.Column, newId);
+ }
+ }
+ }
+ }
+ } else //Cellrange
+ {
+ for (int col = address.Start.Column; col <= address.End.Column; col++) {
+ for (int row = address.Start.Row; row <= address.End.Row; row++) {
+ var s = GetStyleId(ws, row, col);
+ if (styleCashe.ContainsKey(s)) {
+ ws.SetStyle(row, col, styleCashe[s]);
+ } else {
+ ExcelXfs st = CellXfs[s];
+ int newId = st.GetNewId(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
+ styleCashe.Add(s, newId);
+ ws.SetStyle(row, col, newId);
+ }
+ }
+ }
+ }
}
internal int GetStyleId(ExcelWorksheet ws, int row, int col) {
@@ -195,6 +404,31 @@
return 0;
}
+ /// <summary>
+ /// Handles property changes on Named styles.
+ /// </summary>
+ /// <param name="sender"></param>
+ /// <param name="e"></param>
+ /// <returns></returns>
+ internal int NamedStylePropertyChange(StyleBase sender, StyleChangeEventArgs e) {
+ int index = NamedStyles.FindIndexById(e.Address);
+ if (index >= 0) {
+ int newId = CellStyleXfs[NamedStyles[index].StyleXfId]
+ .GetNewId(CellStyleXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
+ int prevIx = NamedStyles[index].StyleXfId;
+ NamedStyles[index].StyleXfId = newId;
+ NamedStyles[index].Style.Index = newId;
+
+ NamedStyles[index].XfId = int.MinValue;
+ foreach (var style in CellXfs) {
+ if (style.XfId == prevIx) {
+ style.XfId = newId;
+ }
+ }
+ }
+ return 0;
+ }
+
public ExcelStyleCollection<ExcelNumberFormatXml> NumberFormats = new();
public ExcelStyleCollection<ExcelFontXml> Fonts = new();
public ExcelStyleCollection<ExcelFillXml> Fills = new();
@@ -244,7 +478,7 @@
CellStyleXfs[styleXfId].XfId = CellStyleXfs.Count - 1;
int xfid = CloneStyle(styles, xfIdCopy, false, true); //Always add a new style (We create a new named style here)
CellXfs[xfid].XfId = styleXfId;
- style.Style = new(this, positionId, name, styleXfId);
+ style.Style = new(this, NamedStylePropertyChange, positionId, name, styleXfId);
style.StyleXfId = styleXfId;
style.Name = name;
diff --git a/EPPlus/ExcelWorksheet.cs b/EPPlus/ExcelWorksheet.cs
index 33d2dd8..c561c33 100644
--- a/EPPlus/ExcelWorksheet.cs
+++ b/EPPlus/ExcelWorksheet.cs
@@ -1514,6 +1514,17 @@
}
}
+ /// <summary>
+ /// Printer settings
+ /// </summary>
+ public ExcelPrinterSettings PrinterSettings {
+ get {
+ var ps = new ExcelPrinterSettings(NameSpaceManager, TopNode, this);
+ ps.SchemaNodeOrder = SchemaNodeOrder;
+ return ps;
+ }
+ }
+
///// <summary>
///// Provides access to an individual cell within the worksheet.
///// </summary>
diff --git a/EPPlus/FormulaParsing/ExpressionGraph/CompileResult.cs b/EPPlus/FormulaParsing/ExpressionGraph/CompileResult.cs
index 90693ee..2545f6a 100644
--- a/EPPlus/FormulaParsing/ExpressionGraph/CompileResult.cs
+++ b/EPPlus/FormulaParsing/ExpressionGraph/CompileResult.cs
@@ -107,7 +107,9 @@
|| DataType == DataType.Boolean
|| DataType == DataType.Date;
- public bool IsNumericString => DataType == DataType.String && ConvertUtil.IsNumericString(Result);
+ public bool IsNumericString {
+ get { return DataType == DataType.String && ConvertUtil.IsNumericString(Result); }
+ }
public bool IsResultOfSubtotal { get; set; }
diff --git a/EPPlus/RangeCollection.cs b/EPPlus/RangeCollection.cs
index c7f8218..5dfb567 100644
--- a/EPPlus/RangeCollection.cs
+++ b/EPPlus/RangeCollection.cs
@@ -103,7 +103,9 @@
/// <returns></returns>
internal IRangeId this[int index] => _cells[_cellIndex[index].ListPointer];
- internal int Count => _cells.Count;
+ internal int Count {
+ get { return _cells.Count; }
+ }
internal void Add(IRangeId cell) {
var ix = IndexOf(cell.RangeID);
diff --git a/EPPlus/Style/Dxf/DxfStyleBase.cs b/EPPlus/Style/Dxf/DxfStyleBase.cs
index 0c73cb0..7bbbcd0 100644
--- a/EPPlus/Style/Dxf/DxfStyleBase.cs
+++ b/EPPlus/Style/Dxf/DxfStyleBase.cs
@@ -21,8 +21,8 @@
protected void SetValueColor(XmlHelper helper, string path, ExcelDxfColor color) {
if (color != null && color.HasValue) {
- if (color.Rgb != null) {
- SetValue(helper, path + "/@rgb", color.Rgb);
+ if (color.Color != null) {
+ SetValue(helper, path + "/@rgb", color.Color.Value.ToArgb().ToString("x"));
} else if (color.Auto != null) {
SetValueBool(helper, path + "/@auto", color.Auto);
} else if (color.Theme != null) {
@@ -68,7 +68,7 @@
}
}
- protected string GetAsString(object v) {
+ protected internal string GetAsString(object v) {
return (v ?? "").ToString();
}
diff --git a/EPPlus/Style/Dxf/ExcelDxfColor.cs b/EPPlus/Style/Dxf/ExcelDxfColor.cs
index d22a7f9..4e6c4f9 100644
--- a/EPPlus/Style/Dxf/ExcelDxfColor.cs
+++ b/EPPlus/Style/Dxf/ExcelDxfColor.cs
@@ -1,4 +1,5 @@
using System;
+using System.Drawing;
namespace OfficeOpenXml.Style.Dxf;
@@ -14,7 +15,7 @@
public double? Tint { get; set; }
- public string Rgb { get; set; }
+ public Color? Color { get; set; }
protected internal override string Id =>
GetAsString(Theme)
@@ -25,20 +26,20 @@
+ "|"
+ GetAsString(Tint)
+ "|"
- + GetAsString(Rgb);
+ + GetAsString(Color == null ? "" : Color.Value.ToArgb().ToString("x"));
protected internal override ExcelDxfColor Clone() {
return new(_styles) {
Theme = Theme,
Index = Index,
- Rgb = Rgb,
+ Color = Color,
Auto = Auto,
Tint = Tint,
};
}
protected internal override bool HasValue =>
- Theme != null || Index != null || Auto != null || Tint != null || Rgb != null;
+ Theme != null || Index != null || Auto != null || Tint != null || Color != null;
protected internal override void CreateNodes(XmlHelper helper, string path) {
throw new NotImplementedException();
diff --git a/EPPlus/Style/Dxf/ExcelDxfStyle.cs b/EPPlus/Style/Dxf/ExcelDxfStyle.cs
index 325d5b1..7180b21 100644
--- a/EPPlus/Style/Dxf/ExcelDxfStyle.cs
+++ b/EPPlus/Style/Dxf/ExcelDxfStyle.cs
@@ -1,4 +1,5 @@
using System;
+using System.Drawing;
using System.Globalization;
using System.Xml;
@@ -52,17 +53,46 @@
return bi;
}
- private ExcelBorderStyle GetBorderStyleEnum(string style) =>
- Enum.TryParse<ExcelBorderStyle>(style, true, out var result) ? result : ExcelBorderStyle.None;
+ private ExcelBorderStyle GetBorderStyleEnum(string style) {
+ if (style == "") {
+ return ExcelBorderStyle.None;
+ }
+ string sInStyle =
+ style.Substring(0, 1).ToUpper(CultureInfo.InvariantCulture)
+ + style.Substring(1, style.Length - 1);
+ try {
+ return (ExcelBorderStyle)Enum.Parse(typeof(ExcelBorderStyle), sInStyle);
+ } catch {
+ return ExcelBorderStyle.None;
+ }
+ }
- private ExcelFillStyle GetPatternTypeEnum(string patternType) =>
- Enum.TryParse<ExcelFillStyle>(patternType, true, out var result) ? result : ExcelFillStyle.None;
+ private ExcelFillStyle GetPatternTypeEnum(string patternType) {
+ if (patternType == "") {
+ return ExcelFillStyle.None;
+ }
+ patternType =
+ patternType.Substring(0, 1).ToUpper(CultureInfo.InvariantCulture)
+ + patternType.Substring(1, patternType.Length - 1);
+ try {
+ return (ExcelFillStyle)Enum.Parse(typeof(ExcelFillStyle), patternType);
+ } catch {
+ return ExcelFillStyle.None;
+ }
+ }
private ExcelDxfColor GetColor(XmlHelperInstance helper, string path) {
ExcelDxfColor ret = new ExcelDxfColor(_styles);
ret.Theme = helper.GetXmlNodeIntNull(path + "/@theme");
ret.Index = helper.GetXmlNodeIntNull(path + "/@indexed");
- ret.Rgb = helper.GetXmlNodeString(path + "/@rgb");
+ string rgb = helper.GetXmlNodeString(path + "/@rgb");
+ if (rgb != "") {
+ ret.Color = Color.FromArgb(
+ int.Parse(rgb.Substring(0, 2), NumberStyles.AllowHexSpecifier),
+ int.Parse(rgb.Substring(2, 2), NumberStyles.AllowHexSpecifier),
+ int.Parse(rgb.Substring(4, 2), NumberStyles.AllowHexSpecifier),
+ int.Parse(rgb.Substring(6, 2), NumberStyles.AllowHexSpecifier));
+ }
ret.Auto = helper.GetXmlNodeBoolNullable(path + "/@auto");
ret.Tint = helper.GetXmlNodeDoubleNull(path + "/@tint");
return ret;
diff --git a/EPPlus/Style/ExcelBorder.cs b/EPPlus/Style/ExcelBorder.cs
index 7784e20..bf07432 100644
--- a/EPPlus/Style/ExcelBorder.cs
+++ b/EPPlus/Style/ExcelBorder.cs
@@ -30,44 +30,53 @@
* Jan Källman License changed GPL-->LGPL 2011-12-16
*******************************************************************************/
+using System.Drawing;
+
namespace OfficeOpenXml.Style;
/// <summary>
/// Cell Border style
/// </summary>
public sealed class Border : StyleBase {
- internal Border(ExcelStyles styles, int positionId, string address, int index)
- : base(styles, positionId, address) {
+ internal Border(
+ ExcelStyles styles,
+ XmlHelper.ChangedEventHandler changedEvent,
+ int positionId,
+ string address,
+ int index)
+ : base(styles, changedEvent, positionId, address) {
Index = index;
}
/// <summary>
/// Left border style
/// </summary>
- public ExcelBorderItem Left => new(_styles, _positionID, _address, eStyleClass.BorderLeft, this);
+ public ExcelBorderItem Left =>
+ new(_styles, _ChangedEvent, _positionID, _address, eStyleClass.BorderLeft, this);
/// <summary>
/// Right border style
/// </summary>
public ExcelBorderItem Right =>
- new(_styles, _positionID, _address, eStyleClass.BorderRight, this);
+ new(_styles, _ChangedEvent, _positionID, _address, eStyleClass.BorderRight, this);
/// <summary>
/// Top border style
/// </summary>
- public ExcelBorderItem Top => new(_styles, _positionID, _address, eStyleClass.BorderTop, this);
+ public ExcelBorderItem Top =>
+ new(_styles, _ChangedEvent, _positionID, _address, eStyleClass.BorderTop, this);
/// <summary>
/// Bottom border style
/// </summary>
public ExcelBorderItem Bottom =>
- new(_styles, _positionID, _address, eStyleClass.BorderBottom, this);
+ new(_styles, _ChangedEvent, _positionID, _address, eStyleClass.BorderBottom, this);
/// <summary>
/// 0Diagonal border style
/// </summary>
public ExcelBorderItem Diagonal =>
- new(_styles, _positionID, _address, eStyleClass.BorderDiagonal, this);
+ new(_styles, _ChangedEvent, _positionID, _address, eStyleClass.BorderDiagonal, this);
/// <summary>
/// A diagonal from the bottom left to top right of the cell
@@ -79,6 +88,10 @@
}
return false;
}
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Border, eStyleProperty.BorderDiagonalUp, value, _positionID, _address));
}
/// <summary>
@@ -91,8 +104,99 @@
}
return false;
}
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Border, eStyleProperty.BorderDiagonalDown, value, _positionID, _address));
}
internal override string Id =>
Top.Id + Bottom.Id + Left.Id + Right.Id + Diagonal.Id + DiagonalUp + DiagonalDown;
+
+ /// <summary>
+ /// Set the border style around the range.
+ /// </summary>
+ /// <param name="style">The border style</param>
+ public void BorderAround(ExcelBorderStyle style) {
+ var addr = new ExcelAddress(_address);
+ SetBorderAroundStyle(style, addr);
+ }
+
+ /// <summary>
+ /// Set the border style around the range.
+ /// </summary>
+ /// <param name="style">The border style</param>
+ /// <param name="color">The color of the border</param>
+ public void BorderAround(ExcelBorderStyle style, Color color) {
+ var addr = new ExcelAddress(_address);
+ SetBorderAroundStyle(style, addr);
+
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.BorderTop,
+ eStyleProperty.Color,
+ color.ToArgb().ToString("X"),
+ _positionID,
+ new ExcelAddress(addr._fromRow, addr._fromCol, addr._fromRow, addr._toCol).Address));
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.BorderBottom,
+ eStyleProperty.Color,
+ color.ToArgb().ToString("X"),
+ _positionID,
+ new ExcelAddress(addr._toRow, addr._fromCol, addr._toRow, addr._toCol).Address));
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.BorderLeft,
+ eStyleProperty.Color,
+ color.ToArgb().ToString("X"),
+ _positionID,
+ new ExcelAddress(addr._fromRow, addr._fromCol, addr._toRow, addr._fromCol).Address));
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.BorderRight,
+ eStyleProperty.Color,
+ color.ToArgb().ToString("X"),
+ _positionID,
+ new ExcelAddress(addr._fromRow, addr._toCol, addr._toRow, addr._toCol).Address));
+ }
+
+ private void SetBorderAroundStyle(ExcelBorderStyle style, ExcelAddress addr) {
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.BorderTop,
+ eStyleProperty.Style,
+ style,
+ _positionID,
+ new ExcelAddress(addr._fromRow, addr._fromCol, addr._fromRow, addr._toCol).Address));
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.BorderBottom,
+ eStyleProperty.Style,
+ style,
+ _positionID,
+ new ExcelAddress(addr._toRow, addr._fromCol, addr._toRow, addr._toCol).Address));
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.BorderLeft,
+ eStyleProperty.Style,
+ style,
+ _positionID,
+ new ExcelAddress(addr._fromRow, addr._fromCol, addr._toRow, addr._fromCol).Address));
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.BorderRight,
+ eStyleProperty.Style,
+ style,
+ _positionID,
+ new ExcelAddress(addr._fromRow, addr._toCol, addr._toRow, addr._toCol).Address));
+ }
}
diff --git a/EPPlus/Style/ExcelBorderItem.cs b/EPPlus/Style/ExcelBorderItem.cs
index c8922d2..7708ab9 100644
--- a/EPPlus/Style/ExcelBorderItem.cs
+++ b/EPPlus/Style/ExcelBorderItem.cs
@@ -44,11 +44,12 @@
internal ExcelBorderItem(
ExcelStyles styles,
+ XmlHelper.ChangedEventHandler changedEvent,
int worksheetId,
string address,
eStyleClass cls,
StyleBase parent)
- : base(styles, worksheetId, address) {
+ : base(styles, changedEvent, worksheetId, address) {
_cls = cls;
_parent = parent;
}
@@ -56,7 +57,10 @@
/// <summary>
/// The line style of the border
/// </summary>
- public ExcelBorderStyle Style => GetSource().Style;
+ public ExcelBorderStyle Style {
+ get => GetSource().Style;
+ set => _ChangedEvent(this, new(_cls, eStyleProperty.Style, value, _positionID, _address));
+ }
private ExcelColor _color;
@@ -66,7 +70,7 @@
public ExcelColor Color {
get {
if (_color == null) {
- _color = new(_styles, _positionID, _address, _cls, _parent);
+ _color = new(_styles, _ChangedEvent, _positionID, _address, _cls, _parent);
}
return _color;
}
diff --git a/EPPlus/Style/ExcelColor.cs b/EPPlus/Style/ExcelColor.cs
index 2050f50..e673510 100644
--- a/EPPlus/Style/ExcelColor.cs
+++ b/EPPlus/Style/ExcelColor.cs
@@ -31,6 +31,7 @@
*******************************************************************************/
using System;
+using System.Drawing;
using OfficeOpenXml.Style.XmlAccess;
namespace OfficeOpenXml.Style;
@@ -44,11 +45,12 @@
internal ExcelColor(
ExcelStyles styles,
+ XmlHelper.ChangedEventHandler changedEvent,
int worksheetId,
string address,
eStyleClass cls,
StyleBase parent)
- : base(styles, worksheetId, address) {
+ : base(styles, changedEvent, worksheetId, address) {
_parent = parent;
_cls = cls;
}
@@ -61,17 +63,41 @@
/// <summary>
/// The tint value
/// </summary>
- public decimal Tint => GetSource().Tint;
+ public decimal Tint {
+ get => GetSource().Tint;
+ set {
+ if (value > 1 || value < -1) {
+ throw (new ArgumentOutOfRangeException("Value must be between -1 and 1"));
+ }
+ _ChangedEvent(this, new(_cls, eStyleProperty.Tint, value, _positionID, _address));
+ }
+ }
/// <summary>
/// The RGB value
/// </summary>
- public string Rgb => GetSource().Rgb;
+ public string Rgb {
+ get => GetSource().Rgb;
+ internal set =>
+ _ChangedEvent(this, new(_cls, eStyleProperty.Color, value, _positionID, _address));
+ }
/// <summary>
/// The indexed color number.
/// </summary>
- public int Indexed => GetSource().Indexed;
+ public int Indexed {
+ get => GetSource().Indexed;
+ set =>
+ _ChangedEvent(this, new(_cls, eStyleProperty.IndexedColor, value, _positionID, _address));
+ }
+
+ /// <summary>
+ /// Set the color of the object
+ /// </summary>
+ /// <param name="color">The color</param>
+ public void SetColor(Color color) {
+ Rgb = color.ToArgb().ToString("X");
+ }
internal override string Id => Theme + Tint + Rgb + Indexed;
diff --git a/EPPlus/Style/ExcelFill.cs b/EPPlus/Style/ExcelFill.cs
index e6cc1b2..d1a5ea6 100644
--- a/EPPlus/Style/ExcelFill.cs
+++ b/EPPlus/Style/ExcelFill.cs
@@ -36,8 +36,13 @@
/// The background fill of a cell
/// </summary>
public class ExcelFill : StyleBase {
- internal ExcelFill(ExcelStyles styles, int positionId, string address, int index)
- : base(styles, positionId, address) {
+ internal ExcelFill(
+ ExcelStyles styles,
+ XmlHelper.ChangedEventHandler changedEvent,
+ int positionId,
+ string address,
+ int index)
+ : base(styles, changedEvent, positionId, address) {
Index = index;
}
@@ -51,6 +56,14 @@
}
return _styles.Fills[Index].PatternType;
}
+ set {
+ if (_gradient != null) {
+ _gradient = null;
+ }
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Fill, eStyleProperty.PatternType, value, _positionID, _address));
+ }
}
private ExcelColor _patternColor;
@@ -61,7 +74,13 @@
public ExcelColor PatternColor {
get {
if (_patternColor == null) {
- _patternColor = new(_styles, _positionID, _address, eStyleClass.FillPatternColor, this);
+ _patternColor = new(
+ _styles,
+ _ChangedEvent,
+ _positionID,
+ _address,
+ eStyleClass.FillPatternColor,
+ this);
if (_gradient != null) {
_gradient = null;
}
@@ -80,6 +99,7 @@
if (_backgroundColor == null) {
_backgroundColor = new(
_styles,
+ _ChangedEvent,
_positionID,
_address,
eStyleClass.FillBackgroundColor,
@@ -100,7 +120,7 @@
public ExcelGradientFill Gradient {
get {
if (_gradient == null) {
- _gradient = new(_styles, _positionID, _address, Index);
+ _gradient = new(_styles, _ChangedEvent, _positionID, _address, Index);
_backgroundColor = null;
_patternColor = null;
}
diff --git a/EPPlus/Style/ExcelFont.cs b/EPPlus/Style/ExcelFont.cs
index 2cb6d5c..36beb83 100644
--- a/EPPlus/Style/ExcelFont.cs
+++ b/EPPlus/Style/ExcelFont.cs
@@ -31,6 +31,7 @@
*******************************************************************************/
using System;
+using System.Drawing;
namespace OfficeOpenXml.Style;
@@ -38,68 +39,146 @@
/// Cell style Font
/// </summary>
public sealed class ExcelFont : StyleBase {
- internal ExcelFont(ExcelStyles styles, int positionId, string address, int index)
- : base(styles, positionId, address) {
+ internal ExcelFont(
+ ExcelStyles styles,
+ XmlHelper.ChangedEventHandler changedEvent,
+ int positionId,
+ string address,
+ int index)
+ : base(styles, changedEvent, positionId, address) {
Index = index;
}
/// <summary>
/// The name of the font
/// </summary>
- public string Name => _styles.Fonts[Index].Name;
+ public string Name {
+ get => _styles.Fonts[Index].Name;
+ set =>
+ _ChangedEvent(this, new(eStyleClass.Font, eStyleProperty.Name, value, _positionID, _address));
+ }
/// <summary>
/// The Size of the font
/// </summary>
- public float Size => _styles.Fonts[Index].Size;
+ public float Size {
+ get => _styles.Fonts[Index].Size;
+ set =>
+ _ChangedEvent(this, new(eStyleClass.Font, eStyleProperty.Size, value, _positionID, _address));
+ }
/// <summary>
/// Font family
/// </summary>
- public int Family => _styles.Fonts[Index].Family;
+ public int Family {
+ get => _styles.Fonts[Index].Family;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Font, eStyleProperty.Family, value, _positionID, _address));
+ }
/// <summary>
/// Cell color
/// </summary>
- public ExcelColor Color => new(_styles, _positionID, _address, eStyleClass.Font, this);
+ public ExcelColor Color =>
+ new(_styles, _ChangedEvent, _positionID, _address, eStyleClass.Font, this);
/// <summary>
/// Scheme
/// </summary>
- public string Scheme => _styles.Fonts[Index].Scheme;
+ public string Scheme {
+ get => _styles.Fonts[Index].Scheme;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Font, eStyleProperty.Scheme, value, _positionID, _address));
+ }
/// <summary>
/// Font-bold
/// </summary>
- public bool Bold => _styles.Fonts[Index].Bold;
+ public bool Bold {
+ get => _styles.Fonts[Index].Bold;
+ set =>
+ _ChangedEvent(this, new(eStyleClass.Font, eStyleProperty.Bold, value, _positionID, _address));
+ }
/// <summary>
/// Font-italic
/// </summary>
- public bool Italic => _styles.Fonts[Index].Italic;
+ public bool Italic {
+ get => _styles.Fonts[Index].Italic;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Font, eStyleProperty.Italic, value, _positionID, _address));
+ }
/// <summary>
/// Font-Strikeout
/// </summary>
- public bool Strike => _styles.Fonts[Index].Strike;
+ public bool Strike {
+ get => _styles.Fonts[Index].Strike;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Font, eStyleProperty.Strike, value, _positionID, _address));
+ }
/// <summary>
/// Font-Underline
/// </summary>
- public bool UnderLine => _styles.Fonts[Index].UnderLine;
+ public bool UnderLine {
+ get => _styles.Fonts[Index].UnderLine;
+ set {
+ if (value) {
+ UnderLineType = ExcelUnderLineType.Single;
+ } else {
+ UnderLineType = ExcelUnderLineType.None;
+ }
+ //_ChangedEvent(this, new StyleChangeEventArgs(eStyleClass.Font, eStyleProperty.UnderlineType, value, _positionID, _address));
+ }
+ }
- public ExcelUnderLineType UnderLineType => _styles.Fonts[Index].UnderLineType;
+ public ExcelUnderLineType UnderLineType {
+ get => _styles.Fonts[Index].UnderLineType;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Font, eStyleProperty.UnderlineType, value, _positionID, _address));
+ }
/// <summary>
/// Font-Vertical Align
/// </summary>
- public ExcelVerticalAlignmentFont VerticalAlign =>
- Enum.TryParse<ExcelVerticalAlignmentFont>(
- _styles.Fonts[Index].VerticalAlign,
- true,
- out var result)
- ? result
- : ExcelVerticalAlignmentFont.None;
+ public ExcelVerticalAlignmentFont VerticalAlign {
+ get {
+ if (_styles.Fonts[Index].VerticalAlign == "") {
+ return ExcelVerticalAlignmentFont.None;
+ }
+ return (ExcelVerticalAlignmentFont)
+ Enum.Parse(typeof(ExcelVerticalAlignmentFont), _styles.Fonts[Index].VerticalAlign, true);
+ }
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Font, eStyleProperty.VerticalAlign, value, _positionID, _address));
+ }
+
+ /// <summary>
+ /// Set the font from a Font object
+ /// </summary>
+ /// <param name="font"></param>
+ public void SetFromFont(Font font) {
+ Name = font.Name;
+ //Family=fnt.FontFamily.;
+ Size = (int)font.Size;
+ Strike = font.Strikeout;
+ Bold = font.Bold;
+ UnderLine = font.Underline;
+ Italic = font.Italic;
+ }
internal override string Id =>
Name
diff --git a/EPPlus/Style/ExcelGradientFill.cs b/EPPlus/Style/ExcelGradientFill.cs
index 508a1f7..16e73c8 100644
--- a/EPPlus/Style/ExcelGradientFill.cs
+++ b/EPPlus/Style/ExcelGradientFill.cs
@@ -39,40 +39,112 @@
/// The background fill of a cell
/// </summary>
public class ExcelGradientFill : StyleBase {
- internal ExcelGradientFill(ExcelStyles styles, int positionId, string address, int index)
- : base(styles, positionId, address) {
+ internal ExcelGradientFill(
+ ExcelStyles styles,
+ XmlHelper.ChangedEventHandler changedEvent,
+ int positionId,
+ string address,
+ int index)
+ : base(styles, changedEvent, positionId, address) {
Index = index;
}
/// <summary>
/// Angle of the linear gradient
/// </summary>
- public double Degree => ((ExcelGradientFillXml)_styles.Fills[Index]).Degree;
+ public double Degree {
+ get => ((ExcelGradientFillXml)_styles.Fills[Index]).Degree;
+ set =>
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.GradientFill,
+ eStyleProperty.GradientDegree,
+ value,
+ _positionID,
+ _address));
+ }
/// <summary>
/// Linear or Path gradient
/// </summary>
- public ExcelFillGradientType Type => ((ExcelGradientFillXml)_styles.Fills[Index]).Type;
+ public ExcelFillGradientType Type {
+ get => ((ExcelGradientFillXml)_styles.Fills[Index]).Type;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.GradientFill, eStyleProperty.GradientType, value, _positionID, _address));
+ }
/// <summary>
/// Specifies in percentage format(from the top to the bottom) the position of the top edge of the inner rectangle (color 1). For top, 0 means the top edge of the inner rectangle is on the top edge of the cell, and 1 means it is on the bottom edge of the cell. (applies to From Corner and From Center gradients).
/// </summary>
- public double Top => ((ExcelGradientFillXml)_styles.Fills[Index]).Top;
+ public double Top {
+ get => ((ExcelGradientFillXml)_styles.Fills[Index]).Top;
+ set {
+ if (value < 0 | value > 1) {
+ throw (new ArgumentOutOfRangeException("Value must be between 0 and 1"));
+ }
+ _ChangedEvent(
+ this,
+ new(eStyleClass.GradientFill, eStyleProperty.GradientTop, value, _positionID, _address));
+ }
+ }
/// <summary>
/// Specifies in percentage format (from the top to the bottom) the position of the bottom edge of the inner rectangle (color 1). For bottom, 0 means the bottom edge of the inner rectangle is on the top edge of the cell, and 1 means it is on the bottom edge of the cell.
/// </summary>
- public double Bottom => ((ExcelGradientFillXml)_styles.Fills[Index]).Bottom;
+ public double Bottom {
+ get => ((ExcelGradientFillXml)_styles.Fills[Index]).Bottom;
+ set {
+ if (value < 0 | value > 1) {
+ throw (new ArgumentOutOfRangeException("Value must be between 0 and 1"));
+ }
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.GradientFill,
+ eStyleProperty.GradientBottom,
+ value,
+ _positionID,
+ _address));
+ }
+ }
/// <summary>
/// Specifies in percentage format (from the left to the right) the position of the left edge of the inner rectangle (color 1). For left, 0 means the left edge of the inner rectangle is on the left edge of the cell, and 1 means it is on the right edge of the cell. (applies to From Corner and From Center gradients).
/// </summary>
- public double Left => ((ExcelGradientFillXml)_styles.Fills[Index]).Left;
+ public double Left {
+ get => ((ExcelGradientFillXml)_styles.Fills[Index]).Left;
+ set {
+ if (value < 0 | value > 1) {
+ throw (new ArgumentOutOfRangeException("Value must be between 0 and 1"));
+ }
+ _ChangedEvent(
+ this,
+ new(eStyleClass.GradientFill, eStyleProperty.GradientLeft, value, _positionID, _address));
+ }
+ }
/// <summary>
/// Specifies in percentage format (from the left to the right) the position of the right edge of the inner rectangle (color 1). For right, 0 means the right edge of the inner rectangle is on the left edge of the cell, and 1 means it is on the right edge of the cell. (applies to From Corner and From Center gradients).
/// </summary>
- public double Right => ((ExcelGradientFillXml)_styles.Fills[Index]).Right;
+ public double Right {
+ get => ((ExcelGradientFillXml)_styles.Fills[Index]).Right;
+ set {
+ if (value < 0 | value > 1) {
+ throw (new ArgumentOutOfRangeException("Value must be between 0 and 1"));
+ }
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.GradientFill,
+ eStyleProperty.GradientRight,
+ value,
+ _positionID,
+ _address));
+ }
+ }
private ExcelColor _gradientColor1;
@@ -82,7 +154,13 @@
public ExcelColor Color1 {
get {
if (_gradientColor1 == null) {
- _gradientColor1 = new(_styles, _positionID, _address, eStyleClass.FillGradientColor1, this);
+ _gradientColor1 = new(
+ _styles,
+ _ChangedEvent,
+ _positionID,
+ _address,
+ eStyleClass.FillGradientColor1,
+ this);
}
return _gradientColor1;
}
@@ -96,7 +174,13 @@
public ExcelColor Color2 {
get {
if (_gradientColor2 == null) {
- _gradientColor2 = new(_styles, _positionID, _address, eStyleClass.FillGradientColor2, this);
+ _gradientColor2 = new(
+ _styles,
+ _ChangedEvent,
+ _positionID,
+ _address,
+ eStyleClass.FillGradientColor2,
+ this);
}
return _gradientColor2;
}
diff --git a/EPPlus/Style/ExcelNumberFormat.cs b/EPPlus/Style/ExcelNumberFormat.cs
index b971e7c..d1841df 100644
--- a/EPPlus/Style/ExcelNumberFormat.cs
+++ b/EPPlus/Style/ExcelNumberFormat.cs
@@ -36,8 +36,13 @@
/// The numberformat of the cell
/// </summary>
public sealed class ExcelNumberFormat : StyleBase {
- internal ExcelNumberFormat(ExcelStyles styles, int positionId, string address, int index)
- : base(styles, positionId, address) {
+ internal ExcelNumberFormat(
+ ExcelStyles styles,
+ XmlHelper.ChangedEventHandler changedEvent,
+ int positionId,
+ string address,
+ int index)
+ : base(styles, changedEvent, positionId, address) {
Index = index;
}
@@ -46,6 +51,10 @@
/// </summary>
public int NumFmtID => Index;
+ //set
+ //{
+ // _ChangedEvent(this, new StyleChangeEventArgs(eStyleClass.Numberformat, "NumFmtID", value, _workSheetID, _address));
+ //}
/// <summary>
/// The numberformat
/// </summary>
@@ -58,6 +67,15 @@
}
return "general";
}
+ set =>
+ _ChangedEvent(
+ this,
+ new(
+ eStyleClass.Numberformat,
+ eStyleProperty.Format,
+ (string.IsNullOrEmpty(value) ? "General" : value),
+ _positionID,
+ _address));
}
internal override string Id => Format;
diff --git a/EPPlus/Style/ExcelRichText.cs b/EPPlus/Style/ExcelRichText.cs
index 969dccc..2207656 100644
--- a/EPPlus/Style/ExcelRichText.cs
+++ b/EPPlus/Style/ExcelRichText.cs
@@ -33,6 +33,7 @@
*******************************************************************************/
using System;
+using System.Drawing;
using System.Globalization;
using System.Xml;
@@ -215,13 +216,33 @@
/// <summary>
/// Vertical Alignment
/// </summary>
- public ExcelVerticalAlignmentFont VerticalAlign =>
- Enum.TryParse<ExcelVerticalAlignmentFont>(
- GetXmlNodeString(TopNode, _vertAlignPath),
- true,
- out var result)
- ? result
- : ExcelVerticalAlignmentFont.None;
+ public ExcelVerticalAlignmentFont VerticalAlign {
+ get {
+ string v = GetXmlNodeString(_vertAlignPath);
+ if (v == "") {
+ return ExcelVerticalAlignmentFont.None;
+ }
+ try {
+ return (ExcelVerticalAlignmentFont)Enum.Parse(typeof(ExcelVerticalAlignmentFont), v, true);
+ } catch {
+ return ExcelVerticalAlignmentFont.None;
+ }
+ }
+ set {
+ _collection.ConvertRichtext();
+ if (value == ExcelVerticalAlignmentFont.None) {
+ // If Excel 2010 encounters a vertical align value of blank, it will not load
+ // the spreadsheet. So if None is specified, delete the node, it will be
+ // recreated if a new value is applied later.
+ DeleteNode(_vertAlignPath);
+ } else {
+ SetXmlNodeString(_vertAlignPath, value.ToString().ToLowerInvariant());
+ }
+ if (_callback != null) {
+ _callback();
+ }
+ }
+ }
private const string _sizePath = "d:rPr/d:sz/@val";
@@ -257,5 +278,30 @@
private const string _colorPath = "d:rPr/d:color/@rgb";
+ /// <summary>
+ /// Text color
+ /// </summary>
+ public Color Color {
+ get {
+ string col = GetXmlNodeString(_colorPath);
+ if (col == "") {
+ return Color.Empty;
+ }
+ return Color.FromArgb(int.Parse(col, NumberStyles.AllowHexSpecifier));
+ }
+ set {
+ _collection.ConvertRichtext();
+ SetXmlNodeString(
+ _colorPath,
+ value
+ .ToArgb()
+ .ToString(
+ "X") /*.Substring(2, 6)*/);
+ if (_callback != null) {
+ _callback();
+ }
+ }
+ }
+
public ExcelRichTextCollection _collection { get; set; }
}
diff --git a/EPPlus/Style/ExcelRichTextCollection.cs b/EPPlus/Style/ExcelRichTextCollection.cs
index 7cd4867..5e87c4b 100644
--- a/EPPlus/Style/ExcelRichTextCollection.cs
+++ b/EPPlus/Style/ExcelRichTextCollection.cs
@@ -32,6 +32,7 @@
using System.Collections;
using System.Collections.Generic;
+using System.Drawing;
using System.Globalization;
using System.Linq;
using System.Text;
@@ -81,6 +82,56 @@
/// </summary>
public int Count => _list.Count;
+ /// <summary>
+ /// Add a rich text string
+ /// </summary>
+ /// <param name="text">The text to add</param>
+ /// <returns></returns>
+ public ExcelRichText Add(string text) {
+ ConvertRichtext();
+ XmlDocument doc;
+ if (TopNode is XmlDocument document) {
+ doc = document;
+ } else {
+ doc = TopNode.OwnerDocument;
+ }
+ var node = doc.CreateElement("d", "r", ExcelPackage._schemaMain);
+ TopNode.AppendChild(node);
+ var rt = new ExcelRichText(NameSpaceManager, node, this);
+ if (_list.Count > 0) {
+ ExcelRichText prevItem = _list[_list.Count - 1];
+ rt.FontName = prevItem.FontName;
+ rt.Size = prevItem.Size;
+ if (prevItem.Color.IsEmpty) {
+ rt.Color = Color.Black;
+ } else {
+ rt.Color = prevItem.Color;
+ }
+ rt.PreserveSpace = rt.PreserveSpace;
+ rt.Bold = prevItem.Bold;
+ rt.Italic = prevItem.Italic;
+ rt.UnderLine = prevItem.UnderLine;
+ } else if (_cells == null) {
+ rt.FontName = "Calibri";
+ rt.Size = 11;
+ } else {
+ var style = _cells.Offset(0, 0).Style;
+ rt.FontName = style.Font.Name;
+ rt.Size = style.Font.Size;
+ rt.Bold = style.Font.Bold;
+ rt.Italic = style.Font.Italic;
+ _cells.IsRichText = true;
+ }
+ rt.Text = text;
+ rt.PreserveSpace = true;
+ if (_cells != null) {
+ rt.SetCallback(UpdateCells);
+ UpdateCells();
+ }
+ _list.Add(rt);
+ return rt;
+ }
+
internal void ConvertRichtext() {
if (_cells == null) {
return;
@@ -109,6 +160,11 @@
this[0].Italic = fnt.Italic;
this[0].Size = fnt.Size;
this[0].UnderLine = fnt.UnderLine;
+
+ if (fnt.Color.Rgb != ""
+ && int.TryParse(fnt.Color.Rgb, NumberStyles.HexNumber, null, out var hex)) {
+ this[0].Color = Color.FromArgb(hex);
+ }
}
}
@@ -117,6 +173,18 @@
}
/// <summary>
+ /// Clear the collection
+ /// </summary>
+ public void Clear() {
+ _list.Clear();
+ TopNode.RemoveAll();
+ UpdateCells();
+ if (_cells != null) {
+ _cells.IsRichText = false;
+ }
+ }
+
+ /// <summary>
/// Removes an item at the specific index
/// </summary>
/// <param name="index"></param>
@@ -156,6 +224,16 @@
}
return sb.ToString();
}
+ set {
+ if (Count == 0) {
+ Add(value);
+ } else {
+ this[0].Text = value;
+ for (int ix = 1; ix < Count; ix++) {
+ RemoveAt(ix);
+ }
+ }
+ }
}
IEnumerator<ExcelRichText> IEnumerable<ExcelRichText>.GetEnumerator() {
diff --git a/EPPlus/Style/ExcelRichTextHtmlUtility.cs b/EPPlus/Style/ExcelRichTextHtmlUtility.cs
new file mode 100644
index 0000000..83e37c6
--- /dev/null
+++ b/EPPlus/Style/ExcelRichTextHtmlUtility.cs
@@ -0,0 +1,187 @@
+/*******************************************************************************
+ * You may amend and distribute as you like, but don't remove this header!
+ *
+ * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
+ * See http://www.codeplex.com/EPPlus for details.
+ *
+ * Copyright (C) 2011 Jan Källman
+ *
+ * This library is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 2.1 of the License, or (at your option) any later version.
+
+ * This library is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
+ * See the GNU Lesser General Public License for more details.
+ *
+ * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
+ * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
+ *
+ * All code and executables are provided "as is" with no warranty either express or implied.
+ * The author accepts no liability for any damage or loss of business that this product may cause.
+ *
+ * Code change notes:
+ *
+ * Author Change Date
+ * ******************************************************************************
+ * Richard Tallent Initial Release 2012-08-13
+ *******************************************************************************/
+
+using System;
+using System.Text.RegularExpressions;
+using System.Web;
+
+namespace OfficeOpenXml.Style;
+
+public class ExcelRichTextHtmlUtility {
+ /// <summary>
+ /// Provides basic HTML support by converting well-behaved HTML into appropriate RichText blocks.
+ /// HTML support is limited, and does not include font colors, sizes, or typefaces at this time,
+ /// and also does not support CSS style attributes. It does support line breaks using the BR tag.
+ ///
+ /// This routine parses the HTML into RegEx pairings of an HTML tag and the text until the NEXT
+ /// tag (if any). The tag is parsed to determine the setting change to be applied to the last set
+ /// of settings, and if the text is not blank, a new block is added to rich text.
+ /// </summary>
+ /// <param name="range"></param>
+ /// <param name="html">The HTML to parse into RichText</param>
+ /// <param name="defaultFontName"></param>
+ /// <param name="defaultFontSize"></param>
+ public static void SetRichTextFromHtml(
+ ExcelRange range,
+ string html,
+ string defaultFontName,
+ short defaultFontSize) {
+ // Reset the cell value, just in case there is an existing RichText value.
+ range.Value = "";
+
+ // Sanity check for blank values, skips creating Regex objects for performance.
+ if (String.IsNullOrEmpty(html)) {
+ range.IsRichText = false;
+ return;
+ }
+
+ // Change all BR tags to line breaks. http://epplus.codeplex.com/discussions/238692/
+ // Cells with line breaks aren't necessarily considered rich text, so this is performed
+ // before parsing the HTML tags.
+ html = Regex.Replace(
+ html,
+ "<br[^>]*>",
+ "\r\n",
+ RegexOptions.Compiled | RegexOptions.IgnoreCase);
+
+ string tag;
+ string text;
+ ExcelRichText thisrt = null;
+ bool isFirst = true;
+
+ // Get all pairs of legitimate tags and the text between them. This loop will
+ // only execute if there is at least one start or end tag.
+ foreach (Match m in Regex.Matches(
+ html,
+ @"<(/?[a-z]+)[^<>]*>([\s\S]*?)(?=</?[a-z]+[^<>]*>|$)",
+ RegexOptions.Compiled | RegexOptions.IgnoreCase)) {
+ if (isFirst) {
+ // On the very first match, set up the initial rich text object with
+ // the defaults for the text BEFORE the match.
+ range.IsRichText = true;
+ thisrt = range.RichText.Add(CleanText(html.Substring(0, m.Index))); // May be 0-length
+ thisrt.Size = defaultFontSize; // Set the default font size
+ thisrt.FontName = defaultFontName; // Set the default font name
+ isFirst = false;
+ }
+ // Get the tag and the block of text until the NEXT tag or EOS. If there are HTML entities
+ // encoded, unencode them, they should be passed to RichText as normal characters (other
+ // than non-breaking spaces, which should be replaced with normal spaces, they break Excel.
+ tag = m.Groups[1].Captures[0].Value;
+ text = CleanText(m.Groups[2].Captures[0].Value);
+
+ if (thisrt.Text == "") {
+ // The most recent rich text block wasn't *actually* used last time around, so update
+ // the text and keep it as the "current" block. This happens with the first block if
+ // it starts with a tag, and may happen later if tags come one right after the other.
+ thisrt.Text = text;
+ } else {
+ // The current rich text block has some text, so create a new one. RichText.Add()
+ // automatically applies the settings from the previous block, other than vertical
+ // alignment.
+ thisrt = range.RichText.Add(text);
+ }
+ // Override the settings based on the current tag, keep all other settings.
+ SetStyleFromTag(tag, thisrt);
+ }
+
+ if (thisrt == null) {
+ // No HTML tags were found, so treat this as a normal text value.
+ range.IsRichText = false;
+ range.Value = CleanText(html);
+ } else if (String.IsNullOrEmpty(thisrt.Text)) {
+ // Rich text was found, but the last node contains no text, so remove it. This can happen if,
+ // say, the end of the string is an end tag or unsupported tag (common).
+ range.RichText.Remove(thisrt);
+
+ // Failsafe -- the HTML may be just tags, no text, in which case there may be no rich text
+ // directives that remain. If that is the case, turn off rich text and treat this like a blank
+ // cell value.
+ if (range.RichText.Count == 0) {
+ range.IsRichText = false;
+ range.Value = "";
+ }
+ }
+ }
+
+ private static void SetStyleFromTag(string tag, ExcelRichText settings) {
+ switch (tag.ToLower()) {
+ case "b":
+ case "strong":
+ settings.Bold = true;
+ break;
+ case "i":
+ case "em":
+ settings.Italic = true;
+ break;
+ case "u":
+ settings.UnderLine = true;
+ break;
+ case "s":
+ case "strike":
+ settings.Strike = true;
+ break;
+ case "sup":
+ settings.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;
+ break;
+ case "sub":
+ settings.VerticalAlign = ExcelVerticalAlignmentFont.Subscript;
+ break;
+ case "/b":
+ case "/strong":
+ settings.Bold = false;
+ break;
+ case "/i":
+ case "/em":
+ settings.Italic = false;
+ break;
+ case "/u":
+ settings.UnderLine = false;
+ break;
+ case "/s":
+ case "/strike":
+ settings.Strike = false;
+ break;
+ case "/sup":
+ case "/sub":
+ settings.VerticalAlign = ExcelVerticalAlignmentFont.None;
+ break;
+ }
+ }
+
+ private static string CleanText(string s) {
+ // Need to convert HTML entities (named or numbered) into actual Unicode characters
+ s = HttpUtility.HtmlDecode(s);
+ // Remove any non-breaking spaces, kills Excel
+ s = s.Replace("\u00A0", " ");
+ return s;
+ }
+}
diff --git a/EPPlus/Style/ExcelStyle.cs b/EPPlus/Style/ExcelStyle.cs
index f7b60f9..9c2509a 100644
--- a/EPPlus/Style/ExcelStyle.cs
+++ b/EPPlus/Style/ExcelStyle.cs
@@ -39,8 +39,13 @@
/// Toplevel class for cell styling
/// </summary>
public sealed class ExcelStyle : StyleBase {
- internal ExcelStyle(ExcelStyles styles, int positionId, string address, int xfsId)
- : base(styles, positionId, address) {
+ internal ExcelStyle(
+ ExcelStyles styles,
+ XmlHelper.ChangedEventHandler changedEvent,
+ int positionId,
+ string address,
+ int xfsId)
+ : base(styles, changedEvent, positionId, address) {
Index = xfsId;
ExcelXfs xfs;
if (positionId > -1) {
@@ -50,10 +55,10 @@
}
Styles = styles;
PositionID = positionId;
- Numberformat = new(styles, PositionID, address, xfs.NumberFormatId);
- Font = new(styles, PositionID, address, xfs.FontId);
- Fill = new(styles, PositionID, address, xfs.FillId);
- Border = new(styles, PositionID, address, xfs.BorderId);
+ Numberformat = new(styles, changedEvent, PositionID, address, xfs.NumberFormatId);
+ Font = new(styles, changedEvent, PositionID, address, xfs.FontId);
+ Fill = new(styles, changedEvent, PositionID, address, xfs.FillId);
+ Border = new(styles, changedEvent, PositionID, address, xfs.BorderId);
}
/// <summary>
@@ -79,54 +84,122 @@
/// <summary>
/// The horizontal alignment in the cell
/// </summary>
- public ExcelHorizontalAlignment HorizontalAlignment => _styles.CellXfs[Index].HorizontalAlignment;
+ public ExcelHorizontalAlignment HorizontalAlignment {
+ get => _styles.CellXfs[Index].HorizontalAlignment;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Style, eStyleProperty.HorizontalAlign, value, _positionID, _address));
+ }
/// <summary>
/// The vertical alignment in the cell
/// </summary>
- public ExcelVerticalAlignment VerticalAlignment => _styles.CellXfs[Index].VerticalAlignment;
+ public ExcelVerticalAlignment VerticalAlignment {
+ get => _styles.CellXfs[Index].VerticalAlignment;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Style, eStyleProperty.VerticalAlign, value, _positionID, _address));
+ }
/// <summary>
/// Wrap the text
/// </summary>
- public bool WrapText => _styles.CellXfs[Index].WrapText;
+ public bool WrapText {
+ get => _styles.CellXfs[Index].WrapText;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Style, eStyleProperty.WrapText, value, _positionID, _address));
+ }
/// <summary>
/// Readingorder
/// </summary>
- public ExcelReadingOrder ReadingOrder => _styles.CellXfs[Index].ReadingOrder;
+ public ExcelReadingOrder ReadingOrder {
+ get => _styles.CellXfs[Index].ReadingOrder;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Style, eStyleProperty.ReadingOrder, value, _positionID, _address));
+ }
/// <summary>
/// Shrink the text to fit
/// </summary>
- public bool ShrinkToFit => _styles.CellXfs[Index].ShrinkToFit;
+ public bool ShrinkToFit {
+ get => _styles.CellXfs[Index].ShrinkToFit;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Style, eStyleProperty.ShrinkToFit, value, _positionID, _address));
+ }
/// <summary>
/// The margin between the border and the text
/// </summary>
- public int Indent => _styles.CellXfs[Index].Indent;
+ public int Indent {
+ get => _styles.CellXfs[Index].Indent;
+ set {
+ if (value < 0 || value > 250) {
+ throw (new ArgumentOutOfRangeException("Indent must be between 0 and 250"));
+ }
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Style, eStyleProperty.Indent, value, _positionID, _address));
+ }
+ }
/// <summary>
/// Text orientation in degrees. Values range from 0 to 180.
/// </summary>
- public int TextRotation => _styles.CellXfs[Index].TextRotation;
+ public int TextRotation {
+ get => _styles.CellXfs[Index].TextRotation;
+ set {
+ if (value < 0 || value > 180) {
+ throw new ArgumentOutOfRangeException("TextRotation out of range.");
+ }
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Style, eStyleProperty.TextRotation, value, _positionID, _address));
+ }
+ }
/// <summary>
/// If true the cell is locked for editing when the sheet is protected
/// <seealso cref="ExcelWorksheet.Protection"/>
/// </summary>
- public bool Locked => _styles.CellXfs[Index].Locked;
+ public bool Locked {
+ get => _styles.CellXfs[Index].Locked;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Style, eStyleProperty.Locked, value, _positionID, _address));
+ }
/// <summary>
/// If true the formula is hidden when the sheet is protected.
/// <seealso cref="ExcelWorksheet.Protection"/>
/// </summary>
- public bool Hidden => _styles.CellXfs[Index].Hidden;
+ public bool Hidden {
+ get => _styles.CellXfs[Index].Hidden;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Style, eStyleProperty.Hidden, value, _positionID, _address));
+ }
/// <summary>
/// The index in the style collection
/// </summary>
- public int XfId => _styles.CellXfs[Index].XfId;
+ public int XfId {
+ get => _styles.CellXfs[Index].XfId;
+ set =>
+ _ChangedEvent(
+ this,
+ new(eStyleClass.Style, eStyleProperty.XfId, value, _positionID, _address));
+ }
internal int PositionID { get; set; }
diff --git a/EPPlus/Style/ExcelTextFont.cs b/EPPlus/Style/ExcelTextFont.cs
index 9252413..f098072 100644
--- a/EPPlus/Style/ExcelTextFont.cs
+++ b/EPPlus/Style/ExcelTextFont.cs
@@ -31,6 +31,7 @@
*******************************************************************************/
using System;
+using System.Drawing;
using System.Globalization;
using System.Xml;
@@ -140,6 +141,22 @@
}
}
+ private readonly string _underLineColorPath = "a:uFill/a:solidFill/a:srgbClr/@val";
+
+ public Color UnderLineColor {
+ get {
+ string col = GetXmlNodeString(_underLineColorPath);
+ if (col == "") {
+ return Color.Empty;
+ }
+ return Color.FromArgb(int.Parse(col, NumberStyles.AllowHexSpecifier));
+ }
+ set {
+ CreateTopNode();
+ SetXmlNodeString(_underLineColorPath, value.ToArgb().ToString("X").Substring(2, 6));
+ }
+ }
+
private readonly string _italicPath = "@i";
public bool Italic {
@@ -170,6 +187,22 @@
}
}
+ private readonly string _colorPath = "a:solidFill/a:srgbClr/@val";
+
+ public Color Color {
+ get {
+ string col = GetXmlNodeString(_colorPath);
+ if (col == "") {
+ return Color.Empty;
+ }
+ return Color.FromArgb(int.Parse(col, NumberStyles.AllowHexSpecifier));
+ }
+ set {
+ CreateTopNode();
+ SetXmlNodeString(_colorPath, value.ToArgb().ToString("X").Substring(2, 6));
+ }
+ }
+
private eUnderLineType TranslateUnderline(string text) {
switch (text) {
case "sng":
@@ -179,9 +212,7 @@
case "":
return eUnderLineType.None;
default:
- return Enum.TryParse<eUnderLineType>(text, true, out var result)
- ? result
- : eUnderLineType.None;
+ return (eUnderLineType)Enum.Parse(typeof(eUnderLineType), text);
}
}
@@ -219,4 +250,26 @@
return "noStrike";
}
}
+
+ /// <summary>
+ /// Set the font style from a font object
+ /// </summary>
+ /// <param name="font"></param>
+ public void SetFromFont(Font font) {
+ LatinFont = font.Name;
+ ComplexFont = font.Name;
+ Size = font.Size;
+ if (font.Bold) {
+ Bold = font.Bold;
+ }
+ if (font.Italic) {
+ Italic = font.Italic;
+ }
+ if (font.Underline) {
+ UnderLine = eUnderLineType.Single;
+ }
+ if (font.Strikeout) {
+ Strike = eStrikeType.Single;
+ }
+ }
}
diff --git a/EPPlus/Style/StyleBase.cs b/EPPlus/Style/StyleBase.cs
index ec560a5..fa1b390 100644
--- a/EPPlus/Style/StyleBase.cs
+++ b/EPPlus/Style/StyleBase.cs
@@ -1,4 +1,4 @@
-/*******************************************************************************
+/*******************************************************************************
* You may amend and distribute as you like, but don't remove this header!
*
* EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
@@ -163,11 +163,17 @@
public abstract class StyleBase {
protected ExcelStyles _styles;
+ internal XmlHelper.ChangedEventHandler _ChangedEvent;
protected int _positionID;
protected string _address;
- internal StyleBase(ExcelStyles styles, int positionId, string address) {
+ internal StyleBase(
+ ExcelStyles styles,
+ XmlHelper.ChangedEventHandler changedEvent,
+ int positionId,
+ string address) {
_styles = styles;
+ _ChangedEvent = changedEvent;
_address = address;
_positionID = positionId;
}
diff --git a/EPPlus/Style/XmlAccess/ExcelBorderItemXml.cs b/EPPlus/Style/XmlAccess/ExcelBorderItemXml.cs
index 9c0de5e..7e94481 100644
--- a/EPPlus/Style/XmlAccess/ExcelBorderItemXml.cs
+++ b/EPPlus/Style/XmlAccess/ExcelBorderItemXml.cs
@@ -61,9 +61,14 @@
if (style == "") {
return ExcelBorderStyle.None;
}
- return Enum.TryParse<ExcelBorderStyle>(style, true, out var result)
- ? result
- : ExcelBorderStyle.None;
+ string sInStyle =
+ style.Substring(0, 1).ToUpper(CultureInfo.InvariantCulture)
+ + style.Substring(1, style.Length - 1);
+ try {
+ return (ExcelBorderStyle)Enum.Parse(typeof(ExcelBorderStyle), sInStyle);
+ } catch {
+ return ExcelBorderStyle.None;
+ }
}
private ExcelBorderStyle _borderStyle = ExcelBorderStyle.None;
diff --git a/EPPlus/Style/XmlAccess/ExcelColorXml.cs b/EPPlus/Style/XmlAccess/ExcelColorXml.cs
index 7a6d024..ba1fd11 100644
--- a/EPPlus/Style/XmlAccess/ExcelColorXml.cs
+++ b/EPPlus/Style/XmlAccess/ExcelColorXml.cs
@@ -31,6 +31,7 @@
*******************************************************************************/
using System;
+using System.Drawing;
using System.Globalization;
using System.Xml;
@@ -141,6 +142,11 @@
_auto = false;
}
+ public void SetColor(Color color) {
+ Clear();
+ _rgb = color.ToArgb().ToString("X");
+ }
+
internal ExcelColorXml Copy() {
return new(NameSpaceManager) {
_indexed = _indexed,
diff --git a/EPPlus/Style/XmlAccess/ExcelFillXml.cs b/EPPlus/Style/XmlAccess/ExcelFillXml.cs
index cf43d0e..98b7c9d 100644
--- a/EPPlus/Style/XmlAccess/ExcelFillXml.cs
+++ b/EPPlus/Style/XmlAccess/ExcelFillXml.cs
@@ -55,9 +55,17 @@
}
private ExcelFillStyle GetPatternType(string patternType) {
- return Enum.TryParse<ExcelFillStyle>(patternType, true, out var result)
- ? result
- : ExcelFillStyle.None;
+ if (patternType == "") {
+ return ExcelFillStyle.None;
+ }
+ patternType =
+ patternType.Substring(0, 1).ToUpper(CultureInfo.InvariantCulture)
+ + patternType.Substring(1, patternType.Length - 1);
+ try {
+ return (ExcelFillStyle)Enum.Parse(typeof(ExcelFillStyle), patternType);
+ } catch {
+ return ExcelFillStyle.None;
+ }
}
internal override string Id => PatternType + PatternColor.Id + BackgroundColor.Id;
diff --git a/EPPlus/Style/XmlAccess/ExcelFontXml.cs b/EPPlus/Style/XmlAccess/ExcelFontXml.cs
index 2d90c95..88ab554 100644
--- a/EPPlus/Style/XmlAccess/ExcelFontXml.cs
+++ b/EPPlus/Style/XmlAccess/ExcelFontXml.cs
@@ -31,6 +31,7 @@
*******************************************************************************/
using System;
+using System.Drawing;
using System.Globalization;
using System.Xml;
@@ -70,9 +71,7 @@
if (ut == "") {
_underlineType = ExcelUnderLineType.Single;
} else {
- if (!Enum.TryParse(ut, true, out _underlineType)) {
- _underlineType = ExcelUnderLineType.Single;
- }
+ _underlineType = (ExcelUnderLineType)Enum.Parse(typeof(ExcelUnderLineType), ut, true);
}
} else {
_underlineType = ExcelUnderLineType.None;
@@ -223,6 +222,16 @@
set => _verticalAlign = value;
}
+ public void SetFromFont(Font font) {
+ Name = font.Name;
+ //Family=fnt.FontFamily.;
+ Size = (int)font.Size;
+ Strike = font.Strikeout;
+ Bold = font.Bold;
+ UnderLine = font.Underline;
+ Italic = font.Italic;
+ }
+
internal ExcelFontXml Copy() {
ExcelFontXml newFont = new ExcelFontXml(NameSpaceManager);
newFont.Name = _name;
diff --git a/EPPlus/Style/XmlAccess/ExcelNamedStyleXml.cs b/EPPlus/Style/XmlAccess/ExcelNamedStyleXml.cs
index 356e90f..8004056 100644
--- a/EPPlus/Style/XmlAccess/ExcelNamedStyleXml.cs
+++ b/EPPlus/Style/XmlAccess/ExcelNamedStyleXml.cs
@@ -57,7 +57,7 @@
CustomBuildin = GetXmlNodeBool(_customBuiltinPath);
_styles = styles;
- _style = new(styles, -1, Name, _styleXfId);
+ _style = new(styles, styles.NamedStylePropertyChange, -1, Name, _styleXfId);
}
internal override string Id => Name;
diff --git a/EPPlus/Style/XmlAccess/ExcelXfsXml.cs b/EPPlus/Style/XmlAccess/ExcelXfsXml.cs
index 9761ea1..d00ed0e 100644
--- a/EPPlus/Style/XmlAccess/ExcelXfsXml.cs
+++ b/EPPlus/Style/XmlAccess/ExcelXfsXml.cs
@@ -31,6 +31,7 @@
*******************************************************************************/
using System;
+using System.Drawing;
using System.Globalization;
using System.Xml;
@@ -81,15 +82,45 @@
}
}
- private ExcelHorizontalAlignment GetHorizontalAlign(string align) =>
- Enum.TryParse<ExcelHorizontalAlignment>(align, true, out var result)
- ? result
- : ExcelHorizontalAlignment.General;
+ private ExcelHorizontalAlignment GetHorizontalAlign(string align) {
+ if (align == "") {
+ return ExcelHorizontalAlignment.General;
+ }
+ align =
+ align.Substring(0, 1).ToUpper(CultureInfo.InvariantCulture)
+ + align.Substring(1, align.Length - 1);
+ try {
+ return (ExcelHorizontalAlignment)Enum.Parse(typeof(ExcelHorizontalAlignment), align);
+ } catch {
+ return ExcelHorizontalAlignment.General;
+ }
+ }
- private ExcelVerticalAlignment GetVerticalAlign(string align) =>
- Enum.TryParse<ExcelVerticalAlignment>(align, true, out var result)
- ? result
- : ExcelVerticalAlignment.Bottom;
+ private ExcelVerticalAlignment GetVerticalAlign(string align) {
+ if (align == "") {
+ return ExcelVerticalAlignment.Bottom;
+ }
+ align =
+ align.Substring(0, 1).ToUpper(CultureInfo.InvariantCulture)
+ + align.Substring(1, align.Length - 1);
+ try {
+ return (ExcelVerticalAlignment)Enum.Parse(typeof(ExcelVerticalAlignment), align);
+ } catch {
+ return ExcelVerticalAlignment.Bottom;
+ }
+ }
+
+ internal void Xf_ChangedEvent(object sender, EventArgs e) {
+ //if (_cell != null)
+ //{
+ // if (!Styles.ChangedCells.ContainsKey(_cell.Id))
+ // {
+ // //_cell.Style = "";
+ // _cell.SetNewStyleID(int.MinValue.ToString());
+ // Styles.ChangedCells.Add(_cell.Id, _cell);
+ // }
+ //}
+ }
private int _xfID;
@@ -320,6 +351,327 @@
return newXf;
}
+ internal int GetNewId(
+ ExcelStyleCollection<ExcelXfs> xfsCol,
+ StyleBase styleObject,
+ eStyleClass styleClass,
+ eStyleProperty styleProperty,
+ object value) {
+ ExcelXfs newXfs = Copy();
+ switch (styleClass) {
+ case eStyleClass.Numberformat:
+ newXfs.NumberFormatId = GetIdNumberFormat(styleProperty, value);
+ styleObject.SetIndex(newXfs.NumberFormatId);
+ break;
+ case eStyleClass.Font: {
+ newXfs.FontId = GetIdFont(styleProperty, value);
+ styleObject.SetIndex(newXfs.FontId);
+ break;
+ }
+ case eStyleClass.Fill:
+ case eStyleClass.FillBackgroundColor:
+ case eStyleClass.FillPatternColor:
+ newXfs.FillId = GetIdFill(styleClass, styleProperty, value);
+ styleObject.SetIndex(newXfs.FillId);
+ break;
+ case eStyleClass.GradientFill:
+ case eStyleClass.FillGradientColor1:
+ case eStyleClass.FillGradientColor2:
+ newXfs.FillId = GetIdGradientFill(styleClass, styleProperty, value);
+ styleObject.SetIndex(newXfs.FillId);
+ break;
+ case eStyleClass.Border:
+ case eStyleClass.BorderBottom:
+ case eStyleClass.BorderDiagonal:
+ case eStyleClass.BorderLeft:
+ case eStyleClass.BorderRight:
+ case eStyleClass.BorderTop:
+ newXfs.BorderId = GetIdBorder(styleClass, styleProperty, value);
+ styleObject.SetIndex(newXfs.BorderId);
+ break;
+ case eStyleClass.Style:
+ switch (styleProperty) {
+ case eStyleProperty.XfId:
+ newXfs.XfId = (int)value;
+ break;
+ case eStyleProperty.HorizontalAlign:
+ newXfs.HorizontalAlignment = (ExcelHorizontalAlignment)value;
+ break;
+ case eStyleProperty.VerticalAlign:
+ newXfs.VerticalAlignment = (ExcelVerticalAlignment)value;
+ break;
+ case eStyleProperty.WrapText:
+ newXfs.WrapText = (bool)value;
+ break;
+ case eStyleProperty.ReadingOrder:
+ newXfs.ReadingOrder = (ExcelReadingOrder)value;
+ break;
+ case eStyleProperty.ShrinkToFit:
+ newXfs.ShrinkToFit = (bool)value;
+ break;
+ case eStyleProperty.Indent:
+ newXfs.Indent = (int)value;
+ break;
+ case eStyleProperty.TextRotation:
+ newXfs.TextRotation = (int)value;
+ break;
+ case eStyleProperty.Locked:
+ newXfs.Locked = (bool)value;
+ break;
+ case eStyleProperty.Hidden:
+ newXfs.Hidden = (bool)value;
+ break;
+ default:
+ throw (new("Invalid property for class style."));
+ }
+ break;
+ }
+ int id = xfsCol.FindIndexById(newXfs.Id);
+ if (id < 0) {
+ return xfsCol.Add(newXfs.Id, newXfs);
+ }
+ return id;
+ }
+
+ private int GetIdBorder(eStyleClass styleClass, eStyleProperty styleProperty, object value) {
+ ExcelBorderXml border = Border.Copy();
+
+ switch (styleClass) {
+ case eStyleClass.BorderBottom:
+ SetBorderItem(border.Bottom, styleProperty, value);
+ break;
+ case eStyleClass.BorderDiagonal:
+ SetBorderItem(border.Diagonal, styleProperty, value);
+ break;
+ case eStyleClass.BorderLeft:
+ SetBorderItem(border.Left, styleProperty, value);
+ break;
+ case eStyleClass.BorderRight:
+ SetBorderItem(border.Right, styleProperty, value);
+ break;
+ case eStyleClass.BorderTop:
+ SetBorderItem(border.Top, styleProperty, value);
+ break;
+ case eStyleClass.Border:
+ if (styleProperty == eStyleProperty.BorderDiagonalUp) {
+ border.DiagonalUp = (bool)value;
+ } else if (styleProperty == eStyleProperty.BorderDiagonalDown) {
+ border.DiagonalDown = (bool)value;
+ } else {
+ throw (new("Invalid property for class Border."));
+ }
+ break;
+ default:
+ throw (new("Invalid class/property for class Border."));
+ }
+ int subId;
+ string id = border.Id;
+ subId = _styles.Borders.FindIndexById(id);
+ if (subId == int.MinValue) {
+ return _styles.Borders.Add(id, border);
+ }
+ return subId;
+ }
+
+ private void SetBorderItem(
+ ExcelBorderItemXml excelBorderItem,
+ eStyleProperty styleProperty,
+ object value) {
+ if (styleProperty == eStyleProperty.Style) {
+ excelBorderItem.Style = (ExcelBorderStyle)value;
+ } else if (styleProperty == eStyleProperty.Color
+ || styleProperty == eStyleProperty.Tint
+ || styleProperty == eStyleProperty.IndexedColor) {
+ if (excelBorderItem.Style == ExcelBorderStyle.None) {
+ throw (new("Can't set bordercolor when style is not set."));
+ }
+ excelBorderItem.Color.Rgb = value.ToString();
+ }
+ }
+
+ private int GetIdFill(eStyleClass styleClass, eStyleProperty styleProperty, object value) {
+ ExcelFillXml fill = Fill.Copy();
+
+ switch (styleProperty) {
+ case eStyleProperty.PatternType:
+ if (fill is ExcelGradientFillXml) {
+ fill = new(NameSpaceManager);
+ }
+ fill.PatternType = (ExcelFillStyle)value;
+ break;
+ case eStyleProperty.Color:
+ case eStyleProperty.Tint:
+ case eStyleProperty.IndexedColor:
+ case eStyleProperty.AutoColor:
+ if (fill is ExcelGradientFillXml) {
+ fill = new(NameSpaceManager);
+ }
+ if (fill.PatternType == ExcelFillStyle.None) {
+ throw (new ArgumentException("Can't set color when patterntype is not set."));
+ }
+ ExcelColorXml destColor;
+ if (styleClass == eStyleClass.FillPatternColor) {
+ destColor = fill.PatternColor;
+ } else {
+ destColor = fill.BackgroundColor;
+ }
+
+ if (styleProperty == eStyleProperty.Color) {
+ destColor.Rgb = value.ToString();
+ } else if (styleProperty == eStyleProperty.Tint) {
+ destColor.Tint = (decimal)value;
+ } else if (styleProperty == eStyleProperty.IndexedColor) {
+ destColor.Indexed = (int)value;
+ } else {
+ destColor.Auto = (bool)value;
+ }
+
+ break;
+ default:
+ throw (new ArgumentException("Invalid class/property for class Fill."));
+ }
+ int subId;
+ string id = fill.Id;
+ subId = _styles.Fills.FindIndexById(id);
+ if (subId == int.MinValue) {
+ return _styles.Fills.Add(id, fill);
+ }
+ return subId;
+ }
+
+ private int GetIdGradientFill(
+ eStyleClass styleClass,
+ eStyleProperty styleProperty,
+ object value) {
+ ExcelGradientFillXml fill;
+ if (Fill is ExcelGradientFillXml) {
+ fill = (ExcelGradientFillXml)Fill.Copy();
+ } else {
+ fill = new(Fill.NameSpaceManager);
+ fill.GradientColor1.SetColor(Color.White);
+ fill.GradientColor2.SetColor(Color.FromArgb(79, 129, 189));
+ fill.Type = ExcelFillGradientType.Linear;
+ fill.Degree = 90;
+ fill.Top = double.NaN;
+ fill.Bottom = double.NaN;
+ fill.Left = double.NaN;
+ fill.Right = double.NaN;
+ }
+
+ switch (styleProperty) {
+ case eStyleProperty.GradientType:
+ fill.Type = (ExcelFillGradientType)value;
+ break;
+ case eStyleProperty.GradientDegree:
+ fill.Degree = (double)value;
+ break;
+ case eStyleProperty.GradientTop:
+ fill.Top = (double)value;
+ break;
+ case eStyleProperty.GradientBottom:
+ fill.Bottom = (double)value;
+ break;
+ case eStyleProperty.GradientLeft:
+ fill.Left = (double)value;
+ break;
+ case eStyleProperty.GradientRight:
+ fill.Right = (double)value;
+ break;
+ case eStyleProperty.Color:
+ case eStyleProperty.Tint:
+ case eStyleProperty.IndexedColor:
+ case eStyleProperty.AutoColor:
+ ExcelColorXml destColor;
+
+ if (styleClass == eStyleClass.FillGradientColor1) {
+ destColor = fill.GradientColor1;
+ } else {
+ destColor = fill.GradientColor2;
+ }
+
+ if (styleProperty == eStyleProperty.Color) {
+ destColor.Rgb = value.ToString();
+ } else if (styleProperty == eStyleProperty.Tint) {
+ destColor.Tint = (decimal)value;
+ } else if (styleProperty == eStyleProperty.IndexedColor) {
+ destColor.Indexed = (int)value;
+ } else {
+ destColor.Auto = (bool)value;
+ }
+ break;
+ default:
+ throw (new ArgumentException("Invalid class/property for class Fill."));
+ }
+ int subId;
+ string id = fill.Id;
+ subId = _styles.Fills.FindIndexById(id);
+ if (subId == int.MinValue) {
+ return _styles.Fills.Add(id, fill);
+ }
+ return subId;
+ }
+
+ private int GetIdNumberFormat(eStyleProperty styleProperty, object value) {
+ if (styleProperty == eStyleProperty.Format) {
+ ExcelNumberFormatXml item = null;
+ if (!_styles.NumberFormats.FindById(value.ToString(), ref item)) {
+ item = new(NameSpaceManager) {
+ Format = value.ToString(),
+ NumFmtId = _styles.NumberFormats.NextId++,
+ };
+ _styles.NumberFormats.Add(value.ToString(), item);
+ }
+ return item.NumFmtId;
+ }
+ throw (new("Invalid property for class Numberformat"));
+ }
+
+ private int GetIdFont(eStyleProperty styleProperty, object value) {
+ ExcelFontXml fnt = Font.Copy();
+
+ switch (styleProperty) {
+ case eStyleProperty.Name:
+ fnt.Name = value.ToString();
+ break;
+ case eStyleProperty.Size:
+ fnt.Size = (float)value;
+ break;
+ case eStyleProperty.Family:
+ fnt.Family = (int)value;
+ break;
+ case eStyleProperty.Bold:
+ fnt.Bold = (bool)value;
+ break;
+ case eStyleProperty.Italic:
+ fnt.Italic = (bool)value;
+ break;
+ case eStyleProperty.Strike:
+ fnt.Strike = (bool)value;
+ break;
+ case eStyleProperty.UnderlineType:
+ fnt.UnderLineType = (ExcelUnderLineType)value;
+ break;
+ case eStyleProperty.Color:
+ fnt.Color.Rgb = value.ToString();
+ break;
+ case eStyleProperty.VerticalAlign:
+ fnt.VerticalAlign =
+ ((ExcelVerticalAlignmentFont)value) == ExcelVerticalAlignmentFont.None
+ ? ""
+ : value.ToString().ToLower(CultureInfo.InvariantCulture);
+ break;
+ default:
+ throw (new("Invalid property for class Font"));
+ }
+ int subId;
+ string id = fnt.Id;
+ subId = _styles.Fonts.FindIndexById(id);
+ if (subId == int.MinValue) {
+ return _styles.Fonts.Add(id, fnt);
+ }
+ return subId;
+ }
+
internal override XmlNode CreateXmlNode(XmlNode topNode) {
return CreateXmlNode(topNode, false);
}
diff --git a/NetCoreTests/ExcelPackageTest.cs b/NetCoreTests/ExcelPackageTest.cs
index 369e55a..8785d5d 100644
--- a/NetCoreTests/ExcelPackageTest.cs
+++ b/NetCoreTests/ExcelPackageTest.cs
@@ -3,7 +3,6 @@
using FluentAssertions;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using OfficeOpenXml;
-using OfficeOpenXml.DataValidation;
namespace NetCoreTests;
@@ -33,20 +32,6 @@
worksheet.Name.Should().Be("pusum11a");
}
- [TestMethod]
- public void TestGetAsByteArray_DataValidation() {
- var package = new ExcelPackage(new FileInfo(GetTestWorkbookPath("DataValidation.xlsx")));
- var data = package.GetAsByteArray();
-
- // Verify that we can reload
- var newPackage = new ExcelPackage(new MemoryStream(data, false));
- newPackage.Workbook.Worksheets.Count.Should().Be(1);
- var worksheet = newPackage.Workbook.Worksheets.First();
- worksheet.Name.Should().Be("Sheet1");
- worksheet.DataValidations.Should().HaveCount(31);
- worksheet.DataValidations[1].ValidationType.Should().Be(ExcelDataValidationType.Whole);
- }
-
private static string GetTestWorkbookPath(string filename) {
var assemblyPath = Path.GetDirectoryName(typeof(ExcelPackageTest).Assembly.Location)!;
return Path.Combine(assemblyPath, "TestWorkbooks", filename);
diff --git a/NetCoreTests/TestWorkbooks/DataValidation.xlsx b/NetCoreTests/TestWorkbooks/DataValidation.xlsx
deleted file mode 100644
index 78d508c..0000000
--- a/NetCoreTests/TestWorkbooks/DataValidation.xlsx
+++ /dev/null
Binary files differ
