prs/prs.desktop/Utils/ExcelImporter.cs

using System.Collections;
using System.Collections.Generic;
using System.IO;
using InABox.Core;
using NPOI.SS.UserModel;

namespace PRSDesktop
{
    public class ExcelImporter<T> : BaseImporter<T> where T : Entity, IRemotable, IPersistent, new()
    {
        private readonly Dictionary<string, int> _columns = new();
        private IEnumerator _row;
        private ISheet _sheet = null;
        private IWorkbook _workbook;

        private DataFormatter formatter = new();

        public override bool Open(Stream stream)
        {
            var xls = WorkbookFactory.Create(stream);
            var sheet = xls.GetSheetAt(0);
            _row = sheet.GetRowEnumerator();
            _row.MoveNext();
            return true;
        }

        public override void Close()
        {
            if (_workbook != null)
                _workbook.Close();
            _workbook = null;
        }

        public override bool ReadHeader()
        {
            _columns.Clear();

            var fields = new List<string>();

            for (var i = 1; i < HeaderRow; i++)
                if (!MoveNext())
                    return false;

            IEnumerator cells = ((IRow)_row.Current).GetEnumerator();
            while (cells.MoveNext())
            {
                var cell = (ICell)cells.Current;
                var value = cell.ToString();
                if (!string.IsNullOrWhiteSpace(value))
                {
                    fields.Add(value);
                    _columns[value] = cell.ColumnIndex;
                }

                Fields = fields.ToArray();
            }

            return true;
        }

        public override bool MoveNext()
        {
            return _row.MoveNext();
        }

        public override Dictionary<string, string> ReadLine()
        {
            var results = new Dictionary<string, string>();
            var row = _row.Current as IRow;
            foreach (var mapping in Mappings)
            {
                var field = mapping.Field;
                if (!string.IsNullOrWhiteSpace(field) && _columns.ContainsKey(field))
                {
                    var cell = row.GetCell(_columns[field]);
                    var value = "";
                    if (cell != null)
                    {
                        var type = cell.CellType == CellType.Formula ? cell.CachedFormulaResultType : cell.CellType;
                        if (type == CellType.Boolean)
                        {
                            value = cell.BooleanCellValue.ToString();
                        }
                        else if (type == CellType.Numeric)
                        {
                            if (DateUtil.IsCellDateFormatted(cell))
                                value = cell.DateCellValue.ToString();
                            else
                                value = cell.NumericCellValue.ToString();
                        }
                        else if (type == CellType.String)
                        {
                            value = cell.StringCellValue;
                        }
                        else
                        {
                            value = cell.ToString();
                        }
                    }


                    results[mapping.Property] = value;
                }
                else
                {
                    results[mapping.Property] = mapping.Constant;
                }
            }

            return results;
        }
    }
}

/*

//css_ref NPOI.dll
//css_ref NPOI.OOXML.dll
//css_ref NPOI.OpenXml4Net.dll

using PRSDesktop;
using Comal.Classes;
using PRSDesktop.Utils;
using InABox.Clients;
using InABox.Configuration;
using InABox.Core;
using Syncfusion.UI.Xaml.Kanban;
using Syncfusion.Windows.Controls.Grid;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Runtime;
using System.Diagnostics;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Media;
using System.Collections;
using PRSDesktop.Forms.Export;
using System.IO;
using Ookii.Dialogs.Wpf;
using NPOI.SS.UserModel;
using System;
using System.Reflection;
using System.Linq.Expressions;
using InABox.Reports;

public class Module
{

public Object Data { get; set; }

private int GetColumn(IRow row, String name)
{
    IEnumerator cells = row.GetEnumerator();
    while (cells.MoveNext())
    {
        ICell cell = (ICell)cells.Current;
        if ((!String.IsNullOrWhiteSpace(cell.StringCellValue)) && (cell.StringCellValue.ToUpper().Trim().Equals(name.ToUpper().Trim())))
            return cell.ColumnIndex;
    }
    throw new Exception("Unable to find Column: " + name);
}

public String GetCellValue(ICell cell)
{
    if (cell.CellType == NPOI.SS.UserModel.CellType.Formula)
    {
        if (cell.CachedFormulaResultType == NPOI.SS.UserModel.CellType.Numeric)
            return String.Format("{0:F}", cell.NumericCellValue.ToString());
        else
            return cell.StringCellValue;
    }
    return cell.ToString().Trim();
}

public void Set<T>(T item, Expression<Func<T>> property, object value, ref bool changed)
{
    var member = (MemberExpression)property.Body;
    string propertyName = member.Member.Name;
    object val = CoreUtils.GetPropertyValue(item,propertyName);
    if ( ((val == null) && (value != null)) || (!val.Equals(value)) )
    {
        CoreUtils.SetPropertyValue(item,propertyName,value);
        changed = true;
       }    
}

public void SetUserProperty<T>(T item, String userprop, object value, ref bool changed) where T : Entity
{
    var userprops = item.UserProperties;
    object val = userprops.ContainsKey(userprop) ? userprops[userprop] : null;        
    if ( ((val == null) && (value != null)) || (!val.Equals(value)) )
    {
        userprops[userprop] = value;
        changed = true;
       }    
}

public bool Execute()
{
    VistaOpenFileDialog dlg = new VistaOpenFileDialog();
    dlg.Filter = "Excel Files|*.xls; *.xlsx";
    if (dlg.ShowDialog() != true)
        return false;
        
    Progress.Show("Starting Up");    
        

    Progress.SetMessage("Loading UOM Codes");
    List<ProductUOM> uoms = new Client<ProductUOM>().Load().ToList();

    Progress.SetMessage("Loading Groups");
    List<ProductGroup> groups = new Client<ProductGroup>().Load().ToList();

    Progress.SetMessage("Loading Products");
    List<Product> products = new Client<Product>().Load().ToList();
    
    Progress.SetMessage("Loading Suppliers");
    List<Supplier> suppliers = new Client<Supplier>().Load().ToList();
    
    Progress.SetMessage("Loading Supplier Products");
    List<SupplierProduct> supplierproducts = new Client<SupplierProduct>().Load().ToList();
    
    List<Guid> labels = new List<Guid>();
    
    Progress.SetMessage("Opening " + Path.GetFileName(dlg.FileName));
            
    List<String> Log = new List<string>();
    Log.Add(String.Format("Import Log {0:dd/MM/yyyy hh:mm:ss}",DateTime.Now));
    Log.Add("==============================");

    try
    {
    
                   
        using (FileStream file = new FileStream(dlg.FileName, FileMode.Open, FileAccess.Read))
        {
            IWorkbook xls = WorkbookFactory.Create(file);
            ISheet sheet = xls.GetSheet("Sheet1");
            IEnumerator rows = sheet.GetRowEnumerator();
            rows.MoveNext();
            
            IRow headerrow = (IRow)rows.Current;
            

            int ProductCodeColumn = GetColumn(headerrow,"Item Number");
            int ProductNameColumn = GetColumn(headerrow,"Item Description");
            int GroupColumn = GetColumn(headerrow, "Item Type");
            int UOMColumn = GetColumn(headerrow, "Base UOM");
            int SupplierColumn = GetColumn(headerrow, "Dflt Vendor");
            int CostColumn = GetColumn(headerrow, "Cost");
           
           
            List<String> Messages = new List<String>();

            int Row = 1;
            while ((rows.MoveNext()) && (Row <= 1)) //int.MaxValue))
            {
                Progress.SetMessage("Processing Row "+Row.ToString());
                Messages.Clear();
                Row++;
                IRow row = (IRow)rows.Current;
               
                try
                {
                
                    
                    Messages.Add("Getting Product Code");                    
                    String productcode = row.GetCell(ProductCodeColumn, MissingCellPolicy.CREATE_NULL_AS_BLANK).StringCellValue.Trim();
                    if (!String.IsNullOrWhiteSpace(productcode))
                    {
                        String productname = row.GetCell(ProductNameColumn, MissingCellPolicy.CREATE_NULL_AS_BLANK).StringCellValue.Trim();
                    
                    
                        Messages.Add("Getting UOM");
                        String uomcode = row.GetCell(UOMColumn, MissingCellPolicy.CREATE_NULL_AS_BLANK).StringCellValue.Trim();
                        ProductUOM uom = null;
                        if (!String.IsNullOrWhiteSpace(uomcode))
                        {
                            Messages.Add("UOM = ["+uomcode+"]");
                            if (uoms == null)
                                Messages.Add("UOM list is null");
                            uom = uoms.FirstOrDefault(x=>x.Code.Equals(uomcode.ToUpper()));
                            if (uom == null)
                            {
                                Messages.Add("Adding UOM");
                                uom = new ProductUOM()
                                {
                                    Code = uomcode.ToUpper(),
                                    Description = uomcode
                                };                            
                                new Client<ProductUOM>().Save(uom,"Imported from CCS");
                                uoms.Add(uom);
                            }
                        }
                        else
                            uom = new ProductUOM();
                        
                        double UnitSize = 1.0;                            
                        // If its an extrusion (ie uom = "L"), then strip out the length from the end of the code (if any)
                        if (String.Equals(uomcode,"L") && productcode.Contains(" "))
                        {
                           String[] comps = productcode.Split(' ');
                           productcode = String.Join(" ",comps.Reverse().Take(1).Reverse());
                           String size = new string(comps.Last().Where(c => char.IsDigit(c) || char.Equals(c,'.')).ToArray());
                           UnitSize = String.IsNullOrEmpty(size) ? 1.0 : double.Parse(size);
                        }
                        
                        //What to do with the Unit Size Here? 
                        // Pro-Rata the cost? 
                        // Try to identify default length (compare to PRS)?
                        // At this stage, we will just use it as the default unit size if a new product is to be created
                                                                          
                        Messages.Add("Getting Group");
                        String groupcode = row.GetCell(GroupColumn, MissingCellPolicy.CREATE_NULL_AS_BLANK).StringCellValue.Trim();
                        ProductGroup group = null;
                        if (!String.IsNullOrWhiteSpace(groupcode))
                        {
                            group = groups.FirstOrDefault(x=>x.Code.Equals(groupcode.ToUpper()));
                            if (group == null)
                            {
                                Messages.Add("Adding Group");
                                group = new ProductGroup()
                                {
                                    Code = groupcode.ToUpper(),
                                    Description = groupcode
                                };                            
                                new Client<ProductGroup>().Save(group,"Imported from CCS");
                                groups.Add(group);
                            }
                        }
                        else
                            group = new ProductGroup();                        
                    
                                               
                        Messages.Add("Locating Product");
                        Product product = products.FirstOrDefault(x=>x.Code.Equals(productcode.ToUpper()));
                        bool labelrequired = false;
                        if (product == null)
                        {
                            Messages.Add("Adding Product");
                            product = new Product()
                            {
                                Code = productcode.ToUpper()
                            };                            

                            product.UnitSize = UnitSize;

                            products.Add(product);
                            labelrequired = true;
                        }
                        product.Group.ID = group.ID;
                        product.Units.ID = uom.ID;
                        product.Name = productname;
                        if (product.IsChanged())
                            new Client<Product>().Save(product,"Imported from CCS"); 

                        if (labelrequired)
                            labels.Add(product.ID);
                        
                        Messages.Add("Getting Supplier");
                        String suppliercode = row.GetCell(SupplierColumn, MissingCellPolicy.CREATE_NULL_AS_BLANK).StringCellValue.Trim();
                        Supplier supplier = suppliers.FirstOrDefault(x=>x.Code.Equals(suppliercode.ToUpper()));
                        if (supplier == null)
                        {
                            Messages.Add("Adding Supplier");
                            supplier = new Supplier()
                            {
                                Code = suppliercode.ToUpper(),
                                Name = suppliercode
                            };
                            new Client<Supplier>().Save(supplier,"Imported from CCS");
                            suppliers.Add(supplier);
                        }
                        
                        Messages.Add("Getting Supplier Cost");
                        double suppliercost = row.GetCell(CostColumn, MissingCellPolicy.CREATE_NULL_AS_BLANK).NumericCellValue;
                        SupplierProduct supprod = supplierproducts.FirstOrDefault(x=>x.ProductLink.ID.Equals(product.ID) && x.SupplierLink.ID.Equals(supplier.ID));
                        if (supprod == null)
                        {
                            Messages.Add("Adding Supplier Product");
                            supprod= new SupplierProduct();
                            supprod.SupplierLink.ID = supplier.ID;
                            supprod.ProductLink.ID = product.ID;
                            supprod.TradePrice = suppliercost;
                            supprod.Discount = 0.0F;
                            supprod.CostPrice = suppliercost;
                            supplierproducts.Add(supprod);
                        }
                        if (supprod.Discount != 100.0F)
                            supprod.TradePrice = supprod.CostPrice * 100.0F / (100.0F / suppliercost);
                        else
                        {
                            supprod.TradePrice = supprod.CostPrice;
                            supprod.CostPrice = 0.0F;
                        }   
                        if (supprod.IsChanged())                           
                            new Client<SupplierProduct>().Save(supprod,"Imported from CCS");
                        product.Supplier.ID = supprod.ID;
                        product.ListPrice = supprod.TradePrice;
                        product.Discount = supprod.Discount;
                        product.NettPrice = supprod.CostPrice;
                        if (product.IsChanged())
                            new Client<Product>().Save(product,"Updated Default Supplier Cost");
                            

                        Messages.Add("Done");
                    }
                }
                catch (Exception ei)
                {
                    Messages.Add(String.Format("Row [{0}]: Exception: {1}", row.RowNum + 1, ei.Message));
                }
                
                Log.AddRange(Messages);

            }

        }

        Progress.Close();
        
        if (labels.Any())
        {
            ProductDataModel model = new ProductDataModel(new Filter<Product>(x=>x.ID).InList(labels.ToArray()));
            ReportTemplate template = new Client<ReportTemplate>().Load(new Filter<ReportTemplate>(x=>x.Section).IsEqualTo(model.Name).And(x=>x.Name).IsEqualTo("Product Barcode")).FirstOrDefault();
            if (template == null)
            {
                template = new ReportTemplate()
                {
                    Section = model.Name,
                    Name = "Product Barcode",
                    Visible = true,
                    SelectedRecords = true,
                    AllRecords = false
                };
                new Client<ReportTemplate>().Save(template,"Autogenerated by CCS Import");
            }                
            ReportUtils.PreviewReport(template,model,!Security.IsAllowed<CanDesignReports>() && !String.IsNullOrWhiteSpace(template.PrinterName), Security.IsAllowed<CanDesignReports>());
        }
        
        
        MessageBox.Show("Import Completed!");

    }
    catch (Exception e)
    {
        Log.Add(e.Message);
        
        Progress.Close();
        MessageBox.Show("Error while Importing File!\n\n" + e.Message);
    }
    
    Log.Add("");
    String LogFile = Path.ChangeExtension(dlg.FileName, ".txt");
    if (File.Exists(LogFile))
        Log.AddRange(File.ReadAllLines(LogFile));
    File.WriteAllLines(LogFile, Log.ToArray());
    
    Process.Start(LogFile);

    return true;

}

}

*/