org.dashbuilder.dataset.service.DataSetExportServicesImpl Maven / Gradle / Ivy

Go to download
/**
 * Copyright 2014 Red Hat, Inc. and/or its affiliates.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.dashbuilder.dataset.service;

import java.io.BufferedWriter;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import javax.enterprise.context.ApplicationScoped;
import javax.inject.Inject;

import au.com.bytecode.opencsv.CSVWriter;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
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.util.DateFormatConverter;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.dashbuilder.DataSetCore;
import org.dashbuilder.dataset.DataColumn;
import org.dashbuilder.dataset.DataSet;
import org.dashbuilder.dataset.DataSetLookup;
import org.dashbuilder.dataset.DataSetDefRegistryCDI;
import org.dashbuilder.dataset.DataSetManagerCDI;
import org.dashbuilder.dataset.group.Interval;
import org.dashbuilder.dataset.uuid.UUIDGenerator;
import org.dashbuilder.exception.ExceptionManager;
import org.jboss.errai.bus.server.annotations.Service;
import org.slf4j.Logger;
import org.uberfire.backend.server.util.Paths;
import org.uberfire.java.nio.file.Files;
import org.uberfire.java.nio.file.Path;

@ApplicationScoped
@Service
public class DataSetExportServicesImpl implements DataSetExportServices {

    protected Logger log;
    protected DataSetManagerCDI dataSetManager;
    protected DataSetDefRegistryCDI gitStorage;
    protected UUIDGenerator uuidGenerator;
    protected ExceptionManager exceptionManager;

    protected String DEFAULT_SEPARATOR_CHAR = ";";
    protected String DEFAULT_QUOTE_CHAR = "\"";
    protected String DEFAULT_ESCAPE_CHAR = "\\";

    protected String dateFormatPattern = "dd/MM/yyyy HH:mm:ss";
    protected String numberFormatPattern = "#,###.##########";

    protected DecimalFormat decf = new DecimalFormat(numberFormatPattern);
    protected DateFormat datef = new SimpleDateFormat(dateFormatPattern);

    public DataSetExportServicesImpl() {
    }

    @Inject
    public DataSetExportServicesImpl(Logger log,
                                     DataSetManagerCDI dataSetManager,
                                     DataSetDefRegistryCDI gitStorage,
                                     ExceptionManager exceptionManager) {
        this.log = log;
        this.dataSetManager = dataSetManager;
        this.gitStorage = gitStorage;
        this.uuidGenerator = DataSetCore.get().getUuidGenerator();
        this.exceptionManager = exceptionManager;
    }

    public org.uberfire.backend.vfs.Path exportDataSetCSV(DataSetLookup lookup) {
        DataSet dataSet = dataSetManager.lookupDataSet(lookup);
        return exportDataSetCSV(dataSet);
    }

    public org.uberfire.backend.vfs.Path exportDataSetCSV(DataSet dataSet) {
        try {
            if (dataSet == null) throw new IllegalArgumentException("Null dataSet specified!");
            int columnCount = dataSet.getColumns().size();
            int rowCount = dataSet.getRowCount();

            List lines = new ArrayList(rowCount+1);

            String[] line = new String[columnCount];
            for (int cc = 0; cc < columnCount; cc++) {
                DataColumn dc = dataSet.getColumnByIndex(cc);
                line[cc] = dc.getId();
            }
            lines.add(line);

            for (int rc = 0; rc < rowCount; rc++) {
                line = new String[columnCount];
                for (int cc = 0; cc < columnCount; cc++) {
                    line[cc] = formatAsString(dataSet.getValueAt(rc, cc));
                }
                lines.add(line);
            }

            String tempCsvFile = uuidGenerator.newUuid() + ".csv";
            Path tempCsvPath = gitStorage.createTempFile(tempCsvFile);

            BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(Files.newOutputStream(tempCsvPath)));
            CSVWriter writer = new CSVWriter(bw,
                    DEFAULT_SEPARATOR_CHAR.charAt(0),
                    DEFAULT_QUOTE_CHAR.charAt(0),
                    DEFAULT_ESCAPE_CHAR.charAt(0));
            writer.writeAll(lines);
            writer.close();
            writer.flush();
            writer.close();
            return Paths.convert(tempCsvPath);
        }
        catch (Exception e) {
            throw exceptionManager.handleException(e);
        }
    }

    @Override
    public org.uberfire.backend.vfs.Path exportDataSetExcel(DataSetLookup dataSetLookup) {
        DataSet dataSet = dataSetManager.lookupDataSet( dataSetLookup );
        return exportDataSetExcel( dataSet );
    }

    @Override
    public org.uberfire.backend.vfs.Path exportDataSetExcel(DataSet dataSet) {
        try {
            // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters
            if (dataSet == null) throw new IllegalArgumentException("Null dataSet specified!");
            int columnCount = dataSet.getColumns().size();
            int rowCount = dataSet.getRowCount() + 1; //Include header row;
            int row = 0;

            SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
            Map styles = createStyles(wb);
            Sheet sh = wb.createSheet("Sheet 1");

            // General setup
            sh.setDisplayGridlines(true);
            sh.setPrintGridlines(false);
            sh.setFitToPage(true);
            sh.setHorizontallyCenter(true);
            PrintSetup printSetup = sh.getPrintSetup();
            printSetup.setLandscape(true);

            // Create header
            Row header = sh.createRow(row++);
            header.setHeightInPoints(20f);
            for (int i = 0; i < columnCount; i++) {
                Cell cell = header.createCell(i);
                cell.setCellStyle(styles.get("header"));
                cell.setCellValue(dataSet.getColumnByIndex(i).getId());
            }

            // Create data rows
            for (; row < rowCount; row++) {
                Row _row = sh.createRow(row);
                for (int cellnum = 0; cellnum < columnCount; cellnum++) {
                    Cell cell = _row.createCell(cellnum);
                    Object value = dataSet.getValueAt(row - 1, cellnum);
                    if (value instanceof Short || value instanceof Long || value instanceof Integer || value instanceof BigInteger ) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellStyle(styles.get("integer_number_cell"));
                        cell.setCellValue(((Number) value).doubleValue());
                    } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal ) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellStyle(styles.get("decimal_number_cell"));
                        cell.setCellValue(((Number) value).doubleValue());
                    } else if (value instanceof Date) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellStyle(styles.get("date_cell"));
                        cell.setCellValue((Date) value);
                    } else if (value instanceof Interval) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellStyle(styles.get("text_cell"));
                        cell.setCellValue(((Interval) value).getName());
                    } else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellStyle(styles.get("text_cell"));
                        cell.setCellValue(value.toString());
                    }
                }
            }

            // Adjust column size
            for (int i = 0; i < columnCount; i++) {
                sh.autoSizeColumn(i);
            }

            String tempXlsFile = uuidGenerator.newUuid() + ".xlsx";
            Path tempXlsPath = gitStorage.createTempFile(tempXlsFile);
            OutputStream os = Files.newOutputStream(tempXlsPath);
            wb.write(os);
            os.flush();
            os.close();

            // Dispose of temporary files backing this workbook on disk
            if (!wb.dispose()) {
                log.warn("Could not dispose of temporary file associated to data export!");
            }
            return Paths.convert(tempXlsPath);
        } catch (Exception e) {
            throw exceptionManager.handleException(e);
        }
    }

    private String formatAsString(Object value) {
        if (value == null) return "";
        if (value instanceof Number) return decf.format(value);
        else if (value instanceof Date) return datef.format(value);
        // TODO verify if this is correct
        else if (value instanceof Interval) return ((Interval)value).getName();
        else return value.toString();
    }

    private Map createStyles(Workbook wb){
        Map styles = new HashMap();
        CellStyle style;

        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short)12);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor( IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(titleFont);
        style.setWrapText(false);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
        styles.put("header", style);

        Font cellFont = wb.createFont();
        cellFont.setFontHeightInPoints((short)10);
        cellFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        style.setFont(cellFont);
        style.setWrapText(false);
        style.setDataFormat(wb.createDataFormat().getFormat( BuiltinFormats.getBuiltinFormat( 3 )));
        styles.put("integer_number_cell", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        style.setFont(cellFont);
        style.setWrapText(false);
        style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(4)));
        styles.put("decimal_number_cell", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        style.setFont(cellFont);
        style.setWrapText(false);
        style.setDataFormat( (short) BuiltinFormats.getBuiltinFormat("text") );
        styles.put("text_cell", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        style.setFont(cellFont);
        style.setWrapText(false);
        style.setDataFormat(wb.createDataFormat().getFormat( DateFormatConverter.convert( Locale.getDefault(), dateFormatPattern )));
        styles.put("date_cell", style);
        return styles;
    }
}