XlsIO - Search and process spreadsheet

[Paul]

Hey,

I'm looking for guidance here with XlsIO. 

I have a spreadsheet that lists classes and students like so:

STUDENT LIST - TERM 2 (2019-2020)
30/12/2019 - 26/06/2020
START:	30/12/2019				Total:
FINISH:	26/06/2020				L1-L4:
TOTAL:	200 hours				L5:
Level:	Elementary (R )
Group	1.1
Teacher	Blah
Room	302.B10
Time:	Mon&Tues&Wed&Thurs&Fri (12.30-4.30pm)
#	IRN	Last Name	First Name	DOB	Email
1	564366	Bob	Ann	18/11/2001	[email protected]
2	536456	Jim	An	26/12/2001	[email protected]
3	3456453	Frank	Ay	23/06/2001	[email protected]
4	354634563456	Jo	Sid	01/05/2001	[email protected]
5	435634563	Kate	Frank	24/12/2001	[email protected]
Level:	Elementary (R )
Group	1.2
Teacher	Blah Blah
Room	306.B10
Time:	Mon&Tues&Wed&Thurs&Fri (12.30-4.30pm)
#	IRN	Last Name	First Name	DOB	Email
1	32523	My	Vu	11/01/2001	[email protected]
2	65547654	hoang	Long	21/05/2001	[email protected]

There may be anywhere from 15-30 classes.

I have created classes in my program for the levels, classes and student. I saw the XlsIO class has the ability to import a range in a spreadsheet and create a list of objects. This looks great.

However, I'm not sure how to use that to pull all the details from my spreadsheet.

I need to essentially search for the 'Level:' field, create a class with the 4 rows below. Then search for '#' and then Import all rows in that class. Finally, looping to search again for 'Level:' and repeat.

Thanks in advance,

Paul

[Shamini Kiruba Sobers]

Hi Paul, 

  

Greetings from Syncfusion. 

  

You can search for a field in the spreadsheet using FindAll method, which finds the specified field (say ‘Level:’) and stores the ranges that contain the field. Then you can loop and import each range along with the following rows in that class. Kindly look into the following UG links that may help to achieve your requirement. 

  

Help links: 

  

https://help.syncfusion.com/file-formats/xlsio/worksheet-cells-manipulation#find-and-replace 

  

https://help.syncfusion.com/file-formats/xlsio/working-with-data#export-data-from-excel-to-nested-class-objects 

  

Kindly let us know if it helps. 

  

Regards, 

Shamini