Export to Excel from Map

[Rodney Bowden]

Hi There, I am trying to export to excel in a particular format. Below is my current code:  

public class NurseRosterExcelExporter extends AbstractXlsxSolutionExporter<NurseRoster> {

	@Override
	public XlxsOutputBuilder<NurseRoster> createXlxsOutputBuilder() {
		return new NurseRosteringOutputBuilder();
	}

	public static class NurseRosteringOutputBuilder
			extends
				XlxsOutputBuilder<NurseRoster> {
	
		private NurseRoster nurseRoster;

		public void setSolution(NurseRoster solution) {
			nurseRoster = solution;
		}

		public void writeSolution(File outputFile) {
			
			List<ShiftAssignment> shiftAssignmentList;
			final List<ShiftAssignment> assignment = (List<ShiftAssignment>) nurseRoster
					.getShiftAssignmentList();
			shiftAssignmentList = new ArrayList<>(assignment.size());
			// Turn the list into a map
			Map<Employee, List<ShiftAssignment>> assignmentMap = assignment
					.stream().collect(Collectors
							.groupingBy(ShiftAssignment::getEmployee));
			// Looked at using the dates as a key
			Map<ShiftDate, List<ShiftAssignment>> assignmentdateMap = assignment
					.stream().collect(Collectors
							.groupingBy(ShiftAssignment::getShiftDate));
			// Just so I could see in the first instance
			System.out.println(assignmentdateMap);

			Workbook workbook = new XSSFWorkbook();
			Sheet sheet = workbook.createSheet("ShiftAssignments");
			// currently no using this
			Set<ShiftDate> datekeyset = assignmentdateMap.keySet();
			// Sets up the Map for the Employee
			Set<Employee> keyset = assignmentMap.keySet();

			int rowNum = 2;
           //hopefully to set up date header which is what I want with shift types under
			Row header = sheet.createRow(1);

			for (Employee key : keyset) {
				// doesn't move the cell but increments the row later
				int cellIndex = 0;
				Row row = sheet.createRow(rowNum++);
				row.createCell(cellIndex++).setCellValue(key.getName());
				
				//Places the each employees details into list
				List<ShiftAssignment> objArr = assignmentMap.get(key);
				
				for (ShiftAssignment obj : objArr) {

					//displays the shift type 
					Cell cell = row.createCell(cellIndex++);
			//		cell.setCellValue(obj.getShiftType().getCode());
					//will display both the date and shift type next to employee
					cell.setCellValue(obj.toString());
					// I want the dates as headers
					// header.createCell(cellIndex++).setCellValue(obj.getShiftDate().getLabel());

				}

			}
			
				try {

					// Output to selected file
					FileOutputStream fos = new FileOutputStream(outputFile);
					workbook.write(fos);
					fos.flush();
					workbook.close();

					

				} catch (FileNotFoundException e) {

					e.printStackTrace();

					System.out.println("Invalid directory or file not found");

				} catch (IOException e) {

					e.printStackTrace();

					System.out.println(
							"Error occurred while writting excel file to directory");

				}

			}

		}

	}

I can get the following formats.. Viewed better in attachment: What I really want is the date to be a header and the shift code to align to the date and employee. Any suggestions?

Isabella       D L E E L N N L
Anthony Phan   D D L D E D L L
Michael B       D L N L L N N L
Alana Gill       N N N N N N N N
Sophie Dreves   E E D D D L E E
Reece Bamford   N D D L N E E D
Clayton Fletcher   E E E E E D L L
Gemma Nevin       D L L N E E D L
Michael C       E E N N D E L D
Lisa Coull       D L L L D L E E
Karen Ahrens   L E E E N D E D
David Hemmings     L N N N N N N N



Isabella            2019-04-09/D 2019-04-10/D 2019-04-11/L 2019-04-14/E 2019-04-15/E 2019-04-16/L 2019-04-19/N 2019-04-20/N 2019-04-21/L
Anthony Phan        2019-04-08/D 2019-04-09/D 2019-04-10/D 2019-04-11/L 2019-04-15/D 2019-04-16/E 2019-04-17/D 2019-04-18/L 2019-04-19/L
Michael B            2019-04-10/L 2019-04-11/D 2019-04-12/L 2019-04-13/N 2019-04-15/L 2019-04-16/L 2019-04-19/N 2019-04-20/N 2019-04-21/L
Alana Gill            2019-04-08/L 2019-04-09/N 2019-04-10/N 2019-04-11/N 2019-04-12/N 2019-04-16/N 2019-04-17/N 2019-04-18/N 2019-04-21/N
Sophie Dreves        2019-04-08/N 2019-04-12/E 2019-04-13/E 2019-04-14/D 2019-04-15/D 2019-04-16/D 2019-04-17/L 2019-04-20/E 2019-04-21/E
Reece Bamford        2019-04-08/L 2019-04-09/N 2019-04-12/D 2019-04-13/D 2019-04-14/L 2019-04-15/N 2019-04-17/E 2019-04-18/E 2019-04-19/D
Clayton Fletcher    2019-04-08/D 2019-04-09/E 2019-04-10/E 2019-04-11/E 2019-04-15/E 2019-04-16/E 2019-04-17/D 2019-04-18/L 2019-04-19/L
Gemma Nevin         2019-04-10/L 2019-04-11/D 2019-04-12/L 2019-04-13/L 2019-04-14/N 2019-04-17/E 2019-04-18/E 2019-04-19/D 2019-04-20/L
Michael C            2019-04-09/E 2019-04-10/E 2019-04-11/E 2019-04-13/N 2019-04-14/N 2019-04-18/D 2019-04-19/E 2019-04-20/L 2019-04-21/D
Lisa Coull            2019-04-08/N 2019-04-12/D 2019-04-13/L 2019-04-14/L 2019-04-15/L 2019-04-16/D 2019-04-17/L 2019-04-20/E 2019-04-21/E
Karen Ahrens        2019-04-08/E 2019-04-09/L 2019-04-12/E 2019-04-13/E 2019-04-14/E 2019-04-15/N 2019-04-18/D 2019-04-19/E 2019-04-20/D
David Hemmings      2019-04-08/E 2019-04-09/L 2019-04-10/N 2019-04-11/N 2019-04-12/N 2019-04-16/N 2019-04-17/N 2019-04-18/N 2019-04-21/N

[Paul Clapham]

Rodney Bowden wrote:What I really want is the date to be a header and the shift code to align to the date and employee. Any suggestions?

I'm not entirely sure what you mean by a "header" and "align to the date and employee", but I think I have a fairly good idea. However my suggestion would be this: first decide what you want the spreadsheet to look like, then write code to produce a spreadsheet which looks like that.

In your case that might involve you deciding that you want a date to appear in row 3 followed by the employees scheduled to work on that day, followed by another date and the employees scheduled to work on that day. Or you might decide you want all of the dates to appear in row 3 followed by the employees with shift codes under the dates they are working. Anyway once you have that figured out and scribbled down on a piece of paper, you could start writing code to produce that layout.

[Carey Brown]

               2019-04-08  2019-04-09  2019-04-10  2019-04-11  2019-04-12  2019-04-13  2019-04-14  2019-04-15  2019-04-16
Isabella                   D           D           L                                   E           E            L
Anthony Phan   D           D           D           L                                               D            E
Michael B                              L           D           L           N                       L            L

Are you thinking of something like this? This would require making one pass through the data to find the min and max dates to use as a header.