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: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  . ja  va 2 s. c om*/
            }
        }
    }
}

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  ww 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");
    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/*  w  ww.  j a  v a 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 . j  av a 2 s. 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-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//from   w  w w . ja  v  a  2s.  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 void readExcel() {
    try {//  w w w.  j  av  a2s.  c o m
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("database/customer.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns (max)
        int tmp = 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) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols)
                    cols = tmp;
            }
        }

        //            for(int r = 0; r < rows; r++) {
        //                row = sheet.getRow(r);
        //                if(row != null) {
        //                    for(int c = 0; c < cols; c++) {
        //                        cell = row.getCell((short)c);
        //                        if(cell != null) {
        //                            // Your code here
        //                        }
        //                    }
        //                }
        //            }

        row = sheet.getRow(8);
        if (row != null) {
            cell = row.getCell(1);
            if (cell != null)
                System.out.println("Row: " + 9 + ", Data: " + cell.getStringCellValue());
        }

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

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

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

    try {//w  w  w .  j a  va  2  s. c  o  m
        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;//from  w w 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;
}

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

License:Open Source License

@SuppressWarnings("unchecked")
public String importFile(List<File> fileList, List<String> fileNameList, Operator operator) throws Exception {
    HSSFWorkbook workbook = null;// ww w . ja  v  a 2s .  co m
    HSSFSheet sheet = null;
    HSSFRow row = null;
    // ?
    String returnMsg = "";
    // ??
    int fileNameIndex = 0;
    // ??
    String fileName = null;
    String sql = null;
    // 
    List<Object[]> dataList = null;

    // ??
    String saMerNo = null;
    // ??
    String saMerChName = null;
    // ??
    String saMerEnName = null;
    // ?
    String saZoneNo = null;
    // ??
    //      String saLimitAmt = null;
    // ?
    //      String saAction = null;
    // 
    String saBrhId = operator.getOprBrhId();
    // ?
    String saOprId = operator.getOprId();
    // 
    String saInitTime = CommonFunction.getCurrentDateTime();

    TblCtlMchtInf tblCtlMchtInf = 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;

            saMerNo = row.getCell(0).getStringCellValue();
            // ?
            if (saMerNo.getBytes().length > 15)
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "???<br>";
            sql = "select  mcht_nm,eng_name,bank_no from TBL_MCHT_BASE_INF where MCHT_NO = '" + saMerNo + "'";

            dataList = CommonFunction.getCommQueryDAO().findBySQLQuery(sql);
            if (dataList.size() == 0)
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "??<br>";
            // ??
            saMerChName = dataList.get(0)[0].toString();
            // ??
            saMerEnName = dataList.get(0)[1].toString();
            saZoneNo = dataList.get(0)[2].toString();

            //            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>";*/

            // ??????
            tblCtlMchtInf = new TblCtlMchtInf();
            tblCtlMchtInf.setId(saMerNo);
            tblCtlMchtInf.setSaMerChName(saMerChName);
            tblCtlMchtInf.setSaMerEnName(saMerEnName);
            tblCtlMchtInf.setSaZoneNo(saZoneNo);
            //            tblCtlMchtInf.setSaLimitAmt(saLimitAmt);
            //            tblCtlMchtInf.setSaAction(saAction);
            tblCtlMchtInf.setSaInitZoneNo(saBrhId);
            tblCtlMchtInf.setSaInitOprId(saOprId);
            tblCtlMchtInf.setSaInitTime(saInitTime);
            tblCtlMchtInfDAO.saveOrUpdate(tblCtlMchtInf);
        }
        fileInputStream.close();
        fileNameIndex++;
    }
    return Constants.SUCCESS_CODE;
}

From source file:com.ibm.asset.trails.service.impl.CauseCodeServiceImpl.java

@SuppressWarnings("unchecked")
private boolean validateExcelCauseCodeContent(HSSFSheet sheet, HSSFCellStyle errorStyle, List<State> steps) {

    State state = State.findStateByLable(steps, STEP2_LABEL);
    if (state == null) {
        state = new State();
        state.setDescription("Data validation");
        state.setLabel(STEP2_LABEL);//from  w  w w.  ja  va 2 s .  co  m
        state.setStatus(EStatus.IN_PROGRESS);
        steps.add(state);
    }

    boolean error = false;
    if (colIndexes == null) {
        int lastCellNO = sheet.getRow(ROW_ALERT_TYPE).getLastCellNum();
        HSSFCell cell = sheet.getRow(ROW_ALERT_TYPE).createCell(lastCellNO + 1);
        cell.setCellStyle(errorStyle);
        cell.setCellValue(new HSSFRichTextString(ERROR_UNKONW_TYPE));
        error = true;
    } else {
        Iterator<Row> rowIter = sheet.rowIterator();
        int rowCounter = -1;
        int totalRows = sheet.getLastRowNum();
        int colStart = colIndexes.getColCauseCode();
        int colEnd = colIndexes.getColInternalId();

        while (rowIter.hasNext()) {
            HSSFRow row = (HSSFRow) rowIter.next();
            rowCounter++;

            int progress = (int) ((float) rowCounter / totalRows * 100);
            state.setProgress(progress);

            if (rowCounter <= ROW_TABLE_HEAD) {
                continue;
            }

            StringBuffer errorMsg = new StringBuffer();
            for (int col = colStart; col <= colEnd; col++) {
                HSSFCell cell = row.getCell(col);

                if (col == colIndexes.getColInternalId()) {
                    if (!isCauseCodeExists(cell)) {

                        buildErrorMsg(errorMsg, colIndexes.getColInternalId(), "Internal ID",
                                ERROR_INTERNAL_ID_NOT_EXIST);
                    } else {
                        Long alertTypeId = getAlertTypeId(cell);
                        if (alertTypeId != null
                                && alertTypeId != this.getAlertTypeIdByCode(colIndexes.getAlertTypeCode())) {
                            buildErrorMsg(errorMsg, colIndexes.getColInternalId(), "Internal ID",
                                    ERROR_ALERT_TYPE_NOT_MATCH);
                        }
                    }

                }

                if (col == colIndexes.getColCauseCode()) {
                    if (cell == null) {
                        buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)",
                                ERROR_UNKONW_CAUSE_CODE);
                        continue;
                    }
                    HSSFCell causeCodeIdCell = row.getCell(colIndexes.getColInternalId());
                    if (!isCauseCodeExists(causeCodeIdCell)) {
                        buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)",
                                ERROR_UNKONW_CAUSE_CODE);
                        continue;
                    }

                    boolean pass = true;
                    // if no change continue;
                    String alertCauseNameInCell = null;
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        alertCauseNameInCell = cell.getStringCellValue();
                    } else {
                        pass = false;
                    }

                    if (alertCauseNameInCell == null || "".equals(alertCauseNameInCell.trim())) {
                        pass = false;
                    }

                    if (!pass) {
                        buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)",
                                ERROR_UNKONW_CAUSE_CODE);
                        continue;
                    }

                    if (alertCauseNameInCell.length() > 128) {
                        alertCauseNameInCell = alertCauseNameInCell.substring(0, 128);
                    }

                    String alertCauseNameInDb = getAlertCauseName(causeCodeIdCell);
                    // compare the cc name and cause code name under id. if
                    // not same check it's availability. if same ignore.
                    if (!strCompare(alertCauseNameInDb, alertCauseNameInCell)) {

                        List<AlertCause> acList = null;
                        try {
                            acList = getEntityManager().createNamedQuery("findActiveAlertCauseByNameAndTypeId")
                                    .setParameter("alertCauseName", alertCauseNameInCell.trim().toUpperCase())
                                    .setParameter("alertTypeId",
                                            this.getAlertTypeIdByCode(colIndexes.getAlertTypeCode()))
                                    .getResultList();
                            if (acList.size() <= 0) {
                                buildErrorMsg(errorMsg, colIndexes.getColCauseCode(), "Cause Code (CC)",
                                        ERROR_UNKONW_CAUSE_CODE);
                            }
                        } catch (Exception e) {
                            log.error(e.getMessage(), e);
                        }
                    }

                }

                if (col == colIndexes.getColTargetDate()) {
                    if (!isDateFormat(cell)) {//CC Target Date is an optional field
                        buildErrorMsg(errorMsg, colIndexes.getColTargetDate(), "CC target date",
                                ERROR_BAD_DATE_FORMAT);
                    }
                }

                if (col == colIndexes.getColOwner()) {
                    if (!isOwnerExistsInBluePage(cell)) {//CC Owner is an optional field
                        buildErrorMsg(errorMsg, colIndexes.getColOwner(), "CC owner", ERROR_UNKNOW_OWNER);
                    }
                }
            }

            if (errorMsg.length() > 0) {
                HSSFCell msgCell = row.createCell(colIndexes.getColMessage());
                msgCell.setCellStyle(errorStyle);
                msgCell.setCellValue(new HSSFRichTextString(errorMsg.toString()));
                error = true;
            }
        }
    }

    if (error) {
        state.setStatus(EStatus.FAILED);
    } else {
        if (state.getProgress() == 100 && state.getStatus().getPriority() < EStatus.FINISHED.getPriority()) {
            state.setStatus(EStatus.FINISHED);
        }
    }
    return error;
}