WebSVN - OpenConcerto - Diff - Rev 156 and 174 - /trunk/OpenConcerto/src/org/openconcerto/erp/importer/DataImporter.java

  * When distributing the software, include this License Header Notice in each file.
  */
  package org.openconcerto.erp.importer;
-import org.openconcerto.erp.config.ComptaPropsConfiguration;
 import org.openconcerto.openoffice.spreadsheet.Sheet;
 import org.openconcerto.openoffice.spreadsheet.SpreadSheet;
-import org.openconcerto.sql.Configuration;
-import org.openconcerto.sql.model.SQLBase;
 import org.openconcerto.sql.model.SQLField;
 import org.openconcerto.sql.model.SQLRowValues;
 import org.openconcerto.sql.model.SQLRowValuesCluster.StoreMode;
 import org.openconcerto.sql.model.SQLRowValuesListFetcher;
 import org.openconcerto.sql.model.SQLTable;
+import org.openconcerto.utils.ooxml.XLSXDocument;
-import org.openconcerto.sql.users.UserManager;
+import org.openconcerto.utils.ooxml.XLSXSheet;
 import org.openconcerto.utils.text.CSVReader;
 import org.openconcerto.utils.text.CSVWriter;
 import org.openconcerto.utils.text.CharsetHelper;
 import java.io.BufferedInputStream;
 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
 import org.apache.poi.hssf.usermodel.HSSFSheet;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
+import org.apache.poi.ss.formula.eval.NotImplementedException;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellValue;
 import org.apache.poi.ss.usermodel.FormulaEvaluator;
 import org.apache.poi.ss.usermodel.Row;
 public class DataImporter {
     private boolean skipFirstLine = true;
     private SQLTable table;
-    private final Map<Integer, ValueConverter> map = new HashMap<Integer, ValueConverter>();
+    private final Map<Integer, ValueConverter> map = new HashMap<>();
-    private final Map<SQLField, List<Integer>> fieldMap = new HashMap<SQLField, List<Integer>>();
+    private final Map<SQLField, List<Integer>> fieldMap = new HashMap<>();
-    private final Map<Integer, Constraint> constraints = new HashMap<Integer, Constraint>();
+    private final Map<Integer, Constraint> constraints = new HashMap<>();
-    private List<SQLField> uniqueField = new ArrayList<SQLField>();
+    private List<SQLField> uniqueField = new ArrayList<>();
-    private List<SQLRowValues> valuesToUpdate = new ArrayList<SQLRowValues>();
+    private List<SQLRowValues> valuesToUpdate = new ArrayList<>();
-    private List<SQLRowValues> valuesToInsert = new ArrayList<SQLRowValues>();
+    private List<SQLRowValues> valuesToInsert = new ArrayList<>();
-    private Map<ValueConverter, SQLField> foreignMap = new HashMap<ValueConverter, SQLField>();
+    private Map<ValueConverter, SQLField> foreignMap = new HashMap<>();
     public DataImporter() {
+    }
     public DataImporter(SQLTable table) {
     public void setTable(SQLTable table) {
         this.table = table;
+    }
-    public static void main(String[] args) throws Exception {
-        System.setProperty(SQLBase.STRUCTURE_USE_XML, "true");
-        final ComptaPropsConfiguration conf = ComptaPropsConfiguration.create();
-        Configuration.setInstance(conf);
-        try {
-            conf.getBase();
-        } catch (Exception e) {
-            e.printStackTrace();
-        }
-        final ComptaPropsConfiguration comptaPropsConfiguration = ((ComptaPropsConfiguration) Configuration.getInstance());
-        comptaPropsConfiguration.setUpSocieteDataBaseConnexion(39);
-        UserManager.getInstance().setCurrentUser(2);
-        SQLTable table = Configuration.getInstance().getRoot().findTable("ARTICLE");
-        DataImporter importer = new DataImporter(table);
-        importer.skipFirstLine = false;
-        importer.map(0, table.getField("CODE"));
-        importer.map(8, table.getField("ID_FOURNISSEUR"));
-        importer.map(4, table.getField("NOM"));
-        importer.addContraint(0, new NotEmptyConstraint());
-        importer.addUniqueField(table.getField("CODE"));
-        // ArrayTableModel m = importer.createModelFromODS(new File("c:/products-en.ods"));
-        // ArrayTableModel m = importer.createModelFromCSV(new File("c:/products-en.csv"));
-        // ArrayTableModel m = importer.createModelFromCSV(new File("c:/products-en.scsv.csv"));
-        ArrayTableModel m = importer.createModelFromXLS(new File("c:/products-en.xls"), 0);
-        m.dump(0, 4);
-        m = importer.createConvertedModel(m);
-        System.out.println("Dump");
-        m.dump(0, 4);
-        importer.importFromModel(m);
-        System.out.println(importer.getValuesToInsert().size() + " rows to insert");
-        System.out.println(importer.getValuesToUpdate().size() + " rows to update");
-        // importer.commit();
-    }
     public void commit() throws SQLException {
         for (SQLRowValues row : this.valuesToInsert) {
             row.getGraph().store(StoreMode.INSERT, false);
+        }
         for (SQLRowValues row : this.valuesToUpdate) {
+        }
         doAfterImport();
+    }
     public List<SQLRowValues> getValuesToInsert() {
-        return valuesToInsert;
+        return this.valuesToInsert;
+    }
     public List<SQLRowValues> getValuesToUpdate() {
-        return valuesToUpdate;
+        return this.valuesToUpdate;
+    }
     public void addUniqueField(SQLField field) {
         if (this.uniqueField.contains(field)) {
             throw new IllegalStateException("Field " + field + " already specified");
         this.uniqueField.add(field);
+    }
     public void addContraint(int columnIndex, Constraint c) {
-        constraints.put(Integer.valueOf(columnIndex), c);
+        this.constraints.put(Integer.valueOf(columnIndex), c);
+    }
     public void map(int columnIndex, SQLField field) {
         map(columnIndex, field, new ValueConverter(field));
+    }
+    }
     public void map(int columnIndex, SQLField field, SQLField foreignField) {
         final ValueConverter converter = new ValueConverter(foreignField);
         map(columnIndex, foreignField, converter);
-        foreignMap.put(converter, field);
+        this.foreignMap.put(converter, field);
+    }
     public void map(int columnIndex, SQLField field, ValueConverter converter) {
         final Integer value = Integer.valueOf(columnIndex);
-        map.put(value, converter);
+        this.map.put(value, converter);
-        List<Integer> l = fieldMap.get(field);
+        List<Integer> l = this.fieldMap.get(field);
         if (l == null) {
-            l = new ArrayList<Integer>();
+            l = new ArrayList<>();
-            fieldMap.put(field, l);
+            this.fieldMap.put(field, l);
         } else if (!field.getType().getJavaType().equals(String.class)) {
             throw new IllegalArgumentException("Mapping multiple column is only supoprted for String values");
+        }
         if (l.contains(value)) {
             throw new IllegalArgumentException("Column " + columnIndex + " already mapped for field " + field.getFullName());
+                }
                 columnCount++;
+            }
+        }
         int start = 0;
-        if (skipFirstLine) {
+        if (this.skipFirstLine) {
             start = 1;
+        }
-        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
+        final List<List<Object>> rows = new ArrayList<>(rowCount - start);
         for (int i = start; i < rowCount; i++) {
-            List<Object> row = new ArrayList<Object>();
+            List<Object> row = new ArrayList<>();
             for (int j = 0; j < columnCount; j++) {
                 row.add(sheet.getValueAt(j, i));
+            }
             rows.add(row);
+        }
         return new ArrayTableModel(rows);
+    }
+    public ArrayTableModel createModelFromXLSXGM(File xlsFile, int sheetNumber) throws IOException {
+        XLSXDocument doc = XLSXDocument.createFromFile(xlsFile);
+        XLSXSheet sheet = doc.getSheet(sheetNumber);
+        final int rowCount = sheet.getRowCount();
+        final int columnCount = sheet.getColumnCount();
+        int start = 0;
+        if (this.skipFirstLine) {
+            start = 1;
+        }
+        final List<List<Object>> selectedRows = new ArrayList<>(rowCount - start);
+        for (int i = start; i < rowCount; i++) {
+            List<Object> row = new ArrayList<>();
+            for (int j = 0; j < columnCount; j++) {
+                row.add(sheet.getValueAt(j, i));
+            }
+            selectedRows.add(row);
+        }
+        return new ArrayTableModel(selectedRows);
+    }
     public ArrayTableModel createModelFromXLS(File xlsFile, int sheetNumber) throws IOException {
         final InputStream inputStream = new FileInputStream(xlsFile);
         final POIFSFileSystem fileSystem = new POIFSFileSystem(new BufferedInputStream(inputStream));
         final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
         final HSSFSheet sheet = workBook.getSheetAt(sheetNumber);
+        final List<List<Object>> rows = createFromExcel(workBook.getCreationHelper().createFormulaEvaluator(), sheet.rowIterator(), sheet.rowIterator());
+        workBook.close();
-        Iterator<Row> rowsIterator = sheet.rowIterator();
+        return new ArrayTableModel(rows);
+    }
+    private List<List<Object>> createFromExcel(FormulaEvaluator evaluator, Iterator<Row> rowsIterator1, Iterator<Row> rowsIterator2) {
         int columnCount = 0;
         int rowCount = 0;
-        while (rowsIterator.hasNext()) {
+        while (rowsIterator1.hasNext()) {
-            Row row = rowsIterator.next();
+            Row row = rowsIterator1.next();
             int i = row.getPhysicalNumberOfCells();
             if (i > columnCount) {
                 columnCount = i;
+            }
             rowCount++;
+        }
         // Extract data
-        rowsIterator = sheet.rowIterator();
         int start = 0;
-        if (skipFirstLine) {
+        if (this.skipFirstLine) {
             start = 1;
-            rowsIterator.next();
+            rowsIterator2.next();
+        }
-        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
+        final List<List<Object>> rows = new ArrayList<>(rowCount - start);
-        FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();
-        while (rowsIterator.hasNext()) {
+        while (rowsIterator2.hasNext()) {
-            final Row row = rowsIterator.next();
+            final Row row = rowsIterator2.next();
-            final List<Object> rowData = new ArrayList<Object>();
+            final List<Object> rowData = new ArrayList<>();
             for (int i = 0; i < columnCount; i++) {
                 final Cell cell = row.getCell(i);
                 if (cell == null) {
                     rowData.add("");
                 } else {
+                    CellValue cellValue = null;
+                    try {
-                    CellValue cellValue = evaluator.evaluate(cell);
+                        cellValue = evaluator.evaluate(cell);
+                    } catch (NotImplementedException exception) {
+                        exception.printStackTrace();
+                    }
                     if (cellValue == null) {
                         rowData.add("");
                     } else {
-                        switch (cellValue.getCellType()) {
+                        switch (cellValue.getCellTypeEnum()) {
-                        case Cell.CELL_TYPE_BOOLEAN:
+                        case BOOLEAN:
                             rowData.add(Boolean.valueOf(cellValue.getBooleanValue()));
                             break;
-                        case Cell.CELL_TYPE_NUMERIC:
+                        case NUMERIC:
                             if (HSSFDateUtil.isCellDateFormatted(cell)) {
-                                System.out.println("Row No.: " + row.getRowNum() + " " + cell.getDateCellValue());
                                 rowData.add(cell.getDateCellValue());
                             } else {
                                 rowData.add(Double.valueOf(cellValue.getNumberValue()));
+                            }
                             break;
-                        case Cell.CELL_TYPE_STRING:
+                        case STRING:
                             rowData.add(cellValue.getStringValue());
                             break;
-                        case Cell.CELL_TYPE_FORMULA:
+                        case FORMULA:
                             rowData.add(cell.getCellFormula());
                             break;
-                        case Cell.CELL_TYPE_BLANK:
+                        case BLANK:
                             rowData.add("");
                             break;
                         default:
                             rowData.add(cellValue.getStringValue());
                             break;
+            }
             rows.add(rowData);
+        }
-        inputStream.close();
+        return rows;
-        return new ArrayTableModel(rows);
+    }
     public ArrayTableModel createModelFromCSV(File csvFile) throws IOException {
         Charset cs = CharsetHelper.guessEncoding(csvFile, 4096, Charset.forName("Cp1252"));
         List<String[]> lines = csvReader.readAll();
         final int rowCount = lines.size();
         final int columnCount = lines.get(0).length;
         int start = 0;
-        if (skipFirstLine) {
+        if (this.skipFirstLine) {
             start = 1;
+        }
-        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
+        final List<List<Object>> rows = new ArrayList<>(rowCount - start);
         for (int i = start; i < rowCount; i++) {
-            List<Object> row = new ArrayList<Object>();
+            List<Object> row = new ArrayList<>();
             String[] values = lines.get(i);
             for (int j = 0; j < columnCount; j++) {
                 row.add(values[j]);
+            }
             rows.add(row);
         csvReader.close();
+    }
     public ArrayTableModel createConvertedModel(ArrayTableModel model) {
         final int rowCount = model.getRowCount();
-        final ArrayList<Integer> colsUsed = new ArrayList<Integer>(map.keySet());
+        final ArrayList<Integer> colsUsed = new ArrayList<>(this.map.keySet());
-        colsUsed.addAll(constraints.keySet());
+        colsUsed.addAll(this.constraints.keySet());
         final int columnCount = 1 + Collections.max(colsUsed);
-        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount);
+        final List<List<Object>> rows = new ArrayList<>(rowCount);
         for (int i = 0; i < rowCount; i++) {
             boolean validRow = true;
-            final List<Object> row = new ArrayList<Object>();
+            final List<Object> row = new ArrayList<>();
             for (int j = 0; j < columnCount; j++) {
                 Object value = model.getValueAt(i, j);
-                ValueConverter converter = map.get(j);
+                ValueConverter converter = this.map.get(j);
                 if (converter != null) {
                     value = converter.convertFrom(value);
+                }
-                final Constraint constraint = constraints.get(j);
+                final Constraint constraint = this.constraints.get(j);
                 // Verification de la validité de la valeur à importer
                 if (constraint != null && !constraint.isValid(value)) {
                     validRow = false;
                     break;
+                }
         return new ArrayTableModel(rows);
+    }
     protected void customizeRowValuesToFetch(SQLRowValues vals) {
+        // Nothing
+    }
     public void importFromModel(ArrayTableModel model) throws IOException {
         final int rowCount = model.getRowCount();
         // Load existing data for duplication check
-        final SQLRowValues vals = new SQLRowValues(table);
+        final SQLRowValues vals = new SQLRowValues(this.table);
         for (SQLField field : this.fieldMap.keySet()) {
-            if (field.getTable().equals(table)) {
+            if (field.getTable().equals(this.table)) {
                 vals.put(field.getName(), null);
             } else {
-                final Set<SQLField> foreignKeys = table.getForeignKeys(field.getTable());
+                final Set<SQLField> foreignKeys = this.table.getForeignKeys(field.getTable());
                 for (SQLField sqlField : foreignKeys) {
                     vals.put(sqlField.getName(), null);
+                }
+            }
+        }
         customizeRowValuesToFetch(vals);
-        System.out.println("Fetching values");
         SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(vals);
         List<SQLRowValues> existingRows = fetcher.fetch();
-        System.out.println("Computing cache");
         final int existingRowsCount = existingRows.size();
-        final ValueConverter[] converters = map.values().toArray(new ValueConverter[map.size()]);
+        final ValueConverter[] converters = this.map.values().toArray(new ValueConverter[this.map.size()]);
         // Une map <Object(valeur),SQLRowValues> pour chaque champs unique
-        Map<SQLField, Map<Object, SQLRowValues>> cache = new HashMap<SQLField, Map<Object, SQLRowValues>>();
+        Map<SQLField, Map<Object, SQLRowValues>> cache = new HashMap<>();
         for (SQLField field : this.uniqueField) {
-            Map<Object, SQLRowValues> m = new HashMap<Object, SQLRowValues>();
+            Map<Object, SQLRowValues> m = new HashMap<>();
             cache.put(field, m);
             final String fieldName = field.getName();
             for (int j = 0; j < existingRowsCount; j++) {
                 SQLRowValues row = existingRows.get(j);
                 m.put(row.getObject(fieldName), row);
         for (int i = 0; i < rowCount; i++) {
             // Recherche d'existant
             SQLRowValues existingRow = null;
             for (SQLField field : this.uniqueField) {
-                List<Integer> cols = fieldMap.get(field);
+                List<Integer> cols = this.fieldMap.get(field);
                 Object objectToInsert = null;
                 for (Integer col : cols) {
                     Object v = model.getValueAt(i, col);
                     if (objectToInsert == null) {
                         objectToInsert = v;
+    }
     private void updateOrInsert(ArrayTableModel model, final ValueConverter[] converters, int i, SQLRowValues existingRow) {
-        final Map<String, Object> newValues = new HashMap<String, Object>();
+        final Map<String, Object> newValues = new HashMap<>();
         if (existingRow != null) {
             // Préremplissage de la map avec la row existante
             newValues.putAll(existingRow.getAbsolutelyAll());
+        }
         for (int j = 0; j < converters.length; j++) {
             ValueConverter valueConverter = converters[j];
-            List<Integer> cols = fieldMap.get(valueConverter.getField());
+            List<Integer> cols = this.fieldMap.get(valueConverter.getField());
             Object objectToInsert = null;
             for (Integer col : cols) {
                 Object v = model.getValueAt(i, col);
                 if (objectToInsert == null) {
                     objectToInsert = v;
+            }
             final String fieldName = valueConverter.getFieldName();
             if (objectToInsert != null || !valueConverter.isIgnoringEmptyValue()) {
-                if (valueConverter.getField().getTable().equals(table)) {
+                if (valueConverter.getField().getTable().equals(this.table)) {
                     newValues.put(fieldName, objectToInsert);
                 } else {
-                    final SQLField sqlField = foreignMap.get(valueConverter);
+                    final SQLField sqlField = this.foreignMap.get(valueConverter);
                     final Object value = newValues.get(sqlField.getName());
                     if (value == null || value instanceof SQLRowValues) {
                         SQLRowValues fRowValues = (SQLRowValues) value;
                         if (fRowValues == null) {
+                    }
+                }
+            }
+        }
-        final SQLRowValues rowVals = new SQLRowValues(table, newValues);
+        final SQLRowValues rowVals = new SQLRowValues(this.table, newValues);
         patchRowValues(rowVals, model.getLineValuesAt(i), existingRow);
         if (existingRow == null) {
             this.valuesToInsert.add(rowVals);
-        }
-        // else if (!newValues.equals(existingRow.getAbsolutelyAll())) {
-        else {
+        } else {
             this.valuesToUpdate.add(rowVals);
-            // for (SQLRowValues ref : rowVals.getReferentRows()) {
-            // this.valuesToUpdate.add(ref);
-            // }
+        }
+    }
     public void doAfterImport() throws SQLException {
+        // Nothing
+    }
     protected void patchRowValues(SQLRowValues rowVals, List<Object> lineValues, SQLRowValues existingRow) {
+        // Nothing
+    }
     public void setSkipFirstLine(boolean skipFirstLine) {
         this.skipFirstLine = skipFirstLine;
+    }
             return createModelFromODS(file, sheetNumber);
         } else if (name.endsWith(".csv")) {
             return createModelFromCSV(file);
         } else if (name.endsWith(".xls")) {
             return createModelFromXLS(file, sheetNumber);
+        } else if (name.endsWith(".xlsx") || name.endsWith(".xlsm")) {
+            return createModelFromXLSXGM(file, sheetNumber);
+        }
-        throw new IllegalArgumentException("File format not supported. Please provide an ods, csv or xls file.");
+        throw new IllegalArgumentException("File format not supported. Please provide an ods, csv, xls or xlsx file.");
+    }
+}

Dépôt officiel du code source de l'ERP OpenConcerto

svn://code.openconcerto.org/openconcerto

(root)/trunk/OpenConcerto/src/org/openconcerto/erp/importer/DataImporter.java - Rev 156 → 174