com.phloc.genericode.excel.ExcelSheetToCodeList10 Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of phloc-genericode Show documentation
Library to read and write OASIS GeneriCode documents
The newest version!
/**
 * Copyright (C) 2006-2014 phloc systems
 * http://www.phloc.com
 * office[at]phloc[dot]com
 *
 * 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 com.phloc.genericode.excel;

import java.net.URI;
import java.util.List;

import javax.annotation.Nonnull;
import javax.annotation.Nullable;
import javax.annotation.concurrent.Immutable;
import javax.xml.bind.JAXBElement;
import javax.xml.namespace.QName;

import org.apache.poi.ss.usermodel.Sheet;

import com.phloc.commons.string.StringHelper;
import com.phloc.genericode.Genericode10Utils;
import com.phloc.genericode.v10.Annotation;
import com.phloc.genericode.v10.AnyOtherContent;
import com.phloc.genericode.v10.CodeListDocument;
import com.phloc.genericode.v10.Column;
import com.phloc.genericode.v10.ColumnSet;
import com.phloc.genericode.v10.Identification;
import com.phloc.genericode.v10.Key;
import com.phloc.genericode.v10.ObjectFactory;
import com.phloc.genericode.v10.Row;
import com.phloc.genericode.v10.SimpleCodeList;
import com.phloc.genericode.v10.UseType;
import com.phloc.genericode.v10.Value;
import com.phloc.poi.excel.ExcelReadUtils;

/**
 * A utility class to convert a simple Excel sheet into a code list v1.0
 * consisting of simple values. Please note that merged cells are currently not
 * supported!
 * 
 * @author Philip Helger
 */
@Immutable
public final class ExcelSheetToCodeList10
{
  private static final QName QNAME_ANNOTATION = new QName ("urn:phloc.com:schemas:genericode-ext", "info");

  private ExcelSheetToCodeList10 ()
  {}

  @Nonnull
  public static CodeListDocument convertToSimpleCodeList (@Nonnull final Sheet aExcelSheet,
                                                          @Nonnull final ExcelReadOptions  aReadOptions,
                                                          @Nonnull final String sCodeListName,
                                                          @Nonnull final String sCodeListVersion,
                                                          @Nonnull final URI aCanonicalUri,
                                                          @Nonnull final URI aCanonicalVersionUri,
                                                          @Nullable final URI aLocationURI)
  {
    if (aExcelSheet == null)
      throw new NullPointerException ("sheet");
    if (aReadOptions == null)
      throw new NullPointerException ("readOptions");

    final ObjectFactory aFactory = new ObjectFactory ();
    final CodeListDocument ret = aFactory.createCodeListDocument ();

    // create annotation
    final Annotation aAnnotation = aFactory.createAnnotation ();
    final AnyOtherContent aContent = aFactory.createAnyOtherContent ();
    aContent.getAny ().add (new JAXBElement  (QNAME_ANNOTATION,
                                                      String.class,
                                                      null,
                                                      "Automatically created by phloc-genericode. Do NOT edit."));
    aAnnotation.setAppInfo (aContent);
    ret.setAnnotation (aAnnotation);

    // create identification
    final Identification aIdentification = aFactory.createIdentification ();
    aIdentification.setShortName (Genericode10Utils.createShortName (sCodeListName));
    aIdentification.setVersion (sCodeListVersion);
    aIdentification.setCanonicalUri (aCanonicalUri.toString ());
    aIdentification.setCanonicalVersionUri (aCanonicalVersionUri.toString ());
    if (aLocationURI != null)
      aIdentification.getLocationUri ().add (aLocationURI.toString ());
    ret.setIdentification (aIdentification);

    // create columns
    final List > aExcelColumns = aReadOptions.getAllColumns ();
    final ColumnSet aColumnSet = aFactory.createColumnSet ();
    for (final ExcelReadColumn  aExcelColumn : aExcelColumns)
    {
      // Read short name (required)
      final String sShortName = aExcelSheet.getRow (aReadOptions.getLineIndexShortName ())
                                           .getCell (aExcelColumn.getIndex ())
                                           .getStringCellValue ();

      // Read long name (optional)
      String sLongName = null;
      if (aReadOptions.getLineIndexLongName () >= 0)
        sLongName = aExcelSheet.getRow (aReadOptions.getLineIndexLongName ())
                               .getCell (aExcelColumn.getIndex ())
                               .getStringCellValue ();

      // Create Genericode column set
      final Column aColumn = Genericode10Utils.createColumn (aExcelColumn.getColumnID (),
                                                             aExcelColumn.getUseType (),
                                                             sShortName,
                                                             sLongName,
                                                             aExcelColumn.getDataType ());

      // add column
      aColumnSet.getColumnChoice ().add (aColumn);

      if (aExcelColumn.isKeyColumn ())
      {
        // Create key definition
        final Key aKey = Genericode10Utils.createKey (aExcelColumn.getColumnID () + "Key",
                                                      sShortName,
                                                      sLongName,
                                                      aColumn);

        // Add key
        aColumnSet.getKeyChoice ().add (aKey);
      }
    }
    ret.setColumnSet (aColumnSet);

    // Read items
    final SimpleCodeList aSimpleCodeList = aFactory.createSimpleCodeList ();

    // Determine the row where reading should start
    int nRowIndex = aReadOptions.getLinesToSkip ();
    while (true)
    {
      // Read a single excel row
      final org.apache.poi.ss.usermodel.Row aExcelRow = aExcelSheet.getRow (nRowIndex++);
      if (aExcelRow == null)
        break;

      // Create Genericode row
      final Row aRow = aFactory.createRow ();
      for (final ExcelReadColumn  aExcelColumn : aExcelColumns)
      {
        final String sValue = ExcelReadUtils.getCellValueString (aExcelRow.getCell (aExcelColumn.getIndex ()));
        if (StringHelper.hasText (sValue) || aExcelColumn.getUseType () == UseType.REQUIRED)
        {
          // Create a single value in the current row
          final Value aValue = aFactory.createValue ();
          aValue.setColumnRef (Genericode10Utils.getColumnOfID (aColumnSet, aExcelColumn.getColumnID ()));
          aValue.setSimpleValue (Genericode10Utils.createSimpleValue (sValue));
          aRow.getValue ().add (aValue);
        }
      }
      aSimpleCodeList.getRow ().add (aRow);
    }
    ret.setSimpleCodeList (aSimpleCodeList);

    return ret;
  }
}