private NAVObject CreateNAVObject(WorkbookPart workbookPart, Row r)
{
NAVObject navObj = new NAVObject();
int i = 1;
foreach (Cell c in r.Elements <Cell>())
{
string value = ReadCellValue(c, workbookPart);
navObj.AddObjDescription(i, value);
i++;
}
return(navObj);
}
private void UpdateObjectData(NAVObject navObj)
{
//* Insert complete Object into Dictionary
if (!ObjData.ContainsKey(navObj.TableNo))
{
List <NAVObject> newList = new List <NAVObject>();
newList.Add(navObj);
ObjData.Add(navObj.TableNo, newList);
}
else
{
List <NAVObject> existingList = ObjData[navObj.TableNo];
existingList.Add(navObj);
}
}
private void UpdateReferences(NAVObject navObj)
{
//* Update Reference Table
if (navObj.RelationTableNo != 0)
{
if (!ReferenceData.ContainsKey(navObj.RelationTableNo))
{
List <NAVObject> newList = new List <NAVObject>();
newList.Add(navObj);
ReferenceData.Add(navObj.RelationTableNo, newList);
}
else
{
List <NAVObject> existingList = ReferenceData[navObj.RelationTableNo];
existingList.Add(navObj);
}
}
}
public void ReadExcel(string fileName, bool ignoreFirstLine)
{
int ignoreLine = 0;
if (ignoreFirstLine)
{
ignoreLine = 1;
}
using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(fileName, true))
{
//Get workbookpart
WorkbookPart workbookPart = excelDocument.WorkbookPart;
//then access to the worksheet part
IEnumerable <WorksheetPart> worksheetPart = workbookPart.WorksheetParts;
foreach (WorksheetPart WSP in worksheetPart)
{
//find sheet data
IEnumerable <SheetData> sheetData = WSP.Worksheet.Elements <SheetData>();
// Iterate through every sheet inside Excel sheet
foreach (SheetData SD in sheetData)
{
IEnumerable <Row> row = SD.Elements <Row>(); // Get the row IEnumerator
foreach (Row r in SD.Elements <Row>())
{
if (r.RowIndex > ignoreLine)
{
NAVObject navObj = CreateNAVObject(workbookPart, r);
if (navObj.FieldName != null)
{
UpdateObjectData(navObj);
UpdateReferences(navObj);
}
}
}
}
}
}
}
private int OpenExcelSheet()
{
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = _xlApp.Workbooks.Open(_excelFilePath);
_Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlWorksheet.Rows.Count;
rowCount = 100; // TEST
int colCount = 6; // xlWorksheet.Columns.Count;
//iterate over the rows and columns and print to the console as it appears in the file
//excel is not zero based!!
for (int i = 1; i <= rowCount; i++)
{
if (i > 1)
{
NAVObject navObj = new NAVObject();
for (int j = 1; j <= colCount; j++)
{
////new line
//if (j == 1)
// Console.Write("\r\n");
//write the value to the console
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
{
navObj.AddObjDescription(j, xlRange.Cells[i, j].Value2.ToString());
}
//_excelData.Add(new ExcelColumnInfo(i, j), xlRange.Cells[i, j].Value2.ToString());
//Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");
if (j == colCount)
{
if ((navObj.FieldName.ToUpper().Contains("NAME")) || (navObj.FieldName.ToUpper().Contains("ADDRESS")))
{
if ((navObj.FieldType.ToUpper() == "CODE") || (navObj.FieldType.ToUpper() == "TEXT"))
{
_objData.Add(navObj.TableNo.ToString() + "_" + navObj.FieldName, navObj);
}
}
}
}
}
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
_xlApp.Quit();
Marshal.ReleaseComObject(_xlApp);
return(_objData.Count);
}