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:com.ostrichemulators.semtool.poi.main.XlsWriter.java

License:Open Source License

/**
 * Adds a new row to the current tab. If the new row requires a new tab to
 * also be created, do it//  w  ww .j ava  2 s. c  o  m
 *
 * @param values the row data
 * @param formatting cell formatting
 * @param datatypes
 * @return true, if a new tab is created, else false
 */
public boolean addRow(String[] values, CellStyle[] formatting, IRI[] datatypes) {
    boolean newtab = (maxtabrows == rowcount);

    if (newtab) {
        // need to make a new tab
        createTab(desiredtabname, currentheader.toArray(new String[0]));
    }

    currentrow = currentsheet.createRow(rowcount++);
    for (int col = 0; col < values.length; col++) {
        Cell cell = currentrow.createCell(col);
        if (null != formatting) {
            if (formatting.length > col && null != formatting[col]) {
                cell.setCellStyle(formatting[col]);
            }
        }

        String val = values[col];
        if (null != val) {
            if (null == datatypes[col]) {
                if (NUMERIC.matcher(val).find()) {
                    cell.setCellValue(Double.parseDouble(val));
                } else {
                    cell.setCellValue(val.replaceAll("\"", ""));
                }
            } else {
                // set the datatype for this element
                String str = val.replaceAll("\"", "");
                str = String.format("\"%s\"^^<%s>", str, datatypes[col]);
                cell.setCellValue(str);
            }
        }
    }

    return newtab;
}

From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java

private void setCellBorder(Workbook wb, Cell cell) {
    CellStyle style = wb.createCellStyle();
    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());

    // Create a new font and alter it.
    Font font = wb.createFont();/*from   w w w .  ja v  a  2  s  .  co m*/
    font.setFontHeightInPoints((short) 9);
    font.setFontName("Georgia");
    style.setFont(font);

    style.setWrapText(true);
    style.setAlignment(CellStyle.VERTICAL_CENTER);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    cell.setCellStyle(style);
    cell.setCellType(Cell.CELL_TYPE_STRING);
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

public void addNew(String filePath, String testName, String cellValue[]) throws PhrescoException {
    try {//w  ww .  j av a  2s  .co  m
        //FileInputStream myInput = new FileInputStream(filePath);

        int numCol;
        int cellno = 0;
        CellStyle tryStyle[] = new CellStyle[20];
        String sheetName = testName;
        //String cellValue[] = {"","",testName,success, fail,"","","",total,testCoverage,"","",""};
        Iterator<Row> rowIterator;
        File testDir = new File(filePath);
        StringBuilder sb = new StringBuilder(filePath);
        if (testDir.isDirectory()) {
            FilenameFilter filter = new PhrescoFileFilter("", "xlsx");
            File[] listFiles = testDir.listFiles(filter);
            if (listFiles.length != 0) {
                for (File file1 : listFiles) {
                    if (file1.isFile()) {
                        sb.append(File.separator);
                        sb.append(file1.getName());
                        break;
                    }
                }
                FileInputStream myInput = new FileInputStream(sb.toString());
                OPCPackage opc = OPCPackage.open(myInput);

                XSSFWorkbook myWorkBook = new XSSFWorkbook(opc);
                XSSFSheet mySheet = myWorkBook.getSheetAt(0);
                rowIterator = mySheet.rowIterator();
                numCol = 13;
                Row next;
                for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) {
                    tryStyle[cellno] = cell.getCellStyle();
                    cellno = cellno + 1;
                }
                do {

                    int flag = 0;
                    next = rowIterator.next();
                    if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")
                            && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) {
                        for (Cell cell : next) {
                            cell.setCellType(1);
                            if (cell.getStringCellValue().equalsIgnoreCase("total")) {
                                mySheet.shiftRows((mySheet.getLastRowNum() - 1),
                                        (mySheet.getPhysicalNumberOfRows() - 1), 1);
                                flag = 1;
                            }
                            if (flag == 1)
                                break;
                        }
                        if (flag == 1)
                            break;
                    }
                } while (rowIterator.hasNext());

                Row r = null;
                if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) {
                    r = mySheet.createRow(next.getRowNum() - 1);

                } else {
                    r = mySheet.createRow(next.getRowNum() + 1);
                }
                for (int i = 0; i < numCol; i++) {
                    Cell cell = r.createCell(i);
                    cell.setCellValue(cellValue[i]);
                    // used only when sheet is 'index'
                    if (i == 2)
                        sheetName = cellValue[i];

                    cell.setCellStyle(tryStyle[i]);
                }
                if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) {
                    Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1));
                    Sheet toSheet = myWorkBook.createSheet(sheetName);
                    int i = 0;
                    Iterator<Row> copyFrom = fromSheet.rowIterator();
                    Row fromRow, toRow;
                    CellStyle newSheetStyle[] = new CellStyle[20];
                    Integer newSheetType[] = new Integer[100];
                    String newSheetValue[] = new String[100];
                    do {
                        fromRow = copyFrom.next();
                        if (fromRow.getRowNum() == 24) {
                            break;
                        }
                        toRow = toSheet.createRow(i);
                        int numCell = 0;
                        for (Cell cell : fromRow) {
                            Cell newCell = toRow.createCell(numCell);

                            cell.setCellType(1);

                            newSheetStyle[numCell] = cell.getCellStyle();
                            newCell.setCellStyle(newSheetStyle[numCell]);

                            newSheetType[numCell] = cell.getCellType();
                            newCell.setCellType(newSheetType[numCell]);
                            if (fromRow.getCell(0).getStringCellValue().length() != 1
                                    && fromRow.getCell(0).getStringCellValue().length() != 2
                                    && fromRow.getCell(0).getStringCellValue().length() != 3) {
                                newSheetValue[numCell] = cell.getStringCellValue();
                                newCell.setCellValue(newSheetValue[numCell]);
                            }

                            numCell = numCell + 1;
                        }
                        i = i + 1;
                    } while (copyFrom.hasNext());
                }
                // write to file
                FileOutputStream fileOut = new FileOutputStream(sb.toString());
                myWorkBook.write(fileOut);
                myInput.close();
                fileOut.close();
            } else {
                FilenameFilter xlsFilter = new PhrescoFileFilter("", "xls");
                File[] xlsListFiles = testDir.listFiles(xlsFilter);
                if (xlsListFiles.length != 0) {
                    for (File file2 : xlsListFiles) {
                        if (file2.isFile()) {
                            sb.append(File.separator);
                            sb.append(file2.getName());
                            break;
                        }
                    }
                    FileInputStream myInput = new FileInputStream(sb.toString());
                    HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput);

                    HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                    rowIterator = mySheet.rowIterator();
                    numCol = 13;
                    Row next;
                    for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) {
                        tryStyle[cellno] = cell.getCellStyle();
                        cellno = cellno + 1;
                    }
                    do {

                        int flag = 0;
                        next = rowIterator.next();
                        if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")
                                && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) {
                            for (Cell cell : next) {
                                cell.setCellType(1);
                                if (cell.getStringCellValue().equalsIgnoreCase("total")) {
                                    mySheet.shiftRows((mySheet.getLastRowNum() - 1),
                                            (mySheet.getPhysicalNumberOfRows() - 1), 1);
                                    flag = 1;
                                }
                                if (flag == 1)
                                    break;
                            }
                            if (flag == 1)
                                break;
                        }
                    } while (rowIterator.hasNext());

                    Row r = null;
                    if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) {
                        r = mySheet.createRow(mySheet.getLastRowNum() - 2);
                    } else {
                        r = mySheet.createRow(next.getRowNum() + 1);
                    }
                    for (int i = 0; i < numCol; i++) {
                        Cell cell = r.createCell(i);
                        cell.setCellValue(cellValue[i]);
                        // used only when sheet is 'index'
                        if (i == 2)
                            sheetName = cellValue[i];

                        cell.setCellStyle(tryStyle[i]);
                    }
                    if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) {
                        Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1));
                        Sheet toSheet = myWorkBook.createSheet(sheetName);
                        int i = 0;
                        Iterator<Row> copyFrom = fromSheet.rowIterator();
                        Row fromRow, toRow;
                        CellStyle newSheetStyle[] = new CellStyle[20];
                        Integer newSheetType[] = new Integer[100];
                        String newSheetValue[] = new String[100];
                        do {
                            fromRow = copyFrom.next();
                            if (fromRow.getRowNum() == 24) {
                                break;
                            }
                            toRow = toSheet.createRow(i);
                            int numCell = 0;
                            for (Cell cell : fromRow) {
                                Cell newCell = toRow.createCell(numCell);

                                cell.setCellType(1);

                                newSheetStyle[numCell] = cell.getCellStyle();
                                newCell.setCellStyle(newSheetStyle[numCell]);

                                newSheetType[numCell] = cell.getCellType();
                                newCell.setCellType(newSheetType[numCell]);
                                if (fromRow.getCell(0).getStringCellValue().length() != 1
                                        && fromRow.getCell(0).getStringCellValue().length() != 2
                                        && fromRow.getCell(0).getStringCellValue().length() != 3) {
                                    newSheetValue[numCell] = cell.getStringCellValue();
                                    newCell.setCellValue(newSheetValue[numCell]);
                                }

                                numCell = numCell + 1;
                                if (numCell == 15) {
                                    break;
                                }
                            }
                            i = i + 1;
                        } while (copyFrom.hasNext());
                    }
                    // write to file
                    FileOutputStream fileOut = new FileOutputStream(sb.toString());
                    myWorkBook.write(fileOut);
                    myInput.close();
                    fileOut.close();
                } else {
                    FilenameFilter odsFilter = new PhrescoFileFilter("", "ods");
                    File[] odsListFiles = testDir.listFiles(odsFilter);
                    for (File file1 : odsListFiles) {
                        if (file1.isFile()) {
                            sb.append(File.separator);
                            sb.append(file1.getName());
                            break;
                        }
                    }
                    File file = new File(sb.toString());
                    addTestSuiteToOds(file, cellValue);
                }
            }
        }
    } catch (Exception e) {
        //         throw new PhrescoException(e);
    }
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

private void writeTestCasesToXLSX(String testSuiteName, String[] cellValue, String status, int numCol,
        int cellno, CellStyle[] tryStyle, StringBuilder sb) throws PhrescoException {
    Iterator<Row> rowIterator;
    try {/*from w  w  w . ja  v a  2  s. com*/
        FileInputStream myInput = new FileInputStream(sb.toString());
        OPCPackage opc = OPCPackage.open(myInput);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(opc);
        int numberOfSheets = myWorkBook.getNumberOfSheets();
        for (int j = 0; j < numberOfSheets; j++) {
            XSSFSheet mySheet = myWorkBook.getSheetAt(j);
            if (mySheet.getSheetName().equals(testSuiteName)) {
                rowIterator = mySheet.rowIterator();
                Row next;
                for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) {
                    tryStyle[cellno] = cell.getCellStyle();
                    cellno = cellno + 1;
                }
                float totalPass = 0;
                float totalFail = 0;
                float totalNotApp = 0;
                float totalBlocked = 0;
                float notExecuted = 0;
                float totalTestCases = 0;
                for (int i = 0; i <= 22; i++) {
                    rowIterator.next();
                }
                do {
                    next = rowIterator.next();
                    if (StringUtils.isNotEmpty(getValue(next.getCell(1)))
                            && !getValue(next.getCell(0)).equalsIgnoreCase("S.NO")) {
                        String value = getValue(next.getCell(11));
                        if (StringUtils.isNotEmpty(value)) {
                            if (value.equalsIgnoreCase("success")) {
                                totalPass = totalPass + 1;
                            } else if (value.equalsIgnoreCase("failure")) {
                                totalFail = totalFail + 1;
                            } else if (value.equalsIgnoreCase("notApplicable")) {
                                totalNotApp = totalNotApp + 1;
                            } else if (value.equalsIgnoreCase("blocked")) {
                                totalBlocked = totalBlocked + 1;
                            }
                        } else {
                            notExecuted = notExecuted + 1;
                        }
                    }
                } while (rowIterator.hasNext());
                //to update the status in the index page 
                if (status.equalsIgnoreCase("success")) {
                    totalPass = totalPass + 1;
                } else if (status.equalsIgnoreCase("failure")) {
                    totalFail = totalFail + 1;
                } else if (status.equalsIgnoreCase("notApplicable")) {
                    totalNotApp = totalNotApp + 1;
                } else if (status.equalsIgnoreCase("blocked")) {
                    totalBlocked = totalBlocked + 1;
                } else {
                    notExecuted = notExecuted + 1;
                }
                totalTestCases = totalPass + totalFail + totalNotApp + totalBlocked + notExecuted;
                XSSFSheet mySheet1 = myWorkBook.getSheetAt(0);
                rowIterator = mySheet1.rowIterator();
                for (int i = 0; i <= 2; i++) {
                    rowIterator.next();
                }
                while (rowIterator.hasNext()) {
                    Row next1 = rowIterator.next();
                    if (StringUtils.isNotEmpty(getValue(next1.getCell(2)))
                            && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) {
                        TestSuite createObject = createObject(next1);
                        if (createObject.getName().equals(testSuiteName)) {
                            addCalculationsToIndex(totalPass, totalFail, totalNotApp, totalBlocked, notExecuted,
                                    totalTestCases, next1);
                        }
                    }
                }

                Row r = null;
                if (mySheet.getSheetName().equalsIgnoreCase("Index")) {
                    r = mySheet.createRow(next.getRowNum() - 1);

                } else {
                    r = mySheet.createRow(next.getRowNum() + 1);
                }
                for (int i = 0; i < numCol; i++) {
                    Cell cell = r.createCell(i);
                    cell.setCellValue(cellValue[i]);

                    cell.setCellStyle(tryStyle[i]);
                }
                FileOutputStream fileOut = new FileOutputStream(sb.toString());
                myWorkBook.write(fileOut);
                myInput.close();
                fileOut.close();
            }

        }
    } catch (PhrescoException e) {
        throw new PhrescoException(e);
    } catch (IOException e) {
        throw new PhrescoException(e);
    } catch (InvalidFormatException e) {
        throw new PhrescoException(e);
    }
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

private void writeTestCaseToXLS(String testSuiteName, String[] cellValue, String status, int numCol, int cellno,
        CellStyle[] tryStyle, StringBuilder sb) throws PhrescoException {
    Iterator<Row> rowIterator;
    try {//from  ww  w .  j av a 2  s  .  co  m
        FileInputStream myInput = new FileInputStream(sb.toString());
        HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput);
        int numberOfSheets = myWorkBook.getNumberOfSheets();
        for (int j = 0; j < numberOfSheets; j++) {
            HSSFSheet myHssfSheet = myWorkBook.getSheetAt(j);
            if (myHssfSheet.getSheetName().equals(testSuiteName)) {
                rowIterator = myHssfSheet.rowIterator();
                Row next;
                for (Cell cell : myHssfSheet.getRow((myHssfSheet.getLastRowNum()) - 1)) {
                    tryStyle[cellno] = cell.getCellStyle();
                    cellno = cellno + 1;
                    if (cellno == 15) {
                        break;
                    }
                }
                float totalPass = 0;
                float totalFail = 0;
                float totalNotApp = 0;
                float totalBlocked = 0;
                float notExecuted = 0;
                float totalTestCases = 0;
                for (int i = 0; i <= 22; i++) {
                    rowIterator.next();
                }

                do {
                    next = rowIterator.next();
                    if (StringUtils.isNotEmpty(getValue(next.getCell(1)))
                            && !getValue(next.getCell(0)).equalsIgnoreCase("S.NO")) {
                        String value = getValue(next.getCell(11));
                        if (StringUtils.isNotEmpty(value)) {
                            if (value.equalsIgnoreCase("success")) {
                                totalPass = totalPass + 1;
                            } else if (value.equalsIgnoreCase("failure")) {
                                totalFail = totalFail + 1;
                            } else if (value.equalsIgnoreCase("notApplicable")) {
                                totalNotApp = totalNotApp + 1;
                            } else if (value.equalsIgnoreCase("blocked")) {
                                totalBlocked = totalBlocked + 1;
                            }
                        } else {
                            notExecuted = notExecuted + 1;
                        }
                    }
                } while (rowIterator.hasNext());
                //to update the status in the index page 
                if (status.equalsIgnoreCase("success")) {
                    totalPass = totalPass + 1;
                } else if (status.equalsIgnoreCase("failure")) {
                    totalFail = totalFail + 1;
                } else if (status.equalsIgnoreCase("notApplicable")) {
                    totalNotApp = totalNotApp + 1;
                } else if (status.equalsIgnoreCase("blocked")) {
                    totalBlocked = totalBlocked + 1;
                } else {
                    notExecuted = notExecuted + 1;
                }
                totalTestCases = totalPass + totalFail + totalNotApp + totalBlocked + notExecuted;
                HSSFSheet mySheetHssf = myWorkBook.getSheetAt(0);
                rowIterator = mySheetHssf.rowIterator();
                for (int i = 0; i <= 2; i++) {
                    rowIterator.next();
                }
                while (rowIterator.hasNext()) {
                    Row next1 = rowIterator.next();
                    if (StringUtils.isNotEmpty(getValue(next1.getCell(2)))
                            && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) {
                        TestSuite createObject = createObject(next1);
                        if (createObject.getName().equals(testSuiteName)) {
                            addCalculationsToIndex(totalPass, totalFail, totalNotApp, totalBlocked, notExecuted,
                                    totalTestCases, next1);
                        }
                    }
                }
                Row r = null;
                if (myHssfSheet.getSheetName().equalsIgnoreCase("Index")) {
                    r = myHssfSheet.createRow(next.getRowNum() - 1);

                } else {
                    r = myHssfSheet.createRow(next.getRowNum() + 1);
                }
                for (int i = 0; i < numCol; i++) {
                    Cell cell = r.createCell(i);
                    cell.setCellValue(cellValue[i]);
                    if (tryStyle[i] != null) {
                        cell.setCellStyle(tryStyle[i]);
                    }
                }
                FileOutputStream fileOut = new FileOutputStream(sb.toString());
                myWorkBook.write(fileOut);
                myInput.close();
                fileOut.close();
            }

        }
    } catch (PhrescoException e) {
        throw new PhrescoException(e);
    } catch (FileNotFoundException e) {
        throw new PhrescoException(e);
    } catch (IOException e) {
        throw new PhrescoException(e);
    }
}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

private static void formatCell(Workbook workbook, Cell cell, ExcelCell excell,
        Map<IndexedColors, CellStyle> s_cellStyle, Font font, Font invisibleFont) {

    if (excell.getFormat() != null) {

        ExcelFormat format = excell.getFormat();

        CellStyle style = s_cellStyle.get(format.getBackgroundColor());

        if (format.isDate()) {
            // for date create a new style
            style = getDateStyle("date", cell.getSheet(), font);
            XSSFCreationHelper createHelper = (XSSFCreationHelper) cell.getSheet().getWorkbook()
                    .getCreationHelper();
            style.setDataFormat(createHelper.createDataFormat().getFormat("MMMM dd, yyyy"));
            font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            font.setBold(false);/*from w  ww  .  j a  v  a  2  s. co  m*/
            font.setFontHeightInPoints((short) 12);
            style.setFont(font);
            cell.setCellValue(new Date());
        }

        if (style == null) {
            style = workbook.createCellStyle();
            s_cellStyle.put(format.getBackgroundColor(), style);
        }

        if (format.getAlignment() > 0) {
            style.setAlignment(format.getAlignment());
        }
        if (format.getBackgroundColor() != null && !IndexedColors.WHITE.equals(format.getBackgroundColor())) {
            style.setFillForegroundColor(format.getBackgroundColor().getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }
        if (format.getTextColor() != null) {
            font.setColor(format.getTextColor().getIndex());
            style.setFont(font);
        }
        if (format.isBold()) {
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        if (format.getFontHeight() > 0) {
            font.setFontHeightInPoints(format.getFontHeight());
        }
        if (format.isWrapText()) {
            style.setWrapText(true);
        }
        style.setFont(font);
        if (format.isHideText()) {
            invisibleFont.setColor(IndexedColors.WHITE.getIndex());
            style.setFont(invisibleFont);
        }
        cell.setCellStyle(style);

    } else {
        // Let's set default formatting for free text cell
        IndexedColors defaultStyle = IndexedColors.AUTOMATIC; // we are using this index
        CellStyle style = s_cellStyle.get(defaultStyle);
        if (style == null) {
            style = workbook.createCellStyle();
            s_cellStyle.put(defaultStyle, style);
        }
        style.setWrapText(true);
        cell.setCellStyle(style);

    }

}

From source file:com.plugin.excel.util.ExcelUtil.java

License:Apache License

/**
 * @param oldCell// w w  w .  ja v  a2  s  .  com
 * @param newCell
 * @param styleMap
 */
public static void copyCell(Cell oldCell, Cell newCell, Map<Integer, CellStyle> styleMap) {
    if (styleMap != null) {
        if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
            newCell.setCellStyle(oldCell.getCellStyle());
        } else {
            int stHashCode = oldCell.getCellStyle().hashCode();
            CellStyle newCellStyle = styleMap.get(stHashCode);
            if (newCellStyle == null) {
                newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
                newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
                styleMap.put(stHashCode, newCellStyle);
            }
            newCell.setCellStyle(newCellStyle);
        }
    }
    switch (oldCell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getStringCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        newCell.setCellType(Cell.CELL_TYPE_BLANK);
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    default:
        break;
    }

}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void setCellValueFeeFormat(Workbook wb, Cell cell, Object oneCellValue, String vendor) {
    CellStyle style = wb.createCellStyle();
    style.setDataFormat(wb.createDataFormat().getFormat("$#,#0.00"));
    cell.setCellStyle(style);

    if (oneCellValue == null) {
        cell.setCellValue(Double.parseDouble("0.0"));
        return;//from  ww w.ja v  a  2  s.c  om
    }

    String feeStr = StringUtils.replace(oneCellValue.toString(), "$", StringUtils.EMPTY);
    feeStr = StringUtils.trimToEmpty(feeStr);
    feeStr = feeStr.replaceAll("\\p{javaSpaceChar}", StringUtils.EMPTY);
    if (StringUtils.isEmpty(feeStr)) {
        cell.setCellValue(Double.parseDouble("0.0"));
        return;
    }

    if (StringUtils.contains(vendor, TOLL_COMPANY_EZ_PASS_PA)
            || StringUtils.contains(vendor, TOLL_COMPANY_IPASS)
            || StringUtils.contains(vendor, TOLL_COMPANY_SUN_PASS)) {
        if (StringUtils.startsWith(feeStr, "-")) {
            feeStr = StringUtils.substring(feeStr, 1);
        } else {
            feeStr = "-" + feeStr;
        }
    }

    cell.setCellValue(Double.parseDouble(feeStr));
}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void setCellValueDateFormat(Workbook wb, Cell cell, Object oneCellValue, String vendor)
        throws ParseException {
    System.out.println("Incoming vendor = " + vendor);
    String tollCompanyDateFormat = tollCompanyToDateFormatMapping.get(vendor);
    System.out.println("Value = " + tollCompanyDateFormat);

    int columnIndex = cell.getColumnIndex();

    if (oneCellValue instanceof Date) {
        System.out.println("Incoming date is a Date Object.");
        tollCompanyDateFormat = "EEE MMM dd HH:mm:ss z yyyy";
    }//from ww w. j  a va 2s  .c  om

    String dateStr = StringUtils.trimToEmpty(oneCellValue.toString());

    if (StringUtils.isEmpty(dateStr)) {
        cell.setCellValue(StringUtils.EMPTY);
    } else {
        if (columnIndex == 7) { // Transaction time
            cell.setCellValue(convertToExpectedTimeFormatStr(dateStr, tollCompanyDateFormat));
            return;
        } else {
            cell.setCellValue(convertToExpectedDateFormat(dateStr, tollCompanyDateFormat));
        }
    }

    CellStyle style = wb.createCellStyle();
    style.setDataFormat(wb.createDataFormat().getFormat(expectedDateFormat.toPattern()));
    cell.setCellStyle(style);
}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void createColumnHeaders(LinkedList<String> expectedColumnList, Sheet sheet, CellStyle style) {
    Row headerRow = sheet.createRow(0);//from   w  w w.  j ava 2 s  .c  om
    int columnHeaderIndex = 0;
    for (String columnHeader : expectedColumnList) { // TODO redundant, use actualColumnListMap.keys instead
        sheet.setColumnWidth(columnHeaderIndex, 256 * 20);
        Cell cell = headerRow.createCell(columnHeaderIndex++);
        cell.setCellValue(columnHeader);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cell.setCellStyle(style);
    }
}