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

Example 46 with XSSFSheet

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

the class TestXLSXResponseWriter method testPseudoFields.

@Test
public void testPseudoFields() throws Exception {
    // Use Pseudo Field
    SolrQueryRequest req = req("q", "id:1", "wt", "xlsx", "fl", "XXX:id,foo_s");
    XSSFSheet resultSheet = getWSResultForQuery(req);
    assertEquals("XXX,foo_s\n1,hi\n", getStringFromSheet(resultSheet));
    String txt = getStringFromSheet(getWSResultForQuery(req("q", "id:1", "wt", "xlsx", "fl", "XXX:id,YYY:[docid],FOO:foo_s")));
    String[] lines = txt.split("\n");
    assertEquals(2, lines.length);
    assertEquals("XXX,YYY,FOO", lines[0]);
    assertEquals("1,0,hi", lines[1]);
    //assertions specific to multiple pseudofields functions like abs, div, exists, etc.. (SOLR-5423)
    String funcText = getStringFromSheet(getWSResultForQuery(req("df", "text", "q", "*", "wt", "xlsx", "fl", "XXX:id,YYY:exists(foo_s1)")));
    String[] funcLines = funcText.split("\n");
    assertEquals(5, funcLines.length);
    assertEquals("XXX,YYY", funcLines[0]);
    assertEquals("1,false", funcLines[1]);
    assertEquals("3,false", funcLines[3]);
}

Example 47 with XSSFSheet

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

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

the class TestStructuredReferences method testTableFormulas.

@Test
public void testTableFormulas() throws Exception {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
    try {
        final FormulaEvaluator eval = new XSSFFormulaEvaluator(wb);
        final XSSFSheet tableSheet = wb.getSheet("Table");
        final XSSFSheet formulaSheet = wb.getSheet("Formulas");
        confirm(eval, tableSheet.getRow(5).getCell(0), 49);
        confirm(eval, formulaSheet.getRow(0).getCell(0), 209);
        confirm(eval, formulaSheet.getRow(1).getCell(0), "one");
        // test changing a table value, to see if the caches are properly cleared
        // Issue 59814
        // this test passes before the fix for 59814
        tableSheet.getRow(1).getCell(1).setCellValue("ONEA");
        confirm(eval, formulaSheet.getRow(1).getCell(0), "ONEA");
        // test adding a row to a table, issue 59814
        Row newRow = tableSheet.getRow(7);
        if (newRow == null)
            newRow = tableSheet.createRow(7);
        newRow.createCell(0, CellType.FORMULA).setCellFormula("\\_Prime.1[[#This Row],[@Number]]*\\_Prime.1[[#This Row],[@Number]]");
        newRow.createCell(1, CellType.STRING).setCellValue("thirteen");
        newRow.createCell(2, CellType.NUMERIC).setCellValue(13);
        // update Table
        final XSSFTable table = wb.getTable("\\_Prime.1");
        final AreaReference newArea = new AreaReference(table.getStartCellReference(), new CellReference(table.getEndRowIndex() + 1, table.getEndColIndex()));
        String newAreaStr = newArea.formatAsString();
        table.getCTTable().setRef(newAreaStr);
        table.getCTTable().getAutoFilter().setRef(newAreaStr);
        table.updateHeaders();
        table.updateReferences();
        // these fail before the fix for 59814
        confirm(eval, tableSheet.getRow(7).getCell(0), 13 * 13);
        confirm(eval, formulaSheet.getRow(0).getCell(0), 209 + 13 * 13);
    } finally {
        wb.close();
    }
}

Example 49 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet in project chilo-producer by cccties.

the class AuthorReader method read.

public void read() {
    try {
        workBook = new XSSFWorkbook(new FileInputStream(filePath.toString()));
        Iterator<XSSFSheet> sheetIte = workBook.iterator();
        while (sheetIte.hasNext()) {
            Sheet sheet = sheetIte.next();
            Map<String, List<String>> map = readSheet(sheet);
            object.put(sheet.getSheetName(), map);
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            workBook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Example 50 with XSSFSheet

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

the class KpiReportExcelCommand method createExcel.

private String createExcel(Context context) throws Exception {
    String visionOid = (String) context.get("visionOid");
    VisionVO vision = null;
    BscStructTreeObj treeObj = (BscStructTreeObj) this.getResult(context);
    for (VisionVO visionObj : treeObj.getVisions()) {
        if (visionObj.getOid().equals(visionOid)) {
            vision = visionObj;
        }
    }
    BscReportPropertyUtils.loadData();
    // 2015-04-18 add
    BscReportSupportUtils.loadExpression();
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    int row = 24;
    if (context.get("pieCanvasToData") == null || context.get("barCanvasToData") == null) {
        row = 0;
    }
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh = wb.createSheet();
    row += this.createHead(wb, sh, row, vision);
    row = this.createMainBody(wb, sh, row, vision);
    // 空一列
    row = row + 1;
    row = this.createDateRange(wb, sh, row, vision, context);
    if (context.get("pieCanvasToData") != null && context.get("barCanvasToData") != null) {
        this.putCharts(wb, sh, context);
    }
    this.putSignature(wb, sh, row + 1, context);
    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);
    out.close();
    wb = null;
    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "kpi-report.xlsx");
    file = null;
    return oid;
}