Search in sources :

Example 11 with KWorkbook

use of org.pentaho.di.core.spreadsheet.KWorkbook in project pentaho-kettle by pentaho.

the class ExcelInputDialog method getSheets.

/**
 * Get the names of the sheets from the Excel workbooks and let the user select some or all of them.
 */
public void getSheets() {
    List<String> sheetnames = new ArrayList<String>();
    ExcelInputMeta info = new ExcelInputMeta();
    getInfo(info);
    FileInputList fileList = info.getFileList(transMeta);
    for (FileObject fileObject : fileList.getFiles()) {
        try {
            KWorkbook workbook = WorkbookFactory.getWorkbook(info.getSpreadSheetType(), KettleVFS.getFilename(fileObject), info.getEncoding(), wPassword.getText());
            int nrSheets = workbook.getNumberOfSheets();
            for (int j = 0; j < nrSheets; j++) {
                KSheet sheet = workbook.getSheet(j);
                String sheetname = sheet.getName();
                if (Const.indexOfString(sheetname, sheetnames) < 0) {
                    sheetnames.add(sheetname);
                }
            }
            workbook.close();
        } catch (Exception e) {
            new ErrorDialog(shell, BaseMessages.getString(PKG, "System.Dialog.Error.Title"), BaseMessages.getString(PKG, "ExcelInputDialog.ErrorReadingFile.DialogMessage", KettleVFS.getFilename(fileObject)), e);
        }
    }
    // Put it in an array:
    String[] lst = sheetnames.toArray(new String[sheetnames.size()]);
    // Let the user select the sheet-names...
    EnterListDialog esd = new EnterListDialog(shell, SWT.NONE, lst);
    String[] selection = esd.open();
    if (selection != null) {
        for (int j = 0; j < selection.length; j++) {
            wSheetnameList.add(new String[] { selection[j], "" });
        }
        wSheetnameList.removeEmptyRows();
        wSheetnameList.setRowNums();
        wSheetnameList.optWidth(true);
        checkAlerts();
    }
}
Also used : KWorkbook(org.pentaho.di.core.spreadsheet.KWorkbook) ArrayList(java.util.ArrayList) KSheet(org.pentaho.di.core.spreadsheet.KSheet) ErrorDialog(org.pentaho.di.ui.core.dialog.ErrorDialog) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) EnterListDialog(org.pentaho.di.ui.core.dialog.EnterListDialog) KettleStepException(org.pentaho.di.core.exception.KettleStepException) KettlePluginException(org.pentaho.di.core.exception.KettlePluginException) ExcelInputMeta(org.pentaho.di.trans.steps.excelinput.ExcelInputMeta) FileObject(org.apache.commons.vfs2.FileObject) FileInputList(org.pentaho.di.core.fileinput.FileInputList)

Example 12 with KWorkbook

use of org.pentaho.di.core.spreadsheet.KWorkbook in project pentaho-kettle by pentaho.

the class StaxWorkBookIT method testReadCells.

@Test
public void testReadCells() throws Exception {
    KWorkbook workbook = getWorkbook(sample, null);
    KSheet sheet = workbook.getSheet(0);
    KCell cell = sheet.getCell(1, 2);
    assertEquals("One", cell.getValue());
    assertEquals(KCellType.LABEL, cell.getType());
    cell = sheet.getCell(2, 2);
    assertEquals(KCellType.DATE, cell.getType());
    assertEquals(new Date(1283817600000L), cell.getValue());
    cell = sheet.getCell(1, 3);
    assertEquals("Two", cell.getValue());
    assertEquals(KCellType.LABEL, cell.getType());
}
Also used : KWorkbook(org.pentaho.di.core.spreadsheet.KWorkbook) KSheet(org.pentaho.di.core.spreadsheet.KSheet) KCell(org.pentaho.di.core.spreadsheet.KCell) Date(java.util.Date) Test(org.junit.Test)

Example 13 with KWorkbook

use of org.pentaho.di.core.spreadsheet.KWorkbook in project pentaho-kettle by pentaho.

the class StaxWorkBookIT method testReadRowRA.

@Test
public void testReadRowRA() throws Exception {
    KWorkbook workbook = getWorkbook(sample, null);
    KSheet sheet1 = workbook.getSheet(0);
    KCell[] row = sheet1.getRow(4);
    assertEquals("Three", row[1].getValue());
    row = sheet1.getRow(2);
    assertEquals("One", row[1].getValue());
}
Also used : KWorkbook(org.pentaho.di.core.spreadsheet.KWorkbook) KSheet(org.pentaho.di.core.spreadsheet.KSheet) KCell(org.pentaho.di.core.spreadsheet.KCell) Test(org.junit.Test)

Example 14 with KWorkbook

use of org.pentaho.di.core.spreadsheet.KWorkbook in project pentaho-kettle by pentaho.

the class ExcelInputDialogTest method getFieldsTest.

@Test
public /**
 * http://jira.pentaho.com/browse/PDI-13930
 */
void getFieldsTest() throws Exception {
    ExcelInputDialog dialog = Mockito.mock(ExcelInputDialog.class);
    RowMeta fields = new RowMeta();
    ExcelInputMeta info = Mockito.mock(ExcelInputMeta.class);
    Mockito.doReturn(true).when(info).readAllSheets();
    int[] startColumn = { 0 };
    Mockito.doReturn(startColumn).when(info).getStartColumn();
    int[] startRow = { 0 };
    Mockito.doReturn(startRow).when(info).getStartRow();
    KWorkbook workbook = Mockito.mock(KWorkbook.class);
    Mockito.doReturn(1).when(workbook).getNumberOfSheets();
    KSheet sheet = Mockito.mock(KSheet.class);
    Mockito.doReturn(sheet).when(workbook).getSheet(0);
    KCell cell = Mockito.mock(KCell.class);
    int fieldCount = 400;
    for (int i = 0; i <= fieldCount - 1; i++) {
        Mockito.doReturn(cell).when(sheet).getCell(i, 0);
        Mockito.doReturn(cell).when(sheet).getCell(i, 1);
    }
    Mockito.doReturn("testValue").when(cell).getContents();
    Mockito.doReturn(KCellType.NUMBER).when(cell).getType();
    PluginRegistry pluginRegistry = Mockito.mock(PluginRegistry.class);
    PluginInterface stringPlugin = Mockito.mock(PluginInterface.class);
    Mockito.doReturn(stringPlugin).when(pluginRegistry).getPlugin(ValueMetaPluginType.class, "1");
    Mockito.doReturn(Mockito.mock(ValueMetaInterface.class)).when(pluginRegistry).loadClass(stringPlugin, ValueMetaInterface.class);
    ValueMetaFactory.pluginRegistry = pluginRegistry;
    Method processingWorkbookMethod = ExcelInputDialog.class.getDeclaredMethod("processingWorkbook", RowMetaInterface.class, ExcelInputMeta.class, KWorkbook.class);
    processingWorkbookMethod.setAccessible(true);
    processingWorkbookMethod.invoke(dialog, fields, info, workbook);
    Assert.assertEquals(fieldCount, fields.size());
}
Also used : ExcelInputMeta(org.pentaho.di.trans.steps.excelinput.ExcelInputMeta) RowMeta(org.pentaho.di.core.row.RowMeta) KWorkbook(org.pentaho.di.core.spreadsheet.KWorkbook) PluginRegistry(org.pentaho.di.core.plugins.PluginRegistry) PluginInterface(org.pentaho.di.core.plugins.PluginInterface) KSheet(org.pentaho.di.core.spreadsheet.KSheet) Method(java.lang.reflect.Method) KCell(org.pentaho.di.core.spreadsheet.KCell) ValueMetaInterface(org.pentaho.di.core.row.ValueMetaInterface) Test(org.junit.Test)

Aggregations

KWorkbook (org.pentaho.di.core.spreadsheet.KWorkbook)14 KSheet (org.pentaho.di.core.spreadsheet.KSheet)13 KCell (org.pentaho.di.core.spreadsheet.KCell)11 Date (java.util.Date)7 Test (org.junit.Test)6 ExcelInputMeta (org.pentaho.di.trans.steps.excelinput.ExcelInputMeta)3 FileObject (org.apache.commons.vfs2.FileObject)2 KettlePluginException (org.pentaho.di.core.exception.KettlePluginException)2 KettleStepException (org.pentaho.di.core.exception.KettleStepException)2 FileInputList (org.pentaho.di.core.fileinput.FileInputList)2 RowMeta (org.pentaho.di.core.row.RowMeta)2 ValueMetaInterface (org.pentaho.di.core.row.ValueMetaInterface)2 ErrorDialog (org.pentaho.di.ui.core.dialog.ErrorDialog)2 Method (java.lang.reflect.Method)1 ArrayList (java.util.ArrayList)1 MessageBox (org.eclipse.swt.widgets.MessageBox)1 PluginInterface (org.pentaho.di.core.plugins.PluginInterface)1 PluginRegistry (org.pentaho.di.core.plugins.PluginRegistry)1 RowMetaInterface (org.pentaho.di.core.row.RowMetaInterface)1 ValueMetaString (org.pentaho.di.core.row.value.ValueMetaString)1