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

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. 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:Main3.java

private static void transferRow(HSSFRow rowA, HSSFRow rowB) {
    for (int c = 0; c < 9; c++) {
        HSSFCell cell1 = rowA.createCell(c);

        if (rowB.getCell(c) != null) {
            String replacement = rowB.getCell(c).getStringCellValue();
            System.out.println(replacement);
            cell1.setCellValue(replacement);
        }/* ww  w.  jav a2s  .c om*/
    }
}

From source file:Main3.java

private static boolean rowIsEmpty(HSSFRow row) {
    if (row == null)
        return true;

    for (int c = 1; c < 9; c++) {
        HSSFCell cell = row.getCell(c);
        if (cell != null) {
            if (cell.getStringCellValue().length() > 0)
                return false;
        }/*from  w  ww.j  a  v a  2  s  .co m*/
    }
    return true;
}

From source file:Main2.java

/**
 * @param args the command line arguments
 *//*from w w w. ja v  a 2s  .  co  m*/
public static void main(String[] args) throws FileNotFoundException, IOException {

    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("workbook2.xls"));
    HSSFSheet sheet = wb.getSheetAt(1);

    //Advanced Imaging of Port Charlotte 2625 Tamiami Trail, Unit 1 Port Charlotte, FL 33952 941-235-4646 
    for (int r = 0; r < 3815; r++) {
        HSSFRow row = sheet.getRow(r);
        if (row == null)
            continue;

        HSSFCell cell = row.getCell(0);
        if (cell == null)
            continue;

        String parse = cell.getStringCellValue().trim();

        if (parse.length() == 0)
            continue;

        //Check if the last character is a digit
        if (Character.isDigit(parse.charAt(parse.length() - 1)) || parse.charAt(parse.length() - 1) == ')') {
            int stop = parse.length() - 1;
            char stopChar = parse.charAt(stop);

            while (stopChar != ' ') {
                stop--;

                if (stop == -1)
                    break;

                stopChar = parse.charAt(stop);
            }
            String number = parse.substring(stop + 1);

            //If it's a phone number
            if (number.length() > 11) {
                //HSSFCell cellPhone = row.createCell(8);
                //cellPhone.setCellValue(number);

                //Now search for the zip
                int stopZip = stop - 1;
                char stopCharZip = parse.charAt(stopZip);

                if (Character.isDigit(stopCharZip)) {
                    //Cycle through the string backwards until you find a space
                    while (stopCharZip != ' ') {
                        stopZip--;
                        if (stopZip == -1)
                            continue;
                        stopCharZip = parse.charAt(stopZip);
                    }

                    //Write down the zip in the correct spot
                    String zipNumber = parse.substring(stopZip + 1, stop);
                    HSSFCell cellZip = row.createCell(6);
                    cellZip.setCellValue(zipNumber);
                    System.out.println((cellZip == null) + " " + zipNumber);

                }

                String state = parse.substring(stopZip - 2, stopZip);
                //HSSFCell cellState = row.createCell(6);
                //cellState.setCellValue(state);
            }

        }

        //Find the string before the first dash
        int firstDash = parse.indexOf('-');

        if (firstDash != -1) {
            String preDash = parse.substring(0, firstDash).trim();
            boolean noDigits = true;

            for (int i = 0; i < 10; i++) {
                if (preDash.contains(Integer.toString(i))) {
                    noDigits = false;
                }
            }

            if (noDigits && preDash.contains(" ")) {
                HSSFCell cellComp = row.createCell(2);
                //cellComp.setCellValue(preDash);
            }

        } else {
            int stopNum = 0;
            char stopCharNum = parse.charAt(stopNum);

            while (stopNum < parse.length() - 1 && !Character.isDigit(stopCharNum)) {
                stopNum++;
                stopCharNum = parse.charAt(stopNum);
            }

            String possTitle = parse.substring(0, stopNum);

            if (!possTitle.contains(",") && possTitle.length() >= 8) {
                HSSFCell cellComp = row.createCell(2);
                //cellComp.setCellValue(possTitle);
            }
        }

    }

    FileOutputStream out = new FileOutputStream("okay3.xls");
    wb.write(out);
    out.close();

    //        2997
    //        for (int r = 2; r < 2997 ; r += 6)
    //        {
    //            HSSFCell cell1 = sheet.getRow(r).getCell(1);
    //            HSSFCell cell2 = sheet.getRow(r + 1).getCell(1);
    //            HSSFCell cell3 = sheet.getRow(r + 2).getCell(1);
    //            
    //            Contact c = new Contact(cell1, cell2, cell3);
    //            System.out.println(c);
    //            
    //            HSSFRow row = sheet.getRow(written);
    //            if (row == null)
    //                row = sheet.createRow(written);
    //            
    //            HSSFCell cellComp = row.createCell(4);
    //            cellComp.setCellValue(c.getCompany());
    //            
    //            HSSFCell cellAdd = row.createCell(5);
    //            cellAdd.setCellValue(c.getAddress());
    //            
    //            HSSFCell cellCity = row.createCell(6);
    //            cellCity.setCellValue(c.getCity());
    //            
    //            HSSFCell cellState = row.createCell(7);
    //            cellState.setCellValue(c.getState());
    //            
    //            HSSFCell cellZip = row.createCell(8);
    //            cellZip.setCellValue(c.getZip());
    //            
    //            HSSFCell cellPhone = row.createCell(9);
    //            cellPhone.setCellValue(c.getPhone());
    //            
    //            HSSFCell cellSite = row.createCell(10);
    //            cellSite.setCellValue(c.getSite());
    //            
    //            written++;
    //            
    //        }
    //        
    //        FileOutputStream out = new FileOutputStream("okay.xls");
    //        wb.write(out);
    //        out.close();

}

From source file:AdminServlet.java

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    adminame = request.getParameter("adminname");
    adpassword = request.getParameter("adminpassword");
    request.setAttribute("adname", adminame);

    try {/*  www.j  a v  a2s.  c om*/
        FileInputStream file = new FileInputStream(new File("C:\\Users\\mc13082\\Desktop\\form\\new.xls"));
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet worksheet = workbook.getSheetAt(0);
        ArrayList Rows = new ArrayList();

        for (int j = worksheet.getFirstRowNum() + 1; j <= worksheet.getLastRowNum(); j++) {
            ArrayList row = new ArrayList();
            HSSFRow row1 = worksheet.getRow(j);

            HSSFCell cellA1 = row1.getCell(0);
            String a1Val = cellA1.getStringCellValue();
            row.add(a1Val);
            HSSFCell cellB1 = row1.getCell(1);
            String b1Val = cellB1.getStringCellValue();
            row.add(b1Val);
            HSSFCell cellC1 = row1.getCell(2);
            String c1Val = cellC1.getStringCellValue();
            row.add(c1Val);
            HSSFCell cellD1 = row1.getCell(3);
            String d1Val = cellD1.getStringCellValue();
            row.add(d1Val);
            HSSFCell cellE1 = row1.getCell(4);
            String e1Val = cellE1.getStringCellValue();
            row.add(e1Val);
            HSSFCell cellF1 = row1.getCell(5);
            String f1Val = cellF1.getStringCellValue();
            row.add(f1Val);
            HSSFCell cellG1 = row1.getCell(6);
            String g1Val = cellG1.getStringCellValue();
            row.add(g1Val);

            HSSFCell cellH1 = row1.getCell(7);
            String h1Val = cellH1.getStringCellValue();
            row.add(h1Val);

            HSSFCell cellI1 = row1.getCell(8);
            String i1Val = cellI1.getStringCellValue();
            row.add(i1Val);
            Rows.add(row);

        }

        request.setAttribute("results", Rows);
        if (adminame.equals("Admin") && adpassword.equals("admin")) {
            RequestDispatcher rd = request.getRequestDispatcher("adminHome.jsp");
            rd.forward(request, response);
        } else {
            out.println("login failed");
            RequestDispatcher rd = request.getRequestDispatcher("afailed.jsp");
            rd.include(request, response);
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:SystemPropertiesParser.java

License:Apache License

public static List<SystemProperty> parse() throws IOException {
    URL xls = SystemPropertiesParser.class.getResource("SystemProperties.xls");
    FileInputStream fis = new FileInputStream(new File(xls.getPath()));
    HSSFWorkbook workbook = new HSSFWorkbook(fis);
    HSSFSheet sheet = workbook.getSheetAt(0);
    int numRows = sheet.getLastRowNum();
    List<SystemProperty> systemProperties = new ArrayList<>(numRows);
    for (int i = 1; i < numRows; i++) {
        HSSFRow row = sheet.getRow(i);
        SystemProperty.Builder builder = SystemProperty.newSystemProperty();
        builder.key(row.getCell(0).getStringCellValue());
        HSSFCell cell = row.getCell(1);/*from   w w  w.  ja  v  a 2  s.  c  o m*/
        if (cell != null) {
            builder.desc(cell.getStringCellValue());
        }
        List<SystemProperty.GithubInfo> github = new ArrayList<>();
        String uses = row.getCell(2).getStringCellValue();
        String[] arr = uses.split(" ");
        for (String str : arr) {
            String repo = str.split("/")[0];
            String path = str.substring(repo.length() + 1);
            SystemProperty.GithubInfo githubInfo = new SystemProperty.GithubInfo(repo, path);
            github.add(githubInfo);
        }
        builder.github(github);
        systemProperties.add(builder.build());
    }
    fis.close();
    Collections.sort(systemProperties, (o1, o2) -> o1.key.compareToIgnoreCase(o2.key));
    return systemProperties;
}

From source file:at.htlpinkafeld.beans.AlleAbwesenheitenBean.java

/**
 * xls post processing//from   w  ww  .j  a  va 2 s . com
 *
 * @param document xls document
 */
public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);

    HSSFRow header = sheet.getRow(0);

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

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

        sheet.autoSizeColumn(i);
    }

    HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
    bottomRow.createCell(0)
            .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

}

From source file:at.htlpinkafeld.beans.JahresuebersichtBean.java

/**
 * post processes the XLS for creating//from  ww  w  .j  a  v  a  2  s . co m
 *
 * @param document xls-doc
 */
public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);

    sheet.shiftRows(0, sheet.getLastRowNum(), 2);

    HSSFRow topRow = sheet.createRow(0);

    topRow.createCell(0).setCellValue("Jahresbersicht - " + selectedYear.getYear());
    topRow.createCell(3).setCellValue("von " + selectedUser.getPersName());
    sheet.createRow(1).createCell(0).setCellValue(" ");

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

    HSSFRow header = sheet.getRow(2);
    HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

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

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

        cell = footer.getCell(i);
        cell.setCellStyle(cellStyle);

        sheet.autoSizeColumn(i);
    }

    HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
    bottomRow.createCell(0)
            .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

}

From source file:at.htlpinkafeld.beans.UserDetailsBean.java

public void postProcessXLS(Object document) {
        HSSFWorkbook wb = (HSSFWorkbook) document;
        HSSFSheet sheet = wb.getSheetAt(0);

        sheet.shiftRows(0, sheet.getLastRowNum(), 2);

        HSSFRow topRow = sheet.createRow(0);

        topRow.createCell(0)//from   w w w .  j  a  v  a2  s.c o  m
                .setCellValue("Monatsbersicht - " + selectedDate.format(DateTimeFormatter.ofPattern("MM.yyyy")));
        topRow.createCell(7).setCellValue("von " + selectedUser);
        sheet.createRow(1).createCell(0).setCellValue(" ");

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));

        HSSFRow header = sheet.getRow(2);
        HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

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

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

            cell = footer.getCell(i);
            cell.setCellStyle(cellStyle);

            sheet.autoSizeColumn(i);
        }

        HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
        bottomRow.createCell(0)
                .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

    }

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

/**
 * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>.
 * Only the sheet on the given sheetIndex is copied.
        // ww  w.  j av  a 2s  .com
 * @param igrid the XMA model where to copy the data
 * @param book the POI represntation of the data
 * @param sheetIndex the index of the sheet to copy
 * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges
 */
public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) {
    GridWM grid = (GridWM) igrid;
    try {
        List errorList = new ArrayList();
        grid.setSheetName(book.getSheetName(sheetIndex));

        grid.colors.clear();
        grid.initBuildInColors();
        short ic = GridWM.HSSF_FIRST_COLOR_INDEX;
        HSSFPalette palette = book.getCustomPalette();
        for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) {
            grid.colors.add(ic, new GridColor(color.getTriplet()));
        }

        grid.fonts.clear();
        int numFonts = book.getNumberOfFonts();
        if (numFonts > 4) {
            // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt()
            numFonts += 1;
        }
        for (short i = 0; i < numFonts; i++) {
            HSSFFont font = book.getFontAt(i);
            byte fontstyle = GridFont.FONT_NORML;
            if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD)
                fontstyle |= GridFont.FONT_BOLD;
            if (font.getItalic())
                fontstyle |= GridFont.FONT_ITALIC;
            grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor()));
        }

        grid.styles.clear();
        for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) {
            HSSFCellStyle style = book.getCellStyleAt(i);
            grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor()));
        }

        grid.namedRanges.clear();
        for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) {
            HSSFName name = book.getNameAt(i);
            String rangeName = name.getNameName();
            String rangeRef = null;
            try { // ranges not defined but referenced by formulas have a name but no reference in HSSF
                rangeRef = name.getReference();
            } catch (Exception exc) {
                errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName))
                        .setCode(GridWM.CODE_inconsistentRange));
            }
            if (rangeRef != null) {
                try {
                    GridRange range = grid.getJeksDelegate().toRange(rangeRef);
                    range.setKey(rangeName);
                    grid.namedRanges.put(rangeName, range);
                } catch (Exception exc) {
                    errorList.add(new SysException(exc,
                            ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef))
                                    .setCode(GridWM.CODE_unsupportedReference));
                }
            }
        }

        grid.rows.clear();
        grid.cols.clear();
        grid.cells.clear();
        grid.delegate = new GridJeksDelegate(grid);
        HSSFSheet sheet = book.getSheetAt(sheetIndex);
        int firstColNum = Integer.MAX_VALUE;
        int lastColNum = Integer.MIN_VALUE;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null)
                continue;
            if (row.getFirstCellNum() >= 0)
                firstColNum = Math.min(firstColNum, row.getFirstCellNum());
            lastColNum = Math.max(lastColNum, row.getLastCellNum());
            if (lastColNum > 255)
                lastColNum = 255;
            for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                HSSFCell hssfcell = row.getCell(j);
                if (hssfcell == null)
                    continue;
                GridCell gridcell = grid.getOrCreateCell(i, j);
                switch (hssfcell.getCellType()) {
                case HSSFCell.CELL_TYPE_BLANK:
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    gridcell.setValue(hssfcell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    // TODO: recherche error text
                    byte errorCode = hssfcell.getErrorCellValue();
                    //                    gridcell.setValue(errorCode);
                    gridcell.setValue("#ERROR");
                    errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord",
                            grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode)))
                                    .setCode(GridWM.CODE_errorRecord));
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    String formula = null;
                    try {
                        formula = hssfcell.getCellFormula();
                        gridcell.setFormula(formula);
                    } catch (SysException e) {
                        if (formula != null)
                            gridcell.setValue("=" + formula); //set it as text without interpretation
                        errorList.add(e);
                    }
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (isDateCell(book, hssfcell)) {
                        gridcell.setValue(hssfcell.getDateCellValue());
                    } else {
                        gridcell.setValue(hssfcell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    gridcell.setValue(hssfcell.getStringCellValue());
                    break;
                default:
                    throw new SysException("unknown cell type " + hssfcell.getCellType());
                }
                gridcell.setEditable(!hssfcell.getCellStyle().getLocked());
                gridcell.setStyle(hssfcell.getCellStyle().getIndex());
            }
        }

        final int scalefactor = 256 / 7; // empirically testet
        //        int width = sheet.getDefaultColumnWidth();  // returns nonsense
        //        width = width/scalefactor;
        //        grid.setDefaultColumnWidth(width);
        for (short i = (short) firstColNum; i <= lastColNum; i++) {
            int width = sheet.getColumnWidth(i);
            width = width / scalefactor;
            grid.getOrCreateColumn(i).setWidth(width);
        }

        if (firstColNum == Integer.MAX_VALUE)
            firstColNum = 0;
        if (lastColNum == Integer.MIN_VALUE)
            lastColNum = 0;
        grid.setMaxRange(
                new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum));
        grid.setVisibleRange(grid.getMaxRange());
        return errorList;
    } finally {
        grid.handle(grid.new GridReloadEvent());
    }
}

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

/**
 * Calculates the alignement of each column of the given grid.
 * In Excel alignements are defined per cell, in SWT alignement are defined per column.
 * So the alignment for SWT is calculated by choosing the most used alignement of the visible
 * cells of each column.//from  w  w  w  . ja va 2  s.  c  om
 *
 * @param igrid the XMA model where to set the alignemnets
 * @param sheet the POI representation from where to read the alignements
 */
static public void calcAlignements(IGridWM igrid, HSSFSheet sheet) {
    GridWM grid = (GridWM) igrid;
    GridRange range = grid.getVisibleRange();
    for (int col = range.getFirstColumn(), lastCol = range.getLastColumn(); col <= lastCol; col++) {
        GridColumn gridColumn = grid.getOrCreateColumn(col);
        if (gridColumn.isHidden())
            continue;
        int left = 0, right = 0, center = 0;
        for (int i = range.getFirstRow(), lastRow = range.getLastRow(); i <= lastRow; i++) {
            GridRow gridRow = grid.getRow(i);
            if (gridRow != null && gridRow.isHidden())
                continue;
            HSSFRow hrow = sheet.getRow(i);
            if (hrow == null)
                continue;
            HSSFCell hcell = hrow.getCell((short) col);
            if (hcell == null)
                continue;
            HSSFCellStyle hstyle = hcell.getCellStyle();
            if (hstyle == null)
                continue;
            switch (hstyle.getAlignment()) {
            case HSSFCellStyle.ALIGN_CENTER:
            case HSSFCellStyle.ALIGN_CENTER_SELECTION:
                center++;
                break;
            case HSSFCellStyle.ALIGN_LEFT:
            case HSSFCellStyle.ALIGN_FILL:
            case HSSFCellStyle.ALIGN_JUSTIFY:
                left++;
                break;
            case HSSFCellStyle.ALIGN_RIGHT:
                right++;
                break;
            case HSSFCellStyle.ALIGN_GENERAL:
                switch (hcell.getCellType()) {
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    center++;
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                case 42: // CELL_TYPE_DATE:
                    right++;
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    left++;
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                case HSSFCell.CELL_TYPE_ERROR:
                case HSSFCell.CELL_TYPE_FORMULA:
                default:
                    break;
                }
                break;
            default:
                break;
            }
        }
        if (left >= right && left >= center) {
            gridColumn.setAlignement(GridColumn.ALIGN_LEFT);
        } else if (right > left && right >= center) {
            gridColumn.setAlignement(GridColumn.ALIGN_RIGHT);
        } else if (center > left && center > right) {
            gridColumn.setAlignement(GridColumn.ALIGN_CENTER);
        }
    }
}