org.datanucleus.store.excel.ExcelUtils Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of datanucleus-excel Show documentation
DataNucleus supports persistence to heterogeneous datastores and this plugin provides persistence to Excel.
The newest version!
/**********************************************************************
Copyright (c) 2008 Andy Jefferson and others. All rights reserved.
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.

Contributors:
    ...
**********************************************************************/
package org.datanucleus.store.excel;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.datanucleus.ClassLoaderResolver;
import org.datanucleus.ExecutionContext;
import org.datanucleus.exceptions.NucleusDataStoreException;
import org.datanucleus.identity.IdentityUtils;
import org.datanucleus.metadata.AbstractClassMetaData;
import org.datanucleus.metadata.AbstractMemberMetaData;
import org.datanucleus.metadata.IdentityType;
import org.datanucleus.metadata.RelationType;
import org.datanucleus.state.ObjectProvider;
import org.datanucleus.store.schema.table.MemberColumnMapping;
import org.datanucleus.store.schema.table.Table;
import org.datanucleus.store.types.converters.TypeConverterHelper;
import org.datanucleus.util.Localiser;
import org.datanucleus.util.NucleusLogger;
import org.datanucleus.util.StringUtils;

/**
 * Class providing convenience methods for handling Excel datastores.
 * Please refer to Apache POI http://poi.apache.org
 */
public class ExcelUtils
{
    private ExcelUtils() {}

    /**
     * Convenience method to return the worksheet used for storing the specified object.
     * @param op ObjectProvider for the object
     * @param wb Workbook
     * @param table The table representing this worksheet
     * @return The Work Sheet
     * @throws NucleusDataStoreException if the work sheet doesn't exist in this workbook
     */
    public static Sheet getSheetForClass(ObjectProvider op, Workbook wb, Table table)
    {
        String sheetName = table.getName();
        final Sheet sheet = wb.getSheet(sheetName);
        if (sheet == null)
        {
            throw new NucleusDataStoreException(Localiser.msg("Excel.SheetNotFoundForWorkbook", sheetName, op.getObjectAsPrintable()));
        }
        return sheet;
    }

    /**
     * Convenience method to find the row number of an object in the provided workbook.
     * For application-identity does a search for a row with the specified PK field values.
     * For datastore-identity does a search for the row with the datastore column having the specified value
     * @param op ObjectProvider for the object
     * @param wb Workbook
     * @param originalValue Use the original value of the identifiying fields if available (for when we are updating and using nondurable identity).
     * @param table The table representing this worksheet
     * @return The row number (or -1 if not found)
     */
    public static int getRowNumberForObjectInWorkbook(ObjectProvider op, Workbook wb, boolean originalValue, Table table)
    {
        final AbstractClassMetaData cmd = op.getClassMetaData();
        if (cmd.getIdentityType() == IdentityType.APPLICATION)
        {
            ExecutionContext ec = op.getExecutionContext();
            ClassLoaderResolver clr = ec.getClassLoaderResolver();
            int[] pkFieldNumbers = cmd.getPKMemberPositions();
NucleusLogger.GENERAL.info(">> getRowNum type=" + cmd.getFullClassName() + " pkFieldNums=" + StringUtils.intArrayToString(pkFieldNumbers));
            List pkFieldColList = new ArrayList(pkFieldNumbers.length);
            List pkFieldValList = new ArrayList(pkFieldNumbers.length);
            List pkFieldTypeList = new ArrayList(pkFieldNumbers.length);
            for (int i=0;i> Field=" + mmd.getFullFieldName() + " value=" + fieldValue + " " + StringUtils.toJVMIDString(fieldValue));
                RelationType relationType = mmd.getRelationType(clr);
                if (RelationType.isRelationSingleValued(relationType) && mmd.isEmbedded())
                {
                    // Embedded PC is part of PK (e.g JPA EmbeddedId)
                    ObjectProvider embOP = ec.findObjectProvider(fieldValue);
                    if (embOP == null)
                    {
                        embOP = ec.getNucleusContext().getObjectProviderFactory().newForEmbedded(ec, fieldValue, false, op, pkFieldNumbers[i]);
                    }
                    AbstractClassMetaData embCmd = op.getExecutionContext().getMetaDataManager().getMetaDataForClass(mmd.getType(), clr);
                    for (int j=0;j embMmds = new ArrayList();
                        embMmds.add(mmd);
                        embMmds.add(embMmd);
                        MemberColumnMapping mapping = table.getMemberColumnMappingForEmbeddedMember(embMmds);
                        pkFieldColList.add(mapping.getColumn(0).getPosition());
                        if (mapping.getTypeConverter() != null)
                        {
                            pkFieldValList.add(mapping.getTypeConverter().toDatastoreType(embOP.provideField(j)));
                            pkFieldTypeList.add(TypeConverterHelper.getDatastoreTypeForTypeConverter(mapping.getTypeConverter(), embMmd.getType()));
                        }
                        else
                        {
                            pkFieldValList.add(embOP.provideField(j));
                            pkFieldTypeList.add(embMmd.getType());
                        }
                    }
                }
                else
                {
                    MemberColumnMapping mapping = table.getMemberColumnMappingForMember(mmd);
                    pkFieldColList.add(mapping.getColumn(0).getPosition());
                    if (mapping.getTypeConverter() != null)
                    {
                        pkFieldValList.add(mapping.getTypeConverter().toDatastoreType(fieldValue));
                        pkFieldTypeList.add(TypeConverterHelper.getDatastoreTypeForTypeConverter(mapping.getTypeConverter(), mmd.getType()));
                    }
                    else
                    {
                        pkFieldValList.add(fieldValue);
                        pkFieldTypeList.add(mmd.getType());
                    }
                }
            }

            String sheetName = table.getName();
            final Sheet sheet = wb.getSheet(sheetName);
            if (sheet != null && sheet.getPhysicalNumberOfRows() > 0)
            {
                for (int i=sheet.getFirstRowNum(); i fieldColList = new ArrayList(fieldNumbers.length);
            List fieldTypeList = new ArrayList(fieldNumbers.length);
            List fieldValList = new ArrayList(fieldNumbers.length);
            for (int i=0;i embMmds = new ArrayList();
                        embMmds.add(mmd);
                        embMmds.add(embMmd);
                        fieldColList.add(table.getMemberColumnMappingForEmbeddedMember(embMmds).getColumn(0).getPosition());
                        fieldTypeList.add(embMmd.getType());
                        fieldValList.add(embOP.provideField(j));
                    }
                }
                else if (relationType == RelationType.NONE)
                {
                    fieldColList.add(table.getMemberColumnMappingForMember(mmd).getColumn(0).getPosition());
                    fieldTypeList.add(mmd.getType());
                    fieldValList.add(fieldValue);
                }
            }

            String sheetName = table.getName();
            final Sheet sheet = wb.getSheet(sheetName);
            if (sheet != null && sheet.getPhysicalNumberOfRows() > 0)
            {
                for (int i=sheet.getFirstRowNum(); i 0)
            {
                for (int i=sheet.getFirstRowNum(); i 0)
            {
                int datastoreIdColNumber = table.getDatastoreIdColumn().getPosition();
                for (int i=sheet.getFirstRowNum(); i 0)
            {
                for (int i=sheet.getFirstRowNum(); i