SpreadsheetView copyClipboard() / pasteClipboard() functions on 8.20.9-SNAPSHOT

[zeyd bilal]

Hi,  how can i copy a value from Excel/Word in to SpreadsheetView table.

I'm trying it with this functions (copyClipboard / pasteClipboard) but it does not work.

[samir.ha...@gmail.com]

Hi,

The copy/paste methods available in the SpreadsheetView are here only for the SpreadsheetView. We do not support other format/application.

Why? Because it's complicate. See https://groups.google.com/forum/?hl=fr#!searchin/controlsfx-dev/POI/controlsfx-dev/bg6stNakOgA/9LP2bkz0kOAJ .

If you want to support Excel for example, you need to override these methods and provide the implementation you want. In my application, I have overridden these methods and provide the POI jar in order to support Excel compatibility.
I can help you do that if you want.

[zeyd bilal]

Hi,
of course if you can help me, i'd be happy.:) I am developing a tool for OpenJEVIS project. I need these functions.

[samir.ha...@gmail.com]

Allright so you will need the POI jar (https://poi.apache.org/).

I'll give you some code that I used and that should be enough to get you started. In the paste and copy methods, I first try to see if an excel format is there, and if not, I call the super method of the SpreadsheetView :

boolean value = ClipBoardExcel.pasteFromExcel(spreadsheetView);
            //If no excel format is found, we go with the normal behavior.
            if (!value) {
                super.pasteClipboard();
            }

Then here is the methods I have, I don't know if they are working perfectly but it should do the trick:

    /**
     * Try to find an Excel format in the clipboard.
     *
     * @param formats
     * @return the DataFormat identified, or null if it has not been found.
     */
    private static DataFormat findExcelFormat(Set<DataFormat> formats) {
        for (DataFormat format : formats) {
            if (format.getIdentifiers().contains(EXCEL_IDENTIFIER)) {
                return format;
            }
        }
        return null;
    }

    /**
     * Return whether the past from Excel has succeed.
     *
     * @param spv
     * @return
     */
    public static boolean pasteFromExcel(SpreadsheetView spv) {
        if (!spv.isEditable()) {
            return false;
        }
        final Clipboard clipboard = Clipboard.getSystemClipboard();

        DataFormat excelFormat = findExcelFormat(clipboard.getContentTypes());

        if (excelFormat == null) {
            return false;
        }

        Workbook wb;
        try {
            wb = new HSSFWorkbook(new ByteArrayInputStream(((ByteBuffer) clipboard.getContent(excelFormat)).array()));
        } catch (IOException ex) {
            LOGGER.error("Problem in ClipBoardExcel when trying to get the clipboard content.", ex);
            return false;
        }

        final TablePosition<?, ?> focusedCell = spv.getSelectionModel().getFocusedCell();
        /**
         * We need to keep records of the different indexes we are considering.
         * Then we can detect if we have some gap between the indexes in order
         * to respect these gap when pasting in Spv.
         */
        int currentRow = focusedCell.getRow();

        final int baseColumn = focusedCell.getColumn();
        int currentColumn = focusedCell.getColumn();

        if (currentRow == -1 || currentColumn == -1) {
            return false;
        }
        pasteIntoSpreadsheetView(wb, currentRow, baseColumn, currentColumn, spv);

        return true;
    }

    private static void pasteIntoSpreadsheetView(Workbook wb, int currentRow, int baseColumn, int currentColumn, SpreadsheetView spv) {
        int oldIndex = 0;
        int newIndex = 0;
        final Grid grid = spv.getGrid();

        final List<ObservableList<SpreadsheetCell>> rows = grid.getRows();

        Sheet sheet = wb.getSheetAt(0);
        for (Row row : sheet) {

            /**
             * The idea here is to keep track of the old indexes in order to see
             * if we have some gap between rows in Excel.
             *
             * So if the next row is situated 3 rows ahead, we will have a
             * newIndex-oldIndex = 3 and that will help us target the right row
             * in our own grid, instead of pasting everything next to each
             * other.
             */
            newIndex = row.getRowNum();
            oldIndex = oldIndex == 0 ? row.getRowNum() - 1 : oldIndex;
            currentRow += newIndex - oldIndex - 1;

            if (currentRow >= grid.getRowCount()) {
                continue;
            }

            for (Cell cell : row) {
                final SpreadsheetView.SpanType type = spv.getSpanType(currentRow, currentColumn);
                if (type == SpreadsheetView.SpanType.NORMAL_CELL || type == SpreadsheetView.SpanType.ROW_VISIBLE) {

                    SpreadsheetCell spc = rows.get(currentRow).get(currentColumn);
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            boolean succeedString = spc.getCellType().match(cell.getRichStringCellValue().getString());
                            if (succeedString) {
                                grid.setCellValue(spc.getRow(), spc.getColumn(),
                                        spc.getCellType().convertValue(cell.getRichStringCellValue().getString()));
                            }
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                        case Cell.CELL_TYPE_FORMULA:
                            //FIXME Verify DateTime here.
                            if (DateUtil.isCellDateFormatted(cell)) {
                                boolean succeedDate = spc.getCellType().match((double) cell.getDateCellValue().getTime());
                                if (succeedDate) {
                                    grid.setCellValue(spc.getRow(), spc.getColumn(),
                                            spc.getCellType().convertValue((double) cell.getDateCellValue().getTime()));
                                }
                            } else {
                                boolean succeedNumber = spc.getCellType().match(cell.getNumericCellValue());
                                if (succeedNumber) {
                                    grid.setCellValue(spc.getRow(), spc.getColumn(),
                                            spc.getCellType().convertValue(cell.getNumericCellValue()));
                                }
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
//                            System.out.println(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_BLANK:
                           
                            spv.getGrid().setCellValue(spc.getRow(), spc.getColumn(), null);
                            break;
                        default:
                    }
                }
                currentColumn++;
            }
            //Reset the column
            currentColumn = baseColumn;
            currentRow++;
            oldIndex = row.getRowNum();
        }
    }

This is for getting from Excel, if you want to export to Excel, I use in fact HTML because Excel can understand it. I'll give you some code later because I have to clean it on my side since I use some internal variable and stuff.

[zeyd bilal]

Thanks for your help!

[zeyd bilal]

Hi,

ich want to copy my table index in a String . If i use for the copy/paste KeyCode.C does not  work but if i use a another key combination KeyCode.X it works.

Do you have any idea what ist the problem ?

        scene.getAccelerators().put(new KeyCodeCombination(KeyCode.X, KeyCombination.CONTROL_ANY), new Runnable() {

            @Override
            public void run() {

                    Clipboard clipboard = Clipboard.getSystemClipboard();
                    ClipboardContent content = new ClipboardContent();
                   
                        ObservableList<TablePosition> focusedCell = spv.getSelectionModel().getSelectedCells();

                        int currentRow = 0;
                        int currentColumn = 0;
                        String contentText = "";

                        for (final TablePosition<?, ?> p : focusedCell) {
                            int tempRow = currentRow;
                            int tempColumn = currentColumn;

                            currentRow = p.getRow();
                            currentColumn = p.getColumn();

                            String spcText = rows.get(currentRow).get(currentColumn).getText();
                            contentText += spcText;

                        }

                        content.putString(contentText);
                        clipboard.setContent(content);

                        System.out.println("clipboard.getString() : " + clipboard.getString());
            }
        });

[samir.ha...@gmail.com]

Hi,

I put the accelerator on a menuItem like that in my code :

new KeyCodeCombination(KeyCode.C, KeyCombination.SHORTCUT_DOWN)

and it's working. So maybe track why the combination is not working in your case? Do you override the default contextMenu of the SpreadsheetView? Because by default, there is a copyItem in the spreadsheetView that has this KeyCodeCombination.

What I would suggest is to override the "copyClipboard()" method of the spreadsheetView, and not to set an accelerator. Then, since the SpreadsheetView actually set an accelerator, the copy should work.

Regards,
Sam'

[zeyd bilal]

Hi Samir,

thanks for your answer but if i override copy/pasteClipboard() methods i can't use the quick copy/paste function(screenshot) of SpreadsheetView. I am trying it with DataFormat class but it doesn't work. Can you help me please ?

if(dataformat == "spreadsheetview"){

do this...

}else{

do this...

}

My Codes :

https://github.com/AIT-JEVis/JEConfig/blob/branch_workspace/src/main/java/org/jevis/jeconfig/batchmode/CreateTable.java

the method is : scene.getAccelerators().put(new KeyCodeCombination(KeyCode.C, KeyCombination.SHORTCUT_DOWN), new Runnable() {}

Regards,

Bilal

[samir.ha...@gmail.com]

Hi,

What do you mean by "quick copy/paste"? DO you mean copy one value into the other cells?

Because if it's that, you can take that code out of the SpreadsheetView and add it in your code. Or maybe call "super()".

From what I see, you want to do a specific copy/pasting BUT also keep some native copy/pasting. What I suggest is that you override the copy pasting methods. Then you detect the case. If it's a case you want to have, deal with it. If not, just call super() and the native SpreadsheetView algorithm will apply.

Take a look in my code, for example I detect myself some scenarios and call different methods when pasting. You should be able to do the same:

if (clipboard.getContent(fmt) != null) {

            @SuppressWarnings("unchecked")
            final ArrayList<GridChange> list = (ArrayList<GridChange>) clipboard.getContent(fmt);
            if (list.size() == 1) {
                pasteOneValue(list.get(0));
            } else if (selectedCells.size() > 1) {
                pasteMixedValues(list);
            } else {
                pasteSeveralValues(list);
            }

Also, for the DataFormat, here is how I instantiate it if you need:

if ((fmt = DataFormat.lookupMimeType("SpreadsheetView")) == null) { //$NON-NLS-1$
            fmt = new DataFormat("SpreadsheetView"); //$NON-NLS-1$
        }

Best regards,

[zeyd bilal]

Hi Samir,

thanks for your answer. Yes i mean copy one value into the other cells. If i make a copy/paste in the SpreadsheetView , i want to use your copy/paste functions from SpreadsheetView. But if i need a copy/paste from SpreadsheetView to Excel or from Excel to SpreadsheetView (screenshot). i want to use also my copy/paste functions .This is a conflict situation for me. Is it possible separate these functions ?

Best regards,

Bilal

[samir.ha...@gmail.com]

Hi,

In my code here is what I do :

 boolean value = ClipBoardExcel.pasteFromExcel(spreadsheetView);
            //If no excel format is found, we go with the normal behavior.
            if (!value) {
                super.pasteClipboard();
            }

In the SpreadsheetView, when pasting, I first look if I find something coming from Excel. If true, it means the user wants to copy from Excel and paste in the SpreadsheetView.

If not, then it surely means that it is a "simple" paste, with data coming from the SpreadsheetView. Thus I just call "super" and let the SpreadsheetView inner methods handle it.

FYI, here is my method to find if there is something coming from Excel :

final Clipboard clipboard = Clipboard.getSystemClipboard();

        DataFormat excelFormat = findExcelFormat(clipboard.getContentTypes());

        if (excelFormat == null) {
            return false;
        }

and :

/**
     * Try to find an Excel format in the clipboard.
     *
     * @param formats
     * @return the DataFormat identified, or null if it has not been found.
     */
    private static DataFormat findExcelFormat(Set<DataFormat> formats) {
        for (DataFormat format : formats) {
            if (format.getIdentifiers().contains(EXCEL_IDENTIFIER)) {
                return format;
            }
        }
        return null;
    }

private static final String EXCEL_IDENTIFIER = "Biff8";

Tell me if that doesn't fit for you.

[zeyd bilal]

Hi Samir,

thanks, i think paste from Excel to SpreadsheetView is ok. I can implement this part. But copy function from SpreadsheetView to SpreadsheetView or from SpreadsheetView to Excel is still for me a puzzel.

We use the KeyCode.C. for both. I want to make a copy in the SpreadsheetView  and then i want to make a copy to Excel. How can I distinguish these copy functions? I think that is not possible. How would you solve this problem? Do you have a solution for that?

Best regards,

Bilal

[samir.ha...@gmail.com]

It is very possible to do what you want.

The Clipboard can store several data. So one for the SpreadsheetView and one for Excel. In the same manner, here is what I do:

   @Override
        public void copyClipboard() {
            super.copyClipboard();
            copyClipBoardSpecific();
        }

public void copyClipBoardSpecific() {
        Object templist = Clipboard.getSystemClipboard().getContent(SpreadsheetViewFormat);
        String htmlContent = ClipBoardHTML.export(this, spreadsheetView.getSelectionModel().getSelectedCells());
        final ClipboardContent content = new ClipboardContent();
        content.putHtml(htmlContent);
        content.put(SpreadsheetViewFormat, templist);
        content.putString(ClipBoardString.copyClipBoardString(this));
        Clipboard.getSystemClipboard().setContent(content);
    }

What I do first is to call the "super" of SpreadsheetView. Then after, I generate an HTML content for Excel (it simplier).

Afterwards I just need to create a new CLipBoard content. I put the data associated with the spreadsheetView. I put the data in an HTML form for Excel (or Outlook etc). And I also put the data in a String representation so that the user can paste into a notepad.

Then, when the user is doing a CTRL - C in the SpreadsheetView. The clipboard contains the selection in every format and the user can freely decide to paste it where he wants.

Is that clear?

[zeyd bilal]

Hi Samir,

it is clear. Thanks for your help.

Best regards,

Bilal