use of org.pentaho.di.core.spreadsheet.KWorkbook in project pentaho-kettle by pentaho.
the class StaxWorkBookIT method readData.
private void readData(String file, String password) throws KettleException {
KWorkbook workbook = getWorkbook(file, null, password);
int numberOfSheets = workbook.getNumberOfSheets();
assertEquals(3, numberOfSheets);
KSheet sheet1 = workbook.getSheet(0);
assertEquals("Sheet1", sheet1.getName());
sheet1 = workbook.getSheet("Sheet1");
assertEquals("Sheet1", sheet1.getName());
assertEquals(5, sheet1.getRows());
KCell[] row = sheet1.getRow(2);
assertEquals(KCellType.LABEL, row[1].getType());
assertEquals("One", row[1].getValue());
assertEquals(KCellType.DATE, row[2].getType());
assertEquals(new Date(1283817600000L), row[2].getValue());
assertEquals(KCellType.NUMBER, row[3].getType());
assertEquals(Double.valueOf("75"), row[3].getValue());
assertEquals(KCellType.BOOLEAN, row[4].getType());
assertEquals(Boolean.valueOf(true), row[4].getValue());
assertEquals(KCellType.NUMBER_FORMULA, row[5].getType());
assertEquals(Double.valueOf("75"), row[5].getValue());
row = sheet1.getRow(3);
assertEquals(KCellType.LABEL, row[1].getType());
assertEquals("Two", row[1].getValue());
assertEquals(KCellType.DATE, row[2].getType());
assertEquals(new Date(1283904000000L), row[2].getValue());
assertEquals(KCellType.NUMBER, row[3].getType());
assertEquals(Double.valueOf("42"), row[3].getValue());
assertEquals(KCellType.BOOLEAN, row[4].getType());
assertEquals(Boolean.valueOf(false), row[4].getValue());
assertEquals(KCellType.NUMBER_FORMULA, row[5].getType());
assertEquals(Double.valueOf("117"), row[5].getValue());
row = sheet1.getRow(4);
assertEquals(KCellType.LABEL, row[1].getType());
assertEquals("Three", row[1].getValue());
assertEquals(KCellType.DATE, row[2].getType());
assertEquals(new Date(1283990400000L), row[2].getValue());
assertEquals(KCellType.NUMBER, row[3].getType());
assertEquals(Double.valueOf("93"), row[3].getValue());
assertEquals(KCellType.BOOLEAN, row[4].getType());
assertEquals(Boolean.valueOf(true), row[4].getValue());
assertEquals(KCellType.NUMBER_FORMULA, row[5].getType());
assertEquals(Double.valueOf("210"), row[5].getValue());
try {
row = sheet1.getRow(5);
fail("No out of bounds exception thrown when expected");
} catch (ArrayIndexOutOfBoundsException e) {
// OK!
}
}
use of org.pentaho.di.core.spreadsheet.KWorkbook in project pentaho-kettle by pentaho.
the class StaxWorkBookIT method testReadSameRow.
@Test
public void testReadSameRow() throws Exception {
KWorkbook workbook = getWorkbook(sample, null);
KSheet sheet1 = workbook.getSheet(0);
KCell[] row = sheet1.getRow(3);
assertEquals("Two", row[1].getValue());
row = sheet1.getRow(3);
assertEquals("Two", row[1].getValue());
}
use of org.pentaho.di.core.spreadsheet.KWorkbook in project pentaho-kettle by pentaho.
the class ExcelInputDialog method getFields.
/**
* Get the list of fields in the Excel workbook and put the result in the fields table view.
*/
public void getFields() {
RowMetaInterface fields = new RowMeta();
ExcelInputMeta info = new ExcelInputMeta();
getInfo(info);
int clearFields = SWT.YES;
if (wFields.nrNonEmpty() > 0) {
MessageBox messageBox = new MessageBox(shell, SWT.YES | SWT.NO | SWT.CANCEL | SWT.ICON_QUESTION);
messageBox.setMessage(BaseMessages.getString(PKG, "ExcelInputDialog.ClearFieldList.DialogMessage"));
messageBox.setText(BaseMessages.getString(PKG, "ExcelInputDialog.ClearFieldList.DialogTitle"));
clearFields = messageBox.open();
if (clearFields == SWT.CANCEL) {
return;
}
}
FileInputList fileList = info.getFileList(transMeta);
for (FileObject file : fileList.getFiles()) {
try {
KWorkbook workbook = WorkbookFactory.getWorkbook(info.getSpreadSheetType(), KettleVFS.getFilename(file), info.getEncoding(), wPassword.getText());
processingWorkbook(fields, info, workbook);
workbook.close();
} catch (Exception e) {
new ErrorDialog(shell, BaseMessages.getString(PKG, "System.Dialog.Error.Title"), BaseMessages.getString(PKG, "ExcelInputDialog.ErrorReadingFile2.DialogMessage", KettleVFS.getFilename(file), e.toString()), e);
}
}
if (fields.size() > 0) {
if (clearFields == SWT.YES) {
wFields.clearAll(false);
}
for (int j = 0; j < fields.size(); j++) {
ValueMetaInterface field = fields.getValueMeta(j);
wFields.add(new String[] { field.getName(), field.getTypeDesc(), "", "", "none", "N" });
}
wFields.removeEmptyRows();
wFields.setRowNums();
wFields.optWidth(true);
} else {
MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_WARNING);
mb.setMessage(BaseMessages.getString(PKG, "ExcelInputDialog.UnableToFindFields.DialogMessage"));
mb.setText(BaseMessages.getString(PKG, "ExcelInputDialog.UnableToFindFields.DialogTitle"));
mb.open();
}
checkAlerts();
}
use of org.pentaho.di.core.spreadsheet.KWorkbook in project pentaho-kettle by pentaho.
the class PoiWorkBookIT method readData.
private void readData() throws KettleException {
KWorkbook workbook = WorkbookFactory.getWorkbook(SpreadSheetType.POI, "src/it/resources/sample-file.xlsx", null);
int numberOfSheets = workbook.getNumberOfSheets();
assertEquals(3, numberOfSheets);
KSheet sheet1 = workbook.getSheet(0);
assertEquals("Sheet1", sheet1.getName());
sheet1 = workbook.getSheet("Sheet1");
assertEquals("Sheet1", sheet1.getName());
assertEquals(5, sheet1.getRows());
KCell[] row = sheet1.getRow(2);
assertEquals(KCellType.LABEL, row[1].getType());
assertEquals("One", row[1].getValue());
assertEquals(KCellType.DATE, row[2].getType());
assertEquals(new Date(1283817600000L), row[2].getValue());
assertEquals(KCellType.NUMBER, row[3].getType());
assertEquals(Double.valueOf("75"), row[3].getValue());
assertEquals(KCellType.BOOLEAN, row[4].getType());
assertEquals(Boolean.valueOf(true), row[4].getValue());
assertEquals(KCellType.NUMBER_FORMULA, row[5].getType());
assertEquals(Double.valueOf("75"), row[5].getValue());
row = sheet1.getRow(3);
assertEquals(KCellType.LABEL, row[1].getType());
assertEquals("Two", row[1].getValue());
assertEquals(KCellType.DATE, row[2].getType());
assertEquals(new Date(1283904000000L), row[2].getValue());
assertEquals(KCellType.NUMBER, row[3].getType());
assertEquals(Double.valueOf("42"), row[3].getValue());
assertEquals(KCellType.BOOLEAN, row[4].getType());
assertEquals(Boolean.valueOf(false), row[4].getValue());
assertEquals(KCellType.NUMBER_FORMULA, row[5].getType());
assertEquals(Double.valueOf("117"), row[5].getValue());
row = sheet1.getRow(4);
assertEquals(KCellType.LABEL, row[1].getType());
assertEquals("Three", row[1].getValue());
assertEquals(KCellType.DATE, row[2].getType());
assertEquals(new Date(1283990400000L), row[2].getValue());
assertEquals(KCellType.NUMBER, row[3].getType());
assertEquals(Double.valueOf("93"), row[3].getValue());
assertEquals(KCellType.BOOLEAN, row[4].getType());
assertEquals(Boolean.valueOf(true), row[4].getValue());
assertEquals(KCellType.NUMBER_FORMULA, row[5].getType());
assertEquals(Double.valueOf("210"), row[5].getValue());
try {
sheet1.getRow(5);
fail("No out of bounds exception thrown when expected");
} catch (ArrayIndexOutOfBoundsException e) {
// OK!
}
workbook.close();
}
use of org.pentaho.di.core.spreadsheet.KWorkbook in project pentaho-kettle by pentaho.
the class StaxWorkBookIT method readData.
private void readData() throws KettleException {
KWorkbook workbook = getWorkbook("src/it/resources/sample-file.xlsx", null);
int numberOfSheets = workbook.getNumberOfSheets();
assertEquals(3, numberOfSheets);
KSheet sheet1 = workbook.getSheet(0);
assertEquals("Sheet1", sheet1.getName());
sheet1 = workbook.getSheet("Sheet1");
assertEquals("Sheet1", sheet1.getName());
assertEquals(5, sheet1.getRows());
KCell[] row = sheet1.getRow(2);
assertEquals(KCellType.LABEL, row[1].getType());
assertEquals("One", row[1].getValue());
assertEquals(KCellType.DATE, row[2].getType());
assertEquals(new Date(1283817600000L), row[2].getValue());
assertEquals(KCellType.NUMBER, row[3].getType());
assertEquals(Double.valueOf("75"), row[3].getValue());
assertEquals(KCellType.BOOLEAN, row[4].getType());
assertEquals(Boolean.valueOf(true), row[4].getValue());
assertEquals(KCellType.NUMBER_FORMULA, row[5].getType());
assertEquals(Double.valueOf("75"), row[5].getValue());
row = sheet1.getRow(3);
assertEquals(KCellType.LABEL, row[1].getType());
assertEquals("Two", row[1].getValue());
assertEquals(KCellType.DATE, row[2].getType());
assertEquals(new Date(1283904000000L), row[2].getValue());
assertEquals(KCellType.NUMBER, row[3].getType());
assertEquals(Double.valueOf("42"), row[3].getValue());
assertEquals(KCellType.BOOLEAN, row[4].getType());
assertEquals(Boolean.valueOf(false), row[4].getValue());
assertEquals(KCellType.NUMBER_FORMULA, row[5].getType());
assertEquals(Double.valueOf("117"), row[5].getValue());
row = sheet1.getRow(4);
assertEquals(KCellType.LABEL, row[1].getType());
assertEquals("Three", row[1].getValue());
assertEquals(KCellType.DATE, row[2].getType());
assertEquals(new Date(1283990400000L), row[2].getValue());
assertEquals(KCellType.NUMBER, row[3].getType());
assertEquals(Double.valueOf("93"), row[3].getValue());
assertEquals(KCellType.BOOLEAN, row[4].getType());
assertEquals(Boolean.valueOf(true), row[4].getValue());
assertEquals(KCellType.NUMBER_FORMULA, row[5].getType());
assertEquals(Double.valueOf("210"), row[5].getValue());
try {
row = sheet1.getRow(5);
fail("No out of bounds exception thrown when expected");
} catch (ArrayIndexOutOfBoundsException e) {
// OK!
}
}
Aggregations