loop through excel package list assign data table dynamically in c#

Question

Hi all, am having an excel sheet neet to import in asp.net core and save the data in sql server. i have readed the excel sheet need to convert that excel sheet columns into key,value pair like below ID Name Age Address 1 john 12 Delhi 2 rohit 14 mumbai need to convert above table columns into key and value pair dynamically based on rows and columns my expected output is below Fieldname value ID 1 Name john Age 12 Address delhi how can i achieve in c# dynamically

Roshan Kumar · Answer

the easiest way to save excel data in the database by reading excel files and then convert excel data into a data table. you can save some libraries available to read excel files e.g. EPPlus. using OfficeOpenXml; public static DataTable GetDataTableFromExcel( string ExcelFilePath, bool hasHeader = true ) { using (var pck = new ExcelPackage()) { using (var stream = File.OpenRead( ExcelFilePath )) { pck.Load(stream); } var ws = pck.Workbook.Worksheets.First(); DataTable tbl = new DataTable(); foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { tbl.Columns.Add(hasHeader ? firstRowCell.Text : string .Format( "Column {0}" , firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for ( int rowNum = startRow; rowNum { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; DataRow row = tbl.Rows.Add(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } } return tbl; } }