use of org.pentaho.di.core.spreadsheet.KSheet in project pentaho-kettle by pentaho.
the class StaxWorkBookIT method testReadEmptyRow.
@Test
public void testReadEmptyRow() throws Exception {
KWorkbook workbook = getWorkbook("src/it/resources/sample-file.xlsx", null);
KSheet sheet1 = workbook.getSheet(0);
KCell[] row = sheet1.getRow(0);
assertEquals(0, row.length);
}
use of org.pentaho.di.core.spreadsheet.KSheet in project pentaho-kettle by pentaho.
the class StaxWorkBookIT method testReadSameRow.
@Test
public void testReadSameRow() throws Exception {
KWorkbook workbook = getWorkbook("src/it/resources/sample-file.xlsx", 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.KSheet in project pentaho-kettle by pentaho.
the class ExcelInput method getRowFromWorkbooks.
public Object[] getRowFromWorkbooks() {
// This procedure outputs a single Excel data row on the destination
// rowsets...
Object[] retval = null;
try {
// First, see if a file has been opened?
if (data.workbook == null) {
// Open a new openFile..
data.file = data.files.getFile(data.filenr);
data.filename = KettleVFS.getFilename(data.file);
// Add additional fields?
if (meta.getShortFileNameField() != null && meta.getShortFileNameField().length() > 0) {
data.shortFilename = data.file.getName().getBaseName();
}
if (meta.getPathField() != null && meta.getPathField().length() > 0) {
data.path = KettleVFS.getFilename(data.file.getParent());
}
if (meta.isHiddenField() != null && meta.isHiddenField().length() > 0) {
data.hidden = data.file.isHidden();
}
if (meta.getExtensionField() != null && meta.getExtensionField().length() > 0) {
data.extension = data.file.getName().getExtension();
}
if (meta.getLastModificationDateField() != null && meta.getLastModificationDateField().length() > 0) {
data.lastModificationDateTime = new Date(data.file.getContent().getLastModifiedTime());
}
if (meta.getUriField() != null && meta.getUriField().length() > 0) {
data.uriName = data.file.getName().getURI();
}
if (meta.getRootUriField() != null && meta.getRootUriField().length() > 0) {
data.rootUriName = data.file.getName().getRootURI();
}
if (meta.getSizeField() != null && meta.getSizeField().length() > 0) {
data.size = new Long(data.file.getContent().getSize());
}
if (meta.isAddResultFile()) {
ResultFile resultFile = new ResultFile(ResultFile.FILE_TYPE_GENERAL, data.file, getTransMeta().getName(), toString());
resultFile.setComment(BaseMessages.getString(PKG, "ExcelInput.Log.FileReadByStep"));
addResultFile(resultFile);
}
if (log.isDetailed()) {
logDetailed(BaseMessages.getString(PKG, "ExcelInput.Log.OpeningFile", "" + data.filenr + " : " + data.filename));
}
fpis = new FileInputStream(data.filename);
data.workbook = WorkbookFactory.getWorkbook(meta.getSpreadSheetType(), fpis, meta.getEncoding());
data.errorHandler.handleFile(data.file);
// Start at the first sheet again...
data.sheetnr = 0;
//
if (meta.readAllSheets()) {
data.sheetNames = data.workbook.getSheetNames();
data.startColumn = new int[data.sheetNames.length];
data.startRow = new int[data.sheetNames.length];
for (int i = 0; i < data.sheetNames.length; i++) {
data.startColumn[i] = data.defaultStartColumn;
data.startRow[i] = data.defaultStartRow;
}
}
}
boolean nextsheet = false;
// What sheet were we handling?
if (log.isDebug()) {
logDetailed(BaseMessages.getString(PKG, "ExcelInput.Log.GetSheet", "" + data.filenr + "." + data.sheetnr));
}
String sheetName = data.sheetNames[data.sheetnr];
KSheet sheet = data.workbook.getSheet(sheetName);
if (sheet != null) {
// at what row do we continue reading?
if (data.rownr < 0) {
data.rownr = data.startRow[data.sheetnr];
// Add an extra row if we have a header row to skip...
if (meta.startsWithHeader()) {
data.rownr++;
}
}
// Start at the specified column
data.colnr = data.startColumn[data.sheetnr];
// Build a new row and fill in the data from the sheet...
try {
KCell[] line = sheet.getRow(data.rownr);
// Already increase cursor 1 row
int lineNr = ++data.rownr;
// Excel starts counting at 0
if (!data.filePlayList.isProcessingNeeded(data.file, lineNr, sheetName)) {
// placeholder, was already null
retval = null;
} else {
if (log.isRowLevel()) {
logRowlevel(BaseMessages.getString(PKG, "ExcelInput.Log.GetLine", "" + lineNr, data.filenr + "." + data.sheetnr));
}
if (log.isRowLevel()) {
logRowlevel(BaseMessages.getString(PKG, "ExcelInput.Log.ReadLineWith", "" + line.length));
}
ExcelInputRow excelInputRow = new ExcelInputRow(sheet.getName(), lineNr, line);
Object[] r = fillRow(data.colnr, excelInputRow);
if (log.isRowLevel()) {
logRowlevel(BaseMessages.getString(PKG, "ExcelInput.Log.ConvertedLinToRow", "" + lineNr, data.outputRowMeta.getString(r)));
}
boolean isEmpty = isLineEmpty(line);
if (!isEmpty || !meta.ignoreEmptyRows()) {
// Put the row
retval = r;
} else {
if (data.rownr > sheet.getRows()) {
nextsheet = true;
}
}
if (isEmpty && meta.stopOnEmpty()) {
nextsheet = true;
}
}
} catch (ArrayIndexOutOfBoundsException e) {
if (log.isRowLevel()) {
logRowlevel(BaseMessages.getString(PKG, "ExcelInput.Log.OutOfIndex"));
}
// We tried to read below the last line in the sheet.
// Go to the next sheet...
nextsheet = true;
}
} else {
nextsheet = true;
}
if (nextsheet) {
// Go to the next sheet
data.sheetnr++;
// Reset the start-row:
data.rownr = -1;
// no previous row yet, don't take it from the previous sheet!
// (that whould be plain wrong!)
data.previousRow = null;
// Perhaps it was the last sheet?
if (data.sheetnr >= data.sheetNames.length) {
jumpToNextFile();
}
}
} catch (Exception e) {
logError(BaseMessages.getString(PKG, "ExcelInput.Error.ProcessRowFromExcel", data.filename + "", e.toString()), e);
setErrors(1);
stopAll();
return null;
}
return retval;
}
use of org.pentaho.di.core.spreadsheet.KSheet in project pentaho-kettle by pentaho.
the class StaxPoiSheetTest method testReadRowRA.
@Test
public void testReadRowRA() throws Exception {
KSheet sheet1 = getSampleSheet();
KCell[] row = sheet1.getRow(4);
assertEquals("Three", row[1].getValue());
row = sheet1.getRow(2);
assertEquals("One", row[1].getValue());
}
use of org.pentaho.di.core.spreadsheet.KSheet in project pentaho-kettle by pentaho.
the class StaxPoiSheetTest method testReadSameRow.
@Test
public void testReadSameRow() throws Exception {
KSheet sheet1 = getSampleSheet();
KCell[] row = sheet1.getRow(3);
assertEquals("Two", row[1].getValue());
row = sheet1.getRow(3);
assertEquals("Two", row[1].getValue());
}
Aggregations