/// <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;
}
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
}
}
// 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>
/// Automatically fit row height according to cell contents.
/// </summary>
/// <param name="StartRowIndex">The row index of the starting row.</param>
/// <param name="EndRowIndex">The row index of the ending row.</param>
public void AutoFitRow(int StartRowIndex, int EndRowIndex)
{
int iStartRowIndex = 1, iEndRowIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (iStartRowIndex < 1) iStartRowIndex = 1;
if (iStartRowIndex > SLConstants.RowLimit) iStartRowIndex = SLConstants.RowLimit;
if (iEndRowIndex < 1) iEndRowIndex = 1;
if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit;
Dictionary<int, int> pixellength = this.AutoFitRowColumn(true, iStartRowIndex, iEndRowIndex);
double fResolution = 96.0;
using (System.Drawing.Bitmap bm = new System.Drawing.Bitmap(32, 32))
{
fResolution = (double)bm.VerticalResolution;
}
double fDefaultRowHeight = slws.SheetFormatProperties.DefaultRowHeight;
double fMinimumHeight = 0;
SLStyle style;
string sFontName;
double fFontSize;
bool bBold;
bool bItalic;
bool bStrike;
bool bUnderline;
System.Drawing.FontStyle drawstyle = System.Drawing.FontStyle.Regular;
System.Drawing.SizeF szf;
SLRowProperties rp;
double fRowHeight;
int iPixelLength;
foreach (int pixlenpt in pixellength.Keys)
{
iPixelLength = pixellength[pixlenpt];
if (iPixelLength > 0)
{
// height in points = number of pixels * 72 (points per inch) / resolution (DPI)
fRowHeight = (double)iPixelLength * 72.0 / fResolution;
if (slws.RowProperties.ContainsKey(pixlenpt))
{
rp = slws.RowProperties[pixlenpt];
// if the row has a style, we have to check if the resulting row height
// based on the typeface, boldness, italicise-ness and whatnot will change.
// Basically we're calculating a "default" row height based on the typeface
// set on the entire row.
style = new SLStyle();
style.FromHash(listStyle[(int)rp.StyleIndex]);
if (style.HasFont)
{
sFontName = SimpleTheme.MinorLatinFont;
fFontSize = SLConstants.DefaultFontSize;
bBold = false;
bItalic = false;
bStrike = false;
bUnderline = false;
drawstyle = System.Drawing.FontStyle.Regular;
if (style.fontReal.HasFontScheme)
{
if (style.fontReal.FontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont;
else if (style.fontReal.FontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont;
else if (style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName;
}
else
{
if (style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName;
}
if (style.fontReal.FontSize != null) fFontSize = style.fontReal.FontSize.Value;
if (style.fontReal.Bold != null && style.fontReal.Bold.Value) bBold = true;
if (style.fontReal.Italic != null && style.fontReal.Italic.Value) bItalic = true;
if (style.fontReal.Strike != null && style.fontReal.Strike.Value) bStrike = true;
if (style.fontReal.HasUnderline) bUnderline = true;
if (bBold) drawstyle |= System.Drawing.FontStyle.Bold;
if (bItalic) drawstyle |= System.Drawing.FontStyle.Italic;
if (bStrike) drawstyle |= System.Drawing.FontStyle.Strikeout;
if (bUnderline) drawstyle |= System.Drawing.FontStyle.Underline;
// any text will do. Apparently the height is the same regardless.
szf = SLTool.MeasureText("0123456789", sFontName, fFontSize, drawstyle);
fMinimumHeight = Math.Min(szf.Height, fDefaultRowHeight);
}
else
{
fMinimumHeight = fDefaultRowHeight;
}
if (fRowHeight > fMinimumHeight)
{
rp.Height = fRowHeight;
rp.CustomHeight = false;
slws.RowProperties[pixlenpt] = rp.Clone();
}
}
else
{
rp = new SLRowProperties(SimpleTheme.ThemeRowHeight);
rp.Height = fRowHeight;
rp.CustomHeight = false;
slws.RowProperties[pixlenpt] = rp.Clone();
}
}
else
{
// else we set autoheight. Meaning we set the default height for any
// existing rows.
if (slws.RowProperties.ContainsKey(pixlenpt))
{
rp = slws.RowProperties[pixlenpt];
rp.Height = SimpleTheme.ThemeRowHeight;
rp.CustomHeight = false;
slws.RowProperties[pixlenpt] = rp.Clone();
}
}
}
}
private List<SLC.SLDataSeries> FillChartDataSeries(string WorksheetName, int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool RowsAsDataSeries, bool ShowHiddenData)
{
List<SLC.SLDataSeries> series = new List<SLC.SLDataSeries>();
int i, j;
SLCell c;
SLCellPoint pt;
Dictionary<int, bool> HiddenRows = new Dictionary<int, bool>();
Dictionary<int, bool> HiddenColumns = new Dictionary<int, bool>();
Dictionary<SLCellPoint, SLCell> cellstore = new Dictionary<SLCellPoint, SLCell>();
#region GetCells
for (i = StartRowIndex; i <= EndRowIndex; ++i)
{
HiddenRows[i] = false;
}
for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
{
HiddenColumns[j] = false;
}
bool bFound = false;
string sWorksheetRelID = string.Empty;
if (WorksheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
{
bFound = false;
}
else
{
bFound = false;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(WorksheetName, StringComparison.OrdinalIgnoreCase))
{
bFound = true;
sWorksheetRelID = sheet.Id;
break;
}
}
}
if (bFound)
{
Dictionary<string, SLCellPoint> cellref = new Dictionary<string, SLCellPoint>();
for (i = StartRowIndex; i <= EndRowIndex; ++i)
{
for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
cellref[SLTool.ToCellReference(i, j)] = pt;
}
}
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sWorksheetRelID);
Row row;
Column column;
Cell cell;
if (!ShowHiddenData)
{
// running it twice because Row contains Cell classes and it's easier this way
using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Row))
{
row = (Row)oxr.LoadCurrentElement();
if (row.RowIndex != null)
{
foreach (var rowindex in HiddenRows.Keys)
{
if (row.RowIndex.Value == rowindex && row.Hidden != null && row.Hidden.Value)
{
HiddenRows[rowindex] = true;
}
}
}
}
}
}
}
using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Column))
{
if (!ShowHiddenData)
{
column = (Column)oxr.LoadCurrentElement();
foreach (var colindex in HiddenColumns.Keys)
{
if (column.Min <= colindex && colindex <= column.Max && column.Hidden != null && column.Hidden.Value)
{
HiddenColumns[colindex] = true;
}
}
}
}
else if (oxr.ElementType == typeof(Cell))
{
cell = (Cell)oxr.LoadCurrentElement();
if (cell.CellReference != null)
{
if (cellref.ContainsKey(cell.CellReference.Value))
{
c = new SLCell();
c.FromCell(cell);
cellstore[cellref[cell.CellReference.Value]] = c.Clone();
}
}
}
}
}
}
else
{
SLRowProperties rp;
SLColumnProperties cp;
if (!ShowHiddenData)
{
for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
{
if (slws.ColumnProperties.ContainsKey(j))
{
cp = slws.ColumnProperties[j];
if (cp.Hidden) HiddenColumns[j] = true;
}
}
}
for (i = StartRowIndex; i <= EndRowIndex; ++i)
{
if (!ShowHiddenData && slws.RowProperties.ContainsKey(i))
{
rp = slws.RowProperties[i];
if (rp.Hidden) HiddenRows[i] = true;
}
for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
cellstore[pt] = slws.Cells[pt].Clone();
}
}
}
}
#endregion
int index = 0;
int index2 = 0;
string sCellValue;
string sFormatCode;
SLC.SLDataSeries ser;
SLC.SLStringReference sr;
SLC.SLNumberReference nr;
SLStyle style;
bool bIsStringReference = true;
// we're going to assume that the format code applies to all in the "category" cells.
string sAxisFormatCode = string.Empty;
SLC.SLAxisDataSourceType cat = new SLC.SLAxisDataSourceType();
if (RowsAsDataSeries)
{
bIsStringReference = true;
sAxisFormatCode = SLConstants.NumberFormatGeneral;
pt = new SLCellPoint(StartRowIndex, StartColumnIndex + 1);
if (cellstore.ContainsKey(pt))
{
// dates are also numbers, so we lump it together
if (cellstore[pt].DataType == CellValues.Number)
{
bIsStringReference = false;
style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
style.FromHash(listStyle[(int)cellstore[pt].StyleIndex]);
this.TranslateStylesToStyleIds(ref style);
sAxisFormatCode = style.FormatCode;
}
}
sr = new SLC.SLStringReference();
nr = new SLC.SLNumberReference();
if (bIsStringReference)
{
cat.UseStringReference = true;
sr = new SLC.SLStringReference();
sr.WorksheetName = WorksheetName;
sr.StartRowIndex = StartRowIndex;
sr.StartColumnIndex = StartColumnIndex + 1;
sr.EndRowIndex = StartRowIndex;
sr.EndColumnIndex = EndColumnIndex;
sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, StartColumnIndex + 1, StartRowIndex, EndColumnIndex);
}
else
{
cat.UseNumberReference = true;
nr.WorksheetName = WorksheetName;
nr.StartRowIndex = StartRowIndex;
nr.StartColumnIndex = StartColumnIndex + 1;
nr.EndRowIndex = StartRowIndex;
nr.EndColumnIndex = EndColumnIndex;
nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, StartColumnIndex + 1, StartRowIndex, EndColumnIndex);
nr.NumberingCache.FormatCode = sAxisFormatCode;
}
index2 = 0;
// if the header row is hidden, I don't know what to do...
for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j)
{
if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j])
{
pt = new SLCellPoint(StartRowIndex, j);
sCellValue = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
if (bIsStringReference)
{
sr.Points.Add(new SLC.SLStringPoint()
{
Index = (uint)index2,
NumericValue = sCellValue
});
}
else
{
nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
{
Index = (uint)index2,
NumericValue = sCellValue
});
}
}
++index2;
}
}
if (bIsStringReference)
{
sr.PointCount = (uint)index2;
cat.StringReference = sr;
}
else
{
nr.NumberingCache.PointCount = (uint)index2;
cat.NumberReference = nr;
}
index = 0;
for (i = StartRowIndex + 1; i <= EndRowIndex; ++i)
{
if (HiddenRows.ContainsKey(i) && !HiddenRows[i])
{
ser = new SLC.SLDataSeries(SimpleTheme.listThemeColors);
ser.Index = (uint)index;
ser.Order = (uint)index;
ser.IsStringReference = true;
sr = new SLC.SLStringReference();
pt = new SLCellPoint(i, StartColumnIndex);
sCellValue = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
}
sr.WorksheetName = WorksheetName;
sr.StartRowIndex = i;
sr.StartColumnIndex = StartColumnIndex;
sr.EndRowIndex = i;
sr.EndColumnIndex = StartColumnIndex;
sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, i, StartColumnIndex);
sr.PointCount = 1;
sr.Points.Add(new SLC.SLStringPoint() { Index = 0, NumericValue = sCellValue });
ser.StringReference = sr;
ser.AxisData = cat.Clone();
ser.NumberData.UseNumberReference = true;
nr = new SLC.SLNumberReference();
nr.WorksheetName = WorksheetName;
nr.StartRowIndex = i;
nr.StartColumnIndex = StartColumnIndex + 1;
nr.EndRowIndex = i;
nr.EndColumnIndex = EndColumnIndex;
nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, i, StartColumnIndex + 1, i, EndColumnIndex);
nr.NumberingCache.FormatCode = SLConstants.NumberFormatGeneral;
index2 = 0;
for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j)
{
if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j])
{
pt = new SLCellPoint(i, j);
sCellValue = string.Empty;
sFormatCode = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
style.FromHash(listStyle[(int)c.StyleIndex]);
this.TranslateStylesToStyleIds(ref style);
if (style.HasNumberingFormat) sFormatCode = style.FormatCode;
nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
{
FormatCode = sFormatCode,
Index = (uint)index2,
NumericValue = sCellValue
});
}
++index2;
}
}
nr.NumberingCache.PointCount = (uint)index2;
ser.NumberData.NumberReference = nr;
series.Add(ser);
++index;
}
}
// end of rows as data series
}
else
{
bIsStringReference = true;
sAxisFormatCode = SLConstants.NumberFormatGeneral;
pt = new SLCellPoint(StartRowIndex + 1, StartColumnIndex);
if (cellstore.ContainsKey(pt))
{
// dates are also numbers, so we lump it together
if (cellstore[pt].DataType == CellValues.Number)
{
bIsStringReference = false;
style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
style.FromHash(listStyle[(int)cellstore[pt].StyleIndex]);
this.TranslateStylesToStyleIds(ref style);
sAxisFormatCode = style.FormatCode;
}
}
sr = new SLC.SLStringReference();
nr = new SLC.SLNumberReference();
if (bIsStringReference)
{
cat.UseStringReference = true;
sr.WorksheetName = WorksheetName;
sr.StartRowIndex = StartRowIndex + 1;
sr.StartColumnIndex = StartColumnIndex;
sr.EndRowIndex = EndRowIndex;
sr.EndColumnIndex = StartColumnIndex;
sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, StartColumnIndex, EndRowIndex, StartColumnIndex);
}
else
{
cat.UseNumberReference = true;
nr.WorksheetName = WorksheetName;
nr.StartRowIndex = StartRowIndex + 1;
nr.StartColumnIndex = StartColumnIndex;
nr.EndRowIndex = EndRowIndex;
nr.EndColumnIndex = StartColumnIndex;
nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, StartColumnIndex, EndRowIndex, StartColumnIndex);
nr.NumberingCache.FormatCode = sAxisFormatCode;
}
index2 = 0;
// if the header column is hidden, I don't know what to do...
for (i = StartRowIndex + 1; i <= EndRowIndex; ++i)
{
if (HiddenRows.ContainsKey(i) && !HiddenRows[i])
{
pt = new SLCellPoint(i, StartColumnIndex);
sCellValue = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
if (bIsStringReference)
{
sr.Points.Add(new SLC.SLStringPoint()
{
Index = (uint)index2,
NumericValue = sCellValue
});
}
else
{
nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
{
Index = (uint)index2,
NumericValue = sCellValue
});
}
}
++index2;
}
}
if (bIsStringReference)
{
sr.PointCount = (uint)index2;
cat.StringReference = sr;
}
else
{
nr.NumberingCache.PointCount = (uint)index2;
cat.NumberReference = nr;
}
index = 0;
for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j)
{
if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j])
{
ser = new SLC.SLDataSeries(SimpleTheme.listThemeColors);
ser.Index = (uint)index;
ser.Order = (uint)index;
ser.IsStringReference = true;
sr = new SLC.SLStringReference();
pt = new SLCellPoint(StartRowIndex, j);
sCellValue = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
}
sr.WorksheetName = WorksheetName;
sr.StartRowIndex = StartRowIndex;
sr.StartColumnIndex = j;
sr.EndRowIndex = StartRowIndex;
sr.EndColumnIndex = j;
sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, j);
sr.PointCount = 1;
sr.Points.Add(new SLC.SLStringPoint() { Index = 0, NumericValue = sCellValue });
ser.StringReference = sr;
ser.AxisData = cat.Clone();
ser.NumberData.UseNumberReference = true;
nr = new SLC.SLNumberReference();
nr.WorksheetName = WorksheetName;
nr.StartRowIndex = StartRowIndex + 1;
nr.StartColumnIndex = j;
nr.EndRowIndex = EndRowIndex;
nr.EndColumnIndex = j;
nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, j, EndRowIndex, j);
nr.NumberingCache.FormatCode = SLConstants.NumberFormatGeneral;
index2 = 0;
for (i = StartRowIndex + 1; i <= EndRowIndex; ++i)
{
if (HiddenRows.ContainsKey(i) && !HiddenRows[i])
{
pt = new SLCellPoint(i, j);
sCellValue = string.Empty;
sFormatCode = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
style.FromHash(listStyle[(int)c.StyleIndex]);
this.TranslateStylesToStyleIds(ref style);
if (style.HasNumberingFormat) sFormatCode = style.FormatCode;
nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
{
FormatCode = sFormatCode,
Index = (uint)index2,
NumericValue = sCellValue
});
}
++index2;
}
}
nr.NumberingCache.PointCount = (uint)index2;
ser.NumberData.NumberReference = nr;
series.Add(ser);
++index;
}
}
// end of columns as data series
}
return series;
}
/// <summary>
/// This returns a list of index with pixel lengths. Depending on the type,
/// the pixel length is for row heights or column widths
/// </summary>
/// <param name="IsRow"></param>
/// <param name="StartIndex"></param>
/// <param name="EndIndex"></param>
/// <param name="MaxPixelLength"></param>
/// <returns></returns>
internal Dictionary<int, int> AutoFitRowColumn(bool IsRow, int StartIndex, int EndIndex, int MaxPixelLength)
{
int i;
Dictionary<int, int> pixellength = new Dictionary<int, int>();
// initialise all to zero first. This also ensures the existence of a dictionary entry.
for (i = StartIndex; i <= EndIndex; ++i)
{
pixellength[i] = 0;
}
List<SLCellPoint> ptkeys = slws.Cells.Keys.ToList<SLCellPoint>();
SLCell c;
string sAutoFitSharedStringCacheKey;
string sAutoFitCacheKey;
double fCellValue;
SLRstType rst;
Text txt;
Run run;
FontSchemeValues vFontScheme;
int index;
SLStyle style = new SLStyle();
int iStyleIndex;
string sFontName;
double fFontSize;
bool bBold;
bool bItalic;
bool bStrike;
bool bUnderline;
System.Drawing.FontStyle drawstyle;
System.Drawing.Font ftUsable;
string sFormatCode;
string sDotNetFormatCode;
int iTextRotation;
System.Drawing.SizeF szf;
string sText;
float fWidth;
float fHeight;
int iPointIndex;
bool bSkipAdjustment;
SLCellPoint ptCheck;
// remove points that are part of merged cells
// Merged cells don't factor into autofitting.
// Start from end because we will be deleting points.
if (slws.MergeCells.Count > 0)
{
for (i = ptkeys.Count - 1; i >= 0; --i)
{
ptCheck = ptkeys[i];
foreach (SLMergeCell mc in slws.MergeCells)
{
if (mc.StartRowIndex <= ptCheck.RowIndex && ptCheck.RowIndex <= mc.EndRowIndex
&& mc.StartColumnIndex <= ptCheck.ColumnIndex && ptCheck.ColumnIndex <= mc.EndColumnIndex)
{
ptkeys.RemoveAt(i);
break;
}
}
}
}
HashSet<SLCellPoint> hsFilter = new HashSet<SLCellPoint>();
if (slws.HasAutoFilter)
{
for (i = slws.AutoFilter.StartColumnIndex; i <= slws.AutoFilter.EndColumnIndex; ++i)
{
hsFilter.Add(new SLCellPoint(slws.AutoFilter.StartRowIndex, i));
}
}
if (slws.Tables.Count > 0)
{
foreach (SLTable t in slws.Tables)
{
if (t.HasAutoFilter)
{
for (i = t.AutoFilter.StartColumnIndex; i <= t.AutoFilter.EndColumnIndex; ++i)
{
ptCheck = new SLCellPoint(t.AutoFilter.StartRowIndex, i);
if (!hsFilter.Contains(ptCheck))
{
hsFilter.Add(ptCheck);
}
}
}
}
}
// Excel seems to stop the maximum column pixel width at 2300 pixels (at least at 120 DPI).
// We need a bitmap of sufficient size because we're rendering the text and measuring it.
// 4096 pixels wide should cover the 2300 pixel thing. Note that this is also wider than
// typical screen monitors.
// 2048 pixels high should also cover most screen monitors' vertical height.
// If your text fills up the entire height of your screen, I would say your font size is
// too large...
// If you're doing this in some distant future where you can do spreadsheets on the
// freaking wall with Olympic pool sized screens, feel free to increase the dimensions.
using (System.Drawing.Bitmap bm = new System.Drawing.Bitmap(4096, 2048))
{
using (System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(bm))
{
foreach (SLCellPoint pt in ptkeys)
{
if (IsRow) iPointIndex = pt.RowIndex;
else iPointIndex = pt.ColumnIndex;
if (StartIndex <= iPointIndex && iPointIndex <= EndIndex)
{
c = slws.Cells[pt];
iStyleIndex = (int)c.StyleIndex;
// assume if the font cache contains the style index,
// the other caches also have it.
if (dictAutoFitFontCache.ContainsKey(iStyleIndex))
{
ftUsable = dictAutoFitFontCache[iStyleIndex];
sDotNetFormatCode = dictAutoFitFormatCodeCache[iStyleIndex];
sFormatCode = sDotNetFormatCode;
iTextRotation = dictAutoFitTextRotationCache[iStyleIndex];
}
else
{
style = new SLStyle();
style.FromHash(listStyle[iStyleIndex]);
#region Get style stuff
sFontName = SimpleTheme.MinorLatinFont;
fFontSize = SLConstants.DefaultFontSize;
bBold = false;
bItalic = false;
bStrike = false;
bUnderline = false;
drawstyle = System.Drawing.FontStyle.Regular;
if (style.HasFont)
{
if (style.fontReal.HasFontScheme)
{
if (style.fontReal.FontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont;
else if (style.fontReal.FontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont;
else if (style.fontReal.FontName != null && style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName;
}
else
{
if (style.fontReal.FontName != null && style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName;
}
if (style.fontReal.FontSize != null) fFontSize = style.fontReal.FontSize.Value;
if (style.fontReal.Bold != null && style.fontReal.Bold.Value) bBold = true;
if (style.fontReal.Italic != null && style.fontReal.Italic.Value) bItalic = true;
if (style.fontReal.Strike != null && style.fontReal.Strike.Value) bStrike = true;
if (style.fontReal.HasUnderline) bUnderline = true;
}
if (bBold) drawstyle |= System.Drawing.FontStyle.Bold;
if (bItalic) drawstyle |= System.Drawing.FontStyle.Italic;
if (bStrike) drawstyle |= System.Drawing.FontStyle.Strikeout;
if (bUnderline) drawstyle |= System.Drawing.FontStyle.Underline;
ftUsable = SLTool.GetUsableNormalFont(sFontName, fFontSize, drawstyle);
sFormatCode = style.FormatCode;
sDotNetFormatCode = SLTool.ToDotNetFormatCode(sFormatCode);
if (style.HasAlignment && style.alignReal.TextRotation != null)
{
iTextRotation = style.alignReal.TextRotation.Value;
}
else
{
iTextRotation = 0;
}
#endregion
dictAutoFitFontCache[iStyleIndex] = (System.Drawing.Font)ftUsable.Clone();
dictAutoFitFormatCodeCache[iStyleIndex] = sDotNetFormatCode;
dictAutoFitTextRotationCache[iStyleIndex] = iTextRotation;
}
sText = string.Empty;
fWidth = 0;
fHeight = 0;
// must set empty first! Used for checking if shared string and if should set into cache.
sAutoFitSharedStringCacheKey = string.Empty;
bSkipAdjustment = false;
if (c.DataType == CellValues.SharedString)
{
index = Convert.ToInt32(c.NumericValue);
sAutoFitSharedStringCacheKey = string.Format("{0}{1}{2}",
index.ToString(CultureInfo.InvariantCulture),
SLConstants.AutoFitCacheSeparator,
c.StyleIndex.ToString(CultureInfo.InvariantCulture));
if (dictAutoFitSharedStringCache.ContainsKey(sAutoFitSharedStringCacheKey))
{
fHeight = dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey].Height;
fWidth = dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey].Width;
bSkipAdjustment = true;
}
else if (index >= 0 && index < listSharedString.Count)
{
rst = new SLRstType();
rst.FromHash(listSharedString[index]);
if (rst.istrReal.ChildElements.Count == 1 && rst.istrReal.Text != null)
{
sText = rst.istrReal.Text.Text.TrimEnd();
sAutoFitCacheKey = string.Format("{0}{1}{2}", sText, SLConstants.AutoFitCacheSeparator, iStyleIndex.ToString(CultureInfo.InvariantCulture));
if (dictAutoFitTextCache.ContainsKey(sAutoFitCacheKey))
{
szf = dictAutoFitTextCache[sAutoFitCacheKey];
fHeight = szf.Height;
fWidth = szf.Width;
}
else
{
szf = SLTool.MeasureText(bm, g, sText, ftUsable);
fHeight = szf.Height;
fWidth = szf.Width;
dictAutoFitTextCache[sAutoFitCacheKey] = new System.Drawing.SizeF(fWidth, fHeight);
}
}
else
{
i = 0;
foreach (var child in rst.istrReal.ChildElements.Reverse())
{
if (child is Text || child is Run)
{
if (child is Text)
{
txt = (Text)child;
sText = txt.Text;
// the last element has the trailing spaces ignored. Hence the Reverse() above.
if (i == 0) sText = sText.TrimEnd();
szf = SLTool.MeasureText(bm, g, sText, ftUsable);
if (szf.Height > fHeight) fHeight = szf.Height;
fWidth += szf.Width;
}
else if (child is Run)
{
sText = string.Empty;
sFontName = (ftUsable.Name != null && ftUsable.Name.Length > 0) ? ftUsable.Name : SimpleTheme.MinorLatinFont;
fFontSize = ftUsable.SizeInPoints;
bBold = ((ftUsable.Style & System.Drawing.FontStyle.Bold) > 0) ? true : false;
bItalic = ((ftUsable.Style & System.Drawing.FontStyle.Italic) > 0) ? true : false;
bStrike = ((ftUsable.Style & System.Drawing.FontStyle.Strikeout) > 0) ? true : false;
bUnderline = ((ftUsable.Style & System.Drawing.FontStyle.Underline) > 0) ? true : false;
drawstyle = System.Drawing.FontStyle.Regular;
run = (Run)child;
sText = run.Text.Text;
vFontScheme = FontSchemeValues.None;
#region Run properties
if (run.RunProperties != null)
{
foreach (var grandchild in run.RunProperties.ChildElements)
{
if (grandchild is RunFont)
{
sFontName = ((RunFont)grandchild).Val;
}
else if (grandchild is FontSize)
{
fFontSize = ((FontSize)grandchild).Val;
}
else if (grandchild is Bold)
{
Bold b = (Bold)grandchild;
if (b.Val == null) bBold = true;
else bBold = b.Val.Value;
}
else if (grandchild is Italic)
{
Italic itlc = (Italic)grandchild;
if (itlc.Val == null) bItalic = true;
else bItalic = itlc.Val.Value;
}
else if (grandchild is Strike)
{
Strike strk = (Strike)grandchild;
if (strk.Val == null) bStrike = true;
else bStrike = strk.Val.Value;
}
else if (grandchild is Underline)
{
Underline und = (Underline)grandchild;
if (und.Val == null)
{
bUnderline = true;
}
else
{
if (und.Val.Value != UnderlineValues.None) bUnderline = true;
else bUnderline = false;
}
}
else if (grandchild is FontScheme)
{
vFontScheme = ((FontScheme)grandchild).Val;
}
}
}
#endregion
if (vFontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont;
else if (vFontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont;
if (bBold) drawstyle |= System.Drawing.FontStyle.Bold;
if (bItalic) drawstyle |= System.Drawing.FontStyle.Italic;
if (bStrike) drawstyle |= System.Drawing.FontStyle.Strikeout;
if (bUnderline) drawstyle |= System.Drawing.FontStyle.Underline;
// the last element has the trailing spaces ignored. Hence the Reverse() above.
if (i == 0) sText = sText.TrimEnd();
szf = SLTool.MeasureText(bm, g, sText, SLTool.GetUsableNormalFont(sFontName, fFontSize, drawstyle));
if (szf.Height > fHeight) fHeight = szf.Height;
fWidth += szf.Width;
}
++i;
}
}
}
}
}
else
{
if (c.DataType == CellValues.Number)
{
#region Numbers
if (sDotNetFormatCode.Length > 0)
{
if (c.CellText != null)
{
if (!double.TryParse(c.CellText, out fCellValue))
{
fCellValue = 0;
}
if (sFormatCode.Equals("@"))
{
sText = fCellValue.ToString(CultureInfo.InvariantCulture);
}
else
{
sText = SLTool.ToSampleDisplayFormat(fCellValue, sDotNetFormatCode);
}
}
else
{
if (sFormatCode.Equals("@"))
{
sText = c.NumericValue.ToString(CultureInfo.InvariantCulture);
}
else
{
sText = SLTool.ToSampleDisplayFormat(c.NumericValue, sDotNetFormatCode);
}
}
}
else
{
if (c.CellText != null)
{
if (!double.TryParse(c.CellText, out fCellValue))
{
fCellValue = 0;
}
sText = SLTool.ToSampleDisplayFormat(fCellValue, "G10");
}
else
{
sText = SLTool.ToSampleDisplayFormat(c.NumericValue, "G10");
}
}
#endregion
}
else if (c.DataType == CellValues.Boolean)
{
if (c.NumericValue > 0.5) sText = "TRUE";
else sText = "FALSE";
}
else
{
if (c.CellText != null) sText = c.CellText;
else sText = string.Empty;
}
sAutoFitCacheKey = string.Format("{0}{1}{2}", sText, SLConstants.AutoFitCacheSeparator, iStyleIndex.ToString(CultureInfo.InvariantCulture));
if (dictAutoFitTextCache.ContainsKey(sAutoFitCacheKey))
{
szf = dictAutoFitTextCache[sAutoFitCacheKey];
fHeight = szf.Height;
fWidth = szf.Width;
}
else
{
szf = SLTool.MeasureText(bm, g, sText, ftUsable);
fHeight = szf.Height;
fWidth = szf.Width;
dictAutoFitTextCache[sAutoFitCacheKey] = new System.Drawing.SizeF(fWidth, fHeight);
}
}
if (!bSkipAdjustment)
{
// Through empirical experimental data, it appears that there's still a bit of padding
// at the end of the column when autofitting column widths. I don't know how to
// calculate this padding. So I guess. I experimented with the widths of obvious
// characters such as a space, an exclamation mark, a period.
// Then I remember there's the documentation on the Open XML class property
// Column.Width, which says there's an extra 5 pixels, 2 pixels on the left/right
// and a pixel for the gridlines.
// Note that this padding appears to change depending on the font/typeface and
// font size used. (Haha... where have I seen this before...) So 5 pixels doesn't
// seem to work exactly. Or maybe it's wrong because the method of measuring isn't
// what Excel actually uses to measure the text.
// Since we're autofitting, it seems fitting (haha) that the column width is slightly
// larger to accomodate the text. So it's best to err on the larger side.
// Thus we add 7 instead of the "recommended" or "documented" 5 pixels, 1 extra pixel
// on the left and right.
fWidth += 7;
// I could also have used 8, but it might have been too much of an extra padding.
// The number 8 is a lucky number in Chinese culture. Goodness knows I need some
// luck figuring out what Excel is doing...
if (iTextRotation != 0)
{
szf = SLTool.CalculateOuterBoundsOfRotatedRectangle(fWidth, fHeight, iTextRotation);
fHeight = szf.Height;
fWidth = szf.Width;
}
// meaning the shared string portion was accessed (otherwise it'd be empty string)
if (sAutoFitSharedStringCacheKey.Length > 0)
{
dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey] = new System.Drawing.SizeF(fWidth, fHeight);
}
}
if (IsRow)
{
if (fHeight > MaxPixelLength) fHeight = MaxPixelLength;
if (pixellength[iPointIndex] < fHeight)
{
pixellength[iPointIndex] = Convert.ToInt32(Math.Ceiling(fHeight));
}
}
else
{
if (hsFilter.Contains(pt)) fWidth += SLConstants.AutoFilterPixelWidth;
if (fWidth > MaxPixelLength) fWidth = MaxPixelLength;
if (pixellength[iPointIndex] < fWidth)
{
pixellength[iPointIndex] = Convert.ToInt32(Math.Ceiling(fWidth));
}
}
}
}
// end of Graphics
}
}
return pixellength;
}
/// <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;
}