1
\$\begingroup\$

Input:

  • Row at index 0 is always the titles of each column.
  • Columns will always be expected to be properly arranged for the chosen format.
  • There may be empty or incorrectly formatted fields.
  • Can always expect a .xls input file (must be manually entered).

Output:

  • Outputs a JSON array of every row in the excel sheet.
  • Outputs as formatted plaintext (.txt) to be handled by the database importer.

Special Notes: It can be reasonably assumed that anyone using the program will have advanced computer user knowledge, and maybe even Java programming knowledge. Either way, it will be minimal-use and does not need to be "idiot-proofed." Also, this is my first time using graphical interfaces with Java.

Here is a program I wrote to aide in an NPO's database migration during a database software upgrade (moving from Silverlight to JS), I wrote it in java because I have a more advanced knowledge in writing programs in Java, but I have more experience with EE, not SE, so I'm nowhere near the best. What concepts, comments, implementations, or cases am I missing or should be improved? Please do not criticize my use of language or library unless it is an integral component of substantial constructive criticism.

Note: You can find the full set here for at least until 10/31/17, but I will put one format set on here (of the five).

bin.Main:

package bin;

import javax.swing.JFrame;

/**
 * Main method that creates the Frame to use the program with.
 * 
 * @author Liam Williams
 * @version 1
 */
public class Main {
    static ParserFrame f;

    public static void main(String[] args) {
        f = new ParserFrame();
        f.add(f.panel);
        f.setSize(800, 200);
        f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        f.setVisible(true);
    }

}

bin.ParserFrame:

package bin;

import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.FileNotFoundException;
import java.io.PrintWriter;

import javax.swing.*;

import bin.parse.*;
import bin.parse.com.Parser;

/**
 * Defines the Frame and its button to use the program with.
 * 
 * @author Liam Williams
 * @version 1
 */
@SuppressWarnings("serial")
public class ParserFrame extends JFrame implements ActionListener {
    private JButton parse;
    private JLabel desc, inpt, oupt;
    private JTextField input, output;
    final JComboBox<String> menu;
    final int FIELDS_WIDTHS = 35;
    String[] CHOICES = {"Members", "MembersDues", "MembersLevel", "MembersAddress", "MembersContact"};

    private Parser p;

    public JPanel panel = new JPanel();

    public ParserFrame() {
        //Defines the elements in the frame.
        desc = new JLabel("Select type of format to parse: ");
        menu = new JComboBox<String>(CHOICES);
        menu.setVisible(true);
        inpt = new JLabel("Select input source .xls file: ");
        input = new JTextField(FIELDS_WIDTHS);
        oupt = new JLabel("Select output location (end with .txt extension): ");
        output = new JTextField(FIELDS_WIDTHS);
        parse = new JButton("Parse the file.");
        parse.addActionListener(this);

        //Adds the elements and layouts to the frame.
        panel.setLayout(new GridLayout(4, 2));
        panel.add(desc);
        panel.add(menu);
        panel.add(inpt);
        panel.add(input);
        panel.add(oupt);
        panel.add(output);
        panel.add(parse);
    }

    private void createButton() {
        parse = new JButton("Parse File");
        parse.addActionListener(this);
    }

    @Override
    public void actionPerformed(ActionEvent e) {
        p = selectParser((String) menu.getSelectedItem());
        String outputJSON = p.parseFile(input.getText());
        try(PrintWriter out = new PrintWriter(output.getText())){
            out.println(outputJSON);
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        }
    }

    private Parser selectParser(String selection) {
        switch (selection) {
        case "Members": return new MembersParser();
        case "MembersDues": return new MembersDuesParser();
        case "MembersLevel": return new MembersLevelParser();
        case "MembersAddress": return new MembersAddressParser();
        case "MembersContact": return new MembersContactParser();
        }
        return null;
    }
}

bin.format.Members:

package bin.format;

import java.util.Date;

/**
 * Holds the data from the Excel sheet and formats it for the JSON output for api/members.
 * 
 * @author Liam Williams
 * @version 1
 */

public class Members {
    public int id;
    public String firstName;
    public String lastName;
    public String title;
    public String suffix;
    public String nickname;
    public String fullName;
    public String chapterName;
    public String gender;
    public String ageGroup;
    public int unit;
    public String level;
    public Date levelDate;
    public String currentPosition;
    public Date youthProtectionDate;
}

bin.parse.MembersParser:

package bin.parse;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.google.gson.Gson;

import bin.format.Members;
import bin.parse.com.*;

/**
 * Parses a file into the Members format.
 * 
 * @author Liam Williams
 * @version 1
 */
public class MembersParser implements Parser, Formatter, Try, Get {
    Gson g;
    List<Members> j = new ArrayList<Members>();
    HSSFWorkbook w = null;
    HSSFRow r = null;

    public MembersParser() {
        g = getGson();
    }

    public String parseFile(String inputLocation) {
        try {
            w = Parser.readFile(inputLocation);
        } catch (IOException e) {
            e.printStackTrace();
        }

        HSSFSheet s = w.getSheetAt(0);
        for(int i = 1; i < s.getPhysicalNumberOfRows(); i++) {
            Members m = new Members();
            r = s.getRow(i);

            m.id = tryNumber(r.getCell(0));
            m.firstName = tryString(r.getCell(1));
            m.lastName = tryString(r.getCell(2));
            m.title = tryString(r.getCell(3));
            m.suffix = tryString(r.getCell(4));
            m.nickname = tryString(r.getCell(5));
            m.fullName = tryString(r.getCell(6));
            m.chapterName = tryString(r.getCell(7));
            m.gender = tryString(r.getCell(8));
            m.ageGroup = tryString(r.getCell(9));
            m.unit = tryNumber(r.getCell(10));
            m.level = tryString(r.getCell(11));
            //m.levelDate = tryDate(r.getCell(12)); Ignored due to field not existing in OALM
            m.currentPosition = tryString(r.getCell(13));
            m.youthProtectionDate = tryDate(r.getCell(14));

            j.add(m);
        }

        Members[] o = new Members[j.size()];
        for(int i = 0; i < j.size(); i++) {
            o[i] = j.get(i);
        }

        return g.toJson(o);
    }

}

bin.parse.com.Parser:

package bin.parse.com;

import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * True Functional Interface containing the method for reading the Excel .xls file and defining the parse method name.
 * 
 * @author Liam Williams
 * @version 1
 */
@FunctionalInterface
public interface Parser {

    public String parseFile(String inputLocation);

    static HSSFWorkbook readFile(String filename) throws IOException {
        FileInputStream fis = new FileInputStream(filename);
        try {
            return new HSSFWorkbook(fis);       // NOSONAR - should not be closed here
        } finally {
            fis.close();
        }
    }

}

bin.parse.com.Formatter:

package bin.parse.com;

import java.util.Date;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.TypeAdapter;

/**
 * Functional Interface-like interface to contain the Gson builder used under free-use from the internet.
 * 
 * @credit bmarty
 * @author Liam Williams
 * @version 1
 */

public interface Formatter {
    /**
     * Credit to bmarty at https://github.com/google/gson/issues/1096
     */
    default Gson getGson() {
        // Trick to get the DefaultDateTypeAdatpter instance
        // Create a first instance a Gson
        Gson gson = new GsonBuilder()
                .setPrettyPrinting()
                .setDateFormat("yyyy-MM-dd'T'HH:mm:ssz")
                .create();

        // Get the date adapter
        TypeAdapter<Date> dateTypeAdapter = gson.getAdapter(Date.class);

        // Ensure the DateTypeAdapter is null safe
        TypeAdapter<Date> safeDateTypeAdapter = dateTypeAdapter.nullSafe();

        // Build the definitive safe Gson instance
        return new GsonBuilder()
                .setPrettyPrinting()
                .setDateFormat("yyyy-MM-dd'T'HH:mm:ssz")
                .registerTypeAdapter(Date.class, safeDateTypeAdapter)
                .create();
    }
}

bin.parse.com.Get:

package bin.parse.com;

import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;

/**
 * Multi-functional interface containing the "get" methods for acquiring raw data from the Excel cells.
 * 
 * @author Liam Williams
 * @version 1
 */
public interface Get {
    default String getString(HSSFCell c) {
        try {
            return c.getStringCellValue();
        } catch (NullPointerException e) {
            e.printStackTrace();
        }

        return "";
    }

    default int getNumber(HSSFCell c) {
        try {
            return (int) c.getNumericCellValue();
        } catch (NullPointerException e) {
            e.printStackTrace();
        } 

        return 0;
    }

    default Date getDate(HSSFCell c) {
        try {
            return c.getDateCellValue();
        } catch (NullPointerException e) {
            e.printStackTrace();
        }

        return null;
    }
}

bin.parse.com.Try:

package bin.parse.com;

import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.CellType;

/**
 * Multi-functional interface containing the "try" methods to gather the correct type of data using "get" method.
 * 
 * @author Liam Williams
 * @version 1
 */
public interface Try extends Get {
    default String tryString(HSSFCell c) {
        if(c == null) {
            return "";
        }
        if(c.getCellTypeEnum() == CellType.STRING) {
            return getString(c);
        } else if (c.getCellTypeEnum() == CellType.NUMERIC) {
            return String.valueOf(getNumber(c));
        } else {
            return "";
        }
    }

    default int tryNumber(HSSFCell c) {
        if(c == null) {
            return 0;
        }
        if(c.getCellTypeEnum() == CellType.NUMERIC) {
            return getNumber(c);
        } else if (c.getCellTypeEnum() == CellType.STRING) {
            if(getString(c) == "") {
                return 0;
            } else {
                return Integer.valueOf(getString(c));
            }
        } else {
            return 0;
        }
    }

    default Date tryDate(HSSFCell c) {
        if(c == null) {
            return null;
        }
        if(c.getCellTypeEnum() != CellType.NUMERIC) {
            return null;
        } else {
            return getDate(c);
        }
    }

    default boolean tryBoolean(HSSFCell c) {
        if(c == null) {
            return false;
        }
        if(c.getCellTypeEnum() != CellType.BOOLEAN) {
            return c.getBooleanCellValue();
        } else {
            return false;
        }
    }
}

Yes, the database in question is called OALM. And all my test cases have been successful.

Sorry for the long post, but it's for an NPO so I'd like to try and get a more experienced opinion before giving it to them.

Edit: Don't worry about the magic index numbers for the for loops, the format styles of the inputs isn't ever changing for this use version.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.