Example usage for org.apache.poi.ss.usermodel DataFormat getFormat

List of usage examples for org.apache.poi.ss.usermodel DataFormat getFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. DataFormat
  6. getFormat

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel DataFormat getFormat.

Prototype

String getFormat(short index);

Source Link

Document

get the format string that matches the given format index

Usage

From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLSX.java

License:Open Source License

/**
 * create a library of cell styles/*from   w  w  w .j  av  a  2s.c  o  m*/
 */
private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    DataFormat df = wb.createDataFormat();

    CellStyle style;
    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    styles.put("header", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    style.setDataFormat(df.getFormat("dd.MM.yyyy"));
    styles.put("header_date", style);

    Font font1 = wb.createFont();
    font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font1);
    styles.put("cell_b", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font1);
    styles.put("cell_b_centered", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setDataFormat(df.getFormat("dd.MM.yyyy"));
    styles.put("cell_b_date", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(df.getFormat("dd.MM.yyyy"));
    styles.put("cell_g", style);

    Font font2 = wb.createFont();
    font2.setColor(IndexedColors.BLUE.getIndex());
    font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font2);
    styles.put("cell_bb", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(df.getFormat("dd.MM.yyyy"));
    styles.put("cell_bg", style);

    Font font3 = wb.createFont();
    font3.setFontHeightInPoints((short) 14);
    font3.setColor(IndexedColors.DARK_BLUE.getIndex());
    font3.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font3);
    style.setWrapText(true);
    styles.put("cell_h", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setWrapText(true);
    styles.put("cell_normal", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    styles.put("cell_normal_centered", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setWrapText(true);
    style.setDataFormat(df.getFormat("dd.MM.yyyy"));
    styles.put("cell_normal_date", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setIndention((short) 1);
    style.setWrapText(true);
    styles.put("cell_indented", style);

    style = createBorderedStyle(wb);
    style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styles.put("cell_blue", style);

    return styles;
}

From source file:de.ryanthara.ja.rycon.converter.excel.Caplan2Excel.java

License:GNU General Public License

/**
 * Converts a Caplan K file element by element into a Microsoft Excel file.
 *
 * @param isXLS           selector to distinguish between XLS and XLSX file extension
 * @param sheetName       name of the sheet (file name from input file)
 * @param writeCommentRow write comment row
 *
 * @return success conversion success//  w  ww  .  j av  a  2 s . co m
 */
public boolean convertCaplan2Excel(boolean isXLS, String sheetName, boolean writeCommentRow) {
    // general preparation of the workbook
    if (isXLS) {
        workbook = new HSSFWorkbook();
    } else {
        workbook = new XSSFWorkbook();
    }

    String safeName = WorkbookUtil.createSafeSheetName(sheetName);
    Sheet sheet = workbook.createSheet(safeName);
    Row row;
    Cell cell;
    CellStyle cellStyle;

    DataFormat format = workbook.createDataFormat();

    short rowNumber = 0;
    short cellNumber = 0;
    short countColumns = 0;

    if (writeCommentRow) {
        row = sheet.createRow(rowNumber);
        rowNumber++;

        cell = row.createCell(cellNumber);
        cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.pointNumber));
        cellNumber++;

        cell = row.createCell(cellNumber);
        cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.easting));
        cellNumber++;

        cell = row.createCell(cellNumber);
        cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.northing));
        cellNumber++;

        cell = row.createCell(cellNumber);
        cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.height));
        cellNumber++;

        cell = row.createCell(cellNumber);
        cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.object));
        cellNumber++;

        cell = row.createCell(cellNumber);
        cell.setCellValue(ResourceBundleUtils.getLangString(COLUMNS, Columns.attribute));
    }

    for (String line : readStringLines) {
        // skip empty lines directly after reading
        if (!line.trim().isEmpty()) {
            row = sheet.createRow(rowNumber);
            rowNumber++;

            cellNumber = 0;

            CaplanBlock caplanBlock = new CaplanBlock(line);

            if (caplanBlock.getNumber() != null) {
                cell = row.createCell(cellNumber);
                cell.setCellValue(caplanBlock.getNumber());
                cellNumber++;
            }

            if (caplanBlock.getEasting() != null) {
                cell = row.createCell(cellNumber);

                if (!caplanBlock.getEasting().equals("")) {
                    cell.setCellValue(Double.parseDouble(caplanBlock.getEasting()));
                    cellStyle = workbook.createCellStyle();
                    cellStyle.setDataFormat(format.getFormat("#,##0.0000"));
                    cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
                    cell.setCellStyle(cellStyle);
                } else {
                    cell.setCellValue("");
                }

                cellNumber++;
            }

            if (caplanBlock.getNorthing() != null) {
                cell = row.createCell(cellNumber);

                if (!caplanBlock.getNorthing().equals("")) {
                    cell.setCellValue(Double.parseDouble(caplanBlock.getNorthing()));
                    cellStyle = workbook.createCellStyle();
                    cellStyle.setDataFormat(format.getFormat("#,##0.0000"));
                    cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
                    cell.setCellStyle(cellStyle);
                } else {
                    cell.setCellValue("");
                }

                cellNumber++;
            }

            if (caplanBlock.getHeight() != null) {
                cell = row.createCell(cellNumber);

                if (!caplanBlock.getHeight().equals("")) {
                    cell.setCellValue(Double.parseDouble(caplanBlock.getHeight()));
                    cellStyle = workbook.createCellStyle();
                    cellStyle.setDataFormat(format.getFormat("#,##0.0000"));
                    cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
                    cell.setCellStyle(cellStyle);
                } else {
                    cell.setCellValue("");
                }

                cellNumber++;
            }

            if (caplanBlock.getCode() != null) {
                cell = row.createCell(cellNumber);
                cell.setCellValue(caplanBlock.getCode());
                cellNumber++;

                if (caplanBlock.getAttributes().size() > 0) {
                    for (String attribute : caplanBlock.getAttributes()) {
                        cell = row.createCell(cellNumber);
                        cell.setCellValue(attribute);
                        cellNumber++;
                    }
                }
            }

            if (cellNumber > countColumns) {
                countColumns = cellNumber;
            }
        }
    }

    // adjust column width to fit the content
    for (int i = 0; i < countColumns; i++) {
        sheet.autoSizeColumn((short) i);
    }

    return rowNumber > 1;
}

From source file:de.ryanthara.ja.rycon.converter.excel.CSVBaselStadt2Excel.java

License:GNU General Public License

/**
 * Converts a comma separated coordinate file from the geodata server Basel Stadt (Switzerland)
 * into a Zeiss REC formatted file.//from   ww w  .  j a  va  2s.c  o  m
 *
 * @param isXLS           selector to distinguish between XLS and XLSX file extension
 * @param sheetName       name of the sheet (file name from input file)
 * @param writeCommentRow write comment row
 *
 * @return success conversion success
 */
public boolean convertCSVBaselStadt2Excel(boolean isXLS, String sheetName, boolean writeCommentRow) {
    // general preparation of the workbook
    if (isXLS) {
        workbook = new HSSFWorkbook();
    } else {
        workbook = new XSSFWorkbook();
    }

    String safeName = WorkbookUtil.createSafeSheetName(sheetName);
    Sheet sheet = workbook.createSheet(safeName);
    Row row;
    Cell cell;
    CellStyle cellStyle;

    DataFormat format = workbook.createDataFormat();

    short rowNumber = 0;
    short cellNumber = 0;

    if (writeCommentRow) {
        row = sheet.createRow(rowNumber);
        rowNumber++;

        String[] commentLine = readCSVLines.get(0);

        for (String description : commentLine) {
            cell = row.createCell(cellNumber);
            cellNumber++;
            cell.setCellValue(description);
        }
    }

    // remove furthermore the still not needed comment line
    readCSVLines.remove(0);

    for (String[] csvLine : readCSVLines) {
        row = sheet.createRow(rowNumber);
        rowNumber++;

        cellNumber = 0;

        for (int i = 0; i < csvLine.length; i++) {
            cell = row.createCell(cellNumber);
            cellNumber++;

            switch (i) {
            case 0:
            case 1:
                cell.setCellValue(csvLine[i]);
                break;
            case 2:
            case 3:
            case 4:
            case 5:
                if (csvLine[i].equalsIgnoreCase("")) {
                    cell.setCellValue(csvLine[i]);
                } else {
                    cell.setCellValue(Double.parseDouble(csvLine[i]));
                    cellStyle = workbook.createCellStyle();
                    cellStyle.setDataFormat(format.getFormat("#,##0.000"));
                    cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
                    cell.setCellStyle(cellStyle);
                }
                break;
            case 6:
            case 7:
            case 8:
            case 9:
            case 10:
                cell.setCellValue(csvLine[i]);
                break;

            default:
                System.err.println(
                        "Error in convertCSVBaselStadt2Excel: unknown element found or to much columns");
            }
        }
    }

    // adjust column width to fit the content
    for (int i = 0; i < readCSVLines.get(0).length; i++) {
        sheet.autoSizeColumn((short) i);
    }

    return rowNumber > 1;
}

From source file:de.ryanthara.ja.rycon.converter.excel.GSI2Excel.java

License:GNU General Public License

/**
 * Converts a GSI file element by element into an Excel file.
 *
 * @param isXLS     selector to distinguish between XLS and XLSX file extension
 * @param sheetName name of the sheet (file name from input file)
 *
 * @return success conversion success//  ww w  . j  av  a  2s  .  c o  m
 */
public boolean convertGSI2Excel(boolean isXLS, String sheetName, boolean writeCommentRow) {
    // general preparation of the workbook
    if (isXLS) {
        workbook = new HSSFWorkbook();
    } else {
        workbook = new XSSFWorkbook();
    }

    String safeName = WorkbookUtil.createSafeSheetName(sheetName);
    Sheet sheet = workbook.createSheet(safeName);
    Row row;
    Cell cell;
    CellStyle cellStyle;

    DataFormat format = workbook.createDataFormat();

    short rowNumber = 0;
    short cellNumber = 0;

    if (writeCommentRow) {
        row = sheet.createRow(rowNumber);
        rowNumber++;

        for (int wordIndex : baseToolsGSI.getFoundAllWordIndices()) {
            cell = row.createCell(cellNumber);
            cellNumber++;

            cell.setCellValue(
                    ResourceBundleUtils.getLangString(WORDINDICES, WordIndices.valueOf("WI" + wordIndex)));
        }
    }

    // fill gsi content into rows and cells
    for (ArrayList<GSIBlock> blocksInLine : baseToolsGSI.getEncodedLinesOfGSIBlocks()) {
        row = sheet.createRow(rowNumber);
        rowNumber++;

        cellNumber = 0;

        for (GSIBlock block : blocksInLine) {
            cell = row.createCell(cellNumber);
            cellNumber++;

            switch (block.getWordIndex()) {
            // GENERAL
            case 11: // Point number (includes block number)
            case 12: // Instrument serial no
            case 13: // Instrument type
            case 18: // Time format 1: pos. 8-9 year, 10-11 sec, 12-14 msec
            case 19: // Time format 2 : pos, 8-9 month 10-11 day, 12-13 hour, 14-15 min
                cell.setCellValue(block.toPrintFormatCSV());
                break;

            // ANGLES
            case 21: // Horizontal Circle (Hz)
            case 22: // Vertical Angle (V)
            case 25: // Horizontal circle difference (Hz0-Hz)
                cell.setCellValue(Double.parseDouble(block.toPrintFormatCSV()));
                break;

            // DISTANCE
            case 31: // Slope Distance
            case 32: // Horizontal Distance
            case 33: // Height Difference
                cell.setCellValue(Double.parseDouble(block.toPrintFormatCSV()));
                break;

            // CODE BLOCK
            case 41: // Code number ( include block number)
            case 42: // Information 1
            case 43: // Information 2
            case 44: // Information 3
            case 45: // Information 4
            case 46: // Information 5
            case 47: // Information 6
            case 48: // Information 7
            case 49: // Information 8
                cell.setCellValue(block.toPrintFormatCSV());
                break;

            // DISTANCE (additional information)
            case 51: // Constants(ppm, mm)
            case 52: // Number of measurements, standard deviation
            case 53: // Deviation
            case 58: // Signal strength
            case 59: // Reflector constant (1/10 mm)ppm
                cell.setCellValue(block.toPrintFormatCSV());
                break;

            // POINT CODING
            case 71: // Point Code
            case 72: // Attribute 1
            case 73: // Attribute 2
            case 74: // Attribute 3
            case 75: // Attribute 4
            case 76: // Attribute 5
            case 77: // Attribute 6
            case 78: // Attribute 7
            case 79: // Attribute 8
                cell.setCellValue(block.toPrintFormatCSV());
                break;

            // COORDINATES
            case 81: // Easting (Target)
            case 82: // Northing (Target)
            case 83: // Elevation (Target)
            case 84: // Station Easting (E0)
            case 85: // Station Northing (N0)
            case 86: // Station Elevation (H0)
                cell.setCellValue(Double.parseDouble(block.toPrintFormatCSV()));
                cellStyle = workbook.createCellStyle();
                cellStyle.setDataFormat(format.getFormat("#,##0.0000"));
                cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
                cell.setCellStyle(cellStyle);
                break;

            case 87: // Reflector height (above ground)
            case 88: // Instrument height (above ground)
                cell.setCellValue(Double.parseDouble(block.toPrintFormatCSV()));
                cellStyle = workbook.createCellStyle();
                cellStyle.setDataFormat(format.getFormat("#,##0.000"));
                cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
                cell.setCellStyle(cellStyle);
                break;

            default:
                System.err.println("GSI2Excel.convertGSI2Excel() : line contains unknown word index "
                        + block.toPrintFormatCSV());
            }
        }
    }

    // adjust column width to fit the content
    for (int i = 0; i < baseToolsGSI.getEncodedLinesOfGSIBlocks().size(); i++) {
        sheet.autoSizeColumn((short) i);
    }

    // check number of written lines
    return rowNumber > 1;
}

From source file:de.ryanthara.ja.rycon.converter.excel.TXTBaselLandschaft2Excel.java

License:GNU General Public License

/**
 * Converts a txt file from the geodata server Basel Landschaft (Switzerland) element by element into an Excel file.
 *
 * @param isXLS           selector to distinguish between XLS and XLSX file extension
 * @param sheetName       name of the sheet (file name from input file)
 * @param writeCommentRow write comment row
 *
 * @return success conversion success/*from  w w  w. j ava2  s .co  m*/
 */
public boolean convertTXTBaselLand2Excel(boolean isXLS, String sheetName, boolean writeCommentRow) {
    // general preparation of the workbook
    if (isXLS) {
        workbook = new HSSFWorkbook();
    } else {
        workbook = new XSSFWorkbook();
    }

    String safeName = WorkbookUtil.createSafeSheetName(sheetName);
    Sheet sheet = workbook.createSheet(safeName);
    Row row;
    Cell cell;
    CellStyle cellStyle;

    DataFormat format = workbook.createDataFormat();

    short rowNumber = 0;
    short cellNumber = 0;
    short countColumns = 0;

    if (writeCommentRow) {
        row = sheet.createRow(rowNumber);
        rowNumber++;

        String[] lineSplit = readStringLines.get(0).trim().split("\\t", -1);

        for (String description : lineSplit) {
            cell = row.createCell(cellNumber);
            cellNumber++;
            cell.setCellValue(description);
        }
    }

    // remove furthermore the still not needed comment line
    readStringLines.remove(0);

    for (String line : readStringLines) {
        row = sheet.createRow(rowNumber);
        rowNumber++;

        String[] lineSplit = line.trim().split("\\t", -1);

        cellNumber = 0;

        switch (lineSplit.length) {
        case 5: // HFP file
            cell = row.createCell(cellNumber); // Art
            cell.setCellValue(lineSplit[0]);
            cellNumber++;

            cell = row.createCell(cellNumber); // Number
            cell.setCellValue(lineSplit[1]);
            cellNumber++;

            cell = row.createCell(cellNumber); // X
            cell.setCellValue(Double.parseDouble(lineSplit[2]));
            cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat(format.getFormat("#,##0.000"));
            cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
            cell.setCellStyle(cellStyle);
            cellNumber++;

            cell = row.createCell(cellNumber); // Y
            cell.setCellValue(Double.parseDouble(lineSplit[3]));
            cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat(format.getFormat("#,##0.000"));
            cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
            cell.setCellStyle(cellStyle);
            cellNumber++;

            cell = row.createCell(cellNumber); // Z
            if (lineSplit[4].equalsIgnoreCase("NULL")) {
                cell.setCellValue("NULL");
            } else {
                cell.setCellValue(Double.parseDouble(lineSplit[4]));
                cellStyle = workbook.createCellStyle();
                cellStyle.setDataFormat(format.getFormat("#,##0.000"));
                cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
                cell.setCellStyle(cellStyle);
            }

            countColumns = 5;
            break;

        case 6: // LFP file
            cell = row.createCell(cellNumber); // Art
            cell.setCellValue(lineSplit[0]);
            cellNumber++;

            cell = row.createCell(cellNumber); // Number
            cell.setCellValue(lineSplit[1]);
            cellNumber++;

            cell = row.createCell(cellNumber); // VArt
            cell.setCellValue(lineSplit[2]);
            cellNumber++;

            cell = row.createCell(cellNumber); // X
            cell.setCellValue(Double.parseDouble(lineSplit[3]));
            cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat(format.getFormat("#,##0.000"));
            cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
            cell.setCellStyle(cellStyle);
            cellNumber++;

            cell = row.createCell(cellNumber); // Y
            cell.setCellValue(Double.parseDouble(lineSplit[4]));
            cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat(format.getFormat("#,##0.000"));
            cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
            cell.setCellStyle(cellStyle);
            cellNumber++;

            cell = row.createCell(cellNumber); // Z
            if (lineSplit[5].equalsIgnoreCase("NULL")) {
                cell.setCellValue("NULL");
            } else {
                cell.setCellValue(Double.parseDouble(lineSplit[5]));
                cellStyle = workbook.createCellStyle();
                cellStyle.setDataFormat(format.getFormat("#,##0.000"));
                cellStyle.setVerticalAlignment(CellStyle.ALIGN_RIGHT);
                cell.setCellStyle(cellStyle);
            }

            countColumns = 6;
            break;

        default:
            System.err.println(
                    "TXTBaselLandschaft2Excel.convertTXTBaselLand2Excel() : line contains less or more tokens "
                            + line);
        }
    }

    // adjust column width to fit the content
    for (int i = 0; i < countColumns; i++) {
        sheet.autoSizeColumn((short) i);
    }

    return rowNumber > 1;
}

From source file:demo.poi.BusinessPlan.java

License:Apache License

/**
 * create a library of cell styles//w  w  w.  jav a 2  s  .  c om
 */
private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    DataFormat df = wb.createDataFormat();

    CellStyle style;
    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    //      style.setFillBackgroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    styles.put("header", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("header_date", style);

    Font font1 = wb.createFont();
    font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font1);
    styles.put("cell_b", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font1);
    styles.put("cell_b_centered", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("cell_b_date", style);

    style.setBottomBorderColor(IndexedColors.AQUA.index);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("cell_g", style);

    Font font2 = wb.createFont();
    font2.setColor(IndexedColors.BLUE.getIndex());
    font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font2);
    styles.put("cell_bb", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("cell_bg", style);

    Font font3 = wb.createFont();
    font3.setFontHeightInPoints((short) 14);
    font3.setColor(IndexedColors.DARK_BLUE.getIndex());
    font3.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font3);
    style.setWrapText(true);
    styles.put("cell_h", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setWrapText(true);
    styles.put("cell_normal", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    styles.put("cell_normal_centered", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setWrapText(true);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("cell_normal_date", style);

    // ???
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setIndention((short) 1);
    style.setWrapText(true);
    styles.put("cell_indented", style);

    style = createBorderedStyle(wb);
    style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styles.put("cell_blue", style);

    return styles;
}

From source file:eu.alpinweiss.filegen.util.Input2TableInfo.java

License:Apache License

public void initCellStyle(Workbook wb) {
    FieldType type = fieldDefinition.getType();
    if (FieldType.DATE.equals(type) || FieldType.DATERANGE.equals(type)) {
        DataFormat dataFormat = wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(dataFormat.getFormat("dd/MM/yyyy"));
    }/*from ww  w .j  a v a2s.  c om*/
}

From source file:eu.esdihumboldt.hale.io.xls.writer.XLSLookupTableWriter.java

License:Open Source License

/**
 * @see eu.esdihumboldt.hale.common.core.io.impl.AbstractIOProvider#execute(eu.esdihumboldt.hale.common.core.io.ProgressIndicator,
 *      eu.esdihumboldt.hale.common.core.io.report.IOReporter)
 *//*from   w  w w .j a  va2s .  c  o  m*/
@Override
protected IOReport execute(ProgressIndicator progress, IOReporter reporter)
        throws IOProviderConfigurationException, IOException {

    Workbook workbook;
    // write xls file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) {
        workbook = new HSSFWorkbook();
    }
    // write xlsx file
    else if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) {
        workbook = new XSSFWorkbook();
    } else {
        reporter.error(new IOMessageImpl("Content type is invalid!", null));
        reporter.setSuccess(false);
        return reporter;
    }

    Sheet sheet = workbook.createSheet();
    workbook.setSheetName(0, "Lookup table");
    Row row = null;
    Cell cell = null;
    DataFormat df = workbook.createDataFormat();

    // create cell style of the header
    CellStyle headerStyle = workbook.createCellStyle();
    Font headerFont = workbook.createFont();
    // use bold font
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);
    // set a medium border
    headerStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    // set cell data format to text
    headerStyle.setDataFormat(df.getFormat("@"));

    // create cell style
    CellStyle rowStyle = workbook.createCellStyle();
    // set thin border around the cell
    rowStyle.setBorderBottom(CellStyle.BORDER_THIN);
    rowStyle.setBorderLeft(CellStyle.BORDER_THIN);
    rowStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    rowStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    rowStyle.setWrapText(true);

    Map<Value, Value> table = getLookupTable().getTable().asMap();

    int rownum = 0;

    // write header
    row = sheet.createRow(rownum++);
    cell = row.createCell(0);
    cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_SOURCE_COLUMN).as(String.class));
    cell.setCellStyle(headerStyle);

    cell = row.createCell(1);
    cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_TARGET_COLUMN).as(String.class));
    cell.setCellStyle(headerStyle);

    for (Value key : table.keySet()) {
        // create a row
        row = sheet.createRow(rownum);

        cell = row.createCell(0);
        cell.setCellValue(key.as(String.class));
        cell.setCellStyle(rowStyle);

        Value entry = table.get(key);
        cell = row.createCell(1);
        cell.setCellValue(entry.as(String.class));
        cell.setCellStyle(rowStyle);
        rownum++;
    }

    // write file
    FileOutputStream out = new FileOutputStream(getTarget().getLocation().getPath());
    workbook.write(out);
    out.close();

    reporter.setSuccess(true);
    return reporter;
}

From source file:eu.esdihumboldt.hale.io.xls.XLSCellStyles.java

License:Open Source License

/**
 * @param workbook the workbook of the cell
 * @return the header cell style//  www .  j  a  v  a  2s.co m
 */
public static CellStyle getHeaderStyle(Workbook workbook) {

    CellStyle headerStyle = workbook.createCellStyle();
    Font headerFont = workbook.createFont();
    DataFormat df = workbook.createDataFormat();
    // use bold font
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);
    // set a medium border
    headerStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    // set cell data format to text
    headerStyle.setDataFormat(df.getFormat("@"));

    return headerStyle;
}

From source file:eu.esdihumboldt.hale.io.xls.XLSCellStyles.java

License:Open Source License

/**
 * @param workbook the workbook of the cell
 * @param strikeOut true, if cell should be striked out
 * @return the normal cell style/*from   ww  w.  ja v a2  s.c  o m*/
 */
public static CellStyle getNormalStyle(Workbook workbook, boolean strikeOut) {

    // create cell style
    CellStyle cellStyle = workbook.createCellStyle();
    DataFormat df = workbook.createDataFormat();
    // set thin border around the cell
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    cellStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    cellStyle.setWrapText(true);

    if (strikeOut) {
        // strike out font
        Font disabledFont = workbook.createFont();
        disabledFont.setStrikeout(true);
        disabledFont.setColor(IndexedColors.GREY_40_PERCENT.getIndex());
        cellStyle.setFont(disabledFont);
    }

    return cellStyle;
}