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

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getLastRowNum

Introduction

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

Prototype

@Override
public int getLastRowNum()

Source Link

Document

Gets the number last row on the sheet.

Usage

From source file:com.googlecode.bdoc.testsupport.excel.ExcelExampleTables.java

License:Open Source License

public ExcelExampleTable getTable(String tableDescription) {

    HSSFSheet sheet = workbook.getSheetAt(0);
    int rowIndex = -1;
    int cellnum = -1;
    HSSFCell cell = null;/*from   ww w  . j  a  v  a 2s.co  m*/
    try {
        for (rowIndex = 0; rowIndex < sheet.getLastRowNum(); rowIndex++) {
            HSSFRow row = sheet.getRow(rowIndex);
            if (null == row) {
                continue;
            }
            for (cellnum = 0; cellnum < row.getLastCellNum(); cellnum++) {
                cell = row.getCell(cellnum);
                if ((HSSFCell.CELL_TYPE_STRING == cell.getCellType())
                        && tableDescription.equals(cell.getRichStringCellValue().getString())) {
                    return new ExcelExampleTable(sheet, rowIndex, cellnum);
                }
            }
        }
    } catch (Exception e) {
        throw new IllegalStateException("Problem with cell (row=" + rowIndex + ",cellnum=" + cellnum + ",val="
                + cell + ") getting table [" + tableDescription + "] from [" + xlsFilePath + "]", e);

    }
    throw new IllegalArgumentException("Can't find [" + tableDescription + "] in [" + xlsFilePath + "]");
}

From source file:com.haulmont.mp2xls.writer.LocalizationBatchExcelWriter.java

License:Apache License

public static void exportToXls(LocalizationsBatch localizations, String outputXls) throws IOException {
    FileOutputStream fileOut = new FileOutputStream(outputXls);
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet worksheet = workbook.createSheet("localizations");

    HSSFCellStyle systemStyle = workbook.createCellStyle();
    systemStyle.setFillForegroundColor(HSSFColor.RED.index);
    systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFRow row = worksheet.createRow(0);
    row.createCell(0).setCellValue("Project path");
    row.createCell(1).setCellValue(localizations.getProjectDirectory());

    HSSFRow headLine = worksheet.createRow(5);
    headLine.createCell(0).setCellValue("Path to File");
    headLine.createCell(1).setCellValue("Parameter Name");

    Map<String, Integer> localeColumn = new HashMap<>();
    int colCount = 1;
    for (String localeId : localizations.getLocalizationIds()) {
        if (localizations.getScanLocalizationIds().contains(localeId == null ? "en" : localeId)) {
            String id = localeId == null ? "default" : localeId;
            headLine.createCell(++colCount).setCellValue(id);
            localeColumn.put(localeId, colCount);
        }/*w  w  w  .j av  a 2s.  c o m*/
    }

    Integer currentRow = headLine.getRowNum();
    for (String folder : localizations.getMessagesLocalizations().keySet()) {
        Set<MessagesLocalization> locales = localizations.getMessagesLocalizations().get(folder);

        Set<String> parameters = new HashSet<>();
        for (MessagesLocalization locale : locales) {
            if (localizations.getScanLocalizationIds()
                    .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId()))
                parameters.addAll(locale.getMessages().keySet());
        }

        for (String parameter : parameters) {
            row = worksheet.createRow(++currentRow);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(folder);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
                row.setZeroHeight(true);
            }

            cell = row.createCell(1);
            cell.setCellValue(parameter);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
            }

            for (MessagesLocalization locale : locales) {
                if (localizations.getScanLocalizationIds()
                        .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId())) {
                    Integer columnNum = localeColumn.get(locale.getLocaleId());
                    cell = row.createCell(columnNum);
                    cell.setCellValue(locale.getMessages().get(parameter));
                }
            }

        }
    }

    worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, colCount));
    worksheet.createFreezePane(0, headLine.getRowNum() + 1);
    /*
            for (int i = 0; i < colCount; i++){
    worksheet.autoSizeColumn(i);
    worksheet.setColumnWidth(i, worksheet.getColumnWidth(i) + 100);
            }
    */
    workbook.write(fileOut);
    fileOut.flush();
    fileOut.close();
}

From source file:com.haulmont.mp2xls.writer.LocalizationLogExcelWriter.java

License:Apache License

public static void exportToXls(List<LocalizationLog> differences, String outputXls) throws IOException {
    FileOutputStream fileOut = new FileOutputStream(outputXls);
    HSSFWorkbook workbook = new HSSFWorkbook();
    try {/*  w  w  w.  j a  va  2  s  . c  o  m*/
        HSSFSheet worksheet = workbook.createSheet("localizations");

        HSSFCellStyle systemStyle = workbook.createCellStyle();
        systemStyle.setFillForegroundColor(HSSFColor.RED.index);
        systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        Integer currentRow = 0;
        HSSFRow headLine = worksheet.createRow(++currentRow);
        headLine.createCell(0).setCellValue("File");
        headLine.createCell(1).setCellValue("Property");
        headLine.createCell(2).setCellValue("Source Value");
        headLine.createCell(3).setCellValue("Excel Value");

        HSSFRow row;

        for (LocalizationLog.Type type : LocalizationLog.Type.values()) {
            List<LocalizationLog> logs = getLogsByType(differences, type);
            if (logs.size() > 0) {
                row = worksheet.createRow(++currentRow);
                HSSFCell cell = row.createCell(0);
                cell.setCellValue(LogHelper.getMessageByType(type));
                cell.setCellStyle(LogHelper.getStyleByType(workbook, type));
                for (int i = 1; i < 4; i++) {
                    row.createCell(i);
                }

                CellRangeAddress region = new CellRangeAddress(currentRow, currentRow, 0, 3);
                worksheet.addMergedRegion(region);

                for (LocalizationLog log : logs) {
                    createNewLogRow(worksheet, ++currentRow, log);
                }

                row = worksheet.createRow(++currentRow);
                for (int i = 0; i < 4; i++) {
                    row.createCell(i);
                }

                region = new CellRangeAddress(currentRow, currentRow, 0, 3);
                worksheet.addMergedRegion(region);
            }
        }

        worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, 3));
        worksheet.createFreezePane(0, headLine.getRowNum() + 1);

        for (int i = 0; i < worksheet.getLastRowNum(); i++) {
            worksheet.autoSizeColumn(i);
        }
    } finally {
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

protected void cleanupCells(HSSFSheet resultSheet) {
    for (int i = resultSheet.getFirstRowNum(); i <= resultSheet.getLastRowNum(); i++) {
        HSSFRow row = resultSheet.getRow(i);
        if (row != null) {
            for (int j = 0; j < row.getLastCellNum(); j++) {
                HSSFCell cell = row.getCell(j);
                if (cell != null) {
                    row.removeCell(cell);
                }//from  w  w  w .j  a v  a  2  s .  co m
            }
        }
    }
}

From source file:com.hp.action.CustomerAction.java

public String addCustomerFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize//from w  w w . j  av a  2s  .co m
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    int total = 0;
    int totalFail = 0;
    String reason = "";
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the customer id null
                if (row.getCell(1) == null || row.getCell(1).getStringCellValue().compareTo("") == 0

                ) {
                    continue;
                }

                //Init Customer Object
                Customer customer = new Customer();
                //                    customer.setmStt((int)row.getCell(tmp++).getNumericCellValue());

                //System.out.println(row.getCell(1).getStringCellValue());

                if (row.getCell(1) != null && !row.getCell(1).getStringCellValue().equals("")) {
                    Customer customer2 = customerDAO.loadCustomer(row.getCell(1).getStringCellValue());
                    if (customer2 != null) {
                        reason += "Khch hng <b>" + row.getCell(1).getStringCellValue()
                                + "</b>  tn ti. ";
                    }
                } else
                    reason += "M Khch hng hng " + (i + 1) + " khng c trng. ";

                if (row.getCell(9) != null && !row.getCell(9).getStringCellValue().equals("")) {
                    Staff staff = staffDAO.loadStaff(row.getCell(9).getStringCellValue());
                    if (staff == null)
                        reason += "M Nhn vin <b>" + row.getCell(9).getStringCellValue()
                                + "</b> khng tn ti. ";
                } else
                    reason += "M Nhn vin hng " + (i + 1) + " khng c trng.";

                try {
                    if (row.getCell(1) != null)
                        customer.setMaDoiTuong(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) != null)
                        customer.setDoiTuong(row.getCell(2).getStringCellValue());
                    if (row.getCell(3) != null)
                        customer.setTinhThanh(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        customer.setX(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        customer.setDiaChi(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null)
                        customer.setTuyenBanHangThu(row.getCell(6).getStringCellValue());

                    if (row.getCell(7) != null)
                        customer.setDienThoai(row.getCell(7).getStringCellValue());
                    //System.out.println(row.getCell(7).getStringCellValue());

                    if (row.getCell(8) != null)
                        customer.setFax(row.getCell(8).getStringCellValue());
                    if (row.getCell(9) != null)
                        customer.setMaNhanVien(row.getCell(9).getStringCellValue());

                    if (row.getCell(10) != null)
                        customer.setGhiChu(row.getCell(10).getStringCellValue());

                    if (row.getCell(11) != null) {
                        if (row.getCell(11).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            customer.setCoordinateX(row.getCell(11).getNumericCellValue());
                        else
                            customer.setCoordinateX(Double.parseDouble(row.getCell(12).getStringCellValue()));
                    }

                    if (row.getCell(12) != null) {
                        if (row.getCell(12).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            customer.setCoordinateY(row.getCell(12).getNumericCellValue());
                        else
                            customer.setCoordinateY(Double.parseDouble(row.getCell(12).getStringCellValue()));
                    }

                    //Add to database
                    if (customerDAO.saveOrUpdate(customer)) {
                        System.out.println("Add Object " + i);
                        total++;
                        customersTotal = total;
                    } else {
                        totalFail++;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    totalFail++;
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }

    reasonFail = reason;
    customersTotal = total;
    importFail = totalFail;

    return SUCCESS;
}

From source file:com.hp.action.ProductsAction.java

public String addProductFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize//ww  w .ja va  2s.c  o  m
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-product");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the product id null
                if (row.getCell(1) == null ||

                        (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getStringCellValue().compareTo("") == 0)
                        || (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getNumericCellValue() <= 0)

                ) {
                    continue;
                }

                //Init Product Object
                Product product = new Product();
                //                  
                try {

                    if (row.getCell(1) != null) {
                        if (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setProductID(
                                    (new BigDecimal(row.getCell(1).getNumericCellValue())).toString());
                        else
                            product.setProductID(row.getCell(1).getStringCellValue());
                    }
                    if (row.getCell(2) != null) {
                        if (row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setBarcode(
                                    (new BigDecimal(row.getCell(2).getNumericCellValue())).toString());
                        else
                            product.setBarcode(row.getCell(2).getStringCellValue());
                    }

                    if (row.getCell(3) != null)
                        product.setProductName(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        product.setBrand(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        product.setOrigin(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null) {
                        if (row.getCell(6).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setPackingSpecifications(row.getCell(6).getNumericCellValue() + "");
                        else
                            product.setPackingSpecifications(row.getCell(6).getStringCellValue());

                    }
                    if (row.getCell(7) != null) {

                        product.setQuantification(row.getCell(7).getStringCellValue());

                    }

                    if (row.getCell(8) != null) {
                        if (row.getCell(8).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setVatTax((float) row.getCell(8).getNumericCellValue());
                        else
                            product.setVatTax(Float.parseFloat(row.getCell(8).getStringCellValue()));

                    }

                    if (row.getCell(9) != null) {
                        if (row.getCell(9).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setImportPrices((float) row.getCell(9).getNumericCellValue());
                        else
                            product.setImportPrices(Float.parseFloat(row.getCell(9).getStringCellValue()));

                    }

                    if (row.getCell(10) != null) {
                        if (row.getCell(10).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            product.setExportPrices((float) row.getCell(10).getNumericCellValue());
                        else
                            product.setExportPrices(Float.parseFloat(row.getCell(10).getStringCellValue()));

                    }
                    if (row.getCell(11) != null)
                        product.setProvider(row.getCell(11).getStringCellValue());

                    if (row.getCell(12) != null)
                        product.setDescription(row.getCell(12).getStringCellValue());
                    if (row.getCell(13) != null)
                        product.setProductImage(row.getCell(13).getStringCellValue());

                    //product.setMProvider("nhacungcap1");
                    //Add to database
                    if (productDAO.saveOrUpdate(product)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        productsTotal = total;
                    } else {
                        int t = 0;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }
    productsTotal = total;
    return SUCCESS;
}

From source file:com.hp.action.ProviderAction.java

public String addProviderFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize//from   w  w w  . ja  v a  2  s . c  o  m
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-provider");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the product id null
                if (row.getCell(1) == null ||

                        (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getStringCellValue().compareTo("") == 0)
                        || (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING
                                && row.getCell(1).getNumericCellValue() <= 0)

                ) {
                    continue;
                }

                //Init Product Object
                Provider provider = new Provider();
                //                  
                try {

                    if (row.getCell(1) != null) {
                        if (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setId((new BigDecimal(row.getCell(1).getNumericCellValue())).toString());
                        else
                            provider.setId(row.getCell(1).getStringCellValue());
                    }
                    if (row.getCell(2) != null) {
                        if (row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setName((new BigDecimal(row.getCell(2).getNumericCellValue())).toString());
                        else
                            provider.setName(row.getCell(2).getStringCellValue());
                    }

                    if (row.getCell(3) != null)
                        provider.setAddress(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        provider.setPhoneNumber(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        provider.setFax(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null) {
                        if (row.getCell(6).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            provider.setNote(row.getCell(6).getNumericCellValue() + "");
                        else
                            provider.setNote(row.getCell(6).getStringCellValue());

                    }

                    //Add to database
                    if (providerDAO.saveOrUpdate(provider)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        providersTotal = total;
                    } else {
                        int t = 0;
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }
    providersTotal = total;
    return SUCCESS;
}

From source file:com.hp.action.StaffsAction.java

public String addStaffFromExcelFile() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize//  w  ww.  j  a v  a 2 s .  c om
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    String saveName = (String) session.getAttribute("upload-name-file-staff");
    System.out.println("Get Attribute file name: " + saveName);
    if (saveName == null)
        return SUCCESS;

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    int total = 0;
    //Import data
    try {
        String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/");
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 1; i < rows; i++) {

            row = sheet.getRow(i);
            System.out.println("__ Rows: " + (i + 1));
            if (row != null) {

                System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells());
                //If the staff id null
                if (row.getCell(1) == null || row.getCell(1).getStringCellValue().compareTo("") == 0

                ) {
                    continue;
                }

                //Init Staff Object                   
                Staff staff = new Staff();
                try {

                    if (row.getCell(1) != null)
                        staff.setId(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) != null)
                        staff.setPw(row.getCell(2).getStringCellValue());
                    if (row.getCell(3) != null)
                        staff.setName(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) != null)
                        staff.setAdress(row.getCell(4).getStringCellValue());
                    if (row.getCell(5) != null)
                        staff.setJob(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) != null)
                        staff.setPhone(row.getCell(6).getStringCellValue());

                    if (row.getCell(7) != null && row.getCell(7).getStringCellValue().compareTo("") != 0) {

                        staff.setDate(df.parse(row.getCell(7).getStringCellValue()));

                    }
                    if (row.getCell(8) != null)
                        staff.setManager(row.getCell(8).getStringCellValue());
                    if (row.getCell(9) != null) {
                        if (row.getCell(9).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            staff.setStatus(row.getCell(9).getNumericCellValue() == 1 ? true : false);
                        else
                            staff.setStatus(
                                    row.getCell(9).getStringCellValue().compareTo("1") == 0 ? true : false);
                    }

                    if (row.getCell(10) != null) {
                        if (row.getCell(10).getCellType() != HSSFCell.CELL_TYPE_STRING)
                            staff.setPermission((int) row.getCell(10).getNumericCellValue());
                        else
                            staff.setPermission(Integer.parseInt(row.getCell(10).getStringCellValue()));
                    }

                    //Add to database
                    if (staffDAO.saveOrUpdate(staff)) {
                        System.out.println("Add Object " + (i + 1));
                        total++;
                        staffsTotal = total;
                    } else
                        continue;
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }

            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return SUCCESS;
    }

    staffsTotal = total;
    return SUCCESS;
}

From source file:com.hp.excelhandle.GetData.java

public ArrayList<Customer> loadCustomer() {
    ArrayList<Customer> listCustomer = new ArrayList();

    try {//from  ww  w  .  j a  v  a  2  s.com
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(mFileInput));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows();
        System.out.println("ROWs number" + rows);
        System.out.println("Cell value: " + sheet.getRow(rows - 1).getCell(0));

        int cols = 0; // No of columns (max)
        int temp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                temp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (temp > cols)
                    cols = temp;
            }
        }

        for (int i = 8; i < rows; i++) {

            row = sheet.getRow(i);
            if (row != null) {

                //If the customer id null
                if (row.getCell(ConfigFile.MA_DOI_TUONG_COL) == null
                        || row.getCell(ConfigFile.X_COORDINATES_COL) == null
                        || row.getCell(ConfigFile.Y_COORDINATES_COL) == null) {
                    continue;
                }

                //Init Customer Object
                Customer custumer = new Customer();
                custumer.setCoordinateX(row.getCell(ConfigFile.X_COORDINATES_COL).getNumericCellValue());
                custumer.setCoordinateY(row.getCell(ConfigFile.Y_COORDINATES_COL).getNumericCellValue());

                int tmp = 0;
                custumer.setStt((int) row.getCell(tmp++).getNumericCellValue());
                custumer.setTinhThanh(row.getCell(tmp++).getStringCellValue());
                custumer.setTuyenBanHangThu(row.getCell(tmp++).getStringCellValue());
                custumer.setMaNhanVien(row.getCell(tmp++).getStringCellValue());

                custumer.setX(row.getCell(tmp++).getStringCellValue());
                custumer.setMaDoiTuong(row.getCell(tmp++).getStringCellValue());
                custumer.setDoiTuong(row.getCell(tmp++).getStringCellValue());
                //                    custumer.setmNoDKy(row.getCell(tmp++).getNumericCellValue());
                //                    
                //                    custumer.setmCoDKy(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmNoTKy(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmTienBan(row.getCell(tmp++).getNumericCellValue());
                //                    
                //                    custumer.setmCoTKy(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmCKGG(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmNhapLai(row.getCell(tmp++).getNumericCellValue());
                //                    
                //                    custumer.setmNoCKy(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmCoCKy(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmDoanhThu(row.getCell(tmp++).getNumericCellValue());
                //                    
                //                    custumer.setmPhanTramNoChiaThu(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmNoToiDa(row.getCell(tmp++).getNumericCellValue());
                //                    custumer.setmDaiDien(row.getCell(tmp++).getStringCellValue());

                custumer.setDiaChi(row.getCell(tmp++).getStringCellValue());
                custumer.setDienThoai(row.getCell(tmp++).getStringCellValue());
                custumer.setFax(row.getCell(tmp++).getStringCellValue());

                custumer.setGhiChu(row.getCell(tmp++).getStringCellValue());

                listCustomer.add(custumer);
                System.out.println("Add Object " + i);
            }
        }

    } catch (Exception ioe) {
        ioe.printStackTrace();
        return null;
    }

    return listCustomer;
}

From source file:com.huateng.bo.impl.risk.T40201BOTarget.java

License:Open Source License

public String importFile(List<File> fileList, List<String> fileNameList, Operator operator) throws Exception {
    HSSFWorkbook workbook = null;//ww w .j ava  2s . c  om
    HSSFSheet sheet = null;
    HSSFRow row = null;
    // ?
    String returnMsg = "";
    // ??
    int fileNameIndex = 0;
    // ??
    String fileName = null;

    // ??
    String saCardNo = null;
    // ??
    //      String saLimitAmt = null;
    // ?
    //      String saAction = null;
    // 
    String saBrhId = operator.getOprBrhId();
    // ?
    String saOprId = operator.getOprId();
    // 
    String saInitTime = CommonFunction.getCurrentDateTime();

    TblCtlCardInf tblCtlCardInf = null;

    FileInputStream fileInputStream = null;

    for (File file : fileList) {

        fileInputStream = new FileInputStream(file);

        workbook = new HSSFWorkbook(fileInputStream);

        sheet = workbook.getSheetAt(0);

        fileName = fileNameList.get(fileNameIndex);

        for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {

            row = sheet.getRow(rowIndex);

            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
                if (row.getCell(i).getCellType() != HSSFCell.CELL_TYPE_STRING)
                    returnMsg += "[ " + fileName + " ]" + (row.getRowNum() + 1) + "" + ""
                            + (i + 1) + "???<br>";

            if (!"".equals(returnMsg))
                return returnMsg;

            saCardNo = row.getCell(0).getStringCellValue();
            // ??
            if (saCardNo.getBytes().length > 19)
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "???<br>";

            //            saLimitAmt = row.getCell(1).getStringCellValue();
            //            saLimitAmt = CommonFunction.transYuanToFen(saLimitAmt);

            // ??
            /*if(!CommonFunction.isAllDigit(saLimitAmt))
               return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "????<br>";
                    
            if(saLimitAmt.getBytes().length > 12) 
               return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/

            //            saAction = row.getCell(2).getStringCellValue();
            // ?
            /*if(!("1".equals(saAction) || "2".equals(saAction) || "3".equals(saAction) || "4".equals(saAction)))
               return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/

            // ???????
            tblCtlCardInf = new TblCtlCardInf();
            tblCtlCardInf.setId(saCardNo);
            //            tblCtlCardInf.setSaLimitAmt(saLimitAmt);
            //            tblCtlCardInf.setSaAction(saAction);
            tblCtlCardInf.setSaInitZoneNo(saBrhId);
            tblCtlCardInf.setSaInitOprId(saOprId);
            tblCtlCardInf.setSaInitTime(saInitTime);
            tblCtlCardInfDAO.saveOrUpdate(tblCtlCardInf);
        }
        fileInputStream.close();
        fileNameIndex++;
    }
    return Constants.SUCCESS_CODE;
}