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:tw.edu.chit.struts.action.secretary.ReportPrintAction.java

/**
 * ????//from  ww  w  .  j  a  va2s .  c om
 * 
 * @param mapping org.apache.struts.action.ActionMapping object
 * @param form org.apache.struts.action.ActionForm object
 * @param request request javax.servlet.http.HttpServletRequest object
 * @param response response javax.servlet.http.HttpServletResponse object
 * @param sterm 
 */
@SuppressWarnings("unchecked")
private void printOpinionList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME);
    DynaActionForm aForm = (DynaActionForm) form;
    ServletContext context = request.getSession().getServletContext();

    String year = aForm.getStrings("year")[0];
    String term = aForm.getString("sterm");

    String hql = "SELECT COUNT(*), s.target FROM StdOpinionSuggestion s "
            + "WHERE s.schoolYear = ? AND s.schoolTerm = ? GROUP BY s.target";
    List<Object> ret = (List<Object>) am.find(hql, new Object[] { year, term });

    if (!ret.isEmpty()) {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("???");
        sheet.setColumnWidth(0, 3000);
        sheet.setColumnWidth(1, 3000);
        sheet.setColumnWidth(2, 5000);
        sheet.setColumnWidth(3, 2000);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

        HSSFFont fontSize16 = workbook.createFont();
        fontSize16.setFontHeightInPoints((short) 16);
        fontSize16.setFontName("Arial Unicode MS");

        HSSFFont fontSize10 = workbook.createFont();
        fontSize10.setFontHeightInPoints((short) 10);
        fontSize10.setFontName("Arial Unicode MS");

        // Header
        Toolket.setCellValue(workbook, sheet, 0, 0, "???", fontSize16,
                HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

        // Column Header
        Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        int index = 2;
        Object[] obj = null;
        for (Object o : ret) {
            obj = (Object[]) o;

            Toolket.setCellValue(workbook, sheet, index, 0, year, fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                    null);
            Toolket.setCellValue(workbook, sheet, index, 1, term, fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                    null);
            Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getOpinionDeptName((String) obj[1]),
                    fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null);
            Toolket.setCellValue(workbook, sheet, index++, 3, ((Integer) obj[0]).toString(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
        }

        File tempDir = new File(
                context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                        + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
        if (!tempDir.exists())
            tempDir.mkdirs();

        File output = new File(tempDir, "OpinionList.xls");
        FileOutputStream fos = new FileOutputStream(output);
        workbook.write(fos);
        fos.close();

        JasperReportUtils.printXlsToFrontEnd(response, output);
        output.delete();
        tempDir.delete();
    }

}

From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java

/**
 * ????//from   w w  w . j  a v a 2s. c o m
 * 
 * @param mapping org.apache.struts.action.ActionMapping object
 * @param form org.apache.struts.action.ActionForm object
 * @param request request javax.servlet.http.HttpServletRequest object
 * @param response response javax.servlet.http.HttpServletResponse object
 * @param sterm 
 */
@SuppressWarnings("unchecked")
private void printOpinionDetailList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME);
    MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME);
    DynaActionForm aForm = (DynaActionForm) form;
    ServletContext context = request.getSession().getServletContext();

    String year = aForm.getStrings("year")[0];
    String term = aForm.getString("sterm");
    String deptCode = request.getParameter("odc");

    List<StdOpinionSuggestion> ret = null;
    if (StringUtils.isNotBlank(deptCode)) {
        String hql = "FROM StdOpinionSuggestion s " + "WHERE s.schoolYear = ? AND s.schoolTerm = ? "
                + "AND s.target = ? ORDER BY s.lastModified";
        ret = (List<StdOpinionSuggestion>) am.find(hql, new Object[] { year, term, deptCode });
    } else {
        String hql = "FROM StdOpinionSuggestion s " + "WHERE s.schoolYear = ? AND s.schoolTerm = ? "
                + "ORDER BY s.lastModified";
        ret = (List<StdOpinionSuggestion>) am.find(hql, new Object[] { year, term });
    }

    if (!ret.isEmpty()) {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("???");
        sheet.setColumnWidth(0, 2500);
        sheet.setColumnWidth(1, 2500);
        sheet.setColumnWidth(2, 5000);
        sheet.setColumnWidth(3, 3000);
        sheet.setColumnWidth(4, 6000);
        sheet.setColumnWidth(5, 6000);
        sheet.setColumnWidth(6, 3000);
        sheet.setColumnWidth(7, 3000);
        sheet.setColumnWidth(8, 12000);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));

        HSSFFont fontSize16 = workbook.createFont();
        fontSize16.setFontHeightInPoints((short) 16);
        fontSize16.setFontName("Arial Unicode MS");

        HSSFFont fontSize10 = workbook.createFont();
        fontSize10.setFontHeightInPoints((short) 10);
        fontSize10.setFontName("Arial Unicode MS");

        // Header
        Toolket.setCellValue(workbook, sheet, 0, 0, "???", fontSize16,
                HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

        // Column Header
        Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, 1, 2, "???", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                true, null);
        Toolket.setCellValue(workbook, sheet, 1, 3, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                true, null);
        Toolket.setCellValue(workbook, sheet, 1, 4, "Email", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, 1, 5, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                true, null);
        Toolket.setCellValue(workbook, sheet, 1, 6, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                true, null);
        Toolket.setCellValue(workbook, sheet, 1, 7, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                true, null);
        Toolket.setCellValue(workbook, sheet, 1, 8, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                true, null);
        int index = 2;
        Student student = null;
        DateFormat df = new SimpleDateFormat("yyyy/MM/dd hh:mm");

        for (StdOpinionSuggestion s : ret) {

            Toolket.setCellValue(workbook, sheet, index, 0, year, fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                    null);
            Toolket.setCellValue(workbook, sheet, index, 1, term, fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                    null);
            Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getOpinionDeptName(s.getTarget()),
                    fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);

            student = mm.findStudentByOid(s.getStudentOid());
            Toolket.setCellValue(workbook, sheet, index, 3, student == null ? "" : student.getStudentName(),
                    fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 4, s.getEmail(), fontSize10, HSSFCellStyle.ALIGN_LEFT,
                    true, null);
            Toolket.setCellValue(workbook, sheet, index, 5, s.getTopic(), fontSize10, HSSFCellStyle.ALIGN_LEFT,
                    true, null);
            Toolket.setCellValue(workbook, sheet, index, 6, s.getPlace(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 7, df.format(s.getLastModified()), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index++, 8, s.getContent(), fontSize10,
                    HSSFCellStyle.ALIGN_LEFT, true, null);
        }

        File tempDir = new File(
                context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                        + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
        if (!tempDir.exists())
            tempDir.mkdirs();

        File output = new File(tempDir, "OpinionDetailList.xls");
        FileOutputStream fos = new FileOutputStream(output);
        workbook.write(fos);
        fos.close();

        JasperReportUtils.printXlsToFrontEnd(response, output);
        output.delete();
        tempDir.delete();
    }
}

From source file:ua.com.ecotep.unianalysis.export.XLSDataExport.java

@Override
public void exportData(String selectedFile, AnProperties props,
        ObservableList<ObservableList<Object>> exportData, List<String> columnTitles) throws Exception {
    if (selectedFile == null) {
        return;//from  w ww. ja  v  a 2s  .  c  o  m
    }
    System.setProperty("java.awt.headless", "true");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFPrintSetup ps = sheet.getPrintSetup();
    ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
    ps.setLandscape(true);

    HSSFCellStyle cellStyleT = wb.createCellStyle();
    HSSFFont font1 = wb.createFont();
    font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font1.setFontHeightInPoints((short) 8);
    cellStyleT.setFont(font1);

    int rnumber = 0;
    HSSFRow row = sheet.createRow(0);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
    HSSFCell cell = row.createCell(0);

    cell.setCellValue(
            new HSSFRichTextString(" ???  ???  - "
                    + DateConverters.getDateToStr(LocalDate.now())));
    cell.setCellStyle(cellStyleT);
    rnumber++;

    if (props != null) {

        String val = " :  " + DateConverters.getDateToStr(props.getDateFrom())
                + "  " + DateConverters.getDateToStr(props.getDateTo());
        createHeaderCell(sheet, rnumber, val, cellStyleT);
        rnumber++;
        //----------
        if (props.isSalMode()) {
            String type = props.getSalSalerType() == AnProperties.SALER_TYPES.PROFILE
                    ? " "
                    : "  ";
            val = "? " + type + "| " + props.getSaler().getNameSaler();
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
            if (props.getSalSalerType() == AnProperties.SALER_TYPES.SALER) {
                type = props.isSalDirectSales() ? "? "
                        : " ";
                val = " : " + type;
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
                if (props.isSalFixedDepartment()) {
                    val = " : ";
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

            }
        }
        if (props.isGoodMode()) {
            if (props.getGoodClassLev0() != null) {
                val = "? 1: " + props.getGoodClassLev0().getName();
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
                if (props.getGoodClassLev1() != null) {
                    val = "? 2: " + props.getGoodClassLev1().getName();
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                    if (props.getGoodClassLev2() != null) {
                        val = "? 3: " + props.getGoodClassLev2().getName();
                        createHeaderCell(sheet, rnumber, val, cellStyleT);
                        rnumber++;
                        if (props.getGoodClassLev3() != null) {
                            val = "? 4: " + props.getGoodClassLev3().getName();
                            createHeaderCell(sheet, rnumber, val, cellStyleT);
                            rnumber++;
                        }
                    }
                }

                if (props.getGoodCustomSearch() != null && !props.getGoodCustomSearch().isEmpty()) {
                    val = " : " + props.getGoodCustomSearch();
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

                if (props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() == 2) {
                    val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(0));
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                    val = " : "
                            + DateConverters.getDateToStr(props.getGoodsIndateLst().get(1));
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }
                if (!props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() > 0) {
                    val = "+: ";
                    for (LocalDate ld : props.getGoodsIndateLst()) {
                        val += DateConverters.getDateToStr(ld) + "; ";
                    }
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

            }

        }

        if (!props.getClLst().isEmpty()) {
            if (props.isClIncluded()) {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            } else {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            }

            val = "+: ";
            for (ClientBean cb : props.getClLst()) {
                val += cb.getClientCl() + "; ";
            }
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
        }

        if (!props.getVLst().isEmpty()) {
            if (props.isVIncluded()) {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            } else {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            }
            val = "";
            for (String v : props.getVLst()) {
                val += v + "; ";
            }
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
        }
    }
    //----------                                    

    HSSFCellStyle cellStyleH = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyleH.setFont(font);
    cellStyleH.setWrapText(true);
    cellStyleH.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleH.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    cellStyleH.setBorderLeft((short) 1);
    cellStyleH.setBorderRight((short) 1);
    cellStyleH.setBorderTop((short) 1);
    cellStyleH.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleHh = wb.createCellStyle();
    font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyleHh.setFont(font);
    cellStyleHh.setWrapText(true);
    cellStyleHh.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleHh.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    cellStyleHh.setBorderLeft((short) 1);
    cellStyleHh.setBorderRight((short) 1);
    cellStyleHh.setBorderTop((short) 1);
    cellStyleHh.setBorderBottom((short) 1);

    //filling table
    HSSFCellStyle cellStyleN = wb.createCellStyle();
    cellStyleN.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cellStyleN.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleN.setBorderLeft((short) 1);
    cellStyleN.setBorderRight((short) 1);
    cellStyleN.setBorderTop((short) 1);
    cellStyleN.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleI = wb.createCellStyle();
    cellStyleI.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleI.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleI.setBorderLeft((short) 1);
    cellStyleI.setBorderRight((short) 1);
    cellStyleI.setBorderTop((short) 1);
    cellStyleI.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleD = wb.createCellStyle();
    cellStyleD.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleD.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
    HSSFDataFormat df = wb.createDataFormat();
    cellStyleD.setDataFormat(df.getFormat("#,##0.0"));
    cellStyleD.setBorderLeft((short) 1);
    cellStyleD.setBorderRight((short) 1);
    cellStyleD.setBorderTop((short) 1);
    cellStyleD.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleP = wb.createCellStyle();
    cellStyleP.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleP.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);

    cellStyleP.setDataFormat(df.getFormat("0.0\\%"));
    cellStyleP.setBorderLeft((short) 1);
    cellStyleP.setBorderRight((short) 1);
    cellStyleP.setBorderTop((short) 1);
    cellStyleP.setBorderBottom((short) 1);

    // filling column headers
    row = sheet.createRow(rnumber);

    String rowTitle = null;

    row = sheet.createRow(rnumber);
    row.setHeightInPoints(40);
    cell = row.createCell(0);
    cell.setCellValue(new HSSFRichTextString("?"));
    cell.setCellStyle(cellStyleH);

    for (int t = 0; t < columnTitles.size(); t++) {
        cell = row.createCell(t + 1);
        cell.setCellValue(new HSSFRichTextString(columnTitles.get(t)));
        cell.setCellStyle(cellStyleH);
    }

    // filling table with data
    rnumber++;

    for (ObservableList<Object> line : exportData) {
        row = sheet.createRow(rnumber);
        cell = row.createCell(0);
        cell.setCellValue(new HSSFRichTextString((String) line.get(0)));
        cell.setCellStyle(cellStyleN);
        for (int i = 1; i < line.size(); i++) {
            Double val = (Double) line.get(i);
            cell = row.createCell(i);
            cell.setCellStyle(cellStyleD);
            cell.setCellValue(val);
        }
        rnumber++;
    }
    for (int t = 0; t < columnTitles.size(); t++) {
        sheet.autoSizeColumn((short) t);
    }
    saveWorkBook(wb, selectedFile);
    execute(selectedFile);
}

From source file:ua.com.ecotep.unianalysis.export.XLSDataExport.java

private void createHeaderCell(HSSFSheet sheet, int rnumber, String val, HSSFCellStyle cellStyleT) {
    HSSFRow row;//from  w w w.  j a  va 2  s  . c om
    HSSFCell cell;
    row = sheet.createRow(rnumber);
    sheet.addMergedRegion(new CellRangeAddress(rnumber, rnumber, 0, 10));
    cell = row.createCell(0);
    cell.setCellValue(new HSSFRichTextString(val));
    cell.setCellStyle(cellStyleT);
}

From source file:uk.co.certait.htmlexporter.writer.excel.ExcelTableRowWriter.java

License:Apache License

public void doMerge(int rowIndex, int columnIndex, int rowSpan, int columnSpan) {
    Cell cell = sheet.getRow(rowIndex).getCell(columnIndex);
    CellRangeAddress range = new CellRangeAddress(rowIndex, rowIndex + rowSpan - 1, columnIndex,
            columnIndex + columnSpan - 1);

    sheet.addMergedRegion(range);//  ww  w . j  a va  2  s  .co  m

    RegionUtil.setBorderBottom(cell.getCellStyle().getBorderBottom(), range, sheet, sheet.getWorkbook());
    RegionUtil.setBorderTop(cell.getCellStyle().getBorderTop(), range, sheet, sheet.getWorkbook());
    RegionUtil.setBorderLeft(cell.getCellStyle().getBorderLeft(), range, sheet, sheet.getWorkbook());
    RegionUtil.setBorderRight(cell.getCellStyle().getBorderRight(), range, sheet, sheet.getWorkbook());

    RegionUtil.setBottomBorderColor(cell.getCellStyle().getBottomBorderColor(), range, sheet,
            sheet.getWorkbook());
    RegionUtil.setTopBorderColor(cell.getCellStyle().getTopBorderColor(), range, sheet, sheet.getWorkbook());
    RegionUtil.setLeftBorderColor(cell.getCellStyle().getLeftBorderColor(), range, sheet, sheet.getWorkbook());
    RegionUtil.setRightBorderColor(cell.getCellStyle().getRightBorderColor(), range, sheet,
            sheet.getWorkbook());
}

From source file:uk.co.spudsoft.birt.emitters.excel.handlers.AbstractRealTableCellHandler.java

License:Open Source License

public void interruptCell(HandlerState state, boolean includeFormatOnly) throws BirtException {

    if (state == null) {
        System.err.println("state == null");
    } else if (state.currentSheet == null) {
        System.err.println("state.currentSheet == null");
    } else if (state.currentSheet.getRow(state.rowNum) == null) {
        System.err.println("state.currentSheet.getRow(" + state.rowNum + ") == null");
    }//ww w  .  jav a2 s .c  om

    if ((lastValue != null) || includeFormatOnly) {
        Cell currentCell = state.currentSheet.getRow(state.rowNum).getCell(column);
        if (currentCell == null) {
            log.debug("Creating cell[", state.rowNum, ",", column, "]");
            currentCell = state.currentSheet.getRow(state.rowNum).createCell(column);
        }

        ICellContent cell = (ICellContent) element;
        Area area = null;

        if ((cell.getColSpan() > 1) || (cell.getRowSpan() > 1)) {

            int endRow = state.rowNum + cell.getRowSpan() - 1;
            int endCol = state.colNum + cell.getColSpan() - 1;

            if (cell.getRowSpan() > 1) {
                log.debug("Adding row span [", state.rowNum, ",", state.colNum, "] to [", endRow, ",", endCol,
                        "]");
                area = state.addRowSpan(state.rowNum, state.colNum, endRow, endCol);
            }

            int offset = state.computeNumberSpanBefore(state.rowNum, state.colNum);
            log.debug("Offset for [", state.rowNum, ",", state.colNum, "] calculated as ", offset);
            log.debug("Merging [", state.rowNum, ",", state.colNum + offset, "] to [", endRow, ",",
                    endCol + offset, "]");
            log.debug("Should be merging ? [", state.rowNum, ",", column, "] to [", endRow, ",",
                    column + cell.getColSpan() - 1, "]");
            // CellRangeAddress newMergedRegion = new CellRangeAddress( state.rowNum, endRow, state.colNum + offset, endCol + offset );
            CellRangeAddress newMergedRegion = new CellRangeAddress(state.rowNum, endRow, column,
                    column + cell.getColSpan() - 1);
            state.currentSheet.addMergedRegion(newMergedRegion);

            colSpan = cell.getColSpan();
        }

        endCellContent(state, cell, lastElement, currentCell, area);
    }

    if (state.cellIsMergedWithBorders(state.rowNum, column)) {
        int absoluteColumn = column;
        ++state.colNum;
        --colSpan;
        while (colSpan > 0) {
            ++absoluteColumn;
            log.debug("Creating cell[", state.rowNum, ",", absoluteColumn, "]");
            Cell currentCell = state.currentSheet.getRow(state.rowNum).createCell(absoluteColumn);
            endCellContent(state, null, null, currentCell, null);
            ++state.colNum;
            --colSpan;
        }
    } else {
        state.colNum += colSpan;
    }

    state.setHandler(parent);
}

From source file:uk.co.spudsoft.birt.emitters.excel.handlers.TopLevelTableHandler.java

License:Open Source License

@Override
public void endTable(HandlerState state, ITableContent table) throws BirtException {
    super.endTable(state, table);

    boolean autoFilter = EmitterServices.booleanOption(state.getRenderOptions(), table,
            ExcelEmitter.AUTO_FILTER, false);
    if (autoFilter) {
        log.debug("Applying auto filter to [", this.startRow, ",", this.startCol, "] - [", this.endDetailsRow,
                ",", state.colNum - 1, "]");
        CellRangeAddress wholeTable = new CellRangeAddress(startRow, endDetailsRow, startCol, state.colNum - 1);
        state.currentSheet.setAutoFilter(wholeTable);
    }/*from www. ja v  a 2  s  . c o  m*/

    boolean blankRowAfterTopLevelTable = EmitterServices.booleanOption(state.getRenderOptions(), table,
            ExcelEmitter.BLANK_ROW_AFTER_TOP_LEVEL_TABLE, false);
    if (blankRowAfterTopLevelTable) {
        ++state.rowNum;
    }

    state.setHandler(parent);
}

From source file:uk.co.spudsoft.birt.emitters.excel.StyleManagerUtils.java

License:Open Source License

public void extendRows(HandlerState state, int startRow, int startCol, int endRow, int endCol) {
    for (int colNum = startCol; colNum < endCol; ++colNum) {
        Cell lastCell = null;/*from ww  w  .  ja  va 2s .  c om*/
        for (int rowNum = startRow; rowNum < endRow; ++rowNum) {
            Row row = state.currentSheet.getRow(rowNum);
            if (row != null) {
                Cell cell = row.getCell(colNum);
                if (cell != null) {
                    lastCell = cell;
                }
            }
        }
        if ((lastCell != null) && (lastCell.getRowIndex() < endRow - 1)) {
            CellRangeAddress range = new CellRangeAddress(lastCell.getRowIndex(), endRow - 1,
                    lastCell.getColumnIndex(), lastCell.getColumnIndex());
            log.debug("Extend: merging from [", range.getFirstRow(), ",", range.getFirstColumn(), "] to [",
                    range.getLastRow(), ",", range.getLastColumn(), "]");
            state.currentSheet.addMergedRegion(range);
            for (int rowNum = lastCell.getRowIndex() + 1; rowNum < endRow; ++rowNum) {
                Row row = state.currentSheet.getRow(rowNum);
                if (row == null) {
                    log.error(0,
                            "Creating a row (for column " + colNum + "), this really shouldn't be necessary",
                            null);
                    row = state.currentSheet.createRow(rowNum);
                }
                Cell cell = row.createCell(colNum);
                cell.setCellStyle(lastCell.getCellStyle());
            }
        }
    }
}

From source file:uk.gov.ofwat.fountain.api.table.POITableRenderer.java

License:Open Source License

public Workbook renderTable(XSSFWorkbook workBook, TableStructure tableStructure, DataTable table) {
    this.workBook = workBook;
    creationHelper = workBook.getCreationHelper();

    sheet = workBook.createSheet(table.getCompany().getCode() + " Table " + tableStructure.getTableName());
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);

    inputDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>();
    copyCellDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>();
    calcDataNumericStyleMap = new HashMap<Short, XSSFCellStyle>();

    yellow = new XSSFColor(new java.awt.Color(255, 255, 0));
    lightYellow = new XSSFColor(new java.awt.Color(255, 255, 224));
    lightBlue = new XSSFColor(new java.awt.Color(224, 255, 255));
    pink = new XSSFColor(new java.awt.Color(255, 204, 204));

    // Styles//from   w w w  . j a  v a 2s .com
    // Row header style
    rowHeaderStyle = workBook.createCellStyle();
    // Col header style
    colHeaderStyle = workBook.createCellStyle();
    colHeaderStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    colHeaderStyle.setFillForegroundColor(yellow);
    Font colHeaderFont = workBook.createFont();
    colHeaderFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    colHeaderStyle.setFont(colHeaderFont);
    // Copycell text data cell style
    copyCellTextStyle = workBook.createCellStyle();
    copyCellTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    copyCellTextStyle.setFillForegroundColor(pink);
    // Input text data cell style
    inputDataTextStyle = workBook.createCellStyle();
    inputDataTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    inputDataTextStyle.setFillForegroundColor(lightYellow);
    // Calc text data cell style
    calcDataTextStyle = workBook.createCellStyle();
    calcDataTextStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    calcDataTextStyle.setFillForegroundColor(lightBlue);
    // Input CG style
    inputCGStyle = workBook.createCellStyle();
    inputCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    inputCGStyle.setFillForegroundColor(lightYellow);
    // Input CG style
    copyCellCGStyle = workBook.createCellStyle();
    copyCellCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    copyCellCGStyle.setFillForegroundColor(pink);
    // Calc CG style
    calcCGStyle = workBook.createCellStyle();
    calcCGStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    calcCGStyle.setFillForegroundColor(lightBlue);

    // data format
    DataFormat format = workBook.createDataFormat();

    int rownum = 1; // starting point
    Row infoRow1 = sheet.createRow(rownum);
    CellStyle style = workBook.createCellStyle();
    Font font = workBook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(font);

    Cell titleCell1 = infoRow1.createCell(0);
    titleCell1.setCellType(Cell.CELL_TYPE_STRING);
    String DATE_FORMAT = "dd MMM yyyy h:mm";
    SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
    Calendar c1 = Calendar.getInstance(); // today
    String today = sdf.format(c1.getTime());
    RichTextString dateRts = creationHelper.createRichTextString(today + ": "
            + tableStructure.getModelPage().getModel().getCode() + " for " + table.getCompany().getName());
    titleCell1.setCellValue(dateRts);
    titleCell1.setCellStyle(style);
    sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, 9));

    rownum++;
    Row infoRow2 = sheet.createRow(rownum);
    Cell titleCell = infoRow2.createCell(0);
    titleCell.setCellType(Cell.CELL_TYPE_STRING);
    RichTextString rts = creationHelper.createRichTextString(tableStructure.getModelPage().getTable().getName()
            + " - " + tableStructure.getModelPage().getTableDescription());
    titleCell.setCellValue(rts);
    titleCell.setCellStyle(style);
    sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, 4));

    rownum++;
    rownum++;

    if (tableStructure.getModelPage().isGroupSelect()) {
        // group dropdown
        groupSelectTable(tableStructure, table, workBook, sheet, format, rownum);
    } else {
        tableWithoutGroupSelect(tableStructure, table, workBook, sheet, format, rownum);
    }
    return workBook;
}