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:HSSFReadWrite.java

License:Apache License

/**
 * given a filename this outputs a sample sheet with just a set of
 * rows/cells.//ww w.j a v  a2 s.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
    s = wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);

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

---

From source file:apm.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?//from ww  w.  jav a 2s  .  c  o  m
 * @param title ?
 * @param headerList 
 */
private void initialize(String title, List<String> headerList) {
    this.wb = new SXSSFWorkbook(500);
    this.sheet = wb.createSheet("Export");
    this.styles = createStyles(wb);
    // Create title
    if (StringUtils.isNotBlank(title)) {
        Row titleRow = sheet.createRow(rownum++);
        titleRow.setHeightInPoints(30);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(styles.get("title"));
        titleCell.setCellValue(title);
        sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(),
                titleRow.getRowNum(), headerList.size() - 1));
    }
    // Create header
    if (headerList == null) {
        throw new RuntimeException("headerList not null!");
    }
    Row headerRow = sheet.createRow(rownum++);
    headerRow.setHeightInPoints(16);
    for (int i = 0; i < headerList.size(); i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellStyle(styles.get("header"));
        String[] ss = StringUtils.split(headerList.get(i), "**", 2);
        if (ss.length == 2) {
            cell.setCellValue(ss[0]);
            Comment comment = this.sheet.createDrawingPatriarch()
                    .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            comment.setString(new XSSFRichTextString(ss[1]));
            cell.setCellComment(comment);
        } else {
            cell.setCellValue(headerList.get(i));
        }
        sheet.autoSizeColumn(i);
    }
    for (int i = 0; i < headerList.size(); i++) {
        int colWidth = sheet.getColumnWidth(i) * 2;
        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
    }
    log.debug("Initialize success.");
}

---

From source file:at.htlpinkafeld.beans.JahresuebersichtBean.java

/**
 * post processes the XLS for creating//from   w  w  w . j  av a 2s.com
 *
 * @param document xls-doc
 */
public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);

    sheet.shiftRows(0, sheet.getLastRowNum(), 2);

    HSSFRow topRow = sheet.createRow(0);

    topRow.createCell(0).setCellValue("Jahresbersicht - " + selectedYear.getYear());
    topRow.createCell(3).setCellValue("von " + selectedUser.getPersName());
    sheet.createRow(1).createCell(0).setCellValue(" ");

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

    HSSFRow header = sheet.getRow(2);
    HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);

        cell = footer.getCell(i);
        cell.setCellStyle(cellStyle);

        sheet.autoSizeColumn(i);
    }

    HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
    bottomRow.createCell(0)
            .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

}

---

From source file:at.htlpinkafeld.beans.UserDetailsBean.java

public void postProcessXLS(Object document) {
        HSSFWorkbook wb = (HSSFWorkbook) document;
        HSSFSheet sheet = wb.getSheetAt(0);

        sheet.shiftRows(0, sheet.getLastRowNum(), 2);

        HSSFRow topRow = sheet.createRow(0);

        topRow.createCell(0)//from  www .  ja va 2 s .co  m
                .setCellValue("Monatsbersicht - " + selectedDate.format(DateTimeFormatter.ofPattern("MM.yyyy")));
        topRow.createCell(7).setCellValue("von " + selectedUser);
        sheet.createRow(1).createCell(0).setCellValue(" ");

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));

        HSSFRow header = sheet.getRow(2);
        HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = header.getCell(i);
            cell.setCellStyle(cellStyle);

            cell = footer.getCell(i);
            cell.setCellStyle(cellStyle);

            sheet.autoSizeColumn(i);
        }

        HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
        bottomRow.createCell(0)
                .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

    }

---

From source file:bad.robot.excel.row.CopyRow.java

License:Apache License

private static void copyMergeRegion(Sheet worksheet, Row sourceRow, Row newRow, CellRangeAddress mergedRegion) {
    CellRangeAddress range = mergedRegion;
    if (range.getFirstRow() == sourceRow.getRowNum()) {
        int lastRow = newRow.getRowNum() + (range.getFirstRow() - range.getLastRow());
        CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), lastRow,
                range.getFirstColumn(), range.getLastColumn());
        worksheet.addMergedRegion(newCellRangeAddress);
    }//from w ww .ja  v a2s .  com
}

---

From source file:bandaru_excelreadwrite.WritetoExcel.java

public void writeSongsListToExcel(List<Song> songList) {

    /*//from  ww w .j a va  2 s  . co  m
    Use XSSF for xlsx format and for xls use HSSF
     */
    Workbook workbook = new XSSFWorkbook();

    /*
    create new sheet 
     */
    Sheet songsSheet = workbook.createSheet("Albums");

    XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle();
    /* Create XSSFFont object from the workbook */
    XSSFFont my_font = (XSSFFont) workbook.createFont();

    /*
    setting cell color
     */
    CellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

    /*
     setting Header color
     */
    CellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());
    style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style2.setAlignment(style2.ALIGN_CENTER);

    Row rowName = songsSheet.createRow(1);

    /*
    Merging the cells
     */
    songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));

    /*
    Applying style to attribute name
     */
    int nameCellIndex = 1;
    Cell namecell = rowName.createCell(nameCellIndex++);
    namecell.setCellValue("Name");
    namecell.setCellStyle(style);

    Cell cel = rowName.createCell(nameCellIndex++);
    cel.setCellValue("Bandaru, Sreekanth");

    /*
    Applying underline to Name
     */
    my_font.setUnderline(XSSFFont.U_SINGLE);
    my_style.setFont(my_font);
    /* Attaching the style to the cell */
    CellStyle combined = workbook.createCellStyle();
    combined.cloneStyleFrom(my_style);
    combined.cloneStyleFrom(style);
    combined.setAlignment(combined.ALIGN_CENTER);

    cel.setCellStyle(combined);

    /*
    Applying  colors to header 
     */
    Row rowMain = songsSheet.createRow(3);
    SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("3");

    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIME.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:F4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    /*
    setting new rule to apply alternate colors to cells having same Genre
     */
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:F5"), CellRangeAddress.valueOf("A6:F6"),
            CellRangeAddress.valueOf("A7:F7"), CellRangeAddress.valueOf("A8:F8"),
            CellRangeAddress.valueOf("A13:F13"), CellRangeAddress.valueOf("A14:F14"),
            CellRangeAddress.valueOf("A15:F15"), CellRangeAddress.valueOf("A16:F16"),
            CellRangeAddress.valueOf("A23:F23"), CellRangeAddress.valueOf("A24:F24"),
            CellRangeAddress.valueOf("A25:F25"), CellRangeAddress.valueOf("A26:F26")

    };

    /*        
    setting new rule to apply alternate colors to cells having same Fenre
     */
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill3 = rule3.createPatternFormatting();
    fill3.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:F9"), CellRangeAddress.valueOf("A10:F10"),
            CellRangeAddress.valueOf("A11:F11"), CellRangeAddress.valueOf("A12:F12"),
            CellRangeAddress.valueOf("A17:F17"), CellRangeAddress.valueOf("A18:F18"),
            CellRangeAddress.valueOf("A19:F19"), CellRangeAddress.valueOf("A20:F20"),
            CellRangeAddress.valueOf("A21:F21"), CellRangeAddress.valueOf("A22:F22"),
            CellRangeAddress.valueOf("A27:F27"), CellRangeAddress.valueOf("A28:F28"),
            CellRangeAddress.valueOf("A29:F29") };

    /*
    Applying above created rule formatting to cells
     */
    sheetCF.addConditionalFormatting(regionsAction, rule2);
    sheetCF.addConditionalFormatting(regionsAdv, rule3);

    /*
     Setting coloumn header values
     */
    int mainCellIndex = 0;
    CellStyle style4 = workbook.createCellStyle();
    XSSFFont my_font2 = (XSSFFont) workbook.createFont();
    my_font2.setBold(true);
    style4.setFont(my_font2);
    rowMain.setRowStyle(style4);
    rowMain.createCell(mainCellIndex++).setCellValue("SNO");
    rowMain.createCell(mainCellIndex++).setCellValue("Genre");
    rowMain.createCell(mainCellIndex++).setCellValue("Rating");
    rowMain.createCell(mainCellIndex++).setCellValue("Movie Name");
    rowMain.createCell(mainCellIndex++).setCellValue("Director");
    rowMain.createCell(mainCellIndex++).setCellValue("Release Date");

    /*
    populating cell values
     */
    int rowIndex = 4;
    int sno = 1;
    for (Song song : songList) {
        if (song.getSno() != 0) {

            Row row = songsSheet.createRow(rowIndex++);
            int cellIndex = 0;

            /*
            first place in row is Sno
             */
            row.createCell(cellIndex++).setCellValue(sno++);

            /*
            second place in row is  Genre
             */
            row.createCell(cellIndex++).setCellValue(song.getGenre());

            /*
            third place in row is Critic score
             */
            row.createCell(cellIndex++).setCellValue(song.getCriticscore());

            /*
            fourth place in row is Album name
             */
            row.createCell(cellIndex++).setCellValue(song.getAlbumname());

            /*
            fifth place in row is Artist
             */
            row.createCell(cellIndex++).setCellValue(song.getArtist());

            /*
            sixth place in row is marks in date
             */
            if (song.getReleasedate() != null) {

                Cell date = row.createCell(cellIndex++);

                DataFormat format = workbook.createDataFormat();
                CellStyle dateStyle = workbook.createCellStyle();

                dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy"));
                date.setCellStyle(dateStyle);

                date.setCellValue(song.getReleasedate());

                /*
                auto-resizing columns
                 */
                songsSheet.autoSizeColumn(6);
                songsSheet.autoSizeColumn(5);
                songsSheet.autoSizeColumn(4);
                songsSheet.autoSizeColumn(3);
                songsSheet.autoSizeColumn(2);
            }

        }
    }

    /*
    writing this workbook to excel file.
     */
    try {
        FileOutputStream fos = new FileOutputStream(FILE_PATH);
        workbook.write(fos);
        fos.close();

        System.out.println(FILE_PATH + " is successfully written");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

---

From source file:br.com.tecsinapse.dataio.util.WorkbookUtil.java

License:LGPL

public Workbook toWorkBook(Workbook wb, Table table) {
    List<List<TableCell>> matrix = table.getCells();
    List<List<TableCell>> matrixFull = table.toTableCellMatrix();

    replaceColorsPallete(table.getColorsReplaceMap(), wb);

    String sheetName = table.getTitle();
    Sheet sheet = sheetName == null ? wb.createSheet() : wb.createSheet(sheetName);
    int titleRows = 0;
    int r = titleRows;
    int c = 0;/*w w  w.j a v  a2  s . c  o m*/
    int maxColumns = -1;
    Map<Integer, Integer> defaultColumnWidth = new HashMap<>();

    ExporterFormatter tableExporterFormatter = table.getExporterFormatter();

    for (List<TableCell> row : matrix) {
        Row sheetRow = sheet.createRow(r);

        for (TableCell tableCell : row) {
            while (matrixFull.get(r - titleRows).get(c) == EmptyTableCell.EMPTY_CELL) {
                c++;
                if (c >= matrixFull.get(r - titleRows).size()) {
                    c = 0;
                    r++;
                }
            }

            Cell cell = sheetRow.createCell(c);
            if (c > maxColumns) {
                maxColumns = c;
            }

            if (tableCell.getRowspan() > 1 || tableCell.getColspan() > 1) {
                int rowStart = r;
                int rowEnd = rowStart + (tableCell.getRowspan() - 1);
                int colStart = c;
                int colEnd = colStart + (tableCell.getColspan() - 1);

                CellRangeAddress cellRange = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
                sheet.addMergedRegion(cellRange);

                RegionUtil.setBorderTop(BorderStyle.THIN, cellRange, sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cellRange, sheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN, cellRange, sheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cellRange, sheet);
            } else if (!table.isAutoSizeColumnSheet()) {
                Integer maxColumnWidth = defaultColumnWidth.get(c);
                if (maxColumnWidth == null) {
                    defaultColumnWidth.put(c, tableCell.getDefaultColumnWidth());
                } else {
                    int defaultWidth = tableCell.getDefaultColumnWidth();
                    if (defaultWidth > maxColumnWidth) {
                        defaultColumnWidth.put(c, defaultWidth);
                    }
                }
            }

            String format = setConvertedValue(cell, tableCell, tableExporterFormatter);
            setCellStyle(cell, tableCell, wb, format);
            c++;
        }
        r++;
        c = 0;
    }

    if (table.isAutoSizeColumnSheet()) {
        for (int i = 0; i <= maxColumns; ++i) {
            if (sheet instanceof SXSSFSheet) {
                ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
            }
            sheet.autoSizeColumn(i, true);
        }
    } else {
        for (int i = 0; i <= maxColumns; ++i) {
            if (defaultColumnWidth.get(i) == null) {
                if (sheet instanceof SXSSFSheet) {
                    ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
                }
                sheet.autoSizeColumn(i, true);
            } else {
                int width = table.getMinOrMaxOrActualCellWidth(defaultColumnWidth.get(i));
                sheet.setColumnWidth(i, width);
            }
        }
    }
    return wb;
}

---

From source file:br.com.tecsinapse.exporter.util.WorkbookUtil.java

License:LGPL

public Workbook toWorkBook(Workbook wb, Table table) {
    List<List<TableCell>> matrix = table.getCells();
    List<List<TableCell>> matrixFull = table.toTableCellMatrix();

    String sheetName = table.getTitle();
    Sheet sheet = sheetName == null ? wb.createSheet() : wb.createSheet(sheetName);
    int titleRows = 0;
    int r = titleRows;
    int c = 0;//from ww  w .  ja v  a2  s. c om
    int maxColumns = -1;
    Map<Integer, Integer> defaultColumnWidth = new HashMap<>();

    ExporterFormatter tableExporterFormatter = table.getExporterFormatter();

    for (List<TableCell> row : matrix) {
        Row sheetRow = sheet.createRow(r);

        for (TableCell tableCell : row) {
            while (matrixFull.get(r - titleRows).get(c) == EmptyTableCell.EMPTY_CELL) {
                c++;
                if (c >= matrixFull.get(r - titleRows).size()) {
                    c = 0;
                    r++;
                }
            }

            Cell cell = sheetRow.createCell(c);
            if (c > maxColumns) {
                maxColumns = c;
            }

            if (tableCell.getRowspan() > 1 || tableCell.getColspan() > 1) {
                int rowStart = r;
                int rowEnd = rowStart + (tableCell.getRowspan() - 1);
                int colStart = c;
                int colEnd = colStart + (tableCell.getColspan() - 1);

                CellRangeAddress cellRange = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
                sheet.addMergedRegion(cellRange);

                RegionUtil.setBorderTop(1, cellRange, sheet, wb);
                RegionUtil.setBorderRight(1, cellRange, sheet, wb);
                RegionUtil.setBorderBottom(1, cellRange, sheet, wb);
                RegionUtil.setBorderLeft(1, cellRange, sheet, wb);
            } else if (!table.isAutoSizeColumnSheet()) {
                Integer maxColumnWidth = defaultColumnWidth.get(c);
                if (maxColumnWidth == null) {
                    defaultColumnWidth.put(c, tableCell.getDefaultColumnWidth());
                } else {
                    int defaultWidth = tableCell.getDefaultColumnWidth();
                    if (defaultWidth > maxColumnWidth) {
                        defaultColumnWidth.put(c, defaultWidth);
                    }
                }
            }

            String format = setConvertedValue(cell, tableCell, tableExporterFormatter);
            setCellStyle(cell, tableCell, wb, format);
            c++;
        }
        r++;
        c = 0;
    }

    if (table.isAutoSizeColumnSheet()) {
        for (int i = 0; i <= maxColumns; ++i) {
            if (sheet instanceof SXSSFSheet) {
                ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
            } else {
                sheet.autoSizeColumn(i, true);
            }
        }
    } else {
        for (int i = 0; i <= maxColumns; ++i) {
            if (defaultColumnWidth.get(i) == null) {
                if (sheet instanceof SXSSFSheet) {
                    ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
                } else {
                    sheet.autoSizeColumn(i, true);
                }
            } else {
                sheet.setColumnWidth(i, defaultColumnWidth.get(i));
            }
        }
    }
    return wb;
}

---

From source file:br.edu.tglima.model.result.PlanilhaXLS.java

License:Open Source License

/**
 * Mtodo responsvel por gerar a planilha XLS.
 * //from  www  . jav a2  s  .  c  o  m
 * 
 * @param arquivo Referente ao local e nome do arquivo.
 * @return Retorno do tipo Boolean, indicando se o arquivo foi gerado
 * com sucesso, ou no.
 */
public boolean gerarPlanilha(File arquivo) {

    try {

        /*Verificamos se j existe um arquivo com esse "nome".
        Caso ele exista, ele ser removido e o novo arquivo ser gerado.*/
        if (workbook.getNumberOfSheets() > 0) {
            workbook.removeSheetAt(0);
        }

        HSSFSheet sheet = workbook.createSheet("Valores Exportados");

        /*         Criando as linhas. --------------------------------------------- */

        HSSFRow header1 = sheet.createRow((short) 0);
        HSSFRow linha02 = sheet.createRow((short) 1);
        HSSFRow linha03 = sheet.createRow((short) 2);
        HSSFRow linha04 = sheet.createRow((short) 3);
        HSSFRow linha05 = sheet.createRow((short) 4);

        HSSFRow header2 = sheet.createRow((short) 7);
        HSSFRow linha09 = sheet.createRow((short) 8);
        HSSFRow linha10 = sheet.createRow((short) 9);
        HSSFRow linha11 = sheet.createRow((short) 10);
        HSSFRow linha12 = sheet.createRow((short) 11);
        HSSFRow linha13 = sheet.createRow((short) 12);
        HSSFRow linha14 = sheet.createRow((short) 13);
        HSSFRow linha15 = sheet.createRow((short) 14);
        HSSFRow linha17 = sheet.createRow((short) 16);

        HSSFRow header3 = sheet.createRow((short) 19);
        HSSFRow linha21 = sheet.createRow((short) 20);
        HSSFRow linha22 = sheet.createRow((short) 21);
        HSSFRow linha23 = sheet.createRow((short) 22);
        HSSFRow linha25 = sheet.createRow((short) 24);

        //  ------------------------------------------------------------------------ //

        /*         Mesclando as clulas. ------------------------------------------ */

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
        sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 2));
        sheet.addMergedRegion(new CellRangeAddress(19, 19, 0, 2));

        //  ------------------------------------------------------------------------ //

        /*         Definindo a largura das colunas. ------------------------------- */

        sheet.setColumnWidth(0, 12000);
        sheet.setColumnWidth(1, 5200);
        sheet.setColumnWidth(2, 6500);

        //  ------------------------------------------------------------------------ //

        /*         Definindo os estilos das clulas. ------------------------------ */

        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 20);
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);

        CellStyle cellCentered = workbook.createCellStyle();
        cellCentered.setAlignment(HorizontalAlignment.CENTER);

        CellStyle cellFontBold = workbook.createCellStyle();
        Font fontBold = workbook.createFont();
        fontBold.setBold(true);
        cellFontBold.setFont(fontBold);

        CellStyle cellResulted = workbook.createCellStyle();
        Font resultFont = workbook.createFont();
        resultFont.setBold(true);
        cellResulted.setFont(resultFont);
        cellResulted.setAlignment(HorizontalAlignment.CENTER);

        Cell cell;

        //  ------------------------------------------------------------------------ //

        /*         Criando as Colunas da Tabela ----------------------------------- */

        //Colunas da linha 1
        header1.createCell(0).setCellValue("Dados Fornecidos");

        //Colunas da linha 2
        linha02.createCell(0).setCellValue("Data de Entrada");
        linha02.createCell(2).setCellValue(this.rst.getDataEntrada());

        //Colunas da Linha 3
        linha03.createCell(0).setCellValue("Data de Sada");
        linha03.createCell(2).setCellValue(this.rst.getDataSaida());

        //Colunas da Linha 4
        linha04.createCell(0).setCellValue("Salrio Informado");
        linha04.createCell(2).setCellValue(this.rst.getSalario());

        //Colunas da Linha 5
        linha05.createCell(0).setCellValue("Motivo da Sada");
        linha05.createCell(2).setCellValue(this.rst.getMotivoRes());

        //Colunas da linha 8
        header2.createCell(0).setCellValue("Resciso");

        //Colunas da linha 9
        linha09.createCell(0).setCellValue("Item");
        linha09.createCell(1).setCellValue("Referncia");
        linha09.createCell(2).setCellValue("Valor");

        //Colunas da Linha 10
        linha10.createCell(0).setCellValue("Saldo Salrio");
        linha10.createCell(1).setCellValue(this.rst.getTotDiasTrabUltMes());
        linha10.createCell(2).setCellValue(this.rst.getUltSalario());

        //Colunas da Linha 11
        linha11.createCell(0).setCellValue("13 Proporcional");
        linha11.createCell(1).setCellValue(this.rst.getTotMesesTrabUltAno());
        linha11.createCell(2).setCellValue(this.rst.getVlrDecimo());

        //Colunas da Linha 12
        linha12.createCell(0).setCellValue("Frias Proporcional");
        linha12.createCell(1).setCellValue(this.rst.getTotMesesAqFerias());
        linha12.createCell(2).setCellValue(this.rst.getVlrFerias());

        //Colunas da Linha 13
        linha13.createCell(0).setCellValue("1/3 Frias Proporcional");
        linha13.createCell(1).setCellValue("-");
        linha13.createCell(2).setCellValue(this.rst.getVlrTercoFerias());

        //Colunas da Linha 14
        linha14.createCell(0).setCellValue("Frias Vencidas");
        linha14.createCell(1).setCellValue(this.rst.getTotFeriasVenc());
        linha14.createCell(2).setCellValue(this.rst.getVlrFeriasVenc());

        //Colunas da linha 15
        linha15.createCell(0).setCellValue("Aviso Prvio");
        linha15.createCell(1).setCellValue(this.rst.getTotDiasAviso());
        linha15.createCell(2).setCellValue(this.rst.getVlrAvisoP());

        //Colunas da linha 17
        linha17.createCell(0).setCellValue("Valor Total");
        linha17.createCell(1).setCellValue("-");
        linha17.createCell(2).setCellValue(this.rst.getVlrTotVenc());

        //Colunas da Linha 20
        header3.createCell(0).setCellValue("FGTS");

        //Colunas da Linha 21
        linha21.createCell(0).setCellValue("Valores do FGTS estaro disponveis para saque?");
        linha21.createCell(2).setCellValue(this.rst.getReceberFgts());

        //Colunas da Linha 22
        linha22.createCell(0).setCellValue("Saldo FGTS");
        linha22.createCell(2).setCellValue(this.rst.getSaldoFgts());

        //Colunas da Linha 23
        linha23.createCell(0).setCellValue("Multa de 40%");
        linha23.createCell(2).setCellValue(this.rst.getVlrMulta());

        //Colunas da Linha 25
        linha25.createCell(0).setCellValue("Valor total");
        linha25.createCell(2).setCellValue(this.rst.getVlrTotFgts());

        //  ------------------------------------------------------------------------ //  

        /*         Aplicando os estilos nas clulas ------------------------------- */

        cell = header1.getCell(0);
        cell.setCellStyle(headerStyle);
        header1.setRowStyle(headerStyle);
        header1.setHeightInPoints(30);

        cell = header2.getCell(0);
        cell.setCellStyle(headerStyle);
        header2.setRowStyle(headerStyle);
        header2.setHeightInPoints(30);

        cell = header3.getCell(0);
        cell.setCellStyle(headerStyle);
        header3.setRowStyle(headerStyle);
        header3.setHeightInPoints(30);

        linha02.getCell(2).setCellStyle(cellCentered);

        linha03.getCell(2).setCellStyle(cellCentered);

        linha04.getCell(2).setCellStyle(cellCentered);

        linha05.getCell(2).setCellStyle(cellCentered);

        linha09.getCell(1).setCellStyle(cellCentered);
        linha09.getCell(2).setCellStyle(cellCentered);

        linha10.getCell(1).setCellStyle(cellCentered);
        linha10.getCell(2).setCellStyle(cellCentered);

        linha11.getCell(1).setCellStyle(cellCentered);
        linha11.getCell(2).setCellStyle(cellCentered);

        linha12.getCell(1).setCellStyle(cellCentered);
        linha12.getCell(2).setCellStyle(cellCentered);

        linha13.getCell(1).setCellStyle(cellCentered);
        linha13.getCell(2).setCellStyle(cellCentered);

        linha14.getCell(1).setCellStyle(cellCentered);
        linha14.getCell(2).setCellStyle(cellCentered);

        linha15.getCell(1).setCellStyle(cellCentered);
        linha15.getCell(2).setCellStyle(cellCentered);

        linha17.getCell(0).setCellStyle(cellFontBold);
        linha17.getCell(1).setCellStyle(cellCentered);
        linha17.getCell(2).setCellStyle(cellResulted);

        linha21.getCell(2).setCellStyle(cellCentered);

        linha22.getCell(2).setCellStyle(cellCentered);

        linha23.getCell(2).setCellStyle(cellCentered);

        linha25.getCell(0).setCellStyle(cellFontBold);
        linha25.getCell(2).setCellStyle(cellResulted);

        //  ------------------------------------------------------------------------ //           

        /*         Escrever, salvar e fechar o arquivo ---------------------------- */

        workbook.write(arquivo); // Escrevendo no arquivo.

        workbook.close(); // Salvando e fechando o arquivo.

        return true;

    } catch (Exception e) {

        System.err.println("No foi possvel gerar seu arquivo!" + " \n" + e.getLocalizedMessage());

        return false;

    }

}

---

From source file:br.solutio.licita.controlador.ControladorPregao.java

public void editandoXlsParaExportar(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet planilha = wb.getSheetAt(0);

    //Move as celulas selecionadas para baixo de acordo com o valor informado
    planilha.shiftRows(planilha.getFirstRowNum(), planilha.getLastRowNum(), 5);

    HSSFRow linha0 = planilha.createRow(0);
    linha0.createCell(0).setCellValue("Instituio Licitadora:");
    planilha.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
    linha0.createCell(2)/*from  w  w  w .  j ava  2  s  .com*/
            .setCellValue(" " + getEntidade().getInstituicaoLicitadora().getPessoaJuridica().getNomeFantasia());
    planilha.addMergedRegion(new CellRangeAddress(0, 0, 2, 6));

    HSSFRow linha1 = planilha.createRow(1);
    linha1.createCell(0).setCellValue("Numero do Pregao:");
    planilha.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
    linha1.createCell(2).setCellValue(" " + getEntidade().getNumeroPregao());
    planilha.addMergedRegion(new CellRangeAddress(1, 1, 2, 6));

    HSSFRow linha2 = planilha.createRow(2);
    linha2.createCell(0).setCellValue("Numero do Processo:");
    planilha.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
    linha2.createCell(2).setCellValue(" " + getEntidade().getNumeroProcesso());
    planilha.addMergedRegion(new CellRangeAddress(2, 2, 2, 6));

    HSSFRow linha3 = planilha.createRow(3);
    linha3.createCell(0).setCellValue("Empresa Licitante:");
    planilha.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
    linha3.createCell(2).setCellValue("Preencha com o nome de sua Empresa");
    planilha.addMergedRegion(new CellRangeAddress(3, 3, 2, 6));

    HSSFRow linha4 = planilha.createRow(4);

    //Nova coluna para a empresas adicionarem seus valores
    HSSFRow linha5 = planilha.getRow(5);
    HSSFCell celula5 = linha5.createCell(5);
    celula5.setCellValue("Valor do Licitante");

    //for para ajustar automaticamente o tamnho das colunas
    for (int i = 0; i < 6; i++) {
        planilha.autoSizeColumn(i);
    }

    //Cor da linha de titulos da tabela
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    for (int i = 0; i < linha5.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = linha5.getCell(i);

        cell.setCellStyle(cellStyle);
    }

    CellStyle unlockedCellStyle = wb.createCellStyle();
    unlockedCellStyle.setLocked(false);

    HSSFCell celula2 = linha3.getCell(2);
    celula2.setCellStyle(unlockedCellStyle);

}

---

• «
• 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
• »