ExcelDataReader · davenuk · Jan 23, 2023
diff --git a/src/ExcelDataReader.DataSet/ExcelDataReaderExtensions.cs b/src/ExcelDataReader.DataSet/ExcelDataReaderExtensions.cs
@@ -25,6 +25,7 @@ public static DataSet AsDataSet(this IExcelDataReader self, ExcelDataSetConfigur
             var result = new DataSet();
             do
             {
+
                 tableIndex++;
                 if (configuration.FilterSheet != null && !configuration.FilterSheet(self, tableIndex))
                 {
@@ -39,6 +40,7 @@ public static DataSet AsDataSet(this IExcelDataReader self, ExcelDataSetConfigur
 
                 var table = AsDataTable(self, tableConfiguration);
                 result.Tables.Add(table);
+
             }
             while (self.NextResult());
 
@@ -71,6 +73,7 @@ private static DataTable AsDataTable(IExcelDataReader self, ExcelDataTableConfig
         {
             var result = new DataTable { TableName = self.Name };
             result.ExtendedProperties.Add("visiblestate", self.VisibleState);
+            if (configuration.ProcessComments) { result.ExtendedProperties.Add("comments", self.Comments); }
             var first = true;
             var emptyRows = 0;
             var columnIndices = new List<int>();

diff --git a/src/ExcelDataReader.DataSet/ExcelDataTableConfiguration.cs b/src/ExcelDataReader.DataSet/ExcelDataTableConfiguration.cs
@@ -17,6 +17,11 @@ public class ExcelDataTableConfiguration
         /// </summary>
         public bool UseHeaderRow { get; set; } = false;
 
+        /// <summary>
+        /// Gets or sets a value indicating whether to process cell comments.
+        /// </summary>
+        public bool ProcessComments { get; set; } = false;
+
         /// <summary>
         /// Gets or sets a callback to determine which row is the header row. Only called when UseHeaderRow = true.
         /// </summary>

diff --git a/src/ExcelDataReader/Core/BinaryFormat/XlsWorksheet.cs b/src/ExcelDataReader/Core/BinaryFormat/XlsWorksheet.cs
@@ -3,6 +3,7 @@
 using System.IO;
 using System.Text;
 using ExcelDataReader.Core.NumberFormat;
+using ExcelDataReader.Core.OpenXmlFormat;
 using ExcelDataReader.Log;
 
 namespace ExcelDataReader.Core.BinaryFormat
@@ -88,6 +89,8 @@ public XlsWorksheet(XlsWorkbook workbook, XlsBiffBoundSheet refSheet, Stream str
 
         public XlsWorkbook Workbook { get; }
 
+        public XlsxComments Comments => throw new NotImplementedException();
+
         public IEnumerable<Row> ReadRows()
         {
             var rowIndex = 0;

diff --git a/src/ExcelDataReader/Core/CsvFormat/CsvWorksheet.cs b/src/ExcelDataReader/Core/CsvFormat/CsvWorksheet.cs
@@ -2,6 +2,7 @@
 using System.Collections.Generic;
 using System.IO;
 using System.Text;
+using ExcelDataReader.Core.OpenXmlFormat;
 
 namespace ExcelDataReader.Core.CsvFormat
 {
@@ -70,6 +71,8 @@ public int RowCount
 
         public Column[] ColumnWidths => null;
 
+        public XlsxComments Comments => throw new NotImplementedException();
+
         private int BomLength { get; set; }
 
         private bool AnalyzedPartial { get; }

diff --git a/src/ExcelDataReader/Core/IWorksheet.cs b/src/ExcelDataReader/Core/IWorksheet.cs
@@ -1,4 +1,5 @@
 using System.Collections.Generic;
+using ExcelDataReader.Core.OpenXmlFormat;
 
 namespace ExcelDataReader.Core
 {
@@ -22,6 +23,7 @@ internal interface IWorksheet
         CellRange[] MergeCells { get; }
 
         Column[] ColumnWidths { get; }
+        XlsxComments Comments { get; }
 
         IEnumerable<Row> ReadRows();
     }

diff --git a/src/ExcelDataReader/Core/OpenXmlFormat/Records/CommentRecord.cs b/src/ExcelDataReader/Core/OpenXmlFormat/Records/CommentRecord.cs
@@ -0,0 +1,24 @@
+using System;
+using System.Collections.Generic;
+using System.Text;
+using System.Web;
+
+namespace ExcelDataReader.Core.OpenXmlFormat.Records
+{
+    internal sealed class CommentRecord : Record
+    {
+
+        public CommentRecord(string cellRef, string comment, string author) 
+        {
+            CellRef = cellRef;
+            Comment = comment;   
+            Author = author;
+        }
+
+        public string CellRef { get; }
+        public string Comment { get; }
+        public string Author { get; }
+
+
+    }
+}
diff --git a/src/ExcelDataReader/Core/OpenXmlFormat/XlsxComments.cs b/src/ExcelDataReader/Core/OpenXmlFormat/XlsxComments.cs
@@ -0,0 +1,13 @@
+using System.Collections.Generic;
+using System.Security.Cryptography.X509Certificates;
+using ExcelDataReader.Core.OpenXmlFormat.Records;
+
+namespace ExcelDataReader.Core.OpenXmlFormat
+{
+    /// <summary>
+    /// Comment table.
+    /// </summary>
+    public class XlsxComments : Dictionary<string,string>
+    {
+    }
+}
diff --git a/src/ExcelDataReader/Core/OpenXmlFormat/XlsxWorkbook.cs b/src/ExcelDataReader/Core/OpenXmlFormat/XlsxWorkbook.cs
@@ -39,7 +39,8 @@ public IEnumerable<XlsxWorksheet> ReadWorksheets()
         {
             foreach (var sheet in Sheets)
             {
-                yield return new XlsxWorksheet(_zipWorker, this, sheet);
+                var comments = ReadComments(sheet);
+                yield return new XlsxWorksheet(_zipWorker, this, sheet, comments);
             }
         }
 
@@ -127,6 +128,26 @@ private void ReadSharedStrings()
             }
         }
 
+        private XlsxComments ReadComments(SheetRecord sheet)
+        {
+            var comments = new XlsxComments();
+            using var reader = _zipWorker.GetCommentsReader(sheet);
+            if (reader == null)
+                return comments;
+
+            Record record;
+            while ((record = reader.Read()) != null)
+            {
+                switch (record)
+                {
+                    case CommentRecord pr:
+                        comments.Add(pr.CellRef,pr.Comment);
+                        break;
+                }
+            }
+            return comments;
+        }
+
         private void ReadStyles()
         {
             using var reader = _zipWorker.GetStylesReader();

diff --git a/src/ExcelDataReader/Core/OpenXmlFormat/XlsxWorksheet.cs b/src/ExcelDataReader/Core/OpenXmlFormat/XlsxWorksheet.cs
@@ -7,7 +7,7 @@ namespace ExcelDataReader.Core.OpenXmlFormat
 {
     internal class XlsxWorksheet : IWorksheet
     {
-        public XlsxWorksheet(ZipWorker document, XlsxWorkbook workbook, SheetRecord refSheet)
+        public XlsxWorksheet(ZipWorker document, XlsxWorkbook workbook, SheetRecord refSheet, XlsxComments comments)
         {
             Document = document;
             Workbook = workbook;
@@ -79,6 +79,8 @@ public XlsxWorksheet(ZipWorker document, XlsxWorkbook workbook, SheetRecord refS
                 FieldCount = columnIndexMaximum + 1;
                 RowCount = rowIndexMaximum + 1;
             }
+
+            Comments = comments;
         }
 
         public int FieldCount { get; }
@@ -109,6 +111,8 @@ public XlsxWorksheet(ZipWorker document, XlsxWorkbook workbook, SheetRecord refS
 
         private XlsxWorkbook Workbook { get; }
 
+        public XlsxComments Comments { get; }
+
         public IEnumerable<Row> ReadRows()
         {
             if (string.IsNullOrEmpty(Path))

diff --git a/src/ExcelDataReader/Core/OpenXmlFormat/XmlFormat/XmlCommentsReader.cs b/src/ExcelDataReader/Core/OpenXmlFormat/XmlFormat/XmlCommentsReader.cs
@@ -0,0 +1,101 @@
+using System.Collections.Generic;
+using System.Xml;
+using ExcelDataReader.Core.OpenXmlFormat.Records;
+
+namespace ExcelDataReader.Core.OpenXmlFormat.XmlFormat
+{
+    internal sealed class XmlCommentsReader : XmlRecordReader
+    {
+        private const string NsSpreadsheetMl = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
+        private const string ElementComments = "comments";
+        private const string ElementCommentList = "commentList";
+        private const string ElementComment = "comment";
+        private const string ElementText = "text";
+        private const string ElementAuthors = "authors";
+        private const string ElementAuthor = "author";
+        private const string AttributeRef = "ref";
+
+
+        public XmlCommentsReader(XmlReader reader)
+            : base(reader)
+        {
+        }
+
+        protected override IEnumerable<Record> ReadOverride()
+        {if (!Reader.IsStartElement(ElementComments, NsSpreadsheetMl))
+            {
+                yield break;
+            }
+
+
+            if (!XmlReaderHelper.ReadFirstContent(Reader))
+            {
+                yield break;
+            }
+
+            while (!Reader.EOF)
+            {
+                //var authors = new List<string>();
+                //var comments = new Dictionary<string,CommentRecord>();
+                //if (Reader.IsStartElement(ElementAuthors, NsSpreadsheetMl))
+                //{
+                //    var value = StringHelper.ReadStringItem(Reader);
+                //    yield return new CommentRecord(value);
+                //}
+                //else
+                if (Reader.IsStartElement(ElementCommentList, NsSpreadsheetMl))
+                {
+                    foreach (var comment in ReadCellComments())
+                    {
+                        yield return comment;
+                    }
+                }
+                else if (!XmlReaderHelper.SkipContent(Reader))
+                {
+                    break;
+                }
+            }
+        }
+
+        private IEnumerable<CommentRecord> ReadCellComments()
+        {
+            if (!XmlReaderHelper.ReadFirstContent(Reader))
+            {
+                yield break;
+            }
+
+            while (!Reader.EOF)
+            {
+                if (Reader.IsStartElement(ElementComment, NsSpreadsheetMl))
+                {
+                    var cellRef = Reader.GetAttribute(AttributeRef);
+                    var comment = "";
+
+                    if (!XmlReaderHelper.ReadFirstContent(Reader))
+                    {
+                        yield break;
+                    }
+
+                    while (!Reader.EOF)
+                    {
+                        if (Reader.IsStartElement(ElementText, NsSpreadsheetMl))
+                        {
+                            comment = StringHelper.ReadStringItem(Reader);
+                           // reader.Skip();
+                        }
+                        else if (!XmlReaderHelper.SkipContent(Reader))
+                        {
+                            break;
+                        }
+                    }
+
+                    yield return new CommentRecord(cellRef, comment, "");
+                }
+                else if (!XmlReaderHelper.SkipContent(Reader))
+                {
+                    break;
+                }
+            }
+        }
+    }
+}
diff --git a/src/ExcelDataReader/Core/OpenXmlFormat/ZipWorker.cs b/src/ExcelDataReader/Core/OpenXmlFormat/ZipWorker.cs
@@ -2,8 +2,10 @@
 using System.Collections.Generic;
 using System.IO;
 using System.IO.Compression;
+using System.Reflection;
 using System.Xml;
 using ExcelDataReader.Core.OpenXmlFormat.BinaryFormat;
+using ExcelDataReader.Core.OpenXmlFormat.Records;
 using ExcelDataReader.Core.OpenXmlFormat.XmlFormat;
 
 namespace ExcelDataReader.Core.OpenXmlFormat
@@ -15,6 +17,15 @@ internal partial class ZipWorker : IDisposable
         private const string FileWorkbook = "xl/workbook.{0}";
         private const string FileRels = "xl/_rels/workbook.{0}.rels";
 
+        // comments - or notes
+        private const string FileComments = "xl/{0}";
+        private const string SheetRels = "xl/worksheets/_rels/{1}.{0}.rels";
+        private const string NsRelationship = "http://schemas.openxmlformats.org/package/2006/relationships";
+        private const string ElementRelationship = "Relationship";
+        private const string ElementRelationships = "Relationships";
+        private const string AttributeType = "Type";
+        private const string AttributeTarget = "Target";
+
         private const string Format = "xml";
         private const string BinFormat = "bin";
 
@@ -60,6 +71,61 @@ public RecordReader GetSharedStringsReader()
             return null;
         }
 
+        /// <summary>
+        /// Gets the cmments reader.
+        /// </summary>
+        public RecordReader GetCommentsReader(SheetRecord sheet)
+        {
+            //find comments sheet from sheet.rels
+            //get threaded comment reader first?
+            var sheetFilename = Path.GetFileNameWithoutExtension(sheet.Path);
+            var entry = FindEntry(string.Format(SheetRels, Format, sheetFilename));
+            if (entry == null)
+                return null;
+            var reader = XmlReader.Create(entry.Open(), XmlSettings);
+            if (!reader.IsStartElement(ElementRelationships, NsRelationship))
+            {
+                return null;
+            }
+
+            if (!XmlReaderHelper.ReadFirstContent(reader))
+            {
+                return null;
+            }
+
+            var commentFileName = "";
+            while (!reader.EOF)
+            {
+                if (reader.IsStartElement(ElementRelationship, NsRelationship))
+                {
+                    string relType = reader.GetAttribute(AttributeType);
+
+                    if (relType.EndsWith("comments"))
+                    {
+                        var target = reader.GetAttribute(AttributeTarget);
+                        commentFileName = string.Format(FileComments,Path.GetFileName(target));
+                        break;
+                    }//todo - hardcodednono
+
+                    reader.Skip();
+                }
+                else if (!XmlReaderHelper.SkipContent(reader))
+                {
+                    break;
+                }
+            }
+
+            entry = FindEntry(commentFileName);
+            if (entry != null)
+                return new XmlCommentsReader(XmlReader.Create(entry.Open(), XmlSettings));
+
+            //entry = FindEntry(string.Format(FileComments, BinFormat));
+            //if (entry != null)
+            //    return new BiffCommentsReader(entry.Open());
+
+            return null;
+        }
+
         /// <summary>
         /// Gets the styles reader.
         /// </summary>

diff --git a/src/ExcelDataReader/ExcelDataReader.cs b/src/ExcelDataReader/ExcelDataReader.cs
@@ -2,6 +2,7 @@
 using System.Collections.Generic;
 using System.Data;
 using ExcelDataReader.Core;
+using ExcelDataReader.Core.OpenXmlFormat;
 
 namespace ExcelDataReader
 {
@@ -53,6 +54,8 @@ internal abstract class ExcelDataReader<TWorkbook, TWorksheet> : IExcelDataReade
 
         protected Cell[] RowCells { get; set; }
 
+        public XlsxComments Comments => _worksheetIterator?.Current?.Comments;
+
         public object this[int i] => GetValue(i);
 
         public object this[string name] => throw new NotSupportedException();