Example usage for org.apache.poi.xssf.usermodel XSSFCell setCellStyle

List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCell
  6. setCellStyle

Introduction

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

Prototype

@Override
public void setCellStyle(CellStyle style)

Source Link

Document

Set the style for the cell.

Usage

From source file:mvjce.Excel_operations.java

public static void insert_internals(XSSFWorkbook workbook, XSSFSheet spreadsheet) {
    try {/*from  www  .jav a  2 s .  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  2s. co  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   www.  j a v a  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);/*from   www.j  a v  a 2s  . c om*/
    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//  w  ww.  j av a  2 s  . com
    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  ww  w . j a v a 2  s. co 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.CellEssence.java

License:Open Source License

public XSSFCell toXSSFCell(XSSFRow row, int columnIndex) {
    if (row != null && columnIndex >= 0) {
        XSSFCell cell = row.createCell(columnIndex);
        cell.setCellType(this.cellType.toPoiCellType());
        if (this.cellStyleEssence != null) {
            XSSFCellStyle cellStyle = this.cellStyleEssence.toXSSFCellStyle(row.getSheet().getWorkbook());
            cell.setCellStyle(cellStyle);
        }//from   w  w  w  . ja v a  2  s.  c  o  m
        cell.setCellValue(this.value);
        return cell;
    } else {
        throw new IllegalArgumentException(
                "Cannot create XSSFCell on null XSSFRow cannot be null or using invalid columnIndex!");
    }
}

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:offishell.excel.Excel.java

License:MIT License

private XSSFRow findFirstBlankRow() {
    XSSFRow head = sheet.getRow(0);//from ww w  . j av  a 2 s. c  om

    // compute head size
    int headerSize = 0;

    for (; headerSize < head.getLastCellNum(); headerSize++) {
        Cell cell = head.getCell(headerSize);

        if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
            headerSize--;
            break;
        }
    }

    row: for (int i = 1; i < sheet.getLastRowNum(); i++) {
        XSSFRow row = sheet.getRow(i);

        if (row == null) {
            row = sheet.createRow(i);
            row.setHeightInPoints(30f);
        }

        for (int j = 0; j < headerSize; j++) {
            XSSFCell cell = row.getCell(j);

            if (cell == null) {
                XSSFCell created = row.createCell(j);
                created.setCellStyle(baseStyle);
            } else if (cell.getCellTypeEnum() != CellType.BLANK) {
                continue row;
            }
        }
        return row;
    }

    XSSFRow row = sheet.getRow(sheet.getLastRowNum());

    if (row == null) {
        row = sheet.createRow(sheet.getLastRowNum());
        row.setHeightInPoints(30f);
    }

    for (int j = 0; j < headerSize; j++) {
        XSSFCell cell = row.getCell(j);

        if (cell == null) {
            XSSFCell created = row.createCell(j);
            created.setCellStyle(baseStyle);
        }
    }
    return row;
}

From source file:opn.greenwebs.FXMLDocumentController.java

private File createStockFile(List<ItemDB> list) {
    int nSize = list.size();
    XSSFWorkbook wbs = createStockWorkbook();

    XSSFSheet sheetStock = wbs.getSheet("Digital Version");
    List<XSSFTable> lTables = sheetStock.getTables();
    // Create a FormulaEvaluator to use
    FormulaEvaluator mainWorkbookEvaluator = sheetStock.getWorkbook().getCreationHelper()
            .createFormulaEvaluator();/*from   w w  w .j  a  v a 2s .  co  m*/
    File fStock = createFilename("STK", "");
    Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault()));
    Row rowed = sheetStock.getRow(6);
    Cell celled = rowed.getCell(10);
    CellStyle cellStyle = celled.getCellStyle();
    XSSFFont font = sheetStock.getWorkbook().createFont();
    font.setFontHeight(14);
    cellStyle.setFont(font);
    celled.setCellValue(Date.from(instant));
    celled.setCellStyle(cellStyle);
    rowed = sheetStock.getRow(10);
    celled = rowed.getCell(2);
    celled.setCellValue(fStock.getName().substring(0, fStock.getName().length() - 5));
    if (!lTables.isEmpty()) {
        XSSFTable table = lTables.get(0);
        table.getCTTable()
                .setRef(new CellRangeAddress(table.getStartCellReference().getRow(),
                        table.getEndCellReference().getRow() + nSize, table.getStartCellReference().getCol(),
                        table.getEndCellReference().getCol()).formatAsString());
        XSSFRow row;
        XSSFCell cell;
        font = sheetStock.getWorkbook().createFont();
        font.setFontHeight(14);
        int nCellRef = table.getStartCellReference().getRow() + 1;
        for (ItemDB itemdb : list) {
            row = sheetStock.createRow(nCellRef++);
            cell = row.createCell(0);
            cellStyle = cell.getCellStyle();
            cell.setCellValue(itemdb.getDblQty());
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell = row.createCell(1);
            cell.setCellValue(itemdb.getStrMfr());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(2);
            cell.setCellValue(itemdb.getStrSKU());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(3);
            cell.setCellValue(itemdb.getStrDescrip());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(4);
            cell.setCellValue(itemdb.getStrSupplier());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(5);
            cell.setCellValue(itemdb.getStrSupPart());
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            //cell.setCellStyle(cellStyle);
            cell = row.createCell(6);
            cell.setCellValue(itemdb.getDblSalePrice());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(7);
            cell.setCellValue(itemdb.getDblCost());
            cell.setCellStyle(cellStyle);
            /*cell = row.createCell(8);
            cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula("IF(A" + nCellRef + ">0,IF(G" + nCellRef + ">0,IF(H" + nCellRef + ">0,A" + nCellRef + "*G" + nCellRef + "-A" + nCellRef + "*H" + nCellRef + ",\"\"),\"\"),\"\")");
            mainWorkbookEvaluator.evaluateFormulaCell(cell);
            cell.setCellStyle(cellStyle);
            cell = row.createCell(9);
            cell.setCellFormula("IF(I" + nCellRef + "<>\"\",I" + nCellRef + "/(A" + nCellRef + "*G" + nCellRef + "),\"\")");
            mainWorkbookEvaluator.evaluateFormulaCell(cell);
            CellStyle style = wbs.createCellStyle();
            style.setDataFormat(wbs.createDataFormat().getFormat("0%"));
            cell.setCellStyle(style);*/
            mainWorkbookEvaluator.evaluateAll();
        }

        try {
            try (FileOutputStream fileOut = new FileOutputStream(fStock)) {
                wbs.write(fileOut);
                return fStock;
            }
        } catch (FileNotFoundException ex) {
            logger.info(ex.getLocalizedMessage());
        } catch (IOException ex) {
            logger.info(ex.getLocalizedMessage());
        }
    }
    return null;
}