/// <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; }