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

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

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

Introduction

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

Prototype

void setCellValue(boolean value);

Source Link

Document

Set a boolean value for the cell

Usage

From source file:com.b510.excel.client.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from w  ww .jav a  2  s. co m

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.base2.kagura.core.ExportHandler.java

License:Apache License

/**
 * Takes the output and transforms it into a Excel file.
 * @param out Output stream./*from w ww  .java2  s.  c  o m*/
 * @param rows Rows of data from reporting-core
 * @param columns Columns to list on report
 */
public void generateXls(OutputStream out, List<Map<String, Object>> rows, List<ColumnDef> columns) {
    try {
        Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
        String safeName = WorkbookUtil.createSafeSheetName("Report"); // returns " O'Brien's sales   "
        Sheet reportSheet = wb.createSheet(safeName);

        short rowc = 0;
        Row nrow = reportSheet.createRow(rowc++);
        short cellc = 0;
        if (rows == null)
            return;
        if (columns == null && rows.size() > 0) {
            columns = new ArrayList<ColumnDef>(CollectionUtils.collect(rows.get(0).keySet(), new Transformer() {
                @Override
                public Object transform(final Object input) {
                    return new ColumnDef() {
                        {
                            setName((String) input);
                        }
                    };
                }
            }));
        }
        if (columns != null) {
            for (ColumnDef column : columns) {
                Cell cell = nrow.createCell(cellc++);
                cell.setCellValue(column.getName());
            }
        }
        for (Map<String, Object> row : rows) {
            nrow = reportSheet.createRow(rowc++);
            cellc = 0;
            for (ColumnDef column : columns) {
                Cell cell = nrow.createCell(cellc++);
                cell.setCellValue(String.valueOf(row.get(column.getName())));
            }
        }
        wb.write(out);
    } catch (IOException e) {
        e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
    }

}

From source file:com.bc.util.XlsxExporter.java

public static void WriteInvoiceToFile(File output, CustomerOrder order, ArrayList<CustomerOrderItem> items) {
    try {//from w ww  .jav a2  s .  c o m
        if (output.exists()) {
            log.info(output.getName() + " exists. Deleting");
            output.delete();
            log.info("Deleted " + output.getName());
        }

        log.info("Creating xlsx file...");

        FileOutputStream fos = new FileOutputStream(output);
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet("Order");
        CellStyle style = workBook.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.ALIGN_FILL);
        Font font = workBook.createFont();
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);

        String[] columnHeaders = { "Invoice", "Salesman", "Customer Name", "Customer Code", "PO", "Ship Date",
                "Post Date", "ISBN", "ISBN13", "Title", "List Price", "Price", "Quantity", "Shipped",
                "Discount", "Extended Price" };

        log.info("Creating header row & columns");

        Row row = sheet.createRow(0);

        for (int i = 0; i < columnHeaders.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnHeaders[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, 4500);
        }

        sheet.setColumnWidth(9, 13500);

        log.info("Writing " + items.size() + " records");

        XSSFDataFormat decimalFormat = workBook.createDataFormat();
        CellStyle dstyle = workBook.createCellStyle();
        dstyle.setDataFormat(decimalFormat.getFormat("0.00"));

        int i = 1;
        for (CustomerOrderItem orderItem : items) {
            Row drow = sheet.createRow(i++);
            Hibernate.initialize(order.getCustomerOrderItems());

            String strValue;
            Float floatValue;
            Integer intVal;

            Cell cInvoice = drow.createCell(0);
            strValue = order.getInvoiceNumber();
            if (strValue == null)
                strValue = "";
            cInvoice.setCellValue(order.getInvoiceNumber());

            Cell cSalesman = drow.createCell(1);
            strValue = order.getSalesman();
            if (strValue == null)
                strValue = "";
            cSalesman.setCellValue(strValue);

            Cell cCustomerName = drow.createCell(2);
            strValue = order.getCustomer().getCompanyName();
            if (strValue == null)
                strValue = "";
            cCustomerName.setCellValue(strValue);

            Cell cCustomerCode = drow.createCell(3);
            strValue = order.getCustomerCode();
            if (strValue == null)
                strValue = "";
            cCustomerCode.setCellValue(strValue);

            Cell cPo = drow.createCell(4);
            strValue = order.getPoNumber();
            if (strValue == null)
                strValue = "";
            cPo.setCellValue(strValue);

            Cell cShipDate = drow.createCell(5);
            Date d = order.getShipDate();
            if (d == null)
                cShipDate.setCellValue("");
            else
                cShipDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear()));

            Cell cPostDate = drow.createCell(6);
            d = order.getPostDate();
            if (d == null)
                cPostDate.setCellValue("");
            else
                cPostDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear()));
            Hibernate.initialize(orderItem.getInventoryItem());
            InventoryItem item = orderItem.getInventoryItem(); //orderItem.getInventoryItem();
            if (item != null) {
                Cell cIsbn = drow.createCell(7);
                strValue = item.getIsbn();
                if (strValue == null)
                    strValue = "";
                cIsbn.setCellValue(strValue);

                Cell cIsbn13 = drow.createCell(8);
                strValue = item.getIsbn13();
                if (strValue == null)
                    strValue = "";
                cIsbn13.setCellValue(strValue);

                Cell cTitle = drow.createCell(9);
                strValue = item.getTitle();
                if (strValue == null)
                    strValue = "";
                cTitle.setCellValue(strValue);

                Cell cListPrice = drow.createCell(10);
                floatValue = item.getListPrice();
                cListPrice.setCellStyle(dstyle);
                if (floatValue == null)
                    floatValue = 0.0f;
                cListPrice.setCellValue(floatValue);

                Cell cPrice = drow.createCell(11);
                floatValue = item.getSellingPrice();
                cPrice.setCellStyle(dstyle);
                if (floatValue == null)
                    floatValue = 0.0f;
                cPrice.setCellValue(floatValue);
            }
            Cell cQuantity = drow.createCell(12);
            intVal = orderItem.getQuantity();
            log.info("Quantity : " + intVal);
            if (intVal == null)
                intVal = 0;
            cQuantity.setCellValue(intVal);

            Cell cShipped = drow.createCell(13);
            intVal = orderItem.getFilled();
            log.info("Shipped QTY : " + intVal);
            if (intVal == null)
                intVal = 0;
            cShipped.setCellValue(intVal);

            Cell cDiscount = drow.createCell(14);
            cDiscount.setCellStyle(dstyle);
            floatValue = orderItem.getDiscount();
            if (floatValue == null)
                floatValue = 0.0f;
            cDiscount.setCellValue(floatValue);

            Cell cExtendedPrice = drow.createCell(15);
            cExtendedPrice.setCellStyle(dstyle);
            BigDecimal dValue = orderItem.getTotalPrice();
            if (dValue == null)
                dValue = BigDecimal.ZERO;
            cExtendedPrice.setCellValue(dValue.doubleValue());

        }

        workBook.write(fos);
        log.info("Finished writing data, closing...");

        fos.close();
        log.info("Completed exporting data to " + output.getAbsolutePath());
    } catch (Exception ex) {
        Logger.getLogger(XlsxExporter.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.beyondb.io.ExcelControl.java

private static void setCellValue(Cell cell, Object value) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        cell.setCellValue((String) value);
        break;//w  w w .j av a  2  s  .com
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            cell.setCellValue((Date) value);
        } else {
            double v = Double.valueOf(value.toString());
            int dInt = (int) Math.floor(v);

            if (v - dInt == 0) {
                cell.setCellValue(dInt);
            } else {
                cell.setCellValue(v);

            }
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        cell.setCellValue((boolean) value);
        break;
    case Cell.CELL_TYPE_FORMULA:
        cell.setCellFormula((String) value);
        break;
    case Cell.CELL_TYPE_BLANK:
        cell.setCellValue("");
    default:

    }
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public boolean addColumn(Object[] columnName, Object[][] columnData)
        throws FileNotFoundException, IOException, InvalidFormatException {
    boolean flag = true;
    Row rowCaption;//from   w  w  w . j a  v  a  2  s.  co  m
    Sheet sheet = null;

    try {
        sheet = getSheet();
        if (sheet == null) {
            return false;
        }
        //
        rowCaption = sheet.getRow(0);
        if (rowCaption != null) {
            int columnsCount = rowCaption.getLastCellNum();
            for (int i = 0; i < columnName.length; i++) {
                Cell cell = rowCaption.createCell(columnsCount + i);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(String.valueOf(columnName[i]));
            }

            for (int i = 0; i < sheet.getLastRowNum(); i++) {
                //?
                Row tmpRow = sheet.getRow(i + 1);

                for (int cIndex = 0; cIndex < columnName.length; cIndex++) {
                    Cell cell = tmpRow.getCell(columnsCount + cIndex);
                    if (cell == null) {
                        cell = tmpRow.createCell(columnsCount + cIndex);
                    }
                    //?
                    Object obj = columnData[i][cIndex];
                    if (obj.getClass().getName().equals(Double.class.getName())) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    } else if (obj.getClass().getName().equals(String.class.getName())) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    } else {
                        //?
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    }
                    setCellValue(cell, obj);
                }

            }
        }

        m_InputStream.close();
        try ( // Write the output to a file
                FileOutputStream fileOut = new FileOutputStream(m_File)) {
            m_Workerbook.write(fileOut);
        }
    } catch (FileNotFoundException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;

    } catch (IOException | InvalidFormatException ex) {
        flag = false;
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex);
        throw ex;
    }

    return flag;
}

From source file:com.bfa.controller.ExcelWriter.java

public void writeTimeTable() {
    FileOutputStream fOutStream = null;
    try {/*from w  ww .  jav a  2  s  .c o  m*/
        File testFile = new File("TimeTable.xlsx");
        XSSFWorkbook myWorkBook = new XSSFWorkbook();
        String[] days = { "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun" };
        String[] times = { "8:55-9:50", "9:50-10:45", "11:15-12:10", "12:10-13:05", "14:00-14:55",
                "14:55-15:50" };
        Iterator timeTableIterator = timeTableDetails.iterator();
        int rowNum = 1, i = 0, cellNum = 0;
        while (timeTableIterator.hasNext()) {
            TimeTableBean currTimeTable = (TimeTableBean) timeTableIterator.next();
            XSSFSheet mySheet = myWorkBook.createSheet(currTimeTable.getClassName());
            Row row = mySheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue(currTimeTable.getClassName());
            int j = 0;
            TimeTableSlot[][] currSlot = currTimeTable.getTimeTable();
            for (TimeTableSlot[] a : currSlot) {
                row = mySheet.createRow(rowNum++);
                cell = row.createCell(0);
                cell.setCellValue(days[j++]);
                //System.out.println(days[j-1]);
                Row secondRow = mySheet.createRow(rowNum++);
                row = mySheet.createRow(rowNum++);
                for (TimeTableSlot p : a) {
                    if (p != null) {
                        int timeSlot = 0;
                        cell = row.createCell(cellNum);
                        Cell secondCell = secondRow.createCell(cellNum++);
                        secondCell.setCellValue(times[timeSlot++]);
                        cell.setCellValue(p.getSubject());
                    }
                    //System.out.println(p.subjectName+"----"+p.teacherName);
                }
                cellNum = 0;
                //System.out.println("\n");
            }
            rowNum = 1;
        }
        fOutStream = new FileOutputStream(testFile);
        myWorkBook.write(fOutStream);
        System.out.println("Success");
    } catch (Exception ex) {
        Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            fOutStream.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.bfds.saec.batch.file.domain.out.damasco_foreign.ForeignTaxRecHeaderCallback.java

License:Apache License

@Override
public void writeHeader(Row row) {
    String[] names = getHeader().split(",");
    for (int i = 0; i < names.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(names[i]);
    }/*from  w w w  .jav a  2 s . co  m*/
}

From source file:com.biomeris.i2b2.export.engine.ExcelCreator.java

License:Open Source License

public void colorTest() {
    for (short k = (short) 0; k < 3; k++) {
        currentSheet = workbook.createSheet("ct " + k);
        currentSheetRow = 0;// ww w  . j  a v  a  2 s.  c  om

        for (short i = (short) 0; i < 82; i++) {
            Row headerRow_XLSX = currentSheet.createRow(currentSheetRow++);

            Cell c2 = headerRow_XLSX.createCell(0);
            c2.setCellValue("" + i);

            for (short j = (short) 0; j < 19; j++) {
                CellStyle colorStyle = workbook.createCellStyle();
                colorStyle.setFillForegroundColor(k);
                colorStyle.setFillBackgroundColor(i);
                colorStyle.setFillPattern(j);
                Cell c1 = headerRow_XLSX.createCell(j + 1);
                c1.setCellStyle(colorStyle);
                c1.setCellValue("" + j);
            }
        }
    }

}

From source file:com.biomeris.i2b2.export.engine.ExcelCreator.java

License:Open Source License

public void writeHeader(List<String> headerElements) {
    Row headerRow_XLSX = currentSheet.createRow(currentSheetRow++);
    headerRow_XLSX.setHeight((short) 350);

    for (int i = 0; i < headerElements.size(); i++) {
        Cell c = headerRow_XLSX.createCell(i);
        c.setCellStyle(headerStyle);//  ww  w  .  ja  v a 2s .c  o  m
        c.setCellValue(headerElements.get(i));
    }
}

From source file:com.biomeris.i2b2.export.engine.ExcelCreator.java

License:Open Source License

public void writeObservations(Concept concept, List<WObservation> observations, boolean maskPatientids) {
    for (WObservation obs : observations) {
        Row row = currentSheet.createRow(currentSheetRow++);

        // 0//from   w  w  w  .  j a va  2s .  c  o m
        if (maskPatientids) {
            String patientId = obs.getPatientId();
            Integer maskedId;
            if (maskedPatientMaps.containsKey(patientId)) {
                maskedId = maskedPatientMaps.get(patientId);
            } else {
                maskedId = nextMaskedId;
                maskedPatientMaps.put(patientId, nextMaskedId++);
            }
            row.createCell(0).setCellValue(maskedId);
        } else {
            row.createCell(0).setCellValue(obs.getPatientId());
        }

        // 1
        row.createCell(1).setCellValue(obs.getEncounterId());

        // 2
        Cell cell2 = row.createCell(2);
        cell2.setCellStyle(dateStyle);
        cell2.setCellValue(obs.getStartDate().getTime());

        // 3
        if (obs.getEndDate() != null) {
            Cell cell3 = row.createCell(3);
            cell3.setCellStyle(dateStyle);
            cell3.setCellValue(obs.getEndDate().getTime());
        }

        // 4
        row.createCell(4).setCellValue(obs.getInstanceNum());

        // 5
        row.createCell(5).setCellValue(concept.getName());

        // 6
        if (concept.getType().equals(ExportConstants.LEAF)) {
            row.createCell(6).setCellValue(obs.getValueTypeCd());
        } else {
            row.createCell(6).setCellValue(ExportConstants.FOLDER);
        }

        // 7
        row.createCell(7).setCellValue(obs.getConceptCd());

        // 8
        row.createCell(8).setCellValue(concept.getChildrenMap().get(obs.getConceptCd()));

        if (concept.getModifier() != null) {
            // 9
            row.createCell(9).setCellValue(concept.getModifier().getName());

            // 10
            row.createCell(10).setCellValue(obs.getModifierCd());

            // 11
            row.createCell(11).setCellValue(concept.getModifier().getChildrenMap().get(obs.getModifierCd()));
        }

        // 12
        // 13
        switch (obs.getValueTypeCd()) {
        case ExportConstants.VALTYPE_N:
            row.createCell(12).setCellValue(operator(obs.getTvalChar()));
            row.createCell(13).setCellValue(obs.getNvalNum());
            break;
        default:
            row.createCell(13).setCellValue(obs.getTvalChar());
            break;
        }

        // 14
        row.createCell(14).setCellValue(obs.getUnitsCd());
    }
}