Read Related Rows

Aspose.Cells Product Family
1

Hello,

I am developing a Time Table android app. Initially time table is for every section and batch but I want to pick some specific section’s or day’s time table. The problem is that some of the lecture are having their name in a single column but lecture time is in 3 columns or some times in two columns. I just want to pick the related time to every lecture. Sample is attached.
2

Hi Zahid,

Thanks for your posting and using Aspose.Cells.

Please explain your issue in more detail. If we check your Time Table.xlsx which you provided earlier, we see cell G3 containing the value

OO Analysis & Design
Mr. Muhammad Usman

is a merged cell spanning 3 columns G, H, I

and G3, H3 and I3 contains times 10:30-11:00, 11:00-11:30, 11:30-11:55 respectively.

Now, let us know your requirement. Do you want to read the time in G3 shown in red color previously?

3

Yes, I want to read time for every lecture accordingly.

4

Hi Zahid,

Thanks for your posting and using Aspose.Cells.

Please see the following code and its console output for your reference. Just specify the LectureCell and it will return the starting and ending time of the lecture.

Java


String filePath = “F:\Shak-Data-RW\Downloads\Timetable14.xlsx”;


//Specify lecture cell to see the lecture time

String LectureCell = “O5”;


//Create workbook

Workbook workbook = new Workbook(filePath);


//Access worksheet by name

Worksheet worksheet = workbook.getWorksheets().get(“Time Table”);


//Access the cell and its merged range

Cell cell = worksheet.getCells().get(LectureCell);

Range range = cell.getMergedRange();


//Access the Starting time cell from 4th row

Cell stTimeCell = worksheet.getCells().get(3, range.getFirstColumn());


//Print time

System.out.println(stTimeCell.getStringValue());


//Access the Ending time cell from 4th row

Cell endTmeCell = worksheet.getCells().get(3, range.getFirstColumn() + range.getColumnCount() - 1);


//Print time

System.out.println(endTmeCell.getStringValue());

Console Output:
01:30-02:00
03:00-03:30
5

Dear I trying to search is days on whcih give section have lecture and storing them in a LinkedList of Cell but the output the not as I wanted. It give some blank cells or worng days. Please help me.

I m using the same excel file and here is my code to get Day Cells.

public LinkedList isValidSection(String selectedSection) {
LinkedList days = new LinkedList();
for (int i = 4; i < getWorksheetCells().getMaxDataRow(); i++) {
Cell cell=getWorksheet().getCells().get(i, 1);
if (cell.getStringValue()
.equals(selectedSection)) {
days.add(getWorksheet().getCells().get(cell.getRow(), 0));
}
}
return days;
}
6

Hi Zahid,

Thanks for your posting and using Aspose.Cells.

Once you found the cell containing the section, you should then find the first cell inside the merged cell containing your days. The data will reside in the first cell of the merged range. For example, if cell A1, A2, A3 are merged and it contains “Test”, then cell A1 will have this value and A2, and A3 will be null.

The following code explains it all. Here the cell is B46, from B46, we went to A46 but A46 will not contain Day because A46 is actually a merged cell consisting of 6 cells A44:A49, now you need to find A44 which is the first cell and this cell contains your Day i.e Wednesday.

I have also attached the source Excel file used in this code and screenshot for a reference.

Java


String filePath = “F:\Shak-Data-RW\Downloads\Timetable14.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(“Time Table”);


Cell cell = worksheet.getCells().get(“B46”);


//This is the cell found by you, but since this cell is merged

//you need to the first cell inside the merged range

Cell foundCell = worksheet.getCells().get(cell.getRow(), 0);


//This cell will be null

System.out.println("Cell Value: " + foundCell.getStringValue());


//Get the merged range of this cell

Range range = foundCell.getMergedRange();


//This is the first cell of the merged range and this will contain

//your day and it will not be null

Cell neededCell = worksheet.getCells().get(range.getFirstRow(), 0);


System.out.println("Cell Value: " + neededCell.getStringValue());

Console Output:
Cell Value:
Cell Value: Wednesday