/*!
* This program is free software; you can redistribute it and/or modify it under the
* terms of the GNU Lesser General Public License, version 2.1 as published by the Free Software
* Foundation.
*
* You should have received a copy of the GNU Lesser General Public License along with this
* program; if not, you can obtain a copy at http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html
* or from the Free Software Foundation, Inc.,
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
*
* This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU Lesser General Public License for more details.
*
* Copyright (c) 2002-2013 Pentaho Corporation.. All rights reserved.
*/
package org.pentaho.reporting.ui.datasources.table;
import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.pentaho.reporting.engine.classic.core.util.TypedTableModel;
import org.pentaho.reporting.libraries.base.util.IOUtils;
import org.pentaho.reporting.libraries.designtime.swing.background.CancelEvent;
import org.pentaho.reporting.libraries.designtime.swing.background.CancelListener;
public class ImportFromFileTask implements Runnable, CancelListener
{
private static final Log logger = LogFactory.getLog(ImportFromFileTask.class);
private File selectedFile;
private boolean useFirstRowAsHeader;
private TableDataSourceEditor parent;
public ImportFromFileTask(final File selectedFile,
final boolean useFirstRowAsHeader,
final TableDataSourceEditor parent)
{
this.parent = parent;
if (selectedFile == null)
{
throw new NullPointerException();
}
this.selectedFile = selectedFile;
this.useFirstRowAsHeader = useFirstRowAsHeader;
}
public void run()
{
importFromFile(selectedFile, useFirstRowAsHeader);
}
/**
* Requests that the thread stop processing as soon as possible.
*/
public void cancelProcessing(final CancelEvent event)
{
Thread.currentThread().interrupt();
}
private void importFromFile(final File file, final boolean firstRowIsHeader)
{
final ByteArrayOutputStream bout = new ByteArrayOutputStream(Math.max(8192, (int) file.length()));
try
{
final InputStream fin = new FileInputStream(file);
try
{
IOUtils.getInstance().copyStreams(new BufferedInputStream(fin), bout);
}
finally
{
fin.close();
}
if (Thread.currentThread().isInterrupted())
{
return;
}
final Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray()));
int sheetIndex = 0;
if (workbook.getNumberOfSheets() > 1)
{
final SheetSelectorDialog selectorDialog = new SheetSelectorDialog(workbook, parent);
if (selectorDialog.performSelection())
{
sheetIndex = selectorDialog.getSelectedIndex();
}
else
{
return;
}
}
final TypedTableModel tableModel = new TypedTableModel();
final Sheet sheet = workbook.getSheetAt(sheetIndex);
final Iterator rowIterator = sheet.rowIterator();
if (firstRowIsHeader)
{
if (rowIterator.hasNext())
{
final Row headerRow = (Row) rowIterator.next();
final short cellCount = headerRow.getLastCellNum();
for (short colIdx = 0; colIdx < cellCount; colIdx++)
{
final Cell cell = headerRow.getCell(colIdx);
if (cell != null)
{
while (colIdx > tableModel.getColumnCount())
{
tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column",
String.valueOf(tableModel.getColumnCount())), Object.class);
}
final RichTextString string = cell.getRichStringCellValue();
if (string != null)
{
tableModel.addColumn(string.getString(), Object.class);
}
else
{
tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column", String.valueOf(colIdx)), Object.class);
}
}
}
}
}
Object[] rowData = null;
while (rowIterator.hasNext())
{
final Row row = (Row) rowIterator.next();
final short cellCount = row.getLastCellNum();
if (cellCount == -1)
{
continue;
}
if (rowData == null || rowData.length != cellCount)
{
rowData = new Object[cellCount];
}
for (short colIdx = 0; colIdx < cellCount; colIdx++)
{
final Cell cell = row.getCell(colIdx);
final Object value;
if (cell != null)
{
if (cell.getCellType() == Cell.CELL_TYPE_STRING)
{
final RichTextString string = cell.getRichStringCellValue();
if (string != null)
{
value = string.getString();
}
else
{
value = null;
}
}
else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
{
final CellStyle hssfCellStyle = cell.getCellStyle();
final short dataFormat = hssfCellStyle.getDataFormat();
final String dataFormatString = hssfCellStyle.getDataFormatString();
if (isDateFormat(dataFormat, dataFormatString))
{
value = cell.getDateCellValue();
}
else
{
value = cell.getNumericCellValue();
}
}
else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
{
value = cell.getBooleanCellValue();
}
else
{
value = cell.getStringCellValue();
}
}
else
{
value = null;
}
if (value != null && "".equals(value) == false)
{
while (colIdx >= tableModel.getColumnCount())
{
tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column",
String.valueOf(tableModel.getColumnCount())), Object.class);
}
}
rowData[colIdx] = value;
}
if (Thread.currentThread().isInterrupted())
{
return;
}
tableModel.addRow(rowData);
}
final int colCount = tableModel.getColumnCount();
final int rowCount = tableModel.getRowCount();
for (int col = 0; col < colCount; col++)
{
Class type = null;
for (int row = 0; row < rowCount; row += 1)
{
final Object value = tableModel.getValueAt(row, col);
if (value == null)
{
continue;
}
if (type == null)
{
type = value.getClass();
}
else if (type != Object.class)
{
if (type.isInstance(value) == false)
{
type = Object.class;
}
}
}
if (Thread.currentThread().isInterrupted())
{
return;
}
if (type != null)
{
tableModel.setColumnType(col, type);
}
}
parent.importComplete(tableModel);
}
catch (Exception e)
{
parent.importFailed(e);
logger.error("Failed to import spreadsheet", e); // NON-NLS
}
}
private boolean isDateFormat(final short knownFormat, final String dataFormat)
{
if ("GENERAL".equalsIgnoreCase(dataFormat))
{
return false;
}
switch (knownFormat)
{
case 0x0e:
case 0x0f:
case 0x10:
case 0x11:
case 0x12:
case 0x13:
case 0x14:
case 0x15:
case 0x16:
case 0x2d:
case 0x2e:
case 0x2f:
return true;
}
boolean inFormatQuote = false;
boolean inQuote = false;
int maybeElapsedHour = 0;
final char[] chars = dataFormat.toCharArray();
for (int i = 0; i < chars.length; i++)
{
final char c = chars[i];
if (c == '[')
{
inFormatQuote = true;
maybeElapsedHour = 0;
}
else if (inFormatQuote)
{
if (c == ']')
{
if (maybeElapsedHour == 1)
{
// seems to contain fragments of date format strings..
return true;
}
}
else if (c == 'h' && maybeElapsedHour == 0)
{
maybeElapsedHour = 2;
}
else if (c == 's' && maybeElapsedHour == 0)
{
maybeElapsedHour = 2;
}
else if (c == 'm' && maybeElapsedHour == 0)
{
maybeElapsedHour = 2;
}
else
{
maybeElapsedHour = 1;
}
}
else if (inQuote == false && c == '"')
{
inQuote = true;
}
else if (inQuote)
{
if (c == '"')
{
inQuote = false;
}
}
else
{
if (c == 'm' || c == 'd' || c == 'y' || c == 'h' || c == 's' || c == 'A' || c == 'a' || c == 'P' || c == 'p')
{
return true;
}
}
}
return false;
}
}