de.ingrid.iplug.excel.service.SheetsService.java Source code

Java tutorial

Introduction

Here is the source code for de.ingrid.iplug.excel.service.SheetsService.java

Source

/*
 * **************************************************-
 * ingrid-iplug-excel
 * ==================================================
 * Copyright (C) 2014 - 2015 wemove digital solutions GmbH
 * ==================================================
 * Licensed under the EUPL, Version 1.1 or  as soon they will be
 * approved by the European Commission - subsequent versions of the
 * EUPL (the "Licence");
 * 
 * You may not use this work except in compliance with the Licence.
 * You may obtain a copy of the Licence at:
 * 
 * http://ec.europa.eu/idabc/eupl5
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the Licence is distributed on an "AS IS" basis,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the Licence for the specific language governing permissions and
 * limitations under the Licence.
 * **************************************************#
 */
package de.ingrid.iplug.excel.service;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.Workbook;
import org.springframework.web.multipart.MultipartFile;

import de.ingrid.admin.command.PlugdescriptionCommandObject;
import de.ingrid.iplug.excel.model.Column;
import de.ingrid.iplug.excel.model.Point;
import de.ingrid.iplug.excel.model.Row;
import de.ingrid.iplug.excel.model.Sheet;
import de.ingrid.iplug.excel.model.Sheets;
import de.ingrid.iplug.excel.model.Values;

/**
 * Service class for sheets. Creating and loading of sheets. 
 *
 */
public class SheetsService {

    /**
     * Load sheets.
     * 
     * @param plugDescription
     * @param sheetIndex
     * @return Loaded sheets.
     * @throws IOException
     */
    public static Sheets loadSheets(final PlugdescriptionCommandObject plugDescription, final int sheetIndex)
            throws IOException {
        // get sheet
        final Sheets sheets = (Sheets) plugDescription.get("sheets");
        final Sheet sheet = sheets.getSheets().get(sheetIndex);

        // load sheet
        final File workinDirectory = plugDescription.getWorkinDirectory();
        final File mappingDir = new File(workinDirectory, "mapping");
        final File file = new File(mappingDir, sheet.getFileName());
        final Sheets loadedSheets = SheetsService.createSheets(file);

        // add required params
        for (final Sheet loadedSheet : loadedSheets) {
            loadedSheet.setFileName(file.getName());
        }

        return loadedSheets;
    }

    /**
     * Load sheet.
     * 
     * @param plugDescription
     * @param sheetIndex
     * @return Load sheet.
     * @throws IOException
     */
    public static Sheet loadSheet(final PlugdescriptionCommandObject plugDescription, final int sheetIndex)
            throws IOException {
        // get sheet
        final Sheets sheets = (Sheets) plugDescription.get("sheets");
        final Sheet sheet = sheets.getSheets().get(sheetIndex);

        // load sheet
        final File workinDirectory = plugDescription.getWorkinDirectory();
        final File mappingDir = new File(workinDirectory, "mapping");
        final File file = new File(mappingDir, sheet.getFileName());
        final Sheets loadedSheets = SheetsService.createSheets(file);
        final Sheet loadedSheet = loadedSheets.getSheet(sheet.getSheetIndex());
        sheet.setValues(loadedSheet.getValues());
        return sheet;
    }

    /**
     * Create sheets.
     * 
     * @param file
     * @return Created sheets.
     * @throws IOException
     */
    public static Sheets createSheets(final File file) throws IOException {
        return createSheets(new FileInputStream(file));
    }

    /**
     * Create sheets.
     * 
     * @param multipartFile
     * @return Created sheets.
     * @throws IOException
     */
    public static Sheets createSheets(final MultipartFile multipartFile) throws IOException {
        final byte[] bytes = multipartFile.getBytes();
        final Sheets sheets = createSheets(new ByteArrayInputStream(bytes));
        for (final Sheet sheet : sheets) {
            sheet.setFileName(multipartFile.getOriginalFilename());
            sheet.setWorkbook(bytes);
        }
        return sheets;
    }

    /**
     * Create sheets.
     * 
     * @param inputStream
     * @return Created sheets.
     * @throws IOException
     */
    public static Sheets createSheets(final InputStream inputStream) throws IOException {
        // sheets
        final Sheets sheets = new Sheets();
        // create workbook
        final Workbook workbook = new HSSFWorkbook(inputStream);
        final FormulaEvaluator eval = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            final org.apache.poi.ss.usermodel.Sheet poiSheet = workbook.getSheetAt(sheetNum);
            // ingrid sheet
            final Sheet sheet = new Sheet();
            sheet.setSheetIndex(sheetNum);
            sheets.addSheet(sheet);
            final Values values = new Values();
            sheet.setValues(values);
            for (final org.apache.poi.ss.usermodel.Row poiRow : poiSheet) {
                boolean hasValues = false;
                final Map<Point, Comparable<? extends Object>> valuesInCell = new HashMap<Point, Comparable<? extends Object>>();
                for (final Cell poiCell : poiRow) {

                    Comparable<? extends Object> value = null;
                    switch (poiCell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        value = new Boolean(poiCell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(poiCell)) {
                            value = getFormattedDateString(poiCell);
                        } else {
                            value = new Double(poiCell.getNumericCellValue());
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        value = poiCell.getStringCellValue();
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        value = calculateFormula(poiCell, eval);
                        break;
                    default:
                        value = "";
                        break;
                    }
                    // trim strings
                    if (value instanceof String) {
                        value = ((String) value).trim();
                    }
                    // only add if at least one value does exist in row
                    if (!value.equals("")) {
                        hasValues = true;
                        // ingrid column
                        if (sheet.getColumn(poiCell.getColumnIndex()) == null) {
                            final Column column = new Column(poiCell.getColumnIndex());
                            sheet.addColumn(column);
                        }
                    }

                    // ingrid point and value
                    final Point point = new Point(poiCell.getColumnIndex(), poiCell.getRowIndex());
                    valuesInCell.put(point, value);
                }
                // ingrid row
                // ! only add if at least one value does exist
                if (hasValues) {
                    final Row row = new Row(poiRow.getRowNum());
                    sheet.addRow(row);
                    for (final Point point : valuesInCell.keySet()) {
                        //
                        if (sheet.getColumn(point.getX()) != null) {
                            values.addValue(point, valuesInCell.get(point));
                        }
                    }
                }
            }
        }

        return sheets;
    }

    private static Comparable<? extends Object> calculateFormula(final Cell poiCell, final FormulaEvaluator eval) {
        Comparable<? extends Object> ret = null;
        final int type = eval.evaluateFormulaCell(poiCell);
        switch (type) {
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(poiCell)) {
                ret = getFormattedDateString(poiCell);
            } else {
                ret = poiCell.getNumericCellValue();
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            ret = poiCell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_BLANK:
        case Cell.CELL_TYPE_ERROR:
        case Cell.CELL_TYPE_FORMULA:
        case Cell.CELL_TYPE_STRING:
        default:
            ret = poiCell.getStringCellValue();
        }
        return ret;
    }

    /**
     * Get formatted data.
     * 
     * @param cell
     * @return Formated date.
     */
    private static String getFormattedDateString(final Cell cell) {
        final Date date = cell.getDateCellValue();
        final SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        final String formatedDate = formatter.format(date);
        return formatedDate;
    }

}