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.Cell;
  29. import org.apache.poi.ss.usermodel.Row;
  30. import org.slf4j.Logger;
  31. import org.slf4j.LoggerFactory;
  32. import org.springframework.stereotype.Component;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  69.     @Override
  70.     public Map<Integer, List<GovernmentBodyAnnualSummary>> getDataPerMinistry(final String name) {
  71.         final Map<Integer, List<GovernmentBodyAnnualSummary>> map = new TreeMap<>();
  72.         try {
  73.             final HSSFWorkbook myWorkBook = createGovermentBodyWorkBook();

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

  77.             myWorkBook.close();
  78.         } catch (final IOException e) {
  79.             LOGGER.warn("Problem loading", e);
  80.         }

  81.         return map;
  82.     }

  83.     private static HSSFWorkbook createGovermentBodyWorkBook() throws IOException {
  84.         return new HSSFWorkbook(EsvExcelReaderImpl.class.getResourceAsStream("/Myndighetsinformation.xls"));
  85.     }

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

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

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

  102.             // Skip header row, ignore first
  103.             rowIterator.next();

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

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

  125.             final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = createGovernmentBodyAnnualSummaryFromRow(
  126.                     year, row);

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

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

  146.     @Override
  147.     public Map<Integer, GovernmentBodyAnnualSummary> getDataPerGovernmentBody(final String name) {
  148.         final Map<Integer, GovernmentBodyAnnualSummary> map = new TreeMap<>();
  149.         try {
  150.             final HSSFWorkbook myWorkBook = createGovermentBodyWorkBook();

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

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

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

  160.         return map;
  161.     }

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

  177.             rowIterator.next();

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

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

  198.             final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = createGovernmentBodyAnnualSummaryFromRow(
  199.                     year, row);
  200.             if (name == null || name.equalsIgnoreCase(governmentBodyAnnualSummary.getName())) {
  201.                 map.put(year, governmentBodyAnnualSummary);
  202.             }
  203.         }
  204.     }

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

  220.     /**
  221.      * Default value if null.
  222.      *
  223.      * @param cell
  224.      *            the cell
  225.      * @return the string
  226.      */
  227.     private static String defaultValueIfNull(final Cell cell) {
  228.         if (cell != null) {
  229.             return cell.toString();
  230.         } else {
  231.             return "";
  232.         }
  233.     }
  234.    
  235. }