Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) 

Source Link

Document

Creates new cell range.

Usage

From source file:ro.fortsoft.wicket.pivot.exporter.PivotXlsExporter.java

License:Apache License

@Override
public void exportPivot(PivotModel pivotModel, OutputStream outputStream) throws IOException {
    PivotTableRenderModel renderModel = PivotTableRenderModel.create(pivotModel);

    HSSFWorkbook wb = new HSSFWorkbook();
    Sheet sheetData = wb.createSheet("Pivot");

    Map<Integer, Integer> rowSpanMap = new HashMap<Integer, Integer>();
    StyleContext styleContext = new StyleContext(wb);

    int rowNumber = 0;
    int maxColNum = 0;
    for (RenderRow row : renderModel.getAllRenderRows()) {
        int col = 0;
        Row poiRow = sheetData.createRow(rowNumber);
        for (RenderCell cell : row.getRenderCells()) {
            maxColNum = Math.max(maxColNum, col);

            /*// www  .j av a  2s . com
             * Check if we currently have a rowspan at this column from the
             * parent row. We only support a colspan of 1 at the moment
             * here, if rowspan > 1
             */
            Integer rowSpan = rowSpanMap.get(col);
            if (rowSpan != null) {
                rowSpan--;
                if (rowSpan == 0)
                    rowSpanMap.remove(col);
                else
                    rowSpanMap.put(col, rowSpan);
                col++;
            }

            Cell poiCell = poiRow.createCell(col);

            /*
             * Output the Value
             */
            Object rawValue = cell.getRawValue();
            if (rawValue != null) {
                if (rawValue instanceof Double) {
                    poiCell.setCellValue((Double) rawValue);
                } else {
                    poiCell.setCellValue(String.valueOf(rawValue));
                }
            }

            styleCell(poiCell, cell, styleContext);

            if (cell.getRowspan() > 1) {
                rowSpanMap.put(col, cell.getRowspan() - 1);
                sheetData.addMergedRegion(
                        new CellRangeAddress(rowNumber, rowNumber + cell.getRowspan() - 1, col, col));
            }

            /*
             * We only support colspan _OR_ rowspan. The current PivotTable
             * also doesnt have rowspan and colspan at the same time.
             */
            if (cell.getColspan() > 1) {
                sheetData.addMergedRegion(
                        new CellRangeAddress(rowNumber, rowNumber, col, col + cell.getColspan() - 1));
            }

            col++;
            for (int i = 1; i < cell.getColspan(); i++) {
                col++;
            }
        }
        rowNumber++;
    }

    autoSizeColumns(sheetData, maxColNum);

    wb.write(outputStream);
    outputStream.flush();
}

---

From source file:ro.nextreports.engine.exporter.util.XlsUtil.java

License:Apache License

/**
 * Copy a row from a sheet to another sheet
 * /*www  .  j  a  va2  s  .  c  o m*/
 * 
 * @param srcSheet the sheet to copy
 * @param destSheet the sheet to copy into
 * @param parentSheetRow the row inside destSheet where we start to copy
 * @param parentSheetColumn the column inside destSheet where we start to copy
 * @param srcRow the row to copy
 * @param destRow the row to create
 * @param styleMap style map
 *       
 */
public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, int parentSheetRow, int parentSheetColumn,
        HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) {
    // manage a list of merged zone in order to not insert two times a
    // merged zone
    Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
    destRow.setHeight(srcRow.getHeight());
    // pour chaque row
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        HSSFCell oldCell = srcRow.getCell(j); // ancienne cell         
        if (oldCell != null) {
            HSSFCell newCell = destRow.createCell(parentSheetColumn + j);
            copyCell(oldCell, newCell, styleMap);

            CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(),
                    (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {

                CellRangeAddress newMergedRegion = new CellRangeAddress(
                        parentSheetRow + mergedRegion.getFirstRow(), parentSheetRow + mergedRegion.getLastRow(),
                        parentSheetColumn + mergedRegion.getFirstColumn(),
                        parentSheetColumn + mergedRegion.getLastColumn());

                CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
    }

}

---

From source file:ro.nextreports.engine.exporter.XlsExporter.java

License:Apache License

private void renderCell(BandElement bandElement, String bandName, Object value, int gridRow, int sheetRow,
        int sheetColumn, int rowSpan, int colSpan, boolean image) {

    if (bandElement instanceof ReportBandElement) {
        colSpan = 1;//from w  w  w. j  a  va2 s  .c  om
    }
    HSSFCellStyle cellStyle = buildBandElementStyle(bandElement, value, gridRow, sheetColumn, colSpan);

    // if we have a subreport on the current grid row we have to take care of the sheetColumn
    if (ReportLayout.HEADER_BAND_NAME.equals(bandName) && (gridRow == prevSubreportFirstRow)
            && (prevSubreportLastColumn != -1)) {
        sheetColumn = prevSubreportLastColumn - prevSubreportFirstColumn - 1 + sheetColumn;
    }
    HSSFCell c = xlsRow.createCell(sheetColumn);

    if (image) {
        if ((value == null) || "".equals(value)) {
            c.setCellType(HSSFCell.CELL_TYPE_STRING);
            c.setCellValue(new HSSFRichTextString(IMAGE_NOT_FOUND));
        } else {
            try {
                ImageBandElement ibe = (ImageBandElement) bandElement;
                byte[] imageBytes = getImage((String) value, ibe.getWidth(), ibe.getHeight());
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) sheetColumn, sheetRow,
                        (short) (sheetColumn + colSpan), (sheetRow + rowSpan));
                int index = wb.addPicture(imageBytes, HSSFWorkbook.PICTURE_TYPE_JPEG);

                // image is created over the cells, so if it's height is bigger we set the row height
                short height = xlsRow.getHeight();
                int realImageHeight = getRealImageSize((String) value)[1];
                if (ibe.isScaled()) {
                    realImageHeight = ibe.getHeight();
                }
                short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
                if (imageHeight > height) {
                    xlsRow.setHeight(imageHeight);
                }

                HSSFPicture picture = patriarch.createPicture(anchor, index);
                picture.resize();
                anchor.setAnchorType(2);
            } catch (Exception ex) {
                c.setCellType(HSSFCell.CELL_TYPE_STRING);
                c.setCellValue(new HSSFRichTextString(IMAGE_NOT_LOADED));
            }
        }

        if (cellStyle != null) {
            c.setCellStyle(cellStyle);
        }

    } else {
        if (bandElement instanceof HyperlinkBandElement) {
            Hyperlink hyp = ((HyperlinkBandElement) bandElement).getHyperlink();
            HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
            link.setAddress(hyp.getUrl());
            c.setHyperlink(link);
            c.setCellValue(new HSSFRichTextString(hyp.getText()));
            c.setCellType(HSSFCell.CELL_TYPE_STRING);
        } else if (bandElement instanceof ReportBandElement) {
            Report report = ((ReportBandElement) bandElement).getReport();
            ExporterBean eb = null;
            try {
                eb = getSubreportExporterBean(report, true);
                XlsExporter subExporter = new XlsExporter(eb, cellStyle);
                subExporter.export();
                HSSFSheet subreportSheet = subExporter.getSubreportSheet();

                if (ReportLayout.HEADER_BAND_NAME.equals(bandName) && (gridRow == prevSubreportFirstRow)) {
                    // other subreports on the same header line after the first
                    sheetColumn = prevSubreportLastColumn;
                    sheetRow -= addedPageRows;
                    pageRow -= addedPageRows;
                    addedPageRows = 0;
                } else {
                    addedPageRows = subreportSheet.getLastRowNum();
                    pageRow += addedPageRows;
                    // if subreport is not on the first column we merge all cells in the columns before, between the rows subreport occupies
                    if (sheetColumn > 0) {
                        for (int i = 0; i <= sheetColumn - 1; i++) {
                            CellRangeAddress cra = new CellRangeAddress(sheetRow, pageRow, i, i);
                            regions.add(new XlsRegion(cra, null));
                        }
                    }
                }
                int cols = XlsUtil.copyToSheet(xlsSheet, sheetRow, sheetColumn, subreportSheet);
                addRegions(xlsSheet, subExporter.getSubreportRegions(), wb);
                if (ReportLayout.HEADER_BAND_NAME.equals(bandName)) {
                    prevSubreportFirstRow = gridRow;
                    prevSubreportFirstColumn = sheetColumn;
                    prevSubreportLastColumn = sheetColumn + cols;
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if ((eb != null) && (eb.getResult() != null)) {
                    eb.getResult().close();
                }
            }
        } else if (bandElement instanceof ImageColumnBandElement) {
            try {
                ImageColumnBandElement icbe = (ImageColumnBandElement) bandElement;
                String v = StringUtil.getValueAsString(value, null);
                if (StringUtil.BLOB.equals(v)) {
                    c.setCellType(HSSFCell.CELL_TYPE_STRING);
                    c.setCellValue(new HSSFRichTextString(StringUtil.BLOB));
                } else {
                    byte[] imageD = StringUtil.decodeImage(v);
                    byte[] imageBytes = getImage(imageD, icbe.getWidth(), icbe.getHeight());
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) sheetColumn, sheetRow,
                            (short) (sheetColumn + colSpan), (sheetRow + rowSpan));
                    int index = wb.addPicture(imageBytes, HSSFWorkbook.PICTURE_TYPE_JPEG);

                    // image is created over the cells, so if it's height is bigger we set the row height
                    short height = xlsRow.getHeight();
                    int realImageHeight = getRealImageSize(imageBytes)[1];
                    if (icbe.isScaled()) {
                        realImageHeight = icbe.getHeight();
                    }
                    short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
                    if (imageHeight > height) {
                        xlsRow.setHeight(imageHeight);
                    }

                    HSSFPicture picture = patriarch.createPicture(anchor, index);
                    picture.resize();
                    anchor.setAnchorType(2);
                }
            } catch (Exception e) {
                e.printStackTrace();
                c.setCellType(HSSFCell.CELL_TYPE_STRING);
                c.setCellValue(new HSSFRichTextString(IMAGE_NOT_LOADED));
            }

        } else {

            if (value == null) {
                c.setCellType(HSSFCell.CELL_TYPE_STRING);
                c.setCellValue(new HSSFRichTextString(""));
            } else if (value instanceof Number) {
                c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                c.setCellValue(((Number) value).doubleValue());
            } else {
                String pattern = null;
                if (bandElement instanceof FieldBandElement) {
                    FieldBandElement fbe = (FieldBandElement) bandElement;
                    pattern = fbe.getPattern();
                }
                if ((value instanceof java.sql.Date) || (value instanceof java.sql.Timestamp)) {
                    Date date;
                    if (value instanceof java.sql.Date) {
                        date = new Date(((java.sql.Date) value).getTime());
                    } else {
                        date = (java.sql.Timestamp) value;
                    }
                    if (cellStyle != null) {
                        if (pattern == null) {
                            // use default pattern if none selected
                            Locale locale = Locale.getDefault();
                            pattern = ((SimpleDateFormat) DateFormat.getDateInstance(SimpleDateFormat.MEDIUM,
                                    locale)).toPattern();
                        } else {
                            pattern = StringUtil.getI18nString(pattern, getReportLanguage());
                        }
                        cellStyle.setDataFormat(wb.createDataFormat().getFormat(pattern));
                    }
                    c.setCellValue(date);
                } else {
                    c.setCellType(HSSFCell.CELL_TYPE_STRING);
                    String text = StringUtil.getValueAsString(value, pattern);
                    if ((bandElement != null) && bandElement.isWrapText()) {
                        // try to interpret new line characters
                        // \\n is used here to be possible to add in designer grid cell with \n
                        if (text.contains("\\n") || text.contains("\n") || text.contains("\r")
                                || text.contains("\r\n")) {
                            String crLf = Character.toString((char) 13) + Character.toString((char) 10);
                            int lines = countLines(text);
                            if (text.contains("\r\n")) {
                                text = text.replaceAll("\r\n", crLf);
                            } else {
                                text = text.replaceAll("(\n)|(\r)|(\\\\n)", crLf);
                            }
                            c.setCellValue(text);
                            cellStyle.setWrapText(true);
                            xlsRow.setHeightInPoints(
                                    lines * (cellStyle.getFont(wb).getFontHeightInPoints() + 3));
                        } else {
                            c.setCellValue(new HSSFRichTextString(text));
                        }
                    } else {
                        c.setCellValue(new HSSFRichTextString(text));
                    }

                }
            }
        }

        if (cellStyle != null) {
            if (bandElement != null) {
                cellStyle.setRotation(bandElement.getTextRotation());
            }
            if (!(bandElement instanceof ReportBandElement)) {
                c.setCellStyle(cellStyle);
            }
        }

        if ((rowSpan > 1) || (colSpan > 1)) {
            CellRangeAddress cra = new CellRangeAddress(sheetRow, sheetRow + rowSpan - 1, sheetColumn,
                    sheetColumn + colSpan - 1);
            Border beBorder = bandElement.getBorder();
            if (hasRowRenderConditions(bandElement, gridRow, value)) {
                // for row render conditions we must keep the row border
                beBorder = border;
            }
            regions.add(new XlsRegion(cra, beBorder));
        }

    }
}

---

From source file:ru.icc.cells.ssdc.model.CTable.java

License:Apache License

public void writeToExcel(File outputFile) {
    try {//  w w w  .ja  v  a 2s. c  o m
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();

        Iterator<CRow> rows = this.rows.iterator();
        int i = 0;
        while (rows.hasNext()) {
            Row excelRow = sheet.createRow(i);
            CRow row = rows.next();

            Iterator<CCell> rcells = row.getCells();
            while (rcells.hasNext()) {
                CCell cll = rcells.next();
                excelRow.createCell(cll.getCl() - 1).setCellValue(cll.getRawText());
            }
            i++;
        }

        Iterator<CCell> cells = this.getCells();
        while (cells.hasNext()) {
            CCell c = cells.next();
            if (c.getRt() != c.getRb() || c.getCl() != c.getCr()) {
                CellRangeAddress rngAdr = new CellRangeAddress(c.getRt() - 1, c.getRb() - 1, c.getCl() - 1,
                        c.getCr() - 1);
                sheet.addMergedRegion(rngAdr);
            }
        }
        FileOutputStream fileOut = new FileOutputStream(outputFile);
        wb.write(fileOut);
        fileOut.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

---

From source file:ru.wmbdiff.ExportIntoExcel.java

License:Apache License

public void export(File file, WMBDiffNoRootTreeTableModel model) {
    logger.info("export begin");
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("WMBDiff");
    int rowNum = 0;
    //Create Header
    CellStyle style;/*from www.  j a  va  2 s  .  c o  m*/
    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    headerFont.setFontHeightInPoints((short) 10);
    style = workbook.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.WHITE.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.WHITE.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.WHITE.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.WHITE.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);

    Row row = sheet.createRow(rowNum++);
    Cell cell;
    cell = row.createCell(0);
    cell.setCellValue("Result");
    cell.setCellStyle(style);
    cell = row.createCell(1);
    cell.setCellValue("Broker");
    cell.setCellStyle(style);
    cell = row.createCell(2);
    cell.setCellValue("Execution Group");
    cell.setCellStyle(style);
    cell = row.createCell(3);
    cell.setCellValue("Name");
    cell.setCellStyle(style);
    cell = row.createCell(4);
    cell.setCellValue("Type");
    cell.setCellStyle(style);
    cell = row.createCell(5);
    cell.setCellValue("Last Modification");
    cell.setCellStyle(style);
    cell = row.createCell(6);
    cell.setCellValue("Deployment Date");
    cell.setCellStyle(style);
    cell = row.createCell(7);
    cell.setCellValue("Bar File");
    cell.setCellStyle(style);
    cell = row.createCell(8);
    cell.setCellValue("Result Description");
    cell.setCellStyle(style);
    sheet.createFreezePane(0, 1);

    List<DiffExecutionGroup> dEG = model.getDiffExecutionGroupList();
    ListIterator<DiffExecutionGroup> litr = dEG.listIterator();
    while (litr.hasNext()) {
        DiffExecutionGroup element = litr.next();
        element.getDiffResultList();
        ListIterator<DiffDeployedObjectResult> litr2 = element.getDiffResultList().listIterator();
        while (litr2.hasNext()) {
            DiffDeployedObjectResult res = litr2.next();
            switch (res.getResult()) {
            case ONLY_IN_A:
                createRow(rowNum++, sheet, res.getAObject(), "A", res.getResultDesc());
                break;
            case ONLY_IN_B:
                createRow(rowNum++, sheet, res.getBObject(), "B", res.getResultDesc());
                break;
            case EQUAL:
                createRow(rowNum++, sheet, res.getAObject(), "=", res.getResultDesc());
                createRow(rowNum++, sheet, res.getBObject(), "=", res.getResultDesc());
                sheet.groupRow(rowNum - 2, rowNum - 2);
                break;
            case DIFF:
                createRow(rowNum++, sheet, res.getAObject(), "!=", res.getResultDesc());
                createRow(rowNum++, sheet, res.getBObject(), "!=", res.getResultDesc());
                sheet.groupRow(rowNum - 2, rowNum - 2);
                break;
            }
            ;

        }
        ;
    }
    ;
    //Adjust column width to fit the contents
    for (int i = 0; i < 9; i++)
        sheet.autoSizeColumn(i);
    //set Filter
    sheet.setAutoFilter(new CellRangeAddress(0, rowNum - 1, 0, 8));
    try {
        FileOutputStream out = new FileOutputStream(file);
        workbook.write(out);
        workbook.close();
        out.close();
    } catch (Exception e) {
        logger.error("export", e);
    }
    logger.info("export end");
}

---

From source file:sample.poi.CalendarDemo.java

License:Apache License

public static void main(String[] args) throws Exception {

    Calendar calendar = Calendar.getInstance();

    SimpleDateFormat formatFile = new SimpleDateFormat("dd-MM-yyyy-hh-mm-ss");

    HSSFWorkbook wb = new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //create a sheet for each month
        Sheet sheet = wb.createSheet(months[month]);

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);//from www  . j  a v a  2 s  . c  o m
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);

        //the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short) 1);
        printSetup.setFitWidth((short) 1);

        //the header row: centered text in 48pt font
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        Cell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + "2013");
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        //header with month titles
        Row monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            //set column widths, the width is measured in units of 1/256th of a character width
            sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide
            sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide
            sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
            Cell monthCell = monthRow.createCell(i * 2);
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            Row row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                Cell dayCell_1 = row.createCell(i * 2);
                Cell dayCell_2 = row.createCell(i * 2 + 1);

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    calendar.set(Calendar.DAY_OF_MONTH, ++day);

                    if (i == 0 || i == days.length - 1) {
                        dayCell_1.setCellStyle(styles.get("weekend_left"));
                        dayCell_2.setCellStyle(styles.get("weekend_right"));
                    } else {
                        dayCell_1.setCellStyle(styles.get("workday_left"));
                        dayCell_2.setCellStyle(styles.get("workday_right"));
                    }
                } else {
                    dayCell_1.setCellStyle(styles.get("grey_left"));
                    dayCell_2.setCellStyle(styles.get("grey_right"));
                }
                cnt++;
            }
            if (calendar.get(Calendar.MONTH) > month)
                break;
        }
    }

    // Write the output to a file
    String file = "calendar.xls";

    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

---

From source file:se.inera.intyg.rehabstod.service.export.xlsx.XlsxExportServiceImpl.java

License:Open Source License

/**
 * Creates a merged span to make all filter sections the same size and style.
 *
 * @param fromColumn/*from w w w . j a  v  a  2 s .c o m*/
 * @param style
 * @param sheet
 * @param row
 */
private void createMergedCellFromColumn(int fromColumn, XSSFCellStyle style, XSSFSheet sheet, XSSFRow row) {
    // Create and style cells for the span
    for (int i = fromColumn + 1; i <= FILTER_END_COLUMN_SPAN; i++) {
        XSSFCell cell = row.createCell(i);
        cell.setCellStyle(style);

    }
    sheet.addMergedRegion(
            new CellRangeAddress(row.getRowNum(), row.getRowNum(), fromColumn, FILTER_END_COLUMN_SPAN));
}

---

From source file:se.minstrel.tools.xssfbuilder.impl.FormulaBuilderImpl.java

License:Open Source License

public String sum(int rowStart, int rowEnd, int colStart, int colEnd) {
    CellRangeAddress cra = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
    return String.format("SUM(%s)", cra.formatAsString());
}

---

From source file:se.minstrel.tools.xssfbuilder.impl.SheetBuilderImpl.java

License:Open Source License

public SheetBuilder merge(int rowStart, int rowEnd, int colStart, int colEnd) {
    CellRangeAddress cra = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
    sheet.addMergedRegion(cra);//from   ww  w . j  a v a 2s .  com
    return this;
}

---

From source file:searchPatternClassifierPackage.ScatterChart.java

License:Apache License

public static void exampeScatterChart() {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("Sheet 1");
    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;/*from  w w  w .jav a 2  s  . co m*/
    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);

    ScatterChartData data = chart.getChartDataFactory().createScatterChartData();

    ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(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.addSerie(xs, ys1);
    data.addSerie(xs, ys2);

    chart.plot(data, bottomAxis, leftAxis);

    try {
        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("ooxml-scatter-chart.xlsx");
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »