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

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

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

Introduction

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

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void formatCellValueForIPass(HSSFWorkbook wb, Cell cell, Object oneCellValue, String vendor)
        throws Exception {
    if (oneCellValue == null) {
        oneCellValue = StringUtils.EMPTY;
        return;/* w w w  .j a v  a 2 s . com*/
    }

    int columnIndex = cell.getColumnIndex();
    if (columnIndex == 5) { // Driver
        setCellValueDriverFormat(wb, cell, oneCellValue);
    } else if (oneCellValue instanceof Date || columnIndex == 6 || columnIndex == 10) { // Transaction date and time, Invoice date
        setCellValueDateFormat(wb, cell, oneCellValue, vendor);
    } else if (columnIndex == 9) { // Amount
        setCellValueFeeFormat(wb, cell, oneCellValue, vendor);
        /*} else if (columnIndex == 11) {
           setCellValueUnitNumberFormat(wb, cell, oneCellValue, vendor);*/
    } else {
        cell.setCellValue(oneCellValue.toString().toUpperCase());
    }
}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void formatCellValueForSunPass(HSSFWorkbook wb, Cell cell, Object oneCellValue, String vendor)
        throws Exception {
    if (oneCellValue == null) {
        oneCellValue = StringUtils.EMPTY;
        return;/*from  w ww  .ja  v  a2  s.  co  m*/
    }

    int columnIndex = cell.getColumnIndex();
    if (columnIndex == 3) { // Tag num
        setCellValueTagNumberFormat(wb, cell, oneCellValue, vendor);
    } else if (columnIndex == 5) { // Driver
        setCellValueDriverFormat(wb, cell, oneCellValue);
    } else if (oneCellValue instanceof Date || columnIndex == 6 || columnIndex == 10) { // Transaction date and time, Invoice date
        setCellValueDateFormat(wb, cell, oneCellValue, vendor);
    } else if (columnIndex == 9) { // Amount
        setCellValueFeeFormat(wb, cell, oneCellValue, vendor);
        /*} else if (columnIndex == 11) {
           setCellValueUnitNumberFormat(wb, cell, oneCellValue, vendor);*/
    } else {
        cell.setCellValue(oneCellValue.toString().toUpperCase());
    }
}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void setCellValueDateFormat(Workbook wb, Cell cell, Object oneCellValue, String vendor)
        throws ParseException {
    System.out.println("Incoming vendor = " + vendor);
    String tollCompanyDateFormat = tollCompanyToDateFormatMapping.get(vendor);
    System.out.println("Value = " + tollCompanyDateFormat);

    int columnIndex = cell.getColumnIndex();

    if (oneCellValue instanceof Date) {
        System.out.println("Incoming date is a Date Object.");
        tollCompanyDateFormat = "EEE MMM dd HH:mm:ss z yyyy";
    }/* w  ww.j  a  v a 2s  . co m*/

    String dateStr = StringUtils.trimToEmpty(oneCellValue.toString());

    if (StringUtils.isEmpty(dateStr)) {
        cell.setCellValue(StringUtils.EMPTY);
    } else {
        if (columnIndex == 7) { // Transaction time
            cell.setCellValue(convertToExpectedTimeFormatStr(dateStr, tollCompanyDateFormat));
            return;
        } else {
            cell.setCellValue(convertToExpectedDateFormat(dateStr, tollCompanyDateFormat));
        }
    }

    CellStyle style = wb.createCellStyle();
    style.setDataFormat(wb.createDataFormat().getFormat(expectedDateFormat.toPattern()));
    cell.setCellStyle(style);
}

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * foreach?//www.  j a  v  a2 s  .  c  o  m
 * @param cell 
 * @param map
 * @param oldString
 * @throws Exception 
 */
private void addListDataToExcel(Cell cell, Map<String, Object> map, String name) throws Exception {
    boolean isCreate = !name.contains(PoiElUtil.FOREACH_NOT_CREATE);
    boolean isShift = name.contains(PoiElUtil.FOREACH_AND_SHIFT);
    name = name.replace(PoiElUtil.FOREACH_NOT_CREATE, PoiElUtil.EMPTY)
            .replace(PoiElUtil.FOREACH_AND_SHIFT, PoiElUtil.EMPTY).replace(PoiElUtil.FOREACH, PoiElUtil.EMPTY)
            .replace(PoiElUtil.START_STR, PoiElUtil.EMPTY);
    String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
    Collection<?> datas = (Collection<?>) com.qihang.winter.poi.util.PoiPublicUtil.getParamsValue(keys[0], map);
    List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> columns = getAllDataColumns(cell,
            name.replace(keys[0], PoiElUtil.EMPTY));
    if (datas == null) {
        return;
    }
    Iterator<?> its = datas.iterator();
    Row row;
    int rowIndex = cell.getRow().getRowNum() + 1;
    //??
    if (its.hasNext()) {
        Object t = its.next();
        cell.getRow().setHeight(columns.get(0).getHeight());
        setForEeachCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map);
    }
    if (isShift) {
        cell.getRow().getSheet().shiftRows(cell.getRowIndex() + 1, cell.getRow().getSheet().getLastRowNum(),
                datas.size() - 1, true, true);
    }
    while (its.hasNext()) {
        Object t = its.next();
        if (isCreate) {
            row = cell.getRow().getSheet().createRow(rowIndex++);
        } else {
            row = cell.getRow().getSheet().getRow(rowIndex++);
            if (row == null) {
                row = cell.getRow().getSheet().createRow(rowIndex - 1);
            }
        }
        row.setHeight(columns.get(0).getHeight());
        setForEeachCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map);
    }
}

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ??/*from   w  w w. j a v  a2s . co  m*/
 * @param cell
 * @param name
 * @return
 */
private List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> getAllDataColumns(Cell cell,
        String name) {
    List<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams> columns = new ArrayList<com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams>();
    cell.setCellValue("");
    if (name.contains(PoiElUtil.END_STR)) {
        columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams(
                name.replace(PoiElUtil.END_STR, PoiElUtil.EMPTY).trim(), cell.getCellStyle(),
                cell.getRow().getHeight()));
        return columns;
    }
    columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams(name.trim(),
            cell.getCellStyle(), cell.getRow().getHeight()));
    int index = cell.getColumnIndex();
    Cell tempCell;
    while (true) {
        tempCell = cell.getRow().getCell(++index);
        if (tempCell == null) {
            break;
        }
        String cellStringString;
        try {//?,??,?
            cellStringString = tempCell.getStringCellValue();
            if (StringUtils.isBlank(cellStringString)) {
                break;
            }
        } catch (Exception e) {
            throw new ExcelExportException("for each ,?");
        }
        //?cell 
        tempCell.setCellValue("");
        if (cellStringString.contains(PoiElUtil.END_STR)) {
            columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams(
                    cellStringString.trim().replace(PoiElUtil.END_STR, ""), tempCell.getCellStyle(),
                    tempCell.getRow().getHeight()));
            break;
        } else {
            if (cellStringString.trim().contains(teplateParams.getTempParams())) {
                columns.add(new com.qihang.winter.poi.excel.entity.params.ExcelTemplateParams(
                        cellStringString.trim(), tempCell.getCellStyle(), tempCell.getRow().getHeight()));
            } else {
                //?
                break;
            }
        }

    }
    return columns;
}

From source file:com.qihang.winter.poi.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * ????//from   w w w.ja va2s  . c  o m
 * @param rows
 * @param params
 * @param excelCollection
 * @return
 */
private Map<Integer, String> getTitleMap(Iterator<Row> rows,
        com.qihang.winter.poi.excel.entity.ImportParams params,
        List<com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams> excelCollection) {
    Map<Integer, String> titlemap = new HashMap<Integer, String>();
    Iterator<Cell> cellTitle;
    String collectionName = null;
    com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams collectionParams = null;
    Row row = null;
    for (int j = 0; j < params.getHeadRows(); j++) {
        row = rows.next();
        if (row == null) {
            continue;
        }
        cellTitle = row.cellIterator();
        while (cellTitle.hasNext()) {
            Cell cell = cellTitle.next();
            String value = getKeyValue(cell);
            int i = cell.getColumnIndex();
            //????
            if (StringUtils.isNotEmpty(value)) {
                if (titlemap.containsKey(i)) {
                    collectionName = titlemap.get(i);
                    collectionParams = getCollectionParams(excelCollection, collectionName);
                    titlemap.put(i, collectionName + "_" + value);
                } else if (StringUtils.isNotEmpty(collectionName)
                        && collectionParams.getExcelParams().containsKey(collectionName + "_" + value)) {
                    titlemap.put(i, collectionName + "_" + value);
                } else {
                    collectionName = null;
                    collectionParams = null;
                }
                if (StringUtils.isEmpty(collectionName)) {
                    titlemap.put(i, value);
                }
            }
        }
    }
    return titlemap;
}

From source file:com.qualogy.qafe.service.DocumentServiceImpl.java

License:Apache License

private DocumentOutput handleExcelData(Sheet sheetData, boolean hasRowHeader) {
    DocumentOutput docOutput = new DocumentOutput();

    // Determine the column names
    List<String> columnNameList = new ArrayList<String>();
    if (sheetData.rowIterator().hasNext()) {
        Row row = sheetData.rowIterator().next();
        int emptyColCountChain = 0;
        String colName = null;//from   ww  w  .j  ava  2 s.  c  o  m
        for (Iterator<Cell> itr = row.cellIterator(); itr.hasNext();) {
            Cell cell = itr.next();
            boolean cellHasData = (cell.getCellType() != Cell.CELL_TYPE_BLANK);
            if (hasRowHeader && cellHasData) {
                colName = getCellValue(cell);
            } else {
                colName = DEFAULT_FIELD_NAME + cell.getColumnIndex();
            }
            columnNameList.add(colName);

            if (cellHasData) {
                emptyColCountChain = 0;
            } else {
                emptyColCountChain++;
            }
            if (emptyColCountChain > EMPTY_NUMCOLUMNS_TOLERANCE) {
                break;
            }
        }
    }

    // Get the data from sheet
    List<Map<String, String>> data = new ArrayList<Map<String, String>>();
    boolean[] columnsHaveData = new boolean[columnNameList.size()];
    for (Iterator<Row> itr = sheetData.rowIterator(); itr.hasNext();) {
        Row row = itr.next();
        if (hasRowHeader && (row.getRowNum() == 0)) {
            continue;
        }
        Map<String, String> rowData = new LinkedHashMap<String, String>();
        boolean rowHasData = false;
        for (Iterator<Cell> itr2 = row.cellIterator(); itr2.hasNext();) {
            Cell cell = itr2.next();
            if (cell.getColumnIndex() < columnNameList.size()) {
                String colName = columnNameList.get(cell.getColumnIndex());
                String cellValue = null;
                if (cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    cellValue = getCellValue(cell);
                }
                boolean cellHasData = ((cellValue != null) && (cellValue.length() > 0));
                columnsHaveData[cell.getColumnIndex()] = columnsHaveData[cell.getColumnIndex()] || cellHasData;
                rowHasData = rowHasData || cellHasData;
                rowData.put(colName, cellValue);
            } else {
                break;
            }
        }
        if (rowHasData) {
            data.add(rowData);
        }
    }

    removeEmptyColumns(columnNameList, data, columnsHaveData);

    printData(data);
    docOutput.setData(data);
    return docOutput;
}

From source file:com.quanticate.opensource.datalistdownload.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body./*from   w  w w. j  a v  a 2s . c om*/
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");

    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY);
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else if ("odf".equals(format) || "ods".equals(format)) {
        try {
            SpreadsheetDocument odf = SpreadsheetDocument.newSpreadsheetDocument();

            // Add the header row
            Table sheet = odf.appendSheet("Export");
            org.odftoolkit.simple.table.Row hr = sheet.appendRow();

            // TODO

            // Have the contents populated
            // TODO

            // Save it for the template
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            odf.save(baos);
            model.put(MODEL_ODF, baos.toByteArray());
        } catch (Exception e) {
            throw new WebScriptException("Error creating ODF file", e);
        }
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        sheet.createFreezePane(0, 1);

        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

private void merge(Workbook excerptWB, Workbook fullWB, String[] sheetsToMerge, OutputStream output)
        throws IOException {
    // Identify the sheets in both workbooks
    List<Sheet> sourceSheets = identifySheets(sheetsToMerge, excerptWB);
    List<Sheet> destSheets = identifySheets(sheetsToMerge, fullWB);

    // Process each sheet from the excerpt in turn
    for (int i = 0; i < sheetsToMerge.length; i++) {
        Sheet source = sourceSheets.get(i);
        Sheet dest = destSheets.get(i);/*from w  w  w  .  j  a v  a2  s .  c o  m*/

        for (Row srcR : source) {
            for (Cell srcC : srcR) {
                if (srcC.getCellType() == Cell.CELL_TYPE_FORMULA
                        || srcC.getCellType() == Cell.CELL_TYPE_ERROR) {
                    // Don't merge these kinds of cells
                } else {
                    Row destR = dest.getRow(srcR.getRowNum());
                    if (destR == null) {
                        // Newly added row to the excerpt file, skip this
                    } else {
                        Cell destC = destR.getCell(srcC.getColumnIndex());
                        if (destC == null && srcC.getCellType() == Cell.CELL_TYPE_BLANK) {
                            // Both are empty, don't need to do anything
                        } else {
                            if (destC == null)
                                destC = destR.createCell(srcC.getColumnIndex(), srcC.getCellType());

                            // Sync contents
                            if (srcC.getCellType() == Cell.CELL_TYPE_BLANK) {
                                destC.setCellType(Cell.CELL_TYPE_BLANK);
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                                destC.setCellValue(srcC.getBooleanCellValue());
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                destC.setCellValue(srcC.getNumericCellValue());
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_STRING) {
                                destC.setCellValue(srcC.getStringCellValue());
                            }

                            // Sync formatting rules
                            // TODO
                        }
                    }
                }
            }
        }
    }

    // Re-evaluate all the formulas in the destination workbook, now that
    //  we have updated cells in it
    FormulaEvaluator eval = fullWB.getCreationHelper().createFormulaEvaluator();
    eval.evaluateAll();

    // Save the new file
    fullWB.write(output);
}

From source file:com.r573.enfili.common.doc.spreadsheet.SpreadsheetHelper.java

License:Apache License

public static String getSpreadsheetCellReference(Cell cell) {
    return getSpreadsheetCellReference(cell.getColumnIndex(), cell.getRowIndex());
}