Question 87545 in ASP.NET Question posted in
The official documentation can be found here.

How to read excel sheet with multiple Headers – Asp.net

how to read the data from excel worksheet using microsoft oldb providers in dotnet c# and i want read the data and save into db.

below is sample format for data reading in excel sheets.

enter image description here

thanks in adavance

Tags:

2

Answers


  1. 0

    If you’re allowed to use ClosedXml you can do something like this

    private static IReadOnlyCollection<Planet> GetPlanets()
{
    // open the workbook
    var workbook = new XLWorkbook("Book1.xlsx");
    var sheet1 = workbook.Worksheet(1);
    var headers = sheet1.Row(1);
    var headersCount = headers.Cells().Count(cell => !cell.IsEmpty());

    var planets = new List<Planet>();

    // loop around each header 
    for (int i = 2; i < headersCount + 3; i=i+2)
    {
        var name = headers.Cell(i).Value?.ToString();
        if (string.IsNullOrEmpty(name))
        {
            break;
        }
        var produced = new Produced(
            int.Parse(sheet1.Cell(3, i).Value?.ToString()),
            int.Parse(sheet1.Cell(4, i).Value?.ToString())
        );
        var sold = new Sold(
            int.Parse(sheet1.Cell(3, i + 1).Value?.ToString()),
            int.Parse(sheet1.Cell(4, i + 1).Value?.ToString())
        );
        planets.Add(new Planet(
            name,
            produced,
            sold
        ));
    }

    return planets;
}

    where

    public sealed record Planet(string? Name, Produced Produced, Sold Sold);
public sealed record Produced(int TeddyBears, int BoardGames) : Metric(TeddyBears, BoardGames);
public sealed record Sold(int TeddyBears, int BoardGames) : Metric(TeddyBears, BoardGames);
public abstract record Metric(int TeddyBears, int BoardGames);

    This of course assumes the exact structure from your example, but its easy to configure it to do whatever you need

    Login or Signup to reply.
  2. 0

    using IronXL Library it will be very easy as you can see in the following code sample

            WorkBook workBook = WorkBook.Load("File.xlsx");
        var newSheet = workBook.DefaultWorkSheet;
        string ColumnsNames = "ABCDE";
        //Skip first 2 row headers and Start from Row number 3
        for (int row = 3; row <= 5; row++)
        {
            var DataBaseRowModel = new DataBaseRowModel();
            foreach (char col in ColumnsNames)
            {
                var cellName = $"{col}{row}";

                switch (col)
                {
                    case 'A': DataBaseRowModel.Name = newSheet[cellName].Value; break;
                    case 'B': DataBaseRowModel.MarsProduced = newSheet[cellName].Value; break;
                    case 'C': DataBaseRowModel.MarsSold = newSheet[cellName].Value; break;
                    case 'D': DataBaseRowModel.VenusProduced = newSheet[cellName].Value; break;
                    case 'E': DataBaseRowModel.MarsSold = newSheet[cellName].Value; break;
                    default: break;
                }
            }
            DataBaseContext.CreateNewRow(DataBaseRowModel);
        }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search