export.notes.view.to.excel.ExcelWriter.java Source code

Java tutorial

Introduction

Here is the source code for export.notes.view.to.excel.ExcelWriter.java

Source

package export.notes.view.to.excel;
/*
 * Copyright 2015
 * 
 * This file is part of Lotus Notes plugin for export Lotus Notes View into Microsoft Excel.
 * 
 * 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.
 */

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Vector;

import lotus.domino.DateTime;
import lotus.domino.NotesException;
import lotus.domino.RichTextStyle;
import lotus.domino.View;
import lotus.domino.ViewColumn;
import lotus.domino.ViewEntry;
import lotus.domino.ViewNavigator;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
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.Row;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelWriter {

    XSSFWorkbook workbook;
    XSSFSheet sheet;

    Map<Integer, ViewColumn> headers = new HashMap<Integer, ViewColumn>();
    ArrayList<CellStyle> styles = new ArrayList<CellStyle>();

    public ExcelWriter() {
        workbook = new XSSFWorkbook();
    }

    public XSSFWorkbook getWorkbook() {
        return workbook;
    }

    public XSSFSheet createSheet(String name) {
        if (workbook == null) {
            workbook = new XSSFWorkbook();
        }
        if (sheet == null) {
            sheet = workbook.createSheet(name.replaceAll("\\\\", "-").trim());//$NON-NLS-1$ //$NON-NLS-2$
        }
        return sheet;
    }

    public void cerateRow(int rowNum, List<Object> entryValues) throws NotesException {
        ArrayList<Object> values = new ArrayList<Object>();

        for (Entry<Integer, ViewColumn> entry : headers.entrySet()) {
            Object object = entryValues.get(entry.getKey());
            if (object.getClass().getName().contains("Vector")) { //$NON-NLS-1$            
                values.add(getVectorString(object));
            } else {
                values.add(object);
            }
        }

        // write the row into excel table
        Row row = sheet.createRow(rowNum);
        for (int i = 0; i < values.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            Object object = values.get(i);
            String name = object.getClass().getSimpleName();
            if (name.contains("String")) { //$NON-NLS-1$                                 
                cell.setCellValue((String) object);
            } else if (name.contains("Double")) { //$NON-NLS-1$
                cell.setCellValue((Double) object);
            } else if (name.contains("DateTime")) { //$NON-NLS-1$            
                cell.setCellValue(((DateTime) values.get(i)).toJavaDate());
            } else {
                cell.setCellValue(object.toString());
            }
            cell.setCellStyle(styles.get(i));
        }

    }

    public void setAutoSizeColumns() {
        for (int x = 0; x < headers.size(); x++) {
            sheet.autoSizeColumn(x);
            if (sheet.getColumnWidth(x) > 25000) {
                sheet.setColumnWidth(x, 25000);
            }
        }
    }

    private String getVectorString(Object object) {
        String s = object.toString();
        // remove brackets from Vector values
        if (s.startsWith("[")) { //$NON-NLS-1$
            s = s.substring(1);
        }
        if (s.endsWith("]")) { //$NON-NLS-1$
            s = s.substring(0, s.length() - 1);
        }
        return s;
    }

    // create header of table
    @SuppressWarnings("unchecked")
    public void createTableHeader(View view) throws NotesException {
        Vector<ViewColumn> columns = view.getColumns();
        // offset column
        int offset = 0;
        for (int x = 0; x < columns.size(); x++) {
            ViewColumn column = columns.get(x);
            if (column.isConstant()) {
                offset++;
            } else if (column.isFormula()) {
                // A column value (ViewEntry.getColumnValues()) is not
                // returned if it is determined by a constant. Check it.
                String formula = column.getFormula();
                if (formula == null) {
                    offset++;
                } else {
                    // empty string
                    formula = formula.replaceAll("\"", "").trim();
                    // some whitespaces
                    if (StringUtils.isBlank(formula)) {
                        offset++;
                    }
                }
            }
            // hidden and icons columns not will be use
            if (!column.isHidden() && !column.isIcon() && !column.isConstant()) {
                String s = columns.get(x).getTitle();
                if (s == null || "".equals(s)) { //$NON-NLS-1$
                    s = " "; //$NON-NLS-1$
                }
                int position = x - offset;
                headers.put(position, column);
                ViewNavigator nav = view.createViewNav();
                ViewEntry entry = nav.getFirst();
                while (!entry.isDocument()) {
                    entry = nav.getNext();
                }
                createCellStyle(position, column, entry);
            }
        }

        // column indexes
        int idy = 0;
        // Generate column headings
        Cell c = null;
        if (sheet == null) {
            SimpleDateFormat sf = new SimpleDateFormat("dd.MM.yyyy HH:mm"); //$NON-NLS-1$
            sheet = createSheet(Messages.ExportAction_10 + " " + sf.format(new Date()));
        }
        Row row = sheet.createRow(0);

        Font fontBold = workbook.createFont();
        fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(fontBold);

        for (Entry<Integer, ViewColumn> entry : headers.entrySet()) {
            ViewColumn column = entry.getValue();
            c = row.createCell(idy++);
            c.setCellValue(column.getTitle());
            c.setCellStyle(cellStyle);
        }

        sheet.createFreezePane(0, 1, 0, 1);
    }

    private void createCellStyle(int position, ViewColumn column, ViewEntry entry) throws NotesException {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        if (column.isFontBold()) {
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        }
        font.setItalic(column.isFontItalic());
        switch (column.getFontColor()) {
        case RichTextStyle.COLOR_BLACK:
            font.setColor(HSSFColor.BLACK.index);
            break;
        case RichTextStyle.COLOR_BLUE:
            font.setColor(HSSFColor.BLUE.index);
            break;
        case RichTextStyle.COLOR_CYAN:
            font.setColor(HSSFColor.CORAL.index);
            break;
        case RichTextStyle.COLOR_DARK_BLUE:
            font.setColor(HSSFColor.DARK_BLUE.index);
            break;
        case RichTextStyle.COLOR_DARK_CYAN:
            font.setColor(HSSFColor.DARK_GREEN.index);
            break;
        case RichTextStyle.COLOR_DARK_GREEN:
            font.setColor(HSSFColor.DARK_GREEN.index);
            break;
        case RichTextStyle.COLOR_DARK_MAGENTA:
            font.setColor(HSSFColor.VIOLET.index);
            break;
        case RichTextStyle.COLOR_DARK_RED:
            font.setColor(HSSFColor.DARK_RED.index);
            break;
        case RichTextStyle.COLOR_DARK_YELLOW:
            font.setColor(HSSFColor.DARK_YELLOW.index);
            break;
        case RichTextStyle.COLOR_GRAY:
            font.setColor(HSSFColor.GREY_80_PERCENT.index);
            break;
        case RichTextStyle.COLOR_GREEN:
            font.setColor(HSSFColor.GREEN.index);
            break;
        case RichTextStyle.COLOR_LIGHT_GRAY:
            font.setColor(HSSFColor.GREY_50_PERCENT.index);
            break;
        case RichTextStyle.COLOR_MAGENTA:
            font.setColor(HSSFColor.VIOLET.index);
            break;
        case RichTextStyle.COLOR_RED:
            font.setColor(HSSFColor.RED.index);
            break;
        case RichTextStyle.COLOR_WHITE:
            font.setColor(HSSFColor.BLACK.index);
            break;
        case RichTextStyle.COLOR_YELLOW:
            font.setColor(HSSFColor.YELLOW.index);
            break;
        default:
            break;
        }

        cellStyle.setFont(font);

        switch (column.getAlignment()) {
        case ViewColumn.ALIGN_CENTER:
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
            break;
        case ViewColumn.ALIGN_LEFT:
            cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
            break;
        case ViewColumn.ALIGN_RIGHT:
            cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
            break;
        default:
            break;
        }

        @SuppressWarnings("unchecked")
        Vector<Object> values = entry.getColumnValues();
        Object value = values.get(position);
        String name = value.getClass().getSimpleName();
        short format = 0;
        if (name.contains("Double")) { //$NON-NLS-1$
            XSSFDataFormat fmt = (XSSFDataFormat) workbook.createDataFormat();
            switch (column.getNumberFormat()) {
            case ViewColumn.FMT_CURRENCY:
                format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(6));
                break;
            case ViewColumn.FMT_FIXED:
                String zero = "0"; //$NON-NLS-1$
                String fixedFormat = "#0"; //$NON-NLS-1$
                int digits = column.getNumberDigits();
                if (digits > 0) {
                    String n = StringUtils.repeat(zero, digits);
                    fixedFormat = fixedFormat + "." + n;
                }
                format = fmt.getFormat(fixedFormat);
                break;
            default:
                format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(1));
                break;
            }
        } else if (name.contains("DateTime")) { //$NON-NLS-1$                     
            XSSFDataFormat fmt = (XSSFDataFormat) workbook.createDataFormat();
            switch (column.getTimeDateFmt()) {
            case ViewColumn.FMT_DATE:
                format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0xe));
                break;
            case ViewColumn.FMT_DATETIME:
                format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0x16));
                break;
            case ViewColumn.FMT_TIME:
                format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0x15));
                break;
            default:
                format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0xe));
                break;
            }
        }
        cellStyle.setDataFormat(format);
        styles.add(cellStyle);
    }
}