LearnPAd/learnpad

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package eu.learnpad.ontology.kpi.data;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 *
 * @author andreas.martin
 */
public class ExcelParser {

    private static final Logger LOGGER = Logger.getLogger(ExcelParser.class.getName());

    private final File excelFile;
    public final static String SHEETNAME = "SOME";
    public final static String QUERYCELLNAME = "SPARQL";
    public final static String DATACELLNAME = "DATA";

    public ExcelParser(File excelFile) {
        this.excelFile = excelFile;
    }

    public String getSPARQLQuery() throws IOException, InvalidFormatException {
        Boolean foundSparqlQuery = false;

        Workbook wb = WorkbookFactory.create(excelFile);

        for (Sheet sheet : wb) {
            if (sheet.getSheetName().equals(SHEETNAME)) {
                for (Row row : sheet) {
                    for (Cell cell : row) {
                        if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                            continue;
                        }
                        if (!foundSparqlQuery && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                            if (cell.getRichStringCellValue().getString().equals(QUERYCELLNAME)) {
                                foundSparqlQuery = true;
                                continue;
                            }
                        }
                        if (foundSparqlQuery && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                            return cell.getRichStringCellValue().getString();
                        }
                    }
                }
            }
        }
        return null;
    }

    public List<List<String>> getDataTable() throws IOException, InvalidFormatException {
        List<List<String>> dataTable = new ArrayList<>();
        Integer rowNumber = -2;

        Workbook wb = WorkbookFactory.create(excelFile);

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        for (Sheet sheet : wb) {
            if (sheet.getSheetName().equals(SHEETNAME)) {
                for (Row row : sheet) {
                    //stop with the first empty row
                    if(row.getCell(0) == null){
                        break;
                    }
                    if (rowNumber >= -1) {
                        rowNumber++;
                        dataTable.add(new ArrayList<String>());
                    }
                    for (Cell cell : row) {
                        String sheetName = sheet.getSheetName();
                        String cellRow = "Row:" + cell.getRowIndex();
                        String cellColumn = "Column:" + cell.getColumnIndex();
                        Object[] o = new Object[]{sheetName, cellRow, cellColumn};
                        LOGGER.log(Level.INFO, "Processing: Sheet={0} celladress={1}", o);
                        if (rowNumber <= -1 && cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                            continue;
                        }
                        if (rowNumber == -2 && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                            if (cell.getRichStringCellValue().getString().equals(DATACELLNAME)) {
                                rowNumber = -1;
                                continue;
                            }
                        }
                        //Attributes (column headers)
                        if (rowNumber == 0) {
                            dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString());
                        }
                        
                        if (rowNumber >= 1) {

                            switch (cell.getCellType()) {
                                case Cell.CELL_TYPE_STRING:
                                    dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString());
                                    break;
                                case Cell.CELL_TYPE_NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        dataTable.get(rowNumber).add(cell.getDateCellValue().toString());
                                    } else {
                                        dataTable.get(rowNumber).add(Double.toString(cell.getNumericCellValue()));
                                    }
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN:
                                    dataTable.get(rowNumber).add(Boolean.toString(cell.getBooleanCellValue()));
                                    break;
                                case Cell.CELL_TYPE_FORMULA:
                                    switch (cell.getCachedFormulaResultType()) {
                                        case Cell.CELL_TYPE_STRING:
                                            dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString());
                                            break;
                                        case Cell.CELL_TYPE_NUMERIC:
                                            if (DateUtil.isCellDateFormatted(cell)) {
                                                dataTable.get(rowNumber).add(cell.getDateCellValue().toString());
                                            } else {
                                                dataTable.get(rowNumber).add(Double.toString(cell.getNumericCellValue()));
                                            }
                                            break;
                                        case Cell.CELL_TYPE_BOOLEAN:
                                            dataTable.get(rowNumber).add(Boolean.toString(cell.getBooleanCellValue()));
                                            break;
                                        default:
                                            dataTable.get(rowNumber).add("");
                                    }
                                    break;
                                default:
                                    dataTable.get(rowNumber).add("");
                            }
                        }
                    }
                }
            }
        }

        return dataTable;
    }

    public static String getCSVFromDataTable(List<List<String>> dataTable) {

        String csvData = "";

        for (List<String> rowData : dataTable) {
            for (String data : rowData) {
                if (csvData.isEmpty()) {
                    csvData = data + ",";
                } else {
                    csvData = csvData + data + ",";
                }
            }
            if (csvData.endsWith(",")) {
                csvData = csvData.substring(0, csvData.length() - 1) + "\n";
            } else {
                csvData = csvData + "\n";
            }
        }
        return csvData;
    }
}