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

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

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 <= ws.Dimension.End.Row; 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;
}
}

1 Reply

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

Roshan Kumar5y ago
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 <= ws.Dimension.End.Row; 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;
            }
        }
0