You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2015/09/17 20:30:13 UTC
svn commit: r1703665 - in /poi/trunk: src/integrationtest/org/apache/poi/
src/ooxml/java/org/apache/poi/xssf/extractor/
src/ooxml/testcases/org/apache/poi/xssf/extractor/ test-data/spreadsheet/
Author: centic
Date: Thu Sep 17 18:30:13 2015
New Revision: 1703665
URL: http://svn.apache.org/viewvc?rev=1703665&view=rev
Log:
Apply patch from bug 57890 to add support for different datatypes in XSSFImportFromXML
Added:
poi/trunk/test-data/spreadsheet/57890.xlsx
Modified:
poi/trunk/src/integrationtest/org/apache/poi/TestAllFiles.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/extractor/XSSFImportFromXML.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFImportFromXML.java
Modified: poi/trunk/src/integrationtest/org/apache/poi/TestAllFiles.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/integrationtest/org/apache/poi/TestAllFiles.java?rev=1703665&r1=1703664&r2=1703665&view=diff
==============================================================================
--- poi/trunk/src/integrationtest/org/apache/poi/TestAllFiles.java (original)
+++ poi/trunk/src/integrationtest/org/apache/poi/TestAllFiles.java Thu Sep 17 18:30:13 2015
@@ -203,6 +203,7 @@ public class TestAllFiles {
// TODO: fails XMLExportTest, is this ok?
EXPECTED_FAILURES.add("spreadsheet/CustomXMLMapping-singleattributenamespace.xlsx");
EXPECTED_FAILURES.add("spreadsheet/55864.xlsx");
+ EXPECTED_FAILURES.add("spreadsheet/57890.xlsx");
// TODO: these fail now with some NPE/file read error because we now try to compute every value via Cell.toString()!
EXPECTED_FAILURES.add("spreadsheet/44958.xls");
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/extractor/XSSFImportFromXML.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/extractor/XSSFImportFromXML.java?rev=1703665&r1=1703664&r2=1703665&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/extractor/XSSFImportFromXML.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/extractor/XSSFImportFromXML.java Thu Sep 17 18:30:13 2015
@@ -19,8 +19,15 @@ package org.apache.poi.xssf.extractor;
import java.io.IOException;
import java.io.StringReader;
+import java.text.DateFormat;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.util.Arrays;
+import java.util.Date;
+import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
+import java.util.Set;
import javax.xml.namespace.NamespaceContext;
import javax.xml.parsers.DocumentBuilder;
@@ -30,7 +37,10 @@ import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
+import org.apache.poi.ss.usermodel.DateUtil;
+import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.DocumentHelper;
+import org.apache.poi.util.LocaleUtil;
import org.apache.poi.util.POILogFactory;
import org.apache.poi.util.POILogger;
import org.apache.poi.xssf.usermodel.XSSFCell;
@@ -39,6 +49,7 @@ import org.apache.poi.xssf.usermodel.XSS
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.helpers.XSSFSingleXmlCell;
import org.apache.poi.xssf.usermodel.helpers.XSSFXmlColumnPr;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STXmlDataType;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NamedNodeMap;
@@ -95,6 +106,7 @@ public class XSSFImportFromXML {
for (XSSFSingleXmlCell singleXmlCell : singleXmlCells) {
+ STXmlDataType.Enum xmlDataType = singleXmlCell.getXmlDataType();
String xpathString = singleXmlCell.getXpath();
Node result = (Node) xpath.evaluate(xpathString, doc, XPathConstants.NODE);
// result can be null if value is optional (xsd:minOccurs=0), see bugzilla 55864
@@ -104,7 +116,7 @@ public class XSSFImportFromXML {
XSSFCell cell = singleXmlCell.getReferencedCell();
logger.log(POILogger.DEBUG, "Setting '" + textContent + "' to cell " + cell.getColumnIndex() + "-" + cell.getRowIndex() + " in sheet "
+ cell.getSheet().getSheetName());
- cell.setCellValue(textContent);
+ setCellValue(textContent, cell, xmlDataType);
}
}
@@ -146,9 +158,71 @@ public class XSSFImportFromXML {
}
logger.log(POILogger.DEBUG, "Setting '" + value + "' to cell " + cell.getColumnIndex() + "-" + cell.getRowIndex() + " in sheet "
+ table.getXSSFSheet().getSheetName());
+ setCellValue(value, cell, xmlColumnPr.getXmlDataType());
+ }
+ }
+ }
+ }
+
+ private static enum DataType {
+ BOOLEAN(STXmlDataType.BOOLEAN), //
+ DOUBLE(STXmlDataType.DOUBLE), //
+ INTEGER(STXmlDataType.INT, STXmlDataType.UNSIGNED_INT, STXmlDataType.INTEGER), //
+ STRING(STXmlDataType.STRING), //
+ DATE(STXmlDataType.DATE);
+
+ private Set<STXmlDataType.Enum> xmlDataTypes;
+
+ private DataType(STXmlDataType.Enum... xmlDataTypes) {
+ this.xmlDataTypes = new HashSet<STXmlDataType.Enum>(Arrays.asList(xmlDataTypes));
+ }
+
+ public static DataType getDataType(STXmlDataType.Enum xmlDataType) {
+ for (DataType dataType : DataType.values()) {
+ if (dataType.xmlDataTypes.contains(xmlDataType)) {
+ return dataType;
+ }
+ }
+ return null;
+ }
+ }
+
+ private void setCellValue(String value, XSSFCell cell, STXmlDataType.Enum xmlDataType) {
+ DataType type = DataType.getDataType(xmlDataType);
+ try {
+ if (value.isEmpty() || type == null) {
+ cell.setCellValue((String) null);
+ } else {
+ switch (type) {
+ case BOOLEAN:
+ cell.setCellValue(Boolean.parseBoolean(value));
+ break;
+ case DOUBLE:
+ cell.setCellValue(Double.parseDouble(value));
+ break;
+ case INTEGER:
+ cell.setCellValue(Integer.parseInt(value));
+ break;
+ case DATE:
+ DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd", LocaleUtil.getUserLocale());
+ Date date = sdf.parse(value);
+ cell.setCellValue(date);
+ if (!DateUtil.isValidExcelDate(cell.getNumericCellValue())) {
+ cell.setCellValue(value);
+ }
+ break;
+ case STRING:
+ default:
cell.setCellValue(value.trim());
+ break;
}
}
+ } catch (IllegalArgumentException e) {
+ throw new IllegalArgumentException(String.format(LocaleUtil.getUserLocale(), "Unable to format value '%s' as %s for cell %s", value,
+ type, new CellReference(cell).formatAsString()));
+ } catch (ParseException e) {
+ throw new IllegalArgumentException(String.format(LocaleUtil.getUserLocale(), "Unable to format value '%s' as %s for cell %s", value,
+ type, new CellReference(cell).formatAsString()));
}
}
@@ -219,7 +293,7 @@ public class XSSFImportFromXML {
// Dummy implementation - not used!
@Override
- public Iterator getPrefixes(String val) {
+ public Iterator<?> getPrefixes(String val) {
return null;
}
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFImportFromXML.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFImportFromXML.java?rev=1703665&r1=1703664&r2=1703665&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFImportFromXML.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/extractor/TestXSSFImportFromXML.java Thu Sep 17 18:30:13 2015
@@ -17,6 +17,8 @@
package org.apache.poi.xssf.extractor;
+import java.text.SimpleDateFormat;
+import java.util.Date;
import junit.framework.TestCase;
@@ -129,10 +131,10 @@ public class TestXSSFImportFromXML exten
public void testSingleAttributeCellWithNamespace() throws Exception{
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("CustomXMLMapping-singleattributenamespace.xlsx");
try {
- String id = "a";
+ int id = 1;
String displayName = "dispName";
String ref="19";
- String count = "21";
+ int count = 21;
String testXML = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\" ?>"+
"<ns1:table xmlns:ns1=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" id=\""+id+"\" displayName=\""+displayName+"\" ref=\""+ref+"\">"+
@@ -146,16 +148,15 @@ public class TestXSSFImportFromXML exten
//Check for Schema element
XSSFSheet sheet=wb.getSheetAt(0);
- assertEquals(id,sheet.getRow(28).getCell(1).getStringCellValue());
- assertEquals(displayName,sheet.getRow(11).getCell(5).getStringCellValue());
- assertEquals(ref,sheet.getRow(14).getCell(7).getStringCellValue());
- assertEquals(count,sheet.getRow(18).getCell(3).getStringCellValue());
+ assertEquals(new Double(id), sheet.getRow(28).getCell(1).getNumericCellValue());
+ assertEquals(displayName, sheet.getRow(11).getCell(5).getStringCellValue());
+ assertEquals(ref, sheet.getRow(14).getCell(7).getStringCellValue());
+ assertEquals(new Double(count), sheet.getRow(18).getCell(3).getNumericCellValue());
} finally {
wb.close();
}
}
-
-
+
public void testOptionalFields_Bugzilla_55864() throws Exception {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("55864.xlsx");
try {
@@ -195,4 +196,36 @@ public class TestXSSFImportFromXML exten
wb.close();
}
}
+
+ public void testOptionalFields_Bugzilla_57890() throws Exception {
+ XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("57890.xlsx");
+
+ String testXML = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" + "<TestInfoRoot>"
+ + "<TestData>" + "<Int>" + Integer.MIN_VALUE + "</Int>" + "<UnsignedInt>12345</UnsignedInt>"
+ + "<double>1.0000123</double>" + "<Date>1991-03-14</Date>" + "</TestData>" + "</TestInfoRoot>";
+
+ XSSFMap map = wb.getMapInfo().getXSSFMapByName("TestInfoRoot_Map");
+ assertNotNull(map);
+ XSSFImportFromXML importer = new XSSFImportFromXML(map);
+
+ importer.importFromXML(testXML);
+
+ XSSFSheet sheet = wb.getSheetAt(0);
+
+ XSSFRow rowHeadings = sheet.getRow(0);
+ XSSFRow rowData = sheet.getRow(1);
+
+ assertEquals("Date", rowHeadings.getCell(0).getStringCellValue());
+ Date date = new SimpleDateFormat("yyyy-MM-dd").parse("1991-3-14");
+ assertEquals(date, rowData.getCell(0).getDateCellValue());
+
+ assertEquals("Amount Int", rowHeadings.getCell(1).getStringCellValue());
+ assertEquals(new Double(Integer.MIN_VALUE), rowData.getCell(1).getNumericCellValue());
+
+ assertEquals("Amount Double", rowHeadings.getCell(2).getStringCellValue());
+ assertEquals(1.0000123, rowData.getCell(2).getNumericCellValue());
+
+ assertEquals("Amount UnsignedInt", rowHeadings.getCell(3).getStringCellValue());
+ assertEquals(new Double(12345), rowData.getCell(3).getNumericCellValue());
+ }
}
Added: poi/trunk/test-data/spreadsheet/57890.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/57890.xlsx?rev=1703665&view=auto
==============================================================================
Binary files poi/trunk/test-data/spreadsheet/57890.xlsx (added) and poi/trunk/test-data/spreadsheet/57890.xlsx Thu Sep 17 18:30:13 2015 differ
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org