Kit.Core/LibCommon/Kit.Helpers.OpenXml/OpenXml/SLExcel/SLExcelReader.cs

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.AspNetCore.Http;
using System.Data;
using System.Text.RegularExpressions;

namespace Kit.Helpers.OpenXml.SLExcel
{
    public interface IExcelReader
    {
        IEnumerable<SLExcelData> ReadAllExcel(IFormFile file);
        SLExcelData ReadExcel(IFormFile file);
        SLExcelData ReadExcel(Stream file, string sheetName, int headerIndex = 0, int? dataIdx = null);
        SLExcelData ReadExcel(Stream file, string sheetName, string header, int? dataIdx = null);
        bool IsExcel(Stream file);
    }

    public class SLExcelReader : IExcelReader
    {
        private string GetColumnName(string cellReference)
        {
            var regex = new Regex("[A-Za-z]+");
            var match = regex.Match(cellReference);

            return match.Value;
        }

        private int ConvertColumnNameToNumber(string columnName)
        {
            var alpha = new Regex("^[A-Z]+$");
            if (!alpha.IsMatch(columnName)) throw new ArgumentException();

            char[] colLetters = columnName.ToCharArray();
            Array.Reverse(colLetters);

            var convertedValue = 0;
            for (int i = 0; i < colLetters.Length; i++)
            {
                char letter = colLetters[i];
                int current = i == 0 ? letter - 65 : letter - 64; // ASCII 'A' = 65
                convertedValue += current * (int)Math.Pow(26, i);
            }

            return convertedValue;
        }

        private IEnumerator<Cell> GetExcelCellEnumerator(Row row)
        {
            int currentCount = 0;
            foreach (Cell cell in row.Descendants<Cell>())
            {
                string columnName = GetColumnName(cell.CellReference);

                int currentColumnIndex = ConvertColumnNameToNumber(columnName);

                for (; currentCount < currentColumnIndex; currentCount++)
                {
                    var emptycell = new Cell() { DataType = null, CellValue = new CellValue(string.Empty) };
                    yield return emptycell;
                }

                yield return cell;
                currentCount++;
            }
        }

        private string ReadExcelCell(Cell cell, SharedStringItem[] sharedStrings)
        {
            if (cell == null)
            {
                return string.Empty;
            }
            string text;
            var cellValue = cell.CellValue;
            if (cell.DataType != null && cell.DataType == CellValues.SharedString)
            {
                int index = Convert.ToInt32(cell.CellValue.Text);
                SharedStringItem sharedString = sharedStrings.ElementAtOrDefault(index);
                text = sharedString.InnerText;
            }
            else
            {
                text = (cellValue == null) ? cell.InnerText : cellValue.Text;
            }

            return (text ?? string.Empty).Trim();
        }

        private SLExcelData ReadRowsAndSharedStrings(Stream file, string sheetName, out IList<Row> rows, out SharedStringItem[] sharedStrings)
        {
            var data = new SLExcelData();
            // Open the excel document
            rows = new List<Row>();
            sharedStrings = new List<SharedStringItem>().ToArray();
            try
            {
                var document = SpreadsheetDocument.Open(file, false);
                WorkbookPart workbookPart = document.WorkbookPart;

                var sheets = workbookPart.Workbook.Descendants<Sheet>();
                var sheet = sheets.FirstOrDefault(x => x.Name == sheetName);
                if (sheet == null)
                {
                    data.Status.Message = $"В файле не найден лист \"{sheetName}\"";
                    return data;
                }

                data.SheetName = sheet.Name;

                var workSheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
                var columns = workSheet.Descendants<Columns>().FirstOrDefault();
                data.ColumnConfigurations = columns;

                var sheetData = workSheet.Elements<SheetData>().First();

                rows = sheetData.Elements<Row>().ToList();

                if (workbookPart.SharedStringTablePart?.SharedStringTable != null)
                {
                    sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Descendants<SharedStringItem>().ToArray();
                }
            }
            catch (Exception e)
            {
                data.Status.Message = "Excel файл не прочитан";
                return data;
            }
            return data;
        }

        private void FillSLExcelData(SLExcelData data, IList<Row> rows, SharedStringItem[] sharedStrings, int headerIndex, int? dataIdx)
        {
            // Read the header
            if (rows.Count > headerIndex)
            {
                var row = rows[headerIndex];
                var cellEnumerator = GetExcelCellEnumerator(row);
                while (cellEnumerator.MoveNext())
                {
                    var cell = cellEnumerator.Current;
                    var text = ReadExcelCell(cell, sharedStrings).Trim();
                    data.Headers.Add(text);
                }
            }
            int dataIndex = dataIdx.HasValue ? dataIdx.Value : headerIndex + 1;
            // Read the sheet data
            if (rows.Count > dataIndex)
            {
                for (var i = dataIndex; i < rows.Count; i++)
                {
                    var dataRow = new List<string>();
                    data.DataRows.Add(dataRow);
                    var row = rows[i];
                    var cellEnumerator = GetExcelCellEnumerator(row);
                    while (cellEnumerator.MoveNext())
                    {
                        var cell = cellEnumerator.Current;
                        var text = ReadExcelCell(cell, sharedStrings).Trim();
                        dataRow.Add(text);
                    }
                    if (data.ColumnConfigurations != null && dataRow.Count() < data.ColumnConfigurations.Count())
                    {
                        int currentCount = dataRow.Count();
                        for (; currentCount < data.ColumnConfigurations.Count(); currentCount++)
                        {
                            dataRow.Add(string.Empty);
                        }
                    }
                }
            }
        }

        public IEnumerable<SLExcelData> ReadAllExcel(IFormFile file)
        {
            var datas = new List<SLExcelData>();

            // Check if the file is excel
            if (file.Length <= 0)
            {
                datas.First().Status.Message = "You uploaded an empty file";
                return datas;
            }

            if (file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                datas.First().Status.Message = "Please upload a valid excel file of version 2007 and above";
                return datas;
            }

            // Open the excel document
            List<Row> rows;
            SharedStringItem[] sharedStrings = new List<SharedStringItem>().ToArray();
            try
            {
                SpreadsheetDocument document;
                using (Stream stream = file.OpenReadStream())
                {
                    document = SpreadsheetDocument.Open(stream, false);
                }
                WorkbookPart workbookPart = document.WorkbookPart;

                var sheets = workbookPart.Workbook.Descendants<Sheet>();

                foreach (var sheet in sheets)
                {
                    var data = new SLExcelData();
                    data.SheetName = sheet.Name;

                    var workSheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
                    var columns = workSheet.Descendants<Columns>().FirstOrDefault();
                    data.ColumnConfigurations = columns;

                    var sheetData = workSheet.Elements<SheetData>().First();

                    rows = sheetData.Elements<Row>().ToList();

                    if (workbookPart.SharedStringTablePart?.SharedStringTable != null)
                    {
                        sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Descendants<SharedStringItem>().ToArray();
                    }

                    if (rows.Count > 0)
                    {
                        var row = rows[0];
                        var cellEnumerator = GetExcelCellEnumerator(row);
                        while (cellEnumerator.MoveNext())
                        {
                            var cell = cellEnumerator.Current;
                            var text = ReadExcelCell(cell, sharedStrings).Trim();
                            data.Headers.Add(text);
                        }
                    }

                    // Read the sheet data
                    if (rows.Count > 1)
                    {
                        for (var i = 1; i < rows.Count; i++)
                        {
                            var dataRow = new List<string>();
                            data.DataRows.Add(dataRow);
                            var row = rows[i];
                            var cellEnumerator = GetExcelCellEnumerator(row);
                            while (cellEnumerator.MoveNext())
                            {
                                var cell = cellEnumerator.Current;
                                var text = ReadExcelCell(cell, sharedStrings).Trim();
                                dataRow.Add(text);
                            }
                        }
                    }

                    datas.Add(data);
                }


            }
            catch (Exception e)
            {
                datas.First().Status.Message = "Unable to open the file";
                return datas;
            }

            return datas;

        }

        public SLExcelData ReadExcel(IFormFile file)
        {
            var data = new SLExcelData();

            // Check if the file is excel
            if (file.Length <= 0)
            {
                data.Status.Message = "You uploaded an empty file";
                return data;
            }

            if (file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                data.Status.Message = "Please upload a valid excel file of version 2007 and above";
                return data;
            }

            // Open the excel document
            List<Row> rows;
            SharedStringItem[] sharedStrings = new List<SharedStringItem>().ToArray();
            try
            {
                SpreadsheetDocument document;
                using (Stream stream = file.OpenReadStream())
                {
                    document = SpreadsheetDocument.Open(stream, false);
                }
                WorkbookPart workbookPart = document.WorkbookPart;

                var sheets = workbookPart.Workbook.Descendants<Sheet>();
                var sheet = sheets.First();
                data.SheetName = sheet.Name;

                var workSheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
                var columns = workSheet.Descendants<Columns>().FirstOrDefault();
                data.ColumnConfigurations = columns;

                var sheetData = workSheet.Elements<SheetData>().First();


                rows = sheetData.Elements<Row>().ToList();

                if (workbookPart.SharedStringTablePart?.SharedStringTable != null)
                {
                    sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Descendants<SharedStringItem>().ToArray();
                }
            }
            catch (Exception e)
            {
                data.Status.Message = "Unable to open the file";
                return data;
            }

            // Read the header
            if (rows.Count > 0)
            {
                var row = rows[0];
                var cellEnumerator = GetExcelCellEnumerator(row);
                while (cellEnumerator.MoveNext())
                {
                    var cell = cellEnumerator.Current;
                    var text = ReadExcelCell(cell, sharedStrings).Trim();
                    data.Headers.Add(text);
                }
            }

            // Read the sheet data
            if (rows.Count > 1)
            {
                for (var i = 1; i < rows.Count; i++)
                {
                    var dataRow = new List<string>();
                    data.DataRows.Add(dataRow);
                    var row = rows[i];
                    var cellEnumerator = GetExcelCellEnumerator(row);
                    while (cellEnumerator.MoveNext())
                    {
                        var cell = cellEnumerator.Current;
                        var text = ReadExcelCell(cell, sharedStrings).Trim();
                        dataRow.Add(text);
                    }
                }
            }

            return data;
        }

        public SLExcelData ReadExcel(Stream file, string sheetName, int headerIndex = 0, int? dataIdx = null)
        {
            SLExcelData data = this.ReadRowsAndSharedStrings(file, sheetName, out IList<Row> rows, out SharedStringItem[] sharedStrings);

            this.FillSLExcelData(data, rows, sharedStrings, headerIndex, dataIdx);

            return data;
        }

        public SLExcelData ReadExcel(Stream file, string sheetName, string header, int? dataIdx = null)
        {
            SLExcelData data = this.ReadRowsAndSharedStrings(file, sheetName, out IList<Row> rows, out SharedStringItem[] sharedStrings);

            int headerIndex = -1;

            for (int i = 0; i < rows.Count(); i++)
            {
                Row row = rows[i];
                var cellEnumerator = GetExcelCellEnumerator(row);
                while (cellEnumerator.MoveNext())
                {
                    var cell = cellEnumerator.Current;
                    var text = ReadExcelCell(cell, sharedStrings).Trim();
                    if (text.ToLowerEquals(header))
                    {
                        headerIndex = i;
                        break;
                    }
                }
                if (headerIndex > -1)
                {
                    break;
                }
            }

            if (headerIndex < 0)
            {
                data.Status.Message = $"На листе \"{sheetName}\" не найден заголовок {header}";
                return data;
            }

            this.FillSLExcelData(data, rows, sharedStrings, headerIndex, dataIdx);

            return data;
        }

        public bool IsExcel(Stream file)
        {
            try
            {
                using (var document = SpreadsheetDocument.Open(file, false)) ;
                file.Position = 0;
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}