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

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

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

Introduction

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

Prototype

@Override
void close() throws IOException;

Source Link

Document

Close the underlying input resource (File or Stream), from which the Workbook was read.

Usage

From source file:com.gsecs.GSECSFrame.java

private void importStudentCourseMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importStudentCourseMenuItemActionPerformed
    // TODO add your handling code here:
    JFileChooser jf = new JFileChooser();
    jf.setDialogTitle("Please select a excel File to import");
    int result = jf.showOpenDialog(null);
    if (result == JFileChooser.APPROVE_OPTION) {
        String filePath = jf.getSelectedFile().getAbsolutePath();

        try {/*  www. j  a v a 2s.c om*/
            FileInputStream fis = new FileInputStream(filePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            List<StudentCourseBean> studentCourseBeansList = new ArrayList<StudentCourseBean>();

            while (iterator.hasNext()) {
                Row nextRow = iterator.next();
                if (nextRow.getRowNum() != 0) {
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    StudentCourseBean studentBean = new StudentCourseBean();

                    while (cellIterator.hasNext()) {
                        Cell nextCell = cellIterator.next();
                        int columnIndex = nextCell.getColumnIndex();

                        switch (columnIndex) {
                        case 0:
                            studentBean.setStudentId(getCellValue(nextCell).toString().replace(".0", ""));
                            break;
                        case 1:
                            studentBean.setCourseId((String) getCellValue(nextCell));
                            break;
                        case 2:
                            studentBean.setCourseDesc((String) getCellValue(nextCell));
                            break;
                        case 3:
                            studentBean.setTerm((String) getCellValue(nextCell));
                            break;
                        case 4:
                            studentBean.setGrade((String) getCellValue(nextCell));
                            break;

                        }

                    }
                    studentCourseBeansList.add(studentBean);
                }

            }
            workbook.close();
            fis.close();

            mainPanel.removeAll();
            mainPanel.repaint();
            StudentModel model = new StudentModel();
            StudentCourseJInternalFrame studentFrame = new StudentCourseJInternalFrame();
            studentFrame.getStudentMsgLabel().setText("");
            studentFrame.getAddStudentCourseButton().setVisible(true);
            studentFrame.getUpdateStudentCourseButton().setVisible(false);
            studentFrame.getDeleteStudentCourseButton().setVisible(false);
            //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (StudentCourseBean studentBean : studentCourseBeansList) {
                try {
                    model.addStudentCourse(studentBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            studentFrame.loadDataIntoJTable();
            mainPanel.add(studentFrame);
            studentFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }
}

From source file:com.gsecs.GSECSFrame.java

private void importcourseSectionMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importcourseSectionMenuItemActionPerformed
    // TODO add your handling code here:
    JFileChooser jf = new JFileChooser();
    jf.setDialogTitle("Please select a excel File to import");
    int result = jf.showOpenDialog(null);
    if (result == JFileChooser.APPROVE_OPTION) {
        String filePath = jf.getSelectedFile().getAbsolutePath();

        try {//  w  ww .  j  ava 2 s. c  o  m
            FileInputStream fis = new FileInputStream(filePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            List<CourseSectionBean> courseSectionBeansList = new ArrayList<CourseSectionBean>();

            while (iterator.hasNext()) {
                Row nextRow = iterator.next();
                if (nextRow.getRowNum() != 0) {
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    CourseSectionBean courseSectionBean = new CourseSectionBean();

                    while (cellIterator.hasNext()) {
                        Cell nextCell = cellIterator.next();
                        int columnIndex = nextCell.getColumnIndex();

                        switch (columnIndex) {
                        case 0:
                            courseSectionBean
                                    .setSectionNumber(getCellValue(nextCell).toString().replace(".0", ""));
                            break;
                        case 1:
                            courseSectionBean.setCourse((String) getCellValue(nextCell));
                            break;
                        case 2:
                            courseSectionBean.setFaculty((String) getCellValue(nextCell));
                            break;

                        }

                    }
                    courseSectionBeansList.add(courseSectionBean);
                }

            }
            workbook.close();
            fis.close();

            mainPanel.removeAll();
            mainPanel.repaint();
            CourseSectionModel model = new CourseSectionModel();
            CourseSectionJInternalFrame courseSectionFrame = new CourseSectionJInternalFrame();
            courseSectionFrame.getCourseSectionMsgLabel().setText("");
            courseSectionFrame.getAddCourseSectionButton().setVisible(true);
            courseSectionFrame.getUpdateCourseSectionButton().setVisible(false);
            courseSectionFrame.getDeleteCourseSectionButton().setVisible(false);
            //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (CourseSectionBean courseSectionBean : courseSectionBeansList) {
                try {
                    model.addCourseSection(courseSectionBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            courseSectionFrame.loadDataIntoJTable();
            mainPanel.add(courseSectionFrame);
            courseSectionFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }
}

From source file:com.gsecs.GSECSFrame.java

private void importSemesterMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importSemesterMenuItemActionPerformed
    // TODO add your handling code here:
    JFileChooser jf = new JFileChooser();
    jf.setDialogTitle("Please select a excel File to import");
    int result = jf.showOpenDialog(null);
    if (result == JFileChooser.APPROVE_OPTION) {
        String filePath = jf.getSelectedFile().getAbsolutePath();

        try {//from   w  w  w. ja  va2 s .co m
            FileInputStream fis = new FileInputStream(filePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            List<SemesterBean> semesterBeansList = new ArrayList<SemesterBean>();

            while (iterator.hasNext()) {
                Row nextRow = iterator.next();
                if (nextRow.getRowNum() != 0) {
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    SemesterBean semesterBean = new SemesterBean();

                    while (cellIterator.hasNext()) {
                        Cell nextCell = cellIterator.next();
                        int columnIndex = nextCell.getColumnIndex();

                        switch (columnIndex) {
                        case 0:
                            semesterBean.setSemester((String) getCellValue(nextCell));
                            break;
                        case 1:
                            String[] stringDate = getCellValue(nextCell).toString().split("/");
                            semesterBean
                                    .setStartDate(stringDate[0] + "/" + stringDate[1] + "/20" + stringDate[2]);
                            break;
                        case 2:
                            String[] stringDate2 = getCellValue(nextCell).toString().split("/");
                            semesterBean
                                    .setEndDate(stringDate2[0] + "/" + stringDate2[1] + "/20" + stringDate2[2]);
                            break;

                        }

                    }
                    semesterBeansList.add(semesterBean);
                }

            }
            workbook.close();
            fis.close();

            mainPanel.removeAll();
            mainPanel.repaint();
            SemesterModel model = new SemesterModel();
            SemesterJInternalFrame semesterFrame = new SemesterJInternalFrame();
            semesterFrame.getSemesterMsgLabel().setText("");
            semesterFrame.getAddSemesterButton().setVisible(true);
            semesterFrame.getUpdateSemesterButton().setVisible(false);
            semesterFrame.getDeleteSemesterButton().setVisible(false);
            //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (SemesterBean semesterBean : semesterBeansList) {
                try {
                    model.addSemester(semesterBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            semesterFrame.loadDataIntoJTable();
            mainPanel.add(semesterFrame);
            semesterFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }
}

From source file:com.gsecs.GSECSFrame.java

private void ImportUsersMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_ImportUsersMenuItemActionPerformed
    // TODO add your handling code here:

    JFileChooser jf = new JFileChooser();
    jf.setDialogTitle("Please select a excel File to import");
    int result = jf.showOpenDialog(null);
    if (result == JFileChooser.APPROVE_OPTION) {
        String filePath = jf.getSelectedFile().getAbsolutePath();

        try {//from  w  ww  .  j  a  v  a  2 s. co  m
            FileInputStream fis = new FileInputStream(filePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            List<UserBean> userBeansList = new ArrayList<UserBean>();

            while (iterator.hasNext()) {
                Row nextRow = iterator.next();
                if (nextRow.getRowNum() != 0) {
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    UserBean userBean = new UserBean();

                    while (cellIterator.hasNext()) {
                        Cell nextCell = cellIterator.next();
                        int columnIndex = nextCell.getColumnIndex();

                        switch (columnIndex) {
                        case 0:
                            userBean.setLoginId(getCellValue(nextCell).toString());
                            break;
                        case 1:
                            userBean.setPassword((String) getCellValue(nextCell));
                            break;
                        case 2:
                            userBean.setRole((String) getCellValue(nextCell));
                            break;

                        }

                    }
                    userBeansList.add(userBean);
                }

            }
            workbook.close();
            fis.close();

            mainPanel.removeAll();
            mainPanel.repaint();
            UserModel model = new UserModel();
            UserJInternalFrame userFrame = new UserJInternalFrame();
            userFrame.getUsersMsgLabel().setText("");
            userFrame.getUserRegister().setVisible(true);
            userFrame.getUserUpdateButton().setVisible(false);
            userFrame.getUserDeleteButton().setVisible(false);
            //userFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (UserBean userBean : userBeansList) {
                try {
                    model.registerUser(userBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            userFrame.loadDataIntoJTable();
            mainPanel.add(userFrame);
            userFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }
}

From source file:com.gsecs.GSECSFrame.java

private void ImportRoomMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_ImportRoomMenuItemActionPerformed
    // TODO add your handling code here:
    JFileChooser jf = new JFileChooser();
    jf.setDialogTitle("Please select a excel File to import");
    int result = jf.showOpenDialog(null);
    if (result == JFileChooser.APPROVE_OPTION) {
        String filePath = jf.getSelectedFile().getAbsolutePath();

        try {// w w w  . j  av a 2s  .c o  m
            FileInputStream fis = new FileInputStream(filePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            List<RoomBean> roomBeansList = new ArrayList<RoomBean>();

            while (iterator.hasNext()) {
                Row nextRow = iterator.next();
                if (nextRow.getRowNum() != 0) {
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    RoomBean roomBean = new RoomBean();

                    while (cellIterator.hasNext()) {
                        Cell nextCell = cellIterator.next();
                        int columnIndex = nextCell.getColumnIndex();

                        switch (columnIndex) {
                        case 0:
                            roomBean.setRoomNumber(getCellValue(nextCell).toString());
                            break;
                        case 1:
                            roomBean.setCourseCode((String) getCellValue(nextCell));
                            break;
                        case 2:
                            roomBean.setCapacity((String) getCellValue(nextCell));
                            break;

                        }

                    }
                    roomBeansList.add(roomBean);
                }

            }
            workbook.close();
            fis.close();

            mainPanel.removeAll();
            mainPanel.repaint();
            RoomModel model = new RoomModel();
            RoomJInternalFrame roomFrame = new RoomJInternalFrame();
            roomFrame.getRoomMsgLabel().setText("");
            roomFrame.getAddRoomButton().setVisible(true);
            roomFrame.getUpdateRoomButton().setVisible(false);
            roomFrame.getDeleteRoomButton().setVisible(false);
            //userFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (RoomBean roomBean : roomBeansList) {
                try {
                    model.addRoom(roomBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            roomFrame.loadDataIntoJTable();
            mainPanel.add(roomFrame);
            roomFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }
}

From source file:com.guardias.excel.CalendarToExcel.java

License:Apache License

public static void GenerateExcel(String RutaFile, Calendar calendar, String JSONContenidos,
        Medico AdministratorUser) throws IOException {

    Guardias[] lGuardias;/*from  ww  w.  j  a va 2s .  c o m*/

    Gson gson = new GsonBuilder().create();

    lGuardias = gson.fromJson(JSONContenidos, Guardias[].class);

    boolean xlsx = true;
    int year = calendar.get(Calendar.YEAR);
    int month = calendar.get(Calendar.MONTH);

    DateFormat _format = new SimpleDateFormat("yyyy-MM-dd");

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    calendar.set(Calendar.MONTH, month);
    calendar.set(Calendar.DAY_OF_MONTH, 1);

    calendar.setFirstDayOfWeek(Calendar.MONDAY);
    //create a sheet for each month
    Sheet sheet = wb.createSheet(_format.format(calendar.getTime()));

    CellStyle styleBORDER = wb.createCellStyle();
    styleBORDER.setBorderRight(CellStyle.BORDER_THICK);
    styleBORDER.setBorderBottom(CellStyle.BORDER_THICK);
    styleBORDER.setBorderTop(CellStyle.BORDER_THICK);
    styleBORDER.setBorderLeft(CellStyle.BORDER_THICK);
    styleBORDER.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    //

    //turn off gridlines
    sheet.setDisplayGridlines(true);
    sheet.autoSizeColumn(0);
    sheet.setPrintGridlines(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //header with month titles
    Row monthRow = sheet.createRow(1);
    Font fontH = wb.createFont();
    CellStyle CStyleH = wb.createCellStyle();
    CStyleH.setBorderRight(CellStyle.BORDER_THICK);
    CStyleH.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    fontH.setBold(true);
    CStyleH.setFont(fontH);
    for (int i = 0; i < days.length; i++) {

        Cell monthCell = monthRow.createCell(i);

        monthCell.setCellStyle(CStyleH);
        monthCell.setCellValue(days[i]);
        sheet.autoSizeColumn(i);

    }

    int cnt = 1, day = 1;
    int rownum = 2;
    for (int j = 0; j < 6; j++) {
        Row row = sheet.createRow(rownum++);
        Row rowGuardias;
        boolean bRowsCreated = false;

        // row.setHeightInPoints(100);
        for (int i = 0; i < days.length; i++) {
            Cell dayCell_1 = row.createCell(i);
            //  Cell dayCell_2 = row.createCell(i*2 + 1);

            int currentDayOfWeek = (calendar.get(Calendar.DAY_OF_WEEK) + 7 - calendar.getFirstDayOfWeek()) % 7;
            //int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);  
            if (cnt > currentDayOfWeek && calendar.get(Calendar.MONTH) == month) {

                Font font = wb.createFont();
                CellStyle CStyle = wb.createCellStyle();
                short colorI = HSSFColor.AQUA.index; // presencia
                //font.set(colorI);
                CStyle.setFillForegroundColor(colorI);
                CStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //CStyle.setBorderBottom( colorBorder);
                CStyle.setBorderRight(CellStyle.BORDER_THICK);
                CStyle.setBorderBottom(CellStyle.BORDER_THICK);
                CStyle.setBorderTop(CellStyle.BORDER_THICK);
                CStyle.setBorderLeft(CellStyle.BORDER_THICK);
                CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                //CStyle.setFont(font);

                dayCell_1.setCellValue(day);
                dayCell_1.setCellStyle(CStyle);

                sheet.autoSizeColumn(i);

                String _Dia = _format.format(calendar.getTime());

                int DataRowCont = 1; // esto sirve para coger la fila de los datos de cada dia
                for (int d = 0; d < lGuardias.length; d++) {

                    Guardias oGuardias = lGuardias[d];
                    if (oGuardias.getDiaGuardia().equals(_Dia)) {

                        if (!bRowsCreated)
                            rowGuardias = sheet.createRow(rownum++);
                        else
                            rowGuardias = sheet.getRow(row.getRowNum() + DataRowCont);

                        Cell dayCell_1_GUARDIAS = rowGuardias.createCell(i);
                        //    Cell dayCell_2_GUARDIAS = rowGuardias.createCell(i*2 + 1);

                        List<Medico> _lMedico = MedicoDBImpl.getMedicos(oGuardias.getIdMedico(),
                                AdministratorUser.getServicioId());

                        Medico _oMedico = _lMedico.get(0);

                        font = wb.createFont();
                        CStyle = wb.createCellStyle();
                        // PRESENCIA 
                        // LOCALIZADA
                        //XSSFRichTextString richString = new HSSFRichTextString(_oMedico.getApellidos() + " " + _oMedico.getNombre());
                        colorI = HSSFColor.LIGHT_ORANGE.index; // presencia                        
                        if (oGuardias.getTipo().equals(Util.eTipoGuardia.LOCALIZADA.toString().toLowerCase()))
                            colorI = HSSFColor.GREEN.index;
                        else if (oGuardias.getTipo()
                                .equals(Util.eTipoGuardia.REFUERZO.toString().toLowerCase()))
                            colorI = HSSFColor.BLUE.index;
                        else if (oGuardias.getTipo().equals("")) // residente
                            colorI = HSSFColor.RED.index;

                        font.setColor(colorI);

                        CStyle.setFont(font);
                        //CStyle.setBorderBottom( colorBorder);
                        CStyle.setBorderRight(CellStyle.BORDER_THICK);
                        CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());

                        dayCell_1_GUARDIAS.setCellValue(_oMedico.getApellidos() + " " + _oMedico.getNombre()
                                + "[" + _oMedico.getIDMEDICO() + "]");
                        dayCell_1_GUARDIAS.setCellStyle(CStyle);

                        DataRowCont++;

                    }

                }
                bRowsCreated = true;

                //      dayCell_1_GUARDIAS.setCellValue(TextoGuardias.toString());

                calendar.set(Calendar.DAY_OF_MONTH, ++day);

                /*if(i == 0 || i == days.length-1) {
                    dayCell_1.setCellStyle(styles.get("weekend_left"));
                    dayCell_2.setCellStyle(styles.get("weekend_right"));
                } else {
                    dayCell_1.setCellStyle(styles.get("workday_left"));
                    dayCell_2.setCellStyle(styles.get("workday_right"));
                }
                } else {
                dayCell_1.setCellStyle(styles.get("grey_left"));
                dayCell_2.setCellStyle(styles.get("grey_right"));*/
            }
            cnt++;
        }
        if (calendar.get(Calendar.MONTH) > month)
            break;
    }
    //  }

    // Write the output to a file
    String file = RutaFile;
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    wb.close();

}

From source file:com.ifeng.vdn.ip.repository.app.IPCheckApp.java

License:Apache License

public static void main(String[] args) {
    Workbook wb = null;
    PrintWriter pw = null;//w  ww. ja  va2  s .  co m
    try {

        pw = new PrintWriter(new FileOutputStream("src/test/resources/data/CDN_BAD.txt"), true);

        AliIPAddressChecker ipChecker = new AliIPAddressChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/CDN_BAD.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();
        Cell ipCell = null;
        //Cell locCell = null;

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

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            ipCell = row.getCell(0);

            ips.add(ipCell.getStringCellValue());
        }

        for (String ip : ips) {
            AliIPBean bean = (AliIPBean) ipChecker.ipcheck(ip);
            pw.println(ip + "-" + bean.getIpString());
        }

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        if (pw != null) {
            pw.flush();
            pw.close();
        }
    }

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliBatchIPAddressCheckerTest.java

License:Apache License

@Test
public void wrap() {
    Workbook wb = null;
    try {//from   w ww. jav  a  2 s.  c o m
        BatchIPAddressChecker<AliIPBean> checker = new AliBatchIPAddressChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/ip_18.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();
        Cell ipCell = null;
        Cell locCell = null;

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

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            ipCell = row.getCell(1);

            ips.add(ipCell.getStringCellValue());
        }

        List<AliIPBean> locations = checker.check(ips);
        String location = "";

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            locCell = row.getCell(3);

            try {
                location = locations.get(i).getIpString();
            } catch (Exception e) {
                location = "ERROR";
            }

            locCell.setCellValue(location);
        }

        /*if(location.length() == (totalRows - 1)){
                   
        }else {
           log.error("Batch executed error");
           throw new RuntimeException("Batch executed error. Some one IP location not be checked.");
        }*/

        wb.write(new FileOutputStream("src/test/resources/data/ip_18_Alibaba.xlsx"));

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
    }

}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliIPAddressCheckerTest.java

License:Apache License

public void wrap(String input, String output) {
    Workbook wb = null;
    PrintWriter pw = null;/*from www  .j av a2s.  c  o  m*/
    try {
        pw = new PrintWriter(output, "UTF-8");

        IPAddressChecker checker = new AliIPAddressChecker();
        wb = WorkbookFactory.create(new FileInputStream(input));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell locCell = null;

        String location = "";
        String ipString = "";

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            locCell = row.getCell(3);

            try {
                ipString = ipCell.getStringCellValue();
                AliIPBean ip = (AliIPBean) checker.ipcheck(ipString);
                location = ip.getIpString();
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                location = "ERROR";
            } finally {
                //locCell.setCellValue(location);

                pw.append(ipString + " " + location);
                pw.println();
            }
        }

        wb.write(new FileOutputStream(output));

    } catch (InvalidFormatException | IOException e) {
        log.error(e.getMessage(), e);
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        pw.flush();
        pw.close();
    }
}

From source file:com.ifeng.vdn.ip.repository.service.impl.IPAddressBaiduCheckerTest.java

License:Apache License

@Test
public void wrap() {

    Workbook wb = null;

    try {/* w ww . ja v a2s.  co m*/
        IPAddressBaiduChecker checker = new IPAddressBaiduChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/ip_16.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell locCell = null;

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            locCell = row.getCell(3);

            BaiduIPBean ip = (BaiduIPBean) checker.ipcheck(ipCell.getStringCellValue());
            List<IPLocation> locations = ip.getData();

            if (locations != null && locations.size() > 0) {

                try {
                    locCell.setCellValue(locations.get(0).getLocation());
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        }

        wb.write(new FileOutputStream("src/test/resources/data/ip_16_loc.xlsx"));

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
    }

}