mesa-fab-approval/Fab2ApprovalSystem/Misc/ExcelData.cs

#if NET8

using ExcelDataReader;

#else 

using Excel;

#endif

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace Fab2ApprovalSystem.Misc;

public class ExcelData {

    private readonly string _Path;

    public ExcelData(string path) {
        _Path = path;
    }

    public IExcelDataReader getExcelReader() {
        FileStream stream = File.Open(_Path, FileMode.Open, FileAccess.Read);
        IExcelDataReader reader = null;
        try {
            if (_Path.EndsWith(".xls")) {
                reader = ExcelReaderFactory.CreateBinaryReader(stream);
            }
            if (_Path.EndsWith(".xlsx")) {
                reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            }
            return reader;
        } catch (Exception) {
            throw;
        }
    }

    public class ExcelLotInfo {
        public string? LotNo { get; set; }
        public string? LotDispo { get; set; }
    }

    public IEnumerable<ExcelLotInfo> ReadData() {
        List<ExcelLotInfo> r = new();
        ExcelData excelData = new(_Path);
        List<DataRow> lots = excelData.getData().ToList();

        int lotDispoColumnIndex = -1;
        foreach (DataColumn col in lots[0].Table.Columns) {
            if (col.ColumnName.ToLower().Contains("dispo")) {
                lotDispoColumnIndex = col.Ordinal;
                break;
            }
        }

        foreach (DataRow row in lots) {
            string temValue = row[0].ToString();
            if (temValue.Trim().Length > 0 && temValue.Trim().Length <= 10) {
                r.Add(new ExcelLotInfo() {
                    LotNo = row[0].ToString(),
                    LotDispo = (lotDispoColumnIndex >= 0 ? row[lotDispoColumnIndex].ToString() : "")
                });
            }

        }
        return r;
    }

    public IEnumerable<string> ReadQDBFlagData() {
        List<string> s = new();
        ExcelData excelData = new(_Path);
        IEnumerable<DataRow> lotNos = excelData.getData();
        foreach (DataRow row in lotNos) {
            string temValue = row[0].ToString();
            if (temValue.Trim().Length > 0 && temValue.Trim().Length == 9) {
                if (row[2].ToString().ToUpper() != "YES" && row[2].ToString().ToUpper() != "NO") {
                    throw new Exception("Invalid data in the file");
                } else {
                    s.Add(row[0].ToString() + "~" + row[1] + "~" + row[2]);
                }
            }

        }
        return s;
    }

#if NET8

    public IEnumerable<DataRow> getData(bool firstRowIsColumnNames = true) =>
        throw new NotImplementedException();

#else

    public IEnumerable<DataRow> getData(bool firstRowIsColumnNames = true) {
        IExcelDataReader reader = getExcelReader();
        reader.IsFirstRowAsColumnNames = firstRowIsColumnNames;
        var workSheet = reader.AsDataSet().Tables[0];
        var rows = from DataRow a in workSheet.Rows select a;
        return rows;
    }

#endif

}