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

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. 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:packtest.CalendarDemo.java

License:Apache License

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

    Calendar calendar = Calendar.getInstance();
    if (args.length > 0)
        calendar.set(Calendar.YEAR, Integer.parseInt(args[0]));

    int year = calendar.get(Calendar.YEAR);

    XSSFWorkbook wb = new XSSFWorkbook();
    Map<String, XSSFCellStyle> 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
        XSSFSheet sheet = wb.createSheet(months[month]);

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);/* ww w. java 2s. co  m*/
        XSSFPrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setOrientation(PrintOrientation.LANDSCAPE);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

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

        //header with month titles
        XSSFRow monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            //for compatibility with HSSF we have to set column width 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));
            XSSFCell 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++) {
            XSSFRow row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                XSSFCell dayCell_1 = row.createCell(i * 2);
                XSSFCell 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
    FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx");
    wb.write(out);
    out.close();

    wb.close();
}

From source file:packtest.LineChart.java

License:Apache License

public static void main(String[] args) throws Exception {
    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;/* w w w. j  a v a2s. 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);

    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);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(Utils.getPath("ooxml-line-chart.xlsx"));
    wb.write(fileOut);
    fileOut.close();
}

From source file:packtest.MergingCells.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");

    Row row = sheet.createRow((short) 1);
    Cell cell = row.createCell((short) 1);
    cell.setCellValue(new XSSFRichTextString("This is a test of merging"));

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("merging_cells.xlsx");
    wb.write(fileOut);/*from   www .  j  av  a  2s. com*/
    fileOut.close();
}

From source file:packtest.ScatterChart.java

License:Apache License

public static void main(String[] args) throws Exception {
    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;//  w  w  w.j  a v  a 2 s . c  o 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);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(Utils.getPath("ooxml-scatter-chart.xlsx"));
    wb.write(fileOut);
    fileOut.close();
}

From source file:panel.AnalysisPanel.java

void excel(Workbook wb, String name, double xData[], double yData[]) {
    // Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet(name); //? ?
    final int NUM_OF_ROWS = 2;
    final int NUM_OF_COLUMNS = yData.length;

    // Create a row and put some cells in it. Rows are 0 based.
    Row row;/*from w ww.j a v a  2 s . c om*/
    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);
            if (rowIndex == 0) {
                cell.setCellValue(xData[colIndex]);
            }
            if (rowIndex == 1) {
                cell.setCellValue(yData[colIndex]);
            }
        }
    }

    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 25, 35);

    org.apache.poi.ss.usermodel.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));

    data.addSeries(xs, ys1);

    chart.plot(data, bottomAxis, leftAxis);

}

From source file:paysheets.PaySheetFormatter.java

/**
 * Given a workbook, will add a thick border to the job.
 * @param workbook /*w w w .  j  av  a  2 s. c o  m*/
 * @param rowIndex int value of the starting row to add border
 */
protected static void addJobBorder(HSSFWorkbook workbook, int rowIndex) {

    if (rowIndex < 0) {
        rowIndex = 0; // Protect against invalid input.
    }
    // Set the cell range
    int firstRow = rowIndex;
    int lastRow = rowIndex + 1;
    int firstCol = 0;
    int lastCol = 5;
    CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
    // Set the border style
    short borderStyle = CellStyle.BORDER_THICK;
    // Get the sheet formatting will be added to
    HSSFSheet sheet = workbook.getSheetAt(0);
    // Apply the border
    RegionUtil.setBorderBottom(borderStyle, region, sheet, workbook);
    RegionUtil.setBorderTop(borderStyle, region, sheet, workbook);
    RegionUtil.setBorderLeft(borderStyle, region, sheet, workbook);
    RegionUtil.setBorderRight(borderStyle, region, sheet, workbook);
}

From source file:pe.gob.mef.gescon.util.ExcelUtil.java

/**
 * Mtodo que establece una regin combinada de la hoja excel.
 * @param valor Valor que tendr la celda, tipo String.
 * @param filaDesde Fila desde, tipo int.
 * @param filaHasta Fila hasta, tipo int.
 * @param columnaDesde Columna desde, tipo int.
 * @param columnaHasta Columna hasta, tipo int.
 * @param estilo Estilo de las celdas agrupadas, tipo XSSFCellStyle.
 *//*from w w  w  .  j  av  a 2s  .  c  om*/
public void crearCeldaCombinada(String valor, int filaDesde, int filaHasta, int columnaDesde, int columnaHasta,
        XSSFCellStyle estilo) {
    Row fila;
    Cell celda;
    for (int ctaFilaLocal = filaDesde; ctaFilaLocal <= filaHasta; ctaFilaLocal++) {
        fila = this.hojaExcel.getRow(ctaFilaLocal);
        for (int ctaColumnaLocal = columnaDesde; ctaColumnaLocal <= columnaHasta; ctaColumnaLocal++) {
            celda = fila.createCell(ctaColumnaLocal);
            celda.setCellStyle(estilo);
            if (ctaColumnaLocal == columnaDesde) {
                celda.setCellValue(valor);
            }
        }
    }
    this.hojaExcel.addMergedRegion(new CellRangeAddress(filaDesde, filaHasta, columnaDesde, columnaHasta));
}

From source file:pl.softech.knf.ofe.opf.members.xls.export.XlsMembersWritter.java

License:Apache License

private void buildHeader(final List<Date> dates, final Sheet sheet, final int rowIdx, final int colIdx) {

    final Workbook wb = sheet.getWorkbook();
    final CreationHelper createHelper = wb.getCreationHelper();
    final CellStyle dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm-yyyy"));

    Row row = sheet.createRow(rowIdx);/* w w w  .  j  a v a  2s.  c om*/

    Cell cell = row.createCell(colIdx);
    cell.setCellValue("Open Pension Fund");

    final CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyle.setFont(createHeaderFont(wb, (short) 12));
    cell.setCellStyle(cellStyle);

    cell = row.createCell(colIdx + 1);
    cell.setCellValue("Number of members");
    cell.setCellStyle(cellStyle);
    row = sheet.createRow(rowIdx + 1);
    sheet.addMergedRegion(new CellRangeAddress(// merge Open Pension Fund
            rowIdx, // first row (0-based)
            rowIdx + 1, // last row (0-based)
            colIdx, // first column (0-based)
            colIdx // last column (0-based)
    ));

    sheet.addMergedRegion(new CellRangeAddress(// merge Number of members
            rowIdx, // first row (0-based)
            rowIdx, // last row (0-based)
            colIdx + 1, // first column (0-based)
            colIdx + dates.size() // last column (0-based)
    ));

    int colIt = colIdx + 1;
    for (final Date date : dates) {
        cell = row.createCell(colIt++);
        cell.setCellValue(date);
        cell.setCellStyle(dateCellStyle);
    }

}

From source file:poi.hssf.usermodel.examples.MergedCells.java

License:Apache License

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");

    HSSFRow row = sheet.createRow(1);/*www  .  ja v  a2s  .  co m*/
    HSSFCell cell = row.createCell(1);
    cell.setCellValue("This is a test of merging");

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
}

From source file:poi.HSSFReadWrite.java

License:Apache License

/**
 * given a filename this outputs a sample sheet with just a set of
 * rows/cells./*from   w  w w . j  a v  a  2s .c  o  m*/
 */
private static void testCreateSampleSheet(String outputFilename) throws IOException {
    int rownum;
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFCellStyle cs2 = wb.createCellStyle();
    HSSFCellStyle cs3 = wb.createCellStyle();
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    f.setFontHeightInPoints((short) 12);
    f.setColor((short) 0xA);
    f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    f2.setFontHeightInPoints((short) 10);
    f2.setColor((short) 0xf);
    f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cs.setFont(f);
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
    cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cs2.setFillPattern((short) 1); // fill w fg
    cs2.setFillForegroundColor((short) 0xA);
    cs2.setFont(f2);
    wb.setSheetName(0, "HSSF Test");
    for (rownum = 0; rownum < 300; rownum++) {
        HSSFRow r = s.createRow(rownum);
        if ((rownum % 2) == 0) {
            r.setHeight((short) 0x249);
        }

        for (int cellnum = 0; cellnum < 50; cellnum += 2) {
            HSSFCell c = r.createCell(cellnum);
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs);
            }
            c = r.createCell(cellnum + 1);
            c.setCellValue(new HSSFRichTextString("TEST"));
            // 50 characters divided by 1/20th of a point
            s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs2);
            }
        }
    }

    // draw a thick black border on the row at the bottom using BLANKS
    rownum++;
    rownum++;
    HSSFRow r = s.createRow(rownum);
    cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK);
    for (int cellnum = 0; cellnum < 50; cellnum++) {
        HSSFCell c = r.createCell(cellnum);
        c.setCellStyle(cs3);
    }
    s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3));
    s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110));

    // end draw thick black border
    // create a sheet, set its title then delete it
    wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);

    // end deleted sheet
    FileOutputStream out = new FileOutputStream(outputFilename);
    wb.write(out);
    out.close();

    //      wb.close();
}