EsvExcelReaderImpl.java

  1. /*
  2.  * Copyright 2010 James Pether Sörling
  3.  *
  4.  * Licensed under the Apache License, Version 2.0 (the "License");
  5.  * you may not use this file except in compliance with the License.
  6.  * You may obtain a copy of the License at
  7.  *
  8.  *   http://www.apache.org/licenses/LICENSE-2.0
  9.  *
  10.  * Unless required by applicable law or agreed to in writing, software
  11.  * distributed under the License is distributed on an "AS IS" BASIS,
  12.  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  13.  * See the License for the specific language governing permissions and
  14.  * limitations under the License.
  15.  *
  16.  *  $Id$
  17.  *  $HeadURL$
  18. */
  19. package com.hack23.cia.service.external.esv.impl;

  20. import java.io.IOException;
  21. import java.util.ArrayList;
  22. import java.util.Iterator;
  23. import java.util.List;
  24. import java.util.Map;
  25. import java.util.TreeMap;

  26. import org.apache.poi.hssf.usermodel.HSSFSheet;
  27. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  28. import org.apache.poi.ss.usermodel.Row;
  29. import org.slf4j.Logger;
  30. import org.slf4j.LoggerFactory;
  31. import org.springframework.stereotype.Component;

  32. import com.hack23.cia.service.external.esv.api.GovernmentBodyAnnualSummary;

  33. /**
  34.  * The Class EsvExcelReaderImpl.
  35.  */
  36. @Component
  37. final class EsvExcelReaderImpl implements EsvExcelReader {

  38.     /** The Constant COMMENT_CELL. */
  39.     private static final int COMMENT_CELL = 9;

  40.     /** The Constant VAT_CELL. */
  41.     private static final int VAT_CELL = 8;

  42.     /** The Constant ANNUAL_HEADCOUNT_CELL. */
  43.     private static final int ANNUAL_HEADCOUNT_CELL = 7;

  44.     /** The Constant HEADCOUNT_CELL. */
  45.     private static final int HEADCOUNT_CELL = 6;

  46.     /** The Constant ORG_NUMBER_CELL. */
  47.     private static final int ORG_NUMBER_CELL = 5;

  48.     /** The Constant MINISTRY_CELL. */
  49.     private static final int MINISTRY_CELL = 4;

  50.     /** The Constant MCODE_CELL. */
  51.     private static final int MCODE_CELL = 3;

  52.     /** The Constant GOVERNMENT_BODY_ID_CELL. */
  53.     private static final int GOVERNMENT_BODY_ID_CELL = 2;

  54.     /** The Constant CONSECUTIVE_NUMBER_CELL. */
  55.     private static final int CONSECUTIVE_NUMBER_CELL = 1;

  56.     /** The Constant NAME_CELL. */
  57.     private static final int NAME_CELL = 0;

  58.     /** The Constant EXPECTED_COLUMN_LENGTH. */
  59.     private static final int EXPECTED_COLUMN_LENGTH = 10;

  60.     /** The Constant LOGGER. */
  61.     private static final Logger LOGGER = LoggerFactory.getLogger(EsvExcelReaderImpl.class);

  62.     /**
  63.      * Instantiates a new esv excel reader impl.
  64.      */
  65.     public EsvExcelReaderImpl() {
  66.         super();
  67.     }

  68.     @Override
  69.     public Map<Integer, List<GovernmentBodyAnnualSummary>> getDataPerMinistry(final String name) {
  70.         final Map<Integer, List<GovernmentBodyAnnualSummary>> map = new TreeMap<>();
  71.         try {
  72.             final HSSFWorkbook myWorkBook = new HSSFWorkbook(
  73.                     EsvExcelReaderImpl.class.getResourceAsStream("/Myndighetsinformation.xls"));

  74.             for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
  75.                 addMinistryPerYearToMap(name, map, myWorkBook.getSheetAt(sheetNr));
  76.             }

  77.             myWorkBook.close();
  78.         } catch (

  79.         final IOException e) {
  80.             LOGGER.warn("Problem loading", e);
  81.         }

  82.         return map;
  83.     }

  84.     /**
  85.      * Adds the ministry per year to map.
  86.      *
  87.      * @param name
  88.      *            the name
  89.      * @param map
  90.      *            the map
  91.      * @param mySheet
  92.      *            the my sheet
  93.      */
  94.     private static void addMinistryPerYearToMap(final String name,
  95.             final Map<Integer, List<GovernmentBodyAnnualSummary>> map, final HSSFSheet mySheet) {
  96.         if (mySheet.getSheetName().chars().allMatch(Character::isDigit)) {

  97.             final int year = Integer.parseInt(mySheet.getSheetName());

  98.             final List<GovernmentBodyAnnualSummary> yearList = new ArrayList<>();
  99.             final Iterator<Row> rowIterator = mySheet.iterator();

  100.             // Skip header row, ignore first
  101.             rowIterator.next();

  102.             while (rowIterator.hasNext()) {
  103.                 addGovernmentBodyAnnualSummaryToList(name, year, yearList, rowIterator.next());
  104.             }
  105.             map.put(year, yearList);
  106.         }
  107.     }

  108.     /**
  109.      * Adds the government body annual summary to list.
  110.      *
  111.      * @param name
  112.      *            the name
  113.      * @param year
  114.      *            the year
  115.      * @param yearList
  116.      *            the year list
  117.      * @param row
  118.      *            the row
  119.      */
  120.     private static void addGovernmentBodyAnnualSummaryToList(final String name, final int year,
  121.             final List<GovernmentBodyAnnualSummary> yearList, final Row row) {
  122.         if (row.getLastCellNum() == EXPECTED_COLUMN_LENGTH) {

  123.             final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = createGovernmentBodyAnnualSummaryFromRow(
  124.                     year, row);

  125.             if (name == null || name.equalsIgnoreCase(governmentBodyAnnualSummary.getMinistry())) {
  126.                 yearList.add(governmentBodyAnnualSummary);
  127.             }
  128.         }
  129.     }

  130.     /**
  131.      * Gets the integer.
  132.      *
  133.      * @param str
  134.      *            the str
  135.      * @return the integer
  136.      */
  137.     private static int getInteger(final String str) {
  138.         if (str == null || str.trim().length() == 0) {
  139.             return 0;
  140.         } else {
  141.             return Integer.parseInt(str);
  142.         }
  143.     }

  144.     @Override
  145.     public Map<Integer, GovernmentBodyAnnualSummary> getDataPerGovernmentBody(final String name) {
  146.         final Map<Integer, GovernmentBodyAnnualSummary> map = new TreeMap<>();
  147.         try {
  148.             final HSSFWorkbook myWorkBook = new HSSFWorkbook(
  149.                     EsvExcelReaderImpl.class.getResourceAsStream("/Myndighetsinformation.xls"));

  150.             for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
  151.                 final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr);

  152.                 addDataForYearToMap(name, map, mySheet);
  153.             }
  154.             myWorkBook.close();
  155.         } catch (

  156.         final IOException e) {
  157.             LOGGER.warn("Problem loading", e);
  158.         }

  159.         return map;
  160.     }

  161.     /**
  162.      * Adds the data for year to map.
  163.      *
  164.      * @param name
  165.      *            the name
  166.      * @param map
  167.      *            the map
  168.      * @param mySheet
  169.      *            the my sheet
  170.      */
  171.     private static void addDataForYearToMap(final String name, final Map<Integer, GovernmentBodyAnnualSummary> map,
  172.             final HSSFSheet mySheet) {
  173.         if (mySheet.getSheetName().chars().allMatch(Character::isDigit)) {
  174.             final int year = Integer.parseInt(mySheet.getSheetName());
  175.             final Iterator<Row> rowIterator = mySheet.iterator();

  176.             rowIterator.next();

  177.             while (rowIterator.hasNext()) {
  178.                 addGovernmentBodyAnnualSummaryToMap(name, map, year, rowIterator.next());
  179.             }
  180.         }
  181.     }

  182.     /**
  183.      * Adds the government body annual summary to map.
  184.      *
  185.      * @param name
  186.      *            the name
  187.      * @param map
  188.      *            the map
  189.      * @param year
  190.      *            the year
  191.      * @param row
  192.      *            the row
  193.      */
  194.     private static void addGovernmentBodyAnnualSummaryToMap(final String name, final Map<Integer, GovernmentBodyAnnualSummary> map,
  195.             final int year, final Row row) {
  196.         if (row.getLastCellNum() == EXPECTED_COLUMN_LENGTH) {

  197.             final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = createGovernmentBodyAnnualSummaryFromRow(
  198.                     year, row);

  199.             if (name == null || name.equalsIgnoreCase(governmentBodyAnnualSummary.getName())) {
  200.                 map.put(year, governmentBodyAnnualSummary);
  201.             }
  202.         }
  203.     }

  204.     /**
  205.      * Creates the government body annual summary from row.
  206.      *
  207.      * @param year
  208.      *            the year
  209.      * @param row
  210.      *            the row
  211.      * @return the government body annual summary
  212.      */
  213.     private static GovernmentBodyAnnualSummary createGovernmentBodyAnnualSummaryFromRow(final int year, final Row row) {
  214.         return new GovernmentBodyAnnualSummary(year, row.getCell(NAME_CELL).toString(), getInteger(row.getCell(CONSECUTIVE_NUMBER_CELL).toString()),
  215.                 row.getCell(GOVERNMENT_BODY_ID_CELL).toString(), row.getCell(MCODE_CELL).toString(), row.getCell(MINISTRY_CELL).toString(),
  216.                 row.getCell(ORG_NUMBER_CELL).toString(), getInteger(row.getCell(HEADCOUNT_CELL).toString()), getInteger(row.getCell(ANNUAL_HEADCOUNT_CELL).toString()),
  217.                 row.getCell(VAT_CELL).toString(), row.getCell(COMMENT_CELL).toString());
  218.     }

  219. }