Example usage for org.apache.poi.ss.usermodel Cell getColumnIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getColumnIndex

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getColumnIndex.

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

From source file:output.ExcelM3Upgrad.java

private void recalculate() {
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
        Sheet sheet = workbook.getSheetAt(sheetNum);
        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    evaluator.evaluateFormulaCell(c);
                }/*from   w  ww . j ava  2 s.  c om*/
                sheet.autoSizeColumn(c.getColumnIndex());
            }
        }
    }

}

From source file:output.ExcelM3Upgrad.java

private void writeMigration() {
    Sheet sheet = workbook.getSheetAt(0);
    workbook.setSheetName(0, "Migration");
    sheet.setDisplayGridlines(false);/*  w ww . j  a  va  2  s. c o m*/
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    styles = createStyles(workbook);

    int rownum = beginROW;
    int cellnum = beginCOL;
    Row row = sheet.createRow(rownum++);
    for (int k = 0; k < model.getListColumn().length; k++) {
        Cell cell = row.createCell(cellnum++);
        cell.setCellValue(i18n.Language.getLabel(model.getListColumn()[k].getIdLng()));
        cell.setCellStyle(styles.get("header"));
        sheet.setColumnHidden(cell.getColumnIndex(), model.getListColumn()[k].isHidden());
        sheet.autoSizeColumn(k);
        dialStatus();
    }
    ArrayList<Integer> listHeader = new ArrayList<>();
    for (int i = 0; i < M3UpdObjModel.header.length; i++) {
        listHeader.add(M3UpdObjModel.header[i]);
    }

    String[] listLevel = i18n.Language.traduce(Ressource.listLevel)
            .toArray(new String[Ressource.listLevel.length]);

    data = model.getData();
    for (int i = 0; i < data.length; i++) {
        busyDial.setText("Alimentation de la ligne " + (i + 1) + " sur " + data.length);
        row = sheet.createRow(rownum++);
        Object[] objArr = data[i];
        cellnum = beginCOL;
        boolean first = true;
        int j = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof Date) {
                cell.setCellValue((Date) obj);
            } else if (obj instanceof Boolean) {
                if (first) {
                    first = false;
                    if ((Boolean) obj) {
                        cell.setCellValue("Oui");
                    } else {
                        cell.setCellValue("Non");
                    }
                } else {
                    if ((Boolean) obj) {
                        cell.setCellValue("OK");
                    } else {
                        cell.setCellValue("KO");
                    }
                }
            } else if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Double) {
                cell.setCellValue((Double) obj);
            }
            if (listHeader.indexOf(218) == j) {
                try {
                    int n = Integer.parseInt(obj.toString().trim());
                    if (n == -1) {
                        cell.setCellValue("ERROR");
                    } else {
                        cell.setCellValue(listLevel[n]);
                    }
                } catch (NumberFormatException ex) {
                    cell.setCellValue("");
                }

            }

            if (j < objArr.length - 3) {
                cell.setCellStyle(styles.get("cell_b_centered_locked"));
            } else {
                cell.setCellStyle(styles.get("cell_b_centered"));
            }
            j++;
            dialStatus();
        }
        dialStatus();
    }

    dialStatus();
    busyDial.setText("Formatage du document");
    CellRangeAddressList userList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 1, beginCOL + data[0].length - 1);
    DataValidationConstraint userConstraint;
    DataValidation userValidation;

    if (type == 0) {
        userConstraint = DVConstraint.createExplicitListConstraint((String[]) model.getM3UserModel()
                .getListUserSelect().toArray(new String[model.getM3UserModel().getListUserSelect().size()]));
        userValidation = new HSSFDataValidation(userList, userConstraint);
    } else {
        XSSFDataValidationHelper userHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        userConstraint = (XSSFDataValidationConstraint) userHelper
                .createExplicitListConstraint((String[]) model.getM3UserModel().getListUserSelect()
                        .toArray(new String[model.getM3UserModel().getListUserSelect().size()]));
        userValidation = (XSSFDataValidation) userHelper.createValidation(userConstraint, userList);
    }
    sheet.addValidationData(userValidation);

    CellRangeAddressList migList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 2, beginCOL + data[0].length - 2);
    DataValidationConstraint migConstraint;
    DataValidation migValidation;

    if (type == 0) {
        migConstraint = DVConstraint.createExplicitListConstraint(new String[] { "OK", "KO" });
        migValidation = new HSSFDataValidation(migList, migConstraint);
    } else {
        XSSFDataValidationHelper migHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        migConstraint = (XSSFDataValidationConstraint) migHelper
                .createExplicitListConstraint(new String[] { "OK", "KO" });
        migValidation = (XSSFDataValidation) migHelper.createValidation(migConstraint, migList);
    }
    sheet.addValidationData(migValidation);

    CellRangeAddressList levelList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 3, beginCOL + data[0].length - 3);
    DataValidationConstraint levelConstraint;
    DataValidation levelValidation;

    ArrayList<String> listNameLevel = new ArrayList<>();
    listNameLevel.add("ERROR");
    listNameLevel.addAll(i18n.Language.traduce(Ressource.listLevel));//.toArray(new String[Ressource.listLevel.length])
    if (type == 0) {
        levelConstraint = DVConstraint
                .createExplicitListConstraint(listNameLevel.toArray(new String[listNameLevel.size()]));
        levelValidation = new HSSFDataValidation(levelList, levelConstraint);
    } else {
        XSSFDataValidationHelper levelHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        levelConstraint = (XSSFDataValidationConstraint) levelHelper.createExplicitListConstraint(
                i18n.Language.traduce(Ressource.listLevel).toArray(new String[Ressource.listLevel.length]));
        levelValidation = (XSSFDataValidation) levelHelper.createValidation(levelConstraint, levelList);
    }
    sheet.addValidationData(levelValidation);

    int irow = beginROW;
    int icol = beginCOL + model.getListColumn().length + 2;
    row = sheet.getRow(irow);
    Cell cell = row.createCell(icol);
    sheet.addMergedRegion(new CellRangeAddress(irow, irow, icol, icol + 1));
    cell.setCellValue("Estimation de la charge");
    cell.setCellStyle(styles.get("header"));

    irow++;
    row = sheet.getRow(irow);

    int cpt = 0;
    ArrayList<String> listStringLevel = i18n.Language.traduce(Ressource.listLevel);
    for (String s : listStringLevel) {
        cell = row.createCell(icol);
        cell.setCellValue(s);
        cell.setCellStyle(styles.get("cell_b_centered_locked"));
        cell = row.createCell(icol + 1);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(218) + beginCOL);
        cell.setCellFormula(
                "COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter
                        + (beginROW + data.length + 1) + ",\"" + s + "\")*" + Ressource.listWeightLevel[cpt]);
        cell.setCellStyle(styles.get("cell_b_centered_locked"));
        irow++;
        row = sheet.getRow(irow);
        cpt++;
    }
    row = sheet.getRow(irow);
    cell = row.createCell(icol);
    cell.setCellValue("Total des charges");
    cell.setCellStyle(styles.get("cell_b_centered_locked"));
    cell = row.createCell(icol + 1);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(icol + 1));
    cell.setCellFormula("SUM(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
            + columnLetter + (beginROW + Ressource.listLevel.length + 1) + ")");
    cell.setCellStyle(styles.get("cell_b_centered_locked"));

    for (int k = 0; k < model.getListColumn().length + 3; k++) {
        sheet.autoSizeColumn(k);
    }

    sheet.protectSheet("3kles2014");
}

From source file:pl.exsio.ck.model.reader.XlsxEntryReaderImpl.java

License:Open Source License

@Override
public Collection<Entry> readEntries(File file, String progressName, boolean serialsOnly) {
    ProgressPresenter progress = ProgressHelper.showProgressBar(progressName, false);
    Row currentRow = null;//from   ww  w.  java 2s .co  m
    Cell currentCell = null;
    ArrayList<Entry> entries = new ArrayList<>();
    try {
        XSSFSheet sheet = this.openSheet(file);

        Iterator<Row> rowIterator = sheet.iterator();
        int totalRowCount = sheet.getPhysicalNumberOfRows() - 1;
        int rowCounter = 0;
        while (rowIterator.hasNext()) {
            ProgressHelper.updateProgressBar(progress, (int) (rowCounter * 100 / totalRowCount));
            currentRow = rowIterator.next();
            if (currentRow.getRowNum() > 0) {
                Entry e = new EntryImpl();
                Iterator<Cell> cellIterator = currentRow.cellIterator();
                while (cellIterator.hasNext()) {
                    currentCell = cellIterator.next();
                    if (!this.fillEntryField(currentCell, e, serialsOnly)) {
                        break;
                    }
                }
                if (e.getSerialNo() != null) {
                    entries.add(e);
                }
            }
            rowCounter++;
        }
    } catch (IOException ex) {
        this.log.log("nieudana prba otwarcia pliku " + file.getAbsolutePath());
        this.log.log(ExceptionUtils.getMessage(ex));
    } catch (ParseException ex) {
        this.log.log("nieprawidowy format daty w komrce " + currentRow.getRowNum()
                + CellReference.convertNumToColString(currentCell.getColumnIndex())
                + ". Akceptowalny format to 'yyyy-mm-dd'");
        this.log.log(ExceptionUtils.getMessage(ex));
    }
    System.gc();
    ProgressHelper.hideProgressBar(progress);
    return entries;
}

From source file:pl.exsio.ck.model.reader.XlsxEntryReaderImpl.java

License:Open Source License

private boolean fillEntryField(Cell currentCell, Entry e, boolean serialsOnly) throws ParseException {
    String value = this.getStringValue(currentCell);
    if (value != null && !value.equals("")) {
        if (serialsOnly) {
            if (currentCell.getColumnIndex() == 0) {
                e.setSerialNo(value);//from  www.  j  a va 2 s.c  o m
            }
        } else {
            switch (currentCell.getColumnIndex()) {
            case 0:
                e.setSerialNo(value);
                break;
            case 1:
                e.setSupplier(value);
                break;
            case 2:
                e.setSupplyDate(sdf.parse(value));
                break;
            case 3:
                e.setBuyInvoiceNo(value);
                break;
            case 4:
                e.setRecipient(value);
                break;
            case 5:
                e.setSellDate(sdf.parse(value));
                break;
            case 6:
                e.setSellInvoiceNo(value);
                break;
            default:
                break;
            }
        }

        return true;
    } else {
        return false;
    }

}

From source file:plugins.excel.client.util.ExcelReader.java

License:Microsoft Reference Source License

private Object getCellValue(Cell c, int targetType) {
    int cellType = c.getCellType();
    Object val;

    try {/*www.  ja  v a2 s  .c o m*/
        switch (cellType) {
        case (Cell.CELL_TYPE_STRING):
        case (Cell.CELL_TYPE_FORMULA):
            val = c.getStringCellValue();

            switch (targetType) {
            case Types.BOOLEAN:
                return Boolean.parseBoolean((String) val);
            case Types.DOUBLE:
                return Double.parseDouble((String) val);
            case Types.INTEGER:
                return Integer.parseInt((String) val);
            case Types.VARCHAR:
                return (String) val;
            case Types.DATE:
                SimpleDateFormat sdf = new SimpleDateFormat();
                try {
                    return sdf.parse((String) val);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            }
            break;
        case (Cell.CELL_TYPE_NUMERIC):
            if (DateUtil.isCellDateFormatted(c)) {
                val = c.getDateCellValue();

                switch (targetType) {
                case Types.BOOLEAN:
                    return (((Date) val).getTime() > 0);
                case Types.DOUBLE:
                    return (double) ((Date) val).getTime();
                case Types.INTEGER:
                    return ((Date) val).getTime();
                case Types.VARCHAR:
                    DateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss");
                    return df.format((Date) val);
                case Types.DATE:
                    return (Date) val;
                }
            } else {
                val = c.getNumericCellValue();

                switch (targetType) {
                case Types.BOOLEAN:
                    return ((double) val > 0.0);
                case Types.DOUBLE:
                    return (double) val;
                case Types.INTEGER:
                    return (long) val;
                case Types.VARCHAR:
                    return new Double((double) val).toString();
                case Types.DATE:
                    Date d = new Date();
                    d.setTime((long) val);
                    return d;
                }
            }
            break;
        case (Cell.CELL_TYPE_ERROR):
            val = c.getErrorCellValue();

            switch (targetType) {
            case Types.BOOLEAN:
                return ((int) val > 0);
            case Types.DOUBLE:
                return (double) val;
            case Types.INTEGER:
                return (int) val;
            case Types.VARCHAR:
                return new Integer((int) val).toString();
            case Types.DATE:
                Date d = new Date();
                d.setTime((long) val);
                return d;
            }
            break;
        case (Cell.CELL_TYPE_BOOLEAN):
            val = c.getBooleanCellValue();

            switch (targetType) {
            case Types.BOOLEAN:
                return (boolean) val;
            case Types.DOUBLE:
                return (double) (((boolean) val ? 1 : 0));
            case Types.INTEGER:
                return (int) (((boolean) val ? 1 : 0));
            case Types.VARCHAR:
                return new Boolean((boolean) val).toString();
            case Types.DATE:
                Date d = new Date();
                d.setTime((long) (((boolean) val ? 1 : 0)));
                return d;
            }
            break;
        }
    } catch (IllegalStateException e) {
        Dialog.msgBox(
                "Could not import cell r:" + c.getRowIndex() + " c: " + c.getColumnIndex()
                        + " because of data type errors in the sheet",
                "Import Excel File", Dialog.ERROR_MESSAGE);
    }
    return null;
}

From source file:ro.dabuno.office.integration.Xlsx2Word.java

public static void main(String[] args) throws Exception {
    log.info("starting app");
    //        Workbook wb = new XSSFWorkbook(new FileInputStream(args[0]));
    Workbook wb = new XSSFWorkbook(new FileInputStream("office-files/Input.xlsx"));

    DataFormatter formatter = new DataFormatter();

    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);/*www  .j av a  2s  .c  o  m*/
        System.out.println(wb.getSheetName(i));
        int j = 4;
        for (Row row : sheet) {
            System.out.println("rownum: " + row.getRowNum());
            for (Cell cell : row) {
                CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                System.out.print(cellRef.formatAsString());
                System.out.print(" - ");
                // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
                String text = formatter.formatCellValue(cell);
                System.out.println(text);

                System.out.println("------------");
                // Alternatively, get the value and format it yourself
                switch (cell.getCellTypeEnum()) {
                case STRING:
                    System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.println(cell.getDateCellValue());
                    } else {
                        System.out.print(cellRef.formatAsString());
                        System.out.print(" - ");
                        System.out.println((long) cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    System.out.println(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    System.out.println(cell.getCellFormula());
                    break;
                case BLANK:
                    System.out.println();
                    break;
                default:
                    System.out.println();
                }

            }
            j--;
            if (j == 0) {
                break;
            }
        }
    }

    XWPFDocument doc = new XWPFDocument();

    XWPFParagraph p0 = doc.createParagraph();
    XWPFRun r0 = p0.createRun();
    r0.setBold(false);
    r0.setText("Domnule");
    XWPFRun r00 = p0.createRun();
    r00.setBold(true);
    r00.setText(" Ionescu Ion");

    FileOutputStream out = new FileOutputStream("out/xlsx2word.docx");
    doc.write(out);
    out.close();
}

From source file:rpt.GUI.ProgramManager.Variants.VariantsController.java

public void importButtonClicked(ActionEvent event) throws IOException {
    //List with all variants read from the imported Excel file
    List<Variant> variants = new ArrayList();

    //Create File Chooser window
    FileChooser fileChooser = new FileChooser();
    fileChooser.setTitle("Import Excel File");

    //Set filter to allow only Excel files
    ExtensionFilter filter = new ExtensionFilter("Excel Files", "*.xls", "*.xlsx");
    fileChooser.getExtensionFilters().addAll(filter);

    //Show File Selector
    File selectedFile = fileChooser.showOpenDialog(null);

    //import Excel file if a file has been selected, if not, do nothing
    //based on good example on:
    //http://www.codejava.net/coding/how-to-read-excel-files-in-java-using-apache-poi
    if (selectedFile != null) {
        System.out.println("File selected: " + selectedFile.getPath());
        FileInputStream inputStream = new FileInputStream(new File(selectedFile.getPath()));
        Workbook workbook = new HSSFWorkbook(inputStream);

        //Use Sheet iterator to extract all sheet names
        Iterator<Sheet> sheetIterator = workbook.sheetIterator();

        //Iterate over all sheets and populate a checkboxfield and let user select on of the sheets
        while (sheetIterator.hasNext()) {
            Sheet nextSheet = sheetIterator.next();
            //TODO
            //Create dialogbox with a radio button selection showing all the available sheets.
            //Clicking on cancel has to break the main if or protect the rest with an if that Cancel wasnt clicked
        }/*w  w w.java  2s.  c o  m*/

        //set sheet to the sheet selected
        Sheet firstSheet = workbook.getSheet("Raw data CP2016A"); //Change to variable name at later date
        Iterator<Row> rowIterator = firstSheet.iterator();

        //find first row
        //TODO
        //set first row keyword into application settings
        Boolean firstRowFound = false;
        Cell nextCell;
        Iterator<Cell> cellIterator;
        do {
            Row nextRow = rowIterator.next();
            cellIterator = nextRow.cellIterator();
            nextCell = cellIterator.next();
            if (getCellValue(nextCell) != null) {//blank cells return null
                if (getCellValue(nextCell).equals("Plant")) {
                    firstRowFound = true;
                }
            }

        } while (!firstRowFound && rowIterator.hasNext());

        //First row is now found, loop through entire row and build a
        //dictionary using HashMaps
        Map<Integer, String> dictionary = new HashMap<Integer, String>();
        while (cellIterator.hasNext()) {
            if (getCellValue(nextCell) != null) { //blank cells return null
                dictionary.put(nextCell.getColumnIndex(), (String) getCellValue(nextCell));
            }
            nextCell = cellIterator.next();
        }

        //loop through all rows in the file
        while (rowIterator.hasNext()) {
            Row nextRow = rowIterator.next();
            cellIterator = nextRow.cellIterator();
            Variant aVariant = new Variant();

            //loop through all columns in the row
            while (cellIterator.hasNext()) {
                nextCell = cellIterator.next();
                int columnIndex = nextCell.getColumnIndex();
                if (getCellValue(nextCell) != null) {
                    aVariant.setValue(dictionary.get(nextCell.getColumnIndex()), getCellValue(nextCell));
                    //                        switch (columnIndex) {
                    //                            case 0:
                    //                                aVariant.setPlant((String) getCellValue(nextCell));
                    //                                break;
                    //                            case 1:
                    //                                Double value = ((Double) getCellValue(nextCell));
                    //                                aVariant.setPlantCode(value.intValue());
                    //                                break;
                    //                            case 2:
                    //                                aVariant.setPlantName((String) getCellValue(nextCell));
                    //                                break;
                    //                            default:
                    //                                break;      
                    //                        }
                } else {
                }

            }
            variants.add(aVariant);
        }
        inputStream.close();

        //now that we have a list of Variants we put them into the tableView
        data.clear();
        int index = 1;
        for (Variant variant : variants) {
            TableVariants entry = new TableVariants(index, variant.getEngineName(), variant.getDenomination(),
                    variant.getGearbox(), variant.getEmiss());
            add(entry);
            index++;
        }

    } //TODO remove the else, we don't do anything if the user presses Cancel
    else {
        System.out.println("File selection cancelled.");
    }
}

From source file:rpt.GUI.ProgramStrategist.CyclePlans.CompareDialogController.java

private int writeRow(Workbook wb, Sheet sheet, Row row, TableVariant variant,
        Map<String, Map<String, String>> diffList, Boolean colorChanges, Boolean addOldSOP) {
    //Used for placing comment at the right position
    CreationHelper factory = wb.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = factory.createClientAnchor();

    //Create new style
    XSSFCellStyle styleRed = (XSSFCellStyle) wb.createCellStyle();
    XSSFCellStyle styleBlack = (XSSFCellStyle) wb.createCellStyle();
    XSSFFont fontRed = (XSSFFont) wb.createFont();
    fontRed.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
    XSSFFont fontBlack = (XSSFFont) wb.createFont();
    fontBlack.setColor(new XSSFColor(new java.awt.Color(0, 0, 0)));
    styleRed.setFont(fontRed);/*w ww  . j a  va 2s  . c om*/
    styleBlack.setFont(fontBlack);

    //xEtract differences to highlight
    Map<String, String> differences;

    if (diffList != null) {
        differences = diffList.get(variant.getVariantID());
    } else {
        differences = new HashMap<String, String>();
    }

    //Start with column 0
    int cols = 0;

    //Create string with columns to print
    String[] columns = { "Plant", "Platform", "Vehicle", "Propulsion", "Denomination", "Fuel", "EngineFamily",
            "Generation", "EngineCode", "Displacement", "EnginePower", "ElMotorPower", "Torque",
            "TorqueOverBoost", "GearboxType", "Gears", "Gearbox", "Driveline", "TransmissionCode", "CertGroup",
            "EmissionClass", "StartOfProd", "EndOfProd" };

    Cell cell;

    for (int i = 0; i < columns.length; i++) {
        cell = row.createCell(i);

        if (differences.containsKey(columns[i])) {
            cell.setCellStyle(styleRed);

            // position the comment
            anchor.setCol1(cell.getColumnIndex());
            anchor.setCol2(cell.getColumnIndex() + 1);
            anchor.setRow1(row.getRowNum());
            anchor.setRow2(row.getRowNum() + 3);

            // Create the comment and set the text+author
            Comment comment = drawing.createCellComment(anchor);
            RichTextString str = factory.createRichTextString(differences.get(columns[i]));
            comment.setString(str);
            comment.setAuthor("RPT");

            // Assign the comment to the cell
            cell.setCellComment(comment);
        } else {
            cell.setCellStyle(styleBlack);
        }
        cell.setCellValue(variant.getValue(columns[i]));
        cols++;
    }

    if (addOldSOP) {
        cell = row.createCell(23);
        cell.setCellValue(variant.getOldSOP());
        cols++;
    }

    if (addOldSOP) {
        cell = row.createCell(24);
        cell.setCellValue(variant.getOldEOP());
        cols++;
    }

    return cols;
}

From source file:rpt.GUI.ProgramStrategist.CyclePlans.CyclePlansController.java

public void importButtonClicked(ActionEvent event) throws IOException {
    //List with all variants read from the imported Excel file
    List<TableVariant> variants = new ArrayList();

    //Create File Chooser window
    FileChooser fileChooser = new FileChooser();
    fileChooser.setTitle("Import Excel File");

    //Set filter to allow only Excel files
    ExtensionFilter filter = new ExtensionFilter("Excel Files", "*.xls", "*.xlsx");
    fileChooser.getExtensionFilters().addAll(filter);

    //Show File Selector
    File selectedFile = fileChooser.showOpenDialog(null);

    //import Excel file if a file has been selected, if not, do nothing
    //based on good example on:
    //http://www.codejava.net/coding/how-to-read-excel-files-in-java-using-apache-poi
    if (selectedFile != null) {
        //open dialog box and show available sheets in the file
        // the dialog box will then process the file and add data into the table
        FileInputStream inputStream = new FileInputStream(new File(selectedFile.getPath()));
        Workbook workbook;/*from ww w  . j a  v a  2  s  .c  o  m*/

        if (selectedFile.getPath().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            workbook = new HSSFWorkbook(inputStream);
        }

        //Use Sheet iterator to extract all sheet names
        Iterator<Sheet> sheetIterator = workbook.sheetIterator();

        //Iterate over all sheets and populate a checkboxfield and let user select on of the sheets
        sheetsInFile = new ArrayList(); //reset just in case
        while (sheetIterator.hasNext()) {
            String nextSheet = sheetIterator.next().getSheetName();
            sheetsInFile.add(nextSheet); //add found sheet into list of available sheets.
        }
        selectedSheet = null;
        //Show dialog box presenting all the available sheets for the user to select from
        Stage stage;
        Parent root;
        stage = new Stage();
        root = FXMLLoader
                .load(getClass().getResource("/rpt/GUI/ProgramStrategist/CyclePlans/dialogSelectSheet.fxml"));
        stage.setScene(new Scene(root));
        stage.setTitle("Select Sheet");
        stage.initModality(Modality.APPLICATION_MODAL);
        stage.showAndWait(); // pause until the user has selected a sheet

        // If user has selected sheet to read, show next dialog box allowing
        // user to set the name of the imported cycle plan
        if (selectedSheet != null) {
            //preset the file name to the file name part before the . sign
            importedCyclePlanName = selectedFile.getName().split("\\.")[0];

            //Create dialog
            stage = new Stage();
            root = FXMLLoader
                    .load(getClass().getResource("/rpt/GUI/ProgramStrategist/CyclePlans/dialogSetName.fxml"));
            stage.setScene(new Scene(root));
            stage.setTitle("Set Cycle Plan Name");
            stage.initModality(Modality.APPLICATION_MODAL);
            stage.showAndWait(); // pause until the user has selected a sheet
        }

        // only continue if a sheet was selected (=abort if used presses cancel)
        // AND if the cycle plan name is unique, i.e. not already imported
        if (selectedSheet != null && importedCyclePlanName != null) {
            // Add new cycleplan into Database
            try {
                Statement statement = RPT.conn.createStatement();
                statement.setQueryTimeout(30);
                query = "INSERT INTO CYCLEPLANS (Name, Version) VALUES (\'" + importedCyclePlanName + "\', 1)";
                statement.executeUpdate(query);

                //set sheet to the sheet selected
                Sheet firstSheet = workbook.getSheet(selectedSheet);
                Iterator<Row> rowIterator = firstSheet.iterator();

                //find first row
                //TODO
                //set first row keyword into application settings
                Boolean firstRowFound = false;
                Cell nextCell;
                Iterator<Cell> cellIterator;

                //dictionary using HashMaps
                Map<Integer, String> dictionary = new HashMap<Integer, String>();
                do {
                    Row nextRow = rowIterator.next();
                    cellIterator = nextRow.cellIterator();
                    nextCell = cellIterator.next();
                    if (getCellValue(nextCell) != null) {//blank cells return null
                        if (getCellValue(nextCell).equals("Plant")) {
                            //dictionary.put(nextCell.getColumnIndex(), (String) getCellValue(nextCell));
                            firstRowFound = true;
                        }
                    }

                } while (!firstRowFound && rowIterator.hasNext());

                //First row is now found, loop through entire row and build a
                while (cellIterator.hasNext()) {
                    if (getCellValue(nextCell) != null) { //blank cells return null
                        dictionary.put(nextCell.getColumnIndex(), getCellValue(nextCell).toString());
                    }
                    nextCell = cellIterator.next();
                }

                // Since Excel stores numbers as floats and as an example 1 will turn into 1.0 this will be a problem
                // Mainly because the cycle plan tends to mix text and numbers in Excel.
                // Create a Dataformatter which will be used to solve this
                DataFormatter fmt = new DataFormatter();
                //loop through all rows in the file
                while (rowIterator.hasNext()) {
                    Row nextRow = rowIterator.next();
                    cellIterator = nextRow.cellIterator();
                    TableVariant aVariant = new TableVariant();
                    //loop through all columns in the row
                    while (cellIterator.hasNext()) {
                        nextCell = cellIterator.next();
                        int columnIndex = nextCell.getColumnIndex();
                        if (getCellValue(nextCell) != null) {
                            aVariant.setValue(dictionary.get(nextCell.getColumnIndex()),
                                    fmt.formatCellValue(nextCell));
                            //aVariant.setValue(dictionary.get(nextCell.getColumnIndex()), getCellValue(nextCell).toString());
                        } else {
                        }

                    }
                    variants.add(aVariant);
                }
            } catch (Exception e) {
                System.err.println("CyclePlansController line 343: " + e.getMessage());
            }

            //remove current selection and add the new variants
            data.clear();
            int index = 1;
            for (TableVariant variant : variants) {
                TableVariant entry = new TableVariant(variant.getPlant(), variant.getPlatform(),
                        variant.getVehicle(), variant.getPropulsion(), variant.getDenomination(),
                        variant.getFuel(), variant.getEngineFamily(), variant.getGeneration(),
                        variant.getEngineName(), variant.getEngineCode(), variant.getDisplacement(),
                        variant.getEnginePower(), variant.getElMotorPower(), variant.getTorque(),
                        variant.getTorqueOverBoost(), variant.getGearboxType(), variant.getGears(),
                        variant.getGearbox(), variant.getDriveline(), variant.getTransmissionCode(),
                        variant.getCertGroup(), variant.getEmissionClass(), variant.getStartOfProd(),
                        variant.getEndOfProd());
                add(entry);
                index++;
                try {
                    Statement statement = RPT.conn.createStatement();
                    statement.setQueryTimeout(30);
                    String variantID = variant.getVehicle() + variant.getEngineCode()
                            + variant.getTransmissionCode() + variant.getEmissionClass()
                            + variant.getStartOfProd();
                    query = "SELECT COUNT(VariantID) FROM VARIANTS WHERE VariantID = '" + variantID + "'";
                    ResultSet rs = statement.executeQuery(query);

                    //check count of previous query, 0 = new variant, 1 = it already exists
                    Integer count = rs.getInt(1);
                    // add variant if it does not exist
                    if (count == 0) { // entry did not existbefore
                        query = "INSERT INTO VARIANTS ("
                                + "Plant, Platform, Vehicle, Propulsion, Denomination, Fuel, EngineFamily, Generation, EngineCode, Displacement, "
                                + "EnginePower, ElMotorPower, Torque, TorqueOverBoost, GearboxType, Gears, Gearbox, Driveline, TransmissionCode, "
                                + "CertGroup, EmissionClass, StartOfProd, EndOfProd, VariantID" + ")" + ""
                                + " VALUES (\'" + variant.getPlant() + "\', \'" + variant.getPlatform()
                                + "\', \'" + variant.getVehicle() + "\', \'" + variant.getPropulsion()
                                + "\', \'" + variant.getDenomination() + "\', \'" + variant.getFuel() + "\', \'"
                                + variant.getEngineFamily() + "\', \'" + variant.getGeneration() + "\', \'"
                                + variant.getEngineCode() + "\', \'" + variant.getDisplacement() + "\', \'"
                                + variant.getEnginePower() + "\', \'" + variant.getElMotorPower() + "\', \'"
                                + variant.getTorque() + "\', \'" + variant.getTorqueOverBoost() + "\', \'"
                                + variant.getGearboxType() + "\', \'" + variant.getGears() + "', '"
                                + variant.getGearbox() + "\', \'" + variant.getDriveline() + "\', \'"
                                + variant.getTransmissionCode() + "\', \'" + variant.getCertGroup() + "\', \'"
                                + variant.getEmissionClass() + "\', \'" + variant.getStartOfProd() + "\', \'"
                                + variant.getEndOfProd() + "\', \'" + variantID + "\')";
                        statement.executeUpdate(query);
                    }
                    // Add relation between cycle plan and variant
                    query = "INSERT INTO VariantBelongsToCyclePlan (VariantID, CyclePlanID) VALUES (\'"
                            + variantID + "\', \'" + importedCyclePlanName + "\')";
                    statement.executeUpdate(query);

                } catch (Exception e) {
                    System.out.println("Query: " + query);
                    System.err.println("CyclePlansController line 394: " + e.getMessage());
                }
            }
            cyclePlanList.add(importedCyclePlanName);
            cyclePlanSelector.getSelectionModel().select(importedCyclePlanName);
        } // end of reading file after user has selected sheet and name of cycle plan
        inputStream.close();

    }
}

From source file:ru.codemine.ccms.sales.domino.DominoSalesLoader.java

License:Open Source License

private Map<LocalDate, Map<String, Sales>> getSalesMap() {
    File path = new File(settingsService.getStorageEmailPath());
    FilenameFilter filter = new EndsWithFilenameFilter(".xls", EndsWithFilenameFilter.NEVER);
    Map<LocalDate, Map<String, Sales>> result = new HashMap();
    for (File file : path.listFiles(filter)) {
        try {/*from w  ww .j  av a  2 s .  co m*/

            log.info(" : " + file.getName());

            FileInputStream fs = new FileInputStream(file);
            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            HSSFSheet sheet = workbook.getSheetAt(0);
            boolean dateFound = false;
            boolean colFound = false;
            Integer colNumber = 0;
            LocalDate fileDate = null;
            Map<String, Sales> parsedFileMap = new HashMap();

            for (Row row : sheet) {
                Cell firstCell = row.getCell(0);

                //
                // ?  
                //
                if (firstCell.getCellType() == Cell.CELL_TYPE_STRING && firstCell.getStringCellValue()
                        .startsWith("    ")) {
                    String bothDatesStr = firstCell.getStringCellValue()
                            .replace("     ? ", "");
                    String[] datesStr = bothDatesStr.split("  ");
                    DateTimeFormatter formatter = DateTimeFormat.forPattern("dd.MM.YYYY");
                    LocalDate startDate = formatter.parseLocalDate(datesStr[0]);
                    LocalDate endDate = formatter.parseLocalDate(datesStr[1]);
                    if (startDate != null && startDate.isEqual(endDate)) {
                        dateFound = true;
                        fileDate = startDate;
                        log.info(" : " + fileDate);
                    }

                }

                //
                // ? ? ? ? ?
                //

                else if (firstCell.getCellType() == Cell.CELL_TYPE_STRING
                        && firstCell.getStringCellValue().startsWith(" /")) {
                    for (Cell headersCell : row) {
                        if (headersCell.getCellType() == Cell.CELL_TYPE_STRING && headersCell
                                .getStringCellValue().startsWith(" ??")) {
                            colFound = true;
                            colNumber = headersCell.getColumnIndex();
                            log.info(" : " + colNumber);
                        }
                    }
                }

                //
                // ?   ? 
                //

                else if (dateFound && colFound && firstCell.getCellType() == Cell.CELL_TYPE_STRING
                        && firstCell.getStringCellValue().startsWith(":")) {
                    Sales sale = new Sales();

                    // ?  
                    String namesStr = firstCell.getStringCellValue().replace(": ",
                            "");
                    String delimiter = " - ";
                    if (namesStr.indexOf(delimiter) > 0
                            && namesStr.indexOf(delimiter) != namesStr.lastIndexOf(delimiter)) {
                        String name = namesStr.substring(namesStr.indexOf(delimiter) + delimiter.length(),
                                namesStr.lastIndexOf(delimiter));

                        //  ? 
                        boolean shopFinished = false;
                        int rcrd = 1;
                        int cashb_rcrd = 0;
                        while (!shopFinished) {
                            Row r = sheet.getRow(row.getRowNum() + rcrd);
                            if (r.getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC) // ? ?   
                            {
                                Double val = r.getCell(colNumber).getNumericCellValue();
                                //log.debug("i is: " + i + ", val is: " + val);
                                if (val > 0)
                                    sale.setValue(sale.getValue() + val); //
                                else {
                                    sale.setCashback(sale.getCashback() - val); //
                                    cashb_rcrd++;
                                }
                            } else if (r.getCell(0).getStringCellValue().startsWith(" :")) {
                                //log.debug("finished shop record, i is: " + i);
                                sale.setChequeCount(rcrd - cashb_rcrd - 1);
                                shopFinished = true;
                            }

                            rcrd++;
                        }

                        parsedFileMap.put(name, sale);
                        //Double value = row.getCell(colNumber).getNumericCellValue();
                        //parsedFileMap.put(name, value);
                        //log.debug("Recieved file map: " + parsedFileMap);
                    }

                }

            } // foreach row in sheet

            if (dateFound && colFound) {
                result.put(fileDate, parsedFileMap);
                log.info("...ok");
                Files.delete(file.toPath());
            } else {
                if (!dateFound) {
                    log.warn("     " + file.getName());
                }
                if (!colFound) {
                    log.warn(" ?      "
                            + file.getName());
                }
                log.error("?   " + file.getName());
            }

        } catch (Exception e) {
            //e.printStackTrace();
            log.error("?  ,  : "
                    + e.getMessage());
        }

    } // foreach file in path

    return result;
}