Example usage for org.apache.poi.ss.usermodel Cell setCellStyle

List of usage examples for org.apache.poi.ss.usermodel Cell setCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellStyle

Introduction

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

Prototype

void setCellStyle(CellStyle style);

Source Link

Document

Set the style for the cell.

Usage

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

public void addCatalogue(MacroscopicTopology macroscopicTopology, TrainPathSlotCatalogue catalogue) {
    String colLayoutString = getPropertyValue(tpaProps.TRAINPATHS_COL_LAYOUT);
    List<ColumnIdentifier> cols = new ArrayList<ColumnIdentifier>(trainPathLayout.values().length);
    for (trainPathLayout l : trainPathLayout.values()) {
        cols.add(l);/*from ww  w  .  j av  a  2  s  . co m*/
    }
    Map<ColumnIdentifier, Integer> colLayoutMapping = getColLayoutMapping(colLayoutString, cols);
    int deptimeColIndex = colLayoutMapping.get(trainPathLayout.DEPTIME);
    int arrtimeColIndex = colLayoutMapping.get(trainPathLayout.ARRTIME);
    int idColIndex = colLayoutMapping.get(trainPathLayout.ID);

    for (PeriodicalTrainPathSlot periodicalTrainPathSlot : catalogue.getTrainPathSlots()) {
        String linkName = periodicalTrainPathSlot.getTrainPathSectionName();

        // Create sheet if it does not exist yet
        if (wb.getSheet(linkName) == null) {
            Sheet sheet = wb.createSheet(linkName);
            // header
            Row headerRow = sheet.createRow(0);
            for (ColumnIdentifier col : colLayoutMapping.keySet()) {
                int i = colLayoutMapping.get(col);
                Cell cell = headerRow.getCell(i, Row.CREATE_NULL_AS_BLANK);
                cell.setCellValue(col.name());
            }
        }

        Sheet sheet = wb.getSheet(linkName);
        int rowNb;
        for (rowNb = 1; rowNb < sheet.getPhysicalNumberOfRows(); rowNb++) {
            if (sheet.getRow(rowNb) == null || StringUtils.isBlank(
                    getCellValueString(sheet.getRow(rowNb).getCell(idColIndex, Row.CREATE_NULL_AS_BLANK)))) {
                break;
            }
        }
        Row row = sheet.createRow(rowNb);

        TrainPathSlot slot = periodicalTrainPathSlot.getSlots().get(0);
        int depHour = slot.getStartTime().getHourOfDay();
        int depMinutes = slot.getStartTime().getMinuteOfHour();
        int arrHour = slot.getEndTime().getHourOfDay();
        int arrMinutes = slot.getEndTime().getMinuteOfHour();
        double deptime = DateUtil
                .convertTime(String.format("%02d", depHour) + ":" + String.format("%02d", depMinutes));
        double arrtime = DateUtil
                .convertTime(String.format("%02d", arrHour) + ":" + String.format("%02d", arrMinutes));

        Cell cell = row.getCell(deptimeColIndex, Row.CREATE_NULL_AS_BLANK);
        cell.setCellStyle(timestyle);
        cell.setCellValue(deptime);
        cell = row.getCell(arrtimeColIndex, Row.CREATE_NULL_AS_BLANK);
        cell.setCellStyle(timestyle);
        cell.setCellValue(arrtime);

        cell = row.getCell(idColIndex, Row.CREATE_NULL_AS_BLANK);
        cell.setCellValue(periodicalTrainPathSlot.getName());
    }
}

From source file:cn.afterturn.easypoi.excel.export.base.BaseExportService.java

License:Apache License

/**
 * Cell//from ww  w  .j a v a2 s . c o  m
 */
public void createStringCell(Row row, int index, String text, CellStyle style, ExcelExportEntity entity) {
    Cell cell = row.createCell(index);
    if (style != null && style.getDataFormat() > 0 && style.getDataFormat() < 12) {
        cell.setCellValue(Double.parseDouble(text));
        cell.setCellType(CellType.NUMERIC);
    } else {
        RichTextString rtext;
        if (type.equals(ExcelType.HSSF)) {
            rtext = new HSSFRichTextString(text);
        } else {
            rtext = new XSSFRichTextString(text);
        }
        cell.setCellValue(rtext);
    }
    if (style != null) {
        cell.setCellStyle(style);
    }
    addStatisticsData(index, text, entity);
}

From source file:cn.afterturn.easypoi.excel.export.base.BaseExportService.java

License:Apache License

/**
 * Cell/* ww  w.  j av a  2 s.  c o m*/
 */
public void createDoubleCell(Row row, int index, String text, CellStyle style, ExcelExportEntity entity) {
    Cell cell = row.createCell(index);
    cell.setCellType(CellType.NUMERIC);
    if (text != null && text.length() > 0) {
        try {
            cell.setCellValue(Double.parseDouble(text));
        } catch (NumberFormatException e) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(text);
        }
    }

    if (style != null) {
        cell.setCellStyle(style);
    }
    addStatisticsData(index, text, entity);
}

From source file:cn.afterturn.easypoi.excel.imports.ExcelImportService.java

License:Apache License

/**
 * ??// w ww. j av a  2 s .co m
 */
public boolean verifyingDataValidity(Object object, Row row, ImportParams params, boolean isMap,
        StringBuilder fieldErrorMsg) {
    boolean isAdd = true;
    Cell cell = null;
    if (params.isNeedVerify()) {
        String errorMsg = PoiValidationUtil.validation(object, params.getVerifyGroup());
        if (StringUtils.isNotEmpty(errorMsg)) {
            cell = row.createCell(row.getLastCellNum());
            cell.setCellValue(errorMsg);
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(errorMsg);
            }
            isAdd = false;
            verifyFail = true;
        }
    }
    if (params.getVerifyHandler() != null) {
        ExcelVerifyHandlerResult result = params.getVerifyHandler().verifyHandler(object);
        if (!result.isSuccess()) {
            if (cell == null) {
                cell = row.createCell(row.getLastCellNum());
            }
            cell.setCellValue(
                    (StringUtils.isNoneBlank(cell.getStringCellValue()) ? cell.getStringCellValue() + "," : "")
                            + result.getMsg());
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(
                        (StringUtils.isNoneBlank(model.getErrorMsg()) ? model.getErrorMsg() + "," : "")
                                + result.getMsg());
            }
            isAdd = false;
            verifyFail = true;
        }
    }
    if ((params.isNeedVerify() || params.getVerifyHandler() != null) && fieldErrorMsg.length() > 0) {
        if (object instanceof IExcelModel) {
            IExcelModel model = (IExcelModel) object;
            model.setErrorMsg((StringUtils.isNoneBlank(model.getErrorMsg()) ? model.getErrorMsg() + "," : "")
                    + fieldErrorMsg.toString());
        }
        if (cell == null) {
            cell = row.createCell(row.getLastCellNum());
        }
        cell.setCellValue(
                (StringUtils.isNoneBlank(cell.getStringCellValue()) ? cell.getStringCellValue() + "," : "")
                        + fieldErrorMsg.toString());
        isAdd = false;
        verifyFail = true;
    }
    if (cell != null) {
        cell.setCellStyle(errorCellStyle);
        failRow.add(row);
        if (isMap) {
            ((Map) object).put("excelErrorMsg", cell.getStringCellValue());
        }
    } else {
        successRow.add(row);
    }
    return isAdd;
}

From source file:cn.afterturn.easypoi.util.PoiSheetUtil.java

License:Apache License

private static void cloneCell(Cell cNew, Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());

    switch (cNew.getCellType()) {
    case BOOLEAN: {
        cNew.setCellValue(cOld.getBooleanCellValue());
        break;//from   ww  w.j  a v  a 2 s  . c om
    }
    case NUMERIC: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }
    case STRING: {
        cNew.setCellValue(cOld.getStringCellValue());
        break;
    }
    case ERROR: {
        cNew.setCellValue(cOld.getErrorCellValue());
        break;
    }
    case FORMULA: {
        cNew.setCellFormula(cOld.getCellFormula());
        break;
    }
    default:
        cNew.setCellValue(cOld.getStringCellValue());
    }

}

From source file:cn.bzvs.excel.export.base.ExcelExportBase.java

License:Apache License

/**
 * Cell/*from w w  w . j  a  v a  2 s  .co m*/
 *
 * @param row
 * @param index
 * @param text
 * @param style
 * @param entity
 */
public void createStringCell(Row row, int index, String text, CellStyle style, ExcelExportEntity entity) {
    Cell cell = row.createCell(index);
    if (style != null && style.getDataFormat() > 0 && style.getDataFormat() < 12) {
        cell.setCellValue(Double.parseDouble(text));
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else {
        RichTextString Rtext;
        if (type.equals(ExcelType.HSSF)) {
            Rtext = new HSSFRichTextString(text);
        } else {
            Rtext = new XSSFRichTextString(text);
        }
        cell.setCellValue(Rtext);
    }
    if (style != null) {
        cell.setCellStyle(style);
    }
    addStatisticsData(index, text, entity);
}

From source file:cn.bzvs.excel.export.base.ExcelExportBase.java

License:Apache License

/**
 * Cell//from   ww w .j  av a2s . c om
 *
 * @param row
 * @param index
 * @param text
 * @param style
 * @param entity
 */
public void createDoubleCell(Row row, int index, String text, CellStyle style, ExcelExportEntity entity) {
    Cell cell = row.createCell(index);
    if (text != null && text.length() > 0) {
        cell.setCellValue(Double.parseDouble(text));
    } else {
        cell.setCellValue(-1);
    }
    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    if (style != null) {
        cell.setCellStyle(style);
    }
    addStatisticsData(index, text, entity);
}

From source file:cn.bzvs.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * ??/*from   w w w  .  j a  v  a  2s.  c  om*/
 * @param object
 * @param row
 * @param params
 * @param pojoClass
 * @return
 */
private boolean verifyingDataValidity(Object object, Row row, ImportParams params, Class<?> pojoClass) {
    boolean isAdd = true;
    Cell cell = null;
    if (params.isNeedVerfiy()) {
        String errorMsg = PoiValidationUtil.validation(object);
        if (StringUtils.isNotEmpty(errorMsg)) {
            cell = row.createCell(row.getLastCellNum());
            cell.setCellValue(errorMsg);
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(errorMsg);
            } else {
                isAdd = false;
            }
            verfiyFail = true;
        }
    }
    if (params.getVerifyHanlder() != null) {
        ExcelVerifyHanlderResult result = params.getVerifyHanlder().verifyHandler(object);
        if (!result.isSuccess()) {
            if (cell == null)
                cell = row.createCell(row.getLastCellNum());
            cell.setCellValue(
                    (StringUtils.isNoneBlank(cell.getStringCellValue()) ? cell.getStringCellValue() + "," : "")
                            + result.getMsg());
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(
                        (StringUtils.isNoneBlank(model.getErrorMsg()) ? model.getErrorMsg() + "," : "")
                                + result.getMsg());
            } else {
                isAdd = false;
            }
            verfiyFail = true;
        }
    }
    if (cell != null)
        cell.setCellStyle(errorCellStyle);
    return isAdd;
}

From source file:cn.com.zhbook.component.poi.PoiPerformanceTest.java

License:Apache License

private static void addContent(Workbook workBook, boolean isHType, int rows, int cols) {
    Map<String, CellStyle> styles = createStyles(workBook);

    Sheet sheet = workBook.createSheet("Main Sheet");

    Cell headerCell = sheet.createRow(0).createCell(0);
    headerCell.setCellValue("Header text is spanned across multiple cells");
    headerCell.setCellStyle(styles.get("header"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));

    int sheetNo = 0;
    int rowIndexInSheet = 1;
    double value = 0;
    Calendar calendar = Calendar.getInstance();
    for (int rowIndex = 0; rowIndex < rows; rowIndex++) {
        if (isHType && sheetNo != rowIndex / 0x10000) {
            sheet = workBook.createSheet("Spillover from sheet " + (++sheetNo));
            headerCell.setCellValue("Header text is spanned across multiple cells");
            headerCell.setCellStyle(styles.get("header"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));
            rowIndexInSheet = 1;//from w  w w. j  a  va 2s  .  c om
        }

        Row row = sheet.createRow(rowIndexInSheet);
        for (int colIndex = 0; colIndex < cols; colIndex++) {
            value = populateCell(styles, value, calendar, rowIndex, row, colIndex);
        }
        rowIndexInSheet++;
    }
}

From source file:cn.com.zhbook.component.poi.PoiPerformanceTest.java

License:Apache License

private static double populateCell(Map<String, CellStyle> styles, double value, Calendar calendar, int rowIndex,
        Row row, int colIndex) {
    Cell cell = row.createCell(colIndex);
    String address = new CellReference(cell).formatAsString();
    switch (colIndex) {
    case 0:/*ww  w . j a v  a  2 s. c om*/
        // column A: default number format
        cell.setCellValue(value++);
        break;
    case 1:
        // column B: #,##0
        cell.setCellValue(value++);
        cell.setCellStyle(styles.get("#,##0.00"));
        break;
    case 2:
        // column C: $#,##0.00
        cell.setCellValue(value++);
        cell.setCellStyle(styles.get("$#,##0.00"));
        break;
    case 3:
        // column D: red bold text on yellow background
        cell.setCellValue(address);
        cell.setCellStyle(styles.get("red-bold"));
        break;
    case 4:
        // column E: boolean
        // TODO booleans are shown as 1/0 instead of TRUE/FALSE
        cell.setCellValue(rowIndex % 2 == 0);
        break;
    case 5:
        // column F:  date / time
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("m/d/yyyy"));
        calendar.roll(Calendar.DAY_OF_YEAR, -1);
        break;
    case 6:
        // column F: formula
        // TODO formulas are not yet supported  in SXSSF
        //cell.setCellFormula("SUM(A" + (rowIndex+1) + ":E" + (rowIndex+1)+ ")");
        //break;
    default:
        cell.setCellValue(value++);
        break;
    }
    return value;
}