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;
}