Example usage for org.apache.poi.hssf.usermodel HSSFSheet getRow

Introduction

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

Prototype

@Override
public HSSFRow getRow(int rowIndex) 

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:Main3.java

/**
 * @param args the command line arguments
 *///from   w  ww  . j  a va 2s . c  o  m
public static void main(String[] args) throws FileNotFoundException, IOException {

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

    removeRow(sheet, 3);

    for (int i = 3; i < 4000; i++) {
        HSSFRow origRow = sheet.getRow(i);
        if (rowIsEmpty(origRow)) {
            System.out.println(i);
            //removeRow(sheet, i);
        }
    }

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

---

From source file:Main3.java

public static void removeRow(HSSFSheet sheet, int rowIndex) {
    int lastRowNum = sheet.getLastRowNum();
    if (rowIndex >= 0 && rowIndex < lastRowNum) {
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
    }/*from  www .  ja v  a2 s.co m*/
    if (rowIndex == lastRowNum) {
        HSSFRow removingRow = sheet.getRow(rowIndex);
        if (removingRow != null) {
            sheet.removeRow(removingRow);
        }
    }
}

---

From source file:NewMain.java

/**
 * @param args the command line arguments
 *//* ww w.  jav a 2  s .  c  o m*/
public static void main(String[] args) throws FileNotFoundException, IOException {

    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("workbook.xls"));
    HSSFSheet sheet = wb.getSheetAt(0);

    int written = 2;

    //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:Main2.java

/**
 * @param args the command line arguments
 *//*from w  w  w . jav  a  2s. c  om*/
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:TestUtil.java

License:BSD License

private void writeCS(Node csNode, HSSFWorkbook wb) {
    HSSFSheet sheet = wb.getSheet("tbl_CLASSIFICATION_SCHEMES");
    HSSFRow row = sheet.getRow(10);
    HSSFCell prefNameCell = row.createCell(3);
    HSSFCell longNameCell = row.createCell(4);
    HSSFCell prefDefCell = row.createCell(5);

    prefNameCell.setCellValue(new HSSFRichTextString(csNode.getTextContent()));
    longNameCell.setCellValue(new HSSFRichTextString(csNode.getTextContent()));
    prefDefCell.setCellValue(new HSSFRichTextString(csNode.getTextContent()));
}

---

From source file:TestUtil.java

License:BSD License

private void writeCSI(Node csiNode, HSSFWorkbook wb) {
    HSSFSheet sheet = wb.getSheet("tbl_CS_ITEMS");
    HSSFRow row = sheet.getRow(10);
    HSSFCell prefNameCell = row.createCell(6);
    HSSFCell longNameCell = row.createCell(7);
    HSSFCell prefDefCell = row.createCell(8);

    prefNameCell.setCellValue(new HSSFRichTextString(csiNode.getTextContent()));
    longNameCell.setCellValue(new HSSFRichTextString(csiNode.getTextContent()));
    prefDefCell.setCellValue(new HSSFRichTextString(csiNode.getTextContent()));
}

---

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 {//from   ww w.j a  v  a 2s . co  m
        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  ww .  j  a v  a  2s.co  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. ja v  a  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/*  ww w.j av  a 2  s .  c  om*/
 *
 * @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")));

}

---

• «
• 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
• »