package com.constellio.app.services.schemas.bulkImport.data.excel;
import java.io.Closeable;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import org.joda.time.LocalDate;
import com.constellio.data.utils.LazyIterator;
public class ExcelDataIterator extends LazyIterator<Map<String, Object>> implements Closeable {
private ExcelSheet sheet;
private int lineToParse = 0;
private List<String> columns;
public ExcelDataIterator(ExcelSheet sheet) {
this.sheet = sheet;
this.columns = new ArrayList<>();
initialize();
}
private void initialize() {
for (ExcelCell cell : sheet.getRow(0)) {
if (!nullOrInvalidData(cell.getContents())) {
columns.add(cell.getContents());
} else {
columns.add(null);
}
}
}
@Override
public void close() {
}
@Override
protected Map<String, Object> getNextOrNull() {
lineToParse++;
if (lineToParse == sheet.getRows()) {
return null;
}
try {
while (lineIsEmpty()) {
lineToParse++;
}
return parseLine();
} catch (ArrayIndexOutOfBoundsException e) {
//OK
return null;
}
}
private Map<String, Object> parseLine() {
Map<String, Object> line = new HashMap<>();
List<ExcelCell> cells = sheet.getRow(lineToParse);
for (int i = 0; i < columns.size(); i++) {
String column = columns.get(i);
if (column != null) {
ExcelCell cell = cells.get(i);
if (!cellIsEmpty(cell)) {
if (cell.isDate()) {
line.put(column, new LocalDate(cell.getDate()));
} else {
String content = cell.getContents();
line.put(column, content == null ? null : content.trim());
}
}
}
}
return line;
}
private boolean lineIsEmpty() {
for (ExcelCell cell : sheet.getRow(lineToParse)) {
if (cell.isNotEmpty() && !nullOrInvalidData(cell.getContents())) {
return false;
}
}
return true;
}
private boolean cellIsEmpty(ExcelCell cell) {
if (cell.isNotEmpty() && !nullOrInvalidData(cell.getContents())) {
return false;
}
return true;
}
private boolean nullOrInvalidData(String content) {
return content == null || content.equals("") || content.equals(" ") || content.equals("\n") || content.equals("null");
}
public static ExcelDataIterator overSheet(File file, String sheet) {
Workbook workbook = loadWorkbook(file);
ExcelSheet excelSheet = new Excel2003Sheet(workbook.getSheet(sheet));
return new ExcelDataIterator(excelSheet);
}
public static Workbook loadWorkbook(File workbookFile) {
WorkbookSettings settings = new WorkbookSettings();
settings.setEncoding("LATIN1");
try {
return Workbook.getWorkbook(workbookFile, settings);
} catch (BiffException | IOException e) {
throw new RuntimeException(e);
}
}
}