9558568508

Committed 18 Jun 2024 03:35AM UTC coverage: 69.67% (-17.1%) from 86.727%

Build # 9558568508

Build Type

push

github

Committed by

JaCraig

Commit Message

feat: Starting work on Excel format

Run Details

566 of 862 branches covered (65.66%)

Branch coverage included in aggregate %.

6 of 220 new or added lines in 6 files covered. (2.73%)

2 existing lines in 1 file now uncovered.

787 of 1080 relevant lines covered (72.87%)

1108.67 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

0.0

/Archivist/Formats/Excel/ExcelReader.cs

﻿using Archivist.BaseClasses;
using Archivist.Interfaces;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Archivist.Formats.Excel
{
    /// <summary>
    /// Represents a reader for Excel files.
    /// </summary>
    public class ExcelReader : ReaderBaseClass
    {
        /// <summary>
        /// Gets the header information for Excel files.
        /// </summary>
        public override byte[] HeaderInfo { get; } = new byte[] { 0x50, 0x4B, 0x03, 0x04 };

        /// <summary>
        /// Gets the regular expression pattern for matching alphabetic strings.
        /// </summary>
        private static Regex AlphaRegex { get; } = new Regex("^[A-Z]+$", RegexOptions.Compiled);

        /// <summary>
        /// Gets the regular expression pattern for matching column names.
        /// </summary>
        private static Regex ColumnNameRegex { get; } = new Regex("[A-Za-z]+", RegexOptions.Compiled);

        /// <summary>
        /// Gets an empty cell.
        /// </summary>
        private static Cell EmptyCell { get; } = new Cell
        {
            DataType = null,
            CellValue = new CellValue("")
        };

        /// <summary>
        /// Determines if the reader can read the given stream as an Excel file.
        /// </summary>
        /// <param name="stream">The stream to read.</param>
        /// <returns>True if the reader can read the file, false otherwise.</returns>
        public override bool InternalCanRead(Stream stream)
        {
            try
            {
                var Document = SpreadsheetDocument.Open(stream, false);
                if (Document.RootPart?.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml")
                    return false;
            }
            catch
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// Reads the Excel file asynchronously and returns the data as a generic file.
        /// </summary>
        /// <param name="stream">The stream to read.</param>
        /// <returns>
        /// A task representing the asynchronous operation. The task result is the generic file data.
        /// </returns>
        public override Task<IGenericFile?> ReadAsync(Stream? stream)
        {
            var ReturnValue = new DataTypes.Tables();
            if (stream is null)
                return Task.FromResult<IGenericFile?>(ReturnValue);

            // Open the excel document
            WorkbookPart? WorkbookPart;
            try
            {
                var Document = SpreadsheetDocument.Open(stream, false);
                WorkbookPart = Document.WorkbookPart;
                if (WorkbookPart is null)
                    return Task.FromResult<IGenericFile?>(ReturnValue);

                foreach (Sheet Sheet in WorkbookPart.Workbook.Descendants<Sheet>())
                {
                    DataTypes.Table CurrentTable = ReturnValue.AddTable();
                    CurrentTable.Title = Sheet.Name?.ToString() ?? "";

                    var SheetId = Sheet.Id?.Value ?? "";

                    if (string.IsNullOrEmpty(SheetId))
                        continue;

                    Worksheet WorkSheet = ((WorksheetPart)WorkbookPart.GetPartById(SheetId)).Worksheet;
                    if (WorkSheet is null)
                        continue;

                    Columns? Columns = WorkSheet.Descendants<Columns>().FirstOrDefault();

                    SheetData? SheetData = WorkSheet.Elements<SheetData>().FirstOrDefault();
                    if (SheetData is null)
                        continue;
                    var Rows = SheetData.Elements<Row>().ToList();

                    // Read the header
                    if (Rows.Count == 0)
                        continue;

                    Row Row = Rows[0];
                    IEnumerator<Cell> CellEnumerator = GetExcelCellEnumerator(Row);
                    while (CellEnumerator.MoveNext())
                    {
                        Cell Cell = CellEnumerator.Current;
                        var Text = ReadExcelCell(Cell, WorkbookPart).Trim();
                        CurrentTable.Columns.Add(Text);
                    }

                    // Read the sheet data
                    if (Rows.Count <= 1)
                        continue;
                    for (var I = 1; I < Rows.Count; I++)
                    {
                        DataTypes.TableRow DataRow = CurrentTable.AddRow();
                        Row = Rows[I];
                        CellEnumerator = GetExcelCellEnumerator(Row);
                        while (CellEnumerator.MoveNext())
                        {
                            Cell Cell = CellEnumerator.Current;
                            var Text = ReadExcelCell(Cell, WorkbookPart).Trim();
                            DataRow.Add(Text);
                        }
                    }
                }
            }
            catch (Exception)
            {
                return Task.FromResult<IGenericFile?>(ReturnValue);
            }

            return Task.FromResult<IGenericFile?>(ReturnValue);
        }

        /// <summary>
        /// Converts the column name to a number.
        /// </summary>
        /// <param name="columnName">The name of the column.</param>
        /// <returns>The column number.</returns>
        private static int ConvertColumnNameToNumber(string columnName)
        {
            if (!AlphaRegex.IsMatch(columnName))
                return -1;

            var ColLetters = columnName.ToCharArray();
            Array.Reverse(ColLetters);

            var ConvertedValue = 0;
            for (var I = 0; I < ColLetters.Length; I++)
            {
                var Letter = ColLetters[I];
                // ASCII 'A' = 65
                var Current = I == 0 ? Letter - 65 : Letter - 64;
                ConvertedValue += Current * (int)Math.Pow(26, I);
            }

            return ConvertedValue;
        }

        /// <summary>
        /// Gets the column name from the cell reference.
        /// </summary>
        /// <param name="cellReference">The cell reference.</param>
        /// <returns>The column name.</returns>
        private static string GetColumnName(string? cellReference) => string.IsNullOrEmpty(cellReference) ? "" : ColumnNameRegex.Match(cellReference).Value;

        /// <summary>
        /// Gets the enumerator for iterating over the cells in a row.
        /// </summary>
        /// <param name="row">The row.</param>
        /// <returns>The cell enumerator.</returns>
        private static IEnumerator<Cell> GetExcelCellEnumerator(Row row)
        {
            var CurrentCount = 0;
            foreach (Cell CurrentCell in row.Descendants<Cell>())
            {
                var ColumnName = GetColumnName(CurrentCell.CellReference);
                if (string.IsNullOrEmpty(ColumnName))
                    continue;

                var CurrentColumnIndex = ConvertColumnNameToNumber(ColumnName);

                if (CurrentColumnIndex < 0)
                    continue;

                for (; CurrentCount < CurrentColumnIndex; ++CurrentCount)
                {
                    yield return EmptyCell;
                }
                yield return CurrentCell;
                ++CurrentCount;
            }
        }

        /// <summary>
        /// Reads the value of an Excel cell.
        /// </summary>
        /// <param name="cell">The cell.</param>
        /// <param name="workbookPart">The workbook part.</param>
        /// <returns>The cell value.</returns>
        private static string ReadExcelCell(Cell cell, WorkbookPart workbookPart)
        {
            if (cell is null)
                return "";

            CellValue? CellValue = cell.CellValue;
            var Text = CellValue?.Text ?? cell.InnerText ?? "";

            if (cell.DataType == null || cell.DataType != CellValues.SharedString || string.IsNullOrEmpty(cell.CellValue?.Text))
                return Text.Trim();

            Text = workbookPart.SharedStringTablePart
                               ?.SharedStringTable
                               .Elements<SharedStringItem>()
                               .ElementAt(Convert.ToInt32(cell.CellValue.Text))
                               .InnerText;

            return (Text ?? "").Trim();
        }
    }
}

1	using Archivist.BaseClasses;
2	using Archivist.Interfaces;
3	using DocumentFormat.OpenXml.Packaging;
4	using DocumentFormat.OpenXml.Spreadsheet;
5	using System;
6	using System.Collections.Generic;
7	using System.IO;
8	using System.Linq;
9	using System.Text.RegularExpressions;
10	using System.Threading.Tasks;
11
12	namespace Archivist.Formats.Excel
13	{
14	/// <summary>
15	/// Represents a reader for Excel files.
16	/// </summary>
17	public class ExcelReader : ReaderBaseClass
18	{
19	/// <summary>
20	/// Gets the header information for Excel files.
21	/// </summary>
NEW 22	public override byte[] HeaderInfo { get; } = new byte[] { 0x50, 0x4B, 0x03, 0x04 };	×
23
24	/// <summary>
25	/// Gets the regular expression pattern for matching alphabetic strings.
26	/// </summary>
NEW 27	private static Regex AlphaRegex { get; } = new Regex("^[A-Z]+$", RegexOptions.Compiled);	×
28
29	/// <summary>
30	/// Gets the regular expression pattern for matching column names.
31	/// </summary>
NEW 32	private static Regex ColumnNameRegex { get; } = new Regex("[A-Za-z]+", RegexOptions.Compiled);	×
33
34	/// <summary>
35	/// Gets an empty cell.
36	/// </summary>
NEW 37	private static Cell EmptyCell { get; } = new Cell	×
NEW 38	{	×
NEW 39	DataType = null,	×
NEW 40	CellValue = new CellValue("")	×
NEW 41	};	×
42
43	/// <summary>
44	/// Determines if the reader can read the given stream as an Excel file.
45	/// </summary>
46	/// <param name="stream">The stream to read.</param>
47	/// <returns>True if the reader can read the file, false otherwise.</returns>
48	public override bool InternalCanRead(Stream stream)
49	{
50	try
51	{
NEW 52	var Document = SpreadsheetDocument.Open(stream, false);	×
NEW 53	if (Document.RootPart?.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml")	×
NEW 54	return false;	×
NEW 55	}	×
NEW 56	catch	×
57	{
NEW 58	return false;	×
59	}
NEW 60	return true;	×
NEW 61	}	×
62
63	/// <summary>
64	/// Reads the Excel file asynchronously and returns the data as a generic file.
65	/// </summary>
66	/// <param name="stream">The stream to read.</param>
67	/// <returns>
68	/// A task representing the asynchronous operation. The task result is the generic file data.
69	/// </returns>
70	public override Task<IGenericFile?> ReadAsync(Stream? stream)
71	{
NEW 72	var ReturnValue = new DataTypes.Tables();	×
NEW 73	if (stream is null)	×
NEW 74	return Task.FromResult<IGenericFile?>(ReturnValue);	×
75
76	// Open the excel document
77	WorkbookPart? WorkbookPart;
78	try
79	{
NEW 80	var Document = SpreadsheetDocument.Open(stream, false);	×
NEW 81	WorkbookPart = Document.WorkbookPart;	×
NEW 82	if (WorkbookPart is null)	×
NEW 83	return Task.FromResult<IGenericFile?>(ReturnValue);	×
84
NEW 85	foreach (Sheet Sheet in WorkbookPart.Workbook.Descendants<Sheet>())	×
86	{
NEW 87	DataTypes.Table CurrentTable = ReturnValue.AddTable();	×
NEW 88	CurrentTable.Title = Sheet.Name?.ToString() ?? "";	×
89
NEW 90	var SheetId = Sheet.Id?.Value ?? "";	×
91
NEW 92	if (string.IsNullOrEmpty(SheetId))	×
93	continue;
94
NEW 95	Worksheet WorkSheet = ((WorksheetPart)WorkbookPart.GetPartById(SheetId)).Worksheet;	×
NEW 96	if (WorkSheet is null)	×
97	continue;
98
NEW 99	Columns? Columns = WorkSheet.Descendants<Columns>().FirstOrDefault();	×
100
NEW 101	SheetData? SheetData = WorkSheet.Elements<SheetData>().FirstOrDefault();	×
NEW 102	if (SheetData is null)	×
103	continue;
NEW 104	var Rows = SheetData.Elements<Row>().ToList();	×
105
106	// Read the header
NEW 107	if (Rows.Count == 0)	×
108	continue;
109
NEW 110	Row Row = Rows[0];	×
NEW 111	IEnumerator<Cell> CellEnumerator = GetExcelCellEnumerator(Row);	×
NEW 112	while (CellEnumerator.MoveNext())	×
113	{
NEW 114	Cell Cell = CellEnumerator.Current;	×
NEW 115	var Text = ReadExcelCell(Cell, WorkbookPart).Trim();	×
NEW 116	CurrentTable.Columns.Add(Text);	×
117	}
118
119	// Read the sheet data
NEW 120	if (Rows.Count <= 1)	×
121	continue;
NEW 122	for (var I = 1; I < Rows.Count; I++)	×
123	{
NEW 124	DataTypes.TableRow DataRow = CurrentTable.AddRow();	×
NEW 125	Row = Rows[I];	×
NEW 126	CellEnumerator = GetExcelCellEnumerator(Row);	×
NEW 127	while (CellEnumerator.MoveNext())	×
128	{
NEW 129	Cell Cell = CellEnumerator.Current;	×
NEW 130	var Text = ReadExcelCell(Cell, WorkbookPart).Trim();	×
NEW 131	DataRow.Add(Text);	×
132	}
133	}
134	}
NEW 135	}	×
NEW 136	catch (Exception)	×
137	{
NEW 138	return Task.FromResult<IGenericFile?>(ReturnValue);	×
139	}
140
NEW 141	return Task.FromResult<IGenericFile?>(ReturnValue);	×
NEW 142	}	×
143
144	/// <summary>
145	/// Converts the column name to a number.
146	/// </summary>
147	/// <param name="columnName">The name of the column.</param>
148	/// <returns>The column number.</returns>
149	private static int ConvertColumnNameToNumber(string columnName)
150	{
NEW 151	if (!AlphaRegex.IsMatch(columnName))	×
NEW 152	return -1;	×
153
NEW 154	var ColLetters = columnName.ToCharArray();	×
NEW 155	Array.Reverse(ColLetters);	×
156
NEW 157	var ConvertedValue = 0;	×
NEW 158	for (var I = 0; I < ColLetters.Length; I++)	×
159	{
NEW 160	var Letter = ColLetters[I];	×
161	// ASCII 'A' = 65
NEW 162	var Current = I == 0 ? Letter - 65 : Letter - 64;	×
NEW 163	ConvertedValue += Current * (int)Math.Pow(26, I);	×
164	}
165
NEW 166	return ConvertedValue;	×
167	}
168
169	/// <summary>
170	/// Gets the column name from the cell reference.
171	/// </summary>
172	/// <param name="cellReference">The cell reference.</param>
173	/// <returns>The column name.</returns>
NEW 174	private static string GetColumnName(string? cellReference) => string.IsNullOrEmpty(cellReference) ? "" : ColumnNameRegex.Match(cellReference).Value;	×
175
176	/// <summary>
177	/// Gets the enumerator for iterating over the cells in a row.
178	/// </summary>
179	/// <param name="row">The row.</param>
180	/// <returns>The cell enumerator.</returns>
181	private static IEnumerator<Cell> GetExcelCellEnumerator(Row row)
182	{
NEW 183	var CurrentCount = 0;	×
NEW 184	foreach (Cell CurrentCell in row.Descendants<Cell>())	×
185	{
NEW 186	var ColumnName = GetColumnName(CurrentCell.CellReference);	×
NEW 187	if (string.IsNullOrEmpty(ColumnName))	×
188	continue;
189
NEW 190	var CurrentColumnIndex = ConvertColumnNameToNumber(ColumnName);	×
191
NEW 192	if (CurrentColumnIndex < 0)	×
NEW 193	continue;	×
194
NEW 195	for (; CurrentCount < CurrentColumnIndex; ++CurrentCount)	×
196	{
NEW 197	yield return EmptyCell;	×
198	}
NEW 199	yield return CurrentCell;	×
NEW 200	++CurrentCount;	×
NEW 201	}	×
NEW 202	}	×
203
204	/// <summary>
205	/// Reads the value of an Excel cell.
206	/// </summary>
207	/// <param name="cell">The cell.</param>
208	/// <param name="workbookPart">The workbook part.</param>
209	/// <returns>The cell value.</returns>
210	private static string ReadExcelCell(Cell cell, WorkbookPart workbookPart)
211	{
NEW 212	if (cell is null)	×
NEW 213	return "";	×
214
NEW 215	CellValue? CellValue = cell.CellValue;	×
NEW 216	var Text = CellValue?.Text ?? cell.InnerText ?? "";	×
217
NEW 218	if (cell.DataType == null \|\| cell.DataType != CellValues.SharedString \|\| string.IsNullOrEmpty(cell.CellValue?.Text))	×
NEW 219	return Text.Trim();	×
220
NEW 221	Text = workbookPart.SharedStringTablePart	×
NEW 222	?.SharedStringTable	×
NEW 223	.Elements<SharedStringItem>()	×
NEW 224	.ElementAt(Convert.ToInt32(cell.CellValue.Text))	×
NEW 225	.InnerText;	×
226
NEW 227	return (Text ?? "").Trim();	×
228	}
229	}
230	}

JaCraig / Archivist / 9558568508

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous