diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 1c06a19..47644cf 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -60,6 +60,10 @@ + + + + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 309952b..88cef1d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -133,5 +133,7 @@ IXLTable InsertTable(IEnumerable data, Boolean createTable); IXLTable InsertTable(IEnumerable data, String tableName); IXLTable InsertTable(IEnumerable data, String tableName, Boolean createTable); + + XLHyperlink Hyperlink { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index f0c30be..9148505 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -969,5 +969,37 @@ } public Boolean ShareString { get; set; } + + public Boolean SettingHyperlink = false; + private XLHyperlink hyperlink; + public XLHyperlink Hyperlink + { + get + { + if (hyperlink == null) + Hyperlink = new XLHyperlink(); + + return hyperlink; + } + set + { + hyperlink = value; + hyperlink.Worksheet = worksheet; + hyperlink.Cell = this; + if (worksheet.Hyperlinks.Where(hl => hl.Cell.Address == Address).Any()) + worksheet.Hyperlinks.Delete(Address); + + worksheet.Hyperlinks.Add(hyperlink); + + if (!SettingHyperlink) + { + if (Style.Font.FontColor == worksheet.Style.Font.FontColor) + Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Hyperlink); + + if (Style.Font.Underline == worksheet.Style.Font.Underline) + Style.Font.Underline = XLFontUnderlineValues.Single; + } + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/IXLHyperlinks.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/IXLHyperlinks.cs new file mode 100644 index 0000000..e298d95 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/IXLHyperlinks.cs @@ -0,0 +1,14 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLHyperlinks: IEnumerable + { + void Add(XLHyperlink hyperlink); + void Delete(XLHyperlink hyperlink); + void Delete(IXLAddress address); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs new file mode 100644 index 0000000..82468b9 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs @@ -0,0 +1,60 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public partial class XLHyperlink + { + internal XLHyperlink() + { } + + internal void SetValues(String address, String tooltip) + { + Tooltip = tooltip; + if (address[0] == '.') + { + externalAddress = new Uri(address, UriKind.Relative); + IsExternal = true; + } + else + { + Uri uri; + if(Uri.TryCreate(address, UriKind.Absolute, out uri)) + { + externalAddress = uri; + IsExternal = true; + } + else + { + internalAddress = address; + IsExternal = false; + } + } + } + + internal void SetValues(Uri uri, String tooltip) + { + Tooltip = tooltip; + externalAddress = uri; + IsExternal = true; + } + + internal void SetValues(IXLCell cell, String tooltip) + { + Tooltip = tooltip; + internalAddress = cell.Address.ToString(); + IsExternal = false; + } + + internal void SetValues(IXLRangeBase range, String tooltip) + { + Tooltip = tooltip; + internalAddress = range.RangeAddress.ToString(); + IsExternal = false; + } + + internal XLWorksheet Worksheet { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs new file mode 100644 index 0000000..edd6d09 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs @@ -0,0 +1,112 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public partial class XLHyperlink + { + public Boolean IsExternal { get; set; } + + public XLHyperlink(String address) + { + SetValues(address, String.Empty); + } + public XLHyperlink(String address, String tooltip) + { + SetValues(address, tooltip); + } + + public XLHyperlink(IXLCell cell) + { + SetValues(cell, String.Empty); + } + public XLHyperlink(IXLCell cell, String tooltip) + { + SetValues(cell, tooltip); + } + + public XLHyperlink(IXLRangeBase range) + { + SetValues(range, String.Empty); + } + public XLHyperlink(IXLRangeBase range, String tooltip) + { + SetValues(range, tooltip); + } + + public XLHyperlink(Uri uri) + { + SetValues(uri, String.Empty); + } + public XLHyperlink(Uri uri, String tooltip) + { + SetValues(uri, tooltip); + } + + private Uri externalAddress; + public Uri ExternalAddress + { + get + { + if (IsExternal) + return externalAddress; + else + return null; + } + set + { + externalAddress = value; + IsExternal = true; + } + } + + public IXLCell Cell { get; internal set; } + + private String internalAddress; + public String InternalAddress + { + get + { + if (IsExternal) + { + return null; + } + else + { + if (internalAddress.Contains('!')) + { + if (internalAddress[0] != '\'') + return String.Format("'{0}'!{1}", internalAddress.Substring(0, internalAddress.IndexOf('!')), internalAddress.Substring(internalAddress.IndexOf('!') + 1)); + else + return internalAddress; + } + else + { + return String.Format("'{0}'!{1}", Worksheet.Name, internalAddress); + } + } + } + set + { + internalAddress = value; + IsExternal = false; + } + } + + public String Tooltip { get; set; } + public void Delete() + { + if (Cell != null) + { + Worksheet.Hyperlinks.Delete(Cell.Address); + if (Cell.Style.Font.FontColor.Equals(XLColor.FromTheme(XLThemeColor.Hyperlink))) + Cell.Style.Font.FontColor = Worksheet.Style.Font.FontColor; + + if (Cell.Style.Font.Underline != Worksheet.Style.Font.Underline) + Cell.Style.Font.Underline = Worksheet.Style.Font.Underline; + } + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlinks.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlinks.cs new file mode 100644 index 0000000..430409b --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlinks.cs @@ -0,0 +1,36 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLHyperlinks: IXLHyperlinks + { + private Dictionary hyperlinks = new Dictionary(); + public IEnumerator GetEnumerator() + { + return hyperlinks.Values.GetEnumerator(); + } + + System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + { + return GetEnumerator(); + } + + public void Add(XLHyperlink hyperlink) + { + hyperlinks.Add(hyperlink.Cell.Address, hyperlink); + } + + public void Delete(XLHyperlink hyperlink) + { + hyperlinks.Remove(hyperlink.Cell.Address); + } + + public void Delete(IXLAddress address) + { + hyperlinks.Remove(address); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLTheme.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLTheme.cs index a65ba76..6e760a1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLTheme.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLTheme.cs @@ -18,6 +18,7 @@ IXLColor Accent4 { get; set; } IXLColor Accent5 { get; set; } IXLColor Accent6 { get; set; } - + IXLColor Hyperlink { get; set; } + IXLColor FollowedHyperlink { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 8799bed..c8235f2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -249,5 +249,6 @@ IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName, Int32 position); IXLRange RangeUsed(); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 3606e1e..1b52c86 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -154,5 +154,7 @@ /// If false the cell's text will not be shared and stored as an inline value. /// Boolean ShareString { set; } + + IXLHyperlinks Hyperlinks { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index a206dc9..bbc5820 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -858,5 +858,18 @@ Cells().ForEach(c => c.ShareString = value); } } + + public IXLHyperlinks Hyperlinks + { + get + { + IXLHyperlinks hyperlinks = new XLHyperlinks(); + var hls = from hl in Worksheet.Hyperlinks + where Contains(hl.Cell.AsRange()) + select hl; + hls.ForEach(hl => hyperlinks.Add(hl)); + return hyperlinks; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/Colors/IXLColor.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/Colors/IXLColor.cs index e4b30e3..a88bcf5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/Colors/IXLColor.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/Colors/IXLColor.cs @@ -18,7 +18,9 @@ Accent3, Accent4, Accent5, - Accent6 + Accent6, + Hyperlink, + FollowedHyperlink } public interface IXLColor: IEquatable { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs index c0b0642..e82d745 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs @@ -11,5 +11,6 @@ IEnumerable Styles { get; } Boolean UpdatingStyle { get; set; } IXLStyle InnerStyle { get; set; } + //Boolean IsDefault { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index dc1f500..717a466 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -220,6 +220,47 @@ return retVal; } + public new IXLRangeColumn Column(Int32 column) + { + return DataRange.Column(column); + } + public new IXLRangeColumn Column(String column) + { + if (XLAddress.IsValidColumn(column)) + { + Int32 coNum = XLAddress.GetColumnNumberFromLetter(column); + if (coNum > ColumnCount()) + { + return DataRange.Column(GetFieldIndex(column) + 1); + } + else + { + return DataRange.Column(coNum); + } + } + else + { + return DataRange.Column(GetFieldIndex(column) + 1); + } + } + + public new IXLRangeColumns Columns() + { + return DataRange.Columns(); + } + public new IXLRangeColumns Columns(Int32 firstColumn, Int32 lastColumn) + { + return DataRange.Columns(firstColumn, lastColumn); + } + public new IXLRangeColumns Columns(String firstColumn, String lastColumn) + { + return DataRange.Columns(firstColumn, lastColumn); + } + public new IXLRangeColumns Columns(String columns) + { + return DataRange.Columns(columns); + } + public IXLTableField Field(String fieldName) { return Field(GetFieldIndex(fieldName)); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs index 33f0a13..aa266ec 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -112,6 +112,28 @@ } } + public static Boolean IsValidColumn(String column) + { + if (StringExtensions.IsNullOrWhiteSpace(column) || column.Length > 3) + { + return false; + } + else + { + Boolean retVal = true; + String theColumn = column.ToUpper(); + for (Int32 i = 1; i <= column.Length; i++) + { + if (theColumn[i] < 'A' || theColumn[i] > 'Z' || (i == 3 && theColumn[i] > 'D')) + { + retVal = false; + break; + } + } + return retVal; + } + } + /// /// Gets the column letter of a given column number. /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLTheme.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLTheme.cs index b30a81b..8748760 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLTheme.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLTheme.cs @@ -18,5 +18,7 @@ public IXLColor Accent4 { get; set; } public IXLColor Accent5 { get; set; } public IXLColor Accent6 { get; set; } + public IXLColor Hyperlink { get; set; } + public IXLColor FollowedHyperlink { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index f14bff7..fbfaab5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -49,6 +49,8 @@ Accent4 = XLColor.FromHtml("#FF8064A2"), Accent5 = XLColor.FromHtml("#FF4BACC6"), Accent6 = XLColor.FromHtml("#FFF79646"), + Hyperlink = XLColor.FromHtml("#FF0000FF"), + FollowedHyperlink = XLColor.FromHtml("#FF800080") }; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 05ae786..e824118 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -104,7 +104,7 @@ Sheet dSheet = ((Sheet)sheet); WorksheetPart worksheetPart = (WorksheetPart)dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id); - + var sheetName = dSheet.Name; var ws = (XLWorksheet)Worksheets.Add(sheetName); @@ -116,21 +116,7 @@ if (sheetFormatProperties.DefaultColumnWidth != null) ws.ColumnWidth = sheetFormatProperties.DefaultColumnWidth; - var sheetView = (SheetView)worksheetPart.Worksheet.Descendants().FirstOrDefault(); - if (sheetView != null) - { - var pane = (Pane)sheetView.Descendants().FirstOrDefault(); - if (pane != null) - { - if (pane.State != null && (pane.State == PaneStateValues.FrozenSplit || pane.State == PaneStateValues.Frozen)) - { - if (pane.HorizontalSplit != null) - ws.SheetView.SplitColumn = (Int32)pane.HorizontalSplit.Value; - if (pane.VerticalSplit != null) - ws.SheetView.SplitRow = (Int32)pane.VerticalSplit.Value; - } - } - } + LoadSheetViews(worksheetPart, ws); foreach (var mCell in worksheetPart.Worksheet.Descendants()) { @@ -138,6 +124,7 @@ ws.Range(mergeCell.Reference).Merge(); } + #region LoadColumns Column wsDefaultColumn = null; var defaultColumns = worksheetPart.Worksheet.Descendants().Where(c => c.Max == XLWorksheet.MaxNumberOfColumns); if (defaultColumns.Count() > 0) @@ -182,7 +169,9 @@ } } } + #endregion + #region LoadRows foreach (var row in worksheetPart.Worksheet.Descendants()) //.Where(r => r.CustomFormat != null && r.CustomFormat).Select(r => r)) { var xlRow = (XLRow)ws.Row((Int32)row.RowIndex.Value, false); @@ -216,7 +205,9 @@ } } } + #endregion + #region LoadCells foreach (var cell in worksheetPart.Worksheet.Descendants()) { var dCell = (Cell)cell; @@ -293,9 +284,10 @@ ws.Cell(dCell.CellReference).Value = Double.Parse(dCell.CellValue.Text, CultureInfo.InvariantCulture); ws.Cell(dCell.CellReference).Style.NumberFormat.NumberFormatId = Int32.Parse(numberFormatId); } - - } + #endregion + + #region LoadTables foreach (var tablePart in worksheetPart.TableDefinitionParts) { var dTable = (Table)tablePart.Table; @@ -333,145 +325,24 @@ xlTable.Field(tableColumn.Name.Value).TotalsRowLabel = tableColumn.TotalsRowLabel.Value; } } - var printOptionsQuery = worksheetPart.Worksheet.Descendants(); - if (printOptionsQuery.Count() > 0) - { - var printOptions = (PrintOptions)printOptionsQuery.First(); - if (printOptions.GridLines != null) - ws.PageSetup.ShowGridlines = printOptions.GridLines; - if (printOptions.HorizontalCentered != null) - ws.PageSetup.CenterHorizontally = printOptions.HorizontalCentered; - if (printOptions.VerticalCentered != null) - ws.PageSetup.CenterVertically = printOptions.VerticalCentered; - if (printOptions.Headings != null) - ws.PageSetup.ShowRowAndColumnHeadings = printOptions.Headings; - } + #endregion - var pageMarginsQuery = worksheetPart.Worksheet.Descendants(); - if (pageMarginsQuery.Count() > 0) - { - var pageMargins = (PageMargins)pageMarginsQuery.First(); - if (pageMargins.Bottom != null) - ws.PageSetup.Margins.Bottom = pageMargins.Bottom; - if (pageMargins.Footer != null) - ws.PageSetup.Margins.Footer = pageMargins.Footer; - if (pageMargins.Header != null) - ws.PageSetup.Margins.Header = pageMargins.Header; - if (pageMargins.Left != null) - ws.PageSetup.Margins.Left = pageMargins.Left; - if (pageMargins.Right != null) - ws.PageSetup.Margins.Right = pageMargins.Right; - if (pageMargins.Top != null) - ws.PageSetup.Margins.Top = pageMargins.Top; - } + + LoadHyperlinks(worksheetPart, ws); - var pageSetupQuery = worksheetPart.Worksheet.Descendants(); - if (pageSetupQuery.Count() > 0) - { - var pageSetup = (PageSetup)pageSetupQuery.First(); - if (pageSetup.PaperSize != null) - ws.PageSetup.PaperSize = (XLPaperSize)Int32.Parse(pageSetup.PaperSize.InnerText); - if (pageSetup.Scale != null) - { - ws.PageSetup.Scale = Int32.Parse(pageSetup.Scale.InnerText); - } - else - { - if (pageSetup.FitToWidth != null) - ws.PageSetup.PagesWide = Int32.Parse(pageSetup.FitToWidth.InnerText); - if (pageSetup.FitToHeight != null) - ws.PageSetup.PagesTall = Int32.Parse(pageSetup.FitToHeight.InnerText); - } - if (pageSetup.PageOrder != null) - ws.PageSetup.PageOrder = pageOrderValues.Single(p => p.Value == pageSetup.PageOrder).Key; - if (pageSetup.Orientation != null) - ws.PageSetup.PageOrientation = pageOrientationValues.Single(p => p.Value == pageSetup.Orientation).Key; - if (pageSetup.BlackAndWhite != null) - ws.PageSetup.BlackAndWhite = pageSetup.BlackAndWhite; - if (pageSetup.Draft != null) - ws.PageSetup.DraftQuality = pageSetup.Draft; - if (pageSetup.CellComments != null) - ws.PageSetup.ShowComments = showCommentsValues.Single(sc => sc.Value == pageSetup.CellComments).Key; - if (pageSetup.Errors != null) - ws.PageSetup.PrintErrorValue = printErrorValues.Single(p => p.Value == pageSetup.Errors).Key; - if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = Int32.Parse(pageSetup.HorizontalDpi.InnerText); - if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = Int32.Parse(pageSetup.VerticalDpi.InnerText); - if (pageSetup.FirstPageNumber != null) ws.PageSetup.FirstPageNumber = Int32.Parse(pageSetup.FirstPageNumber.InnerText); - } + LoadPrintOptions(worksheetPart, ws); - var headerFooters = worksheetPart.Worksheet.Descendants(); - if (headerFooters.Count() > 0) - { - var headerFooter = (HeaderFooter)headerFooters.First(); - if (headerFooter.AlignWithMargins != null) - ws.PageSetup.AlignHFWithMargins = headerFooter.AlignWithMargins; - if (headerFooter.ScaleWithDoc != null) - ws.PageSetup.ScaleHFWithDocument = headerFooter.ScaleWithDoc; + LoadPageMargins(worksheetPart, ws); - // Footers - var xlFooter = (XLHeaderFooter)ws.PageSetup.Footer; - var evenFooter = (EvenFooter)headerFooter.EvenFooter; - if (evenFooter != null) - xlFooter.SetInnerText(XLHFOccurrence.EvenPages, evenFooter.Text); - var oddFooter = (OddFooter)headerFooter.OddFooter; - if (oddFooter != null) - xlFooter.SetInnerText(XLHFOccurrence.OddPages, oddFooter.Text); - var firstFooter = (FirstFooter)headerFooter.FirstFooter; - if (firstFooter != null) - xlFooter.SetInnerText(XLHFOccurrence.FirstPage, firstFooter.Text); - // Headers - var xlHeader = (XLHeaderFooter)ws.PageSetup.Header; - var evenHeader = (EvenHeader)headerFooter.EvenHeader; - if (evenHeader != null) - xlHeader.SetInnerText(XLHFOccurrence.EvenPages, evenHeader.Text); - var oddHeader = (OddHeader)headerFooter.OddHeader; - if (oddHeader != null) - xlHeader.SetInnerText(XLHFOccurrence.OddPages, oddHeader.Text); - var firstHeader = (FirstHeader)headerFooter.FirstHeader; - if (firstHeader != null) - xlHeader.SetInnerText(XLHFOccurrence.FirstPage, firstHeader.Text); - } + LoadPageSetup(worksheetPart, ws); - var sheetProperties = worksheetPart.Worksheet.Descendants(); - if (sheetProperties.Count() > 0) - { - var sheetProperty = (SheetProperties)sheetProperties.First(); - if (sheetProperty.OutlineProperties != null) - { - if (sheetProperty.OutlineProperties.SummaryBelow != null) - { - ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow ? - XLOutlineSummaryVLocation.Bottom : XLOutlineSummaryVLocation.Top; - } + LoadHeaderFooter(worksheetPart, ws); - if (sheetProperty.OutlineProperties.SummaryRight != null) - { - ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight ? - XLOutlineSummaryHLocation.Right : XLOutlineSummaryHLocation.Left; - } - } - } + LoadSheetProperties(worksheetPart, ws); - var rowBreaksList = worksheetPart.Worksheet.Descendants(); - if (rowBreaksList.Count() > 0) - { - var rowBreaks = (RowBreaks)rowBreaksList.First(); - foreach (var rowBreak in rowBreaks.Descendants()) - { - ws.PageSetup.RowBreaks.Add(Int32.Parse(rowBreak.Id.InnerText)); - } - } + LoadRowBreaks(worksheetPart, ws); - var columnBreaksList = worksheetPart.Worksheet.Descendants(); - if (columnBreaksList.Count() > 0) - { - var columnBreaks = (ColumnBreaks)columnBreaksList.First(); - foreach (var columnBreak in columnBreaks.Descendants()) - { - if (columnBreak.Id != null) - ws.PageSetup.ColumnBreaks.Add(Int32.Parse(columnBreak.Id.InnerText)); - } - } + LoadColumnBreaks(worksheetPart, ws); } var workbook = (Workbook)dSpreadsheet.WorkbookPart.Workbook; @@ -517,8 +388,210 @@ } } } + } + private void LoadHyperlinks(WorksheetPart worksheetPart, XLWorksheet ws) + { + var hyperlinkDictionary = new Dictionary(); + if (worksheetPart.HyperlinkRelationships != null) + hyperlinkDictionary = worksheetPart.HyperlinkRelationships.ToDictionary(hr => hr.Id, hr => hr.Uri); + + var hyperlinkList = worksheetPart.Worksheet.Descendants(); + if (hyperlinkList.Count() > 0) + { + var hyperlinks = (Hyperlinks)hyperlinkList.First(); + foreach (var hl in hyperlinks.Descendants()) + { + String tooltip = hl.Tooltip != null ? tooltip = hl.Tooltip.Value : tooltip = String.Empty; + var xlCell = (XLCell)ws.CellFast(hl.Reference.Value); + xlCell.SettingHyperlink = true; + if (hl.Id != null) + xlCell.Hyperlink = new XLHyperlink(hyperlinkDictionary[hl.Id], tooltip); + else + xlCell.Hyperlink = new XLHyperlink(hl.Location.Value, tooltip); + xlCell.SettingHyperlink = false; + } + } + } + private void LoadColumnBreaks(WorksheetPart worksheetPart, XLWorksheet ws) + { + var columnBreaksList = worksheetPart.Worksheet.Descendants(); + if (columnBreaksList.Count() > 0) + { + var columnBreaks = (ColumnBreaks)columnBreaksList.First(); + foreach (var columnBreak in columnBreaks.Descendants()) + { + if (columnBreak.Id != null) + ws.PageSetup.ColumnBreaks.Add(Int32.Parse(columnBreak.Id.InnerText)); + } + } + } + + private void LoadRowBreaks(WorksheetPart worksheetPart, XLWorksheet ws) + { + var rowBreaksList = worksheetPart.Worksheet.Descendants(); + if (rowBreaksList.Count() > 0) + { + var rowBreaks = (RowBreaks)rowBreaksList.First(); + foreach (var rowBreak in rowBreaks.Descendants()) + { + ws.PageSetup.RowBreaks.Add(Int32.Parse(rowBreak.Id.InnerText)); + } + } + } + + private void LoadSheetProperties(WorksheetPart worksheetPart, XLWorksheet ws) + { + var sheetProperties = worksheetPart.Worksheet.Descendants(); + if (sheetProperties.Count() > 0) + { + var sheetProperty = (SheetProperties)sheetProperties.First(); + if (sheetProperty.OutlineProperties != null) + { + if (sheetProperty.OutlineProperties.SummaryBelow != null) + { + ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow ? + XLOutlineSummaryVLocation.Bottom : XLOutlineSummaryVLocation.Top; + } + + if (sheetProperty.OutlineProperties.SummaryRight != null) + { + ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight ? + XLOutlineSummaryHLocation.Right : XLOutlineSummaryHLocation.Left; + } + } + } + } + + private void LoadHeaderFooter(WorksheetPart worksheetPart, XLWorksheet ws) + { + var headerFooters = worksheetPart.Worksheet.Descendants(); + if (headerFooters.Count() > 0) + { + var headerFooter = (HeaderFooter)headerFooters.First(); + if (headerFooter.AlignWithMargins != null) + ws.PageSetup.AlignHFWithMargins = headerFooter.AlignWithMargins; + if (headerFooter.ScaleWithDoc != null) + ws.PageSetup.ScaleHFWithDocument = headerFooter.ScaleWithDoc; + + // Footers + var xlFooter = (XLHeaderFooter)ws.PageSetup.Footer; + var evenFooter = (EvenFooter)headerFooter.EvenFooter; + if (evenFooter != null) + xlFooter.SetInnerText(XLHFOccurrence.EvenPages, evenFooter.Text); + var oddFooter = (OddFooter)headerFooter.OddFooter; + if (oddFooter != null) + xlFooter.SetInnerText(XLHFOccurrence.OddPages, oddFooter.Text); + var firstFooter = (FirstFooter)headerFooter.FirstFooter; + if (firstFooter != null) + xlFooter.SetInnerText(XLHFOccurrence.FirstPage, firstFooter.Text); + // Headers + var xlHeader = (XLHeaderFooter)ws.PageSetup.Header; + var evenHeader = (EvenHeader)headerFooter.EvenHeader; + if (evenHeader != null) + xlHeader.SetInnerText(XLHFOccurrence.EvenPages, evenHeader.Text); + var oddHeader = (OddHeader)headerFooter.OddHeader; + if (oddHeader != null) + xlHeader.SetInnerText(XLHFOccurrence.OddPages, oddHeader.Text); + var firstHeader = (FirstHeader)headerFooter.FirstHeader; + if (firstHeader != null) + xlHeader.SetInnerText(XLHFOccurrence.FirstPage, firstHeader.Text); + } + } + + private void LoadPageSetup(WorksheetPart worksheetPart, XLWorksheet ws) + { + var pageSetupQuery = worksheetPart.Worksheet.Descendants(); + if (pageSetupQuery.Count() > 0) + { + var pageSetup = (PageSetup)pageSetupQuery.First(); + if (pageSetup.PaperSize != null) + ws.PageSetup.PaperSize = (XLPaperSize)Int32.Parse(pageSetup.PaperSize.InnerText); + if (pageSetup.Scale != null) + { + ws.PageSetup.Scale = Int32.Parse(pageSetup.Scale.InnerText); + } + else + { + if (pageSetup.FitToWidth != null) + ws.PageSetup.PagesWide = Int32.Parse(pageSetup.FitToWidth.InnerText); + if (pageSetup.FitToHeight != null) + ws.PageSetup.PagesTall = Int32.Parse(pageSetup.FitToHeight.InnerText); + } + if (pageSetup.PageOrder != null) + ws.PageSetup.PageOrder = pageOrderValues.Single(p => p.Value == pageSetup.PageOrder).Key; + if (pageSetup.Orientation != null) + ws.PageSetup.PageOrientation = pageOrientationValues.Single(p => p.Value == pageSetup.Orientation).Key; + if (pageSetup.BlackAndWhite != null) + ws.PageSetup.BlackAndWhite = pageSetup.BlackAndWhite; + if (pageSetup.Draft != null) + ws.PageSetup.DraftQuality = pageSetup.Draft; + if (pageSetup.CellComments != null) + ws.PageSetup.ShowComments = showCommentsValues.Single(sc => sc.Value == pageSetup.CellComments).Key; + if (pageSetup.Errors != null) + ws.PageSetup.PrintErrorValue = printErrorValues.Single(p => p.Value == pageSetup.Errors).Key; + if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = Int32.Parse(pageSetup.HorizontalDpi.InnerText); + if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = Int32.Parse(pageSetup.VerticalDpi.InnerText); + if (pageSetup.FirstPageNumber != null) ws.PageSetup.FirstPageNumber = Int32.Parse(pageSetup.FirstPageNumber.InnerText); + } + } + + private void LoadPageMargins(WorksheetPart worksheetPart, XLWorksheet ws) + { + var pageMarginsQuery = worksheetPart.Worksheet.Descendants(); + if (pageMarginsQuery.Count() > 0) + { + var pageMargins = (PageMargins)pageMarginsQuery.First(); + if (pageMargins.Bottom != null) + ws.PageSetup.Margins.Bottom = pageMargins.Bottom; + if (pageMargins.Footer != null) + ws.PageSetup.Margins.Footer = pageMargins.Footer; + if (pageMargins.Header != null) + ws.PageSetup.Margins.Header = pageMargins.Header; + if (pageMargins.Left != null) + ws.PageSetup.Margins.Left = pageMargins.Left; + if (pageMargins.Right != null) + ws.PageSetup.Margins.Right = pageMargins.Right; + if (pageMargins.Top != null) + ws.PageSetup.Margins.Top = pageMargins.Top; + } + } + + private void LoadPrintOptions(WorksheetPart worksheetPart, XLWorksheet ws) + { + var printOptionsQuery = worksheetPart.Worksheet.Descendants(); + if (printOptionsQuery.Count() > 0) + { + var printOptions = (PrintOptions)printOptionsQuery.First(); + if (printOptions.GridLines != null) + ws.PageSetup.ShowGridlines = printOptions.GridLines; + if (printOptions.HorizontalCentered != null) + ws.PageSetup.CenterHorizontally = printOptions.HorizontalCentered; + if (printOptions.VerticalCentered != null) + ws.PageSetup.CenterVertically = printOptions.VerticalCentered; + if (printOptions.Headings != null) + ws.PageSetup.ShowRowAndColumnHeadings = printOptions.Headings; + } + } + + private void LoadSheetViews(WorksheetPart worksheetPart, XLWorksheet ws) + { + var sheetView = (SheetView)worksheetPart.Worksheet.Descendants().FirstOrDefault(); + if (sheetView != null) + { + var pane = (Pane)sheetView.Descendants().FirstOrDefault(); + if (pane != null) + { + if (pane.State != null && (pane.State == PaneStateValues.FrozenSplit || pane.State == PaneStateValues.Frozen)) + { + if (pane.HorizontalSplit != null) + ws.SheetView.SplitColumn = (Int32)pane.HorizontalSplit.Value; + if (pane.VerticalSplit != null) + ws.SheetView.SplitRow = (Int32)pane.VerticalSplit.Value; + } + } + } } private void SetProperties(SpreadsheetDocument dSpreadsheet) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 5bb1e7b..e834dde 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -355,27 +355,17 @@ foreach (var worksheet in Worksheets.Cast().OrderBy(w=>w.Position)) { WorksheetPart worksheetPart; - var sheets = workbookPart.Workbook.Sheets.Elements(); if (workbookPart.Parts.Where(p => p.RelationshipId == worksheet.RelId).Any()) { worksheetPart = (WorksheetPart)workbookPart.GetPartById(worksheet.RelId); var wsPartsToRemove = worksheetPart.TableDefinitionParts.ToList(); wsPartsToRemove.ForEach(tdp=>worksheetPart.DeletePart(tdp)); - //foreach (var tdp in worksheetPart.TableDefinitionParts) - //{ - // worksheetPart.DeletePart(tdp); - // var w = workbookPart.Parts.Where(s => s.RelationshipId == tdp.RelationshipType); - // if (w.Count() > 0) - // { - // var wsPartToRemove = w.Single(); - // workbookPart.DeletePart(wsPartToRemove.OpenXmlPart); - // } - //} } else { worksheetPart = workbookPart.AddNewPart(worksheet.RelId); } + GenerateWorksheetPartContent(worksheetPart, worksheet); } @@ -1551,7 +1541,7 @@ worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight = xlWorksheet.RowHeight; worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = xlWorksheet.ColumnWidth; - worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true; + //worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true; if (maxOutlineColumn > 0) worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = (byte)maxOutlineColumn; @@ -1738,23 +1728,33 @@ if (maxColumn > 0) row.Spans = new ListValue() { InnerText = "1:" + maxColumn.ToString() }; + row.Height = null; + row.CustomHeight = null; + row.Hidden = null; + row.StyleIndex = null; + row.CustomFormat = null; if (xlWorksheet.Internals.RowsCollection.ContainsKey(distinctRow)) { var thisRow = xlWorksheet.Internals.RowsCollection[distinctRow]; - //var thisRowStyleString = thisRow.Style.ToString(); - row.Height = thisRow.Height; - row.CustomHeight = true; - row.StyleIndex = sharedStyles[thisRow.Style].StyleId; - row.CustomFormat = !thisRow.Style.Equals(xlWorksheet.Style); + if (thisRow.Height != xlWorksheet.RowHeight) + { + row.Height = thisRow.Height; + row.CustomHeight = true; + } + if (!thisRow.Style.Equals(xlWorksheet.Style)) + { + row.StyleIndex = sharedStyles[thisRow.Style].StyleId; + row.CustomFormat = true; + } if (thisRow.IsHidden) row.Hidden = true; if (thisRow.Collapsed) row.Collapsed = true; if (thisRow.OutlineLevel > 0) row.OutlineLevel = (byte)thisRow.OutlineLevel; } else { - row.Height = xlWorksheet.RowHeight; - row.CustomHeight = true; - row.Hidden = false; + //row.Height = xlWorksheet.RowHeight; + //row.CustomHeight = true; + //row.Hidden = false; } List cellsToRemove = new List(); @@ -1913,7 +1913,66 @@ var phoneticProperties = worksheetPart.Worksheet.Elements().FirstOrDefault(); - var hyperlinks = worksheetPart.Worksheet.Elements().FirstOrDefault(); + + + #region Hyperlinks + Hyperlinks hyperlinks = null; + var relToRemove = worksheetPart.HyperlinkRelationships.ToList(); + relToRemove.ForEach(h => worksheetPart.DeleteReferenceRelationship(h)); + if (xlWorksheet.Hyperlinks.Count() == 0) + { + worksheetPart.Worksheet.RemoveAllChildren(); + } + else + { + worksheetPart.Worksheet.Elements().FirstOrDefault(); + if (worksheetPart.Worksheet.Elements().Count() == 0) + { + OpenXmlElement previousElement; + if (phoneticProperties != null) + previousElement = phoneticProperties; + else if (mergeCells != null) + previousElement = mergeCells; + else if (customSheetViews != null) + previousElement = customSheetViews; + else if (autoFilter != null) + previousElement = autoFilter; + else if (sheetData != null) + previousElement = sheetData; + else if (columns != null) + previousElement = columns; + else + previousElement = worksheetPart.Worksheet.SheetFormatProperties; + + worksheetPart.Worksheet.InsertAfter(new Hyperlinks(), previousElement); + } + + hyperlinks = worksheetPart.Worksheet.Elements().First(); + hyperlinks.RemoveAllChildren(); + foreach (var hl in xlWorksheet.Hyperlinks) + { + Hyperlink hyperlink; + if (hl.IsExternal) + { + String rId = relId.GetNext(RelType.Workbook); + hyperlink = new Hyperlink() { Reference = hl.Cell.Address.ToString(), Id = rId }; + worksheetPart.AddHyperlinkRelationship(hl.ExternalAddress, true, rId); + } + else + { + hyperlink = new Hyperlink() + { + Reference = hl.Cell.Address.ToString(), + Location = hl.InternalAddress, + Display = hl.Cell.GetFormattedString() + }; + } + if (!StringExtensions.IsNullOrWhiteSpace(hl.Tooltip)) + hyperlink.Tooltip = hl.Tooltip; + hyperlinks.Append(hyperlink); + } + } + #endregion #region PrintOptions PrintOptions printOptions = null; @@ -2431,12 +2490,12 @@ accent6Color1.Append(rgbColorModelHex8); A.Hyperlink hyperlink1 = new A.Hyperlink(); - A.RgbColorModelHex rgbColorModelHex9 = new A.RgbColorModelHex() { Val = "0000FF" }; + A.RgbColorModelHex rgbColorModelHex9 = new A.RgbColorModelHex() { Val = Theme.Hyperlink.Color.ToHex().Substring(2) }; hyperlink1.Append(rgbColorModelHex9); A.FollowedHyperlinkColor followedHyperlinkColor1 = new A.FollowedHyperlinkColor(); - A.RgbColorModelHex rgbColorModelHex10 = new A.RgbColorModelHex() { Val = "800080" }; + A.RgbColorModelHex rgbColorModelHex10 = new A.RgbColorModelHex() { Val = Theme.FollowedHyperlink.Color.ToHex().Substring(2) }; followedHyperlinkColor1.Append(rgbColorModelHex10); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 86cd60b..f6068b8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -29,6 +29,7 @@ NamedRanges = new XLNamedRanges(workbook); SheetView = new XLSheetView(); Tables = new XLTables(); + Hyperlinks = new XLHyperlinks(); this.workbook = workbook; style = new XLStyle(this, workbook.Style); Internals = new XLWorksheetInternals(new XLCellCollection(), new XLColumnsCollection(), new XLRowsCollection(), new XLRanges(workbook, workbook.Style) , workbook); @@ -597,5 +598,7 @@ return rowOutlineCount.Where(kp => kp.Value > 0).Max(kp => kp.Key); } #endregion + + public new IXLHyperlinks Hyperlinks { get; private set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 4cb632d..1064c4c 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -58,6 +58,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index c299a00..50c16f1 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -68,6 +68,7 @@ new CopyingWorksheets().Create(); new InsertingTables().Create(@"C:\Excel Files\Created\InsertingTables.xlsx"); new InsertingData().Create(@"C:\Excel Files\Created\InsertingData.xlsx"); + new Hyperlinks().Create(@"C:\Excel Files\Created\Hyperlinks.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Hyperlinks.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Hyperlinks.cs new file mode 100644 index 0000000..669348c --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Hyperlinks.cs @@ -0,0 +1,114 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class Hyperlinks + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Hyperlinks"); + wb.Worksheets.Add("Second Sheet"); + + Int32 ro = 0; + + // You can create a link with pretty much anything you can put on a + // browser: http, ftp, mailto, gopher, news, nntp, etc. + + ws.Cell(++ro, 1).Value = "Link to a web page, no tooltip - Yahoo!"; + ws.Cell(ro, 1).Hyperlink = new XLHyperlink(@"http://www.yahoo.com"); + + ws.Cell(++ro, 1).Value = "Link to a web page, with a tooltip - Yahoo!"; + ws.Cell(ro, 1).Hyperlink = new XLHyperlink(@"http://www.yahoo.com", "Click to go to Yahoo!"); + + ws.Cell(++ro, 1).Value = "Link to a file - same folder"; + ws.Cell(ro, 1).Hyperlink = new XLHyperlink("Test.xlsx"); + + ws.Cell(++ro, 1).Value = "Link to a file - relative address"; + ws.Cell(ro, 1).Hyperlink = new XLHyperlink(@"../Test.xlsx"); + + ws.Cell(++ro, 1).Value = "Link to an address in this worksheet"; + ws.Cell(ro, 1).Hyperlink = new XLHyperlink("B1"); + + ws.Cell(++ro, 1).Value = "Link to an address in another worksheet"; + ws.Cell(ro, 1).Hyperlink = new XLHyperlink("'Second Sheet'!A1"); + + // You can also set the properties of a hyperlink directly: + + ws.Cell(++ro, 1).Value = "Link to a range in this worksheet"; + ws.Cell(ro, 1).Hyperlink.InternalAddress = "B1:C2"; + ws.Cell(ro, 1).Hyperlink.Tooltip = "SquareBox"; + + ws.Cell(++ro, 1).Value = "Link to an email message"; + ws.Cell(ro, 1).Hyperlink.ExternalAddress = new Uri(@"mailto:SantaClaus@NorthPole.com?subject=Presents"); + + // Deleting a hyperlink + ws.Cell(++ro, 1).Value = "This is no longer a link"; + ws.Cell(ro, 1).Hyperlink.InternalAddress = "A1"; + ws.Cell(ro, 1).Hyperlink.Delete(); + + // Setting a hyperlink preserves previous formatting: + ws.Cell(++ro, 1).Value = "Odd looking link"; + ws.Cell(ro, 1).Style.Font.FontColor = XLColor.Red; + ws.Cell(ro, 1).Style.Font.Underline = XLFontUnderlineValues.Double; + ws.Cell(ro, 1).Hyperlink = new XLHyperlink(ws.Range("B1:C2")); + + // List all hyperlinks in a worksheet: + var hyperlinksInWorksheet = ws.Hyperlinks; + + // List all hyperlinks in a range: + var hyperlinksInRange = ws.Range("A1:A3").Hyperlinks; + + ws.Columns().AdjustToContents(); + + wb.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 85731d1..45a49ed 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -94,6 +94,18 @@ Excel\CustomProperties\XLCustomProperty.cs + + Excel\Hyperlinks\IXLHyperlinks.cs + + + Excel\Hyperlinks\XLHyperlinks.cs + + + Excel\Hyperlinks\XLHyperlink_Internal.cs + + + Excel\Hyperlinks\XLHyperlink_public.cs + Excel\IXLAddress.cs diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 369fe81..d0d772a 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -12,15 +12,56 @@ { class Program { + static void Main(string[] args) { - new ClosedXML_Examples.Ranges.UsingTables().Create(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); - //var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); + //new ClosedXML_Examples.Ranges.UsingTables().Create(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); + var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\Hyperlinks_2.xlsx"); //var wb = new XLWorkbook(); //var c = wb.Worksheets.Add("New").Cell(1, 1); //c.Value = "Hello"; //c.ShareString = false; - //wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); + wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); + + //String test = @"../file.txt"; + //Console.WriteLine("Uri.IsWellFormedUriString says: {0}", Uri.IsWellFormedUriString(test, UriKind.RelativeOrAbsolute)); + //Console.WriteLine("TryCreate says: {0}", TryCreate(test)); + //Console.WriteLine("IsValidUri says: {0}", IsValidUri(test)); + //Console.ReadKey(); + } + public static String GetSheetPassword(String password) + { + Int32 pLength = password.Length; + Int32 hash = 0; + if (pLength == 0) return hash.ToString("X"); + + for (Int32 i = pLength - 1; i >= 0; i--) + { + hash ^= password[i]; + hash = hash >> 14 & 0x01 | hash << 1 & 0x7fff; + } + hash ^= 0x8000 | 'N' << 8 | 'K'; + hash ^= pLength; + return hash.ToString("X"); + } + + static Boolean IsValidUri(String uri) + { + try + { + new Uri(uri, UriKind.Relative); + return true; + } + catch + { + return false; + } + } + + static Boolean TryCreate(String address) + { + Uri uri; + return Uri.TryCreate(address, UriKind.Absolute, out uri); } static void Main_5961(string[] args)