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

Introduction

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

Prototype

public void setVerticalAlignment(VerticalAlignment align) 

Source Link

Document

Set the type of vertical alignment for the cell

Usage

From source file:mvjce.Excel_operations.java

public static void fill_exceldata(XSSFWorkbook workbook, XSSFSheet spreadsheet) {
    try {//from www .  j a  v a2 s .  com
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Sample_data", "root", "root");
        Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet detail = st.executeQuery(
                "select attendance.USN,Student_info.Name,attendance.sub1_class,attendance.sub2_class,"
                        + " attendance.sub3_class,attendance.sub4_class,attendance.sub5_class,attendance.sub6_class,attendance.sub7_class,attendance.sub8_class from attendance inner join "
                        + " Student_info on attendance.USN = Student_info.USN where class='" + Writesheet.sec
                        + "' and semester=" + Writesheet.sem_string + " ;");
        int i = 1, j = 8;

        XSSFFont font = workbook.createFont();
        font.setFontName("Arial");
        font.setBold(true);
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);
        style.setFont(font);
        XSSFCell cell;
        while (detail.next()) {
            XSSFRow row = spreadsheet.createRow((short) j);
            row.setHeight((short) 900);
            cell = (XSSFCell) row.createCell((short) 0);
            cell.setCellValue(i);
            i++;
            cell.setCellStyle(style);
            cell = (XSSFCell) row.createCell((short) 1);
            cell.setCellValue(detail.getString(1));
            cell.setCellStyle(style);
            cell = (XSSFCell) row.createCell((short) 2);
            cell.setCellValue(detail.getString(2));
            cell.setCellStyle(style);
            cell = (XSSFCell) row.createCell((short) 3);
            cell.setCellValue(detail.getString(3));
            cell.setCellStyle(style);
            cell = (XSSFCell) row.createCell((short) 5);
            cell.setCellValue(detail.getString(4));
            cell.setCellStyle(style);
            cell = (XSSFCell) row.createCell((short) 7);
            cell.setCellValue(detail.getString(5));
            cell.setCellStyle(style);
            cell = (XSSFCell) row.createCell((short) 9);
            cell.setCellValue(detail.getString(6));
            cell.setCellStyle(style);
            cell = (XSSFCell) row.createCell((short) 11);
            cell.setCellValue(detail.getString(7));
            cell.setCellStyle(style);
            cell = (XSSFCell) row.createCell((short) 13);
            cell.setCellValue(detail.getString(8));
            cell.setCellStyle(style);
            cell = (XSSFCell) row.createCell((short) 15);
            cell.setCellValue(detail.getString(9));
            cell.setCellStyle(style);
            cell = (XSSFCell) row.createCell((short) 17);
            cell.setCellValue(detail.getString(10));
            cell.setCellStyle(style);
            j++;
        }

    } catch (Exception e) {
        System.out.println(e);
    }
}

---

From source file:mvjce.Excel_operations.java

public static void insert_internals(XSSFWorkbook workbook, XSSFSheet spreadsheet) {
    try {/*ww w  . j  a  v  a  2s  .c om*/
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Sample_data", "root", "root");
        Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet detail = st.executeQuery(
                "select s.USN,s.Name,i.sub1_int1,i.sub1_int2,i.sub1_int3,i.sub2_int1,i.sub2_int2,i.sub2_int3,i.sub3_int1,i.sub3_int2,i.sub3_int3,i.sub4_int1,i.sub4_int2,i.sub4_int3,\n"
                        + "i2.sub5_int1,i2.sub5_int2,i2.sub5_int3,i2.sub6_int1,i2.sub6_int2,i2.sub6_int3,i2.sub7_int1,i2.sub7_int2,i2.sub7_int3,i2.sub8_int1,i2.sub8_int2,i2.sub8_int3\n"
                        + "from internals as i\n" + "join Student_info as s\n" + "on i.USN=s.USN\n"
                        + "join internals2 as i2\n" + "on i2.USN=s.USN\n" + "where s.Class='" + Writesheet.sec
                        + "' and s.semester=" + Writesheet.sem_string + ";");
        int i = 1, j = 6;
        XSSFFont font = workbook.createFont();
        font.setFontName("Arial");
        font.setBold(true);
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);
        style.setFont(font);
        XSSFCell cell;
        while (detail.next()) {
            XSSFRow row = spreadsheet.createRow((short) j);
            cell = (XSSFCell) row.createCell((short) 0);
            cell.setCellValue(i);
            cell.setCellStyle(style);
            for (int k = 1; k <= 26; k++) {
                cell = (XSSFCell) row.createCell((short) k);
                cell.setCellValue(detail.getString(k));
                cell.setCellStyle(style);
            }
            i++;
            j++;
        }
        spreadsheet.autoSizeColumn(2);
        spreadsheet.autoSizeColumn(1);
    } catch (Exception e) {
        System.out.println(e);
    }
}

---

From source file:mvjce.internal_sheet.java

public static void internal_details(XSSFWorkbook workbook) {
    XSSFSheet spreadsheet = workbook.createSheet("test_excel_internal");
    XSSFRow row = spreadsheet.createRow((short) 0);
    XSSFCell cell = (XSSFCell) row.createCell((short) 0);
    cell.setCellValue("MVJ College of Bangalore- 560067");
    //MEARGING CELLS 
    spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19));
    XSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setBold(true);/*from w  w  w .  ja  v  a  2 s . c o  m*/
    XSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    style.setWrapText(true);
    style.setFont(font);
    cell.setCellStyle(style);
    Excel_operations.set_subcode();
    row = spreadsheet.createRow((short) 1);
    cell = (XSSFCell) row.createCell((short) 0);
    cell.setCellValue("Department of " + dept_name);
    spreadsheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 19));
    cell.setCellStyle(style);
    row = spreadsheet.createRow(3);
    row.setHeight((short) 600);
    cell = (XSSFCell) row.createCell((short) 1);
    cell.setCellValue("Semester: \n" + sem_string + sec);
    cell.setCellStyle(style);
    row = spreadsheet.createRow(4);
    String[] text = new String[3];
    text[0] = "SI.No";
    text[1] = "USN";
    text[2] = "STUDENT NAME";
    for (int i = 0; i < 3; i++) {
        cell = (XSSFCell) row.createCell((short) i);
        cell.setCellValue(text[i]);
        cell.setCellStyle(style);
        spreadsheet.addMergedRegion(new CellRangeAddress(4, 5, i, i));
    }
    XSSFRow row2 = spreadsheet.createRow((short) 5);
    for (int i = 0, j = 3; j <= 23; j += 4) {
        cell = row.createCell((short) j);
        cell.setCellValue(sub[i]);
        i++;
        cell.setCellStyle(style);
        cell = row2.createCell(j);
        cell.setCellValue("T1");
        cell.setCellStyle(style);
        cell = row2.createCell(j + 1);
        cell.setCellValue("T2");
        cell.setCellStyle(style);
        cell = row2.createCell(j + 2);
        cell.setCellValue("T3");
        cell.setCellStyle(style);
        cell = row2.createCell(j + 3);
        cell.setCellValue("Avg");
        cell.setCellStyle(style);
        spreadsheet.addMergedRegion(new CellRangeAddress(4, 4, j, j + 3));
        Excel_operations.insert_internals(workbook, spreadsheet);
    }
}

---

From source file:mvjce.Writesheet.java

public static void writesheet() {

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet spreadsheet = workbook.createSheet(sem_string + sec);
    XSSFRow row = spreadsheet.createRow((short) 0);
    XSSFCell cell = (XSSFCell) row.createCell((short) 0);
    cell.setCellValue("MVJ College of Bangalore- 560067");
    //MEARGING CELLS 
    spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19));
    XSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setBold(true);//from   w  w w .  j  a  va  2s  .c  o m
    XSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    style.setWrapText(true);
    style.setFont(font);
    cell.setCellStyle(style);
    Excel_operations.set_subcode();
    for (int i = 0; i < 8; i++) {
        internal_sheet.sub[i] = sub[i];
    }
    internal_sheet.dept_name = dept_name;
    internal_sheet.sec = sec;
    internal_sheet.sem_string = sem_string;
    internal_sheet.internal_details(workbook);
    row = spreadsheet.createRow((short) 1);
    cell = (XSSFCell) row.createCell((short) 0);
    cell.setCellValue("Department of " + dept_name);
    spreadsheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 19));
    cell.setCellStyle(style);
    row = spreadsheet.createRow(4);
    row.setHeight((short) 600);
    cell = (XSSFCell) row.createCell((short) 1);
    cell.setCellValue("Semester: \n" + sem_string + sec);
    cell.setCellStyle(style);
    row = spreadsheet.createRow(5);
    String[] text = new String[3];
    text[0] = "SI.No";
    text[1] = "USN";
    text[2] = "STUDENT\nNAME";
    for (int i = 0; i < 3; i++) {
        cell = (XSSFCell) row.createCell((short) i);
        cell.setCellValue(text[i]);
        cell.setCellStyle(style);
        spreadsheet.addMergedRegion(new CellRangeAddress(5, 7, i, i));
    }
    int j = 0;
    XSSFRow row1 = spreadsheet.createRow((short) 6);
    row1.setHeight((short) 1000);
    XSSFRow row2 = spreadsheet.createRow((short) 7);
    row2.setHeight((short) 1000);
    for (int i = 3; i < 18; i++) {
        cell = row1.createCell((short) i);
        cell.setCellValue("Total no. of classes");
        cell.setCellStyle(style);
        cell = row2.createCell((short) i);
        cell.setCellValue("No.of Classes attended");
        cell.setCellStyle(style);
        cell = row2.createCell((short) i + 1);
        cell.setCellValue("%");
        cell.setCellStyle(style);
        cell = row.createCell((short) i);
        cell.setCellValue(sub[j]);
        j++;
        cell.setCellStyle(style);
        spreadsheet.addMergedRegion(new CellRangeAddress(5, 5, i, i + 1));
        i++;
    }
    cell = row1.createCell((short) 19);
    cell.setCellValue("%");
    cell.setCellStyle(style);
    cell = row.createCell((short) 19);
    cell.setCellValue("AVG");
    cell.setCellStyle(style);
    spreadsheet.addMergedRegion(new CellRangeAddress(5, 5, 19, 19));
    Excel_operations.fill_exceldata(workbook, spreadsheet);
    try {
        FileOutputStream out = new FileOutputStream(new File("test_excel.xlsx"));
        workbook.write(out);
        out.close();
    } catch (Exception e) {
        Database.print_error("Excel_output_stream");
    }
    System.out.println("typesofcells.xlsx written successfully");

}

---

From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java

License:Open Source License

private static int createTitle(XSSFWorkbook workbook, XSSFSheet worksheet, Service service) {

    XSSFCellStyle titleStyle = workbook.createCellStyle();
    titleStyle.setAlignment(HorizontalAlignment.CENTER);
    titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    XSSFFont txtFont = workbook.createFont();
    txtFont.setFontName("calibri");
    txtFont.setFontHeightInPoints((short) 11);
    txtFont.setBold(true);/*w w  w . j  a va  2s  . co m*/
    titleStyle.setFont(txtFont);

    XSSFRow row = worksheet.createRow(0);

    XSSFCell cell = row.createCell(0);
    cell.setCellValue(
            "Inventaire " + service.getDirection().getLibelleCourt() + " - " + service.getLibelleCourt());
    cell.setCellStyle(titleStyle);

    worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));

    return 2;
}

---

From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java

License:Open Source License

private static int generateHeader(XSSFSheet worksheet, XSSFWorkbook workbook, int rowNum) {
    // Now add//from   w ww .j a  va  2  s  .co m
    XSSFRow row = worksheet.createRow(rowNum);
    XSSFCell cell;

    XSSFCellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.index);
    headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    headerStyle.setBorderBottom(BorderStyle.MEDIUM);
    headerStyle.setBorderLeft(BorderStyle.MEDIUM);
    headerStyle.setBorderRight(BorderStyle.MEDIUM);
    headerStyle.setBorderTop(BorderStyle.MEDIUM);
    headerStyle.setAlignment(HorizontalAlignment.CENTER);
    headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    XSSFFont txtFont = workbook.createFont();
    txtFont.setFontName("calibri");
    txtFont.setFontHeightInPoints((short) 9);
    txtFont.setBold(true);
    headerStyle.setFont(txtFont);

    cell = row.createCell(0);
    cell.setCellValue("Photo");
    cell.setCellStyle(headerStyle);
    worksheet.setColumnWidth(0, ConvertImageUnits.pixel2WidthUnits(COLUMN_WIDTH_PX));//4387

    cell = row.createCell(1);
    cell.setCellValue("Rfrence");
    cell.setCellStyle(headerStyle);

    cell = row.createCell(2);
    cell.setCellValue("Libell");
    cell.setCellStyle(headerStyle);

    cell = row.createCell(3);
    cell.setCellValue("Stock\n Appock");
    cell.setCellStyle(headerStyle);
    cell.getCellStyle().setWrapText(true);

    cell = row.createCell(4);
    cell.setCellValue("Stock\n rel");
    cell.setCellStyle(headerStyle);
    cell.getCellStyle().setWrapText(true);

    row.setHeight((short) 600);

    return rowNum + 1;
}

---

From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java

License:Open Source License

private static void createRow(XSSFSheet worksheet, XSSFWorkbook workbook, ArticleStock article,
        CatalogueService catalogueService, int rowNumber) throws IOException {

    int col = 0;/*from  w  w w  .ja va  2 s. c o m*/
    XSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    XSSFCellStyle cellImageStyle = workbook.createCellStyle();
    cellImageStyle.setBorderBottom(BorderStyle.THIN);
    cellImageStyle.setBorderLeft(BorderStyle.THIN);
    cellImageStyle.setBorderRight(BorderStyle.THIN);
    cellImageStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellImageStyle.setAlignment(HorizontalAlignment.CENTER);

    if (rowNumber != 1) {
        cellStyle.setBorderTop(BorderStyle.THIN);
    }

    XSSFFont txtFont = workbook.createFont();
    txtFont.setFontName("calibri");
    txtFont.setFontHeightInPoints((short) 9);
    txtFont.setBold(false);
    cellStyle.setFont(txtFont);

    XSSFRow row = worksheet.createRow(rowNumber);
    row.setHeight((short) ROW_HEIGHT_TWIPS);//80px 1600
    // Photo
    File image = null;

    try {
        image = catalogueService.getFilePieceJointe(article.getArticleCatalogue().getPhotoArticleCatalogue());
    } catch (IllegalArgumentException e) {
        log.warn("No image to display for article " + article.getArticleCatalogue().getLibelle());
    }
    XSSFCell cell = row.createCell(col);
    cell.setCellStyle(cellImageStyle);

    if (image != null)
        addImage(workbook, worksheet, image, rowNumber);
    col = col + 1;

    // Rfrence
    cell = row.createCell(col);
    cell.setCellStyle(cellStyle);
    col = col + 1;
    cell.setCellValue(article.getReferenceArticleStock());

    // Libell
    cell = row.createCell(col);
    cell.setCellStyle(cellStyle);
    col = col + 1;
    cell.setCellValue(article.getArticleCatalogue().getLibelle());

    // Appock Stock
    cell = row.createCell(col);
    cell.setCellStyle(cellStyle);
    col = col + 1;
    cell.setCellValue(article.getQuantiteStock());
    cell.setCellType(CellType.NUMERIC);

    // Stock reel
    cell = row.createCell(col);
    cell.setCellStyle(cellStyle);
    cell.setCellType(CellType.NUMERIC);

}

---

From source file:net.mcnewfamily.rmcnew.model.excel.CellStyleEssence.java

License:Open Source License

public XSSFCellStyle toXSSFCellStyle(XSSFWorkbook workbook) {
    if (workbook != null) {
        XSSFCellStyle xssfCellStyle = workbook.createCellStyle();
        xssfCellStyle.setBorderTop(topBorder.toPoiCellStyle());
        xssfCellStyle.setBorderBottom(bottomBorder.toPoiCellStyle());
        xssfCellStyle.setBorderLeft(leftBorder.toPoiCellStyle());
        xssfCellStyle.setBorderRight(rightBorder.toPoiCellStyle());
        xssfCellStyle.setAlignment(horizontalAlignment.toPoiCellStyle());
        xssfCellStyle.setVerticalAlignment(verticalAlignment.toPoiCellStyle());
        xssfCellStyle.setFillPattern(fillPattern.toPoiCellStyle());
        // foreground color must be set before background color is set
        xssfCellStyle.setFillForegroundColor(foregroundColor);
        xssfCellStyle.setFillBackgroundColor(backgroundColor);
        xssfCellStyle.setWrapText(this.wrappedText);
        xssfCellStyle.setIndention(this.indentationInSpaces);
        if (this.fontEssence != null) {
            XSSFFont font = fontEssence.toXSSFFont(workbook);
            xssfCellStyle.setFont(font);
        }/*from  w  w  w  .jav a2s.  c  o m*/
        return xssfCellStyle;
    } else {
        throw new IllegalArgumentException("Cannot create XSSFCellStyle using a null XSSFWorkbook!");
    }
}

---

From source file:net.mcnewfamily.rmcnew.shared.Util.java

License:Open Source License

public static void copyXSSFCellStyle(XSSFCell srcCell, XSSFCell destCell) {
    XSSFCellStyle srcCellStyle = srcCell.getCellStyle();
    XSSFCellStyle destCellStyle = destCell.getCellStyle();
    //        destCellStyle.cloneStyleFrom(srcCellStyle);
    destCellStyle.setAlignment(srcCellStyle.getAlignment());
    destCellStyle.setVerticalAlignment(srcCellStyle.getVerticalAlignment());
    destCellStyle.setFont(srcCellStyle.getFont());
    destCellStyle.setBorderBottom(srcCellStyle.getBorderBottom());
    destCellStyle.setBorderLeft(srcCellStyle.getBorderLeft());
    destCellStyle.setBorderRight(srcCellStyle.getBorderRight());
    destCellStyle.setBorderTop(srcCellStyle.getBorderTop());
    destCellStyle.setFillPattern(srcCellStyle.getFillPattern());
    // foreground color must be set before background color is set
    destCellStyle.setFillForegroundColor(srcCellStyle.getFillForegroundColor());
    destCellStyle.setFillBackgroundColor(srcCellStyle.getFillBackgroundColor());
    destCellStyle.setIndention(srcCellStyle.getIndention());
    destCellStyle.setWrapText(srcCellStyle.getWrapText());
    destCell.setCellStyle(destCellStyle);
}

---

From source file:org.agmip.ui.afsirs.util.SummaryReportExcelFormat.java

private XSSFCellStyle getCellStyle(int type) {
    XSSFFont font = workbook.createFont();
    XSSFCellStyle style = null;

    switch (type) {
    case 1:/*from  w  ww  .  j a  v a2 s.c  om*/
        font.setFontHeightInPoints((short) 15);
        font.setFontName("IMPACT");
        font.setItalic(true);
        font.setColor(HSSFColor.BLUE.index);
        style = workbook.createCellStyle();
        style.setWrapText(true);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);
        break;

    case 2:
        font.setFontHeightInPoints((short) 15);
        font.setFontName("IMPACT");
        font.setItalic(true);
        font.setColor(HSSFColor.BLUE.index);
        style = workbook.createCellStyle();
        style.setWrapText(true);
        style.setShrinkToFit(true);
        style.setFont(font);
        break;

    case 3:
        font.setFontHeightInPoints((short) 15);
        font.setFontName("IMPACT");
        font.setItalic(true);
        font.setColor(HSSFColor.BLUE.index);
        style = workbook.createCellStyle();
        style.setWrapText(true);
        style.setShrinkToFit(true);
        style.setFont(font);
        break;

    case 4:
        font.setFontHeightInPoints((short) 15);
        font.setFontName("IMPACT");
        font.setItalic(true);
        font.setColor(HSSFColor.BLUE.index);
        style = workbook.createCellStyle();
        style.setWrapText(true);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);
        break;

    case 5:
        font.setFontHeightInPoints((short) 15);
        font.setFontName("IMPACT");
        font.setItalic(true);
        font.setColor(HSSFColor.BLUE.index);
        style = workbook.createCellStyle();
        style.setWrapText(true);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);
        break;

    }

    return style;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• »