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

Example 41 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet 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 42 with XSSFSheet

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

the class TestCommentsTable method existing.

@Test
public void existing() {
    Workbook workbook = XSSFTestDataSamples.openSampleWorkbook("WithVariousData.xlsx");
    Sheet sheet1 = workbook.getSheetAt(0);
    Sheet sheet2 = workbook.getSheetAt(1);
    assertTrue(((XSSFSheet) sheet1).hasComments());
    assertFalse(((XSSFSheet) sheet2).hasComments());
    // Comments should be in C5 and C7
    Row r5 = sheet1.getRow(4);
    Row r7 = sheet1.getRow(6);
    assertNotNull(r5.getCell(2).getCellComment());
    assertNotNull(r7.getCell(2).getCellComment());
    // Check they have what we expect
    // TODO: Rich text formatting
    Comment cc5 = r5.getCell(2).getCellComment();
    Comment cc7 = r7.getCell(2).getCellComment();
    assertEquals("Nick Burch", cc5.getAuthor());
    assertEquals("Nick Burch:\nThis is a comment", cc5.getString().getString());
    assertEquals(4, cc5.getRow());
    assertEquals(2, cc5.getColumn());
    assertEquals("Nick Burch", cc7.getAuthor());
    assertEquals("Nick Burch:\nComment #1\n", cc7.getString().getString());
    assertEquals(6, cc7.getRow());
    assertEquals(2, cc7.getColumn());
}

Example 43 with XSSFSheet

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

the class TestOutlining method testOutlineGettersXSSF.

@Test
public void testOutlineGettersXSSF() throws IOException {
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
    XSSFSheet xssfSheet = xssfWorkbook.createSheet();
    xssfSheet.createRow(0);
    xssfSheet.createRow(1);
    xssfSheet.createRow(2);
    xssfSheet.createRow(3);
    xssfSheet.createRow(4);
    xssfSheet.groupRow(1, 3);
    xssfSheet.groupRow(2, 3);
    assertEquals(0, xssfSheet.getRow(0).getOutlineLevel());
    assertEquals(1, xssfSheet.getRow(1).getOutlineLevel());
    assertEquals(2, xssfSheet.getRow(2).getOutlineLevel());
    assertEquals(2, xssfSheet.getRow(3).getOutlineLevel());
    assertEquals(0, xssfSheet.getRow(4).getOutlineLevel());
    xssfWorkbook.close();
}

Example 44 with XSSFSheet

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

the class XSSFRowShifter method updateConditionalFormatting.

public void updateConditionalFormatting(FormulaShifter shifter) {
    XSSFSheet xsheet = (XSSFSheet) sheet;
    XSSFWorkbook wb = xsheet.getWorkbook();
    int sheetIndex = wb.getSheetIndex(sheet);
    //don't care, structured references not allowed in conditional formatting
    final int rowIndex = -1;
    XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
    CTWorksheet ctWorksheet = xsheet.getCTWorksheet();
    CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray();
    // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
    for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) {
        CTConditionalFormatting cf = conditionalFormattingArray[j];
        ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>();
        for (Object stRef : cf.getSqref()) {
            String[] regions = stRef.toString().split(" ");
            for (String region : regions) {
                cellRanges.add(CellRangeAddress.valueOf(region));
            }
        }
        boolean changed = false;
        List<CellRangeAddress> temp = new ArrayList<CellRangeAddress>();
        for (CellRangeAddress craOld : cellRanges) {
            CellRangeAddress craNew = shiftRange(shifter, craOld, sheetIndex);
            if (craNew == null) {
                changed = true;
                continue;
            }
            temp.add(craNew);
            if (craNew != craOld) {
                changed = true;
            }
        }
        if (changed) {
            int nRanges = temp.size();
            if (nRanges == 0) {
                ctWorksheet.removeConditionalFormatting(j);
                continue;
            }
            List<String> refs = new ArrayList<String>();
            for (CellRangeAddress a : temp) refs.add(a.formatAsString());
            cf.setSqref(refs);
        }
        for (CTCfRule cfRule : cf.getCfRuleArray()) {
            String[] formulaArray = cfRule.getFormulaArray();
            for (int i = 0; i < formulaArray.length; i++) {
                String formula = formulaArray[i];
                Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
                if (shifter.adjustFormula(ptgs, sheetIndex)) {
                    String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
                    cfRule.setFormulaArray(i, shiftedFmla);
                }
            }
        }
    }
}

Example 45 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet in project lucene-solr by apache.

the class TestXLSXResponseWriter method testStructuredDataViaBaseWriters.

@Test
public void testStructuredDataViaBaseWriters() throws IOException, Exception {
    SolrQueryResponse rsp = new SolrQueryResponse();
    // Don't send a ContentStream back, this will fall back to the configured base writer.
    // But abuse the CONTENT key to ensure writer is also checking type
    rsp.add(RawResponseWriter.CONTENT, "test");
    rsp.add("foo", "bar");
    SolrQueryRequest r = req();
    // check Content-Type
    assertEquals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", writerXlsx.getContentType(r, rsp));
    // test our basic types,and that fields come back in the requested order
    XSSFSheet resultSheet = getWSResultForQuery(req("q", "id:1", "wt", "xlsx", "fl", "id,foo_s,foo_i,foo_l,foo_b,foo_f,foo_d,foo_dt1"));
    assertEquals("id,foo_s,foo_i,foo_l,foo_b,foo_f,foo_d,foo_dt1\n1,hi,-1,12345678987654321,F,1.414,-1.0E300,2000-01-02T03:04:05Z\n", getStringFromSheet(resultSheet));
    resultSheet = getWSResultForQuery(req("q", "id:1^0", "wt", "xlsx", "fl", "id,score,foo_s"));
    // test retrieving score
    assertEquals("id,score,foo_s\n1,0.0,hi\n", getStringFromSheet(resultSheet));
    resultSheet = getWSResultForQuery(req("q", "id:1^0", "wt", "xlsx", "colname.id", "I.D.", "colwidth.id", "10", "fl", "id,score,foo_s"));
    // test override colname/width
    assertEquals("I.D.,score,foo_s\n1,0.0,hi\n", getStringFromSheet(resultSheet));
    // test colwidth (value returned is in 256ths of a character as per excel standard)
    assertEquals(10 * 256, resultSheet.getColumnWidth(0));
    resultSheet = getWSResultForQuery(req("q", "id:2", "wt", "xlsx", "fl", "id,v_ss"));
    // test multivalued
    assertEquals("id,v_ss\n2,hi; there\n", getStringFromSheet(resultSheet));
    // test retrieving fields from index
    resultSheet = getWSResultForQuery(req("q", "*:*", "wt", "xslx", "fl", "*,score"));
    String result = getStringFromSheet(resultSheet);
    for (String field : "id,foo_s,foo_i,foo_l,foo_b,foo_f,foo_d,foo_dt1,v_ss,v2_ss,score".split(",")) {
        assertTrue(result.indexOf(field) >= 0);
    }
    // test null values
    resultSheet = getWSResultForQuery(req("q", "id:2", "wt", "xlsx", "fl", "id,foo_s,v_ss"));
    assertEquals("id,foo_s,v_ss\n2,,hi; there\n", getStringFromSheet(resultSheet));
    // now test SolrDocumentList
    SolrDocument d = new SolrDocument();
    SolrDocument d1 = d;
    d.addField("id", "1");
    d.addField("foo_i", -1);
    d.addField("foo_s", "hi");
    d.addField("foo_l", "12345678987654321L");
    d.addField("foo_b", false);
    d.addField("foo_f", 1.414f);
    d.addField("foo_d", -1.0E300);
    d.addField("foo_dt1", new Date(Instant.parse("2000-01-02T03:04:05Z").toEpochMilli()));
    d.addField("score", "2.718");
    d = new SolrDocument();
    SolrDocument d2 = d;
    d.addField("id", "2");
    d.addField("v_ss", "hi");
    d.addField("v_ss", "there");
    d.addField("v2_ss", "nice");
    d.addField("v2_ss", "output");
    d.addField("score", "89.83");
    d.addField("shouldbeunstored", "foo");
    SolrDocumentList sdl = new SolrDocumentList();
    sdl.add(d1);
    sdl.add(d2);
    SolrQueryRequest req = req("q", "*:*");
    rsp = new SolrQueryResponse();
    rsp.addResponse(sdl);
    rsp.setReturnFields(new SolrReturnFields("id,foo_s", req));
    resultSheet = getWSResultForQuery(req, rsp);
    assertEquals("id,foo_s\n1,hi\n2,\n", getStringFromSheet(resultSheet));
    // try scores
    rsp.setReturnFields(new SolrReturnFields("id,score,foo_s", req));
    resultSheet = getWSResultForQuery(req, rsp);
    assertEquals("id,score,foo_s\n1,2.718,hi\n2,89.83,\n", getStringFromSheet(resultSheet));
    // get field values from docs... should be ordered and not include score unless requested
    rsp.setReturnFields(new SolrReturnFields("*", req));
    resultSheet = getWSResultForQuery(req, rsp);
    assertEquals("id,foo_i,foo_s,foo_l,foo_b,foo_f,foo_d,foo_dt1,v_ss,v2_ss\n" + "1,-1,hi,12345678987654321L,false,1.414,-1.0E300,2000-01-02T03:04:05Z,,\n" + "2,,,,,,,,hi; there,nice; output\n", getStringFromSheet(resultSheet));
    // get field values and scores - just check that the scores are there... we don't guarantee where
    rsp.setReturnFields(new SolrReturnFields("*,score", req));
    resultSheet = getWSResultForQuery(req, rsp);
    String s = getStringFromSheet(resultSheet);
    assertTrue(s.indexOf("score") >= 0 && s.indexOf("2.718") > 0 && s.indexOf("89.83") > 0);
    // Test field globs
    rsp.setReturnFields(new SolrReturnFields("id,foo*", req));
    resultSheet = getWSResultForQuery(req, rsp);
    assertEquals("id,foo_i,foo_s,foo_l,foo_b,foo_f,foo_d,foo_dt1\n" + "1,-1,hi,12345678987654321L,false,1.414,-1.0E300,2000-01-02T03:04:05Z\n" + "2,,,,,,,\n", getStringFromSheet(resultSheet));
    rsp.setReturnFields(new SolrReturnFields("id,*_d*", req));
    resultSheet = getWSResultForQuery(req, rsp);
    assertEquals("id,foo_d,foo_dt1\n" + "1,-1.0E300,2000-01-02T03:04:05Z\n" + "2,,\n", getStringFromSheet(resultSheet));
    // Test function queries
    rsp.setReturnFields(new SolrReturnFields("sum(1,1),id,exists(foo_s1),div(9,1),foo_f", req));
    resultSheet = getWSResultForQuery(req, rsp);
    assertEquals("sum(1,1),id,exists(foo_s1),div(9,1),foo_f\n" + ",1,,,1.414\n" + ",2,,,\n", getStringFromSheet(resultSheet));
    // Test transformers
    rsp.setReturnFields(new SolrReturnFields("mydocid:[docid],[explain]", req));
    resultSheet = getWSResultForQuery(req, rsp);
    assertEquals("mydocid,[explain]\n" + ",\n" + ",\n", getStringFromSheet(resultSheet));
    req.close();
}