Example usage for org.apache.poi.ss.usermodel Workbook getSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheet

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getSheet.

Prototype

Sheet getSheet(String name);

Source Link

Document

Get sheet with the given name

Usage

From source file:info.informationsea.tableio.excel.test.ExcelWorkbookWriterTest.java

License:Open Source License

@Test
public void testXlsWorkbookWriter() throws Exception {
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    try (ExcelWorkbookWriter workbookWriter = new XlsWorkbookWriter(outputStream)) {
        commonTestExcelWorkbookWriter(workbookWriter);
    }/* w w w .j  a v  a  2 s . c  om*/

    ByteArrayInputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
    Workbook workbook = new HSSFWorkbook(inputStream);
    assertData(new ExcelSheetReader(workbook.getSheet("table1")), data[0]);
    assertData(new ExcelSheetReader(workbook.getSheet("table2")), data[1]);
}

From source file:jasco.Jasco.java

private void exec(String xlsFile, String sheetName, int x, int y, int width, int height)
        throws FileNotFoundException {
    InputStream in = new FileInputStream(xlsFile);
    Workbook wb;
    try {/*w w w .j a  v a2s .c o  m*/
        wb = WorkbookFactory.create(in);
        Sheet sheet1 = wb.getSheet(sheetName);

        List<List<String>> rows = convertSheetToArrayList(y, height, x, width, sheet1);

        String encode = JSON.encode(rows);

        //TODO output File
        System.out.println(encode);

    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(Jasco.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:javaapp.CompareOpenClose.java

public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6,
        String tbl) throws IOException {

    String excelFilePath = "S9/GBRCNCOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    ArrayList<String> ar = new ArrayList<String>();
    String sqlstr = "";

    Workbook workbook = new XSSFWorkbook(inputStream);
    //Sheet uninv_open = workbook.getSheetAt(sno);  
    Sheet uninv_open = workbook.getSheet(name);
    //String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl);
    Iterator<Row> iterator = uninv_open.iterator();

    String rec = "";
    String pay = "";
    String per = "";
    String svc = "";
    double dval = 0;
    double cval = 0;
    String rpps = "";

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();/*  w  ww. ja v a  2 s. c  o  m*/
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        dval = 0;
        cval = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3
                    || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5
                    || cell.getColumnIndex() == c6) {

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:

                    if (cell.getColumnIndex() == c1) {
                        rec = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c2) {
                        pay = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c3) {
                        svc = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c4) {
                        per = cell.getStringCellValue();
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue());
                    if (cell.getColumnIndex() == c5) {
                        dval = cell.getNumericCellValue();
                    }
                    if (cell.getColumnIndex() == c6) {
                        cval = cell.getNumericCellValue();
                    }
                    break;
                }

            }

        }
        if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;
            //System.out.println(rpps+"|"+dval+"|"+cval);
            // ADD insert Query to Array 
            sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")";
            ar.add(sqlstr);
            //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
        }
    }

    workbook.close();
    inputStream.close();

    return ar;

}

From source file:javaapp.ParseCreditorTransactionsData.java

public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6,
        String tbl) throws IOException {

    String excelFilePath = "S9/GBRCNCOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    ArrayList<String> ar = new ArrayList<String>();
    String sqlstr = "";

    Workbook workbook = new XSSFWorkbook(inputStream);
    //Sheet uninv_open = workbook.getSheetAt(sno);  
    Sheet uninv_open = workbook.getSheet(name);
    //String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl);
    Iterator<Row> iterator = uninv_open.iterator();

    String rec = "";
    String pay = "";
    String per = "";
    String svc = "";
    double dval = 0;
    double cval = 0;
    String rpps = "";
    String filter = "";

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();//w ww  .  j a  v a  2s . c o m
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        dval = 0;
        cval = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            if (cell.getColumnIndex() == 32 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2
                    || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5
                    || cell.getColumnIndex() == c6) {

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:

                    if (cell.getColumnIndex() == c1) {
                        rec = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c2) {
                        pay = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c3) {
                        svc = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c4) {
                        per = cell.getStringCellValue();
                    }

                    if (cell.getColumnIndex() == 32) {
                        filter = cell.getStringCellValue();
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue());
                    if (cell.getColumnIndex() == c5) {
                        dval = cell.getNumericCellValue();
                    }
                    if (cell.getColumnIndex() == c6) {
                        cval = cell.getNumericCellValue();
                    }
                    break;
                }

            }

        }
        if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;

            if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) {

                if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled")
                        || filter.equalsIgnoreCase("") || filter.equalsIgnoreCase("-")) {
                    System.out.print(rpps);
                    System.out.println(filter);
                    continue;
                }

                System.out.print(filter);
                System.out.println(rpps);

            }

            // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){
            //System.out.println(rpps+"|"+dval+"|"+cval);
            //}
            //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
            //System.out.println();
            // ADD insert Query to Array 
            sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")";
            ar.add(sqlstr);
            //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
        }
    }

    workbook.close();
    inputStream.close();

    return ar;

}

From source file:javaapp.ParseCreditorTransactionsDataEE.java

public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6,
        String tbl) throws IOException {

    String excelFilePath = "EE/GBRMECOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    ArrayList<String> ar = new ArrayList<String>();
    String sqlstr = "";

    Workbook workbook = new XSSFWorkbook(inputStream);
    //Sheet uninv_open = workbook.getSheetAt(sno);  
    Sheet uninv_open = workbook.getSheet(name);
    //String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl);
    Iterator<Row> iterator = uninv_open.iterator();

    String rec = "";
    String pay = "";
    String per = "";
    String svc = "";
    double dval = 0;
    double cval = 0;
    String rpps = "";
    String filter = "";

    while (iterator.hasNext()) {

        rec = "";
        pay = "";

        Row nextRow = iterator.next();// ww w .jav  a  2  s  .  co  m
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        dval = 0;
        cval = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            if (cell.getColumnIndex() == 23 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2
                    || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5
                    || cell.getColumnIndex() == c6) {

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:

                    if (cell.getColumnIndex() == c1) {
                        rec = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c2) {
                        pay = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c3) {
                        svc = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c4) {
                        per = cell.getStringCellValue();
                    }

                    if (cell.getColumnIndex() == 23) {
                        filter = cell.getStringCellValue();
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue());
                    if (cell.getColumnIndex() == c5) {
                        dval = cell.getNumericCellValue();
                    }
                    if (cell.getColumnIndex() == c6) {
                        cval = cell.getNumericCellValue();
                    }
                    break;
                }

            }

        }
        if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;
            //System.out.print(rpps);
            //System.out.println("--->"+tbl);

            if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) {
                if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled")
                        || filter.equalsIgnoreCase("")) {
                    continue;
                }

            }

            // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){
            //System.out.println(rpps+"|"+dval+"|"+cval);
            //}
            //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
            //System.out.println();
            // ADD insert Query to Array 
            sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")";
            ar.add(sqlstr);
            //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
        }

    }

    workbook.close();
    inputStream.close();

    return ar;

}

From source file:javaapp.ParseDebtorData.java

public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6,
        String tbl) throws IOException {

    String excelFilePath = "S9/GBRCNCOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    ArrayList<String> ar = new ArrayList<String>();
    String sqlstr = "";

    Workbook workbook = new XSSFWorkbook(inputStream);
    //Sheet uninv_open = workbook.getSheetAt(sno);  
    Sheet uninv_open = workbook.getSheet(name);
    //String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl);
    Iterator<Row> iterator = uninv_open.iterator();

    String rec = "";
    String pay = "";
    String per = "";
    String svc = "";
    double dval = 0;
    double cval = 0;
    String rpps = "";

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();/*from  w  w w.  j a  v a  2s.  c  om*/
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        dval = 0;
        cval = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3
                    || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5
                    || cell.getColumnIndex() == c6) {

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:

                    if (cell.getColumnIndex() == c1) {
                        rec = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c2) {
                        pay = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c3) {
                        svc = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c4) {
                        per = cell.getStringCellValue();
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue());
                    if (cell.getColumnIndex() == c5) {
                        dval = cell.getNumericCellValue();
                    }
                    if (cell.getColumnIndex() == c6) {
                        cval = cell.getNumericCellValue();
                    }
                    break;
                }

            }

        }
        if (rec.length() == 5 || rec.length() == 8) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;
            //System.out.print(rpps+"|"+dval+"|"+cval);
            //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
            //System.out.println();
            // ADD insert Query to Array 
            sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")";
            ar.add(sqlstr);
            //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
        }
    }

    workbook.close();
    inputStream.close();

    return ar;

}

From source file:javaapp.ParseDebtorTransactionsData.java

public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6,
        String tbl) throws IOException {

    String excelFilePath = "S9/GBRCNCOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    ArrayList<String> ar = new ArrayList<String>();
    String sqlstr = "";

    Workbook workbook = new XSSFWorkbook(inputStream);
    //Sheet uninv_open = workbook.getSheetAt(sno);  
    Sheet uninv_open = workbook.getSheet(name);
    //String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl);
    Iterator<Row> iterator = uninv_open.iterator();

    String rec = "";
    String pay = "";
    String per = "";
    String svc = "";
    double dval = 0;
    double cval = 0;
    String rpps = "";
    String filter = "";

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();//w  w w .  ja  va2s. c o m
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        dval = 0;
        cval = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            if (cell.getColumnIndex() == 23 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2
                    || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5
                    || cell.getColumnIndex() == c6) {

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:

                    if (cell.getColumnIndex() == c1) {
                        rec = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c2) {
                        pay = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c3) {
                        svc = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c4) {
                        per = cell.getStringCellValue();
                    }

                    if (cell.getColumnIndex() == 23) {
                        filter = cell.getStringCellValue();
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue());
                    if (cell.getColumnIndex() == c5) {
                        dval = cell.getNumericCellValue();
                    }
                    if (cell.getColumnIndex() == c6) {
                        cval = cell.getNumericCellValue();
                    }
                    break;
                }

            }

        }
        if (rec.length() == 5 || rec.length() == 8) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;

            if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) {
                if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled")
                        || filter.equalsIgnoreCase("")) {
                    continue;
                }

            }

            // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){
            //System.out.println(rpps+"|"+dval+"|"+cval);
            //}
            //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
            //System.out.println();
            // ADD insert Query to Array 
            sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")";
            ar.add(sqlstr);
            //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
        }
    }

    workbook.close();
    inputStream.close();

    return ar;

}

From source file:jp.co.orangeright.crossheadofficesample2.jsf.ItemFileInterfaceContoroller.java

public String createTodenHomeItemExcel() {
    int count = 0;
    try {// w w w.j  a v a  2  s . com
        File excelFile = this.getFile("todenhome" + this.dataFile.getSubmittedFileName());
        Workbook workbook = WorkbookFactory.create(excelFile);
        Sheet sheet = workbook.getSheet("Sheet1");
        for (int rowNumber = sheet.getFirstRowNum() + 1; rowNumber <= sheet.getLastRowNum(); rowNumber++) {
            String itemCd = this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(0));
            ItemSearchCondition itemCondition = new ItemSearchCondition();
            itemCondition.setItemcd(itemCd);
            List<Item> itemList = this.itemEjb.findAll(itemCondition);
            if (itemList.size() > 0) {
                //???????
            } else {
                this.itemController.prepareCreate();
                this.itemController.getSelected().setItemcd(itemCd);
                this.itemController.getSelected().setCustomerid(this.customerEjb.find(31925));
                this.itemController.getSelected().setUserid(this.userEjb.find("mitanto"));
                StringBuilder detail = new StringBuilder();
                detail.append("/****** ????? ******/");
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(0)));
                detail.append(System.lineSeparator());
                detail.append("?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(1)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(13)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(14)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(15)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(16)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("??: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(17)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?_: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(18)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("?_: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(19)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(20)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("WiFi: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(21)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("IoTNo: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(57)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("ID: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(58)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(59)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(60)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("Notion: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(61)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("Notion: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(62)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(79)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("???_: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(80)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(81)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("???_: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(82)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(83)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(84)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(85)));
                detail.append("/");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(86)));
                detail.append("/");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(87)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("_?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(107)));
                detail.append(System.lineSeparator());
                detail.append("_?: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(108)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(109)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(110)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(111)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(112)));
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(113)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("_: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(114)));
                detail.append("-");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(115)));
                detail.append("-");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(116)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append("__??: ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(117)));
                detail.append("-");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(118)));
                detail.append("-");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(119)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                detail.append(": ");
                detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(120)));
                detail.append(System.lineSeparator());
                detail.append(System.lineSeparator());
                this.itemController.getSelected().setDetail(detail.toString());
                this.itemController.getSelected().setMemo("");
                this.itemController.create();
                count++;
            }
        }
        excelFile.delete();
        JsfUtil.addSuccessMessage(count + "????");
    } catch (Exception e) {
        return null;
    }
    return null;
}

From source file:magicware.scm.redmine.tools.IssueSyncApp.java

License:Apache License

public void execute(SyncItem syncItem) throws IOException, InvalidFormatException {

    FileInputStream in = null;/*from w w w.j a  v a2 s . c  o  m*/

    try {

        // ?JSON??
        String issueTemplate = FileUtils.readFileAsString(syncItem.getJsonTemplate());

        // ???
        Matcher m = Pattern.compile(Constants.ISSUE_FIELD_VALUE_EXP).matcher(issueTemplate);

        List<MatchResult> mrList = new ArrayList<MatchResult>();

        while (m.find()) {
            MatchResult mr = m.toMatchResult();
            mrList.add(mr);
        }

        // ????
        in = new FileInputStream(syncItem.getFilePath());
        Workbook wb = WorkbookFactory.create(in);

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sheet = wb.getSheet(syncItem.getSheetName());
        Row row = null;
        Cell cell = null;

        List<String> issues = new ArrayList<String>();

        // ?????
        for (int i = sheet.getLastRowNum(); i >= (syncItem.getKeyRowBeginIdx() > 0
                ? (syncItem.getKeyRowBeginIdx() - 1)
                : 0); i--) {
            // ????
            row = sheet.getRow(i);

            if (row != null) {

                String keyNo = ExcelUtils.getCellContent(row.getCell(syncItem.getKeyColumnIdx() - 1),
                        evaluator);

                // ??????????
                if (StringUtils.isBlank(keyNo)) {
                    break;
                }

                // ????
                if (redmineClient.queryIssue(syncItem.getProjectId(), syncItem.getKeyFiledId(), keyNo) == 0) {
                    StringBuilder newIssue = new StringBuilder();
                    int eolIdx = 0;
                    for (MatchResult matchResult : mrList) {

                        newIssue.append(issueTemplate.substring(eolIdx, matchResult.start()));

                        int cellIndex = Integer.valueOf(matchResult.group(1)) - 1;
                        cell = row.getCell(cellIndex);
                        String cellvalue = ExcelUtils.getCellContent(cell, evaluator);

                        // ?
                        String valueMapStr = matchResult.group(3);
                        Map<String, String> valueMap = null;
                        if (valueMapStr != null) {
                            valueMap = JSON.decode(valueMapStr);
                            if (StringUtils.isNotEmpty(cellvalue) && valueMap.containsKey(cellvalue)) {
                                cellvalue = valueMap.get(cellvalue);
                            } else {
                                cellvalue = valueMap.get("default");
                            }
                        }

                        if (StringUtils.isNotEmpty(cellvalue)) {
                            cellvalue = StringEscapeUtils.escapeJavaScript(cellvalue);
                            newIssue.append(cellvalue);
                        }
                        eolIdx = matchResult.end();
                    }
                    newIssue.append(issueTemplate.substring(eolIdx));
                    issues.add(newIssue.toString());
                } else {
                    // ???
                    break;
                }
            }
        }

        for (int i = issues.size() - 1; i >= 0; i--) {
            Map<String, Issue> issueMap = JSON.decode(issues.get(i));
            log.debug("create new issue >>>");
            log.debug(JSON.encode(issueMap, true));
            redmineClient.createNewIssue(issues.get(i));
        }

    } finally {
        if (in != null) {
            in.close();
            in = null;
        }
    }
}

From source file:massiveanalyserxryv2.ServiceTaskSearch.java

@Override
protected Task createTask() {
    Task task;/* ww w .  j a v  a  2s  . c om*/
    task = new Task() {
        @Override
        protected Object call() throws Exception {
            // rcupration de la liste des mots cls
            // si il s'agit d'une base de donne slectionn dans la liste
            String path;
            if (modelDataSearch.getAbosoluthPathDb() == null) {
                path = System.getProperty("user.dir");
                path = path + "/db/";
                path = path + modelDataSearch.getNameDb();
            } else {
                // sinon on cre le path avec le chemin absolu (fichier import)
                path = modelDataSearch.getAbosoluthPathDb();
            }

            ArrayList<String> keyWords = new ArrayList<String>();
            keyWords.clear();
            // lecture
            for (String line : Files.readAllLines(Paths.get(path))) {
                keyWords.add(line);
            }

            // rcupration de la liste des contents du tableau excel
            Workbook book = WorkbookFactory.create(modelDataSearch.getFile());
            // rcupration du sheet
            Sheet sheet = book.getSheet(modelDataSearch.getNameSheet());
            // rcupration de la colonne
            int top = sheet.getFirstRowNum();
            int down = sheet.getLastRowNum();
            Row row = sheet.getRow(top);
            // on parse les column jusqu'a ce que le nom soit le meme que celui dans le modele
            short start = row.getFirstCellNum();
            short end = row.getLastCellNum();
            int indiceColumn = -1;
            for (short i = start; i <= end; i++) {
                if (row.getCell(i).getStringCellValue().equals(modelDataSearch.getNameColumn())) {
                    // on connait l'indice de column
                    indiceColumn = i;
                    break;
                }
            }

            // cration de la liste des contents
            ArrayList<DataContent> listContent = new ArrayList<DataContent>();
            listContent.clear();
            for (int j = top; j <= down; j++) {
                if (sheet.getRow(j) != null) {
                    if ((sheet.getRow(j).getCell(indiceColumn).getCellType() == CellType.STRING.getCode())) {
                        DataContent data = new DataContent(j,
                                sheet.getRow(j).getCell(indiceColumn).getStringCellValue());
                        listContent.add(data);
                    }
                }
            }

            // recherches
            for (DataContent content : listContent) {
                for (String key : keyWords) {
                    if (key.isEmpty())
                        continue;

                    int res = content.getContent().toLowerCase().indexOf(key.toLowerCase());
                    if (res != -1) {
                        DataResultat data = new DataResultat(content.getNumRow() + 1, content.getContent(),
                                key); // +1 car dans le fichie excel les row commence  1 et pas  0
                        ob.add(data);
                    }
                }
            }

            // Fermeture du workbook
            if (book != null)
                book.close();
            //tableauResultat.setItems(ob);
            return (Object) ob;

        }

    };

    return task;
}