public void setColumnNamesPattern(Sheet sheet) {
sbColumnNames = new StringBuilder();
for (Column c : sheet.getColumns()) {
sbColumnNames.append("P\\{" + c.getName() + "\\}" + "|I\\{" + c.getName() + "\\}" + "|");
}
sbColumnNames.delete(sbColumnNames.length() - 1, sbColumnNames.length());
System.out.println(sbColumnNames.toString());
}
@Override
public void bracketEvent(String content) {
System.out.println(content);
Column c = sheet.getColumnByName(content);
if (c != null) {
c.setIgnore(false);
System.out.println("yes");
if (sbColumnNames == null) {
setColumnNamesPattern(sheet);
}
paneSyntax.highlightPattern(sbColumnNames.toString());
table.repaint();
}
}
private String createStatements(boolean preview) {
boolean insOnly = true;
StringBuilder sbTotal = new StringBuilder();
StringBuffer sbStatement = new StringBuffer();
String rawText = new String(paneSyntax.getRawText());
Pattern bracketPattern = Pattern.compile("P\\{.*?\\}|I\\{.*?\\}");
Matcher matcher = bracketPattern.matcher(rawText);
while (matcher.find()) {
String s = matcher.group(0);
if (matcher.group(0).charAt(0) == 'P') {
insOnly = false;
}
s = s.substring(1, s.length() - 1);
}
int iRows = sheet.getRowCount() - 1;
int iCol = sheet.getColCount();
StringBuilder sbStmt = new StringBuilder();
StringBuilder sbWhere = new StringBuilder();
StringBuilder sbSet = new StringBuilder();
List<String> listWheres = new ArrayList<String>();
List<String> listSets = new ArrayList<String>();
Row currRow;
if (!insOnly) {
for (int i = 0; i < (preview ? 1 : iRows); i++) {
matcher.reset();
sbStatement.setLength(0);
while (matcher.find()) {
String s = matcher.group(0);
s = s.substring(2, s.length() - 1);
Column c = sheet.getColumnByName(s);
if (c != null) {
matcher.appendReplacement(sbStatement, "");
sbStatement.append(c.getValueAsString(i));
}
}
matcher.appendTail(sbStatement);
System.out.println(sbStatement.toString());
sbTotal.append(sbStatement.toString() + "\n");
}
return sbTotal.toString();
} else {
sbTotal.setLength(0);
matcher.reset();
sbStatement.setLength(0);
while (matcher.find()) {
String s = matcher.group(0);
matcher.appendReplacement(sbStatement, "");
if (s.charAt(0) == 'I') {
s = s.substring(2, s.length() - 1);
Column c = sheet.getColumnByName(s);
if (c != null) {
sbStatement.append("(");
for (int i = 0; i < sheet.getRowCount() - 1; i++) {
sbStatement.append(c.getValueAsString(i) + ", ");
}
sbStatement.delete(sbStatement.length() - 2, sbStatement.length());
sbStatement.append(")");
}
}
}
matcher.appendTail(sbStatement);
return sbStatement.toString();
}
}
public void checkValid(Sheet sheet) {
if (!sheet.isValid()) {
JOptionPane.showConfirmDialog(this, "Tabelle kann in diesem Programm nicht benutzt werden");
}
}
private String createUpdateFromColumns(boolean preview) {
String table = textField.getText().trim();
while (table == null || table.equals("")) {
table = JOptionPane.showInputDialog("Bitte erst einen Tabellennamen eingeben");
if (table != null) {
table.trim();
}
}
textField.setText(table);
int iRows = sheet.getRowCount() - 1;
int iCol = sheet.getColCount();
StringBuilder sbTotal = new StringBuilder();
StringBuilder sbStmt = new StringBuilder();
StringBuilder sbWhere = new StringBuilder();
StringBuilder sbSet = new StringBuilder();
List<String> listWheres = new ArrayList<String>();
List<String> listSets = new ArrayList<String>();
Row currRow;
for (int i = 0; i < (preview ? 1 : iRows); i++) {
listWheres.clear();
listSets.clear();
sbStmt.setLength(0);
sbWhere.setLength(0);
sbSet.setLength(0);
sbStmt.append("UPDATE " + table + " ");
currRow = xssfSheet.getRow(i);
for (int j = 0; j < iCol; j++) {
if (!sheet.getColumn(j).isIgnore()) {
if (sheet.getColumn(j).isWhere()) {
sbWhere.setLength(0);
sbWhere.append(sheet.getColumn(j).getName() + " = ");
Cell cell = xssfSheet.getRow(i).getCell(j);
if (sheet.getColumn(j).getType() == Column.COL_TYPE_NUMBER) {
sbWhere.append("" + sheet.getColumn(j).getNumber(i));
} else if (sheet.getColumn(j).getType() == Column.COL_TYPE_STRING) {
String strCleaned = cleanString(sheet.getColumn(j).getString(i));
sbWhere.append("'" + strCleaned + "'");
}
listWheres.add(new String(sbWhere.toString()));
} else {
sbSet.setLength(0);
sbSet.append(sheet.getColumn(j).getName() + " = ");
Cell cell = xssfSheet.getRow(i).getCell(j);
if (sheet.getColumn(j).getType() == Column.COL_TYPE_NUMBER) {
sbSet.append("" + sheet.getColumn(j).getNumber(i));
} else if (sheet.getColumn(j).getType() == Column.COL_TYPE_STRING) {
String strCleaned = cleanString(sheet.getColumn(j).getString(i));
sbSet.append("'" + strCleaned + "'");
}
listSets.add(new String(sbSet.toString()));
}
}
}
Iterator<String> iterator = listSets.iterator();
if (iterator.hasNext()) {
sbStmt.append("SET " + iterator.next());
while (iterator.hasNext()) {
sbStmt.append(", " + iterator.next());
}
sbStmt.append(" ");
}
iterator = listWheres.iterator();
if (iterator.hasNext()) {
sbStmt.append(" WHERE " + iterator.next());
while (iterator.hasNext()) {
sbStmt.append(" AND " + iterator.next());
}
}
sbTotal.append(sbStmt.toString() + ";\n");
System.out.println(sbStmt.toString());
}
return new String(sbTotal.toString());
}