Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getSheetAt

Introduction

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

Prototype

@Override
public HSSFSheet getSheetAt(int index)

Source Link

Document

Get the HSSFSheet object at the given index.

Usage

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

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

    try {//from  w ww . ja  v  a  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.hrr3.services.FileImportService.java

License:Apache License

public List<ImportSSRLRRData> parseFileToObjects(String fileName, Date startDate, Date endDate)
        throws ApplicationException, IOException {

    HSSFWorkbook wb = this.readFile(fileName);
    List<ImportSSRLRRData> rowDataList = null;

    //1. Validate SheetName
    if (wb.getNumberOfSheets() == 0 || !wb.getSheetAt(0).getSheetName().equals("LRR"))
        throw new ApplicationException("Excel file must contain a valid sheet called 'LRR'");

    //2. Validate row 3 (index 2) contains at least 14 columns
    if (wb.getSheetAt(0).getRow(2).getPhysicalNumberOfCells() < 14)
        throw new ApplicationException(
                "'LRR setup sheet must contains at least 14 columns at Row#3 to identify SSR-LRR template.");

    //Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations
    HSSFSheet lrrSheet = wb.getSheetAt(0);
    int totalRows = lrrSheet.getPhysicalNumberOfRows();
    int startIndex = 3;//DataIndex to start reading srr info
    int startDateIndex = -1; //To save row index for startdate
    int endDateIndex = -1;//To save row inxed for enddate
    int dateColumnIndex = 1;

    //3.Validate date range exists (ie. 1/1/2014 to 1/20/2014) and get start and end indexes containing the data
    for (int rowIndex = startIndex; rowIndex < totalRows; rowIndex++) {
        HSSFCell currentDateCell = lrrSheet.getRow(rowIndex).getCell(dateColumnIndex);

        //3.1 If row is not latest one, validate Date and Format
        if (rowIndex < totalRows) {
            if (currentDateCell == null || currentDateCell.getDateCellValue() == null)
                throw new ApplicationException("Column[Date] at row[" + (rowIndex + 1) + "] can't be empty.");
            else if (!HSSFDateUtil.isCellDateFormatted(currentDateCell))
                throw new ApplicationException(
                        "Column[Date] at row[" + (rowIndex + 1) + "] must be Date Format.");
        }/*from  ww w .  j a  v  a2s . c  o  m*/

        //3.2 Get Start and End indexes according to date range requested by user
        if (currentDateCell.getDateCellValue().compareTo(startDate) == 0)
            startDateIndex = rowIndex;

        if (currentDateCell.getDateCellValue().compareTo(endDate) == 0) {
            endDateIndex = rowIndex;
            break;
        }

    }

    System.out.println("******** RANGE [" + startDate + "-" + endDate + "] Found *************");
    System.out.println("******** StartDate INDEX: " + startDateIndex + " *************");
    System.out.println("******** EndDate INDEX: " + endDateIndex + " *************");

    //Create RowDataList to contain each row
    rowDataList = new ArrayList<ImportSSRLRRData>();

    //4. Now having start and end date, get the range of rows and fill out a list of ImportSSRData objects.      
    for (int i = startDateIndex; i <= endDateIndex; i++) {

        HSSFCell currentDateCell = lrrSheet.getRow(i).getCell(dateColumnIndex);
        Date statdateCol = currentDateCell.getDateCellValue();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

        String statdate = dateFormat.format(statdateCol);
        String lrr1 = getStringCellValue(lrrSheet.getRow(i).getCell(3));
        String lrr2 = getStringCellValue(lrrSheet.getRow(i).getCell(4));
        String lrr3 = getStringCellValue(lrrSheet.getRow(i).getCell(5));
        String lrr4 = getStringCellValue(lrrSheet.getRow(i).getCell(6));
        String lrr5 = getStringCellValue(lrrSheet.getRow(i).getCell(7));
        String lrr6 = getStringCellValue(lrrSheet.getRow(i).getCell(8));
        String lrr7 = getStringCellValue(lrrSheet.getRow(i).getCell(9));
        String lrr8 = getStringCellValue(lrrSheet.getRow(i).getCell(10));
        String lrr9 = getStringCellValue(lrrSheet.getRow(i).getCell(11));
        String hp1 = getStringCellValue(lrrSheet.getRow(i).getCell(12));
        String hp2 = getStringCellValue(lrrSheet.getRow(i).getCell(13));

        //Fill rowData object
        ImportSSRLRRData rowData = new ImportSSRLRRData();

        rowData.setStatdate(statdate);
        rowData.setLrr1(new BigDecimal(lrr1));
        rowData.setLrr2(new BigDecimal(lrr2));
        rowData.setLrr3(new BigDecimal(lrr3));
        rowData.setLrr4(new BigDecimal(lrr4));
        rowData.setLrr5(new BigDecimal(lrr5));
        rowData.setLrr6(new BigDecimal(lrr6));
        rowData.setLrr7(new BigDecimal(lrr7));
        rowData.setLrr8(new BigDecimal(lrr8));
        rowData.setLrr9(new BigDecimal(lrr9));
        rowData.setLrrHp1(new BigDecimal(hp1));
        rowData.setLrrHp2(new BigDecimal(hp2));
        //Add row to the list
        rowDataList.add(rowData);
        System.out.println(rowData);

    }

    System.out.println("******** Rows to return => " + rowDataList.size() + " ********");

    return rowDataList;
}

From source file:com.hrr3.services.LRRFileImportService.java

License:Apache License

public List<ImportSSRLRRData> parseFileToObjects(String fileName, Date startDate, Date endDate)
        throws ApplicationException, IOException {

    HSSFWorkbook wb = this.readFile(fileName);
    List<ImportSSRLRRData> rowDataList = null;

    //1. Validate SheetName
    if (wb.getNumberOfSheets() == 0 || !wb.getSheetAt(0).getSheetName().equals("LRR"))
        throw new ApplicationException("Excel file must contain a valid sheet called 'LRR'");

    //2. Validate row 3 (index 2) contains at least 14 columns
    if (wb.getSheetAt(0).getRow(2).getPhysicalNumberOfCells() < 14)
        throw new ApplicationException(
                "'LRR setup sheet must contains at least 14 columns at Row#3 to identify SSR-LRR template.");

    //Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations
    HSSFSheet lrrSheet = wb.getSheetAt(0);
    int totalRows = lrrSheet.getPhysicalNumberOfRows();
    int startIndex = 3;//DataIndex to start reading srr info
    int startDateIndex = -1; //To save row index for startdate
    int endDateIndex = -1;//To save row inxed for enddate
    int dateColumnIndex = 1;

    //3.Validate date range exists (ie. 1/1/2014 to 1/20/2014) and get start and end indexes containing the data
    for (int rowIndex = startIndex; rowIndex < totalRows; rowIndex++) {
        HSSFCell currentDateCell = lrrSheet.getRow(rowIndex).getCell(dateColumnIndex);

        //3.1 If row is not latest one, validate Date and Format
        if (rowIndex < totalRows) {
            if (currentDateCell == null || currentDateCell.getDateCellValue() == null)
                throw new ApplicationException("Column[Date] at row[" + (rowIndex + 1) + "] can't be empty.");
            else if (!HSSFDateUtil.isCellDateFormatted(currentDateCell))
                throw new ApplicationException(
                        "Column[Date] at row[" + (rowIndex + 1) + "] must be Date Format.");
        }/*  w w w  .  j av  a  2 s.  c o m*/

        //3.2 Get Start and End indexes according to date range requested by user
        if (currentDateCell.getDateCellValue().compareTo(startDate) == 0)
            startDateIndex = rowIndex;

        if (currentDateCell.getDateCellValue().compareTo(endDate) == 0) {
            endDateIndex = rowIndex;
            break;
        }

    }

    System.out.println("******** RANGE [" + startDate + "-" + endDate + "] Found *************");
    System.out.println("******** StartDate INDEX: " + startDateIndex + " *************");
    System.out.println("******** EndDate INDEX: " + endDateIndex + " *************");

    if (startDateIndex == -1 || endDateIndex == -1)
        throw new ApplicationException(
                "Sheet does not contain data for the date range. Please provide a valid one.");

    //Create RowDataList to contain each row
    rowDataList = new ArrayList<ImportSSRLRRData>();

    //4. Now having start and end date, get the range of rows and fill out a list of ImportSSRData objects.      
    for (int i = startDateIndex; i <= endDateIndex; i++) {

        HSSFCell currentDateCell = lrrSheet.getRow(i).getCell(dateColumnIndex);
        Date statdateCol = currentDateCell.getDateCellValue();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

        String statdate = dateFormat.format(statdateCol);
        String lrr1 = getStringCellValue(lrrSheet.getRow(i).getCell(3));
        String lrr2 = getStringCellValue(lrrSheet.getRow(i).getCell(4));
        String lrr3 = getStringCellValue(lrrSheet.getRow(i).getCell(5));
        String lrr4 = getStringCellValue(lrrSheet.getRow(i).getCell(6));
        String lrr5 = getStringCellValue(lrrSheet.getRow(i).getCell(7));
        String lrr6 = getStringCellValue(lrrSheet.getRow(i).getCell(8));
        String lrr7 = getStringCellValue(lrrSheet.getRow(i).getCell(9));
        String lrr8 = getStringCellValue(lrrSheet.getRow(i).getCell(10));
        String lrr9 = getStringCellValue(lrrSheet.getRow(i).getCell(11));
        String hp1 = getStringCellValue(lrrSheet.getRow(i).getCell(12));
        String hp2 = getStringCellValue(lrrSheet.getRow(i).getCell(13));

        //Fill rowData object
        ImportSSRLRRData rowData = new ImportSSRLRRData();

        rowData.setStatdate(statdate);
        rowData.setLrr1(new BigDecimal(lrr1));
        rowData.setLrr2(new BigDecimal(lrr2));
        rowData.setLrr3(new BigDecimal(lrr3));
        rowData.setLrr4(new BigDecimal(lrr4));
        rowData.setLrr5(new BigDecimal(lrr5));
        rowData.setLrr6(new BigDecimal(lrr6));
        rowData.setLrr7(new BigDecimal(lrr7));
        rowData.setLrr8(new BigDecimal(lrr8));
        rowData.setLrr9(new BigDecimal(lrr9));
        rowData.setLrrHp1(new BigDecimal(hp1));
        rowData.setLrrHp2(new BigDecimal(hp2));
        //Add row to the list
        rowDataList.add(rowData);
        System.out.println(rowData);

    }

    System.out.println("******** Rows to return => " + rowDataList.size() + " ********");

    return rowDataList;
}

From source file:com.hrr3.services.SSRFileImportService.java

License:Apache License

public List<ImportSSRData> parseFileToObjects(String fileName, Date startDate, Date endDate)
        throws ApplicationException, IOException {

    HSSFWorkbook wb = this.readFile(fileName);
    List<ImportSSRData> rowDataList = null;

    //1. Validate SheetName
    if (wb.getNumberOfSheets() == 0 || !wb.getSheetAt(0).getSheetName().equals("SellStrat"))
        throw new ApplicationException("Excel file must contain a valid sheet called 'SellStrat'");

    //2. Validate row 3 (index 2) contains at least 22 columns
    if (wb.getSheetAt(0).getRow(2).getPhysicalNumberOfCells() < 22)
        throw new ApplicationException(
                "'SellStrat sheet must contains at least 22 columns at Row#3 to identify SSR template.");

    //Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations
    HSSFSheet sellStrat = wb.getSheetAt(0);
    int totalRows = sellStrat.getPhysicalNumberOfRows();
    int startIndex = 3;//DataIndex to start reading srr info
    int startDateIndex = -1; //To save row index for startdate
    int endDateIndex = -1;//To save row inxed for enddate

    //3.Validate date range exists (ie. 1/1/2014 to 1/20/2014) and get start and end indexes containing the data
    for (int rowIndex = startIndex; rowIndex < totalRows; rowIndex++) {
        HSSFCell currentDateCell = sellStrat.getRow(rowIndex).getCell(2);

        //3.1 If row is not latest one, validate Date and Format
        if (rowIndex < totalRows) {
            if (currentDateCell == null || currentDateCell.getDateCellValue() == null)
                throw new ApplicationException("Column[Date] at row[" + (rowIndex + 1) + "] can't be empty.");
            else if (!HSSFDateUtil.isCellDateFormatted(currentDateCell))
                throw new ApplicationException(
                        "Column[Date] at row[" + (rowIndex + 1) + "] must be Date Format.");
        }//from www .  j  a  v  a 2  s  .com

        //3.2 Get Start and End indexes according to date range requested by user
        if (currentDateCell.getDateCellValue().compareTo(startDate) == 0)
            startDateIndex = rowIndex;

        if (currentDateCell.getDateCellValue().compareTo(endDate) == 0) {
            endDateIndex = rowIndex;
            break;
        }

    }

    System.out.println("******** RANGE [" + startDate + "-" + endDate + "] Found *************");
    System.out.println("******** StartDate INDEX: " + startDateIndex + " *************");
    System.out.println("******** EndDate INDEX: " + endDateIndex + " *************");

    if (startDateIndex == -1 || endDateIndex == -1)
        throw new ApplicationException(
                "Sheet does not contain data for the date range. Please provide a valid one.");

    //Create RowDataList to contain each row
    rowDataList = new ArrayList<ImportSSRData>();

    //4. Now having start and end date, get the range of rows and fill out a list of ImportSSRData objects.      
    for (int i = startDateIndex; i <= endDateIndex; i++) {

        HSSFCell currentDateCell = sellStrat.getRow(i).getCell(2);
        Date statdateCol = currentDateCell.getDateCellValue();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

        String comments = sellStrat.getRow(i).getCell(0) != null
                ? sellStrat.getRow(i).getCell(0).getStringCellValue()
                : "";
        String statdate = dateFormat.format(statdateCol);

        Calendar c = Calendar.getInstance();
        try {
            c.setTime(dateFormat.parse(statdate));
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        String[] strDays = new String[] { "Sunday", "Monday", "Tuesday", "Wednesday", "Thusday", "Friday",
                "Saturday" };

        String down = strDays[c.get(Calendar.DAY_OF_WEEK) - 1]; //sellStrat.getRow(i).getCell(1).getStringCellValue();

        String a1 = sellStrat.getRow(i).getCell(3).getStringCellValue();
        String b2 = sellStrat.getRow(i).getCell(4).getStringCellValue();
        String c3 = sellStrat.getRow(i).getCell(5).getStringCellValue();
        String d4 = sellStrat.getRow(i).getCell(6).getStringCellValue();
        String e5 = sellStrat.getRow(i).getCell(7).getStringCellValue();
        String f6 = sellStrat.getRow(i).getCell(8).getStringCellValue();
        String g7 = sellStrat.getRow(i).getCell(9).getStringCellValue();
        String h8 = sellStrat.getRow(i).getCell(10).getStringCellValue();
        String i9 = sellStrat.getRow(i).getCell(11).getStringCellValue();
        String hp1 = getStringCellValue(sellStrat.getRow(i).getCell(12));
        String hp2 = getStringCellValue(sellStrat.getRow(i).getCell(13));
        String ssrTransient = getStringCellValue(sellStrat.getRow(i).getCell(14));
        String ssrGrpblock = getStringCellValue(sellStrat.getRow(i).getCell(15));
        String ssrContract = getStringCellValue(sellStrat.getRow(i).getCell(16));
        String ssrGrppu = getStringCellValue(sellStrat.getRow(i).getCell(18));
        String ssrGrprem = getStringCellValue(sellStrat.getRow(i).getCell(19));
        String ssrDemandtd = getStringCellValue(sellStrat.getRow(i).getCell(20));
        String ssrPricetd = getStringCellValue(sellStrat.getRow(i).getCell(21));

        //Fill rowData object
        ImportSSRData rowData = new ImportSSRData();
        rowData.setComment(comments);
        rowData.setDow(down);
        rowData.setStatdate(statdate);
        rowData.setA1(a1);
        rowData.setB2(b2);
        rowData.setC3(c3);
        rowData.setD4(d4);
        rowData.setE5(e5);
        rowData.setF6(f6);
        rowData.setG7(g7);
        rowData.setH8(h8);
        rowData.setI9(i9);
        rowData.setHp1(hp1);
        rowData.setHp2(hp2);
        rowData.setSsrTransient(new BigDecimal(ssrTransient));
        rowData.setSsrGrpblock(new BigDecimal(ssrGrpblock));
        rowData.setSsrContract(new BigDecimal(ssrContract));
        rowData.setSsrGrppu(new BigDecimal(ssrGrppu));
        rowData.setSsrGrprem(new BigDecimal(ssrGrprem));
        rowData.setSsrDemandtd(new BigDecimal(ssrDemandtd));
        rowData.setSsrPricetd(new BigDecimal(ssrPricetd));
        //Add row to the list
        rowDataList.add(rowData);
        System.out.println(rowData);

    }

    System.out.println("******** Rows to return => " + rowDataList.size() + " ********");

    return rowDataList;
}

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;
    HSSFSheet sheet = null;/*from ww w.  j a  v a 2  s .c o m*/
    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;
    HSSFSheet sheet = null;//  ww w . j av  a2 s  . c o  m
    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

@Transactional(readOnly = true, propagation = Propagation.REQUIRES_NEW)
public ByteArrayOutputStream loadSpreadsheet(File file, String remoteUser, List<State> steps)
        throws IOException {

    ByteArrayOutputStream bos = null;

    FileInputStream fin = new FileInputStream(file);
    HSSFWorkbook wb = new HSSFWorkbook(fin);

    HSSFSheet sheet = wb.getSheetAt(0);

    HSSFCell reportNameCell = sheet.getRow(ROW_ALERT_TYPE).getCell(COL_ALERT_TYPE);
    String reportName = reportNameCell.getStringCellValue().trim();

    HSSFCellStyle errorStyle = wb.createCellStyle();
    errorStyle.setFillForegroundColor(HSSFColor.RED.index);
    errorStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    colIndexes = ECauseCodeReport.getReportByName(reportName);

    boolean error = validateExcelCauseCodeContent(sheet, errorStyle, steps);

    if (!error) {
        saveCauseCode(wb, remoteUser, steps);
    } else {//from w w  w .  ja  v a2s  .  c  om
        State state = State.findStateByLable(steps, STEP3_LABEL);
        if (state == null) {
            state = new State();
            state.setDescription("Persist changes");
            state.setLabel(STEP3_LABEL);
            state.setStatus(EStatus.IGNORED);
            steps.add(state);
        }
    }

    bos = new ByteArrayOutputStream();
    wb.write(bos);

    return bos;

}

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

private void saveCauseCode(HSSFWorkbook wb, String remoteUser, List<State> steps) {
    HSSFSheet sheet = wb.getSheetAt(0);
    Iterator<Row> rowIter = sheet.rowIterator();

    State state = State.findStateByLable(steps, STEP3_LABEL);
    if (state == null) {
        state = new State();
        state.setDescription("Persist changes");
        state.setLabel(STEP3_LABEL);//from  ww  w. j a v a2s .co  m
        state.setStatus(EStatus.IN_PROGRESS);
        steps.add(state);
    }

    int rowCounter = -1;
    int totalRows = sheet.getLastRowNum();
    while (rowIter.hasNext()) {
        HSSFRow row = (HSSFRow) rowIter.next();
        rowCounter++;

        int progress = (int) ((float) rowCounter / totalRows * 100);
        state.setProgress(progress);
        if (progress == 100) {
            state.setStatus(EStatus.FINISHED);
        }

        if (rowCounter <= ROW_TABLE_HEAD) {
            continue;
        }

        HSSFCell causeCodeIdCell = row.getCell(colIndexes.getColInternalId());

        long causeCodeId = -1;
        if (causeCodeIdCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            causeCodeId = Long.valueOf(causeCodeIdCell.getStringCellValue());
        } else if (causeCodeIdCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            causeCodeId = Math.round(causeCodeIdCell.getNumericCellValue());
        }

        CauseCode causeCode = (CauseCode) getEntityManager().createNamedQuery("getCauseCodeById")
                .setParameter("id", causeCodeId).getSingleResult();

        String causeCodeName = causeCode.getAlertCause().getName();

        HSSFCell causeCodeCell = row.getCell(colIndexes.getColCauseCode());
        String colCauseCode = null;
        if (causeCodeCell != null) {
            colCauseCode = causeCodeCell.getStringCellValue().trim();
        }

        Date targetDate = causeCode.getTargetDate();
        HSSFCell targetDateCell = row.getCell(colIndexes.getColTargetDate());
        Date colTargetDate = null;
        if (targetDateCell != null) {
            if (targetDateCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC
                    && HSSFDateUtil.isCellDateFormatted(targetDateCell)) {
                colTargetDate = targetDateCell.getDateCellValue();
            } else if (targetDateCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                colTargetDate = convertTextToDate(targetDateCell);//Convert Date Text to Date Object
            }
        }
        String owner = causeCode.getOwner();

        HSSFCell ownerCell = row.getCell(colIndexes.getColOwner());
        String colOwner = null;
        if (ownerCell != null) {
            colOwner = ownerCell.getStringCellValue().trim();
        }

        //Assignee Comments Function Start
        if (colIndexes.getColAssigneeComments() != -1) {
            HSSFCell assigneeCommentsCell = row.getCell(colIndexes.getColAssigneeComments());

            String assigneeComments = "";
            if (assigneeCommentsCell != null
                    && assigneeCommentsCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                assigneeComments = assigneeCommentsCell.getStringCellValue();
            }

            if (assigneeComments != null && !"".equals(assigneeComments.trim())) {
                updateAssigneeComments(causeCode.getAlertId(), assigneeComments.trim(),
                        colIndexes.getReportName().trim(), remoteUser);
            }
        }
        //Assignee Comments Function End 

        boolean changed = false;

        if (!strCompare(causeCodeName, colCauseCode) || !dateCompare(targetDate, colTargetDate)
                || !strCompare(owner, colOwner)) {
            changed = true;
        }

        if (!changed) {
            continue;
        }

        CauseCodeHistory history = new CauseCodeHistory();
        history.setCauseCode(causeCode);
        history.setAlertType(causeCode.getAlertType());
        history.setAlertId(causeCode.getAlertId());
        history.setAlertCause(causeCode.getAlertCause());
        history.setTargetDate(causeCode.getTargetDate());
        history.setOwner(causeCode.getOwner());
        history.setRecordTime(causeCode.getRecordTime());
        history.setRemoteUser(causeCode.getRemoteUser());

        if (!strCompare(causeCodeName, colCauseCode)) {
            try {
                AlertCause alertCause = null;

                if ("UNDEFINED".equals(colCauseCode.trim().toUpperCase())) {
                    alertCause = (AlertCause) getEntityManager()
                            .createNamedQuery("findAlertCauseByNameWithoutShowInGui")
                            .setParameter("name", colCauseCode.trim().toUpperCase()).getSingleResult();
                } else {
                    alertCause = (AlertCause) getEntityManager().createNamedQuery("findAlertCauseByName")
                            .setParameter("name", colCauseCode.trim().toUpperCase()).getSingleResult();
                }

                if (alertCause != null) {
                    causeCode.setAlertCause(alertCause);
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }

        if (!dateCompare(targetDate, colTargetDate)) {
            causeCode.setTargetDate(colTargetDate);
        }

        if (!strCompare(owner, colOwner)) {
            causeCode.setOwner(colOwner);
        }

        causeCode.setRemoteUser(remoteUser);
        causeCode.setRecordTime(new Date());

        try {
            getEntityManager().persist(history);
            getEntityManager().persist(causeCode);
            getEntityManager().flush();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

}

From source file:com.ibm.ioes.actions.NewOrderAction.java

public ActionForward uploadPlanExcel(ActionMapping mapping, ActionForm form, HttpServletRequest request,
        HttpServletResponse response) {/*from  w  w  w . j  ava 2s  .c o m*/
    NewOrderBean formBean = (NewOrderBean) form;
    ActionMessages messages = new ActionMessages();
    ActionForward forward = null;
    boolean validation_error = false;
    FormFile uploadedFile = formBean.getUploadedFile();
    if (uploadedFile == null || "".equals(uploadedFile.getFileName())) {
        messages.add("", new ActionMessage("uploadPlan.error.attachment.notSelected"));
        validation_error = true;
    } else if (!(uploadedFile.getFileName().substring(uploadedFile.getFileName().lastIndexOf(".") + 1))
            .equalsIgnoreCase("xls")) {
        messages.add("", new ActionMessage("uploadPlan.error.attachment.notXls"));
        validation_error = true;
    }
    /*else if(uploadedFile.getFileSize()>SessionObjectsDto.getInstance().getAttachmentSize())
    {
    messages.add("attachment",new ActionMessage("error.attachment.size"));
    validation_error = true;
    }*/

    if (validation_error) {
        saveMessages(request, messages);
        //TODO : 
        forward = mapping.findForward("DisplayUploadPage");
        /*uploadPlanExcelInitPage(mapping, form, request, response);
        forwardMapping="uploadPlanExcel";
        return mapping.findForward(forwardMapping);*/
    }
    int isValid = 1;
    int status = 0;
    ExcelValidator validator = new ExcelValidator();
    String templateFilePath = AppConstants.UploadPrdCatelogExcelTemplateFilePath;
    try {
        //ArrayList<String> dao.getAllowedSections();
        status = validator.validateUploadedExcel(uploadedFile, templateFilePath, messages);
    } catch (IOESException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    if (status == 1) {
        isValid = 0;
        messages.add("sheet_mismatch", new ActionMessage("errors.excel.sheet.mismatch"));
        AppConstants.IOES_LOGGER.info("errors.excel.sheet.mismatch");

    } else if (status == 2) {
        isValid = 0;
        messages.add("sheetName_mismatch", new ActionMessage("errors.excel.sheetname.mismatch"));
        AppConstants.IOES_LOGGER.info("errors.excel.sheetname.mismatch");

    } else if (status == 3) {
        isValid = 0;
        messages.add("sheet_blank", new ActionMessage("errors.excel.sheet.blank"));
        AppConstants.IOES_LOGGER.info("errors.excel.sheet.blank");

    } else if (status == 4) {
        isValid = 0;
        messages.add("colNum_mismatch", new ActionMessage("errors.excel.columnnumber.mismatch"));
        AppConstants.IOES_LOGGER.info("errors.excel.columnnumber.mismatch");

    } else if (status == 5) {
        isValid = 0;
        messages.add("colNanme_mismatch", new ActionMessage("errors.excel.columnname.mismatch"));
        AppConstants.IOES_LOGGER.info("errors.excel.columnname.mismatch");

    } else if (status == 7) {
        isValid = 0;
        messages.add("invalid_excel", new ActionMessage("errors.excel.invalid.excel"));
        AppConstants.IOES_LOGGER.info("errors.excel.invalid.excel");

    } else if (status == 8) {
        isValid = 0;
        messages.add("invalidObjInFile", new ActionMessage("errors.excel.invalid.filehasobject"));
        AppConstants.IOES_LOGGER.info("errors.excel.invalid.filehasobject");

    } else if (status == 9) {
        isValid = 0;

        AppConstants.IOES_LOGGER.info("errors.excel.column.length");

    } else if (status == 10) {
        isValid = 0;

        AppConstants.IOES_LOGGER.info("errors.excel.row.name.mismatch");

    } else if (status == 11) {
        isValid = 0;

        AppConstants.IOES_LOGGER.info("errors.excel.row.count.mismatch");

    }
    if (isValid == 0) {
        saveMessages(request, messages);
        formBean.setLoadExcelProductConfig_status(AppConstants.loadExcelProductConfig_status_ValidationError);
        //TODO
        forward = mapping.findForward("DisplayUploadPage");
    } else {
        try {
            int statusSave = 0;
            HSSFWorkbook uploadedWorkBook = new HSSFWorkbook(uploadedFile.getInputStream());
            int noOfSheets = uploadedWorkBook.getNumberOfSheets();
            HSSFSheet lastSheet = uploadedWorkBook.getSheetAt(noOfSheets - 1);
            //Last sheet of uploaded file have product ID on second row's first cell;
            //int productID = new Double(lastSheet.getRow(1).getCell(0).getNumericCellValue()).intValue();
            int productID = 28;
            if (productID > 0) {
                statusSave = saveUploadedFileInfo(uploadedFile, productID, templateFilePath);
            }
        } catch (IOESException ex) {
            //TODO:
        } catch (FileNotFoundException es) {

        } catch (IOException ex) {

        }

    }

    return forward;
}

From source file:com.ibm.ioes.actions.NewOrderAction.java

/**
   * @param templateStream //  w w w  .  j a v  a  2  s. co m
   * @method saveUploadedFileInfo
   * @purpose save uploaded file data in staging table in database
   * @param FormFile,
   *            filepath, userName
   * @param excel_uploadPath,
   *            uploadedFilePath
   * @return
   * @throws NpdException
   */
public int saveUploadedFileInfo(FormFile uploadedFile, int productID, String templateFilePath)
        throws IOESException {
    //   AppConstants.IOES_LOGGER.info("saveUploadedFileInfo() started");
    int sheetCol, sheetRow;
    ArrayList<Object[][]> excelDataList = new ArrayList<Object[][]>();
    int thisSaveCode = 0;
    int saveStatusCode = 0;
    try {
        String fileName = null;

        if (uploadedFile != null) {
            fileName = uploadedFile.getFileName();
        }

        if (fileName != null) {
            HSSFWorkbook workbook = null;
            HSSFSheet sheet = null;
            HSSFRow rowInSheet = null;
            HSSFCell cellInSheet = null;

            workbook = new HSSFWorkbook(uploadedFile.getInputStream());
            for (int count = 0; count < workbook.getNumberOfSheets() - 1; count++) {
                sheet = workbook.getSheetAt(count);
                sheetRow = sheet.getLastRowNum();
                sheetCol = sheet.getRow(0).getLastCellNum();
                Object excelData[][] = new Object[sheetRow][sheetCol];
                for (int r = 1; r <= sheetRow; r++) {
                    rowInSheet = sheet.getRow(r);
                    int columIndex = 0;
                    for (int c = 1; c < sheetCol + 1; c++) {
                        if (rowInSheet != null) {
                            cellInSheet = rowInSheet.getCell(c - 1);
                            if (cellInSheet != null) {
                                if (cellInSheet.getCellType() == 0) {
                                    excelData[r - 1][columIndex++] = Utility.convertWithOutE_WithOutDotZero(
                                            String.valueOf(cellInSheet.getNumericCellValue()));
                                    /*NumberFormat formatter = new DecimalFormat("0");
                                    excelData[r - 1][columIndex++] = formatter
                                     .format(cellInSheet.getNumericCellValue());*/
                                } else {
                                    excelData[r - 1][columIndex++] = cellInSheet.toString().trim();
                                }

                            } else {
                                excelData[r - 1][columIndex++] = "";
                            }
                        } else {
                            excelData[r - 1][columIndex++] = "";
                        }
                    }
                }
                excelDataList.add(excelData);
            }

        }
        //if (checkCode == 1) {
        NewOrderModel model = new NewOrderModel();
        saveStatusCode = model.saveUploadedFileToTemporaryTable(excelDataList, productID, fileName);
        if (saveStatusCode > 0) {
            thisSaveCode = 1;
        } else {
            thisSaveCode = 0;
        }

        /*}
        else
        {
          thisSaveCode= 0;
        }*/

        //         AppConstants.IOES_LOGGER.info("Completed..");
        return thisSaveCode;
    } catch (Exception ed) {
        ed.printStackTrace();
        AppConstants.IOES_LOGGER.error("Error while getting saveUploadedFileInfo " + ed.getMessage());
        throw new IOESException(ed);
    }

    finally {
        AppConstants.IOES_LOGGER.info("saveUploadedFileInfo() completed");
    }

}