Louis,
From the code I gather that you are doing a direct insert into a table with sql? This will indeed work in older versions but not in the newer versions as there is a id column added that you are not in control of. This id is a Mendix internal ID that is giving you the issues. I would strongly suggest to use the excel importer for this function and not try to play around with the Mendix internal ID, or rewrite the code so that you create the entries via the Mendix API instead of direct inserts with sql.
I assume you updated the Excel importer and the MxModelreflection to the latest version?
[EDIT] I indeed misread your question. But I do not understand the reason for not using the Excel importer and MxModelreflection module. Since either way you have to adjust the model by either adapting the java code or using the Excelimporter (or if it is csv files you need to import, use the new flat file importer from Erwin 't Hoen). Setting up these modules is way faster then adjusting this code.
Regards,
Ronald
(This is the only code I can put here, but it runs three main sections, setup a hashmap, get rid of the headers and finally load the spreadsheet). It's currently failing on the load of the spreadsheet. I'm assuming we could add a call to insert the unique id from the last record on the table. LR.
private void importSpreadsheet(String spreadsheetName) throws CoreException, InvalidFormatException, IOException, SQLException {
setHashMap(spreadsheetName);
bindHeaders(spreadsheetName);
loadSpreadsheet(spreadsheetName);
}
private void setHashMap(String spreadsheetName) {
this.referenceMap = new HashMap<String, String>();
this.referenceMap.put("Job Number", "jobnumber");
this.referenceMap.put("BP Company", "suppliername");
this.referenceMap.put("v_Branch", "supplierbranch");
this.referenceMap.put("Job Type", "jobtype");
this.referenceMap.put("Visit Completion Date", "datecompleted");
this.referenceMap.put("Account Number", "customeraccountno");
this.referenceMap.put("Post Code", "customerpostcode");
this.referenceMap.put("Visit Requirement Description", "visitrequirementdescription");
this.referenceMap.put("Customer Status", "customerstatus");
this.referenceMap.put("No Phone Line", "nophoneline");
this.referenceMap.put("Visit Date", "datevisited");
if (spreadsheetName.equals("Install-Move")) {
this.referenceMap.put("Total Visit Minutes", "givenminutes");
} else {
this.referenceMap.put("Visit Minutes", "givenminutes");
}
}
private void bindHeaders(String spreadsheetName) throws CoreException, IOException, InvalidFormatException {
InputStream inputStream = null;
Workbook workbook = null;
String cellValue = "";
boolean found = false;
spreadsheetMap = new HashMap<Integer, String>();
try {
inputStream = new PushbackInputStream (Core.getFileDocumentContent(context, dataFile.getMendixObject()));
if (inputStream == null) {
throw new CoreException ("There is no file uploaded");
}
workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheet(spreadsheetName);
if (sheet == null) {
System.out.println (spreadsheetName + " not found in " + this.workbookName);
return;
}
for (Row row : sheet) {
for (Cell cell : row) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
cellValue = cell.getRichStringCellValue().getString();
if (this.referenceMap.containsKey(cellValue)) {
found = true;
if (cellValue.equals("Job Number")) {
this.jobNumberColumn = cell.getColumnIndex();
} else if (cellValue.equals("Visit Requirement Description")) {
this.visitRequrementDescriptionColumn = cell.getColumnIndex();
}
this.spreadsheetMap.put(new Integer(cell.getColumnIndex()), this.referenceMap.get(cellValue));
}
}
}
if (found) {
if (this.referenceMap.size() != this.spreadsheetMap.size()) {
System.out.println ("Unable to map spreadsheet fields");
}
break;
}
}
} catch (FileNotFoundException e) {
System.out.println ("Unable to find spreadsheet: " + spreadsheetName);
rows = -1l;
throw e;
} catch (IOException e) {
System.out.println ("Unable to read spreadsheet: " + spreadsheetName);
rows = -1l;
throw e;
} catch (InvalidFormatException e) {
System.out.println ("Invalid spreadsheet format. Please ensure the spreadsheet is of type XLS (Office 2003)");
rows = -1l;
throw e;
}
}
private void loadSpreadsheet(String spreadsheetName) throws CoreException, IOException, InvalidFormatException, SQLException {
InputStream inputStream = null;
Workbook workbook = null;
String cellValue = "";
VisitRequirementDescription visitRequirementDescription = new VisitRequirementDescription();
String sql = null;
try {
inputStream = new PushbackInputStream (Core.getFileDocumentContent(context, dataFile.getMendixObject()));
if (inputStream == null) {
throw new CoreException ("There is no file uploaded");
}
workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheet(spreadsheetName);
if (sheet == null) {
System.out.println (spreadsheetName + " not found in " + this.workbookName);
return;
}
for (Row row : sheet) {
if ((row.getCell(this.jobNumberColumn) != null &&
(row.getCell(this.jobNumberColumn).getCellType() == Cell.CELL_TYPE_STRING &&
!row.getCell(this.jobNumberColumn).getStringCellValue().equals("Job Number"))) &&
(row.getCell(this.visitRequrementDescriptionColumn) != null &&
row.getCell(this.visitRequrementDescriptionColumn).getCellType() == Cell.CELL_TYPE_STRING)) {
rows++;
String inserts = "";
String values = "";
String house = "";
String street = "";
for (Cell cell : row) {
if (this.spreadsheetMap.containsKey(new Integer(cell.getColumnIndex()))) {
switch(cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue().replace('\'', '"');
if (cell.getColumnIndex() != this.jobNumberColumn) {
if (cellValue.equals("#MULTIVALUE")) {
cellValue = "0";
} else {
cellValue = "'" + cellValue + "'";
}
}
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
cellValue = "'" + simpleDateFormat.format(cell.getDateCellValue()).toString() + "'";
} else {
cellValue = cell.toString();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
if (cell.getBooleanCellValue()) {
cellValue = "1";
} else {
cellValue = "0";
}
break;
default:
continue;
}
if (cell.getColumnIndex() == this.visitRequrementDescriptionColumn) {
inserts += ", tasks";
values += ", '" + visitRequirementDescription.parse(cellValue.substring(1, cellValue.length() - 1)) + "'";
}
if (inserts.isEmpty()) {
inserts = this.spreadsheetMap.get(new Integer(cell.getColumnIndex()));
values = cellValue;
} else {
inserts += ", " + this.spreadsheetMap.get(new Integer(cell.getColumnIndex()));
values += ", " + cellValue;
}
}
}
if (!inserts.isEmpty()) {
sql = "insert into " + STORAGE_TABLE + " (" + inserts + ") values (" + values + ")";
// System.out.println (sql);
this.statement.executeUpdate(sql);
}
}
}
} catch (FileNotFoundException e) {
System.out.println ("Unable to find spreadsheet: " + spreadsheetName);
rows = -1l;
throw e;
} catch (IOException e) {
System.out.println ("Unable to read spreadsheet: " + spreadsheetName);
rows = -1l;
throw e;
} catch (InvalidFormatException e) {
System.out.println ("Invalid spreadsheet format. Please ensure the spreadsheet is of type XLS (Office 2003)");
rows = -1l;
throw e;
} catch (SQLException e) {
System.out.println (sql);
rows = -1l;
throw e;
}
}