Skip to content

Instantly share code, notes, and snippets.

@michaelbukachi
Created June 30, 2017 13:03
Show Gist options
  • Save michaelbukachi/3387f1f464e5fb4a9d1cecc649e6f60b to your computer and use it in GitHub Desktop.
Save michaelbukachi/3387f1f464e5fb4a9d1cecc649e6f60b to your computer and use it in GitHub Desktop.
Exam timetable parser
package dita.dev.myportal.utils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import java.io.File;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import dita.dev.myportal.exceptions.InvalidShiftException;
import dita.dev.myportal.model.Schedule;
/**
* Created by michael on 15/06/17.
*/
public class ExcelParser {
private final String filename;
private final String shift;
private NPOIFSFileSystem fileSystem;
private HSSFSheet sheet;
private DataFormatter formatter;
private Locale locale = Locale.getDefault();
public ExcelParser(String filename, String shift) {
this.filename = filename;
this.shift = shift;
}
public void start() throws IOException, InvalidShiftException {
//Log.i(getClass().getName(), "OPENING EXCEL...");
fileSystem = new NPOIFSFileSystem(new File(filename));
HSSFWorkbook workbook = new HSSFWorkbook(fileSystem.getRoot(), true);
final int sheets = workbook.getNumberOfSheets();
boolean invalidShift = true;
for (int i = 0; i < sheets; i++) {
sheet = workbook.getSheetAt(i);
if (sheet.getSheetName().toLowerCase().contains(shift.toLowerCase())) {
invalidShift = false;
break;
}
}
if (invalidShift) {
throw new InvalidShiftException(shift + " is an invalid shift");
}
formatter = new DataFormatter();
}
public List<Schedule> getDetails(String... units) throws ParseException {
final List<Schedule> result = new ArrayList<>();
Pattern pattern;
Matcher matcher;
int _row, col;
boolean breakRow;
for (String unit : units) {
breakRow = false;
unit = unit.trim();
String[] sections = split(unit);
pattern = Pattern.compile(String.format("%s(?:-|\\s|.{0})%s", sections[0], sections[1]));
for (Row row : sheet) {
for (Cell cell : row) {
String text = formatter.formatCellValue(cell);
if (text.isEmpty()) {
continue;
}
matcher = pattern.matcher(text);
if (matcher.find()) {
_row = cell.getRowIndex();
col = cell.getColumnIndex();
Schedule event = new Schedule();
event.name = unit + " exame";
event.isExam = true;
event.startTime = getDate(_row, col);
event.room = formatter.formatCellValue(row.getCell(0));
result.add(event);
if (result.size() == units.length) {
return result;
}
breakRow = true;
break;
}
}
if (breakRow) {
break;
}
}
}
return result;
}
private String[] split(String text) {
if (text.contains("-")) {
return text.split("-");
} else if (text.contains(" ")) {
return text.split("\\s");
} else {
return new String[]{text.substring(0, 3), text.substring(3)};
}
}
private Date getDate(int row, int col) throws ParseException {
SimpleDateFormat timeFormatter = new SimpleDateFormat("h:mma", locale);
SimpleDateFormat dateFormatter = new SimpleDateFormat("dd/MM/yy", locale);
Calendar time = Calendar.getInstance();
Calendar result = Calendar.getInstance();
Pattern pattern;
Matcher matcher;
HSSFRow tempRow;
HSSFCell cell;
String text;
for (int i = row; row >= 0; i--) {
tempRow = sheet.getRow(i);
if (tempRow != null) {
cell = tempRow.getCell(col);
if (cell != null) {
text = formatter.formatCellValue(cell);
pattern = Pattern.compile("([\\d]+:[\\d]+[apm]+)", Pattern.CASE_INSENSITIVE);
matcher = pattern.matcher(text);
if (matcher.find()) {
time.setTime(timeFormatter.parse(matcher.group()));
pattern = Pattern.compile("[\\w]+day[\\s]+([\\d]+\\/[\\d]+\\/[\\d]+)", Pattern.CASE_INSENSITIVE);
i--;
for (int j = col; j >= 0; j--) {
cell = sheet.getRow(i).getCell(j);
text = formatter.formatCellValue(cell);
matcher = pattern.matcher(text);
if (matcher.find()) {
result.setTime(dateFormatter.parse(matcher.group(1)));
result.set(Calendar.HOUR_OF_DAY, time.get(Calendar.HOUR_OF_DAY));
result.set(Calendar.MINUTE, time.get(Calendar.MINUTE));
return result.getTime();
}
}
}
}
}
}
return null;
}
public void close() throws IOException {
fileSystem.close();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment