Examples with CellRangeAddress org.apache.poi.ss.util.CellRangeAddress used on opensource projects

Example 31 with CellRangeAddress

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

the class TestUnfixedBugs method testBug55752.

@Test
public void testBug55752() throws IOException {
    Workbook wb = new XSSFWorkbook();
    try {
        Sheet sheet = wb.createSheet("test");
        for (int i = 0; i < 4; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 2; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(wb.createCellStyle());
            }
        }
        // set content
        Row row1 = sheet.getRow(0);
        row1.getCell(0).setCellValue("AAA");
        Row row2 = sheet.getRow(1);
        row2.getCell(0).setCellValue("BBB");
        Row row3 = sheet.getRow(2);
        row3.getCell(0).setCellValue("CCC");
        Row row4 = sheet.getRow(3);
        row4.getCell(0).setCellValue("DDD");
        // merge cells
        CellRangeAddress range1 = new CellRangeAddress(0, 0, 0, 1);
        sheet.addMergedRegion(range1);
        CellRangeAddress range2 = new CellRangeAddress(1, 1, 0, 1);
        sheet.addMergedRegion(range2);
        CellRangeAddress range3 = new CellRangeAddress(2, 2, 0, 1);
        sheet.addMergedRegion(range3);
        assertEquals(0, range3.getFirstColumn());
        assertEquals(1, range3.getLastColumn());
        assertEquals(2, range3.getLastRow());
        CellRangeAddress range4 = new CellRangeAddress(3, 3, 0, 1);
        sheet.addMergedRegion(range4);
        // set border
        RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, range1, sheet, wb);
        row2.getCell(0).getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
        row2.getCell(1).getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
        Cell cell0 = CellUtil.getCell(row3, 0);
        CellUtil.setCellStyleProperty(cell0, CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN);
        Cell cell1 = CellUtil.getCell(row3, 1);
        CellUtil.setCellStyleProperty(cell1, CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN);
        RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, range4, sheet, wb);
        // write to file
        OutputStream stream = new FileOutputStream(new File("C:/temp/55752.xlsx"));
        try {
            wb.write(stream);
        } finally {
            stream.close();
        }
    } finally {
        wb.close();
    }
}

Example 32 with CellRangeAddress

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

the class TestXSSFChartTitle method createWorkbookWithChart.

private Workbook createWorkbookWithChart() {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("linechart");
    final int NUM_OF_ROWS = 3;
    final int NUM_OF_COLUMNS = 10;
    // Create a row and put some cells in it. Rows are 0 based.
    Row row;
    Cell cell;
    for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
        row = sheet.createRow((short) rowIndex);
        for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
            cell = row.createCell((short) colIndex);
            cell.setCellValue(colIndex * (rowIndex + 1));
        }
    }
    Drawing<?> drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
    Chart chart = drawing.createChart(anchor);
    ChartLegend legend = chart.getOrCreateLegend();
    legend.setPosition(LegendPosition.TOP_RIGHT);
    LineChartData data = chart.getChartDataFactory().createLineChartData();
    // Use a category axis for the bottom axis.
    ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
    ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
    leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
    ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
    ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
    ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
    data.addSeries(xs, ys1);
    data.addSeries(xs, ys2);
    chart.plot(data, bottomAxis, leftAxis);
    return wb;
}

Example 33 with CellRangeAddress

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

the class AbstractExcelUtils method getMergedRange.

/**
     * @param mergedRanges
     *            map of sheet merged ranges built with
     *            {@link ExcelToHtmlUtils#buildMergedRangesMap(HSSFSheet)}
     * @return {@link CellRangeAddress} from map if cell with specified row and
     *         column numbers contained in found range, <tt>null</tt> otherwise
     */
public static CellRangeAddress getMergedRange(CellRangeAddress[][] mergedRanges, int rowNumber, int columnNumber) {
    CellRangeAddress[] mergedRangeRowInfo = rowNumber < mergedRanges.length ? mergedRanges[rowNumber] : null;
    CellRangeAddress cellRangeAddress = mergedRangeRowInfo != null && columnNumber < mergedRangeRowInfo.length ? mergedRangeRowInfo[columnNumber] : null;
    return cellRangeAddress;
}

Example 34 with CellRangeAddress

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

the class TestHSSFChart method testExistingSheet1.

@Test
public void testExistingSheet1() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("49581.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFChart[] charts = HSSFChart.getSheetCharts(sheet);
    for (HSSFChart chart : charts) {
        //System.out.println( chart.getType() ) ;
        HSSFSeries[] seriesArray = chart.getSeries();
        //System.out.println( "seriesArray.length=" + seriesArray.length ) ;
        for (HSSFSeries series : seriesArray) {
            //System.out.println( "serie.getNumValues()=" + series.getNumValues() ) ;
            CellRangeAddressBase range;
            range = series.getValuesCellRange();
            //System.out.println( range.toString() ) ;
            range.setLastRow(range.getLastRow() + 1);
            series.setValuesCellRange(range);
            range = series.getCategoryLabelsCellRange();
            //System.out.println( range.toString() ) ;
            range.setLastRow(range.getLastRow() + 1);
            series.setCategoryLabelsCellRange(range);
        }
        for (int id = 0; id < 2; id++) {
            HSSFSeries newSeries = chart.createSeries();
            newSeries.setValuesCellRange(new CellRangeAddress(1 + id, 4, 3, 3));
            String oldSeriesTitle = newSeries.getSeriesTitle();
            if (oldSeriesTitle != null) {
                //System.out.println( "old series title: " + oldSeriesTitle ) ;
                newSeries.setSeriesTitle("new series");
            }
        }
    }
    HSSFChart chart = charts[2];
    chart.removeSeries(chart.getSeries()[0]);
}

Example 35 with CellRangeAddress

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

the class TestHSSFChart method testExistingSheet2.

@Test
public void testExistingSheet2() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("49581.xls");
    HSSFSheet sheet = wb.getSheetAt(1);
    HSSFChart[] charts = HSSFChart.getSheetCharts(sheet);
    assertEquals(1, charts.length);
    for (HSSFChart chart : charts) {
        HSSFSeries series;
        // Starts with one
        assertEquals(1, chart.getSeries().length);
        // Add two more
        series = chart.createSeries();
        series.setCategoryLabelsCellRange(new CellRangeAddress(3, 4, 0, 0));
        series.setValuesCellRange(new CellRangeAddress(3, 4, 1, 1));
        series = chart.createSeries();
        series.setCategoryLabelsCellRange(new CellRangeAddress(6, 7, 0, 0));
        series.setValuesCellRange(new CellRangeAddress(6, 7, 1, 1));
    }
    // Save and re-check
    wb = HSSFITestDataProvider.instance.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(1);
    assertEquals(1, HSSFChart.getSheetCharts(sheet).length);
    HSSFChart c = HSSFChart.getSheetCharts(sheet)[0];
    assertEquals(3, c.getSeries().length);
}