how to write a console application that will Import the excel file

List all the training registrations for all the courses. The following information should be displayed. ? A list of all courses and their training registrations. After every course training iteration, display a list of all delegates registered with all their relevant details (e.g address, dietary options etc ) Please ensure that your lab meet the below technology requirements. ? The Application should be in a source version control repository e.g GitHub ,GitLab, Bitbucket, Microsoft's Azure DevOps etc . ? Use Entity framework (EF) code first approach or database first approach. ? If you chose code first approach use Entity framework (EF) migration to propagate your database (or to deploy your changes to thedatabase). ? Use Entity Framework and Linq to retrieve all the required information that need to be displayed. ? Use any plugin / library to read the excel document “Training.xlsx”. Bonus Task The application should be able to register a delegate for training

use OpenXml

var table = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"C:\Users\ReadDatafromExcel.xlsx", false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild().Elements();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild();
IEnumerable rows = sheetData.Descendants();
foreach (Cell cell in rows.ElementAt(0))
{
table.Columns.Add(GetCellValue(spreadSheetDocument, cell));
}
foreach (Row row in rows) //this will also include your header row...
{
DataRow tempRow = table.NewRow();
for (int i = 0; i < row.Descendants().Count(); i++)
{
tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants().ElementAt(i));
}
table.Rows.Add(tempRow);
}
}
table.Rows.RemoveAt(0);
return table;

The above code will read data from excel. and its loaded in datatable. and now you can add to Database

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Vishal Yelve4y ago
Accepted answer
use OpenXml
var table = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"C:\Users\ReadDatafromExcel.xlsx", false))
{
    WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
    IEnumerable sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild().Elements();
    string relationshipId = sheets.First().Id.Value;
    WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
    Worksheet workSheet = worksheetPart.Worksheet;
    SheetData sheetData = workSheet.GetFirstChild();
    IEnumerable rows = sheetData.Descendants();

    foreach (Cell cell in rows.ElementAt(0))
    {
        table.Columns.Add(GetCellValue(spreadSheetDocument, cell));
    }

    foreach (Row row in rows) //this will also include your header row...
    {
        DataRow tempRow = table.NewRow();

        for (int i = 0; i < row.Descendants().Count(); i++)
        {
            tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants().ElementAt(i));
        }

        table.Rows.Add(tempRow);
    }
}

table.Rows.RemoveAt(0);

return table;
The above code will read data from excel. and its loaded in datatable. and now you can add to Database
+2