DataStructure from xlsx file

Question

I'm working on a project where I have to take folders and subfolders names from an excel file, keeping the hierarchy structure and store them in an array, to use that array for creating the directory structure.

I'm using Apache POI to read the excel file.

How can I store them into an array, considering that I have 8 tree-levels ?

For example :

Folder                                  
    Subfolder01                             
    Subfolder02                             
        Subfolder02.01                          
            Subfolder02.01.01                                       
                Subfolder02.01.01.01                                
            Subfolder02.01.02                                       
    Subfolder03                             
    Subfolder04                                     
        Subfolder04.01                              
            Subfolder04.01.01                                       
                Subfolder04.01.01.01                                
            Subfolder04.01.02                       
                Subfolder04.01.02.01                            
        Subfolder04.02                              

Here is how I read the excel file using Apache POI libs :

public class ExcelReadClass {

    private static final String FILE_NAME = "D:/Work-Space/TESTExcel.xlsx";

    public void readExcel(String fileName) {

        try {

            // XSSFWorkbook  = XML SpreadSheet Format
            // is for Excel 2007 or above  


            Workbook workbook = new XSSFWorkbook(fileName); 

            // Accessing the particular sheet
            // here the parameter indicates the sheet number. 0 means first sheet, 1 means the second and so on
            /// accessing first sheet - which is " Components " 
            Sheet sheet = workbook.getSheetAt(0);

            /*
             * Sheet can also be accessed using the sheet name like shown below
             * Sheet sheet = workbook.getSheet("Components");
             */

            // geting the rows

            // following code will work with empty cells
            Row row = null;
            Cell cell = null;

            // Returns: the number of physically defined rows in the selected sheet
            //int noOfRows = sheet.getPhysicalNumberOfRows();

            //Returns: last row contained n this sheet (0-based)
            int noOfRows = sheet.getLastRowNum();

            // starting from  0 - which is the first row
            for(int i = 2; i <= noOfRows; i++) {

                row = sheet.getRow(i);

                //int noOfCells = row.getPhysicalNumberOfCells(); // returns the total number of cells in the selected row
                //int noOfCells = row.getLastCellNum(); // returns the number of the last cell in the row
                int noOfCells = 11;

Here I'm outputing the file structure with Sysout The place where I have to store the whole structure into an array

                // starting from 0 - which is the first column ( aka cell )
                for(int j = 1; j < noOfCells; j++) {

                    cell = row.getCell(j) ; // if there's no more cells, it returns null


                    if(cell != null ) {
                        System.out.print(getCellValue(cell) + "\t");
                    } else {
                        Cell blanckCell = row.createCell(j);
                        blanckCell.setCellValue("");
                        System.out.print(getCellValue(blanckCell) + "\t");
                    }
                }
                System.out.println();
            }

            workbook.close();

        } catch (FileNotFoundException e) {

            System.out.println("File is not available.");
            e.printStackTrace();

        } catch (IOException e) {

            System.out.println("Problem reading file from directory.");
            e.printStackTrace();

        } catch (NullPointerException e){

            System.err.println("Last part of Excel");
            e.printStackTrace();

        }


    }

    public Object getCellValue(Cell cell){
        Object cellValue = null;
        if(cell.getCellTypeEnum() == CellType.STRING){
            cellValue = cell.getStringCellValue();
        }else if(cell.getCellTypeEnum() == CellType.NUMERIC){
            cellValue = cell.getNumericCellValue();
        }else if(cell.getCellTypeEnum() == CellType.BOOLEAN){
            cellValue = cell.getBooleanCellValue();
        }else if(cell.getCellTypeEnum() == CellType.BLANK){
            cellValue = "";
        }
        return cellValue;
    }

}

Answer 1

Here is a code snippet to help you start

try {

            final String FILENAME = "c:\\Rest\\Test\\data.txt"; //change to your file location
            BufferedReader br = new BufferedReader(new InputStreamReader(
                    new FileInputStream(FILENAME), "UTF-8")); //change it to your reader
            String line;
            //read file line by line
            while ((line = br.readLine()) != null) {
                System.out.println(line);
                Node root = null;
                if (line == "Folder") {
                    root = new Node(null);
                } else {
                    String indexs = line.substring(9, line.length());
                    if (indexs.length() == 2) {
                        // insert to root
                    } else if (indexs.length() == 4) {
                        // create node and use readLine to all sub nodes
                    }
                }

            }
            br.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

And the Node class:

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

public class Node {
    private String id;
    private final List<Node> children = new ArrayList<>();
    private final Node parent;

    public Node(Node parent) {
        this.parent = parent;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public List<Node> getChildren() {
        return children;
    }

    public Node getParent() {
        return parent;
    }

}

Answer 2

In Java you can use a HashMap or a TreeMap data structure in conjunction to an ArrayList and model your requirements as follows.

Map<String, Map<String, List<String>>>map = new HashMap<String, Map<String, List<String>>>();

Then while you read the names of the folders you can add them into the data structure i proposed. The hashmap is your Folder. The first level keys of the hashmap map will contain " Subfolder01 , Subfolder02, Subfolder03 , Subfolder04 ". The corresponding values will be the folders contained in the already mentioned folders. Each of those values will be a Map<String, List<String>>mi which in turn will contain the name of the subfolders "Subfolder02.01 ... " and so on for each level. When you reach the last level and find the files contained in the last folder you can insert those names in the ArrayList. You can extend or shring the levels of you datastructure as much as you need it according to the levels that you have.