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