/*
* Licensed Materials - Property of Perforce Software, Inc.
* © Copyright Perforce Software, Inc. 2014, 2021
* © Copyright IBM Corp. 2009, 2014
* © Copyright ILOG 1996, 2009
* All Rights Reserved.
*
* Note to U.S. Government Users Restricted Rights:
* The Software and Documentation were developed at private expense and
* are "Commercial Items" as that term is defined at 48 CFR 2.101,
* consisting of "Commercial Computer Software" and
* "Commercial Computer Software Documentation", as such terms are
* used in 48 CFR 12.212 or 48 CFR 227.7202-1 through 227.7202-4,
* as applicable.
*/
package excelxml;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import javax.swing.table.DefaultTableModel;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.FactoryConfigurationError;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import ilog.views.chart.datax.IlvColumnUtilities;
import ilog.views.chart.datax.IlvDefaultDataColumnInfo;
import ilog.views.chart.datax.adapter.IlvTableToFlatTableModel;
import ilog.views.chart.datax.flat.table.IlvFlatTableModel;
import ilog.views.util.convert.IlvConvert;
import ilog.views.util.convert.IlvConvertException;
/**
* This class reads an XML file saved by Microsoft Excel, and presents the
* sheets in the form of <code>TableModel</code> instances.
*
* Note: This class cannot handle the native Microsoft Excel (.xls) format.
*/
public class Workbook {
// Format description:
//
// Character set: UTF-8.
// Structure:
// <?xml version="1.0"?>
// <Workbook ...>
// ...
// <Worksheet ...>
// <Table ss:ExpandedColumnCount="NN" ss:ExpandedRowCount="NN" ...>
// <Column .../>
// ...
// <Row [ss:Index="NN"]>
// <Cell>
// <Data ...>VALUE</Data>
// </Cell>
// ...
// </Row>
// ...
// </Table>
// ...
// </WorkSheet>
// ...
// </Workbook>
// The entire contents of the XML file.
private Document document;
/**
* Creates a Workbook from the contents of an XML file, given as file name.
*/
public Workbook(String filename) throws IOException {
this(new File(filename).toURI().toURL());
}
/**
* Creates a Workbook from the contents of an XML file, given as an URL.
*/
public Workbook(URL file) throws IOException {
this(openStream(file));
}
/**
* Creates a Workbook from the contents of an XML file, given as an open
* stream. The stream is closed by this constructor.
*/
public Workbook(InputStream stream) throws IOException {
// Use a buffered stream, for speed.
BufferedInputStream bstream = new BufferedInputStream(stream);
InputStreamReader cstream = new InputStreamReader(bstream, "UTF-8");
// Read the XML contents into memory.
InputSource xmlsource = new InputSource(cstream);
DocumentBuilderFactory docBuilderFactory;
try {
docBuilderFactory = DocumentBuilderFactory.newInstance();
} catch (FactoryConfigurationError e) {
e.printStackTrace();
return;
}
DocumentBuilder docBuilder;
try {
docBuilder = docBuilderFactory.newDocumentBuilder();
} catch (ParserConfigurationException e) {
e.printStackTrace();
return;
}
Document doc;
try {
doc = docBuilder.parse(xmlsource);
} catch (SAXException e) {
e.printStackTrace();
return;
}
cstream.close();
bstream.close();
document = doc;
}
/**
* Opens an URL for read-only access. Like url.openStream(), except that it
* also works for Windows UNC pathnames. See Sun BR #4671171.
*/
private static InputStream openStream(URL url) throws IOException {
if ("file".equals(url.getProtocol()) && !"".equals(url.getHost()))
// Do it directly, avoiding a "Connection refused: connect" error.
return new BufferedInputStream(new FileInputStream("//" + url.getHost() + url.getPath()));
else {
// But note that URLs of the form "jar:file://HOST/...!..." work
// and don't need special treatment.
return url.openStream();
}
}
/**
* Returns the worksheet at the given index. Returns null if there are less
* than index+1 worksheets.
*
* @param columnNames
* A list of column names to use, or <code>null</code>.
* @param columnTypes
* A list of column types to use, or <code>null</code>.
*/
public IlvFlatTableModel getWorksheet(int index, String[] columnNames, Class<?>[] columnTypes) {
return getWorksheet(index, columnNames, columnTypes, 0);
}
/**
* Returns the worksheet at the given index, omitting the first rowsToSkip
* rows. Returns null if there are less than index+1 worksheets.
*
* @param columnNames
* A list of column names to use, or <code>null</code>.
* @param columnTypes
* A list of column types to use, or <code>null</code>.
* @param rowsToSkip
* The number of initial rows to skip.
*/
public IlvFlatTableModel getWorksheet(int index, String[] columnNames, Class<?>[] columnTypes, int rowsToSkip) {
return getWorksheet(index, columnNames, columnTypes, rowsToSkip, Integer.MAX_VALUE);
}
/**
* Returns the worksheet at the given index, limited to rows in the range
* minRow..maxRow. Returns null if there are less than index+1 worksheets.
*
* @param columnNames
* A list of column names to use, or <code>null</code>.
* @param columnTypes
* A list of column types to use, or <code>null</code>.
* @param minRow
* The first row to consider.
* @param maxRow
* The last row to consider.
*/
public IlvFlatTableModel getWorksheet(int index, String[] columnNames, Class<?>[] columnTypes, int minRow,
int maxRow) {
Element root = document.getDocumentElement();
if ("Workbook".equals(root.getTagName())) {
NodeList workbookChildren = root.getChildNodes();
for (int i = 0; i < workbookChildren.getLength(); i++)
if (workbookChildren.item(i) instanceof Element) {
Element level1 = (Element) workbookChildren.item(i);
if ("Worksheet".equals(level1.getTagName())) {
if (index > 0)
index--;
else if (index == 0) {
DefaultTableModel table = getWorksheetContents(level1, minRow, maxRow);
if (table != null) {
table = trimRows(table);
table = trimColumns(table);
return addColumnInfos(table, columnNames, columnTypes);
}
return null;
}
}
}
}
return null;
}
// Extract the contents of a <Worksheet...> element.
private DefaultTableModel getWorksheetContents(Element level1, int minRow, int maxRow) {
// String worksheetName = level1.getAttribute("ss:Name");
DefaultTableModel table = null;
NodeList worksheetChildren = level1.getChildNodes();
for (int j = 0; j < worksheetChildren.getLength(); j++)
if (worksheetChildren.item(j) instanceof Element) {
Element level2 = (Element) worksheetChildren.item(j);
if ("Table".equals(level2.getTagName())) {
String expandedColumnCount = level2.getAttribute("ss:ExpandedColumnCount");
String expandedRowCount = level2.getAttribute("ss:ExpandedRowCount");
int columnCount = Integer.parseInt(expandedColumnCount);
int rowCount = Integer.parseInt(expandedRowCount);
table = new DefaultTableModel(rowCount, columnCount);
int rowIndex = 0;
NodeList tableChildren = level2.getChildNodes();
for (int k = 0; k < tableChildren.getLength(); k++)
if (tableChildren.item(k) instanceof Element) {
Element level3 = (Element) tableChildren.item(k);
if ("Row".equals(level3.getTagName())) {
String index = level3.getAttribute("ss:Index");
if (index.length() > 0)
rowIndex = Integer.parseInt(index) - 1;
if (rowIndex >= minRow && rowIndex <= maxRow) {
getRowContents(level3, table, rowIndex - minRow);
}
rowIndex++;
}
}
// Care only about the first <Table...> element.
break;
}
}
return table;
}
// Extract the contents of a <Row...> element.
private void getRowContents(Element level3, DefaultTableModel table, int rowIndex) {
int columnIndex = 0;
NodeList rowChildren = level3.getChildNodes();
for (int l = 0; l < rowChildren.getLength(); l++)
if (rowChildren.item(l) instanceof Element) {
Element level4 = (Element) rowChildren.item(l);
if ("Cell".equals(level4.getTagName())) {
NodeList cellChildren = level4.getChildNodes();
for (int m = 0; m < cellChildren.getLength(); m++)
if (cellChildren.item(m) instanceof Element) {
Element level5 = (Element) cellChildren.item(m);
if ("Data".equals(level5.getTagName())) {
Object value = getCellDataValue(level5, columnIndex);
table.setValueAt(value, rowIndex, columnIndex);
// Care only about the first <Data...> element.
break;
}
}
columnIndex++;
}
}
}
private static DateFormat XMLDateFormat = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss.SSS", Locale.US);
// Extract the value of a <Data...> element.
private Object getCellDataValue(Element dataElement, int columnIndex) {
StringBuffer valueBuffer = new StringBuffer();
NodeList dataChilds = dataElement.getChildNodes();
for (int n = 0; n < dataChilds.getLength(); n++)
switch (dataChilds.item(n).getNodeType()) {
case Node.TEXT_NODE:
case Node.CDATA_SECTION_NODE:
valueBuffer.append(dataChilds.item(n).getNodeValue());
break;
}
String string = valueBuffer.toString();
String type = dataElement.getAttribute("ss:Type");
if (type.equals("Number"))
return convertToNumber(string, columnIndex);
if (type.equals("Boolean"))
return convertToBoolean(string, columnIndex);
if (type.equals("DateTime"))
return convertToDate(string, columnIndex);
// Other possible types: Error, String, ...
return string;
}
/**
* Converts a cell entry of type "Number" to a Java object, ideally of type
* Double.
*/
protected Object convertToNumber(String string, int columnIndex) {
try {
return Double.valueOf(Double.parseDouble(string));
} catch (Exception e) {
// Complain later.
return string;
}
}
/**
* Converts a cell entry of type "Boolean" to a Java object, ideally of type
* Boolean.
*/
protected Object convertToBoolean(String string, int columnIndex) {
if (string.equals("0"))
return Boolean.FALSE;
if (string.equals("1"))
return Boolean.TRUE;
return string;
}
/**
* Converts a cell entry of type "DateTime" to a Java object, ideally of type
* Date.
*/
protected Object convertToDate(String string, int columnIndex) {
try {
// FIXME: What about the time zone?
return XMLDateFormat.parseObject(string);
} catch (ParseException e) {
// Complain later.
return string;
}
}
/**
* Removes empty lines from the beginning and from the end of the given table,
* and returns the modified table,
*/
private DefaultTableModel trimRows(DefaultTableModel table) {
// Remove empty lines from the end.
while (table.getRowCount() > 0) {
int n = table.getRowCount();
int rowIndex = n - 1;
boolean blank = true;
for (int col = 0; col < table.getColumnCount(); col++)
if (table.getValueAt(rowIndex, col) != null) {
blank = false;
break;
}
if (blank)
table.removeRow(rowIndex);
else
break;
}
// Remove empty lines from the start.
while (table.getRowCount() > 0) {
int rowIndex = 0;
boolean blank = true;
for (int col = 0; col < table.getColumnCount(); col++)
if (table.getValueAt(rowIndex, col) != null) {
blank = false;
break;
}
if (blank)
table.removeRow(rowIndex);
else
break;
}
return table;
}
/**
* Removes empty lines from the right border of the given table, and returns
* the modified table,
*/
private DefaultTableModel trimColumns(DefaultTableModel table) {
// Determine the number of columns as the maximum number of fields
// in any line.
int columnCount = 0;
for (int i = 0; i < table.getRowCount(); i++)
for (int j = columnCount; j < table.getColumnCount(); j++)
if (table.getValueAt(i, j) != null)
columnCount = j + 1;
table.setColumnCount(columnCount);
return table;
}
/**
* Adds column metainformation to the given table, and converts it to an
* instance of <code>IlvFlatTableModel</code>.
*
* @param columnNames
* A list of column names to use, or <code>null</code>.
* @param columnTypes
* A list of column types to use, or <code>null</code>.
*/
private IlvFlatTableModel addColumnInfos(DefaultTableModel table, String[] columnNames, Class<?>[] columnTypes) {
int columnCount = table.getColumnCount();
IlvDefaultDataColumnInfo[] columns = new IlvDefaultDataColumnInfo[columnCount];
for (int col = 0; col < columnCount; col++) {
Class<?> type = null;
if (columnTypes != null && col < columnTypes.length)
type = columnTypes[col];
if (type != null) {
// Enforce the given type.
for (int i = 0; i < table.getRowCount(); i++) {
Object value = table.getValueAt(i, col);
if (value != null && !type.isInstance(value))
table.setValueAt(convertTo(value, type, col), i, col);
}
} else {
// Guess the type.
boolean allDouble = true;
boolean allBoolean = true;
boolean allDate = true;
for (int i = 0; i < table.getRowCount(); i++) {
Object value = table.getValueAt(i, col);
if (value != null) {
if (!(value instanceof Double))
allDouble = false;
if (!(value instanceof Boolean))
allBoolean = false;
if (!(value instanceof Date))
allDate = false;
}
}
type = (allDouble && !allBoolean && !allDate ? Double.class
: allBoolean && !allDouble && !allDate ? Boolean.class
: allDate && !allDouble && !allBoolean ? Date.class : Object.class);
}
columns[col] = new IlvDefaultDataColumnInfo(columnNames != null && col < columnNames.length ? columnNames[col]
: IlvColumnUtilities.constructColumnName(col), type);
if (type == Boolean.class)
columns[col].setEnumerated(true);
}
return new IlvTableToFlatTableModel(table, columns);
}
/**
* Converts a cell entry to the desired type.
*/
protected Object convertTo(Object object, Class<?> targetType, int columnIndex) {
try {
Object attempt = IlvConvert.convert(object, targetType);
if (targetType.isInstance(attempt))
return attempt;
} catch (IlvConvertException e) {
}
return null;
}
}