Example usage for org.apache.poi.xssf.usermodel XSSFCellStyle setBorderBottom

List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setBorderBottom

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCellStyle
  6. setBorderBottom

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFCellStyle setBorderBottom.

Prototype

@Override
public void setBorderBottom(BorderStyle border)

Source Link

Document

Set the type of border to use for the bottom border of the cell

Usage

From source file:com.sec.ose.osi.report.standard.CoverSheetTemplate.java

License:Open Source License

protected XSSFCellStyle getCellStyle(int border, Font font) {
    XSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true); // new line

    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());

    if ((border & BORDER_BOTTOM) > 0)
        style.setBorderBottom(CellStyle.BORDER_DOUBLE);
    else/*w ww. j  av  a  2s .c o m*/
        style.setBorderBottom(CellStyle.BORDER_THIN);

    if ((border & BORDER_LEFT) > 0)
        style.setBorderLeft(CellStyle.BORDER_DOUBLE);
    else
        style.setBorderLeft(CellStyle.BORDER_THIN);

    if ((border & BORDER_RIGHT) > 0)
        style.setBorderRight(CellStyle.BORDER_DOUBLE);
    else
        style.setBorderRight(CellStyle.BORDER_THIN);

    if ((border & BORDER_TOP) > 0)
        style.setBorderTop(CellStyle.BORDER_DOUBLE);
    else
        style.setBorderTop(CellStyle.BORDER_THIN);

    if (font != null)
        style.setFont(font);

    return style;
}

From source file:com.sec.ose.osi.report.standard.ISheetTemplate.java

License:Open Source License

/**
 * @param color/*from  w  w  w  . j  ava  2s .co  m*/
 * @param font
 * @return CellStyle
 */
protected XSSFCellStyle getCellStyle(XSSFColor color, Font font) {
    XSSFCellStyle style = wb.createCellStyle();
    style.setFillForegroundColor(color);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true); // new line

    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());

    if (font != null)
        style.setFont(font);

    return style;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.AbstractSeccionXLS.java

License:Apache License

public void addBorders(XSSFWorkbook wb, XSSFCellStyle cellStyle, short borderType) {
    cellStyle.setBorderBottom(borderType);
    cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderLeft(borderType);
    cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderRight(borderType);
    cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderTop(borderType);/*from   w w  w.  j a v a 2  s. com*/
    cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.AbstractSeccionXLS.java

License:Apache License

public void paintBorder(XSSFWorkbook wb, Sheet sheet, short borderType, BordeSeccion borde) {
    for (int i = borde.getUpperRow(); i <= borde.getLowerRow(); i++) {
        Row row = sheet.getRow(i);/*from   w w  w  .  j  av  a2 s  . c o  m*/
        for (int j = borde.getLeftColumn(); j <= borde.getRightColumn(); j++) {
            if (i == borde.getUpperRow() || i == borde.getLowerRow() || j == borde.getLeftColumn()
                    || j == borde.getRightColumn()) {
                Cell cell = row.getCell(j);
                XSSFCellStyle actual = (XSSFCellStyle) cell.getCellStyle();
                XSSFCellStyle nuevo = wb.createCellStyle();
                nuevo.cloneStyleFrom(actual);
                if (i == borde.getUpperRow()) {
                    nuevo.setBorderTop(borderType);
                    nuevo.setTopBorderColor(IndexedColors.BLACK.getIndex());
                }
                if (i == borde.getLowerRow()) {
                    nuevo.setBorderBottom(borderType);
                    nuevo.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                }
                if (j == borde.getLeftColumn()) {
                    nuevo.setBorderLeft(borderType);
                    nuevo.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                }
                if (j == borde.getRightColumn()) {
                    nuevo.setBorderRight(borderType);
                    nuevo.setRightBorderColor(IndexedColors.BLACK.getIndex());
                }
                cell.setCellStyle(nuevo);
            }
        }
    }
}

From source file:contestTabulation.Setup.java

License:Open Source License

@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    HttpTransport httpTransport = new NetHttpTransport();
    JacksonFactory jsonFactory = new JacksonFactory();
    DatastoreService datastore = DatastoreServiceFactory.getDatastoreService();

    Entity contestInfo = Retrieve.contestInfo();

    GoogleCredential credential = new GoogleCredential.Builder().setJsonFactory(jsonFactory)
            .setTransport(httpTransport)
            .setClientSecrets((String) contestInfo.getProperty("OAuth2ClientId"),
                    (String) contestInfo.getProperty("OAuth2ClientSecret"))
            .build().setFromTokenResponse(new JacksonFactory().fromString(
                    ((Text) contestInfo.getProperty("OAuth2Token")).getValue(), GoogleTokenResponse.class));

    String docName = null, docLevel = null;
    for (Level level : Level.values()) {
        docName = req.getParameter("doc" + level.getName());
        if (docName != null) {
            docLevel = level.toString();
            break;
        }//from  w ww  . j av  a 2s  .  co  m
    }

    if (docLevel == null) {
        resp.sendError(HttpServletResponse.SC_BAD_REQUEST,
                "Spreadsheet creation request must have paramater document name parameter set");
        return;
    }

    Query query = new Query("registration")
            .setFilter(new FilterPredicate("schoolLevel", FilterOperator.EQUAL, docLevel))
            .addSort("schoolName", SortDirection.ASCENDING);
    List<Entity> registrations = datastore.prepare(query).asList(FetchOptions.Builder.withDefaults());

    Map<String, List<JSONObject>> studentData = new HashMap<String, List<JSONObject>>();
    for (Entity registration : registrations) {
        String regSchoolName = ((String) registration.getProperty("schoolName")).trim();
        String regStudentDataJSON = unescapeHtml4(((Text) registration.getProperty("studentData")).getValue());

        JSONArray regStudentData = null;
        try {
            regStudentData = new JSONArray(regStudentDataJSON);
        } catch (JSONException e) {
            e.printStackTrace();
            resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
            return;
        }

        for (int i = 0; i < regStudentData.length(); i++) {
            if (!studentData.containsKey(regSchoolName)) {
                studentData.put(regSchoolName, new ArrayList<JSONObject>());
            }
            try {
                studentData.get(regSchoolName).add(regStudentData.getJSONObject(i));
            } catch (JSONException e) {
                resp.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                e.printStackTrace();
                return;
            }
        }
    }

    for (List<JSONObject> students : studentData.values()) {
        Collections.sort(students, new Comparator<JSONObject>() {
            @Override
            public int compare(JSONObject a, JSONObject b) {
                try {
                    return a.getString("name").compareTo(b.getString("name"));
                } catch (JSONException e) {
                    e.printStackTrace();
                    return 0;
                }
            }
        });
    }

    Workbook workbook = new XSSFWorkbook();

    XSSFCellStyle boldStyle = (XSSFCellStyle) workbook.createCellStyle();
    Font boldFont = workbook.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldStyle.setFont(boldFont);

    Map<Subject, XSSFCellStyle> subjectCellStyles = new HashMap<Subject, XSSFCellStyle>();
    for (Subject subject : Subject.values()) {
        final double ALPHA = .144;
        String colorStr = (String) contestInfo.getProperty("color" + subject.getName());
        byte[] backgroundColor = new byte[] { Integer.valueOf(colorStr.substring(1, 3), 16).byteValue(),
                Integer.valueOf(colorStr.substring(3, 5), 16).byteValue(),
                Integer.valueOf(colorStr.substring(5, 7), 16).byteValue() };
        // http://en.wikipedia.org/wiki/Alpha_compositing#Alpha_blending
        byte[] borderColor = new byte[] { (byte) ((backgroundColor[0] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[1] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[2] & 0xff) * (1 - ALPHA)) };

        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
        style.setFillBackgroundColor(new XSSFColor(backgroundColor));
        style.setFillPattern(CellStyle.ALIGN_FILL);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(new XSSFColor(borderColor));
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(new XSSFColor(borderColor));
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(new XSSFColor(borderColor));
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(new XSSFColor(borderColor));
        subjectCellStyles.put(subject, style);
    }

    Entry<String, List<JSONObject>>[] studentDataEntries = studentData.entrySet().toArray(new Entry[] {});
    Arrays.sort(studentDataEntries, Collections.reverseOrder(new Comparator<Entry<String, List<JSONObject>>>() {
        @Override
        public int compare(Entry<String, List<JSONObject>> arg0, Entry<String, List<JSONObject>> arg1) {
            return Integer.compare(arg0.getValue().size(), arg1.getValue().size());
        }
    }));

    for (Entry<String, List<JSONObject>> studentDataEntry : studentDataEntries) {
        Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(studentDataEntry.getKey()));
        Row row = sheet.createRow((short) 0);

        String[] columnNames = { "Name", "Grade", "N", "C", "M", "S" };
        for (int i = 0; i < columnNames.length; i++) {
            String columnName = columnNames[i];
            Cell cell = row.createCell(i);
            cell.setCellValue(columnName);
            cell.setCellStyle(boldStyle);
            CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_CENTER);
        }

        int longestNameLength = 7;
        int rowNum = 1;
        for (JSONObject student : studentDataEntry.getValue()) {
            try {
                row = sheet.createRow((short) rowNum);
                row.createCell(0).setCellValue(student.getString("name"));
                row.createCell(1).setCellValue(student.getInt("grade"));

                for (Subject subject : Subject.values()) {
                    String value = student.getBoolean(subject.toString()) ? "" : "X";
                    Cell cell = row.createCell(Arrays.asList(columnNames).indexOf(subject.toString()));
                    cell.setCellValue(value);
                    cell.setCellStyle(subjectCellStyles.get(subject));
                }

                if (student.getString("name").length() > longestNameLength) {
                    longestNameLength = student.getString("name").length();
                }

                rowNum++;
            } catch (JSONException e) {
                e.printStackTrace();
                resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
                return;
            }
        }

        sheet.createFreezePane(0, 1, 0, 1);
        // sheet.autoSizeColumn((short) 0); Not supported by App Engine
        sheet.setColumnWidth((short) 0, (int) (256 * longestNameLength * 1.1));
    }

    Drive drive = new Drive.Builder(httpTransport, jsonFactory, credential)
            .setApplicationName("contestTabulation").build();

    File body = new File();
    body.setTitle(docName);
    body.setMimeType("application/vnd.google-apps.spreadsheet");

    ByteArrayOutputStream outStream = new ByteArrayOutputStream();
    workbook.write(outStream);
    ByteArrayInputStream inStream = new ByteArrayInputStream(outStream.toByteArray());
    InputStreamContent content = new InputStreamContent(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inStream);

    drive.files().insert(body, content).execute();
    workbook.close();
}

From source file:coverageqc.functions.MyExcelEditor.java

private static XSSFCellStyle getDefaultCellStyle(XSSFRow currentRow) {

    XSSFCellStyle cellStyle = currentRow.getSheet().getWorkbook().createCellStyle();

    cellStyle.setWrapText(true);//from   w w w  . j  a  va  2  s. c  om
    cellStyle.setBorderBottom(cellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(cellStyle.BORDER_THIN);
    cellStyle.setBorderRight(cellStyle.BORDER_THIN);
    cellStyle.setBorderTop(cellStyle.BORDER_THIN);
    XSSFFont myFont = currentRow.getSheet().getWorkbook().createFont();
    myFont.setFontHeight(9);
    cellStyle.setFont(myFont);
    cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(new XSSFColor(Color.white));
    return cellStyle;
}

From source file:coverageqc.functions.MyExcelEditor.java

private static XSSFCellStyle getGrayCellStyle(XSSFRow currentRow) {

    XSSFCellStyle cellStyle = currentRow.getSheet().getWorkbook().createCellStyle();

    cellStyle.setWrapText(true);//ww w  .j a v  a 2 s . c  o  m
    cellStyle.setBorderBottom(cellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(cellStyle.BORDER_THIN);
    cellStyle.setBorderRight(cellStyle.BORDER_THIN);
    cellStyle.setBorderTop(cellStyle.BORDER_THIN);
    XSSFFont myFont = currentRow.getSheet().getWorkbook().createFont();
    myFont.setFontHeight(9);
    cellStyle.setFont(myFont);
    cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(new XSSFColor(Color.gray));
    return cellStyle;
}

From source file:coverageqc.functions.MyExcelGenerator.java

private XSSFCellStyle getDefaultCellStyle(XSSFRow currentRow, Color specifiedColor) {

    XSSFCellStyle cellStyle = currentRow.getSheet().getWorkbook().createCellStyle();

    cellStyle.setWrapText(true);/*  www.  j a  v a  2s  .  co  m*/
    cellStyle.setBorderBottom(cellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(cellStyle.BORDER_THIN);
    cellStyle.setBorderRight(cellStyle.BORDER_THIN);
    cellStyle.setBorderTop(cellStyle.BORDER_THIN);
    XSSFFont myFont = currentRow.getSheet().getWorkbook().createFont();
    myFont.setFontHeight(9);
    cellStyle.setFont(myFont);
    cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(new XSSFColor(specifiedColor));
    return cellStyle;
}

From source file:de.symeda.sormas.api.doc.UserRightsGenerator.java

License:Open Source License

@Test
public void generateUserRights() throws FileNotFoundException, IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();

    // Create User Rights sheet
    String safeName = WorkbookUtil.createSafeSheetName("User Rights");
    XSSFSheet sheet = workbook.createSheet(safeName);

    // Initialize cell styles
    // Authorized style
    XSSFCellStyle authorizedStyle = workbook.createCellStyle();
    authorizedStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    authorizedStyle.setFillForegroundColor(new XSSFColor(new Color(0, 153, 0)));
    authorizedStyle.setBorderBottom(BorderStyle.THIN);
    authorizedStyle.setBorderLeft(BorderStyle.THIN);
    authorizedStyle.setBorderTop(BorderStyle.THIN);
    authorizedStyle.setBorderRight(BorderStyle.THIN);
    authorizedStyle.setBorderColor(BorderSide.BOTTOM, new XSSFColor(Color.BLACK));
    authorizedStyle.setBorderColor(BorderSide.LEFT, new XSSFColor(Color.BLACK));
    authorizedStyle.setBorderColor(BorderSide.TOP, new XSSFColor(Color.BLACK));
    authorizedStyle.setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.BLACK));
    // Unauthorized style
    XSSFCellStyle unauthorizedStyle = workbook.createCellStyle();
    unauthorizedStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    unauthorizedStyle.setFillForegroundColor(new XSSFColor(Color.RED));
    unauthorizedStyle.setBorderBottom(BorderStyle.THIN);
    unauthorizedStyle.setBorderLeft(BorderStyle.THIN);
    unauthorizedStyle.setBorderTop(BorderStyle.THIN);
    unauthorizedStyle.setBorderRight(BorderStyle.THIN);
    unauthorizedStyle.setBorderColor(BorderSide.BOTTOM, new XSSFColor(Color.BLACK));
    unauthorizedStyle.setBorderColor(BorderSide.LEFT, new XSSFColor(Color.BLACK));
    unauthorizedStyle.setBorderColor(BorderSide.TOP, new XSSFColor(Color.BLACK));
    unauthorizedStyle.setBorderColor(BorderSide.RIGHT, new XSSFColor(Color.BLACK));
    // Bold style
    XSSFFont boldFont = workbook.createFont();
    boldFont.setBold(true);/*w w w . j  a va  2  s .  c  o m*/
    XSSFCellStyle boldStyle = workbook.createCellStyle();
    boldStyle.setFont(boldFont);

    int rowCounter = 0;

    // Header
    Row headerRow = sheet.createRow(rowCounter++);
    Cell userRightHeadlineCell = headerRow.createCell(0);
    userRightHeadlineCell.setCellValue("User Right");
    userRightHeadlineCell.setCellStyle(boldStyle);
    Cell descHeadlineCell = headerRow.createCell(1);
    descHeadlineCell.setCellValue("Description");
    descHeadlineCell.setCellStyle(boldStyle);
    sheet.setColumnWidth(0, 256 * 35);
    sheet.setColumnWidth(1, 256 * 50);
    for (UserRole userRole : UserRole.values()) {
        String columnCaption = userRole.toString();
        Cell headerCell = headerRow.createCell(userRole.ordinal() + 2);
        headerCell.setCellValue(columnCaption);
        headerCell.setCellStyle(boldStyle);
        sheet.setColumnWidth(userRole.ordinal() + 2, 256 * 14);
    }

    // User right rows
    for (UserRight userRight : UserRight.values()) {
        Row row = sheet.createRow(rowCounter++);

        // User right name
        Cell nameCell = row.createCell(0);
        nameCell.setCellValue(userRight.name());
        nameCell.setCellStyle(boldStyle);

        // User right description
        Cell descCell = row.createCell(1);
        descCell.setCellValue(userRight.toString());

        // Add styled cells for all user roles
        for (UserRole userRole : UserRole.values()) {
            Cell roleRightCell = row.createCell(userRole.ordinal() + 2);
            if (userRole.hasDefaultRight(userRight)) {
                roleRightCell.setCellStyle(authorizedStyle);
                roleRightCell.setCellValue("Yes");
            } else {
                roleRightCell.setCellStyle(unauthorizedStyle);
                roleRightCell.setCellValue("No");
            }
        }
    }

    XssfHelper.addAboutSheet(workbook);

    String filePath = "src/main/resources/doc/SORMAS_User_Rights.xlsx";
    try (OutputStream fileOut = new FileOutputStream(filePath)) {
        workbook.write(fileOut);
    }
    workbook.close();

    //      Desktop.getDesktop().open(new File(filePath));
}

From source file:document.ExcelDocumentStyles.java

private void withBorder(XSSFCellStyle style) {
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(borderColor);
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(borderColor);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(borderColor);
}