Examples with XSSFRow org.apache.poi.xssf.usermodel.XSSFRow used on opensource projects

Example 36 with XSSFRow

use of org.apache.poi.xssf.usermodel.XSSFRow in project poi by apache.

the class TestXSSFImportFromXML method testOptionalFields_Bugzilla_55864.

@Test
public void testOptionalFields_Bugzilla_55864() throws IOException, XPathExpressionException, SAXException {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("55864.xlsx");
    try {
        String testXML = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" + "<PersonInfoRoot>" + "<PersonData>" + "<FirstName>Albert</FirstName>" + "<LastName>Einstein</LastName>" + "<BirthDate>1879-03-14</BirthDate>" + "</PersonData>" + "</PersonInfoRoot>";
        XSSFMap map = wb.getMapInfo().getXSSFMapByName("PersonInfoRoot_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("FirstName", rowHeadings.getCell(0).getStringCellValue());
        assertEquals("Albert", rowData.getCell(0).getStringCellValue());
        assertEquals("LastName", rowHeadings.getCell(1).getStringCellValue());
        assertEquals("Einstein", rowData.getCell(1).getStringCellValue());
        assertEquals("BirthDate", rowHeadings.getCell(2).getStringCellValue());
        assertEquals("1879-03-14", rowData.getCell(2).getStringCellValue());
        // Value for OptionalRating is declared optional (minOccurs=0) in 55864.xlsx
        assertEquals("OptionalRating", rowHeadings.getCell(3).getStringCellValue());
        assertNull("", rowData.getCell(3));
    } finally {
        wb.close();
    }
}

Example 37 with XSSFRow

use of org.apache.poi.xssf.usermodel.XSSFRow in project poi by apache.

the class TestXSLFChart method testFillChartTemplate.

/**
     * a modified version from POI-examples
     */
@Test
public void testFillChartTemplate() throws IOException {
    // first line is chart title
    String chartTitle = "Apache POI";
    XMLSlideShow pptx = XSLFTestDataSamples.openSampleDocument("pie-chart.pptx");
    XSLFSlide slide = pptx.getSlides().get(0);
    // find chart in the slide
    XSLFChart chart = null;
    for (POIXMLDocumentPart part : slide.getRelations()) {
        if (part instanceof XSLFChart) {
            chart = (XSLFChart) part;
            break;
        }
    }
    if (chart == null)
        throw new IllegalStateException("chart not found in the template");
    // embedded Excel workbook that holds the chart data
    POIXMLDocumentPart xlsPart = chart.getRelations().get(0);
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    CTChart ctChart = chart.getCTChart();
    CTPlotArea plotArea = ctChart.getPlotArea();
    CTPieChart pieChart = plotArea.getPieChartArray(0);
    //Pie Chart Series
    CTPieSer ser = pieChart.getSerArray(0);
    // Series Text
    CTSerTx tx = ser.getTx();
    tx.getStrRef().getStrCache().getPtArray(0).setV(chartTitle);
    sheet.createRow(0).createCell(1).setCellValue(chartTitle);
    String titleRef = new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString();
    tx.getStrRef().setF(titleRef);
    // Category Axis Data
    CTAxDataSource cat = ser.getCat();
    CTStrData strData = cat.getStrRef().getStrCache();
    // Values
    CTNumDataSource valSrc = ser.getVal();
    CTNumData numData = valSrc.getNumRef().getNumCache();
    // unset old axis text
    strData.setPtArray(null);
    // unset old values
    numData.setPtArray(null);
    Map<String, Double> pieModel = new LinkedHashMap<String, Double>();
    pieModel.put("First", 1.0);
    pieModel.put("Second", 3.0);
    pieModel.put("Third", 4.0);
    // set model
    int idx = 0;
    int rownum = 1;
    for (String key : pieModel.keySet()) {
        double val = pieModel.get(key);
        CTNumVal numVal = numData.addNewPt();
        numVal.setIdx(idx);
        numVal.setV("" + val);
        CTStrVal sVal = strData.addNewPt();
        sVal.setIdx(idx);
        sVal.setV(key);
        idx++;
        XSSFRow row = sheet.createRow(rownum++);
        row.createCell(0).setCellValue(key);
        row.createCell(1).setCellValue(val);
    }
    numData.getPtCount().setVal(idx);
    strData.getPtCount().setVal(idx);
    String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true);
    valSrc.getNumRef().setF(numDataRange);
    String axisDataRange = new CellRangeAddress(1, rownum - 1, 0, 0).formatAsString(sheet.getSheetName(), true);
    cat.getStrRef().setF(axisDataRange);
    // updated the embedded workbook with the data
    OutputStream xlsOut = xlsPart.getPackagePart().getOutputStream();
    wb.write(xlsOut);
    xlsOut.close();
    wb.close();
}

Example 38 with XSSFRow

use of org.apache.poi.xssf.usermodel.XSSFRow in project poi by apache.

the class XSSFImportFromXML method importFromXML.

/**
     * Imports an XML into the XLSX using the Custom XML mapping defined
     *
     * @param xmlInputString the XML to import
     * @throws SAXException if error occurs during XML parsing
     * @throws XPathExpressionException if error occurs during XML navigation
     * @throws ParserConfigurationException if there are problems with XML parser configuration
     * @throws IOException  if there are problems reading the input string
     */
public void importFromXML(String xmlInputString) throws SAXException, XPathExpressionException, IOException {
    DocumentBuilder builder = DocumentHelper.newDocumentBuilder();
    Document doc = builder.parse(new InputSource(new StringReader(xmlInputString.trim())));
    List<XSSFSingleXmlCell> singleXmlCells = _map.getRelatedSingleXMLCell();
    List<XSSFTable> tables = _map.getRelatedTables();
    XPathFactory xpathFactory = XPathFactory.newInstance();
    XPath xpath = xpathFactory.newXPath();
    // Setting namespace context to XPath
    // Assuming that the namespace prefix in the mapping xpath is the
    // same as the one used in the document
    xpath.setNamespaceContext(new DefaultNamespaceContext(doc));
    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
        if (result != null) {
            String textContent = result.getTextContent();
            logger.log(POILogger.DEBUG, "Extracting with xpath " + xpathString + " : value is '" + textContent + "'");
            XSSFCell cell = singleXmlCell.getReferencedCell();
            logger.log(POILogger.DEBUG, "Setting '" + textContent + "' to cell " + cell.getColumnIndex() + "-" + cell.getRowIndex() + " in sheet " + cell.getSheet().getSheetName());
            setCellValue(textContent, cell, xmlDataType);
        }
    }
    for (XSSFTable table : tables) {
        String commonXPath = table.getCommonXpath();
        NodeList result = (NodeList) xpath.evaluate(commonXPath, doc, XPathConstants.NODESET);
        // the first row contains the table header
        int rowOffset = table.getStartCellReference().getRow() + 1;
        int columnOffset = table.getStartCellReference().getCol() - 1;
        for (int i = 0; i < result.getLength(); i++) {
            // TODO: implement support for denormalized XMLs (see
            // OpenOffice part 4: chapter 3.5.1.7)
            Node singleNode = result.item(i).cloneNode(true);
            for (XSSFXmlColumnPr xmlColumnPr : table.getXmlColumnPrs()) {
                int localColumnId = (int) xmlColumnPr.getId();
                int rowId = rowOffset + i;
                int columnId = columnOffset + localColumnId;
                String localXPath = xmlColumnPr.getLocalXPath();
                localXPath = localXPath.substring(localXPath.substring(1).indexOf('/') + 2);
                // TODO: convert the data to the cell format
                String value = (String) xpath.evaluate(localXPath, singleNode, XPathConstants.STRING);
                logger.log(POILogger.DEBUG, "Extracting with xpath " + localXPath + " : value is '" + value + "'");
                XSSFRow row = table.getXSSFSheet().getRow(rowId);
                if (row == null) {
                    row = table.getXSSFSheet().createRow(rowId);
                }
                XSSFCell cell = row.getCell(columnId);
                if (cell == null) {
                    cell = row.createCell(columnId);
                }
                logger.log(POILogger.DEBUG, "Setting '" + value + "' to cell " + cell.getColumnIndex() + "-" + cell.getRowIndex() + " in sheet " + table.getXSSFSheet().getSheetName());
                setCellValue(value, cell, xmlColumnPr.getXmlDataType());
            }
        }
    }
}

Example 39 with XSSFRow

use of org.apache.poi.xssf.usermodel.XSSFRow in project poi by apache.

the class XSSFExportToXml method exportToXML.

/**
     * Exports the data in an XML stream
     *
     * @param os OutputStream in which will contain the output XML
     * @param encoding the output charset encoding
     * @param validate if true, validates the XML against the XML Schema
     * @throws SAXException If validating the document fails
     * @throws TransformerException If transforming the document fails
     */
public void exportToXML(OutputStream os, String encoding, boolean validate) throws SAXException, TransformerException {
    List<XSSFSingleXmlCell> singleXMLCells = map.getRelatedSingleXMLCell();
    List<XSSFTable> tables = map.getRelatedTables();
    String rootElement = map.getCtMap().getRootElement();
    Document doc = DocumentHelper.createDocument();
    final Element root;
    if (isNamespaceDeclared()) {
        root = doc.createElementNS(getNamespace(), rootElement);
    } else {
        root = doc.createElementNS("", rootElement);
    }
    doc.appendChild(root);
    List<String> xpaths = new Vector<String>();
    Map<String, XSSFSingleXmlCell> singleXmlCellsMappings = new HashMap<String, XSSFSingleXmlCell>();
    Map<String, XSSFTable> tableMappings = new HashMap<String, XSSFTable>();
    for (XSSFSingleXmlCell simpleXmlCell : singleXMLCells) {
        xpaths.add(simpleXmlCell.getXpath());
        singleXmlCellsMappings.put(simpleXmlCell.getXpath(), simpleXmlCell);
    }
    for (XSSFTable table : tables) {
        String commonXPath = table.getCommonXpath();
        xpaths.add(commonXPath);
        tableMappings.put(commonXPath, table);
    }
    Collections.sort(xpaths, this);
    for (String xpath : xpaths) {
        XSSFSingleXmlCell simpleXmlCell = singleXmlCellsMappings.get(xpath);
        XSSFTable table = tableMappings.get(xpath);
        if (!xpath.matches(".*\\[.*")) {
            // Exports elements and attributes mapped with simpleXmlCell
            if (simpleXmlCell != null) {
                XSSFCell cell = simpleXmlCell.getReferencedCell();
                if (cell != null) {
                    Node currentNode = getNodeByXPath(xpath, doc.getFirstChild(), doc, false);
                    mapCellOnNode(cell, currentNode);
                    //remove nodes which are empty in order to keep the output xml valid
                    if ("".equals(currentNode.getTextContent()) && currentNode.getParentNode() != null) {
                        currentNode.getParentNode().removeChild(currentNode);
                    }
                }
            }
            // Exports elements and attributes mapped with tables
            if (table != null) {
                List<XSSFXmlColumnPr> tableColumns = table.getXmlColumnPrs();
                XSSFSheet sheet = table.getXSSFSheet();
                int startRow = table.getStartCellReference().getRow();
                // In mappings created with Microsoft Excel the first row contains the table header and must be skipped
                startRow += 1;
                int endRow = table.getEndCellReference().getRow();
                for (int i = startRow; i <= endRow; i++) {
                    XSSFRow row = sheet.getRow(i);
                    Node tableRootNode = getNodeByXPath(table.getCommonXpath(), doc.getFirstChild(), doc, true);
                    short startColumnIndex = table.getStartCellReference().getCol();
                    for (int j = startColumnIndex; j <= table.getEndCellReference().getCol(); j++) {
                        XSSFCell cell = row.getCell(j);
                        if (cell != null) {
                            XSSFXmlColumnPr pointer = tableColumns.get(j - startColumnIndex);
                            String localXPath = pointer.getLocalXPath();
                            Node currentNode = getNodeByXPath(localXPath, tableRootNode, doc, false);
                            mapCellOnNode(cell, currentNode);
                        }
                    }
                }
            }
        }
    /*else {
                // TODO:  implement filtering management in xpath
            }*/
    }
    boolean isValid = true;
    if (validate) {
        isValid = isValid(doc);
    }
    if (isValid) {
        /////////////////
        //Output the XML
        //set up a transformer
        TransformerFactory transfac = TransformerFactory.newInstance();
        Transformer trans = transfac.newTransformer();
        trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
        trans.setOutputProperty(OutputKeys.INDENT, "yes");
        trans.setOutputProperty(OutputKeys.ENCODING, encoding);
        //create string from xml tree
        StreamResult result = new StreamResult(os);
        DOMSource source = new DOMSource(doc);
        trans.transform(source, result);
    }
}

Example 40 with XSSFRow

use of org.apache.poi.xssf.usermodel.XSSFRow in project poi by apache.

the class TestWorkbookProtection method testEncryptDecrypt.

@Test
public void testEncryptDecrypt() throws Exception {
    final String password = "abc123";
    final String sheetName = "TestSheet1";
    final String cellValue = "customZipEntrySource";
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet1 = workbook.createSheet(sheetName);
    XSSFRow row1 = sheet1.createRow(1);
    XSSFCell cell1 = row1.createCell(1);
    cell1.setCellValue(cellValue);
    File tf1 = TempFile.createTempFile("poitest", ".xlsx");
    FileOutputStream fos1 = new FileOutputStream(tf1);
    workbook.write(fos1);
    IOUtils.closeQuietly(fos1);
    POIFSFileSystem poiFileSystem = new POIFSFileSystem();
    EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.agile);
    Encryptor enc = encryptionInfo.getEncryptor();
    enc.confirmPassword(password);
    FileInputStream fis = new FileInputStream(tf1);
    OPCPackage opc = OPCPackage.open(fis);
    IOUtils.closeQuietly(fis);
    try {
        OutputStream os = enc.getDataStream(poiFileSystem);
        opc.save(os);
        IOUtils.closeQuietly(os);
    } finally {
        IOUtils.closeQuietly(opc);
    }
    tf1.delete();
    FileOutputStream fos2 = new FileOutputStream(tf1);
    poiFileSystem.writeFilesystem(fos2);
    IOUtils.closeQuietly(fos2);
    workbook.close();
    fis = new FileInputStream(tf1);
    POIFSFileSystem poiFileSystem2 = new POIFSFileSystem(fis);
    IOUtils.closeQuietly(fis);
    EncryptionInfo encryptionInfo2 = new EncryptionInfo(poiFileSystem2);
    Decryptor decryptor = encryptionInfo2.getDecryptor();
    decryptor.verifyPassword(password);
    XSSFWorkbook workbook2 = new XSSFWorkbook(decryptor.getDataStream(poiFileSystem2));
    workbook2.close();
    tf1.delete();
}