Search in sources :

Example 36 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress 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();
    }
}
Also used : CTStrData(org.openxmlformats.schemas.drawingml.x2006.chart.CTStrData) CTNumDataSource(org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource) POIXMLDocumentPart(org.apache.poi.POIXMLDocumentPart) OutputStream(java.io.OutputStream) FileOutputStream(java.io.FileOutputStream) CTPlotArea(org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea) CTSerTx(org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx) CellReference(org.apache.poi.ss.util.CellReference) CTAxDataSource(org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) CTChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTChart) XSSFRow(org.apache.poi.xssf.usermodel.XSSFRow) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) FileReader(java.io.FileReader) CTNumData(org.openxmlformats.schemas.drawingml.x2006.chart.CTNumData) FileInputStream(java.io.FileInputStream) CTPieChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTPieChart) CTPieSer(org.openxmlformats.schemas.drawingml.x2006.chart.CTPieSer) CTStrVal(org.openxmlformats.schemas.drawingml.x2006.chart.CTStrVal) FileOutputStream(java.io.FileOutputStream) BufferedReader(java.io.BufferedReader) CTNumVal(org.openxmlformats.schemas.drawingml.x2006.chart.CTNumVal) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress)

Example 37 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class CFHeaderBase method createEmpty.

protected void createEmpty() {
    field_3_enclosing_cell_range = new CellRangeAddress(0, 0, 0, 0);
    field_4_cell_ranges = new CellRangeAddressList();
}
Also used : CellRangeAddressList(org.apache.poi.ss.util.CellRangeAddressList) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress)

Example 38 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class CFHeaderBase method read.

protected void read(RecordInputStream in) {
    field_1_numcf = in.readShort();
    field_2_need_recalculation_and_id = in.readShort();
    field_3_enclosing_cell_range = new CellRangeAddress(in);
    field_4_cell_ranges = new CellRangeAddressList(in);
}
Also used : CellRangeAddressList(org.apache.poi.ss.util.CellRangeAddressList) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress)

Example 39 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class TestXSSFSheetMergeRegions method runTest.

private long runTest(final XSSFWorkbook wb) {
    final long start = System.currentTimeMillis();
    final XSSFSheet sheet = wb.getSheetAt(0);
    final List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
    assertEquals(50000, mergedRegions.size());
    for (CellRangeAddress cellRangeAddress : mergedRegions) {
        assertEquals(cellRangeAddress.getFirstRow(), cellRangeAddress.getLastRow());
        assertEquals(2, cellRangeAddress.getNumberOfCells());
    }
    return System.currentTimeMillis() - start;
}
Also used : CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress)

Example 40 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class TestXSSFSheetUpdateArrayFormulas method testXSSFSetArrayFormula_multiCell.

@Test
public void testXSSFSetArrayFormula_multiCell() throws IOException {
    CellRange<XSSFCell> cells;
    String formula2 = "456";
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();
    CellRangeAddress range = CellRangeAddress.valueOf("C4:C6");
    cells = sheet.setArrayFormula(formula2, range);
    assertEquals(3, cells.size());
    // sheet.setArrayFormula creates rows and cells for the designated range
    /*
         * From the spec:
         * For a multi-cell formula, the c elements for all cells except the top-left
         * cell in that range shall not have an f element;
         */
    // Check that each cell exists and that the formula text is set correctly on the first cell
    XSSFCell firstCell = cells.getTopLeftCell();
    confirmArrayFormulaCell(firstCell, "C4", formula2, "C4:C6");
    confirmArrayFormulaCell(cells.getCell(1, 0), "C5");
    confirmArrayFormulaCell(cells.getCell(2, 0), "C6");
    assertSame(firstCell, sheet.getFirstCellInArrayFormula(firstCell));
    workbook.close();
}
Also used : CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) Test(org.junit.Test)

Aggregations

CellRangeAddress (org.apache.poi.ss.util.CellRangeAddress)199 Test (org.junit.Test)74 Row (org.apache.poi.ss.usermodel.Row)35 Cell (org.apache.poi.ss.usermodel.Cell)34 Sheet (org.apache.poi.ss.usermodel.Sheet)23 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)21 ArrayList (java.util.ArrayList)20 Workbook (org.apache.poi.ss.usermodel.Workbook)19 HSSFConditionalFormattingRule (org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule)16 ConditionalFormattingRule (org.apache.poi.ss.usermodel.ConditionalFormattingRule)16 FileOutputStream (java.io.FileOutputStream)15 SheetConditionalFormatting (org.apache.poi.ss.usermodel.SheetConditionalFormatting)15 XSSFCellStyle (org.apache.poi.xssf.usermodel.XSSFCellStyle)14 XSSFFont (org.apache.poi.xssf.usermodel.XSSFFont)14 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)14 XSSFColor (org.apache.poi.xssf.usermodel.XSSFColor)13 HSSFConditionalFormatting (org.apache.poi.hssf.usermodel.HSSFConditionalFormatting)12 CellReference (org.apache.poi.ss.util.CellReference)12 HSSFSheet (org.apache.poi.hssf.usermodel.HSSFSheet)8 Ptg (org.apache.poi.ss.formula.ptg.Ptg)8