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

Example 16 with XSSFSheet

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

the class TestColumnHelper method testGetSetColDefaultStyle.

@Test
public void testGetSetColDefaultStyle() throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();
    CTWorksheet ctWorksheet = sheet.getCTWorksheet();
    ColumnHelper columnHelper = sheet.getColumnHelper();
    // POI column 3, OOXML column 4
    CTCol col = columnHelper.getOrCreateColumn1Based(4, false);
    assertNotNull(col);
    assertNotNull(columnHelper.getColumn(3, false));
    columnHelper.setColDefaultStyle(3, 2);
    assertEquals(2, columnHelper.getColDefaultStyle(3));
    assertEquals(-1, columnHelper.getColDefaultStyle(4));
    StylesTable stylesTable = workbook.getStylesSource();
    CTXf cellXf = CTXf.Factory.newInstance();
    cellXf.setFontId(0);
    cellXf.setFillId(0);
    cellXf.setBorderId(0);
    cellXf.setNumFmtId(0);
    cellXf.setXfId(0);
    stylesTable.putCellXf(cellXf);
    CTCol col_2 = ctWorksheet.getColsArray(0).addNewCol();
    col_2.setMin(10);
    col_2.setMax(12);
    col_2.setStyle(1);
    assertEquals(1, columnHelper.getColDefaultStyle(11));
    XSSFCellStyle cellStyle = new XSSFCellStyle(0, 0, stylesTable, null);
    columnHelper.setColDefaultStyle(11, cellStyle);
    assertEquals(0, col_2.getStyle());
    assertEquals(1, columnHelper.getColDefaultStyle(10));
    workbook.close();
}

Example 17 with XSSFSheet

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

the class PieChartDemo method main.

public static void main(String[] args) throws Exception {
    if (args.length < 2) {
        usage();
        return;
    }
    BufferedReader modelReader = new BufferedReader(new FileReader(args[1]));
    XMLSlideShow pptx = null;
    try {
        // first line is chart title
        String chartTitle = modelReader.readLine();
        pptx = new XMLSlideShow(new FileInputStream(args[0]));
        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();
        try {
            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 val = ser.getVal();
            CTNumData numData = val.getNumRef().getNumCache();
            // unset old axis text
            strData.setPtArray(null);
            // unset old values
            numData.setPtArray(null);
            // set model
            int idx = 0;
            int rownum = 1;
            String ln;
            while ((ln = modelReader.readLine()) != null) {
                String[] vals = ln.split("\\s+");
                CTNumVal numVal = numData.addNewPt();
                numVal.setIdx(idx);
                numVal.setV(vals[1]);
                CTStrVal sVal = strData.addNewPt();
                sVal.setIdx(idx);
                sVal.setV(vals[0]);
                idx++;
                XSSFRow row = sheet.createRow(rownum++);
                row.createCell(0).setCellValue(vals[0]);
                row.createCell(1).setCellValue(Double.valueOf(vals[1]));
            }
            numData.getPtCount().setVal(idx);
            strData.getPtCount().setVal(idx);
            String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true);
            val.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();
            try {
                wb.write(xlsOut);
            } finally {
                xlsOut.close();
            }
            // save the result
            OutputStream out = new FileOutputStream("pie-chart-demo-output.pptx");
            try {
                pptx.write(out);
            } finally {
                out.close();
            }
        } finally {
            wb.close();
        }
    } finally {
        if (pptx != null)
            pptx.close();
        modelReader.close();
    }
}

Example 18 with XSSFSheet

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

the class XSSFRowShifter method updateRowFormulas.

/**
     * Update the formulas in specified row using the formula shifting policy specified by shifter
     *
     * @param row the row to update the formulas on
     * @param shifter the formula shifting policy
     */
@Internal
public void updateRowFormulas(Row row, FormulaShifter shifter) {
    XSSFSheet sheet = (XSSFSheet) row.getSheet();
    for (Cell c : row) {
        XSSFCell cell = (XSSFCell) c;
        CTCell ctCell = cell.getCTCell();
        if (ctCell.isSetF()) {
            CTCellFormula f = ctCell.getF();
            String formula = f.getStringValue();
            if (formula.length() > 0) {
                String shiftedFormula = shiftFormula(row, formula, shifter);
                if (shiftedFormula != null) {
                    f.setStringValue(shiftedFormula);
                    if (f.getT() == STCellFormulaType.SHARED) {
                        int si = (int) f.getSi();
                        CTCellFormula sf = sheet.getSharedFormula(si);
                        sf.setStringValue(shiftedFormula);
                        updateRefInCTCellFormula(row, shifter, sf);
                    }
                }
            }
            //Range of cells which the formula applies to.
            updateRefInCTCellFormula(row, shifter, f);
        }
    }
}

Example 19 with XSSFSheet

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

the class TestXSSFImportFromXML method testImportFromXML.

@Test
public void testImportFromXML() throws IOException, XPathExpressionException, SAXException {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("CustomXMLMappings.xlsx");
    try {
        String name = "name";
        String teacher = "teacher";
        String tutor = "tutor";
        String cdl = "cdl";
        String duration = "duration";
        String topic = "topic";
        String project = "project";
        String credits = "credits";
        String testXML = "<CORSO>" + "<NOME>" + name + "</NOME>" + "<DOCENTE>" + teacher + "</DOCENTE>" + "<TUTOR>" + tutor + "</TUTOR>" + "<CDL>" + cdl + "</CDL>" + "<DURATA>" + duration + "</DURATA>" + "<ARGOMENTO>" + topic + "</ARGOMENTO>" + "<PROGETTO>" + project + "</PROGETTO>" + "<CREDITI>" + credits + "</CREDITI>" + "</CORSO>";
        XSSFMap map = wb.getMapInfo().getXSSFMapByName("CORSO_mapping");
        assertNotNull(map);
        XSSFImportFromXML importer = new XSSFImportFromXML(map);
        importer.importFromXML(testXML);
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = sheet.getRow(0);
        assertTrue(row.getCell(0).getStringCellValue().equals(name));
        assertTrue(row.getCell(1).getStringCellValue().equals(teacher));
        assertTrue(row.getCell(2).getStringCellValue().equals(tutor));
        assertTrue(row.getCell(3).getStringCellValue().equals(cdl));
        assertTrue(row.getCell(4).getStringCellValue().equals(duration));
        assertTrue(row.getCell(5).getStringCellValue().equals(topic));
        assertTrue(row.getCell(6).getStringCellValue().equals(project));
        assertTrue(row.getCell(7).getStringCellValue().equals(credits));
    } finally {
        wb.close();
    }
}

Example 20 with XSSFSheet

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

the class TestCalculationChain method test46535.

public void test46535() {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("46535.xlsx");
    CalculationChain chain = wb.getCalculationChain();
    //the bean holding the reference to the formula to be deleted
    CTCalcCell c = chain.getCTCalcChain().getCArray(0);
    int cnt = chain.getCTCalcChain().sizeOfCArray();
    assertEquals(10, c.getI());
    assertEquals("E1", c.getR());
    XSSFSheet sheet = wb.getSheet("Test");
    XSSFCell cell = sheet.getRow(0).getCell(4);
    assertEquals(CellType.FORMULA, cell.getCellTypeEnum());
    cell.setCellFormula(null);
    //the count of items is less by one
    c = chain.getCTCalcChain().getCArray(0);
    int cnt2 = chain.getCTCalcChain().sizeOfCArray();
    assertEquals(cnt - 1, cnt2);
    //the first item in the calculation chain is the former second one
    assertEquals(10, c.getI());
    assertEquals("C1", c.getR());
    assertEquals(CellType.STRING, cell.getCellTypeEnum());
    cell.setCellValue("ABC");
    assertEquals(CellType.STRING, cell.getCellTypeEnum());
}