Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheetAt

Introduction

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

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:TestCaseReaderTest.java

public void testSimple() throws Exception {
    //public List<TestScriptTemplate> readExcel(){

    FileInputStream file = new FileInputStream(
            new File("C:\\Users\\nbret00\\Documents\\SeleniumSmokeTest\\TestCases.xlsx"));

    List<TestScriptTemplate> tstList = new ArrayList<TestScriptTemplate>();
    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);

    //Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);

    //Iterate through each rows one by one
    Iterator<Row> rowIterator = sheet.iterator();

    List<TestScriptTemplate> TestScriptTemplateList = new ArrayList();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();/*from  ww w .  j a v a2  s . c  om*/
        //skip until row 5
        if (row.getRowNum() > 4) {

            TestScriptTemplate tscripttemp = new TestScriptTemplate();

            //Cell appcode = row.getCell(0); //this should be the item # on the list
            //System.out.println("application name #: "+itemnum.getStringCellValue());
            tscripttemp.setAppCode(getCellValueStr(row.getCell(1)));
            tscripttemp.setAppURL(getCellValueStr(row.getCell(2)));
            tscripttemp.setAppUserID(getCellValueStr(row.getCell(3)));
            tscripttemp.setAppPassword(getCellValueStr(row.getCell(4)));
            tscripttemp.setHomePageTitle(getCellValueStr(row.getCell(5)));
            tscripttemp.setHomePageElement(getCellValueStr(row.getCell(6)));
            tscripttemp.setLevel1URL(getCellValueStr(row.getCell(7)));
            tscripttemp.setLevel1PageTitle(getCellValueStr(row.getCell(8)));
            tscripttemp.setLevel1Element(getCellValueStr(row.getCell(9)));

            //tscripttemp.setAppUserID(row.getCell(3).getStringCellValue());
            //tscripttemp.setAppPassword(row.getCell(3).getStringCellValue());
            TestScriptTemplateList.add(tscripttemp);
            System.out.println("this to string: " + tscripttemp.toString());
        }

    }
    file.close();
}

From source file:StatusUpdater.java

static boolean updateStatus(String path, String username, String task, int optionChosen) {
    File myFile = new File(path);
    FileInputStream fis = null;/* www .  jav  a 2 s.c o  m*/
    try {
        fis = new FileInputStream(myFile);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(fis);
    } catch (IOException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    XSSFSheet sheet = workbook.getSheetAt(0);
    if (sheet == null) {
        return false;
    }
    Iterator ite1 = sheet.rowIterator();
    if (ite1 == null) {
        return false;
    }
    XSSFRow myRow = null;
    DateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm:ss");
    Date dateobj = new Date();
    df.format(dateobj);
    if (ite1.hasNext()) {
        ite1.next();
    }
    while (ite1.hasNext()) {
        myRow = (XSSFRow) ite1.next();
        XSSFCell usernameCell = myRow.getCell(0);
        String sheet_userid = null;
        if (usernameCell.getStringCellValue() != null) {
            sheet_userid = usernameCell.getStringCellValue();
        } else {
            return false;
        }
        System.out.println("sheet_userid=" + sheet_userid);
        XSSFCell taskCell = myRow.getCell(1);
        if (taskCell == null) {
            return false;
        }
        String sheet_task = taskCell.getStringCellValue();
        System.out.println("sheet_task=" + sheet_task);
        if (sheet_task == null) {
            return false;
        }
        if (sheet_userid.equals(username) && sheet_task.equals(task)) {
            break;
        }
    }
    if (optionChosen == 1) { //Resume is pressed.
        XSSFCell statusCell = myRow.getCell(2);
        String status = null;
        if (statusCell != null) {
            status = statusCell.getStringCellValue();
            if (status.equalsIgnoreCase("Paused") || status.equalsIgnoreCase("Deferred")) {
                XSSFCell timestampCell = myRow.getCell(3);
                timestampCell.setCellValue(df.format(dateobj));
                XSSFCell status_cell = myRow.getCell(2);
                status_cell.setCellValue("In-Progress");
            } else if (status.equalsIgnoreCase("In-Progress")) //trying to Resume an in-progress task.
            {
                return true;
            } else { //trying to resume a finished task or invalid status task.
                return false;
            }
        } else {
            return false;
        }
    } else if (optionChosen == 2) { //Pause is pressed
        XSSFCell statusCell = myRow.getCell(2);
        if (statusCell != null) {
            String status = statusCell.getStringCellValue();
            if (status != null) {

                if (status.equalsIgnoreCase("Paused"))
                    return true;
                else if (status.equalsIgnoreCase("In-Progress")) {
                    XSSFCell timestampCell = myRow.getCell(3);
                    String dateInString = timestampCell.getStringCellValue();
                    Date date_obj = null;
                    try {
                        date_obj = df.parse(dateInString);
                    } catch (ParseException ex) {
                        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    System.out.println("date value of sheet in pause button=" + dateobj.toString());

                    Date obj = new Date();
                    df.format(obj);
                    long diff = date_obj.getTime() - obj.getTime();
                    long divisor = 60 * 60 * 1000;
                    double diffHours = ((double) diff / (double) divisor);
                    //XSSFCell cell2=myRow.getCell(4);
                    XSSFCell totalTimeCell = null;
                    if (myRow.getCell(4) == null) {
                        totalTimeCell = myRow.createCell(4);
                        totalTimeCell.setCellValue(Double.toString(diffHours));
                    } else {
                        totalTimeCell = myRow.getCell(4);
                        double timeSpent = Double.parseDouble(totalTimeCell.getStringCellValue());
                        timeSpent += diffHours;
                        totalTimeCell.setCellValue(String.valueOf(timeSpent));
                    }
                    statusCell.setCellValue("Paused");
                } else if (status.equalsIgnoreCase("Deferred")) {
                    statusCell.setCellValue("Paused");
                } else
                    return false;
            } else
                return false;
        } else {
            return false;
        }
    } else if (optionChosen == 3) { //Stop is pressed
        XSSFCell statusCell = myRow.getCell(2);
        if (statusCell != null) {
            String status = statusCell.getStringCellValue();
            if (status != null) {

                if (status.equalsIgnoreCase("Paused"))
                    return true;
                else if (status.equalsIgnoreCase("In-Progress")) {
                    XSSFCell timestampCell = myRow.getCell(3);
                    String dateInString = timestampCell.getStringCellValue();
                    Date date_obj = null;
                    try {
                        date_obj = df.parse(dateInString);
                    } catch (ParseException ex) {
                        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    System.out.println("date value of sheet in pause button=" + dateobj.toString());

                    Date obj = new Date();
                    df.format(obj);
                    long diff = date_obj.getTime() - obj.getTime();
                    long divisor = 60 * 60 * 1000;
                    double diffHours = ((double) diff / (double) divisor);
                    XSSFCell totalTimeCell = null;
                    if (myRow.getCell(4) == null) {
                        totalTimeCell = myRow.createCell(4);
                        totalTimeCell.setCellValue(Double.toString(diffHours));
                    } else {
                        totalTimeCell = myRow.getCell(4);
                        double timeSpent = Double.parseDouble(totalTimeCell.getStringCellValue());
                        timeSpent += diffHours;
                        totalTimeCell.setCellValue(String.valueOf(timeSpent));
                    }
                    statusCell.setCellValue("Deferred");
                } else if (status.equalsIgnoreCase("Paused")) {
                    statusCell.setCellValue("Deferred");
                } else {
                    return false;
                }
            } else {
                return false;
            }
        } else {
            return false;
        }
    } else if (optionChosen == 4) {
        XSSFCell status_cell = myRow.getCell(2);
        if (status_cell.getStringCellValue() == "In-Progress") //logic to calculate the time taken if the task was in-process so far
        {
            XSSFCell timestampCell = myRow.getCell(3);
            String dateInString = timestampCell.getStringCellValue();
            Date date_obj = null;
            try {
                date_obj = df.parse(dateInString);
            } catch (ParseException ex) {
                Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
            }
            Date obj = new Date();
            df.format(obj);
            long fv = date_obj.getTime();
            long sv = obj.getTime();
            long diff = sv - fv;
            long divisor = 60 * 60 * 1000;
            double diffHours = ((double) diff / (double) divisor);

            XSSFCell cell2 = null;
            if (myRow.getCell(4) == null) {
                cell2 = myRow.createCell(4);
                cell2.setCellValue(Double.toString(diffHours));
            } else {
                cell2 = myRow.getCell(4);
                double timeSpent = Double.parseDouble(cell2.getStringCellValue());
                timeSpent += diffHours;

                cell2.setCellValue(String.valueOf(timeSpent));
            }
        }
        status_cell.setCellValue("Completed");

    } else {
        System.out.println("Invalid value for optionChosen");
    }
    try {
        fis.close();
    } catch (IOException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Debug one");
    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(myFile);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Debug two");
    try {
        workbook.write(fileOut);
    } catch (IOException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        fileOut.close();
    } catch (IOException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    return true;
}

From source file:StatusUpdater.java

static void addStatusUpdate(String path, String username, String task, String comments, int optionChosen) {
    DateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm:ss");
    Date dateobj = new Date();

    FileInputStream file = null;/*  w  ww.  ja v a  2 s . c o  m*/
    try {
        file = new FileInputStream(new File(path));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }

    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(file);
    } catch (IOException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }

    //Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(1);
    int rownum = sheet.getLastRowNum();
    //Blank workbook
    Row row = sheet.createRow(rownum + 1);

    Cell usernameCell = row.createCell(0);
    usernameCell.setCellValue(username);
    Cell taskCell = row.createCell(1);
    taskCell.setCellValue(task);
    Cell statusCell = row.createCell(2);
    switch (optionChosen) {
    case 1:
        statusCell.setCellValue("Resumed");
        break;
    case 2:
        statusCell.setCellValue("Paused");
        break;
    case 3:
        statusCell.setCellValue("Deferred");
        break;
    case 4:
        statusCell.setCellValue("Completed");
        break;
    }
    Cell timestampCell = row.createCell(3);
    timestampCell.setCellValue(df.format(dateobj).toString());
    Cell commentsCell = row.createCell(4);
    commentsCell.setCellValue(comments);

    FileOutputStream out = null;
    try {
        out = new FileOutputStream(new File(path));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        workbook.write(out);
    } catch (IOException ex) {
        Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        out.close();
    } catch (IOException ex) {
        Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:ExcelConverter.java

public List<ScheduleClass> Converter() throws FileNotFoundException, IOException {
    ArrayList<ScheduleClass> scheduleList = new ArrayList<>();

    FileInputStream fis = new FileInputStream(pathFile);

    XSSFWorkbook wb = new XSSFWorkbook(fis);
    XSSFSheet sheet = wb.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();

    CellRangeAddress add;/* ww w  .  ja va  2s . c  om*/
    int colNoIdx = 0;
    ArrayList<String> dosen = new ArrayList<>();
    ArrayList<Integer> idxDosen = new ArrayList<>();
    ArrayList<Integer> colDosen = new ArrayList<>();
    ArrayList<String> location = new ArrayList<>();
    int idxNumber = 0;
    ArrayList<Integer> locationIdx = new ArrayList<>();
    outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) {
        row = sheet.getRow(j);
        for (int f = 0; f < row.getLastCellNum(); f++) {
            Cell cell = row.getCell(j);
            if (cell.getStringCellValue().contains("No.")) {
                rowNoIdx = j;
                colNoIdx = cell.getColumnIndex();

                break outerloop;
            }
        }
    }
    outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        outerloop: for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3
                    && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) {
                i = sheet.getLastRowNum();
                break outerloop2;
            }

            if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) {
                String delims = "[,. ]";
                String[] sumary = cell.getStringCellValue().split(delims);
                for (int l = 0; l < sumary.length; l++) {
                    if (sumary[l].equalsIgnoreCase("Mrt")) {
                        sumary[l] = "3";
                    }
                }

                lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]),
                        Integer.parseInt(sumary[2]));
            }
            if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) {
                if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) {
                    i = i + 1;
                    break outerloop;
                } else {
                    String delimsJam = "[-]";
                    String[] arrJam = cell.getStringCellValue().split(delimsJam);
                    for (int k = 0; k < arrJam.length; k++) {
                        arrJam[k] = arrJam[k].replace('.', ':');
                    }
                    lt = LocalTime.parse(arrJam[0]);
                }

            }
            if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 5)) {
                subject = cell.getStringCellValue();
            }

            if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colNoIdx + 6
                    && cell.getColumnIndex() < row.getLastCellNum()) {
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                }
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getStringCellValue().contains(":")) {
                        String[] splt = cell.getStringCellValue().split(":");
                        String[] splt2 = splt[1].split(",");
                        for (int l = 0; l < splt2.length; l++) {
                            dosen.add(splt2[l].trim());
                            location.add("Lab");
                        }
                    } else {
                        CellReference cr = new CellReference(1, cell.getColumnIndex());
                        Row row2 = sheet.getRow(cr.getRow());
                        Cell c = row2.getCell(cr.getCol());
                        if (!cell.getStringCellValue().isEmpty()) {
                            dosen.add(cell.getStringCellValue().trim());
                            location.add(String.valueOf((int) c.getNumericCellValue()).trim());
                        }
                    }

                }
                if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) {
                    CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex());
                    Row row2 = sheet.getRow(cr.getRow());
                    Cell c = row2.getCell(cr.getCol());
                    CellReference cr2 = new CellReference(1, cell.getColumnIndex());
                    Row row3 = sheet.getRow(cr2.getRow());
                    Cell c2 = row3.getCell(cr2.getCol());
                    if (c.getStringCellValue().contains(":")) {
                        String[] splt = c.getStringCellValue().split(":");
                        String[] splt2 = splt[1].split(",");
                        for (int l = 0; l < splt2.length; l++) {
                            dosen.add("".trim());
                            location.add("");
                        }
                    } else {
                        if (!c.getStringCellValue().isEmpty()) {
                            dosen.add("");
                            location.add("");
                        }
                    }
                }
            }
        }

        for (int j = 0; j < dosen.size(); j++) {
            scheduleList
                    .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j)));
        }
        dosen.clear();
        location.clear();

    }

    return Mergering(scheduleList);
}

From source file:leerArchivos.java

public leerArchivos(File fileName) {
    List cellData = new ArrayList();

    try {/*from w w  w  .j a  v a 2s.  co m*/
        FileInputStream fileInputStream = new FileInputStream(fileName);
        XSSFWorkbook workBook = new XSSFWorkbook(fileInputStream);
        XSSFSheet hssfSheet = workBook.getSheetAt(0);
        Iterator rowIterator = hssfSheet.rowIterator();

        while (rowIterator.hasNext()) {
            XSSFRow hssfRow = (XSSFRow) rowIterator.next();
            Iterator iterator = hssfRow.cellIterator();
            List cellTemp = new ArrayList();
            while (iterator.hasNext()) {
                XSSFCell hssfCell = (XSSFCell) iterator.next();
                cellTemp.add(hssfCell);
            }
            cellData.add(cellTemp);
        }

    } catch (Exception e) {
    }
    obtener(cellData);
}

From source file:TimeInOut2.java

private void insertActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_insertActionPerformed
    // TODO add your handling code here:
    PreparedStatement preparedStatement;

    try {//from   ww  w .ja  v a2s  .  c  o m

        FileInputStream fis = new FileInputStream(new File(file));
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.iterator();
        boolean isSuccessful = true;
        while (rowIterator.hasNext()) {

            statusBar.setValue(statusBar.getValue() + (int) (100 / numberOfRows));
            statusBar.update(statusBar.getGraphics());
            try {
                Thread.sleep(100);
            } catch (InterruptedException ex) {
                Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex);
            }

            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            preparedStatement = connection.prepareStatement(
                    "INSERT INTO logs (logsDate,logsTime,logsStatus,residentIdnum) VALUES (?,?,?,?)");

            java.util.Date utilDate = date.getDateFormat().parse(date.getText());
            String formattedDate = new SimpleDateFormat("yyyy-MM-dd").format(utilDate);
            java.sql.Date sqlDate = new java.sql.Date(
                    new SimpleDateFormat("yyyy-MM-dd").parse(formattedDate).getTime());
            preparedStatement.setDate(1, sqlDate);

            preparedStatement.setInt(4, (int) cellIterator.next().getNumericCellValue());

            java.util.Date utilTime = cellIterator.next().getDateCellValue();
            String formattedTime = new SimpleDateFormat("HH:mm:ss").format(utilTime);
            java.sql.Time sqlTime = new java.sql.Time(
                    new SimpleDateFormat("HH:mm:ss").parse(formattedTime).getTime());
            preparedStatement.setTime(2, sqlTime);

            preparedStatement.setString(3, cellIterator.next().getStringCellValue());

            if (preparedStatement.execute()) {
                isSuccessful = false;
                break;
            }
        }
        statusBar.setValue(100);
        statusBar.update(statusBar.getGraphics());

        fis.close();
        if (isSuccessful) {
            new MessageDialog().successful(this);
            insert.setEnabled(false);
        } else {
            new MessageDialog().unsuccessful(this);
        }
    } catch (FileNotFoundException ex) {
        //            Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex);
        new MessageDialog().error(this, ex.getMessage());
    } catch (IOException ex) {
        //            Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex);
        new MessageDialog().error(this, ex.getMessage());
    } catch (SQLException | ParseException ex) {
        //            Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex);
        new MessageDialog().error(this, ex.getMessage());
    }
}

From source file:TimeInOut2.java

private void checkFile() {
    try {/*from w  ww  .  java2 s. com*/

        FileInputStream fis = new FileInputStream(new File(file));
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet sheet = workbook.getSheetAt(0);

        boolean readyToSave = true;
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            numberOfRows++;
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            if (!idValidator(cellIterator.next(), numberOfRows)
                    | !timeValidator(cellIterator.next(), numberOfRows)
                    | !statusValidator(cellIterator.next(), numberOfRows)) {
                readyToSave = false;
                logs.append("Problem(s) encountered at row " + numberOfRows + ".\n");
            }
        }
        if (readyToSave) {
            insertPanel.setVisible(readyToSave);
            logs.append("File ready to be inserted/saved.");
        } else {
            logs.append("Please fix the problems(s) first to proceed.");
        }
        //                statusLabel.setText("Finished");
        fis.close();
    } catch (FileNotFoundException ex) {
        //            Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex);
        new MessageDialog().error(this, ex.getMessage());
    } catch (IOException ex) {
        //            Logger.getLogger(TimeInOut2.class.getName()).log(Level.SEVERE, null, ex);
        new MessageDialog().error(this, ex.getMessage());
    }
}

From source file:UploadImage.java

@Override
public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    HttpSession session = request.getSession();
    if (null == session.getAttribute("idsupp")) {
        response.sendRedirect("public/pages/supplier/login_soft.jsp");
    }//from  w  w w .  ja  v a2s .co  m
    PrintWriter out = response.getWriter();
    boolean isMultipart = ServletFileUpload.isMultipartContent(request);
    System.out.println("request: " + request);
    if (!isMultipart) {
        System.out.println("File Not Uploaded");
    } else {
        System.out.println("File  Uploaded");
        FileItemFactory factory = new DiskFileItemFactory();
        ServletFileUpload upload = new ServletFileUpload(factory);
        List items = null;
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            Connection cnx = DriverManager.getConnection("jdbc:derby://localhost:1527/pcdb", "pcdbun",
                    "pcdbpw");

            try {

                items = upload.parseRequest(request);
                System.out.println("items: " + items);
            } catch (Exception e) {
                e.printStackTrace();
                //System.out.println(e);
            }
            Iterator itr = items.iterator();
            while (itr.hasNext()) {
                FileItem item = (FileItem) itr.next();
                if (item.isFormField()) {
                    String name = item.getFieldName();
                    System.out.println("name: " + name);
                    String value = item.getString();
                    System.out.println("value: " + value);
                } else {
                    try {
                        /*
                                    *note to self:
                                    *this isn't my code
                                    *i need to understand how he randomly name the files
                                    **
                                    */
                        String itemName = item.getName();
                        Random generator = new Random();
                        int r = Math.abs(generator.nextInt());

                        String reg = "[.*]";
                        String replacingtext = "";
                        System.out.println("Text before replacing is:-" + itemName);
                        Pattern pattern = Pattern.compile(reg);
                        Matcher matcher = pattern.matcher(itemName);
                        StringBuffer buffer = new StringBuffer();

                        while (matcher.find()) {
                            matcher.appendReplacement(buffer, replacingtext);
                        }
                        int IndexOf = itemName.indexOf(".");
                        String domainName = itemName.substring(IndexOf);
                        System.out.println("domainName: " + domainName);

                        String finalimage = buffer.toString() + "_" + r + domainName;
                        System.out.println("Final Image===" + finalimage);

                        File savedFile = new File(
                                "/home/ayoub/NetBeansProjects/pricomp/web/images/" + finalimage);
                        item.write(savedFile);

                        try ( //print elements from excel file
                                FileInputStream file = new FileInputStream(new File(
                                        "/home/ayoub/NetBeansProjects/pricomp/web/images/" + finalimage))
                        //Get the workbook instance for XLS file
                        ) {
                            XSSFWorkbook workbook;
                            workbook = new XSSFWorkbook(file);

                            //Get first sheet from the workbook
                            XSSFSheet sheet = workbook.getSheetAt(0);

                            //Get iterator to all the rows in current sheet
                            Iterator<Row> rowIterator = sheet.iterator();
                            Row firstRow = rowIterator.next();
                            //          int idprod=4;
                            //get the number of lines in table first
                            String sql = "SELECT COUNT(*) AS number FROM PRODUCTS";

                            //out.println();
                            Statement stat = cnx.createStatement();

                            ResultSet rs = stat.executeQuery(sql);
                            rs.next();
                            int idprod = Integer.parseInt(rs.getString("number"));
                            //System.out.println(idprod);

                            while (rowIterator.hasNext()) {
                                idprod++;
                                Row row = rowIterator.next();

                                //For each row, iterate through each columns
                                Iterator<Cell> cellIterator = row.cellIterator();

                                Cell cell = cellIterator.next();
                                String name = cell.getStringCellValue();
                                cell = cellIterator.next();
                                float price = (float) cell.getNumericCellValue();

                                String insertTableSQL = "INSERT INTO products VALUES (?,?,?,?)";
                                //String sql="insert into SUPPLIERS  values("+request.getParameter("name")+","+request.getParameter("adresse")+","+request.getParameter("email")+","+request.getParameter("password")+","+tel+")";
                                PreparedStatement pst = cnx.prepareStatement(insertTableSQL);
                                //int num=Integer.parseInt(request.getParameter("numero"));

                                int idsupp = Integer.parseInt(session.getAttribute("idsupp").toString());
                                //pst.setInt(1,idprod);
                                //pst.setString(1, );
                                pst.setInt(1, idprod);
                                pst.setString(2, name);
                                pst.setFloat(3, price);
                                pst.setInt(4, idsupp);
                                int rset = pst.executeUpdate();

                                //System.out.println("");

                            }
                        }

                        /*
                            Statement stat=cnx.createStatement();
                                 
                        String sqll="insert into suppliers (adressesupp,nomsupp) values('11','hatim')";
                           int rss=stat.executeUpdate(sqll);*/
                        //name=&adresse=&telephone=&password=&email=&description=
                        //String telephone=request.getParameter("telephone");
                        //int tel=Integer.parseInt(telephone);

                        // int rs=stat.executeUpdate("insert into suppliers (nomsupp,adressesup,emailsupp) values ('z','g','g')");
                        // int rs=stat.executeUpdate(sql);

                        response.sendRedirect("public/pages/supplier/homeSupplier.html");

                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch (Exception e) {
            out.println(e);
        }

    }
}

From source file:TaskAdder.java

static void addtask(String task, String comments, String username, String excel_path) {
    DateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm:ss");
    Date dateobj = new Date();

    FileInputStream file = null;//  w  w  w  .j a v a2 s .c o  m
    try {
        file = new FileInputStream(new File(excel_path));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }

    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(file);
    } catch (IOException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }

    //Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);
    int rownum = sheet.getLastRowNum();
    //Blank workbook
    Row row = sheet.createRow(rownum + 1);

    Cell usernameCell = row.createCell(0);
    usernameCell.setCellValue(username);
    Cell taskCell = row.createCell(1);
    taskCell.setCellValue(task);
    Cell statusCell = row.createCell(2);
    statusCell.setCellValue("In-Progress");
    Cell timestampCell = row.createCell(3);
    timestampCell.setCellValue(df.format(dateobj).toString());
    Cell commentsCell = row.createCell(5);
    commentsCell.setCellValue(comments);

    FileOutputStream out = null;
    try {
        out = new FileOutputStream(new File(excel_path));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        workbook.write(out);
    } catch (IOException ex) {
        Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        out.close();
    } catch (IOException ex) {
        Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:TaskAdder.java

static void addAllTask(String task, String comments, String username, String excel_path) {
    DateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm:ss");
    Date dateobj = new Date();

    FileInputStream file = null;//from   ww  w  .  j  a  v a 2s  .com
    try {
        file = new FileInputStream(new File(excel_path));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }

    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(file);
    } catch (IOException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }

    //Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(1);
    int rownum = sheet.getLastRowNum();
    //Blank workbook
    Row row = sheet.createRow(rownum + 1);

    Cell usernameCell = row.createCell(0);
    usernameCell.setCellValue(username);
    Cell taskCell = row.createCell(1);
    taskCell.setCellValue(task);
    Cell statusCell = row.createCell(2);
    statusCell.setCellValue("Task Created");
    Cell timestampCell = row.createCell(3);
    timestampCell.setCellValue(df.format(dateobj).toString());
    Cell commentsCell = row.createCell(4);
    commentsCell.setCellValue(comments);

    FileOutputStream out = null;
    try {
        out = new FileOutputStream(new File(excel_path));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        workbook.write(out);
    } catch (IOException ex) {
        Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        out.close();
    } catch (IOException ex) {
        Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex);
    }
}