Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Extract data with Collections explanation #27

Open
baffourt opened this issue Jan 17, 2021 · 1 comment
Open

Extract data with Collections explanation #27

baffourt opened this issue Jan 17, 2021 · 1 comment

Comments

@baffourt
Copy link

baffourt commented Jan 17, 2021

This is an excellent project, thank you @ipvalverde for the time. I have a quick question
How do i extract this data into this ScoresPoco

public class ScoresPoco
{
    public int ApplicantID { get; set; }
    public List<ScoreData> Scores { get; set; }
}

public class ScoreData
{
    public int SubjectID { get; set; }
    public double Score { get; set; }
}

image

Subject ID is in Row 2 hidden for both C & D columns

I am extracting with this

var data = worksheet.Extract<ScoresPoco>()
.WithProperty(p => p.ApplicantID, "B")
.WithCollectionProperty(p => p.Scores,
                        item => item.SubjectID, 1,
                        item => item.Score, "C", "E")
.GetData(model.DataRowStart, row => worksheet.Cells[row, model.DataColStart].Value != null)
.ToList();

This only returns the ApplicantID and Scores correctly but the subjectID is always 0.
There can also be more than 2 or Subjects, but all the IDs will be in the Row 2.
When debugging, I can see all that data in the worksheet but a way to add it to the SubjectID is my question.

@baffourt
Copy link
Author

baffourt commented Jan 17, 2021

I was able to solve this after asking the question.

using ExcelPackage excelPackage = new ExcelPackage(model.FileData);
            var worksheet = excelPackage.Workbook.Worksheets[0];
            var data = worksheet.Extract<ScoresPoco>()
                .WithProperty(p => p.ApplicantID, "B")
                    .WithCollectionProperty(p => p.Scores,
                        item => item.SubjectID, 2,
                        item => item.Score, "C", "F")
                .GetData(model.DataRowStart, row => worksheet.Cells[row, model.DataColStart].Value != null)
                .ToList();

Setting this line .WithCollectionProperty(p => p.Scores, item => item.SubjectID, 2, instead of 1 was able to present the SubjectIDs correctly. So am only left with If the subjects in the excel sheet are more than 2.
For now I handle it by specifying a higher number of cells (C to F), it may be C to J in the future

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant