Example usage for org.apache.poi.hssf.usermodel HSSFRow getCell

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFRow getCell.

Prototype

@Override
public HSSFCell getCell(int cellnum) 

Source Link

Document

Get the hssfcell representing a given column (logical cell) 0-based.

Usage

From source file:bs.proveedores.web.informe.CuentaCorrienteProveedorBean.java

public void postProcessXLS(Object document) {

    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);//  w  ww  .j a  va  2s  .com
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.ORANGE.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);
    }
}

---

From source file:ca.uwo.csd.cs2212.team02.DailyTips.java

/**
 * Generate a tip from .csv file//from  w  w  w.j  av a 2  s .c  om
 */
private void generateTip() {
    HSSFRow row = sheet.getRow(this.tipIndex);
    JTextArea tip = new JTextArea(row.getCell(0).toString());
    tip = textAreaProperties(tip);
    tip.setSize(350, 200);
    c.ipady = 40;
    c.gridx = 0;
    c.gridy = 1;
    c.gridwidth = 4;
    c.fill = GridBagConstraints.HORIZONTAL;
    this.add(tip, c);
    JTextArea URL = new JTextArea(row.getCell(1).toString());
    URL = textAreaProperties(URL);
    URL.setSize(250, 200);
    c.ipady = 0;
    c.gridx = 1;
    c.gridy = 2;
    c.gridwidth = 3;
    c.anchor = GridBagConstraints.PAGE_END;
    this.add(URL, c);
}

---

From source file:cdc.impl.datasource.office.ExcelDataSource.java

License:LGPL

private static DataColumnDefinition[] readDataModel(String sourceName, Map params)
        throws IOException, RJException {
    BufferedInputStream is = null;
    try {//  www.  j  a  va2  s  .  c om
        is = new BufferedInputStream(new FileInputStream((String) params.get(PARAM_FILE)));
        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));
        String[] sheets;
        if (params.get(PARAM_SHEET) != null) {
            sheets = new String[] { (String) params.get(PARAM_SHEET) };
        } else {
            sheets = new String[wb.getNumberOfSheets()];
            for (int i = 0; i < sheets.length; i++) {
                sheets[i] = wb.getSheetName(i);
            }
        }
        if (sheets.length == 0) {
            throw new RJException("Excel file " + params.get(PARAM_FILE) + " does not provide any sheets.");
        }
        List cols = new ArrayList();
        HSSFSheet sheet = wb.getSheet(sheets[0]);
        if (sheet == null) {
            //System.out.println("Thorwing: " + "Sheet " + sheets[0] + " is not provided by file " + params.get(PARAM_FILE));
            throw new RJException(
                    "Sheet '" + sheets[0] + "' is not provided by file " + params.get(PARAM_FILE));
        }
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
        //first row should provide data model
        HSSFRow row = sheet.getRow(0);
        evaluator.setCurrentRow(row);
        for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = row.getCell(i);
            cols.add(new ExcelDataColumnDefinition(decodeValue(cell, evaluator),
                    DataColumnDefinition.TYPE_STRING, sourceName, i));
        }
        for (int i = 1; i < sheets.length; i++) {
            sheet = wb.getSheet(sheets[i]);
            if (sheet == null) {
                throw new RJException("Sheet '" + params.get(PARAM_SHEET) + "' is not provided by file "
                        + params.get(PARAM_FILE));
            }
            evaluator = new HSSFFormulaEvaluator(sheet, wb);
            //first row should provide data model
            row = sheet.getRow(0);
            evaluator.setCurrentRow(row);
            List localCols = new ArrayList();
            for (i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                HSSFCell cell = row.getCell(i);
                DataColumnDefinition col = new ExcelDataColumnDefinition(decodeValue(cell, evaluator),
                        DataColumnDefinition.TYPE_STRING, sourceName, i);
                localCols.add(col);
            }
            List toRemove = new ArrayList();
            for (Iterator iterator = cols.iterator(); iterator.hasNext();) {
                DataColumnDefinition object = (DataColumnDefinition) iterator.next();
                if (!localCols.contains(object)) {
                    toRemove.add(object);
                }
            }
            cols.removeAll(toRemove);
        }

        return (DataColumnDefinition[]) cols.toArray(new DataColumnDefinition[] {});
    } finally {
        if (is != null) {
            is.close();
        }
    }
}

---

From source file:cdc.impl.datasource.office.ExcelDataSource.java

License:LGPL

protected DataRow nextRow() throws IOException, RJException {
    if (!filesOpen) {
        openFile();//w w w . j  ava 2 s  .  c o m
    }

    if (iterator.hasNext()) {
        HSSFRow row = (HSSFRow) iterator.next();
        iterator.getEvaluator().setCurrentRow(row);
        ModelGenerator generator = getDataModel();
        DataColumnDefinition[] inputColumns = generator.getInputFormat();
        DataCell[] rowCols = new DataCell[inputColumns.length];
        for (int i = 0; i < rowCols.length; i++) {
            DataColumnDefinition col = generator.getInputFormat()[i];
            rowCols[i] = new DataCell(col.getColumnType(), decodeValue(
                    row.getCell(((ExcelDataColumnDefinition) col).getCellId()), iterator.getEvaluator()));
        }
        return new DataRow(generator.getOutputFormat(), generator.generateOutputRow(rowCols), getSourceName());
    } else {
        return null;
    }
}

---

From source file:ch.elexis.core.importer.div.importers.ExcelWrapper.java

License:Open Source License

/**
 * Return a row of data from the sheet./*from  www .ja v a 2 s. co  m*/
 * 
 * @param rowNr
 *            zero based index of the desired row
 * @return a List of Strings with the row values or null if no such row exists.
 */
public List<String> getRow(final int rowNr) {
    HSSFRow row = sheet.getRow(rowNr);
    if (row == null) {
        return null;
    }
    ArrayList<String> ret = new ArrayList<String>();
    short first = 0;
    short last = 100;
    if (types != null) {
        last = (short) (types.length);
    } else {
        first = row.getFirstCellNum();
        last = row.getLastCellNum();
    }
    for (short i = first; i < last; i++) {
        HSSFCell cell = row.getCell(i);
        if (cell != null) {
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_BLANK:
                ret.add(""); //$NON-NLS-1$
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                ret.add(Boolean.toString(cell.getBooleanCellValue()));
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (types != null) {
                    if (types[i].equals(Integer.class)) {
                        ret.add(Long.toString(Math.round(cell.getNumericCellValue())));
                    } else if (types[i].equals(TimeTool.class)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            TimeTool tt = new TimeTool(date.getTime());
                            ret.add(tt.toString(TimeTool.FULL_MYSQL));
                        } else {
                            ret.add(""); //$NON-NLS-1$
                        }
                    } else if (types[i].equals(Double.class)) {
                        ret.add(Double.toString(cell.getNumericCellValue()));
                        break;
                    } else /* if(types[i].equals(String.class)) */ {
                        double cv = cell.getNumericCellValue();
                        // String r=Double.toString(cv);
                        String r = NumberFormat.getNumberInstance().format(cv);
                        ret.add(r);
                    }
                    break;
                } // else fall thru
            case HSSFCell.CELL_TYPE_FORMULA:
                ret.add(Double.toString(cell.getNumericCellValue()));
                break;
            case HSSFCell.CELL_TYPE_STRING:
                ret.add(cell.toString());
                break;
            default:
                ret.add(Messages.ExcelWrapper_ErrorUnknownCellType);
            }

        } else {
            // empty cell
            ret.add(""); //$NON-NLS-1$
        }
    }
    return ret;
}

---

From source file:ch.javasoft.metabolic.generate.ExcelGenerator.java

License:BSD License

protected static HSSFCell getCell(HSSFSheet sheet, int row, int col, boolean create) {
    HSSFRow xlsRow = sheet.getRow(row);
    if (xlsRow == null) {
        if (!create)
            return null;
        xlsRow = sheet.createRow(row);//from   ww  w. ja va  2  s.c  o  m
    }
    HSSFCell xlsCell = xlsRow.getCell((short) col);
    if (xlsCell == null) {
        if (!create)
            return null;
        xlsCell = xlsRow.createCell((short) col);
    }
    return xlsCell;
}

---

From source file:ch.javasoft.metabolic.parse.ExcelParser.java

License:BSD License

/**
 * Parses the excel file and returns the resulting metabolic network,
 * skipping <tt>headerRows</tt> rows, using the given columns to extract
 * information from, column indices being 0-based. The given pattern is
 * used to recognize external metabolites; a reversible exchange flux 
 * reaction is added to each external metabolite.
 * /*from  w  w w  . j  av  a 2s . c o  m*/
 * @param reactionColumn      0-based column index for reaction formula
 * @param reactionNameColumn   0-based column index for reaction name
 * @param headerRows         number of header rows (being ignored)
 * @param externalPattern      pattern to recognize external metabolites
 * @throws IOException         if any unexpected exception occurs
 */
public MetabolicNetwork parse(int reactionColumn, int reactionNameColumn, int headerRows,
        Pattern externalPattern) throws IOException {
    Set<String> reacNames = new HashSet<String>();
    ByteArrayOutputStream buf = new ByteArrayOutputStream();
    PrintWriter pw = new PrintWriter(new OutputStreamWriter(buf));
    HSSFRow row;
    int rowIndex = headerRows;
    try {
        row = mSheet.getRow(rowIndex++);
        while (row != null) {
            HSSFCell nameCell = row.getCell((short) reactionNameColumn);
            HSSFCell formCell = row.getCell((short) reactionColumn);
            if (nameCell != null || formCell != null) {
                if (nameCell == null) {
                    throw new IOException("reaction name cell " + (reactionNameColumn + 1) + " is empty");
                }
                if (formCell == null) {
                    throw new IOException("reaction formula cell " + (reactionColumn + 1) + " is empty");
                }
                String name = nameCell.toString().trim();
                String form = formCell.toString().trim();
                if (name.length() > 0 && form.length() > 0) {
                    if (reacNames.contains(name))
                        throw new Exception("duplicate reaction name: " + name);
                    reacNames.add(name);
                    pw.println("\"" + name + "\"\t\"" + name + "\"\t\"" + form + "\"");
                    row = mSheet.getRow(rowIndex++);
                } else {
                    LOG.info("row " + (rowIndex + 1) + " found empty in excel file '" + mFile.getAbsolutePath()
                            + "', stopping here.");
                    row = null;
                }
            } else {
                LOG.info("row " + (rowIndex + 1) + " found empty in excel file '" + mFile.getAbsolutePath()
                        + "', stopping here.");
                row = null;
            }
        }
        pw.flush();
        ByteArrayInputStream in = new ByteArrayInputStream(buf.toByteArray());
        CompartmentReaction[] reacts = externalPattern == null
                ? new PalssonParser().parseReactions(new InputStreamReader(in))
                : new PalssonParser().parseReactions(new InputStreamReader(in), externalPattern);
        return new DefaultMetabolicNetwork(reacts);
    } catch (Exception ex) {
        IOException ioe = new IOException(ex.getMessage() + " [row " + (rowIndex + 1) + ", file="
                + mFile.getAbsolutePath() + ", sheet=" + mSheetName + "]");
        ioe.initCause(ex);
        throw ioe;
    }
}

---

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

/** Don't do fixup between xrow0 and xrow1, non-inclusive. */
public void fixupFormulas(HSSFSheet sheet, int rowIx, int n, int xrow0, int xrow1) {
    //System.out.println("--------- fixupFormulas(" + rowIx + "," + n + ")");
    int prows = sheet.getPhysicalNumberOfRows();
    int pr = 0;/*from   w w w .j  a v  a 2 s . com*/
    for (int r = 0; pr < prows; r++) {
        HSSFRow row = sheet.getRow(r);
        if (row == null)
            continue;
        ++pr;

        int pcells = row.getPhysicalNumberOfCells();
        int pc = 0;
        for (int c = 0; pc < pcells; ++c) {
            HSSFCell cell = row.getCell((short) c);
            if (cell == null)
                continue;
            ++pc;

            // Fixup the formula
            if (cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA)
                continue;
            //System.out.println("Formula cell: " + cell.getCellFormula());
            //System.out.println("    ncells = " + row.getLastCellNum());
            FormulaParser fp = new FormulaParser(cell.getCellFormula(), wb.getWorkbook());
            fp.parse();
            Ptg[] ptg = fp.getRPNPtg();
            for (int i = 0; i < ptg.length; ++i) {
                Ptg pi = ptg[i];
                //               if (pi.getPtgClass() != Ptg.CLASS_REF) continue;
                if (pi instanceof AreaPtg) {
                    //System.out.println("Fixing area: " + pi);
                    AreaPtg pp = (AreaPtg) pi;
                    if (pp.getFirstRow() >= rowIx)
                        pp.setFirstRow((short) (pp.getFirstRow() + n));
                    if (pp.getLastRow() >= rowIx) {
                        pp.setLastRow((short) (pp.getLastRow() + n));
                    }
                } else if (pi instanceof ReferencePtg) {
                    ReferencePtg pp = (ReferencePtg) pi;
                    if (r >= xrow0 && r < xrow1) {
                        if (pp.getRow() <= r && pp.isRowRelative())
                            pp.setRow((short) (r + pp.getRow() - rowIx));
                    } else if (pp.getRow() >= rowIx) {
                        pp.setRow((short) (pp.getRow() + n));
                    }
                }
            }

            // Done fixing the formula; set it back
            String fstr = fp.toFormulaString(wb.getWorkbook(), ptg);
            //System.out.println("replacing formula string (" + r + "," + c + "): " + fstr);
            cell.setCellFormula(fstr);
        }
    }
}

---

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

/** Creates a new instance of PoiTest */
public void replaceHolders(java.util.Map<String, Object> models)
//throws Exception
{
    for (int k = 0; k < wb.getNumberOfSheets(); k++) {
        HSSFSheet sheet = wb.getSheetAt(k);

        // Iterate through all rows and cols of the sheet
        int prows = sheet.getPhysicalNumberOfRows();
        int pr = 0;
        for (int r = 0; pr < prows; r++) {
            System.out.println(r + ", " + pr + ", " + prows);
            HSSFRow row = sheet.getRow(r);
            if (row == null)
                continue;
            ++pr;/*from  w w  w.  j a  v  a2 s.c  o m*/
            int pcells = row.getPhysicalNumberOfCells();
            int pc = 0;
            for (int c = 0; pc < pcells; ++c) {
                HSSFCell cell = row.getCell((short) c);
                if (cell == null)
                    continue;
                ++pc;

                // Look for cells like ${var}
                if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING)
                    continue;

                String value = cell.getRichStringCellValue().getString().trim();
                if (!value.startsWith("${"))
                    continue;
                String rsname = value.substring(2, value.length() - 1);

                int n = replaceOneHolder(sheet, r, c, models, rsname);
                if (n != NOROWCHANGE) {
                    r += n;
                    break; // We just deleted the whole line!
                }
            }
        }
    }
}

---

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

/** Only copies formatting from col0 to col1, non-inclusive. */
void copyRow(HSSFRow r0, HSSFRow r1, int col0, int col1) {
    // Clear r1/*from w  ww.  j  a  v  a 2s  .  c  om*/
    int pcells = r1.getPhysicalNumberOfCells();
    int pc = 0;
    for (int c = 0; pc < pcells; ++c) {
        HSSFCell c1 = r1.getCell((short) c);
        if (c1 == null)
            continue;
        ++pc;
        r1.removeCell(c1);
    }

    // Copy over cells from r0
    pcells = r0.getPhysicalNumberOfCells();
    pc = 0;
    for (int c = 0; pc < pcells; ++c) {
        HSSFCell c0 = r0.getCell((short) c);
        if (c0 == null)
            continue;
        ++pc;
        HSSFCell c1 = r1.createCell((short) c);
        if (c >= col0 && c < col1)
            copyCellFormatting(c0, c1);
        else
            copyCell(c0, c1);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »