Exemple #1
0
        public List<ExcelData> ReadExcel(MemoryStream stream)
        {
            var data = new List<ExcelData>();
            WorkbookPart workbookPart;
            List<Row> rows;
            var document = SpreadsheetDocument.Open(stream, false);
            workbookPart = document.WorkbookPart;

            var sheets = workbookPart.Workbook.Descendants<Sheet>();
            foreach (var sheet in sheets)
            {
                var dataSheet = new ExcelData();
                dataSheet.SheetName = sheet.Name;

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

                var sheetData = workSheet.Elements<SheetData>().First();
                rows = sheetData.Elements<Row>().ToList();
                // 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, workbookPart).Trim();
                        dataSheet.Headers.Add(text);
                    }
                }
                // Read the sheet data
                if (rows.Count > 1)
                {
                    for (var i = 1; i < rows.Count; i++)
                    {
                        var dataRow = new List<string>();
                        dataSheet.DataRows.Add(dataRow);
                        var row = rows[i];
                        var cellEnumerator = GetExcelCellEnumerator(row);
                        while (cellEnumerator.MoveNext())
                        {
                            var cell = cellEnumerator.Current;
                            var text = ReadExcelCell(cell, workbookPart).Trim();
                            dataRow.Add(text);
                        }
                    }
                }
                data.Add(dataSheet);
            }
            return data;
        }
        public bool Import(ExcelData data, string user)
        {
            OracleRepository repo = new OracleRepository();
            var listingMapping = GetMapping(Type, "TLISTING").ToList();
            var officeMapping = GetMapping(Type, "TOFFICE_LISTING").ToList();
            var rentMapping = GetMapping(Type, "TRENT_LISTING").ToList();
            var loadingDockMapping = GetMapping(Type, "TLOADINGDOCK_LISTING").ToList(); ;
            var driveInMapping = GetMapping(Type, "TDRIVE_IN_LISTING").ToList();
            var brokerMapping = GetMapping(Type, "TBROKER_LISTING").ToList();

            var notesMapping = GetMapping(Type, "TNOTES_LISTING").ToList();
            var priceMapping = GetMapping(Type, "TPRICE_MAPPING").ToList();
            var taxMapping = GetMapping(Type, "TTAX_LISTING").ToList();

            for (int i=0;i<data.DataRows.Count();i++)
            {
                decimal? id = null;

                decimal? listing_id= GetExistingEntity("TLISTING", null, listingMapping, data.DataRows[i], data.Headers);

                if (listing_id==null)
                {
                    listing_id=ProcessEntity(null, id, Type, listingMapping, "TLISTING", data.DataRows[i], data.Headers, user);
                }
                else
                {
                    ProcessEntity(null, listing_id, Type, listingMapping, "TLISTING", data.DataRows[i], data.Headers, user);
                }
                var keysBySequence = GetMultipleMapping(officeMapping);
                foreach (var office in keysBySequence.Keys)
                {
                    id = GetExistingEntity("TOFFICE_LISTING", listing_id, keysBySequence[office], data.DataRows[i], data.Headers);
                    ProcessEntity(listing_id, id, null, keysBySequence[office], "TOFFICE_LISTING", data.DataRows[i], data.Headers, user);
                }
                keysBySequence = GetMultipleMapping(rentMapping);
                foreach (var rent in keysBySequence.Keys)
                {
                    id = GetExistingEntity("TRENT_LISTING", listing_id, keysBySequence[rent], data.DataRows[i], data.Headers);
                    ProcessEntity(listing_id, id, null, keysBySequence[rent], "TRENT_LISTING", data.DataRows[i], data.Headers, user);
                }
                keysBySequence = GetMultipleMapping(loadingDockMapping);
                foreach (var loadingDock in keysBySequence.Keys)
                {
                    id = GetExistingEntity("TLOADINGDOCK_LISTING", listing_id, keysBySequence[loadingDock], data.DataRows[i], data.Headers);
                    ProcessEntity(listing_id, id, null, keysBySequence[loadingDock], "TLOADINGDOCK_LISTING", data.DataRows[i], data.Headers, user);
                }
                keysBySequence = GetMultipleMapping(driveInMapping);
                foreach (var driveIn in keysBySequence.Keys)
                {
                    id = GetExistingEntity("TDRIVE_IN_LISTING", listing_id, keysBySequence[driveIn], data.DataRows[i], data.Headers);
                    ProcessEntity(listing_id, id, null, keysBySequence[driveIn], "TDRIVE_IN_LISTING", data.DataRows[i], data.Headers, user);
                }
                keysBySequence = GetMultipleMapping(brokerMapping);
                foreach (var broker in keysBySequence.Keys)
                {
                    id = GetExistingEntity("TBROKER_LISTING", listing_id, keysBySequence[broker], data.DataRows[i], data.Headers);
                    ProcessEntity(listing_id, id, null, keysBySequence[broker], "TBROKER_LISTING", data.DataRows[i], data.Headers, user);
                }
                keysBySequence = GetMultipleMapping(notesMapping);
                foreach (var notes in keysBySequence.Keys)
                {
                    id = GetExistingEntity("TNOTES_LISTING", listing_id, keysBySequence[notes], data.DataRows[i], data.Headers);
                    ProcessEntity(listing_id, id, null, keysBySequence[notes], "TNOTES_LISTING", data.DataRows[i], data.Headers, user);
                }
                keysBySequence = GetMultipleMapping(priceMapping);
                foreach (var price in keysBySequence.Keys)
                {
                    id = GetExistingEntity("TPRICE_LISTING", listing_id, keysBySequence[price], data.DataRows[i], data.Headers);
                    ProcessEntity(listing_id, id, null, keysBySequence[price], "TPRICE_LISTING", data.DataRows[i], data.Headers, user);
                }
                keysBySequence = GetMultipleMapping(taxMapping);
                foreach (var tax in keysBySequence.Keys)
                {
                    id = GetExistingEntity("TTAX_LISTING", listing_id, keysBySequence[tax], data.DataRows[i], data.Headers);
                    ProcessEntity(listing_id, id, null, keysBySequence[tax], "TTAX_LISTING", data.DataRows[i], data.Headers, user);
                }
            }

            return false;
        }