/// <summary>
/// Set the cell's style.
/// </summary>
/// <param name="CellReference">The cell reference, such as "A1".</param>
/// <param name="Style">The style to set.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool SetCellStyle(string CellReference, SLStyle Style)
{
int iRowIndex = -1;
int iColumnIndex = -1;
if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
{
return false;
}
return SetCellStyle(iRowIndex, iColumnIndex, iRowIndex, iColumnIndex, Style);
}
/// <summary>
/// Get the cell's style. The default style is returned if cell doesn't have an existing style, or if the cell reference is invalid.
/// </summary>
/// <param name="CellReference">The cell reference, such as "A1".</param>
/// <returns>The cell's style.</returns>
public SLStyle GetCellStyle(string CellReference)
{
int iRowIndex = -1;
int iColumnIndex = -1;
if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
{
SLStyle style = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
style.FromHash(listStyle[0]);
return style;
}
return GetCellStyle(iRowIndex, iColumnIndex);
}
/// <summary>
/// Get the style of the column. If the column doesn't have an existing style, the default style is returned.
/// </summary>
/// <param name="ColumnIndex">The column index.</param>
/// <returns>The column style.</returns>
public SLStyle GetColumnStyle(int ColumnIndex)
{
bool bFound = false;
SLStyle style = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
if (slws.ColumnProperties.ContainsKey(ColumnIndex))
{
SLColumnProperties cp = slws.ColumnProperties[ColumnIndex];
bFound = true;
style.FromHash(listStyle[(int)cp.StyleIndex]);
}
if (!bFound)
{
style.FromHash(listStyle[0]);
}
return style;
}
/// <summary>
/// Set the row style for a range of rows.
/// </summary>
/// <param name="StartRowIndex">The row index of the starting row.</param>
/// <param name="EndRowIndex">The row index of the ending row.</param>
/// <param name="RowStyle">The style for the rows.</param>
/// <returns>True if the row indices are valid. False otherwise.</returns>
public bool SetRowStyle(int StartRowIndex, int EndRowIndex, SLStyle RowStyle)
{
int iStartRowIndex = 1, iEndRowIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
bool result = false;
if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit && iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit)
{
result = true;
int i = 0;
int iStyleIndex = this.SaveToStylesheet(RowStyle.ToHash());
SLRowProperties rp;
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
if (slws.RowProperties.ContainsKey(i))
{
rp = slws.RowProperties[i];
rp.StyleIndex = (uint)iStyleIndex;
slws.RowProperties[i] = rp;
}
else
{
rp = new SLRowProperties(SimpleTheme.ThemeRowHeight);
rp.StyleIndex = (uint)iStyleIndex;
slws.RowProperties.Add(i, rp);
}
slws.RowColumnStyleHistory.Add(new SLRowColumnStyleHistory(true, i));
}
List<SLCellPoint> listCellKeys = slws.Cells.Keys.ToList<SLCellPoint>();
SLStyle cellstyle;
foreach (SLCellPoint pt in listCellKeys)
{
if (iStartRowIndex <= pt.RowIndex && pt.RowIndex <= iEndRowIndex)
{
cellstyle = this.GetCellStyle(pt.RowIndex, pt.ColumnIndex);
cellstyle.MergeStyle(RowStyle);
this.SetCellStyle(pt.RowIndex, pt.ColumnIndex, cellstyle);
}
}
}
return result;
}
/// <summary>
/// Set the row style.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="RowStyle">The style for the row.</param>
/// <returns>True if the row index is valid. False otherwise.</returns>
public bool SetRowStyle(int RowIndex, SLStyle RowStyle)
{
return SetRowStyle(RowIndex, RowIndex, RowStyle);
}
/// <summary>
/// Apply a named cell style to a range of columns. Existing styles are kept, unless the chosen named cell style overrides those styles.
/// </summary>
/// <param name="StartColumnIndex">The column index of the starting column.</param>
/// <param name="EndColumnIndex">The column index of the ending column.</param>
/// <param name="NamedCellStyle">The named cell style to be applied.</param>
/// <returns>True if the column indices are valid. False otherwise.</returns>
public bool ApplyNamedCellStyleToColumn(int StartColumnIndex, int EndColumnIndex, SLNamedCellStyleValues NamedCellStyle)
{
int iStartColumnIndex = 1, iEndColumnIndex = 1;
bool result = false;
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
SLStyle style = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
if (iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
{
result = true;
int i = 0;
for (i = iStartColumnIndex; i <= iEndColumnIndex; ++i)
{
style = this.GetColumnStyle(i);
style.ApplyNamedCellStyle(NamedCellStyle);
this.SetColumnStyle(i, style);
slws.RowColumnStyleHistory.Add(new SLRowColumnStyleHistory(false, i));
}
}
return result;
}
/// <summary>
/// Apply a named cell style to a range of cells. Existing styles are kept, unless the chosen named cell style overrides those styles.
/// </summary>
/// <param name="StartRowIndex">The row index of the starting row.</param>
/// <param name="StartColumnIndex">The column index of the starting column.</param>
/// <param name="EndRowIndex">The row index of the ending row.</param>
/// <param name="EndColumnIndex">The column index of the ending column.</param>
/// <param name="NamedCellStyle">The named cell style to be applied.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool ApplyNamedCellStyle(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, SLNamedCellStyleValues NamedCellStyle)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
bool result = false;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
SLStyle style = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
if (SLTool.CheckRowColumnIndexLimit(iStartRowIndex, iStartColumnIndex) && SLTool.CheckRowColumnIndexLimit(iEndRowIndex, iEndColumnIndex))
{
result = true;
int i = 0, j = 0;
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
style = this.GetCellStyle(i, j);
style.ApplyNamedCellStyle(NamedCellStyle);
this.SetCellStyle(i, j, style);
}
}
}
return result;
}
internal void WriteStylesheet()
{
int i = 0;
SLStyle styleSL = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
for (i = 0; i < listStyle.Count; ++i)
{
styleSL.FromHash(listStyle[i]);
if (styleSL.nfFormatCode.FormatCode.Length > 0)
{
this.SaveToStylesheetNumberingFormat(styleSL.nfFormatCode.ToHash());
}
if (styleSL.HasFont)
{
this.SaveToStylesheetFont(styleSL.fontReal.ToHash());
}
if (styleSL.HasFill)
{
this.SaveToStylesheetFill(styleSL.fillReal.ToHash());
}
if (styleSL.HasBorder)
{
this.SaveToStylesheetBorder(styleSL.borderReal.ToHash());
}
}
if (wbp.WorkbookStylesPart != null)
{
int diff = 0;
WorkbookStylesPart wbsp = wbp.WorkbookStylesPart;
if (dictStyleNumberingFormat.Count > 0)
{
if (dictStyleNumberingFormat.Count == 1
&& dictStyleNumberingFormatHash.ContainsKey(SLConstants.NumberFormatGeneral)
&& dictStyleNumberingFormatHash[SLConstants.NumberFormatGeneral] == 0)
{
// it just contains our "default" number format, so don't do anything.
}
else
{
wbsp.Stylesheet.NumberingFormats = new NumberingFormats();
List<int> listKeys = dictStyleNumberingFormat.Keys.ToList<int>();
listKeys.Sort();
if (listKeys[0] == 0
&& dictStyleNumberingFormat[listKeys[0]].Equals(SLConstants.NumberFormatGeneral, StringComparison.InvariantCultureIgnoreCase))
{
listKeys.RemoveAt(0);
}
wbsp.Stylesheet.NumberingFormats.Count = (uint)listKeys.Count;
SLNumberingFormat nfSL;
for (i = 0; i < listKeys.Count; ++i)
{
nfSL = new SLNumberingFormat();
nfSL.FromHash(dictStyleNumberingFormat[listKeys[i]]);
wbsp.Stylesheet.NumberingFormats.Append(new NumberingFormat()
{
NumberFormatId = (uint)listKeys[i],
FormatCode = SLTool.XmlWrite(nfSL.FormatCode)
});
}
}
}
if (listStyleFont.Count > countStyleFont)
{
if (wbsp.Stylesheet.Fonts == null)
{
wbsp.Stylesheet.Fonts = new Fonts();
}
wbsp.Stylesheet.Fonts.Count = (uint)listStyleFont.Count;
diff = listStyleFont.Count - countStyleFont;
for (i = 0; i < diff; ++i)
{
wbsp.Stylesheet.Fonts.Append(new Font() { InnerXml = listStyleFont[i + countStyleFont] });
}
}
if (listStyleFill.Count > countStyleFill)
{
if (wbsp.Stylesheet.Fills == null)
{
wbsp.Stylesheet.Fills = new Fills();
}
wbsp.Stylesheet.Fills.Count = (uint)listStyleFill.Count;
diff = listStyleFill.Count - countStyleFill;
for (i = 0; i < diff; ++i)
{
wbsp.Stylesheet.Fills.Append(new Fill() { InnerXml = listStyleFill[i + countStyleFill] });
}
}
if (listStyleBorder.Count > countStyleBorder)
{
if (wbsp.Stylesheet.Borders == null)
{
wbsp.Stylesheet.Borders = new Borders();
}
wbsp.Stylesheet.Borders.Count = (uint)listStyleBorder.Count;
diff = listStyleBorder.Count - countStyleBorder;
SLBorder borderSL = new SLBorder(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
for (i = 0; i < diff; ++i)
{
borderSL.FromHash(listStyleBorder[i + countStyleBorder]);
wbsp.Stylesheet.Borders.Append(borderSL.ToBorder());
}
}
int iCanonicalCellStyleFormatId = 0;
SLStyle styleCanonical = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
styleCanonical.FormatCode = this.NumberFormatGeneralText;
styleCanonical.fontReal.FromHash(listStyleFont[0]);
styleCanonical.Fill.SetPatternType(PatternValues.None);
styleCanonical.Border = new SLBorder(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
iCanonicalCellStyleFormatId = this.SaveToStylesheetCellStylesFormat(styleCanonical.ToHash());
this.TranslateStylesToStyleIds(ref styleCanonical);
if (listStyleCellStyleFormat.Count > countStyleCellStyleFormat)
{
if (wbsp.Stylesheet.CellStyleFormats == null)
{
wbsp.Stylesheet.CellStyleFormats = new CellStyleFormats();
}
wbsp.Stylesheet.CellStyleFormats.Count = (uint)listStyleCellStyleFormat.Count;
diff = listStyleCellStyleFormat.Count - countStyleCellStyleFormat;
styleSL = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
for (i = 0; i < diff; ++i)
{
styleSL.FromHash(listStyleCellStyleFormat[i + countStyleCellStyleFormat]);
this.TranslateStylesToStyleIds(ref styleSL);
wbsp.Stylesheet.CellStyleFormats.Append(styleSL.ToCellFormat());
}
}
if (listStyle.Count > countStyle)
{
if (wbsp.Stylesheet.CellFormats == null)
{
wbsp.Stylesheet.CellFormats = new CellFormats();
}
wbsp.Stylesheet.CellFormats.Count = (uint)listStyle.Count;
diff = listStyle.Count - countStyle;
styleSL = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
for (i = 0; i < diff; ++i)
{
styleSL.FromHash(listStyle[i + countStyle]);
this.TranslateStylesToStyleIds(ref styleSL);
styleSL.CellStyleFormatId = (uint)iCanonicalCellStyleFormatId;
if (styleSL.NumberFormatId != styleCanonical.NumberFormatId) styleSL.ApplyNumberFormat = true;
if (styleSL.FontId != styleCanonical.FontId) styleSL.ApplyFont = true;
if (styleSL.FillId != styleCanonical.FillId) styleSL.ApplyFill = true;
if (styleSL.BorderId != styleCanonical.BorderId) styleSL.ApplyBorder = true;
if (styleSL.HasAlignment) styleSL.ApplyAlignment = true;
if (styleSL.HasProtection) styleSL.ApplyProtection = true;
wbsp.Stylesheet.CellFormats.Append(styleSL.ToCellFormat());
}
}
if (listStyleCellStyle.Count > countStyleCellStyle)
{
if (wbsp.Stylesheet.CellStyles == null)
{
wbsp.Stylesheet.CellStyles = new CellStyles();
}
wbsp.Stylesheet.CellStyles.Count = (uint)listStyleCellStyle.Count;
diff = listStyleCellStyle.Count - countStyleCellStyle;
SLCellStyle csSL = new SLCellStyle();
for (i = 0; i < diff; ++i)
{
csSL.FromHash(listStyleCellStyle[i + countStyleCellStyle]);
wbsp.Stylesheet.CellStyles.Append(csSL.ToCellStyle());
}
}
if (listStyleDifferentialFormat.Count > countStyleDifferentialFormat)
{
if (wbsp.Stylesheet.DifferentialFormats == null)
{
wbsp.Stylesheet.DifferentialFormats = new DifferentialFormats();
}
wbsp.Stylesheet.DifferentialFormats.Count = (uint)listStyleDifferentialFormat.Count;
diff = listStyleDifferentialFormat.Count - countStyleDifferentialFormat;
for (i = 0; i < diff; ++i)
{
wbsp.Stylesheet.DifferentialFormats.Append(new DifferentialFormat() { InnerXml = listStyleDifferentialFormat[i + countStyleDifferentialFormat] });
}
}
if (listStyleTableStyle.Count > countStyleTableStyle)
{
if (wbsp.Stylesheet.TableStyles == null)
{
wbsp.Stylesheet.TableStyles = new TableStyles();
}
wbsp.Stylesheet.TableStyles.Count = (uint)listStyleTableStyle.Count;
if (this.TableStylesDefaultTableStyle.Length > 0)
{
wbsp.Stylesheet.TableStyles.DefaultTableStyle = this.TableStylesDefaultTableStyle;
}
if (this.TableStylesDefaultPivotStyle.Length > 0)
{
wbsp.Stylesheet.TableStyles.DefaultPivotStyle = this.TableStylesDefaultPivotStyle;
}
diff = listStyleTableStyle.Count - countStyleTableStyle;
SLTableStyle tsSL = new SLTableStyle();
for (i = 0; i < diff; ++i)
{
tsSL.FromHash(listStyleTableStyle[i + countStyleTableStyle]);
wbsp.Stylesheet.TableStyles.Append(tsSL.ToTableStyle());
}
}
// we're not touching Colors here, so there shouldn't be anything to update
}
else
{
WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
using (MemoryStream ms = new MemoryStream())
{
using (StreamWriter sw = new StreamWriter(ms))
{
sw.Write("<x:styleSheet xmlns:x=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
if (dictStyleNumberingFormat.Count > 0)
{
if (dictStyleNumberingFormat.Count == 1
&& dictStyleNumberingFormatHash.ContainsKey(SLConstants.NumberFormatGeneral)
&& dictStyleNumberingFormatHash[SLConstants.NumberFormatGeneral] == 0)
{
// it just contains our "default" number format, so don't do anything.
}
else
{
List<int> listKeys = dictStyleNumberingFormat.Keys.ToList<int>();
listKeys.Sort();
if (listKeys[0] == 0
&& dictStyleNumberingFormat[listKeys[0]].Equals(SLConstants.NumberFormatGeneral, StringComparison.InvariantCultureIgnoreCase))
{
listKeys.RemoveAt(0);
}
sw.Write("<x:numFmts count=\"{0}\">", listKeys.Count);
for (i = 0; i < listKeys.Count; ++i)
{
sw.Write("<x:numFmt numFmtId=\"{0}\" formatCode=\"{1}\" />", listKeys[i], SLTool.XmlWrite(dictStyleNumberingFormat[listKeys[i]]));
}
sw.Write("</x:numFmts>");
}
}
sw.Write("<x:fonts count=\"{0}\">", listStyleFont.Count);
for (i = 0; i < listStyleFont.Count; ++i)
{
sw.Write("<x:font>{0}</x:font>", listStyleFont[i]);
}
sw.Write("</x:fonts>");
sw.Write("<x:fills count=\"{0}\">", listStyleFill.Count);
for (i = 0; i < listStyleFill.Count; ++i)
{
sw.Write("<x:fill>{0}</x:fill>", listStyleFill[i]);
}
sw.Write("</x:fills>");
List<System.Drawing.Color> listempty = new List<System.Drawing.Color>();
sw.Write("<x:borders count=\"{0}\">", listStyleBorder.Count);
SLBorder slb;
for (i = 0; i < listStyleBorder.Count; ++i)
{
slb = new SLBorder(listempty, listempty);
slb.FromHash(listStyleBorder[i]);
slb.Sync();
sw.Write("<x:border");
if (slb.DiagonalUp != null) sw.Write(" diagonalUp=\"{0}\"", slb.DiagonalUp.Value ? "1" : "0");
if (slb.DiagonalDown != null) sw.Write(" diagonalDown=\"{0}\"", slb.DiagonalDown.Value ? "1" : "0");
if (slb.Outline != null && !slb.Outline.Value) sw.Write(" outline=\"0\"");
sw.Write(">");
// by "default" always have left, right, top, bottom and diagonal borders, even if empty?
sw.Write(SLBorderProperties.WriteToXmlTag("left", slb.LeftBorder));
sw.Write(SLBorderProperties.WriteToXmlTag("right", slb.RightBorder));
sw.Write(SLBorderProperties.WriteToXmlTag("top", slb.TopBorder));
sw.Write(SLBorderProperties.WriteToXmlTag("bottom", slb.BottomBorder));
sw.Write(SLBorderProperties.WriteToXmlTag("diagonal", slb.DiagonalBorder));
if (slb.HasVerticalBorder) sw.Write(SLBorderProperties.WriteToXmlTag("vertical", slb.VerticalBorder));
if (slb.HasHorizontalBorder) sw.Write(SLBorderProperties.WriteToXmlTag("horizontal", slb.HorizontalBorder));
sw.Write("</x:border>");
}
sw.Write("</x:borders>");
int iCanonicalCellStyleFormatId = 0;
SLStyle styleCanonical = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
styleCanonical.FormatCode = this.NumberFormatGeneralText;
styleCanonical.fontReal.FromHash(listStyleFont[0]);
styleCanonical.Fill.SetPatternType(PatternValues.None);
styleCanonical.Border = new SLBorder(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
iCanonicalCellStyleFormatId = this.SaveToStylesheetCellStylesFormat(styleCanonical.ToHash());
this.TranslateStylesToStyleIds(ref styleCanonical);
sw.Write("<x:cellStyleXfs count=\"{0}\">", listStyleCellStyleFormat.Count);
for (i = 0; i < listStyleCellStyleFormat.Count; ++i)
{
styleSL = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, listempty, listempty);
styleSL.FromHash(listStyleCellStyleFormat[i]);
this.TranslateStylesToStyleIds(ref styleSL);
sw.Write(styleSL.WriteToXmlTag());
}
sw.Write("</x:cellStyleXfs>");
sw.Write("<x:cellXfs count=\"{0}\">", listStyle.Count);
for (i = 0; i < listStyle.Count; ++i)
{
styleSL = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, listempty, listempty);
styleSL.FromHash(listStyle[i]);
this.TranslateStylesToStyleIds(ref styleSL);
styleSL.CellStyleFormatId = (uint)iCanonicalCellStyleFormatId;
if (styleSL.NumberFormatId != styleCanonical.NumberFormatId) styleSL.ApplyNumberFormat = true;
if (styleSL.FontId != styleCanonical.FontId) styleSL.ApplyFont = true;
if (styleSL.FillId != styleCanonical.FillId) styleSL.ApplyFill = true;
if (styleSL.BorderId != styleCanonical.BorderId) styleSL.ApplyBorder = true;
if (styleSL.HasAlignment) styleSL.ApplyAlignment = true;
if (styleSL.HasProtection) styleSL.ApplyProtection = true;
sw.Write(styleSL.WriteToXmlTag());
}
sw.Write("</x:cellXfs>");
sw.Write("<x:cellStyles count=\"{0}\">", listStyleCellStyle.Count);
SLCellStyle csSL;
for (i = 0; i < listStyleCellStyle.Count; ++i)
{
csSL = new SLCellStyle();
csSL.FromHash(listStyleCellStyle[i]);
sw.Write(csSL.WriteToXmlTag());
}
sw.Write("</x:cellStyles>");
sw.Write("<x:dxfs count=\"{0}\">", listStyleDifferentialFormat.Count);
for (i = 0; i < listStyleDifferentialFormat.Count; ++i)
{
sw.Write("<x:dxf>{0}</x:dxf>", listStyleDifferentialFormat[i]);
}
sw.Write("</x:dxfs>");
sw.Write("<x:tableStyles count=\"{0}\"", listStyleTableStyle.Count);
if (this.TableStylesDefaultTableStyle.Length > 0)
{
sw.Write(" defaultTableStyle=\"{0}\"", this.TableStylesDefaultTableStyle);
}
if (this.TableStylesDefaultPivotStyle.Length > 0)
{
sw.Write(" defaultPivotStyle=\"{0}\"", this.TableStylesDefaultPivotStyle);
}
sw.Write(">");
SLTableStyle tsSL;
for (i = 0; i < listStyleTableStyle.Count; ++i)
{
tsSL = new SLTableStyle();
tsSL.FromHash(listStyleTableStyle[i]);
sw.Write(tsSL.WriteToXmlTag());
}
sw.Write("</x:tableStyles>");
sw.Write("</x:styleSheet>");
sw.Flush();
ms.Position = 0;
wbsp.FeedData(ms);
}
}
// end of writing new stylesheet
}
}
internal void LoadStylesheet()
{
countStyle = 0;
listStyle = new List<string>();
dictStyleHash = new Dictionary<string, int>();
NumberFormatGeneralId = -1;
NumberFormatGeneralText = SLConstants.NumberFormatGeneral;
NextNumberFormatId = SLConstants.CustomNumberFormatIdStartIndex;
dictStyleNumberingFormat = new Dictionary<int, string>();
dictStyleNumberingFormatHash = new Dictionary<string, int>();
countStyleFont = 0;
listStyleFont = new List<string>();
dictStyleFontHash = new Dictionary<string, int>();
countStyleFill = 0;
listStyleFill = new List<string>();
dictStyleFillHash = new Dictionary<string, int>();
countStyleBorder = 0;
listStyleBorder = new List<string>();
dictStyleBorderHash = new Dictionary<string, int>();
countStyleCellStyle = 0;
listStyleCellStyle = new List<string>();
dictStyleCellStyleHash = new Dictionary<string, int>();
countStyleCellStyleFormat = 0;
listStyleCellStyleFormat = new List<string>();
dictStyleCellStyleFormatHash = new Dictionary<string, int>();
countStyleDifferentialFormat = 0;
listStyleDifferentialFormat = new List<string>();
dictStyleDifferentialFormatHash = new Dictionary<string, int>();
countStyleTableStyle = 0;
listStyleTableStyle = new List<string>();
dictStyleTableStyleHash = new Dictionary<string, int>();
int i = 0;
string sHash = string.Empty;
if (wbp.WorkbookStylesPart != null)
{
WorkbookStylesPart wbsp = wbp.WorkbookStylesPart;
this.NextNumberFormatId = SLConstants.CustomNumberFormatIdStartIndex;
this.StylesheetColors = null;
using (OpenXmlReader oxr = OpenXmlReader.Create(wbsp))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(NumberingFormats))
{
NumberingFormat nf;
using (OpenXmlReader oxrNF = OpenXmlReader.Create((NumberingFormats)oxr.LoadCurrentElement()))
{
while (oxrNF.Read())
{
if (oxrNF.ElementType == typeof(NumberingFormat))
{
nf = (NumberingFormat)oxrNF.LoadCurrentElement();
if (nf.NumberFormatId != null && nf.FormatCode != null)
{
i = (int)nf.NumberFormatId.Value;
sHash = nf.FormatCode.Value;
dictStyleNumberingFormat[i] = sHash;
dictStyleNumberingFormatHash[sHash] = i;
if (sHash.Equals(SLConstants.NumberFormatGeneral, StringComparison.InvariantCultureIgnoreCase))
{
this.NumberFormatGeneralText = sHash;
this.NumberFormatGeneralId = i;
}
// if there's a number format greater than the next number,
// obviously we want to increment.
// if there's a current number equal to the next number,
// we want to increment because this number exists!
// Emphasis on *next* number
if (i >= this.NextNumberFormatId)
{
this.NextNumberFormatId = this.NextNumberFormatId + 1;
}
}
}
}
}
}
else if (oxr.ElementType == typeof(Fonts))
{
SLFont fontSL;
using (OpenXmlReader oxrFont = OpenXmlReader.Create((Fonts)oxr.LoadCurrentElement()))
{
while (oxrFont.Read())
{
if (oxrFont.ElementType == typeof(Font))
{
fontSL = new SLFont(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
fontSL.FromFont((Font)oxrFont.LoadCurrentElement());
this.ForceSaveToStylesheetFont(fontSL.ToHash());
}
}
}
countStyleFont = listStyleFont.Count;
}
else if (oxr.ElementType == typeof(Fills))
{
SLFill fillSL;
using (OpenXmlReader oxrFill = OpenXmlReader.Create((Fills)oxr.LoadCurrentElement()))
{
while (oxrFill.Read())
{
if (oxrFill.ElementType == typeof(Fill))
{
fillSL = new SLFill(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
fillSL.FromFill((Fill)oxrFill.LoadCurrentElement());
this.ForceSaveToStylesheetFill(fillSL.ToHash());
}
}
}
countStyleFill = listStyleFill.Count;
}
else if (oxr.ElementType == typeof(Borders))
{
SLBorder borderSL;
using (OpenXmlReader oxrBorder = OpenXmlReader.Create((Borders)oxr.LoadCurrentElement()))
{
while (oxrBorder.Read())
{
if (oxrBorder.ElementType == typeof(Border))
{
borderSL = new SLBorder(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
borderSL.FromBorder((Border)oxrBorder.LoadCurrentElement());
this.ForceSaveToStylesheetBorder(borderSL.ToHash());
}
}
}
countStyleBorder = listStyleBorder.Count;
}
else if (oxr.ElementType == typeof(CellStyleFormats))
{
SLStyle styleSL;
using (OpenXmlReader oxrCellStyleFormats = OpenXmlReader.Create((CellStyleFormats)oxr.LoadCurrentElement()))
{
while (oxrCellStyleFormats.Read())
{
if (oxrCellStyleFormats.ElementType == typeof(CellFormat))
{
styleSL = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
styleSL.FromCellFormat((CellFormat)oxrCellStyleFormats.LoadCurrentElement());
this.TranslateStyleIdsToStyles(ref styleSL);
this.ForceSaveToStylesheetCellStylesFormat(styleSL.ToHash());
}
}
}
countStyleCellStyleFormat = listStyleCellStyleFormat.Count;
}
else if (oxr.ElementType == typeof(CellFormats))
{
SLStyle styleSL;
using (OpenXmlReader oxrCellFormats = OpenXmlReader.Create((CellFormats)oxr.LoadCurrentElement()))
{
while (oxrCellFormats.Read())
{
if (oxrCellFormats.ElementType == typeof(CellFormat))
{
styleSL = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
styleSL.FromCellFormat((CellFormat)oxrCellFormats.LoadCurrentElement());
this.TranslateStyleIdsToStyles(ref styleSL);
this.ForceSaveToStylesheet(styleSL.ToHash());
}
}
}
countStyle = listStyle.Count;
}
else if (oxr.ElementType == typeof(CellStyles))
{
SLCellStyle csSL;
using (OpenXmlReader oxrCellStyles = OpenXmlReader.Create((CellStyles)oxr.LoadCurrentElement()))
{
while (oxrCellStyles.Read())
{
if (oxrCellStyles.ElementType == typeof(CellStyle))
{
csSL = new SLCellStyle();
csSL.FromCellStyle((CellStyle)oxrCellStyles.LoadCurrentElement());
this.ForceSaveToStylesheetCellStyle(csSL.ToHash());
}
}
}
countStyleCellStyle = listStyleCellStyle.Count;
}
else if (oxr.ElementType == typeof(DifferentialFormats))
{
SLDifferentialFormat dfSL;
using (OpenXmlReader oxrDiff = OpenXmlReader.Create((DifferentialFormats)oxr.LoadCurrentElement()))
{
while (oxrDiff.Read())
{
if (oxrDiff.ElementType == typeof(DifferentialFormat))
{
dfSL = new SLDifferentialFormat();
dfSL.FromDifferentialFormat((DifferentialFormat)oxrDiff.LoadCurrentElement());
this.ForceSaveToStylesheetDifferentialFormat(dfSL.ToHash());
}
}
}
countStyleDifferentialFormat = listStyleDifferentialFormat.Count;
}
else if (oxr.ElementType == typeof(TableStyles))
{
TableStyles tss = (TableStyles)oxr.LoadCurrentElement();
SLTableStyle tsSL;
i = 0;
using (OpenXmlReader oxrTableStyles = OpenXmlReader.Create(tss))
{
while (oxrTableStyles.Read())
{
if (oxrTableStyles.ElementType == typeof(TableStyle))
{
tsSL = new SLTableStyle();
tsSL.FromTableStyle((TableStyle)oxrTableStyles.LoadCurrentElement());
sHash = tsSL.ToHash();
listStyleTableStyle.Add(sHash);
dictStyleTableStyleHash[sHash] = i;
++i;
}
}
}
countStyleTableStyle = listStyleTableStyle.Count;
if (tss.DefaultTableStyle != null)
{
this.TableStylesDefaultTableStyle = tss.DefaultTableStyle.Value;
}
else
{
this.TableStylesDefaultTableStyle = string.Empty;
}
if (tss.DefaultPivotStyle != null)
{
this.TableStylesDefaultPivotStyle = tss.DefaultPivotStyle.Value;
}
else
{
this.TableStylesDefaultPivotStyle = string.Empty;
}
}
else if (oxr.ElementType == typeof(Colors))
{
this.StylesheetColors = (Colors)(oxr.LoadCurrentElement().CloneNode(true));
}
}
}
// Force a "General" number format to be saved.
// Upper case is used by LibreOffice. Is it case insensitive?
if (this.NumberFormatGeneralId < 0)
{
if (!dictStyleNumberingFormat.ContainsKey(0))
{
this.NumberFormatGeneralId = 0;
this.NumberFormatGeneralText = SLConstants.NumberFormatGeneral;
dictStyleNumberingFormat[this.NumberFormatGeneralId] = this.NumberFormatGeneralText;
dictStyleNumberingFormatHash[this.NumberFormatGeneralText] = this.NumberFormatGeneralId;
}
else
{
this.NumberFormatGeneralId = this.NextNumberFormatId;
this.NumberFormatGeneralText = SLConstants.NumberFormatGeneral;
dictStyleNumberingFormat[this.NumberFormatGeneralId] = this.NumberFormatGeneralText;
dictStyleNumberingFormatHash[this.NumberFormatGeneralText] = this.NumberFormatGeneralId;
++this.NextNumberFormatId;
}
}
if (listStyleFont.Count == 0)
{
SLFont fontSL = new SLFont(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
fontSL.SetFont(FontSchemeValues.Minor, SLConstants.DefaultFontSize);
fontSL.SetFontThemeColor(SLThemeColorIndexValues.Dark1Color);
this.SaveToStylesheetFont(fontSL.ToHash());
}
if (listStyleFill.Count == 0)
{
SLFill fillNone = new SLFill(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
fillNone.SetPatternType(PatternValues.None);
this.SaveToStylesheetFill(fillNone.ToHash());
SLFill fillGray125 = new SLFill(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
fillGray125.SetPatternType(PatternValues.Gray125);
this.SaveToStylesheetFill(fillGray125.ToHash());
}
else
{
// make sure there's at least a "none" pattern
SLFill fillNone = new SLFill(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
fillNone.SetPatternType(PatternValues.None);
this.SaveToStylesheetFill(fillNone.ToHash());
}
// make sure there's at least an empty border
SLBorder borderEmpty = new SLBorder(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
this.SaveToStylesheetBorder(borderEmpty.ToHash());
int iCanonicalCellStyleFormatId = 0;
SLStyle style = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
style.FormatCode = this.NumberFormatGeneralText;
style.fontReal.FromHash(listStyleFont[0]);
style.Fill.SetPatternType(PatternValues.None);
style.Border = new SLBorder(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
// there's at least one cell style format
iCanonicalCellStyleFormatId = this.SaveToStylesheetCellStylesFormat(style.ToHash());
// there's at least one style
style.CellStyleFormatId = (uint)iCanonicalCellStyleFormatId;
this.SaveToStylesheet(style.ToHash());
if (listStyleCellStyle.Count == 0)
{
SLCellStyle csNormal = new SLCellStyle();
csNormal.Name = "Normal";
//csNormal.FormatId = 0;
csNormal.FormatId = (uint)iCanonicalCellStyleFormatId;
csNormal.BuiltinId = 0;
this.SaveToStylesheetCellStyle(csNormal.ToHash());
}
}
else
{
// no numbering format by default
this.NextNumberFormatId = SLConstants.CustomNumberFormatIdStartIndex;
this.NumberFormatGeneralId = 0;
this.NumberFormatGeneralText = SLConstants.NumberFormatGeneral;
dictStyleNumberingFormat[this.NumberFormatGeneralId] = this.NumberFormatGeneralText;
dictStyleNumberingFormatHash[this.NumberFormatGeneralText] = this.NumberFormatGeneralId;
SLFont fontDefault = new SLFont(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
fontDefault.SetFont(FontSchemeValues.Minor, SLConstants.DefaultFontSize);
fontDefault.SetFontThemeColor(SLThemeColorIndexValues.Dark1Color);
this.SaveToStylesheetFont(fontDefault.ToHash());
SLFill fillNone = new SLFill(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
fillNone.SetPatternType(PatternValues.None);
this.SaveToStylesheetFill(fillNone.ToHash());
SLFill fillGray125 = new SLFill(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
fillGray125.SetPatternType(PatternValues.Gray125);
this.SaveToStylesheetFill(fillGray125.ToHash());
SLBorder borderEmpty = new SLBorder(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
this.SaveToStylesheetBorder(borderEmpty.ToHash());
int iCanonicalCellStyleFormatId = 0;
SLStyle style = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
style.FormatCode = this.NumberFormatGeneralText;
style.Font = fontDefault;
style.Fill = fillNone;
style.Border = borderEmpty;
iCanonicalCellStyleFormatId = this.SaveToStylesheetCellStylesFormat(style.ToHash());
style.CellStyleFormatId = (uint)iCanonicalCellStyleFormatId;
this.SaveToStylesheet(style.ToHash());
SLCellStyle csNormal = new SLCellStyle();
csNormal.Name = "Normal";
//csNormal.FormatId = 0;
csNormal.FormatId = (uint)iCanonicalCellStyleFormatId;
csNormal.BuiltinId = 0;
this.SaveToStylesheetCellStyle(csNormal.ToHash());
this.TableStylesDefaultTableStyle = SLConstants.DefaultTableStyle;
this.TableStylesDefaultPivotStyle = SLConstants.DefaultPivotStyle;
}
}
internal void TranslateStylesToStyleIds(ref SLStyle style)
{
style.Sync();
if (style.nfFormatCode.FormatCode.Length > 0)
{
style.NumberFormatId = (uint)this.SaveToStylesheetNumberingFormat(style.nfFormatCode.ToHash());
}
else
{
style.NumberFormatId = (uint)this.NumberFormatGeneralId;
}
if (style.HasFont)
{
style.FontId = (uint)this.SaveToStylesheetFont(style.fontReal.ToHash());
}
else
{
style.FontId = (uint)this.SaveToStylesheetFont(listStyleFont[0]);
}
if (style.HasFill)
{
style.FillId = (uint)this.SaveToStylesheetFill(style.fillReal.ToHash());
}
else
{
SLFill fill = new SLFill(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
fill.SetPatternType(PatternValues.None);
style.FillId = (uint)this.SaveToStylesheetFill(fill.ToHash());
}
if (style.HasBorder)
{
style.BorderId = (uint)this.SaveToStylesheetBorder(style.borderReal.ToHash());
}
else
{
SLBorder border = new SLBorder(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
style.BorderId = (uint)this.SaveToStylesheetBorder(border.ToHash());
}
}
/// <summary>
/// Set the style of a range of cells.
/// </summary>
/// <param name="StartRowIndex">The row index of the start cell of the cell range. This is typically the top-left cell.</param>
/// <param name="StartColumnIndex">The column index of the start cell of the cell range. This is typically the top-left cell.</param>
/// <param name="EndRowIndex">The row index of the end cell of the cell range. This is typically the bottom-right cell.</param>
/// <param name="EndColumnIndex">The column index of the end cell of the cell range. This is typically the bottom-right cell.</param>
/// <param name="Style">The style to set.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool SetCellStyle(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, SLStyle Style)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
bool result = false;
if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit
&& iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit
&& iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
&& iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
{
int iStyleIndex = this.SaveToStylesheet(Style.ToHash());
// default is 0, the default style
if (iStyleIndex > 0)
{
result = true;
SLCellPoint pt;
SLCell c;
int i, j;
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
c.StyleIndex = (uint)iStyleIndex;
slws.Cells[pt] = c;
}
else
{
c = new SLCell();
c.CellText = string.Empty;
c.StyleIndex = (uint)iStyleIndex;
slws.Cells[pt] = c;
}
}
}
}
}
return result;
}
/// <summary>
/// Set the column style.
/// </summary>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="ColumnStyle">The style for the column.</param>
/// <returns>True if the column index is valid. False otherwise.</returns>
public bool SetColumnStyle(int ColumnIndex, SLStyle ColumnStyle)
{
return SetColumnStyle(ColumnIndex, ColumnIndex, ColumnStyle);
}
/// <summary>
/// Set the column style for a range of rows.
/// </summary>
/// <param name="StartColumnIndex">The column index of the starting column.</param>
/// <param name="EndColumnIndex">The column index of the ending column.</param>
/// <param name="ColumnStyle">The style for the columns.</param>
/// <returns>True if the column indices are valid. False otherwise.</returns>
public bool SetColumnStyle(int StartColumnIndex, int EndColumnIndex, SLStyle ColumnStyle)
{
int iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
bool result = false;
if (iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
{
result = true;
int i = 0;
int iStyleIndex = this.SaveToStylesheet(ColumnStyle.ToHash());
SLColumnProperties cp;
for (i = iStartColumnIndex; i <= iEndColumnIndex; ++i)
{
if (slws.ColumnProperties.ContainsKey(i))
{
cp = slws.ColumnProperties[i];
cp.StyleIndex = (uint)iStyleIndex;
slws.ColumnProperties[i] = cp;
}
else
{
cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
cp.StyleIndex = (uint)iStyleIndex;
slws.ColumnProperties.Add(i, cp);
}
slws.RowColumnStyleHistory.Add(new SLRowColumnStyleHistory(false, i));
}
List<SLCellPoint> listCellKeys = slws.Cells.Keys.ToList<SLCellPoint>();
SLStyle cellstyle;
foreach (SLCellPoint pt in listCellKeys)
{
if (iStartColumnIndex <= pt.ColumnIndex && pt.ColumnIndex <= iEndColumnIndex)
{
cellstyle = this.GetCellStyle(pt.RowIndex, pt.ColumnIndex);
cellstyle.MergeStyle(ColumnStyle);
this.SetCellStyle(pt.RowIndex, pt.ColumnIndex, cellstyle);
}
}
}
return result;
}
/// <summary>
/// Highlight cells with values less than a given value.
/// </summary>
/// <param name="IncludeEquality">True for less than or equal to. False for strictly less than.</param>
/// <param name="Value">The value to be compared with.</param>
/// <param name="HighlightStyle">A custom formatted style. Note that only number formats, fonts, borders and fills are used. Note further that for fonts, only italic/bold, underline, color and strikethrough settings are used.</param>
public void HighlightCellsLessThan(bool IncludeEquality, string Value, SLStyle HighlightStyle)
{
this.HighlightCellsLessThan(IncludeEquality, Value, this.TranslateToDifferentialFormat(HighlightStyle));
}
// TODO: Hyperlink cell range
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="HyperlinkType">The type of hyperlink.</param>
/// <param name="Address">The URL for web pages, the file path for existing files, a cell reference (such as Sheet1!A1 or Sheet1!A1:B5), a defined name or an email address. NOTE: Do NOT include the "mailto:" portion for email addresses.</param>
/// <param name="Display">The display text. Set null or an empty string to use the default.</param>
/// <param name="ToolTip">The tooltip (or screentip) text. Set null or an empty string to ignore this.</param>
/// <param name="OverwriteExistingCell">True to overwrite the existing cell value with the hyperlink display text. False otherwise.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool InsertHyperlink(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell)
{
if (RowIndex < 1 || RowIndex > SLConstants.RowLimit) return false;
if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit) return false;
SLHyperlink hl = new SLHyperlink();
hl.IsNew = true;
hl.Reference = new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex);
switch (HyperlinkType)
{
case SLHyperlinkTypeValues.EmailAddress:
hl.IsExternal = true;
hl.HyperlinkUri = string.Format("mailto:{0}", Address);
hl.HyperlinkUriKind = UriKind.Absolute;
break;
case SLHyperlinkTypeValues.FilePath:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
// assume if it starts with ../ or ./ it's a relative path.
hl.HyperlinkUriKind = Address.StartsWith(".") ? UriKind.Relative : UriKind.Absolute;
break;
case SLHyperlinkTypeValues.InternalDocumentLink:
hl.IsExternal = false;
hl.Location = Address;
break;
case SLHyperlinkTypeValues.Url:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
hl.HyperlinkUriKind = UriKind.Absolute;
break;
}
if (Display == null)
{
hl.Display = Address;
}
else
{
if (Display.Length == 0)
{
hl.Display = Address;
}
else
{
hl.Display = Display;
}
}
if (ToolTip != null && ToolTip.Length > 0) hl.ToolTip = ToolTip;
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
SLStyle style;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
style = new SLStyle();
if (c.StyleIndex < listStyle.Count) style.FromHash(listStyle[(int)c.StyleIndex]);
else style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
if (OverwriteExistingCell)
{
// in case there's a formula
c.CellFormula = null;
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
}
// else don't have to do anything
slws.Cells[pt] = c.Clone();
}
else
{
c = new SLCell();
style = new SLStyle();
style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
slws.Cells[pt] = c.Clone();
}
slws.Hyperlinks.Add(hl);
return true;
}
/// <summary>
/// Highlight cells with values equal to a given value.
/// </summary>
/// <param name="IsEqual">True for equal to given value. False for not equal to given value.</param>
/// <param name="Value">The value to be compared with.</param>
/// <param name="HighlightStyle">A custom formatted style. Note that only number formats, fonts, borders and fills are used. Note further that for fonts, only italic/bold, underline, color and strikethrough settings are used.</param>
public void HighlightCellsEqual(bool IsEqual, string Value, SLStyle HighlightStyle)
{
this.HighlightCellsEqual(IsEqual, Value, this.TranslateToDifferentialFormat(HighlightStyle));
}
/// <summary>
/// Highlight cells with values in the top/bottom range.
/// </summary>
/// <param name="IsTopRange">True if in the top range. False if in the bottom range.</param>
/// <param name="Rank">The value of X in "Top/Bottom X". If <paramref name="IsPercent"/> is true, then X refers to X%, otherwise it's X number of items.</param>
/// <param name="IsPercent">True if referring to percentage. False if referring to number of items.</param>
/// <param name="HighlightStyle">A custom formatted style. Note that only number formats, fonts, borders and fills are used. Note further that for fonts, only italic/bold, underline, color and strikethrough settings are used.</param>
public void HighlightCellsInTopRange(bool IsTopRange, uint Rank, bool IsPercent, SLStyle HighlightStyle)
{
this.HighlightCellsInTopRange(IsTopRange, Rank, IsPercent, this.TranslateToDifferentialFormat(HighlightStyle));
}
/// <summary>
/// Highlight cells with values containing a given value.
/// </summary>
/// <param name="IsContaining">True for containing given value. False for not containing given value.</param>
/// <param name="Value">The value to be compared with.</param>
/// <param name="HighlightStyle">A custom formatted style. Note that only number formats, fonts, borders and fills are used. Note further that for fonts, only italic/bold, underline, color and strikethrough settings are used.</param>
public void HighlightCellsContainingText(bool IsContaining, string Value, SLStyle HighlightStyle)
{
this.HighlightCellsContainingText(IsContaining, Value, this.TranslateToDifferentialFormat(HighlightStyle));
}
/// <summary>
/// Highlight cells with values ending with a given value.
/// </summary>
/// <param name="Value">The value to be compared with.</param>
/// <param name="HighlightStyle">A custom formatted style. Note that only number formats, fonts, borders and fills are used. Note further that for fonts, only italic/bold, underline, color and strikethrough settings are used.</param>
public void HighlightCellsEndingWith(string Value, SLStyle HighlightStyle)
{
this.HighlightCellsEndingWith(Value, this.TranslateToDifferentialFormat(HighlightStyle));
}
/// <summary>
/// Highlight cells with values between 2 given values.
/// </summary>
/// <param name="IsBetween">True for between the 2 given values. False for not between the 2 given values.</param>
/// <param name="Value1">The 1st value to be compared with.</param>
/// <param name="Value2">The 2nd value to be compared with.</param>
/// <param name="HighlightStyle">A custom formatted style. Note that only number formats, fonts, borders and fills are used. Note further that for fonts, only italic/bold, underline, color and strikethrough settings are used.</param>
public void HighlightCellsBetween(bool IsBetween, string Value1, string Value2, SLStyle HighlightStyle)
{
this.HighlightCellsBetween(IsBetween, Value1, Value2, this.TranslateToDifferentialFormat(HighlightStyle));
}
/// <summary>
/// Highlight cells containing errors.
/// </summary>
/// <param name="ContainsErrors">True for containing errors. False for not containing errors.</param>
/// <param name="HighlightStyle">A custom formatted style. Note that only number formats, fonts, borders and fills are used. Note further that for fonts, only italic/bold, underline, color and strikethrough settings are used.</param>
public void HighlightCellsContainingErrors(bool ContainsErrors, SLStyle HighlightStyle)
{
this.HighlightCellsContainingErrors(ContainsErrors, this.TranslateToDifferentialFormat(HighlightStyle));
}
internal SLDifferentialFormat TranslateToDifferentialFormat(SLStyle style)
{
style.Sync();
SLDifferentialFormat df = new SLDifferentialFormat();
if (style.HasNumberingFormat) df.FormatCode = style.FormatCode;
if (style.Font.Italic != null && style.Font.Italic.Value) df.Font.Italic = true;
if (style.Font.Bold != null && style.Font.Bold.Value) df.Font.Bold = true;
if (style.Font.HasUnderline) df.Font.Underline = style.Font.Underline;
if (style.Font.HasFontColor)
{
df.Font.clrFontColor = style.Font.clrFontColor.Clone();
df.Font.HasFontColor = true;
}
if (style.Font.Strike != null && style.Font.Strike.Value) df.Font.Strike = true;
if (style.HasBorder) df.Border = style.Border.Clone();
if (style.HasFill) df.Fill = style.Fill.Clone();
df.Sync();
return df;
}
/// <summary>
/// Highlight cells with values compared to the average.
/// </summary>
/// <param name="AverageType">The type of comparison to the average.</param>
/// <param name="HighlightStyle">A custom formatted style. Note that only number formats, fonts, borders and fills are used. Note further that for fonts, only italic/bold, underline, color and strikethrough settings are used.</param>
public void HighlightCellsAboveAverage(SLHighlightCellsAboveAverageValues AverageType, SLStyle HighlightStyle)
{
this.HighlightCellsAboveAverage(AverageType, this.TranslateToDifferentialFormat(HighlightStyle));
}
/// <summary>
/// Highlight cells with values according to a formula.
/// </summary>
/// <param name="Formula">The formula to apply.</param>
/// <param name="HighlightStyle">A custom formatted style. Note that only number formats, fonts, borders and fills are used. Note further that for fonts, only italic/bold, underline, color and strikethrough settings are used.</param>
public void HighlightCellsWithFormula(string Formula, SLStyle HighlightStyle)
{
this.HighlightCellsWithFormula(Formula, this.TranslateToDifferentialFormat(HighlightStyle));
}
/// <summary>
/// Highlight cells with unique values.
/// </summary>
/// <param name="HighlightStyle">A custom formatted style. Note that only number formats, fonts, borders and fills are used. Note further that for fonts, only italic/bold, underline, color and strikethrough settings are used.</param>
public void HighlightCellsWithUniques(SLStyle HighlightStyle)
{
this.HighlightCellsWithUniques(this.TranslateToDifferentialFormat(HighlightStyle));
}
/// <summary>
/// Highlight cells with date values occurring according to a given time period.
/// </summary>
/// <param name="DatePeriod">A given time period.</param>
/// <param name="HighlightStyle">A custom formatted style. Note that only number formats, fonts, borders and fills are used. Note further that for fonts, only italic/bold, underline, color and strikethrough settings are used.</param>
public void HighlightCellsWithDatesOccurring(TimePeriodValues DatePeriod, SLStyle HighlightStyle)
{
this.HighlightCellsWithDatesOccurring(DatePeriod, this.TranslateToDifferentialFormat(HighlightStyle));
}
/// <summary>
/// Set the style of a range of cells.
/// </summary>
/// <param name="StartCellReference">The cell reference of the start cell of the cell range, such as "A1". This is typically the top-left cell.</param>
/// <param name="EndCellReference">The cell reference of the end cell of the cell range, such as "A1". This is typically the bottom-right cell.</param>
/// <param name="Style">The style to set.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool SetCellStyle(string StartCellReference, string EndCellReference, SLStyle Style)
{
int iStartRowIndex = -1;
int iStartColumnIndex = -1;
int iEndRowIndex = -1;
int iEndColumnIndex = -1;
if (!SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex)
|| !SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex))
{
return false;
}
return SetCellStyle(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, Style);
}
/// <summary>
/// Set the cell's style.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Style">The style to set.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool SetCellStyle(int RowIndex, int ColumnIndex, SLStyle Style)
{
return SetCellStyle(RowIndex, ColumnIndex, RowIndex, ColumnIndex, Style);
}
internal void TranslateStyleIdsToStyles(ref SLStyle style)
{
int index;
if (style.NumberFormatId != null)
{
index = (int)style.NumberFormatId.Value;
style.nfFormatCode = new SLNumberingFormat();
style.nfFormatCode.NumberFormatId = (uint)index;
if (dictStyleNumberingFormat.ContainsKey(index))
{
style.nfFormatCode.FromHash(dictStyleNumberingFormat[index]);
}
else if (dictBuiltInNumberingFormat.ContainsKey(index))
{
style.nfFormatCode.FormatCode = dictBuiltInNumberingFormat[index];
}
else
{
// don't know the format code, but *something* has to be written.
//style.nfFormatCode.FormatCode = string.Format("Built-in format code {0}", index);
style.nfFormatCode.FormatCode = SLConstants.NumberFormatGeneral;
}
style.HasNumberingFormat = true;
}
else
{
style.RemoveFormatCode();
}
if (style.FontId != null)
{
style.HasFont = true;
index = (int)style.FontId.Value;
if (index >= 0 && index < listStyleFont.Count)
{
style.fontReal = new SLFont(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
style.fontReal.FromHash(listStyleFont[index]);
}
else
{
style.RemoveFont();
}
}
else
{
style.RemoveFont();
}
if (style.FillId != null)
{
style.HasFill = true;
index = (int)style.FillId.Value;
if (index >= 0 && index < listStyleFill.Count)
{
style.fillReal = new SLFill(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
style.fillReal.FromHash(listStyleFill[index]);
}
else
{
style.RemoveFill();
}
}
else
{
style.RemoveFill();
}
if (style.BorderId != null)
{
style.HasBorder = true;
index = (int)style.BorderId.Value;
if (index >= 0 && index < listStyleBorder.Count)
{
style.borderReal = new SLBorder(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
style.borderReal.FromHash(listStyleBorder[index]);
}
else
{
style.RemoveBorder();
}
}
else
{
style.RemoveBorder();
}
style.Sync();
}
/// <summary>
/// Get the cell's style. The default style is returned if cell doesn't have an existing style, or if the row or column indices are invalid.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <returns>The cell's style.</returns>
public SLStyle GetCellStyle(int RowIndex, int ColumnIndex)
{
bool bFound = false;
SLStyle style = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
SLCell c = slws.Cells[pt];
bFound = true;
style.FromHash(listStyle[(int)c.StyleIndex]);
}
}
if (!bFound)
{
style.FromHash(listStyle[0]);
}
return style;
}