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.github.crab2died.ExcelUtils.java

License:Open Source License

private void generateSheet(Workbook workbook, List<?> data, Class clazz, boolean isWriteHeader,
        String sheetName) throws Excel4JException {

    Sheet sheet;/* w  w  w.java 2s .co m*/
    if (null != sheetName && !"".equals(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    Row row = sheet.createRow(0);
    List<ExcelHeader> headers = Utils.getHeaderList(clazz);
    if (isWriteHeader) {
        // 
        for (int i = 0; i < headers.size(); i++) {
            row.createCell(i).setCellValue(headers.get(i).getTitle());
        }
    }
    // ?
    Object _data;
    for (int i = 0; i < data.size(); i++) {
        row = sheet.createRow(i + 1);
        _data = data.get(i);
        for (int j = 0; j < headers.size(); j++) {
            row.createCell(j).setCellValue(
                    Utils.getProperty(_data, headers.get(j).getFiled(), headers.get(j).getWriteConverter()));
        }
    }

}

From source file:com.github.cutstock.excel.model.SheetBuilder.java

License:Apache License

public SheetBuilder createColumns(ICellInfo columns) {
    IExcelRectangle rect = columns.getRect();
    int rowLine = rect.getStartRow();
    Row row = createRow(rowLine);
    // String colName = columns.getText();
    // String[] colNames = colName.split(",");
    Object[] colNames = columns.getColumns();
    for (int i = rect.getStartCol(), j = rect.getEndCol(), index = 0; i <= j; i++, index++) {
        Cell colCell = row.createCell(i);
        // cut num should cast to number 5,13
        if (colNames[index] instanceof BigDecimal || colNames[index] instanceof Integer) {
            colCell.setCellValue(Double.parseDouble(colNames[index].toString()));
            colCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        } else {//w ww .ja  v  a2 s .com
            colCell.setCellValue(colNames[index].toString());
        }
        CellStyle style = styles.get(columns.getCellType().typeValue());
        colCell.setCellStyle(style);
    }

    Row preRow = createRow(rowLine - 1);
    if (preRow != null) {
        Cell nameCel = preRow.getCell(rect.getStartCol());
        if (nameCel != null) {
            if (nameCel.getStringCellValue().equals(row.getCell(rect.getStartCol()).getStringCellValue())) {
                mergeRegion(ExcelModelFactory.createCellRect(rect.getStartCol(), rect.getStartCol(),
                        rowLine - 1, rowLine));
            }
        }
    }
    return this;
}

From source file:com.github.cutstock.excel.model.SheetBuilder.java

License:Apache License

public void createTitle(ICellInfo title) {
    IExcelRectangle titleRect = title.getRect();
    int row = titleRect.getStartRow();
    Row titleRow = createRow(row);
    titleRow.setHeightInPoints(titleRect.getHeight());
    int startCol = titleRect.getStartCol();
    Cell titleCell = titleRow.getCell(startCol);
    if (titleCell == null) {
        titleCell = titleRow.createCell(startCol);
    }/* w w w  .  j  av  a  2s  .com*/
    titleCell.setCellValue(title.getColumns()[0].toString());
    mergeRegion(titleRect);
    CellStyle style = styles.get(title.getCellType().typeValue());
    titleCell.setCellStyle(style);
}

From source file:com.github.gaborfeher.grantmaster.framework.base.ExcelExporter.java

License:Open Source License

private int addExcelRow(HSSFWorkbook workbook, HSSFSheet sheet, int rowId, List<Object> data) {
    Row excelRow = sheet.createRow(rowId);
    int cellId = 0;
    for (Object cellValue : data) {
        Cell excelCell = excelRow.createCell(cellId);
        cellId += 1;//from w ww . j  a v a  2 s  .  c  o m
        setExcelCell(workbook, cellValue, excelCell);
    }
    return rowId + 1;
}

From source file:com.github.gujou.deerbelling.sonarqube.service.XlsTasksGenerator.java

License:Open Source License

public static File generateFile(Project sonarProject, FileSystem sonarFileSystem, String sonarUrl,
        String sonarLogin, String sonarPassword) {

    short formatIndex;
    HSSFDataFormat dataFormat = null;//from w  w  w .  java  2s  .co m
    FileOutputStream out = null;
    HSSFWorkbook workbook = null;

    String filePath = sonarFileSystem.workDir().getAbsolutePath() + File.separator + "tasks_report_"
            + sonarProject.getEffectiveKey().replace(':', '-') + "."
            + ReportsKeys.TASKS_REPORT_TYPE_XLS_EXTENSION;

    File resultFile = new File(filePath);

    try {
        out = new FileOutputStream(resultFile);

        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Tasks list");

        // Date format.
        dataFormat = workbook.createDataFormat();
        formatIndex = dataFormat.getFormat("yyyy-MM-ddTHH:mm:ss");
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(formatIndex);

        Issues rootIssue = IssueGateway.getOpenIssues(sonarProject.getEffectiveKey(), sonarUrl, sonarLogin,
                sonarPassword);

        if (rootIssue == null) {
            return null;
        }

        DataValidationHelper validationHelper = new HSSFDataValidationHelper(sheet);
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(
                new String[] { "OPENED", "CONFIRMED", "REOPENED", "RESOLVED", "CLOSE" });
        CellRangeAddressList addressList = new CellRangeAddressList(1, rootIssue.getIssues().size() + 1,
                STATUS_COLUMN_INDEX, STATUS_COLUMN_INDEX);
        DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
        dataValidation.setSuppressDropDownArrow(false);
        sheet.addValidationData(dataValidation);

        int rownum = 0;

        Row row = sheet.createRow(rownum++);
        row.createCell(STATUS_COLUMN_INDEX).setCellValue("Status");
        row.createCell(SEVERITY_COLUMN_INDEX).setCellValue("Severity");
        row.createCell(COMPONENT_COLUMN_INDEX).setCellValue("Component");
        row.createCell(LINE_COLUMN_INDEX).setCellValue("Line");
        row.createCell(MESSAGE_COLUMN_INDEX).setCellValue("Message");
        row.createCell(AUTHOR_COLUMN_INDEX).setCellValue("Author");
        row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue("Assigned");
        row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue("CreationDate");
        row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue("UpdateDate");
        row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue("Path");

        for (Issue issue : rootIssue.getIssues()) {
            if (issue != null) {
                row = sheet.createRow(rownum++);
                int componentIndex = 0;
                if (issue.getComponent() != null) {
                    componentIndex = issue.getComponent().lastIndexOf('/');
                }
                String component;
                String path;
                if (componentIndex > 0) {
                    component = issue.getComponent().substring(componentIndex + 1);
                    path = issue.getComponent().substring(0, componentIndex);
                } else {
                    component = issue.getComponent();
                    path = "";
                }

                // Set values.
                row.createCell(STATUS_COLUMN_INDEX).setCellValue(issue.getStatus());
                row.createCell(SEVERITY_COLUMN_INDEX).setCellValue(issue.getSeverity());
                row.createCell(COMPONENT_COLUMN_INDEX).setCellValue(component);
                row.createCell(LINE_COLUMN_INDEX).setCellValue(issue.getLine());
                row.createCell(MESSAGE_COLUMN_INDEX).setCellValue(issue.getMessage());
                row.createCell(AUTHOR_COLUMN_INDEX).setCellValue(issue.getAuthor());
                row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue(issue.getAssignee());
                row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue(issue.getCreationDate());
                row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue(issue.getUpdateDate());
                row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue(path);

                // Set date style to date column.
                row.getCell(CREATION_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
                row.getCell(UPDATE_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
            }
        }

        // Auto-size sheet columns.
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_COLUMN_INDEX);
        sheet.autoSizeColumn(LINE_COLUMN_INDEX);
        sheet.autoSizeColumn(MESSAGE_COLUMN_INDEX);
        sheet.autoSizeColumn(AUTHOR_COLUMN_INDEX);
        sheet.autoSizeColumn(ASSIGNED_COLUMN_INDEX);
        sheet.autoSizeColumn(CREATION_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(UPDATE_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_PATH_COLUMN_INDEX);

        workbook.write(out);

    } catch (FileNotFoundException e) {

        // TODO manage error.
        e.printStackTrace();
    } catch (IOException e) {

        // TODO manage error.
        e.printStackTrace();
    } finally {
        IOUtils.closeQuietly(workbook);
        IOUtils.closeQuietly(out);
    }

    return resultFile;
}

From source file:com.github.igor_kudryashov.utils.excel.ExcelWriter.java

License:Apache License

/**
 * Creates new row in the worksheet/*from www.j  a v  a 2 s .  c  om*/
 *
 * @param sheet
 *            Sheet
 * @param values
 *            the value of the new cell line
 * @param header
 *            <code>true</code> if this row is the header, otherwise
 *            <code>false</code>
 * @param withStyle
 *            <code>true</code> if in this row will be applied styles for
 *            the cells, otherwise <code>false</code>
 * @return created row
 */
public Row createRow(Sheet sheet, Object[] values, boolean header, boolean withStyle) {
    Row row;
    String sheetName = sheet.getSheetName();
    int rownum = 0;
    if (rows.containsKey(sheetName)) {
        rownum = rows.get(sheetName);
    }
    // create new row
    row = sheet.createRow(rownum);
    // create a cells of row
    for (int x = 0; x < values.length; x++) {
        Object o = values[x];
        Cell cell = row.createCell(x);
        if (o != null) {
            if (o.getClass().getName().contains("String")) {
                String value = (String) values[x];
                cell.setCellValue(value);
            } else if (o.getClass().getName().contains("Double")) {
                cell.setCellValue((Double) values[x]);
            } else if (o.getClass().getName().contains("Integer")) {
                cell.setCellValue((Integer) values[x]);
            } else if (o.getClass().getName().contains("Date")) {
                cell.setCellValue((Date) values[x]);
            }
            if (withStyle) {
                cell.setCellStyle(getCellStyle(rownum, values[x], header));
            }
        }
        // save max column width
        if (!header) {
            saveColumnWidth(sheet, x, o);
        }
    }
    // save the last number of row for this worksheet
    rows.put(sheetName, ++rownum);
    return row;
}

From source file:com.github.jaydsolanki.excelio.ExcelIO.java

private boolean insertCell(Object obj, Sheet sheet, int rowNo, int cellNo) {

    if (sheet == null) {
        return false;
    }//  w w w.  jav a 2 s  . com

    Row row = sheet.getRow(rowNo);
    if (row == null) {
        row = sheet.createRow(rowNo);
    }
    Cell cell = row.getCell(cellNo);
    if (cell == null) {
        cell = row.createCell(cellNo);
    }
    cell.setCellValue(obj.toString());
    return true;
}

From source file:com.github.lucapino.sheetmaker.parsers.mediainfo.MediaInfoExtractor.java

private void print(String rootPath) throws Exception {
    FileFilter filter = new FileFilter() {

        @Override/*  w  w  w  .  ja  va  2  s.c  om*/
        public boolean accept(File pathname) {
            String lowerCaseName = pathname.getName().toLowerCase();
            return (lowerCaseName.endsWith("ifo") && lowerCaseName.startsWith("vts"))
                    || lowerCaseName.endsWith("mkv") || lowerCaseName.endsWith("iso")
                    || lowerCaseName.endsWith("avi");
        }
    };
    // parse all the tree under rootPath
    File rootFolder = new File(rootPath);
    File[] files = rootFolder.listFiles();
    Arrays.sort(files);
    Map<File, List<File>> mediaMap = new TreeMap<>();
    for (File file : files) {
        System.out.println(file.getName());
        // name of the folder -> name of media
        List<File> fileList;
        if (file.isDirectory()) {
            fileList = recurseSubFolder(filter, file);
            if (!fileList.isEmpty()) {
                System.out.println("adding " + fileList);
                mediaMap.put(file, fileList);
            }
        } else {
            if (filter.accept(file)) {
                fileList = new ArrayList<>();
                fileList.add(file);
                System.out.println("adding " + fileList);
                mediaMap.put(file, fileList);
            }
        }
    }
    Set<File> fileNamesSet = mediaMap.keySet();
    File outputFile = new File("/home/tagliani/tmp/HD-report.xls");
    Workbook wb = new HSSFWorkbook(new FileInputStream(outputFile));
    Sheet sheet = wb.createSheet("Toshiba");

    MediaInfo MI = new MediaInfo();
    int j = 0;
    for (File mediaFile : fileNamesSet) {
        List<File> filesList = mediaMap.get(mediaFile);
        for (File fileInList : filesList) {
            List<String> audioTracks = new ArrayList<>();
            List<String> videoTracks = new ArrayList<>();
            List<String> subtitlesTracks = new ArrayList<>();
            MI.Open(fileInList.getAbsolutePath());

            String durationInt = MI.Get(MediaInfo.StreamKind.General, 0, "Duration", MediaInfo.InfoKind.Text,
                    MediaInfo.InfoKind.Name);
            System.out.println(fileInList.getName() + " -> " + durationInt);
            if (StringUtils.isNotEmpty(durationInt) && Integer.valueOf(durationInt) >= 60 * 60 * 1000) {

                Row row = sheet.createRow(j);
                String duration = MI.Get(MediaInfo.StreamKind.General, 0, "Duration/String",
                        MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                // Create a cell and put a value in it.
                row.createCell(0).setCellValue(WordUtils.capitalizeFully(mediaFile.getName()));
                if (fileInList.getName().toLowerCase().endsWith("iso")
                        || fileInList.getName().toLowerCase().endsWith("ifo")) {
                    row.createCell(1).setCellValue("DVD");
                } else {
                    row.createCell(1)
                            .setCellValue(FilenameUtils.getExtension(fileInList.getName()).toUpperCase());
                }
                row.createCell(2).setCellValue(FileUtils.byteCountToDisplaySize(FileUtils.sizeOf(mediaFile)));
                // row.createCell(3).setCellValue(fileInList.getAbsolutePath());
                row.createCell(3).setCellValue(duration);
                // MPEG-2 720x576 @ 25fps 16:9
                String format = MI.Get(MediaInfo.StreamKind.Video, 0, "Format", MediaInfo.InfoKind.Text,
                        MediaInfo.InfoKind.Name);
                row.createCell(4).setCellValue(format);
                String width = MI.Get(MediaInfo.StreamKind.Video, 0, "Width", MediaInfo.InfoKind.Text,
                        MediaInfo.InfoKind.Name);
                String height = MI.Get(MediaInfo.StreamKind.Video, 0, "Height", MediaInfo.InfoKind.Text,
                        MediaInfo.InfoKind.Name);
                row.createCell(5).setCellValue(width + "x" + height);
                String fps = MI.Get(MediaInfo.StreamKind.Video, 0, "FrameRate", MediaInfo.InfoKind.Text,
                        MediaInfo.InfoKind.Name);
                row.createCell(6).setCellValue(fps);
                String aspectRatio = MI.Get(MediaInfo.StreamKind.Video, 0, "DisplayAspectRatio/String",
                        MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                row.createCell(7).setCellValue(aspectRatio);

                int audioStreamNumber = MI.Count_Get(MediaInfo.StreamKind.Audio);
                for (int i = 0; i < audioStreamNumber; i++) {
                    String audioTitle = MI.Get(MediaInfo.StreamKind.Audio, i, "Title", MediaInfo.InfoKind.Text,
                            MediaInfo.InfoKind.Name);
                    String language = MI.Get(MediaInfo.StreamKind.Audio, i, "Language/String3",
                            MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                    String codec = MI.Get(MediaInfo.StreamKind.Audio, i, "Codec", MediaInfo.InfoKind.Text,
                            MediaInfo.InfoKind.Name);
                    String channels = MI.Get(MediaInfo.StreamKind.Audio, i, "Channel(s)",
                            MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                    String sampleRate = MI.Get(MediaInfo.StreamKind.Audio, i, "SamplingRate/String",
                            MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                    // AC3 ITA 5.1 48.0KHz
                    StringBuilder sb = new StringBuilder();
                    if (StringUtils.isEmpty(audioTitle)) {
                        sb.append(codec).append(" ").append(language.toUpperCase()).append(" ")
                                .append(channels);
                    } else {
                        sb.append(audioTitle);
                    }
                    sb.append(" ").append(sampleRate);
                    audioTracks.add(sb.toString());
                }

                int textStreamNumber = MI.Count_Get(MediaInfo.StreamKind.Text);
                for (int i = 0; i < textStreamNumber; i++) {
                    String textLanguage = MI.Get(MediaInfo.StreamKind.Text, i, "Language/String",
                            MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                    subtitlesTracks.add(textLanguage);
                }
                MI.Close();
                row.createCell(8).setCellValue(audioTracks.toString());
                row.createCell(9).setCellValue(subtitlesTracks.toString());
                j++;
            }

        }

        //            System.out.println(mediaName);
    }
    try (FileOutputStream fileOut = new FileOutputStream(outputFile)) {
        wb.write(fileOut);
    }

}

From source file:com.github.luischavez.lat.excel.Excel.java

License:Open Source License

protected void createCell(int index, Row row, CellStyle style, String value) {
    Cell cell = row.createCell(index);
    if (null != style) {
        cell.setCellStyle(style);/*from  w  w  w. j a va 2  s .  c om*/
    }
    cell.setCellValue(value);
}

From source file:com.github.luischavez.lat.excel.Excel.java

License:Open Source License

protected void createNumber(int index, Row row, CellStyle style, double value) {
    Cell cell = row.createCell(index);
    if (null != style) {
        cell.setCellStyle(style);//from   w w  w .jav  a2 s .  c  om
    }
    cell.setCellValue(value);
}