/**
 *  Copyright (C) 2004 Orbeon, Inc.
 *
 *  This program is free software; you can redistribute it and/or modify it under the terms of the
 *  GNU Lesser General Public License as published by the Free Software Foundation; either version
 *  2.1 of the License, or (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
 *  without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 *  See the GNU Lesser General Public License for more details.
 *
 *  The full text of the license is available at http://www.gnu.org/copyleft/lesser.html
 */
package org.orbeon.oxf.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class XLSUtils {

    private static Pattern FORMAT_XPATH = Pattern.compile("\"([^\"]+)\"$");

    public interface Handler {
        public void cell(HSSFCell cell, String sourceXPath, String targetXPath);
    }

    public static void walk(HSSFDataFormat dataFormat, HSSFSheet sheet, Handler handler) {

        // Go through cells
        boolean[][] merged = getMergedCells(sheet);
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
            HSSFRow row = sheet.getRow(rowNum);
            walk(merged, dataFormat, row, handler);
        }
    }

    public static void walk(HSSFDataFormat dataFormat, HSSFSheet sheet, HSSFRow row, Handler handler) {
        boolean[][] merged = getMergedCells(sheet);
        walk(merged, dataFormat, row, handler);
    }

    private static void walk(boolean[][] merged, HSSFDataFormat dataFormat, HSSFRow row, Handler handler) {
        if (row != null) {
            for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
                HSSFCell cell = row.getCell((short) cellNum);
                if (cell != null && !merged[row.getRowNum()][cellNum]) {
                    short dataFormatId = cell.getCellStyle().getDataFormat();
                    if (dataFormatId > 0) {
                        String format = dataFormat.getFormat(dataFormatId);
                        final Matcher matcher = FORMAT_XPATH.matcher(format);
                        if (matcher.find()) {
                            // Found XPath expression
                            String xpath = matcher.group(1);
                            int separtorPosition = xpath.indexOf('|');
                            String sourceXPath = separtorPosition == -1 ? xpath : xpath.substring(0, separtorPosition);
                            String targetXPath = separtorPosition == -1 ? null : xpath.substring(separtorPosition + 1);
                            handler.cell(cell, sourceXPath, targetXPath);
                        }
                    }
                }
            }
        }
    }

    public static boolean[][] getMergedCells(HSSFSheet sheet) {
        int lastRowNum = sheet.getLastRowNum();
        short maxCellNum = getMaxCellNum(sheet);

        // Compute merged regions
        boolean[][] merged = new boolean[lastRowNum + 1][maxCellNum + 1];
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            Region region = sheet.getMergedRegionAt(i);
            for (int rowNum = region.getRowFrom(); rowNum <= region.getRowTo(); rowNum++) {
                for (int columnNum = region.getColumnFrom(); columnNum <= region.getColumnTo(); columnNum++) {
                    if (rowNum != region.getRowFrom() || columnNum != region.getColumnFrom())
                        merged[rowNum][columnNum] = true;
                }
            }
        }

        return merged;
    }

    /**
     * Compute max number of columns
     */
    public static short getMaxCellNum(HSSFSheet sheet) {
        int lastRowNum = sheet.getLastRowNum();
        short maxCellNum = 0;
        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
            HSSFRow hssfRow = sheet.getRow(rowNum);
            if (hssfRow != null) {
                short lastCellNum = hssfRow.getLastCellNum();
                if (lastCellNum > maxCellNum)
                    maxCellNum = lastCellNum;
            }
        }
        return maxCellNum;
    }

    public static void copySheet(HSSFWorkbook workbook, HSSFSheet destination, HSSFSheet source) {

        // Copy column width
        short maxCellNum = getMaxCellNum(source);
        for (short i = 0; i <= maxCellNum; i++) {
            destination.setColumnWidth(i, source.getColumnWidth(i));
        }

        // Copy merged cells
        for (int i = 0; i < source.getNumMergedRegions(); i++) {
            Region region = source.getMergedRegionAt(i);
            destination.addMergedRegion(region);
        }

        // Copy rows
        for (int i = 0; i <= source.getLastRowNum(); i++) {
            HSSFRow sourceRow = source.getRow(i);
            HSSFRow destinationRow = destination.createRow(i);
            copyRow(workbook, destinationRow, sourceRow);
        }
    }

    public static void copyRow(HSSFWorkbook workbook, HSSFRow destination, HSSFRow source) {
        for (short i = 0; i <= source.getLastCellNum(); i++) {
            HSSFCell templateCell = source.getCell(i);
            if (templateCell != null) {
                HSSFCell newCell = destination.createCell(i);
                XLSUtils.copyCell(workbook, newCell, templateCell);
            }
        }
    }

    public static void copyCell(HSSFWorkbook workbook, HSSFCell destination, HSSFCell source) {

        // Copy cell content
        destination.setCellType(source.getCellType());
        switch (source.getCellType()) {
            case HSSFCell.CELL_TYPE_BOOLEAN:
                destination.setCellValue(source.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
            case HSSFCell.CELL_TYPE_STRING:
                destination.setCellValue(source.getStringCellValue());
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                destination.setCellValue(source.getNumericCellValue());
                break;
        }

        // Copy cell style
        HSSFCellStyle sourceCellStyle = source.getCellStyle();
        HSSFCellStyle destinationCellStyle = workbook.createCellStyle();
        destinationCellStyle.setAlignment(sourceCellStyle.getAlignment());
        destinationCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom());
        destinationCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft());
        destinationCellStyle.setBorderRight(sourceCellStyle.getBorderRight());
        destinationCellStyle.setBorderTop(sourceCellStyle.getBorderTop());
        destinationCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor());
        destinationCellStyle.setDataFormat(sourceCellStyle.getDataFormat());
        destinationCellStyle.setFillBackgroundColor(sourceCellStyle.getFillForegroundColor());
        destinationCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor());
        destinationCellStyle.setFillPattern(sourceCellStyle.getFillPattern());
        destinationCellStyle.setFont(workbook.getFontAt(sourceCellStyle.getFontIndex()));
        destinationCellStyle.setHidden(sourceCellStyle.getHidden());
        destinationCellStyle.setIndention(sourceCellStyle.getIndention());
        destinationCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor());
        destinationCellStyle.setLocked(sourceCellStyle.getLocked());
        destinationCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor());
        destinationCellStyle.setRotation(sourceCellStyle.getRotation());
        destinationCellStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor());
        destinationCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment());
        destinationCellStyle.setWrapText(sourceCellStyle.getWrapText());
        destination.setCellStyle(destinationCellStyle);
    }

    public static void copyFont(HSSFFont destination, HSSFFont source) {
        destination.setBoldweight(source.getBoldweight());
        destination.setColor(source.getColor());
        destination.setFontHeight(source.getFontHeight());
        destination.setFontHeightInPoints(source.getFontHeightInPoints());
        destination.setFontName(source.getFontName());
        destination.setItalic(source.getItalic());
        destination.setStrikeout(source.getStrikeout());
        destination.setTypeOffset(source.getTypeOffset());
        destination.setUnderline(source.getUnderline());
    }
}
