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:ch.astina.hesperid.util.jasper.JasperExcelStreamResponse.java

License:Apache License

@Override
public void exportReportToStream(JasperPrint jasperPrint, OutputStream outputStream) throws Exception {
    JRXlsExporter exporter = new JRXlsExporter();
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
    exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, baos);
    exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS,
            this.removeEmptySpaceBetweenRows);
    exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS,
            this.removeEmptySpaceBetweenColumns);
    exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, this.printWhitePageBackground);
    exporter.setParameter(JRXlsExporterParameter.IS_FONT_SIZE_FIX_ENABLED, this.fixFontSize);
    exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_GRAPHICS, this.ignoreGraphics);
    exporter.exportReport();/*w  w w.jav  a  2  s .  co m*/

    HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(baos.toByteArray()));
    workbook.getSheetAt(0).setAutobreaks(true);
    workbook.getSheetAt(0).getPrintSetup().setFitHeight((short) jasperPrint.getPages().size());
    workbook.getSheetAt(0).getPrintSetup().setFitWidth((short) 1);

    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");

    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

    for (Integer x = 0; x < workbook.getSheetAt(0).getPhysicalNumberOfRows(); x++) {
        HSSFRow row = workbook.getSheetAt(0).getRow(x);

        Iterator<Cell> ci = row.cellIterator();

        Cell c = null;
        Date d = null;

        while (ci.hasNext()) {
            c = ci.next();
            try {
                d = sdf.parse(c.getStringCellValue().trim());
                c.setCellValue(d);
                c.setCellStyle(cellStyle);
            } catch (Exception e) {
            }
        }
    }

    workbook.write(outputStream);
}

From source file:ch.bfh.lca._15h.library.export.ExportToExcel.java

/***
 * Generate an Excel file based on a list of results.
 * Use the name of the fields described in the GenericResultRow to label the columns.
 * @param language Language of the label in the Excel file
 * @param sheetTitle Title of the sheet in the Excel file
 * @param tableTitle Title of the table in the Excel file
 * @param rows Arrays of rows to include in the listing
 * @param excelFilePath Path of the outputed file
 * @throws FileNotFoundException//from w  w w. ja  va 2 s  . co  m
 * @throws IOException 
 */
public static void exportToExcel(Translation.TRANSLATION_LANGUAGE language, String sheetTitle,
        String tableTitle, GenericResultRow[] rows, String excelFilePath)
        throws FileNotFoundException, IOException {
    //Workbook wb = new HSSFWorkbook(); //xls
    Workbook wb = new SXSSFWorkbook(); //xlsx
    //create new sheet
    Sheet sheet1 = wb.createSheet(sheetTitle);
    Row row;
    Cell cell;
    String translation;

    int rowIndex = 0;

    //add title
    row = sheet1.createRow((short) rowIndex);
    cell = row.createCell(0);
    cell.setCellValue(tableTitle);
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 24);
    //font.setFontName("Courier New");
    style.setFont(font);
    cell.setCellStyle(style);

    //add rows
    for (int i = 0; i < rows.length; i++) {
        //if first line, write col names
        if (i == 0) {
            row = sheet1.createRow((short) rowIndex + 2);

            for (int j = 0; j < rows[i].getColNames().length; j++) {
                cell = row.createCell(j);

                //look for translation
                translation = Translation.getForKey(language, rows[i].getColNames()[j]);
                if (translation == null) {
                    translation = rows[i].getColNames()[j]; //if doesn't found a translation for the column take name of col
                }
                cell.setCellValue(translation);
            }
        }

        row = sheet1.createRow((short) (rowIndex + i + 3));

        for (int j = 0; j < rows[i].getColNames().length; j++) {
            cell = row.createCell(j);
            cell.setCellValue(rows[i].getValueAt(j).toString());
        }
    }

    //write to the file
    FileOutputStream fileOut = new FileOutputStream(excelFilePath);
    wb.write(fileOut);
    fileOut.close();

    wb.close();
}

From source file:ch.bfh.lca._15h.library.export.ExportToExcel.java

/***
 * Prototype function. Not yet functional.
 * Allow to generate an age pyramid graphic in Excel by using an existing Excel Template.
 * @param language Language of the label in the Excel file
 * @param rows Arrays of rows to include in the listing
 * @param excelFilePath Path of the outputed file
 * @throws FileNotFoundException//from   www  .  j  ava  2 s  . c om
 * @throws IOException
 * @throws InvalidFormatException 
 */
public static void exportToAgePyramid(Translation.TRANSLATION_LANGUAGE language, GenericResultRow[] rows,
        String excelFilePath) throws FileNotFoundException, IOException, InvalidFormatException {
    //open template
    URL url = Translation.class.getClassLoader()
            .getResource("ch/bfh/lca/_15h/library/export/template/alter-pyramide-v2.xlsx");
    //Workbook wb = WorkbookFactory.create(new File(url.getPath()));
    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(url.getPath())));

    Sheet sheet = wb.getSheetAt(0);

    //http://www.programming-free.com/2012/12/create-charts-in-excel-using-java.html
    //https://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook
    Row row;
    Cell cell;

    for (int i = 0; i < 20; i++) {
        row = sheet.getRow(i + 1);
        if (row == null) {
            row = sheet.createRow(i + 1);
        }

        for (int j = 0; j < 3; j++) {
            cell = row.getCell(j);
            if (cell == null) {
                cell = row.createCell(j);
            }

            switch (j) {
            case 0:
                cell.setCellValue(i);
                break;
            case 1:
                cell.setCellValue(i * j * -1);
                break;
            case 2:
                cell.setCellValue(i * j);
                break;
            }
        }
    }

    //redefine data range
    //http://thinktibits.blogspot.ch/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html
    XSSFSheet sheet1 = wb.getSheetAt(0);
    XSSFTable table = sheet1.getTables().get(0);
    CTTable cttable = table.getCTTable();

    AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(20, 2));
    /* Set Range to the Table */
    cttable.setRef(my_data_range.formatAsString());
    // cttable.setDisplayName("DATEN");      /* this is the display name of the table */
    //cttable.setName("test");    /* This maps to "displayName" attribute in &lt;table&gt;, OOXML */
    //cttable.setId(1L); //id attribute against table as long value

    /*
    //redefine data range
    Name rangeCell = wb.getName("DATEN");
    //Set new range for named range 
    //String reference = sheetName + "!$C$" + (deface + 1) + ":$C$" + (rowNum + deface);
    String reference = sheet.getSheetName() + "!$A$2:$C$20";
    //Assigns range value to named range
    rangeCell.setRefersToFormula(reference);
    */

    //write to the file
    FileOutputStream fileOut = new FileOutputStream(excelFilePath);
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

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

License:Apache License

private void emptyWorksheetColumns(String wsName, int rowsFrom, List<Integer> colsToDelete) {
    String trainpathsNoOperatingDayMarker = getPropertyValue(tpaProps.TRAINPATHS_NO_OPERATING_DAY_MARKER);

    Sheet sheet = wb.getSheet(wsName);/*w ww.  j av  a 2 s  . c  o m*/
    for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        for (Integer j : colsToDelete) {
            Cell cell = row.getCell(j);
            if (cell != null && !formatter.formatCellValue(cell).equals(trainpathsNoOperatingDayMarker)) {
                cell.setCellValue((String) null);
            }
        }
    }
}

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

License:Apache License

/**
 * @param wsName//from   ww w .j a  v a  2 s  .  c o m
 * @param rowsFrom
 * @param colsToDelete
 */
private void resetRequestAllocations(String wsName, int rowsFrom, List<Integer> colsToDelete) {
    String requestsAllocatedDayMarker = getPropertyValue(tpaProps.REQUESTS_ALLOCATED_DAY_MARKER);
    String requestsRequestedDayMarker = getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER);

    Sheet sheet = wb.getSheet(wsName);
    for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        for (Integer j : colsToDelete) {
            Cell cell = row.getCell(j);
            if (cell != null && formatter.formatCellValue(cell).equals(requestsAllocatedDayMarker)) {
                cell.setCellValue(requestsRequestedDayMarker);
            }
        }
    }
}

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

License:Apache License

public void generateRequests(MacroscopicTopology macroscopicTopology, int requestsPerHour, int durationMinutes,
        int offset, String from, String to, Periodicity p) {
    String colLayoutString = getPropertyValue(tpaProps.REQUESTS_COL_LAYOUT);

    List<ColumnIdentifier> cols = new ArrayList<ColumnIdentifier>(requestsLayout.values().length);
    for (requestsLayout l : requestsLayout.values()) {
        cols.add(l);//  w  ww. j ava 2 s .c  o  m
    }
    Map<ColumnIdentifier, Integer> colLayoutMapping = getColLayoutMapping(colLayoutString, cols);

    int arrtimeColIndex = colLayoutMapping.get(requestsLayout.ARRTIME);
    int fromColIndex = colLayoutMapping.get(requestsLayout.FROM);
    int toColIndex = colLayoutMapping.get(requestsLayout.TO);
    int deptimeColIndex = colLayoutMapping.get(requestsLayout.DEPTIME);
    int idColIndex = colLayoutMapping.get(requestsLayout.ID);
    String requestedMarker = getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER);

    List<String> linkNames = macroscopicTopology.getLinkNames();
    Sheet sheet = wb.getSheet(getPropertyValue(tpaProps.REQUESTS_WS_NAME));

    // 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());
    }
    // train path slots hourly
    int k = 1;
    for (int i = 0; i < 24; i++) {
        for (int j = 0; j < requestsPerHour; j++) {
            Row row = sheet.createRow(sheet.getLastRowNum() + 1);
            int hour = i;
            int minutes = (offset + j * (60 / requestsPerHour)) % 60;
            double deptime = DateUtil
                    .convertTime(String.format("%02d", hour) + ":" + String.format("%02d", minutes));
            double arrtime = (deptime * 24 * 60 + durationMinutes) / (24 * 60);

            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);

            row.getCell(fromColIndex, Row.CREATE_NULL_AS_BLANK).setCellValue(from);
            row.getCell(toColIndex, Row.CREATE_NULL_AS_BLANK).setCellValue(to);

            row.getCell(idColIndex, Row.CREATE_NULL_AS_BLANK)
                    .setCellValue(from + "_" + to + "_" + String.format("%03d", k));
            k++;

            // peridiocity
            for (Integer integer : p.getWeekDays()) {
                int weekdayColIndex = colLayoutMapping.get(requestsLayout.getWeekDayTrainPathLayout(integer));
                row.getCell(weekdayColIndex, Row.CREATE_NULL_AS_BLANK).setCellValue(requestedMarker);
            }
        }
    }
}

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 w  w  w.j  a  v a  2s.c om
    }
    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:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

private void allocate(SimpleTrainPathApplication simpleTrainPathApplication, SolutionCandidate allocation) {
    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  a v  a 2s .c o  m*/
    }

    Map<ColumnIdentifier, Integer> colLayoutMapping = getColLayoutMapping(colLayoutString, cols);
    int rowsFrom = Integer.parseInt(getPropertyValue(tpaProps.TRAINPATHS_WS_HEADER_ROWS));

    for (TrainPathSlot trainPathSlot : allocation.getPath()) {
        Sheet sheet = wb.getSheet(trainPathSlot.getPeriodicalTrainPathSlot().getTrainPathSectionName());

        for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            String slotId = getCellValueString(row.getCell(colLayoutMapping.get(trainPathLayout.ID)));
            if (trainPathSlot.getPeriodicalTrainPathSlot().getName().equals(slotId)) {
                trainPathLayout day = trainPathLayout
                        .getWeekDayTrainPathLayout(trainPathSlot.getStartTime().getDayOfWeek());
                String slotName = trainPathSlot.getName();
                int colNum = colLayoutMapping.get(day);
                Cell cell = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK);
                if (StringUtils.isNotBlank(getCellValueString(cell))) {
                    throw new IllegalStateException("Cell must be empty; trying to allocate " + slotName
                            + " on " + day + " to request " + simpleTrainPathApplication.getName()
                            + "; cell value is " + getCellValueString(cell));
                }
                cell.setCellValue(simpleTrainPathApplication.getName()); // TODO show periodicity here when implementing "non-flat" allocation
                LOGGER.debug("Allocating " + slotName + " on " + day + " by request "
                        + simpleTrainPathApplication.getName() + " of weight " + allocation.getWeight());
            }
        }
    }

}

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

License:Apache License

/**
 * Mark the request as allocated on all days of the request.
 *
 * @param request/*from  w w  w.j  av a  2s  . c  om*/
 */
private void markRequestAllocated(TrainPathApplication request) {
    String colLayoutString = getPropertyValue(tpaProps.REQUESTS_COL_LAYOUT);
    List<ColumnIdentifier> cols = new ArrayList<ColumnIdentifier>(requestsLayout.values().length);
    for (requestsLayout l : requestsLayout.values()) {
        cols.add(l);
    }

    Map<ColumnIdentifier, Integer> colLayoutMapping = getColLayoutMapping(colLayoutString, cols);
    int rowsFrom = Integer.parseInt(getPropertyValue(tpaProps.REQUESTS_WS_HEADER_ROWS));
    Sheet sheet = wb.getSheet(getPropertyValue(tpaProps.REQUESTS_WS_NAME));

    for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        if (row != null && row.getCell(colLayoutMapping.get(requestsLayout.ID)) != null) {
            String allocatedRequest = getCellValueString(row.getCell(colLayoutMapping.get(requestsLayout.ID)));
            if (allocatedRequest.equals(request.getName())) {
                for (Integer day : request.getPeriodicity().getWeekDays()) {
                    requestsLayout requestDay = requestsLayout.getWeekDayTrainPathLayout(day);
                    int colNum = colLayoutMapping.get(requestDay);

                    Cell cell = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK);
                    if (!getCellValueString(cell)
                            .equals(getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER))) {
                        throw new IllegalStateException("Application " + request.getName() + " on day " + day
                                + " must have requested flag \""
                                + getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER)
                                + "\" since we're trying to mark it satisfied; found "
                                + getCellValueString(cell));
                    }
                    cell.setCellValue(getPropertyValue(tpaProps.REQUESTS_ALLOCATED_DAY_MARKER));
                    LOGGER.debug("Set allocated flag for  " + request.getName() + " on day " + day);
                }
            }
        }
    }
}

From source file:cherry.goods.excel.ExcelWriter.java

License:Apache License

/**
 * ?<br />//w  w w.ja v  a2 s . c  om
 * ???1(1)
 * 
 * @param offsetCols ????? (???)
 * @param record 1(1)
 */
public void write(int offsetCols, String... record) {
    Row row = currentSheet.getRow(rownum);
    if (row == null) {
        row = currentSheet.createRow(rownum);
    }
    rownum += 1;
    for (int i = 0; i < record.length; i++) {
        int colnum = i + offsetCols;
        Cell cell = row.getCell(colnum);
        if (record[i] == null) {
            if (cell != null) {
                row.removeCell(cell);
            }
        } else {
            if (cell == null) {
                cell = row.createCell(colnum);
            }
            cell.setCellValue(record[i]);
        }
    }
}