Example usage for org.apache.poi.ss.usermodel Row createCell

List of usage examples for org.apache.poi.ss.usermodel Row createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. createCell

Introduction

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

Prototype

Cell createCell(int column);

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:com.alvexcore.repo.documents.generation.ExportDataListToXlsx.java

License:Open Source License

protected Workbook createXlsx(JSONArray rows, String XLS_SHEET_NAME) {

    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet(XLS_SHEET_NAME);

    for (int k = 0; k < rows.size(); k++) {
        Row row = sheet.createRow((short) k);
        JSONArray cells = (JSONArray) rows.get(k);
        for (int c = 0; c < cells.size(); c++) {
            String displayValue;/*  ww  w  . j a va 2  s  .c  om*/
            Object item = cells.get(c);
            if (item == null)
                displayValue = I18NUtil.getMessage("label.empty");
            else if (item instanceof Boolean)
                displayValue = (Boolean) item ? I18NUtil.getMessage("label.yes")
                        : I18NUtil.getMessage("label.no");
            else
                displayValue = item.toString();
            row.createCell(c).setCellValue(createHelper.createRichTextString(displayValue));
        }
    }

    return wb;
}

From source file:com.alvexcore.repo.documents.generation.ExportReportToXlsx.java

License:Open Source License

protected Workbook createXlsx(JSONArray rows, String XLS_SHEET_NAME) {

    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet(XLS_SHEET_NAME);

    for (int k = 0; k < rows.size(); k++) {
        Row row = sheet.createRow((short) k);
        JSONArray cells = (JSONArray) rows.get(k);
        row.createCell(0).setCellValue(createHelper.createRichTextString((String) cells.get(0)));
        for (int c = 1; c < cells.size(); c++)
            row.createCell(c).setCellValue((String) cells.get(c));
    }//from w  ww .j  av a  2s .c om

    return wb;
}

From source file:com.anritsu.mcrepositorymanager.utils.GenerateRSS.java

public String getRSS() {
    FileInputStream file = null;//from w  w  w  . ja  v  a  2  s  .c o m
    String rssFileName = rssTemplateFileName.replaceAll("template", mcVersion);
    try {
        file = new FileInputStream(
                new File(Configuration.getInstance().getRssTemplatePath() + rssTemplateFileName));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        workbook.setSheetName(workbook.getSheetIndex("MC X.X.X"), "MC " + mcVersion);
        XSSFSheet sheet = workbook.getSheet("MC " + mcVersion);
        CreationHelper createHelper = workbook.getCreationHelper();

        Cell cell = null;

        // Update the sheet title
        cell = sheet.getRow(0).getCell(0);
        cell.setCellValue(cell.getStringCellValue().replaceAll("template", mcVersion));

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        XSSFCellStyle hlinkstyle = workbook.createCellStyle();
        XSSFFont hlinkfont = workbook.createFont();
        hlinkfont.setUnderline(XSSFFont.U_SINGLE);
        hlinkfont.setColor(HSSFColor.BLUE.index);
        hlinkstyle.setFont(hlinkfont);
        hlinkstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        XSSFCellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MMMM-yyyy"));
        dateCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        // Populate the table
        int rowCount = 1;
        for (RecommendedMcPackage rmcp : sortedMcPackages) {
            if (rmcp.getRecommendedVersion() != null && rmcp.isShowInTable()) {
                Row row = sheet.createRow(rowCount + 1);
                rowCount++;

                cell = row.createCell(0);
                cell.setCellValue(rmcp.getTier().replaceAll("Anritsu/MasterClaw/", ""));
                cell.setCellStyle(cellStyle);

                cell = row.createCell(1);
                cell.setCellValue(rmcp.getGroup());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(2);
                cell.setCellValue(rmcp.getPackageName());

                UrlValidator defaultValidator = new UrlValidator(UrlValidator.ALLOW_LOCAL_URLS);

                if (rmcp.getRecommendedVersion().getReleaseNote() != null
                        && defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())) {
                    XSSFHyperlink releaseNotelink = (XSSFHyperlink) createHelper
                            .createHyperlink(Hyperlink.LINK_URL);
                    releaseNotelink.setAddress(rmcp.getRecommendedVersion().getReleaseNote());
                    //System.out.println("Inside(if) RN: " + rmcp.getRecommendedVersion().getReleaseNote() + " Valid: " + defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote()));

                    cell.setHyperlink(releaseNotelink);
                }
                cell.setCellStyle(hlinkstyle);

                cell = row.createCell(3);
                cell.setCellValue(rmcp.getRecommendedVersion().getPackageVersion());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(4);
                cell.setCellValue(rmcp.getAvailability());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(5);
                String customers = Arrays.asList(rmcp.getRecommendedVersion().getCustomerList().toArray())
                        .toString();
                if (customers.equalsIgnoreCase("[All]")) {
                    customers = "";
                }
                cell.setCellValue(customers);
                cell.setCellStyle(cellStyle);

                cell = row.createCell(6);
                cell.setCellValue(rmcp.getRecommendedVersion().getRisk());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(7);
                cell.setCellValue(rmcp.getPackageName());
                XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
                link.setAddress(rmcp.getRecommendedVersion().getDownloadLinks().iterator().next());
                cell.setHyperlink((XSSFHyperlink) link);
                cell.setCellStyle(hlinkstyle);

                cell = row.createCell(8);
                cell.setCellValue((rmcp.getRecommendedVersion() != null
                        && rmcp.getRecommendedVersion().isLessRecommended()) ? "#" : "");
                cell.setCellStyle(cellStyle);

                cell = row.createCell(9);
                cell.setCellValue(rmcp.getRecommendedVersion().getNotes());
                cell.setCellStyle(cellStyle);

                StringBuilder newFeatures = new StringBuilder();
                for (MCPackageActivities mcpa : rmcp.getRecommendedVersion().getActivities()) {
                    if (!mcpa.getActivityType().equalsIgnoreCase("epr")) {
                        newFeatures.append(mcpa.getActivityType() + " " + mcpa.getActivityId() + "; ");
                    }
                }
                cell = row.createCell(10);
                cell.setCellValue(newFeatures.toString());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(11);
                cell.setCellValue(rmcp.getRecommendedVersion().getReleaseDate());
                cell.setCellStyle(dateCellStyle);
            }
            sheet.autoSizeColumn(0);
            sheet.autoSizeColumn(1);
            sheet.autoSizeColumn(2);
            sheet.autoSizeColumn(3);
            sheet.autoSizeColumn(4);
            sheet.autoSizeColumn(6);
            sheet.autoSizeColumn(7);
            sheet.autoSizeColumn(8);
            sheet.autoSizeColumn(11);

        }

        FileOutputStream outFile = new FileOutputStream(
                new File(Configuration.getInstance().getRssTemplatePath() + rssFileName));
        workbook.write(outFile);
        outFile.close();
        return Configuration.getInstance().getRssTemplatePath() + rssFileName;

    } catch (FileNotFoundException ex) {
        Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            file.close();
        } catch (IOException ex) {
            Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    return "";
}

From source file:com.asakusafw.testdata.generator.excel.SheetEditor.java

License:Apache License

/**
 * Creates a data sheet with specified name.
 * @param name sheet name/*  w  w  w .ja  va 2  s .  c  o m*/
 * @param model the target model
 * @throws IllegalArgumentException if some parameters were {@code null}
 */
public void addData(String name, ModelDeclaration model) {
    if (name == null) {
        throw new IllegalArgumentException("name must not be null"); //$NON-NLS-1$
    }
    if (model == null) {
        throw new IllegalArgumentException("model must not be null"); //$NON-NLS-1$
    }
    Sheet sheet = info.workbook.createSheet(name);
    Row titleRow = sheet.createRow(0);
    Row valueRow = sheet.createRow(1);
    int index = 0;
    for (PropertyDeclaration property : model.getDeclaredProperties()) {
        if (index >= info.version.getMaxColumns()) {
            LOG.warn(MessageFormat.format(Messages.getString("SheetEditor.warnExceedColumnCount"), //$NON-NLS-1$
                    info.version.getMaxColumns(), model.getName()));
            break;
        }
        Cell title = titleRow.createCell(index);
        title.setCellStyle(info.titleStyle);
        title.setCellValue(property.getName().identifier);

        Cell value = valueRow.createCell(index);
        value.setCellStyle(info.dataStyle);
        if (property.getType() instanceof BasicType) {
            BasicType type = (BasicType) property.getType();
            switch (type.getKind()) {
            case DATE:
                value.setCellStyle(info.dateDataStyle);
                break;
            case DATETIME:
                value.setCellStyle(info.datetimeDataStyle);
                break;
            default:
                break;
            }
        }
        index++;
    }
    adjustDataWidth(sheet);
}

From source file:com.axelor.studio.service.data.validator.ValidatorService.java

License:Open Source License

public void addLog(String log, String sheetName, int rowNum) throws IOException {

    if (logFile == null) {
        logFile = File.createTempFile("ImportLog", ".xlsx");
        logBook = new XSSFWorkbook();
    }/*from   w  ww .j  ava 2s  .  c  o  m*/

    XSSFSheet sheet = logBook.getSheet(sheetName);

    if (sheet == null) {
        sheet = logBook.createSheet(sheetName);
        XSSFRow titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("Row Number");
        titleRow.createCell(1).setCellValue("Issues");
    }

    Iterator<Row> rowIterator = sheet.rowIterator();
    Row logRow = null;
    while (rowIterator.hasNext()) {
        Row sheetRow = rowIterator.next();
        Cell cell = sheetRow.getCell(0);
        if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            continue;
        }
        double value = cell.getNumericCellValue();
        if (value == rowNum + 1) {
            logRow = sheetRow;
            break;
        }
    }

    if (logRow == null) {
        logRow = sheet.createRow(sheet.getPhysicalNumberOfRows());
    }

    Cell cell = logRow.getCell(0);
    if (cell == null) {
        cell = logRow.createCell(0);
        cell.setCellValue(rowNum + 1);
    }
    cell = logRow.getCell(1);
    if (cell == null) {
        cell = logRow.createCell(1);
    }
    String oldValue = cell.getStringCellValue();
    if (oldValue == null) {
        cell.setCellValue(log);
    } else {
        cell.setCellValue(oldValue + "\n" + log);
    }

}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java

License:Apache License

/**
 * Creates the index sheet based on the given sheet names.
 * //from   ww w.j a v  a  2 s  .co  m
 * @param sheetNames
 */
protected void createIndexSheet(final Collection<T> components) {

    final Sheet indexSheet = workbook.createSheet("INDEX");

    final List<T> filteredComponents = Lists.newArrayList(Iterables.filter(components, new Predicate<T>() {
        @Override
        public boolean apply(T input) {
            return isToExport(getComponentId(input));
        }
    }));

    final List<String> sheetNames = extractSheetNamesFromTerminologyComponents(filteredComponents);

    final Row firstRow = indexSheet.createRow(0);
    createCell(firstRow, getIndexSheetHeaderName(), BOLD_STYLE, 0);

    for (int i = 0; i < sheetNames.size(); i++) {

        final String sheetName = getFinalSheetName(i + 1, sheetNames.get(i));
        final Hyperlink hyperlink = workbook.getCreationHelper().createHyperlink(XSSFHyperlink.LINK_DOCUMENT);

        hyperlink.setLabel(sheetName);
        hyperlink.setAddress(String.format("'%s'!A1", sheetName));

        final Row row = indexSheet.createRow(i + 1);
        final Cell cell = row.createCell(0);

        cell.setCellValue(sheetName);
        cell.setCellStyle(hyperlinkStyle);
        cell.setHyperlink(hyperlink);

    }

    indexSheet.autoSizeColumn(0);

}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java

License:Apache License

/**
 * Creates a property row in the excel with the given property name and value.
 * // www  .  j  av  a2 s.co m
 * @param sheet
 *            the sheet where the property is created.
 * @param rowNumber
 *            the number of the row where the property is created.
 * @param propertyName
 *            the name of the property.
 * @param propertyValue
 *            the value of the property.
 */
protected void createProperty(final Sheet sheet, final int rowNumber, final String propertyName,
        final String propertyValue) {
    final Row row = sheet.createRow(rowNumber);
    Cell cell = row.createCell(0);
    cell.setCellValue(propertyName);
    cell.setCellStyle(BOLD_STYLE);

    cell = row.createCell(1);
    cell.setCellValue(propertyValue);
    cell.setCellStyle(defaultStyle);
}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java

License:Apache License

/**
 * Creates a metadata row in the excel with the given group name and keyword.
 * //  w  w  w.  j a v  a 2  s  . c o  m
 * @param sheet
 *            the sheet where the metadata is created.
 * @param rowNum
 *            the number of the row where the metadata is created.
 * @param groupName
 *            the name of the group.
 * @param keyword
 *            the name of the keyword.
 */
protected void createMetadata(Sheet sheet, int rowNum, String groupName, String keyword) {
    final Row row = sheet.createRow(rowNum);

    Cell cell = row.createCell(0);
    cell.setCellValue(groupName);
    cell.setCellStyle(defaultStyle);

    cell = row.createCell(1);
    cell.setCellValue(keyword);
    cell.setCellStyle(defaultStyle);
}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java

License:Apache License

/**
 * Creates a cell with the given string value.
 * //from w w  w .  ja  va 2 s .c  om
 * @param row
 *            the row where the cell is created.
 * @param cellValue
 *            the string value of the cell.
 * @param cellStyle
 *            the style of the cell.
 * @param cellIndex
 *            the index of the cell in the row.
 */
protected void createCell(final Row row, final String cellValue, final CellStyle cellStyle,
        final int cellIndex) {
    final Cell cell = row.createCell(cellIndex);
    cell.setCellValue(cellValue);
    cell.setCellStyle(cellStyle);
}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelExporter.java

License:Apache License

/**
 * Creates a cell with the given int value.
 * /*from w  w w  . java2s.c  o m*/
 * @param row
 *            the row where the cell is created.
 * @param cellValue
 *            the int value of the cell.
 * @param cellStyle
 *            the style of the cell.
 * @param cellIndex
 *            the index of the cell in the row.
 */
protected void createCell(final Row row, final int cellValue, final CellStyle cellStyle, final int cellIndex) {
    final Cell cell = row.createCell(cellIndex);
    cell.setCellValue(cellValue);
    cell.setCellStyle(cellStyle);
}