I need to import an excel sheet and read the data and add this data to the database in ASP.NET Core MVC.

My table looks like this:

when I have the data in excel this format:

I'm able to read and add the data to database.

But I my Excel sheet is filled like this:

where the date is Day and Score and CutOf is in the single column header and these are repeating in a single row.

So how to read and convert them into a row basis for each guy and add them to the database?

var dt = new DataTable();

//Checking file content length and Extension must be .xlsx
if (file != null && file.ContentType.Length > 0 && System.IO.Path.GetExtension(file.FileName).ToLower() == ".xlsx")
{
    //Create a Folder.
    string path = Path.Combine(hostingEnv.WebRootPath, "Uploads");
    
    if (!Directory.Exists(path))
    {
        Directory.CreateDirectory(path);
    }
    
    //Save the uploaded Excel file.
    string fileName = Path.GetFileName(file.FileName);
    string filePath = Path.Combine(path, fileName);

    using (var stream = new FileStream(filePath, FileMode.Create))
    {
        file.CopyTo(stream);
    }
    
    using (var workbook = new XLWorkbook(filePath))
    {
        IXLWorksheet worksheet = workbook.Worksheet(1);
        bool FirstRow = true;

        //Range for reading the cells based on the last cell used.
        string readRange = "1:1";
        
        foreach (IXLRow row in worksheet.RowsUsed())
        {
            //If Reading the First Row (used) then add them as column name
            if (FirstRow)
            {
                //Checking the Last cellused for column generation in datatable
                readRange = string.Format("{0}:{1}", 1, row.LastCellUsed().Address.ColumnNumber);
                
                foreach (IXLCell cell in row.Cells(readRange))
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                
                FirstRow = false;
            }
            else
            {
                //Adding a Row in datatable
                dt.Rows.Add();
                int cellIndex = 0;
                
                //Updating the values of datatable
                foreach (IXLCell cell in row.Cells(readRange))
                {
                    dt.Rows[dt.Rows.Count - 1][cellIndex] = cell.Value.ToString();
                    cellIndex  ;
                }
            }
        }
        
        //If no data in Excel file
        if (FirstRow)
        {
            ViewBag.Message = "Empty Excel File!";
        }

CodePudding user response：

Since you have a dynamic number of columns, it's really just a matter of looping through the columns and grabbing the values. I recreated this on my machine.

using System;
using System.Collections.Generic;
using ClosedXML.Excel;

namespace HelloDotNetCore
{
    class Program
    {
        static void Main(string[] args)
        {
            var scoreResults = new List<ScoreResult>();

            using (var workbook = new XLWorkbook("Scores.xlsx"))
            {
                var isHeaderRow = true;

                foreach (var row in workbook.Worksheet(1).RowsUsed())
                {
                    if (isHeaderRow)
                    {
                        isHeaderRow = false;
                        continue;
                    }

                    var name = row.Cell(1).GetString();

                    // This goes through the Date, Score column pairs until the end is reached for that row
                    for (int i = 2; i <= row.LastCellUsed().Address.ColumnNumber; i =2)
                    {
                        Console.WriteLine(row.Cell(i).GetString());

                        var scoreResult = new ScoreResult
                        {
                            Name = name,
                            DateTaken = row.Cell(i).GetDateTime(),
                            Score = row.Cell(i   1).GetValue<int>(),
                        };

                        scoreResults.Add(scoreResult);
                    }
                }
            }

            foreach (var s in scoreResults)
            {
                Console.WriteLine($"Name: {s.Name}, DateTaken: {s.DateTaken}, Score: {s.Score}");
            }
        }
    }

    class ScoreResult
    {
        public string Name { get; set; }

        public DateTime DateTaken { get; set; }

        public int Score { get; set; }
    }
}

Notice how it grabs the scores, and stores them in a list for later use? That's far preferable to using a DataTable.