mardi 5 avril 2016

Storing a JavaFX 2D array into an SQLite database

I am trying to implement the ability to import a spreadhseet (.xls) file within my JavaFX program and store the information into the database. The software is to be used as an inventory management system and I would like a way to enter a lot of data at once, versus entering in the data one item at a time. I have been able to print to the console the array which is working fine. I would like to create a string of 18 (there are 18 columns in the database) and write each row of 18 to the database. Also the first item to be written is a unique id and I have been successful in the past by selecting the max id, adding 1, and using that as a new id for a new item. I am not quite sure how this will work if the array item is blank (possibly the sqlite database will auto-increment?). Anyway, my code is:

public class NewExcel {

private String inputFile;
String[][] data = null;
public void setInputFile(String inputFile) 
{
    this.inputFile = inputFile;
}

public String[][] read() throws IOException  
{
    File inputWorkbook = new File(inputFile);
    Workbook w;

    try 
    {
        w = Workbook.getWorkbook(inputWorkbook);
        // Get the first sheet

        StringBuilder row = new StringBuilder();
        Sheet sheet = w.getSheet(0);
        data = new String[sheet.getRows()][sheet.getColumns()];

        for (int j = 1; j <sheet.getRows(); j++) 
        {
            for (int i = 0; i < sheet.getColumns(); i++) 
            {
                Cell cell = sheet.getCell(i, j);
                data[j][i] = cell.getContents();
                System.out.println(cell.getContents());
                // System.out.println(j);
                // System.out.println(i);
            }
        }


    } 
    catch (BiffException e) 
    {
        System.out.println(e);
        e.printStackTrace();
    }
return data;
}

}

This is a separate class to handle the processing of the .xls file. It is called from my MainController in:

@FXML
private void importDB() {
    Stage window = new Stage();
    FileChooser fileChooser = new FileChooser();
    fileChooser.setTitle("Choose excel file");
    //Set extension filter
    FileChooser.ExtensionFilter extFilterXLS = new FileChooser.ExtensionFilter("XLS files", "*.xls");
    fileChooser.getExtensionFilters().addAll(extFilterXLS);
    file = fileChooser.showOpenDialog(window);
    fileName = file.getPath();

    NewExcel newExcel = new NewExcel();
    newExcel.setInputFile(fileName);
    try {
        newExcel.read();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

I would like to loop through the .xls file and create a 2d array and then save that array to the programs sqlite database. Please help, thanks!

Aucun commentaire:

Enregistrer un commentaire