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:com.xhsoft.framework.common.file.ExcelHandle.java

License:Open Source License

/**
 * ????.xls//from w w w .  j  a v  a2s  .  c  om
 * @params {:,:}
 * @return void
 * @author lijiangwei
 * @since 2012-11-12
 */
private void readXLS() {
    for (int sheetNum = 0; sheetNum < xls.getNumberOfSheets(); sheetNum++) {
        HSSFSheet xls_sheet = xls.getSheetAt(sheetNum);
        if (xls_sheet == null) {
            continue;
        }
        // ??Sheet
        for (int rowNum = 0; rowNum <= xls_sheet.getLastRowNum(); rowNum++) {
            HSSFRow xls_row = xls_sheet.getRow(rowNum);
            if (xls_row == null) {
                continue;
            }
            // ????
            for (int cellNum = 0; cellNum < xls_row.getLastCellNum(); cellNum++) {
                HSSFCell xls_cell = xls_row.getCell(cellNum);
                if (xls_cell == null) {
                    continue;
                }
                System.out.print("   " + getCellValue(xls_cell));
            }
            System.out.println();
        }
    }
}

---

From source file:com.xpn.xwiki.plugin.lucene.textextraction.MSExcelTextExtractor.java

License:Open Source License

/**
 * It will parse the sheet with row wise and get the text from the sheet.
 *///from w  w  w.  ja  v a  2 s .c  om
private void parseSheet(HSSFSheet sheet, StringBuffer cleanBuffer) {
    int firstRow = sheet.getFirstRowNum();
    int lastRow = sheet.getLastRowNum();
    for (int rowIdx = firstRow; rowIdx <= lastRow; rowIdx++) {
        HSSFRow row = sheet.getRow(rowIdx);

        if (row != null) {
            parseRow(row, cleanBuffer);
        }
    }
}

---

From source file:com.xx.platform.util.tools.ms.ExcelExtractor.java

License:Apache License

public String extractText(POIFSFileSystem poifs) throws Exception {
    StringBuffer resultText = new StringBuffer();
    HSSFWorkbook wb = new HSSFWorkbook(poifs, true);
    if (wb == null) {
        return "";
    }/*from w ww. ja  va 2 s.c  o m*/

    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();

            for (int k = 0; k < cNum; k++) {
                if ((cell = row.getCell((short) k)) != null) {
                    /*if(HSSFDateUtil.isCellDateFormatted(cell) == true) {
                        resultText += cell.getDateCellValue().toString() + " ";
                      } else
                     */
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        resultText.append(cell.getStringCellValue());
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        Double d = new Double(cell.getNumericCellValue());
                        resultText.append(d.toString());
                    }
                    /* else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
                         resultText += cell.getCellFormula() + " ";
                       }
                     */
                }
            }
        }
    }
    return resultText.toString();
}

---

From source file:com.xx.platform.util.tools.ms.ExcelExtrator.java

License:Apache License

public String extractText(POIFSFileSystem poifs) throws Exception {
    List<Map<String, String>> list = new ArrayList<Map<String, String>>();
    String text = null;/*from w w  w  .  jav  a  2  s .co  m*/
    try {
        HSSFWorkbook wb = new HSSFWorkbook(poifs, true);
        if (wb == null) {
            return null;
        }

        HSSFSheet sheet;
        HSSFRow row;
        HSSFCell cell;
        int sNum = 0;
        int rNum = 0;
        int cNum = 0;

        sNum = wb.getNumberOfSheets();
        for (int i = 0; i < sNum; i++) {
            if ((sheet = wb.getSheetAt(i)) == null) {
                continue;
            }
            String[] key = null; //field
            boolean init = false; //key 
            rNum = sheet.getLastRowNum();
            for (int j = 0; j <= rNum; j++) {
                if ((row = sheet.getRow(j)) == null) {
                    continue;
                }
                Map<String, String> rowdata = new HashMap<String, String>();
                cNum = row.getLastCellNum();
                if (!init)
                    key = new String[cNum];
                String value = "";
                StringBuffer content = new StringBuffer();
                for (int k = 0; k < cNum; k++) {
                    if ((cell = row.getCell((short) k)) != null) {
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            value = cell.getStringCellValue();
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            value = d.toString();
                        } else
                            value = "";
                        if (init) {
                            content.append(value);
                        } else {
                            key[k] = value;
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        text = "";
    }
    return text;

}

---

From source file:com.yf.kp.design.siswa.FrameSiswa.java

public void fillData(File file) {
    try {//from  www.  j  a v a2 s.c  o  m
        siswaService = new SiswaServiceImpl();
        Siswa siswa;
        try (FileInputStream input = new FileInputStream(file)) {
            POIFSFileSystem fs = new POIFSFileSystem(input);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            Row row;
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                System.out.println(i);
                row = sheet.getRow(i);
                siswa = new Siswa();
                siswa.setNis(row.getCell(0).getStringCellValue());
                siswa.setNama(row.getCell(1).getStringCellValue());
                siswa.setKelas(row.getCell(2).getStringCellValue());
                siswa.setJenis_kelamin(row.getCell(3).getStringCellValue());
                siswa.setAgama(row.getCell(4).getStringCellValue());
                siswa.setTempat_lahir(row.getCell(5).getStringCellValue());
                siswa.setTgl_lahir(row.getCell(6).getDateCellValue());
                siswa.setAlamat(row.getCell(7).getStringCellValue());
                siswa.setNama_ortu(row.getCell(8).getStringCellValue());
                siswa.setPekerjaan(row.getCell(9).getStringCellValue());
                siswa.setAgama_ortu(row.getCell(10).getStringCellValue());
                siswa.setTelp(row.getCell(11).getStringCellValue());
                siswa.setAlamat_ortu(row.getCell(12).getStringCellValue());
                siswaService.save(siswa);
            }
        }
        JOptionPane.showMessageDialog(this, "Data Berhasil Dimasukan");
    } catch (HeadlessException | IOException | HibernateException | OfficeXmlFileException
            | IllegalStateException | NullPointerException e) {
        JOptionPane.showMessageDialog(this, "Error, Contact Your System Admin\n" + e.getMessage());
    }
}

---

From source file:com.yyl.common.utils.excel.ExcelTools.java

private static List<List<Object>> readXLSWithHeader(InputStream inputStream) throws IOException {
    //        InputStream is = new FileInputStream(file);
    HSSFWorkbook wb = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = wb.getSheetAt(0);

    List<List<Object>> o = new ArrayList();
    List<Object> temp = null;
    if (sheet == null) {
        return null;
    }/*from   w w w  .j  a v  a2  s  . c o m*/

    for (int row_index = 0; row_index <= sheet.getLastRowNum(); row_index++) {
        HSSFRow row = sheet.getRow(row_index);
        if (row == null) {
            continue;
        }
        temp = new ArrayList();
        for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) {
            temp.add(getCellValue(row.getCell(col_index)));
        }
        o.add(temp);
    }

    return o;
}

---

From source file:com.yyl.common.utils.excel.ExcelTools.java

private static List<List<Object>> readXLS(InputStream inputStream) throws IOException {
    //        InputStream is = new FileInputStream(file);
    HSSFWorkbook wb = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = wb.getSheetAt(0);

    List<List<Object>> o = new ArrayList();
    List<Object> temp = null;
    if (sheet == null) {
        return null;
    }//from   w  w  w.ja va  2 s  .c  om

    for (int row_index = 1; row_index <= sheet.getLastRowNum(); row_index++) {
        HSSFRow row = sheet.getRow(row_index);
        if (row == null) {
            continue;
        }
        temp = new ArrayList();
        for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) {
            temp.add(getCellValue(row.getCell(col_index)));
        }
        o.add(temp);
    }

    return o;
}

---

From source file:com.yyl.common.utils.excel.ExcelTools.java

private static List<Map<String, String>> readXLS(InputStream inputStream, Map<String, Integer> keyMaps)
        throws IOException {
    //        InputStream is = new FileInputStream(file);
    HSSFWorkbook wb = new HSSFWorkbook(inputStream);

    List<Map<String, String>> list = new ArrayList();
    Map<String, String> temp = null;
    for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
        HSSFSheet sheet = wb.getSheetAt(sheetIndex);
        if (sheet == null) {
            continue;
        }//from www.  ja va2s  .  com
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            HSSFRow row = sheet.getRow(rowIndex);
            temp = new HashMap();
            if (row != null) {
                for (Entry<String, Integer> entry : keyMaps.entrySet()) {
                    int index = entry.getValue();
                    HSSFCell cell = row.getCell(index);
                    temp.put(entry.getKey(), getCellValue(cell));
                }
            }
            list.add(temp);
        }
    }
    return list;
}

---

From source file:com.zhu.action.CarAction.java

public void exportpeople(ActionMapping mapping, ActionForm form, HttpServletRequest request,
        HttpServletResponse response) {/*w w w.  jav  a2s  . c om*/
    CarForm carForm = (CarForm) form;

    // webbookExcel
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet
    HSSFSheet sheet = wb.createSheet("?");
    // sheet0,??poiExcel?short
    HSSFRow row = sheet.createRow((int) 0);
    // ? 
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?
    HSSFCell cell = row.createCell(0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(1);
    cell.setCellValue("???");
    cell.setCellStyle(style);
    // ? ??
    OrderService orderService = new OrderService();
    List<Orderinfo> list = orderService.getOrderCarDetail(carForm.getId());
    System.out.println(list.size());
    for (int i = 0; i < list.size(); i++) {
        row = sheet.createRow(i + 1);
        Orderinfo order = (Orderinfo) list.get(i);
        // ?
        row.createCell(0).setCellValue(order.getN1());
        row.createCell(1).setCellValue(order.getI1());
        row = sheet.createRow(i + list.size() + 1);
        row.createCell(0).setCellValue(order.getN2());
        row.createCell(1).setCellValue(order.getI2());
        row = sheet.createRow(i + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN3());
        row.createCell(1).setCellValue(order.getI3());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN4());
        row.createCell(1).setCellValue(order.getI4());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN5());
        row.createCell(1).setCellValue(order.getI5());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN6());
        row.createCell(1).setCellValue(order.getI6());
        row = sheet.createRow(
                i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN7());
        row.createCell(1).setCellValue(order.getI7());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size()
                + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN8());
        row.createCell(1).setCellValue(order.getI8());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size()
                + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN9());
        row.createCell(1).setCellValue(order.getI9());
        row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size()
                + list.size() + list.size() + list.size() + list.size() + 1);
        row.createCell(0).setCellValue(order.getN10());
        row.createCell(1).setCellValue(order.getI10());
    }
    // ?
    String filePath = "";
    Date dt = new Date();
    DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
    String date = df.format(dt).toString();
    filePath = "/Users/Nemo/Documents/carpeople" + date + ".xls";
    File file = new File(filePath);

    try {
        // FileOutputStream fout = new FileOutputStream(
        // "/Users/Nemo/Documents/carpeople.xls");
        // wb.write(fout);
        // fout.close();
        OutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
    int key = 0;
    int MaxRowNum = 0, MaxCellNum = 0;
    try {
        FileInputStream in = new FileInputStream(filePath);
        POIFSFileSystem fs = new POIFSFileSystem(in);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        FileOutputStream out = new FileOutputStream("/Users/Nemo/Documents/carpeople" + date + ".xls");
        int number = workbook.getNumberOfSheets();
        for (int i = 0; i < number; i++) { // ?sheet
            sheet = workbook.getSheetAt(i); // 14
            System.out.println("" + sheet.getSheetName() + " ? "
                    + (sheet.getLastRowNum() + 1));
            MaxRowNum = 0;
            for (int k = 0; k <= sheet.getLastRowNum(); k++) {
                HSSFRow hRow = sheet.getRow(k);
                // System.out.println((k + 1) + "");
                if (isBlankRow(hRow)) // 
                {
                    int m = 0;
                    for (m = k + 1; m <= sheet.getLastRowNum(); m++) {
                        HSSFRow nhRow = sheet.getRow(m);
                        if (!isBlankRow(nhRow)) {
                            // System.out.println("?" + (m + 1));
                            sheet.shiftRows(m, sheet.getLastRowNum(), k - m);
                            break;
                        }
                    }
                    if (m > sheet.getLastRowNum())
                        break; // ?
                } else { // ?
                    MaxRowNum++;
                    if (MaxCellNum < hRow.getLastCellNum())
                        MaxCellNum = hRow.getLastCellNum();
                }
            }
            workbook.setPrintArea(i, 0, MaxCellNum, 0, MaxRowNum);
            System.out
                    .println("?? " + sheet.getSheetName() + "  " + MaxRowNum);
        }
        workbook.write(out);
        in.close();
        out.close();
    } catch (IOException e) {
        System.out.println(key + " " + e.getMessage() + " ");
        e.printStackTrace();

    }

    System.out.println("??");

}

---

From source file:com.zrx.authority.util.ObjectExcelView.java

@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    // TODO Auto-generated method stub
    Date date = new Date();
    String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
    HSSFSheet sheet;
    HSSFCell cell;//from   w  ww  . j a v a  2  s  .  c o m
    response.setContentType("application/octet-stream");
    response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
    sheet = workbook.createSheet("sheet1");

    List<String> titles = (List<String>) model.get("titles");
    int len = titles.size();
    HSSFCellStyle headerStyle = workbook.createCellStyle(); //?
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    HSSFFont headerFont = workbook.createFont(); //
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerFont.setFontHeightInPoints((short) 11);
    headerStyle.setFont(headerFont);
    short width = 20, height = 25 * 20;
    sheet.setDefaultColumnWidth(width);
    for (int i = 0; i < len; i++) { //
        String title = titles.get(i);
        cell = getCell(sheet, 0, i);
        cell.setCellStyle(headerStyle);
        setText(cell, title);
    }
    sheet.getRow(0).setHeight(height);

    HSSFCellStyle contentStyle = workbook.createCellStyle(); //?
    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    List<PageData> varList = (List<PageData>) model.get("varList");
    int varCount = varList.size();
    for (int i = 0; i < varCount; i++) {
        PageData vpd = varList.get(i);
        for (int j = 0; j < len; j++) {
            String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : "";
            cell = getCell(sheet, i + 1, j);
            cell.setCellStyle(contentStyle);
            setText(cell, varstr);
        }

    }

}

---

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