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

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

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

Introduction

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

Prototype

@Override
public HSSFRow getRow(int rowIndex)

Source Link

Document

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

Usage

From source file:com.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.");
        }//from   ww  w. j  ava 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<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 w  ww.  j  a  v a 2 s . co  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<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.hrr3.services.StarFileImportService.java

License:Apache License

public List<SSRSnapshotStarData> parseFileToObjects(String fileName, int hotelId)
        throws ApplicationException, IOException {

    String sTemp;// w w  w .jav  a2  s.co  m
    String sDayFrom;
    String sDayTo;
    String iPos;

    String sMonth;
    String sYear = "";
    String sTract;
    String sTractScale;

    String sOCCProp;
    String sOCCPropPC;
    String sOCCCompSet;
    String sOCCCompSetPC;
    String sOCCIndex;
    String sOCCIndexPC;

    String sARRProp;
    String sARRPropPC;
    String sARRCompSet;
    String sARRCompSetPC;
    String sARRIndex;
    String sARRIndexPC;

    String sRPProp;
    String sRPPropPC;
    String sRPCompSet;
    String sRPCompSetPC;
    String sRPIndex;
    String sRPIndexPC;

    String sMktShSupply = "";
    String sMktShDemand = "";
    String sMktShRev = "";

    String sType = "";
    String sCapHotel;
    String sCapWeek;
    int nDayStarID;
    boolean bSkip;
    int iWSColIndex;
    int iGroup;
    boolean bIsSummary;
    String sMonthSummary = "";
    String strCol;

    final String SSR_WRKSHT = "Comp";
    final String SSR_WRKSHTSUM = "Summary";
    final int COL_START = 2;
    final int COL_END = 31;
    final String REPLACE_TRACT = "Tract: ";
    final String REPLACE_TRACT_SCALE = "Tract Scale: ";
    String sOutput = "";

    SSRSnapshotStarData dayData;
    List<SSRSnapshotStarData> dayDataList = new ArrayList<SSRSnapshotStarData>(); //Day data   
    HSSFWorkbook wb = this.readFile(fileName);

    //1. Validate Summary SheetName
    if (wb.getNumberOfSheets() == 0 || wb.getSheet(SSR_WRKSHTSUM) == null)
        throw new ApplicationException("Excel file must contain a valid sheet called 'Summary'");

    //3. Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations
    HSSFSheet summarySheet = wb.getSheet(SSR_WRKSHTSUM);
    HSSFSheet compSheet = wb.getSheet(SSR_WRKSHT);
    HSSFSheet currentSheet = compSheet;
    //3.1 Instantiate main container
    dayData = new SSRSnapshotStarData();

    //4. Fill out Comp Data
    sCapHotel = getStringCellValue(compSheet.getRow(1).getCell(1)).trim().split("        ")[0];
    sCapWeek = "STAR Trend Report - " + formatMonth(
            getStringCellValue(compSheet.getRow(3).getCell(1)).trim().split("        ")[0].split(":")[1]);

    //Define global vars
    final int ROW_BASE_INDEX = 1;

    final int ROW_OCC_TRACT = 11 - ROW_BASE_INDEX;
    final int ROW_OCC_TRACTSCALE = 12 - ROW_BASE_INDEX;

    final int ROW_ADR_TRACT = 21 - ROW_BASE_INDEX;
    final int ROW_ADR_TRACTSCALE = 22 - ROW_BASE_INDEX;

    final int ROW_REVPAR_TRACT = 31 - ROW_BASE_INDEX;
    final int ROW_REVPAR_TRACTSCALE = 32 - ROW_BASE_INDEX;

    final int ROW_OCC_MY_PROP = 21 - ROW_BASE_INDEX;
    final int ROW_OCC_MY_PROP_PER_CHG = 26 - ROW_BASE_INDEX;
    final int ROW_OCC_COMP_SET = 22 - ROW_BASE_INDEX;
    final int ROW_OCC_COMP_SET_PER_CHG = 27 - ROW_BASE_INDEX;
    final int ROW_OCC_INDEX = 23 - ROW_BASE_INDEX;
    final int ROW_OCC_INDEX_PER_CHG = 28 - ROW_BASE_INDEX;

    final int ROW_ADR_MY_PROP = 33 - ROW_BASE_INDEX;
    final int ROW_ADR_MY_PROP_PER_CHG = 38 - ROW_BASE_INDEX;
    final int ROW_ADR_COMP_SET = 34 - ROW_BASE_INDEX;
    final int ROW_ADR_COMP_SET_PER_CHG = 39 - ROW_BASE_INDEX;
    final int ROW_ADR_INDEX = 35 - ROW_BASE_INDEX;
    final int ROW_ADR_INDEX_PER_CHG = 40 - ROW_BASE_INDEX;

    final int ROW_REVPAR_MY_PROP = 45 - ROW_BASE_INDEX;
    final int ROW_REVPAR_MY_PROP_PER_CHG = 50 - ROW_BASE_INDEX;
    final int ROW_REVPAR_COMP_SET = 46 - ROW_BASE_INDEX;
    final int ROW_REVPAR_COMP_SET_PER_CHG = 51 - ROW_BASE_INDEX;
    final int ROW_REVPAR_INDEX = 47 - ROW_BASE_INDEX;
    final int ROW_REVPAR_INDEX_PER_CHG = 52 - ROW_BASE_INDEX;

    //5. Fill out Summary Data
    for (iWSColIndex = COL_START; iWSColIndex <= COL_END + 6; iWSColIndex++) {

        //Initialize
        bSkip = false;
        sMonth = "";
        //sType = ""
        sTract = "";
        sTractScale = "";
        iGroup = 0;
        bIsSummary = false;

        sOCCProp = "";
        sOCCPropPC = "";
        sOCCCompSet = "";
        sOCCCompSetPC = "";
        sOCCIndex = "";
        sOCCIndexPC = "";

        sARRProp = "";
        sARRPropPC = "";
        sARRCompSet = "";
        sARRCompSetPC = "";
        sARRIndex = "";
        sARRIndexPC = "";

        sRPProp = "";
        sRPPropPC = "";
        sRPCompSet = "";
        sRPCompSetPC = "";
        sRPIndex = "";
        sRPIndexPC = "";

        strCol = CellReference.convertNumToColString(iWSColIndex);

        //System.out.println("StrCol: " + strCol);

        if (iWSColIndex == COL_END + 1) {//Use summary worksheet

            //2. Validate Comp SheetName
            if (wb.getNumberOfSheets() == 0 || wb.getSheet(SSR_WRKSHT) == null)
                throw new ApplicationException("Excel file must contain a valid sheet called 'Comp'");

            //Set symmarySheet
            currentSheet = summarySheet;
        }

        if (iWSColIndex <= COL_END) {
            bIsSummary = false;

            if (isBetween(iWSColIndex, 3 - ROW_BASE_INDEX, 20 - ROW_BASE_INDEX)) {
                sType = "";
                sMonth = formatMonth(getStringCellValue(currentSheet.getRow(20 - ROW_BASE_INDEX)
                        .getCell(CellReference.convertColStringToIndex(strCol))));
                iGroup = 1;
            }

            if (isBetween(iWSColIndex, 26 - ROW_BASE_INDEX, 28 - ROW_BASE_INDEX)) {
                iGroup = 2;
            }

            if (isBetween(iWSColIndex, 22 - ROW_BASE_INDEX, 24 - ROW_BASE_INDEX)) {
                iGroup = 3;
            }

            if (isBetween(iWSColIndex, 30 - ROW_BASE_INDEX, 32 - ROW_BASE_INDEX)) {
                iGroup = 4;
            }

            if (iWSColIndex == 20 - ROW_BASE_INDEX) {
                sMonthSummary = sMonth;
            }

            if (iWSColIndex != (21 - ROW_BASE_INDEX) && iWSColIndex != (25 - ROW_BASE_INDEX)
                    && iWSColIndex != (29 - ROW_BASE_INDEX)) {

                if (isBetween(iWSColIndex, 3 - ROW_BASE_INDEX, 20 - ROW_BASE_INDEX)) {
                    sYear = findYear(currentSheet, iWSColIndex);
                    //sYear = getStringCellValue(currentSheet.getRow(19 - ROW_BASE_INDEX).getCell(CellReference.convertColStringToIndex(strCol))).trim();
                }

                if (isBetween(iWSColIndex, 22 - ROW_BASE_INDEX, 32 - ROW_BASE_INDEX)) {
                    sYear = getStringCellValue(currentSheet.getRow(20 - ROW_BASE_INDEX)
                            .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                }

                sOCCProp = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_MY_PROP)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sOCCPropPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_MY_PROP_PER_CHG)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sOCCCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_COMP_SET)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sOCCCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_COMP_SET_PER_CHG)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sOCCIndex = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_INDEX)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sOCCIndexPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_INDEX_PER_CHG)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();

                sARRProp = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_MY_PROP)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sARRPropPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_MY_PROP_PER_CHG)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sARRCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_COMP_SET)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sARRCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_COMP_SET_PER_CHG)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sARRIndex = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_INDEX)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sARRIndexPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_INDEX_PER_CHG)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();

                sRPProp = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_MY_PROP)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sRPPropPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_MY_PROP_PER_CHG)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_COMP_SET)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_COMP_SET_PER_CHG)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sRPIndex = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_INDEX)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
                sRPIndexPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_INDEX_PER_CHG)
                        .getCell(CellReference.convertColStringToIndex(strCol))).trim();
            } else //Heading rows 
            {
                bSkip = true;
                switch (iWSColIndex) {

                case 20:
                    sType = formatHeader(getStringCellValue(currentSheet.getRow(18).getCell(iWSColIndex + 1)),
                            sMonthSummary);
                    break;
                case 24:
                    sType = formatHeader(getStringCellValue(currentSheet.getRow(18).getCell(iWSColIndex + 1)),
                            sMonthSummary);
                    break;
                case 28:
                    sType = formatHeader(getStringCellValue(currentSheet.getRow(18).getCell(iWSColIndex + 1)),
                            sMonthSummary);
                    break;

                }

            }

        }

        //Summary Worksheet
        else {

            bIsSummary = true;
            switch (iWSColIndex) {

            case COL_END + 1:
                //current month                   
                sTract = getStringCellValue(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B")))
                                .trim().replaceAll(REPLACE_TRACT, "");
                sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("B"))).trim()
                                .replaceAll(REPLACE_TRACT_SCALE, "");
                iGroup = 1;
                sType = "SummaryTract";
                sMonth = sMonthSummary;

                //set OC, ARR, RPC - TODO roundNumber
                sOCCCompSet = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("E")))
                                .trim();
                sOCCCompSetPC = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("F")))
                                .trim();
                sARRCompSet = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("G")))
                                .trim();
                sARRCompSetPC = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("H")))
                                .trim();
                sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT)
                        .getCell(CellReference.convertColStringToIndex("G"))).trim();
                sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT)
                        .getCell(CellReference.convertColStringToIndex("H"))).trim();
                break;

            case COL_END + 2:
                sTract = getStringCellValue(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B")))
                                .trim().replaceAll(REPLACE_TRACT, "");
                sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("B"))).trim()
                                .replaceAll(REPLACE_TRACT_SCALE, "");
                iGroup = 1;
                sType = "SummaryTractScale";
                sMonth = sMonthSummary;

                //set OC, ARR, RPC - TODO roundNumber
                sOCCCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("E"))).trim();
                sOCCCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("F"))).trim();
                sARRCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("G"))).trim();
                sARRCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("H"))).trim();
                sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("G"))).trim();
                sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("H"))).trim();
                break;

            case COL_END + 3:
                sTract = getStringCellValue(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B")))
                                .trim().replaceAll(REPLACE_TRACT, "");
                sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("B"))).trim()
                                .replaceAll(REPLACE_TRACT_SCALE, "");
                iGroup = 3;
                sType = "SummaryTract";

                //set OC, ARR, RPC - TODO roundNumber
                sOCCCompSet = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("G")))
                                .trim();
                sOCCCompSetPC = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("H")))
                                .trim();
                sARRCompSet = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("G")))
                                .trim();
                sARRCompSetPC = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("H")))
                                .trim();
                sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT)
                        .getCell(CellReference.convertColStringToIndex("G"))).trim();
                sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT)
                        .getCell(CellReference.convertColStringToIndex("H"))).trim();
                break;

            case COL_END + 4:
                sTract = getStringCellValue(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B")))
                                .trim().replaceAll(REPLACE_TRACT, "");
                sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("B"))).trim()
                                .replaceAll(REPLACE_TRACT_SCALE, "");
                iGroup = 3;
                sType = "SummaryTractScale";

                //set OC, ARR, RPC - TODO roundNumber
                sOCCCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("G"))).trim();
                sOCCCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("H"))).trim();
                sARRCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("G"))).trim();
                sARRCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("H"))).trim();
                sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("G"))).trim();
                sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("H"))).trim();
                break;

            case COL_END + 5:

                sTract = getStringCellValue(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B")))
                                .trim().replaceAll(REPLACE_TRACT, "");
                sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("B"))).trim()
                                .replaceAll(REPLACE_TRACT_SCALE, "");
                iGroup = 4;
                sType = "SummaryTract";

                //set OC, ARR, RPC - TODO roundNumber
                sOCCCompSet = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("K")))
                                .trim();
                sOCCCompSetPC = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("L")))
                                .trim();
                sARRCompSet = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("K")))
                                .trim();
                sARRCompSetPC = getNumberAsStringInExcelFormat(
                        currentSheet.getRow(ROW_ADR_TRACT).getCell(CellReference.convertColStringToIndex("L")))
                                .trim();
                sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT)
                        .getCell(CellReference.convertColStringToIndex("K"))).trim();
                sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACT)
                        .getCell(CellReference.convertColStringToIndex("L"))).trim();
                break;

            case COL_END + 6:

                sTract = getStringCellValue(
                        currentSheet.getRow(ROW_OCC_TRACT).getCell(CellReference.convertColStringToIndex("B")))
                                .trim().replaceAll(REPLACE_TRACT, "");
                sTractScale = getStringCellValue(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("B"))).trim()
                                .replaceAll(REPLACE_TRACT_SCALE, "");
                iGroup = 4;
                sType = "SummaryTractScale";

                //set OC, ARR, RPC - TODO roundNumber
                sOCCCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("K"))).trim();
                sOCCCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_OCC_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("L"))).trim();
                sARRCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("K"))).trim();
                sARRCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_ADR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("L"))).trim();
                sRPCompSet = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("K"))).trim();
                sRPCompSetPC = getNumberAsStringInExcelFormat(currentSheet.getRow(ROW_REVPAR_TRACTSCALE)
                        .getCell(CellReference.convertColStringToIndex("L"))).trim();
                break;

            }
        } //Else

        if (iGroup > 0) {

            dayData = new SSRSnapshotStarData();

            dayData.setHotelId(hotelId);
            dayData.setType(sType);
            dayData.setGroup(iGroup);
            dayData.setSequence(iWSColIndex + 5 + ROW_BASE_INDEX);
            dayData.setStarYear(Double.valueOf(sYear).intValue());
            dayData.setStarMonth(sMonth);
            dayData.setHotelCaption(sCapHotel);
            dayData.setMonthCaption(sCapWeek);
            dayData.setTract(sTract);
            dayData.setTractScale(sTractScale);
            //OCC
            dayData.setOccProp(sOCCProp);
            dayData.setOccPropPc(sOCCPropPC);
            dayData.setOccCompset(sOCCCompSet);
            dayData.setOccCompsetPc(sOCCCompSetPC);
            dayData.setOccIndex(sOCCIndex);
            dayData.setOccIndexPc(sOCCIndexPC);
            //ARR
            dayData.setArrProp(sARRProp);
            dayData.setArrPropPc(sARRPropPC);
            dayData.setArrCompset(sARRCompSet);
            dayData.setArrCompsetPc(sARRCompSetPC);
            dayData.setArrIndex(sARRIndex);
            dayData.setArrIndexPc(sARRIndexPC);
            //REVPAR
            dayData.setRevparProp(sRPProp);
            dayData.setRevparPropPc(sRPPropPC);
            dayData.setRevparCompset(sRPCompSet);
            dayData.setRevparCompsetPc(sRPCompSetPC);
            dayData.setRevparIndex(sRPIndex);
            dayData.setRevparIndexPc(sRPIndexPC);
            //MKTSH
            dayData.setMktshSupply(sMktShSupply);
            dayData.setMktshDemand(sMktShDemand);
            dayData.setMktshRev(sMktShRev);

            System.out.println(dayData);

            dayDataList.add(dayData);

        }

    } //For

    return dayDataList;

}

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

License:Apache License

private String findYear(HSSFSheet vWorksheet, int vColumn) {
    int iCount;//from ww  w .  j a  v  a  2s .  c  o  m
    String sValue;

    iCount = vColumn;

    do {
        sValue = getStringCellValue(vWorksheet.getRow(18).getCell(iCount)).trim();
        //System.out.println("sValue:"+ sValue);
        iCount = iCount - 1;
    } while (sValue.equalsIgnoreCase("") || sValue.equalsIgnoreCase("0"));

    return sValue;
}

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.ja  v a2  s. c o m
    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. j  ava  2  s.  com*/
    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

@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  ww.j a v a2  s  .c  o m*/
        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

@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);// ww  w.  j a  va  2s  .  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;
}

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

/**
   * @param templateStream //from w ww .  j a  v a2 s .c  om
   * @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");
    }

}

From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java

@SuppressWarnings("deprecation")
public String getErrorExcel(String filePath, int fileID) throws IOESException {
    //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName());

    int colCount, ctr = 0;
    String fileName;/*from w ww  .  j ava2s.  c o m*/
    HSSFWorkbook wb;
    HSSFSheet ws;
    HSSFRow wr;
    HSSFCell wc;
    ArrayList errVal = new ArrayList();
    ErrorFileDaoImpl objDao = new ErrorFileDaoImpl();
    ErrorLogDto dtoObj;
    BillingTriggerValidation validateDto = null;

    try {
        ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources");

        fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length());
        String newFile = bundle.getString("excel.errors") + "/" + fileName;
        FileOutputStream fileOut = new FileOutputStream(newFile);
        wb = new HSSFWorkbook(new FileInputStream(filePath));

        errVal = objDao.getErrorLog(fileID);
        System.out.println(errVal.toString());
        System.out.println(wb.getNumberOfSheets());
        for (int s = 0; s < wb.getNumberOfSheets(); s++) {

            ws = wb.getSheetAt(s);
            wr = ws.getRow(1);
            colCount = wr.getLastCellNum();
            wc = wr.createCell(colCount);
            wc.setCellValue("ERROR LOG");

            for (int r = 2; r <= ws.getLastRowNum(); r++) {
                if ((ctr < errVal.size())) {
                    dtoObj = (ErrorLogDto) errVal.get(ctr);
                    wr = ws.getRow(r);
                    if (wr != null) {
                        int chk = 0;
                        for (int col = 0; col < colCount; col++) {
                            wc = wr.getCell(col);
                            if (wc != null) {
                                if (!(wc.toString().trim().equals(""))) {
                                    chk = 1;
                                }
                            }
                        }
                        if (chk == 1) {
                            wc = wr.createCell(colCount);
                            System.out.println(dtoObj.getErrorLogValue());
                            if (dtoObj.getErrorLogValue() == null) {
                                wc.setCellValue("No Errors");
                            } else {
                                wc.setCellValue(dtoObj.getErrorLogValue().toString());
                            }

                            ctr++;
                        }
                    }
                }
            }
        }
        wb.write(fileOut);
        fileOut.close();
        filePath = newFile;
    } catch (IOESException ex) {
        logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel method of "
                + this.getClass().getSimpleName());
        throw new IOESException("SQL Exception : " + ex.getMessage(), ex);
    } catch (IOException ioExp) {
        //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName());
        //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp);
        Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getErrorExcel method of "
                + this.getClass().getSimpleName());
        return filePath = "NOTFOUND";
    }
    return filePath;
}