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.adobe.ags.curly.controller.DataImporterController.java

License:Apache License

private void openWorkbook(Workbook workbook) throws IOException {
    try {/*from  w ww. j a v a2s  .co m*/
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            worksheetSelector.getItems().add(workbook.getSheetName(i));
        }

        sheetReader = (String sheetName) -> readSheet(workbook.getSheet(sheetName));

        Platform.runLater(() -> worksheetSelector.getSelectionModel().selectFirst());
    } finally {
        workbook.close();
    }
}

From source file:com.bawan.vims.common.util.ExcelHelper.java

/**
 * ?excel/*from w  w w.  j a  va  2  s  .  c o  m*/
 */
public static Map<String, List<Map<String, Object>>> parserExcel(String excelFilePath) {

    Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();

    InputStream in = null;
    Workbook wb = null;
    try {
        File excelFile = new File(excelFilePath);
        if (excelFile == null || !excelFile.exists()) {
            logger.error("ExcelHelper[parserExcel]  excel file don't exist!");
            return null;
        }
        in = new FileInputStream(excelFile);

        String suffix = excelFilePath.substring(excelFilePath.lastIndexOf("."));
        if (!".xls".equals(suffix) && !".xlsx".equals(suffix)) {
            logger.error("ExcelHelper[parserExcel]  file suffix do'not match[*.xls, *.xlsx]! ");
            return null;
        } /*else if ("xls".equals(suffix)){
           wb = new HSSFWorkbook(in);
          } else if("xlsx".equals(suffix)) {
           wb = new XSSFWorkbook(in);
          }*/

        wb = WorkbookFactory.create(in); // POI 3.8?, ??xls?xlsx?
        int sheetSize = 0;

        while (true) {
            Sheet sheet = wb.getSheetAt(sheetSize);
            if (sheet == null) {
                break;
            }
            String sheetName = sheet.getSheetName();

            List<Map<String, Object>> sheetContent = new ArrayList<Map<String, Object>>();
            for (int rowNum = 521; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                Map<String, Object> rowMap = new HashMap<String, Object>();
                StringBuffer rowContent = new StringBuffer(
                        "insert into vims_car_market_spread_data(ID, MANUFACTURER, BRAND, VEHICEL_LEVEL, VEHICEL, YEAR, MONTH, AREA, SALE_SIZE, PRODUCTION_SIZE, LICENSED_SIZE, TOTAL_FEE, INTERNET_FEE, TV_FEE, MAGAZINE_FEE, NEWSPAPER_FEE, RADIO_FEE, METRO_FEE, OUTDOORS_FEE) values(");
                rowContent.append("'").append(IDGenerator.getID(32)).append("',");
                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    //                  if (cell == null) {
                    //                     rowMap.put(rowNum + "_" + cellIndex, null);
                    //                  } else {
                    //                     rowMap.put(rowNum + "_" + cellIndex, cell.toString());
                    //                  }
                    if (cellIndex == 2) {
                        if (cell == null) {
                            rowContent.append(0).append(",");
                        } else if ("mpv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(1).append(",");
                        } else if ("suv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(2).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(3).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(4).append(",");
                        } else if ("?".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(5).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(6).append(",");
                        }
                        continue;
                    }

                    if (cell == null || cell.toString().trim().length() == 0) {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("default").append(",");
                        } else {
                            rowContent.append("0").append(",");
                        }
                    } else {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("'").append(cell.toString()).append("',");
                        } else if (cellIndex == 4 || cellIndex == 5 || cellIndex == 7 || cellIndex == 8
                                || cellIndex == 9) {
                            String value = cell.toString().substring(0, cell.toString().indexOf("."));
                            rowContent.append(Integer.valueOf(value)).append(",");
                        } else {
                            rowContent.append(cell.toString()).append(",");
                        }
                    }
                }
                String sql = rowContent.toString();
                sql = sql.substring(0, sql.length() - 1);
                sql += ");";
                System.out.println(sql);
                sheetContent.add(rowMap);
            }

            result.put(sheetName, sheetContent);
            sheetSize++;
        }

    } catch (Exception e) {
        e.printStackTrace();
        logger.error("ExcelHelper[parserExcel] excel file not found! error:" + e.getMessage(), e);
    } finally {
        try {
            if (wb != null) {
                wb.close();
                wb = null;
            }
        } catch (IOException e1) {
        }

        try {
            if (in != null) {
                in.close();
                in = null;
            }
        } catch (IOException e) {
        }
    }

    return result;
}

From source file:com.cisco.ca.cstg.pdi.utils.Util.java

public static void json2Excel(OutputStream output, String json, String[] properties, String[] columnsNames)
        throws IOException {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    Row header = sheet.createRow(0);/*  www. ja  va  2  s  . co m*/

    for (int i = 0; i < columnsNames.length; i++) {
        String string = columnsNames[i];
        Cell cell = header.createCell(i);
        RichTextString text = new XSSFRichTextString(string);
        cell.setCellValue(text);
    }

    LOGGER.info("Writing on workbook.");
    try {
        ObjectMapper mapper = new ObjectMapper();
        JsonNode jsonNode = mapper.readTree(json);

        int i = 0;
        for (JsonNode jsonNode2 : jsonNode) {
            Row row = sheet.createRow(++i);

            for (int j = 0; j < properties.length; j++) {
                String string = properties[j];
                Cell cell = row.createCell(j);
                RichTextString text = new XSSFRichTextString(jsonNode2.get(string).getTextValue());
                cell.setCellValue(text);
            }
        }
    } catch (JsonProcessingException e) {
        LOGGER.error(e.getMessage(), e);
    }

    workbook.write(output);
    workbook.close();
    LOGGER.info("Written on workbook.");
}

From source file:com.cn.led.DrawFromExcel.java

public static void drawExcelToPNG(String excelFilePath, String pngFilePath, int[] fromIndex, int[] toIndex)
        throws Exception {
    // ???/*  w ww  .  j  av a2  s . com*/
    //        int[] fromIndex = {0, 0};
    //        int[] toIndex = {1, 5};

    int imageWidth = 0;
    int imageHeight = 0;

    File file = new File(excelFilePath);
    Workbook wb = WorkbookFactory.create(file);
    Sheet sheet = wb.getSheetAt(0);
    List<CellRangeAddress> rangeAddress = sheet.getMergedRegions(); // ?sheet????

    // ????
    int rowSum = sheet.getPhysicalNumberOfRows();
    int colSum = sheet.getRow(0).getPhysicalNumberOfCells();
    if (fromIndex[0] > rowSum || fromIndex[0] > toIndex[0] || toIndex[0] > rowSum) {
        throw new Exception("the rowIndex of the area is wrong!");
    }
    if (fromIndex[1] > colSum || fromIndex[1] > toIndex[1] || toIndex[1] > colSum) {
        throw new Exception("the colIndex of the area is wrong!");
    }

    // ?Cell???
    int rowSize = toIndex[0] + 1;
    int colSize = toIndex[1] + 1;

    // ?????
    UserCell[][] cells = new UserCell[rowSize][colSize];
    int[] rowPixPos = new int[rowSize + 1];
    rowPixPos[0] = 0;
    int[] colPixPos = new int[colSize + 1];
    colPixPos[0] = 0;
    for (int i = 0; i < rowSize; i++) {

        for (int j = 0; j < colSize; j++) {

            cells[i][j] = new UserCell();
            cells[i][j].setCell(sheet.getRow(i).getCell(j));
            cells[i][j].setRow(i);
            cells[i][j].setCol(j);
            boolean ifShow = (i >= fromIndex[0]) && (j >= fromIndex[1]); //?
            ifShow = ifShow && !(sheet.isColumnHidden(j) || sheet.getRow(i).getZeroHeight()); //????
            cells[i][j].setShow(ifShow);

            // 
            float widthPix = (!ifShow ? 0 : sheet.getColumnWidthInPixels(j)); // ???0
            if (i == fromIndex[0]) {
                imageWidth += widthPix;
            }
            colPixPos[j + 1] = (int) (widthPix + colPixPos[j]);

        }

        // 
        boolean ifShow = (i >= fromIndex[0]); //?
        ifShow = ifShow && !sheet.getRow(i).getZeroHeight(); //????
        float heightPoint = !ifShow ? 0 : sheet.getRow(i).getHeightInPoints(); // ???0
        imageHeight += heightPoint;
        rowPixPos[i + 1] = (int) (heightPoint * 96 / 80) + rowPixPos[i];
    }

    imageHeight = imageHeight * 96 / 80 + 2;

    wb.close();

    List<Grid> grids = new ArrayList<>();
    for (int i = 0; i < rowSize; i++) {
        for (int j = 0; j < colSize; j++) {
            Grid grid = new Grid();
            // ??
            grid.setX(colPixPos[j]);
            grid.setY(rowPixPos[i]);
            grid.setWidth(colPixPos[j + 1] - colPixPos[j]);
            grid.setHeight(rowPixPos[i + 1] - rowPixPos[i]);
            grid.setRow(cells[i][j].getRow());
            grid.setCol(cells[i][j].getCol());
            grid.setShow(cells[i][j].isShow());

            // ???
            int[] isInMergedStatus = isInMerged(grid.getRow(), grid.getCol(), rangeAddress);

            if (isInMergedStatus[0] == 0 && isInMergedStatus[1] == 0) {
                // ???????
                continue;
            } else if (isInMergedStatus[0] != -1 && isInMergedStatus[1] != -1) {
                // ??????                 
                int lastRowPos = isInMergedStatus[0] > rowSize - 1 ? rowSize - 1 : isInMergedStatus[0];
                int lastColPos = isInMergedStatus[1] > colSize - 1 ? colSize - 1 : isInMergedStatus[1];

                grid.setWidth(colPixPos[lastColPos + 1] - colPixPos[j]);
                grid.setHeight(rowPixPos[lastRowPos + 1] - rowPixPos[i]);

            }

            // ?
            CellStyle cs = cells[i][j].getCell().getCellStyle();
            if (cs.getFillPattern() == CellStyle.SOLID_FOREGROUND) {
                grid.setBgColor(cells[i][j].getCell().getCellStyle().getFillForegroundColorColor());
            }

            // 
            String strCell = "";
            switch (cells[i][j].getCell().getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cells[i][j].getCell().getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cells[i][j].getCell().getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                try {
                    strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                } catch (IllegalStateException e) {
                    strCell = String.valueOf(cells[i][j].getCell().getRichStringCellValue());
                }
                break;
            default:
                strCell = "";
            }
            //                System.out.println("strCell:" + strCell);

            if (cells[i][j].getCell().getCellStyle().getDataFormatString().contains("0.00%")) {
                try {
                    double dbCell = Double.valueOf(strCell);
                    strCell = new DecimalFormat("#.00").format(dbCell * 100) + "%";
                } catch (NumberFormatException e) {
                }
            }

            grid.setText(strCell.matches("\\w*\\.0") ? strCell.substring(0, strCell.length() - 2) : strCell);

            grids.add(grid);
        }
    }

    BufferedImage image = new BufferedImage(imageWidth, imageHeight + 1, BufferedImage.TYPE_INT_RGB);
    Graphics2D g2d = image.createGraphics();
    // 
    //g2d.setRenderingHint(SunHints.KEY_ANTIALIASING, SunHints.VALUE_ANTIALIAS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIASING, SunHints.VALUE_TEXT_ANTIALIAS_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_STROKE_CONTROL, SunHints.VALUE_STROKE_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIAS_LCD_CONTRAST, 140);
    //g2d.setRenderingHint(SunHints.KEY_FRACTIONALMETRICS, SunHints.VALUE_FRACTIONALMETRICS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_RENDERING, SunHints.VALUE_RENDER_DEFAULT);

    g2d.setColor(Color.white);
    g2d.fillRect(0, 0, imageWidth, imageHeight + 1);

    // 
    Iterator<Grid> iterable = grids.iterator();
    while (iterable.hasNext()) {
        Grid g = iterable.next();
        if (!g.isShow()) {
            continue;
        }

        // 
        g2d.setColor(g.getBgColor() == null ? Color.black : g.getBgColor());
        g2d.fillRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // 
        g2d.setColor(Color.red);
        g2d.setStroke(new BasicStroke(1));
        g2d.drawRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // ,
        g2d.setColor(g.getFtColor());

        Font font = g.getFont();
        FontMetrics fm = g2d.getFontMetrics(font);
        int strWidth = fm.stringWidth(g.getText());// ??
        g2d.setFont(font);

        g2d.drawString(g.getText(), g.getX() + (g.getWidth() - strWidth) / 2,
                g.getY() + (g.getHeight() - font.getSize()) / 2 + font.getSize());
    }

    g2d.dispose();
    ImageIO.write(image, "png", new File(pngFilePath));
    //        BMPWriter.write(image, new File(pngFilePath));
    System.out.println("Output to png file Success!");
}

From source file:com.cn.led.DrawFromExcel.java

public static void drawExcelToBMP(String excelFilePath, String bmpFilePath, int[] fromIndex, int[] toIndex)
        throws Exception {
    // ???/*from  w w w . jav  a 2s  .  com*/
    //        int[] fromIndex = {0, 0};
    //        int[] toIndex = {1, 5};

    int imageWidth = 0;
    int imageHeight = 0;

    Workbook wb = WorkbookFactory.create(new File(excelFilePath));
    Sheet sheet = wb.getSheetAt(0);
    List<CellRangeAddress> rangeAddress = sheet.getMergedRegions(); // ?sheet????

    // ????
    int rowSum = sheet.getPhysicalNumberOfRows();
    int colSum = sheet.getRow(0).getPhysicalNumberOfCells();
    if (fromIndex[0] > rowSum || fromIndex[0] > toIndex[0] || toIndex[0] > rowSum) {
        throw new Exception("the rowIndex of the area is wrong!");
    }
    if (fromIndex[1] > colSum || fromIndex[1] > toIndex[1] || toIndex[1] > colSum) {
        throw new Exception("the colIndex of the area is wrong!");
    }

    // ?Cell???
    int rowSize = toIndex[0] + 1;
    int colSize = toIndex[1] + 1;

    // ?????
    UserCell[][] cells = new UserCell[rowSize][colSize];
    int[] rowPixPos = new int[rowSize + 1];
    rowPixPos[0] = 0;
    int[] colPixPos = new int[colSize + 1];
    colPixPos[0] = 0;
    for (int i = 0; i < rowSize; i++) {

        for (int j = 0; j < colSize; j++) {

            cells[i][j] = new UserCell();
            cells[i][j].setCell(sheet.getRow(i).getCell(j));
            cells[i][j].setRow(i);
            cells[i][j].setCol(j);
            boolean ifShow = (i >= fromIndex[0]) && (j >= fromIndex[1]); //?
            ifShow = ifShow && !(sheet.isColumnHidden(j) || sheet.getRow(i).getZeroHeight()); //????
            cells[i][j].setShow(ifShow);

            // 
            float widthPix = (!ifShow ? 0 : sheet.getColumnWidthInPixels(j)); // ???0
            if (i == fromIndex[0]) {
                imageWidth += widthPix;
            }
            colPixPos[j + 1] = (int) (widthPix + colPixPos[j]);

        }

        // 
        boolean ifShow = (i >= fromIndex[0]); //?
        ifShow = ifShow && !sheet.getRow(i).getZeroHeight(); //????
        float heightPoint = !ifShow ? 0 : sheet.getRow(i).getHeightInPoints(); // ???0
        imageHeight += heightPoint;
        rowPixPos[i + 1] = (int) (heightPoint * 96 / 80) + rowPixPos[i];
    }

    imageHeight = imageHeight * 96 / 80 + 2;
    //        imageWidth = imageWidth;

    wb.close();

    List<Grid> grids = new ArrayList<>();
    for (int i = 0; i < rowSize; i++) {
        for (int j = 0; j < colSize; j++) {
            Grid grid = new Grid();
            // ??
            grid.setX(colPixPos[j]);
            grid.setY(rowPixPos[i]);
            grid.setWidth(colPixPos[j + 1] - colPixPos[j]);
            grid.setHeight(rowPixPos[i + 1] - rowPixPos[i]);
            grid.setRow(cells[i][j].getRow());
            grid.setCol(cells[i][j].getCol());
            grid.setShow(cells[i][j].isShow());

            // ???
            int[] isInMergedStatus = isInMerged(grid.getRow(), grid.getCol(), rangeAddress);

            if (isInMergedStatus[0] == 0 && isInMergedStatus[1] == 0) {
                // ???????
                continue;
            } else if (isInMergedStatus[0] != -1 && isInMergedStatus[1] != -1) {
                // ??????                 
                int lastRowPos = isInMergedStatus[0] > rowSize - 1 ? rowSize - 1 : isInMergedStatus[0];
                int lastColPos = isInMergedStatus[1] > colSize - 1 ? colSize - 1 : isInMergedStatus[1];

                grid.setWidth(colPixPos[lastColPos + 1] - colPixPos[j]);
                grid.setHeight(rowPixPos[lastRowPos + 1] - rowPixPos[i]);

            }

            // ?
            CellStyle cs = cells[i][j].getCell().getCellStyle();
            if (cs.getFillPattern() == CellStyle.SOLID_FOREGROUND) {
                grid.setBgColor(cells[i][j].getCell().getCellStyle().getFillForegroundColorColor());
            }

            // 
            String strCell = "";
            switch (cells[i][j].getCell().getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cells[i][j].getCell().getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cells[i][j].getCell().getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                try {
                    strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                } catch (IllegalStateException e) {
                    strCell = String.valueOf(cells[i][j].getCell().getRichStringCellValue());
                }
                break;
            default:
                strCell = "";
            }
            //                System.out.println("strCell:" + strCell);

            if (cells[i][j].getCell().getCellStyle().getDataFormatString().contains("0.00%")) {
                try {
                    double dbCell = Double.valueOf(strCell);
                    strCell = new DecimalFormat("#.00").format(dbCell * 100) + "%";
                } catch (NumberFormatException e) {
                }
            }

            grid.setText(strCell.matches("\\w*\\.0") ? strCell.substring(0, strCell.length() - 2) : strCell);

            grids.add(grid);
        }
    }

    BufferedImage image = new BufferedImage(imageWidth, imageHeight + 1, BufferedImage.TYPE_INT_RGB);
    Graphics2D g2d = image.createGraphics();
    // 
    //g2d.setRenderingHint(SunHints.KEY_ANTIALIASING, SunHints.VALUE_ANTIALIAS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIASING, SunHints.VALUE_TEXT_ANTIALIAS_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_STROKE_CONTROL, SunHints.VALUE_STROKE_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIAS_LCD_CONTRAST, 140);
    //g2d.setRenderingHint(SunHints.KEY_FRACTIONALMETRICS, SunHints.VALUE_FRACTIONALMETRICS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_RENDERING, SunHints.VALUE_RENDER_DEFAULT);

    g2d.setColor(Color.white);
    g2d.fillRect(0, 0, imageWidth, imageHeight + 1);

    // 
    Iterator<Grid> iterable = grids.iterator();
    while (iterable.hasNext()) {
        Grid g = iterable.next();
        if (!g.isShow()) {
            continue;
        }

        // 
        g2d.setColor(g.getBgColor() == null ? Color.black : g.getBgColor());
        g2d.fillRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // 
        g2d.setColor(Color.red);
        g2d.setStroke(new BasicStroke(1));
        g2d.drawRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // ,
        g2d.setColor(g.getFtColor());

        Font font = g.getFont();
        FontMetrics fm = g2d.getFontMetrics(font);
        int strWidth = fm.stringWidth(g.getText());// ??
        g2d.setFont(font);

        g2d.drawString(g.getText(), g.getX() + (g.getWidth() - strWidth) / 2,
                g.getY() + (g.getHeight() - font.getSize()) / 2 + font.getSize());
    }

    g2d.dispose();
    BMPWriter.write(image, new File(bmpFilePath));
    System.out.println("Output to png file Success!");
}

From source file:com.gsecs.GSECSFrame.java

private void importSchoolDataActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importSchoolDataActionPerformed
    // 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 {/* ww  w .  j  av  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<SchoolBean> schoolBeanList = new ArrayList<SchoolBean>();

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

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

                        switch (columnIndex) {
                        case 0:
                            school.setSchoolAbbrv((String) getCellValue(nextCell));
                            break;
                        case 1:
                            school.setSchoolName((String) getCellValue(nextCell));
                            break;

                        }

                    }
                    schoolBeanList.add(school);
                }

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

            mainPanel.removeAll();
            mainPanel.repaint();
            GradSchoolModel model = new GradSchoolModel();
            GradSchoolJInternalFrame schoolFrame = new GradSchoolJInternalFrame();
            schoolFrame.getSchoolMsgLabel().setText("");
            schoolFrame.getAddSchoolButton().setVisible(true);
            schoolFrame.getUpdateSchoolButton().setVisible(false);
            schoolFrame.getDeleteSchoolButton().setVisible(false);
            //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (SchoolBean schoolBean : schoolBeanList) {
                try {
                    model.addSchool(schoolBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            schoolFrame.loadDataIntoJTable();
            mainPanel.add(schoolFrame);
            schoolFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }

}

From source file:com.gsecs.GSECSFrame.java

private void facultyImportMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_facultyImportMenuItemActionPerformed
    // 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 .  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<FacultyBean> facultyBeanList = new ArrayList<FacultyBean>();

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

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

                        switch (columnIndex) {
                        case 0:
                            faculty.setLastName((String) getCellValue(nextCell));
                            break;
                        case 1:
                            faculty.setFirstName((String) getCellValue(nextCell));
                            break;
                        case 2:
                            faculty.setGradSchool((String) getCellValue(nextCell));
                            break;
                        case 3:
                            faculty.setDegree((String) getCellValue(nextCell));
                            break;
                        case 4:
                            faculty.setTitle((String) getCellValue(nextCell));
                            break;
                        case 5:
                            faculty.setDaysToTeach(getCellValue(nextCell).toString());
                            break;
                        case 6:
                            faculty.setMaxLoadFall(Double.valueOf(getCellValue(nextCell).toString()));
                            break;
                        case 7:
                            faculty.setMaxLoadSpring(Double.valueOf(getCellValue(nextCell).toString()));
                            break;
                        case 8:
                            faculty.setMaxLoadSummer(Double.valueOf(getCellValue(nextCell).toString()));
                            break;
                        }

                    }
                    facultyBeanList.add(faculty);
                }

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

            mainPanel.removeAll();
            mainPanel.repaint();
            FacultyModel model = new FacultyModel();
            FacultyJInternalFrame facultyFrame = new FacultyJInternalFrame();
            facultyFrame.getFacultyMsgLabel().setText("");
            facultyFrame.getAddFacultyButton().setVisible(true);
            facultyFrame.getUpdateFacultyButton().setVisible(false);
            facultyFrame.getDeleteFacultyButton().setVisible(false);
            //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (FacultyBean facultyBean : facultyBeanList) {
                try {
                    model.addFaculty(facultyBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            facultyFrame.loadDataIntoJTable();
            mainPanel.add(facultyFrame);
            facultyFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }
}

From source file:com.gsecs.GSECSFrame.java

private void importCourseMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importCourseMenuItemActionPerformed
    // 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 .  jav a 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<CourseBean> courseBeanList = new ArrayList<CourseBean>();

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

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

                        switch (columnIndex) {
                        case 0:
                            courseBean.setCourseCode((String) getCellValue(nextCell));
                            break;
                        case 1:
                            courseBean.setCourseName((String) getCellValue(nextCell));
                            break;
                        case 2:
                            courseBean.setCourseDesc((String) getCellValue(nextCell));
                            break;
                        case 3:
                            courseBean.setCourseHours(Double.valueOf(getCellValue(nextCell).toString()));
                            break;
                        case 4:
                            courseBean.setCourseCap(Double.valueOf(getCellValue(nextCell).toString()));
                            break;
                        case 5:
                            courseBean.setOfferedFall((String) getCellValue(nextCell));
                            break;
                        case 6:
                            courseBean.setOfferedSpring((String) getCellValue(nextCell));
                            break;
                        case 7:
                            courseBean.setOfferedSummer((String) getCellValue(nextCell));
                            break;
                        case 8:
                            courseBean.setCoursePreReq((String) getCellValue(nextCell));
                            break;
                        case 9:
                            courseBean.setTeacher((String) getCellValue(nextCell));
                            break;
                        }

                    }
                    courseBeanList.add(courseBean);
                }

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

            mainPanel.removeAll();
            mainPanel.repaint();
            CourseModel model = new CourseModel();
            CourseJInternalFrame courseFrame = new CourseJInternalFrame();
            courseFrame.getCourseMsgLabel().setText("");
            courseFrame.getAddCourseButton().setVisible(true);
            courseFrame.getUpdateCourseButton().setVisible(false);
            courseFrame.getDeleteCourseButton().setVisible(false);
            //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (CourseBean courseBean : courseBeanList) {
                try {
                    model.addCourse(courseBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            courseFrame.loadDataIntoJTable();
            mainPanel.add(courseFrame);
            courseFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }
}

From source file:com.gsecs.GSECSFrame.java

private void importDegreePlanMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importDegreePlanMenuItemActionPerformed
    // 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 a  va 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<DegreePlanBean> degreePlanBeanList = new ArrayList<DegreePlanBean>();

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

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

                        switch (columnIndex) {
                        case 0:
                            degreePlan.setDegreeCode((String) getCellValue(nextCell));
                            break;
                        case 1:
                            degreePlan.setDeptTrack((String) getCellValue(nextCell));
                            break;
                        case 2:
                            degreePlan.setDescription((String) getCellValue(nextCell));
                            break;
                        case 3:
                            degreePlan.setHoursReq(Double.valueOf(getCellValue(nextCell).toString()));
                            break;
                        case 4:
                            degreePlan.setType((String) getCellValue(nextCell));
                            break;
                        case 5:
                            degreePlan.setCourses((String) getCellValue(nextCell));
                            break;
                        case 6:
                            degreePlan.setGradSchool((String) getCellValue(nextCell));
                            break;
                        case 7:
                            degreePlan.setForecast(Double.valueOf(getCellValue(nextCell).toString()));
                            break;

                        }

                    }
                    degreePlanBeanList.add(degreePlan);
                }

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

            mainPanel.removeAll();
            mainPanel.repaint();
            DegreePlanModel model = new DegreePlanModel();
            DegreeJInternalFrame degreeFrame = new DegreeJInternalFrame();
            degreeFrame.getDegreeMsgLabel().setText("");
            degreeFrame.getAddDegreeButton().setVisible(true);
            degreeFrame.getUpdateDegreeButton().setVisible(false);
            degreeFrame.getDeleteDegreeButton().setVisible(false);
            //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (DegreePlanBean degreePlanBean : degreePlanBeanList) {
                try {
                    model.addDegree(degreePlanBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            degreeFrame.loadDataIntoJTable();
            mainPanel.add(degreeFrame);
            degreeFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }
}

From source file:com.gsecs.GSECSFrame.java

private void importStudentMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importStudentMenuItemActionPerformed
    // 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 va2s . co m*/
            FileInputStream fis = new FileInputStream(filePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            List<StudentBean> studentBeansList = new ArrayList<StudentBean>();

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

                    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.setDegreeCode((String) getCellValue(nextCell));
                            break;
                        case 2:
                            studentBean.setGraduationDate((String) getCellValue(nextCell));
                            break;

                        }

                    }
                    studentBeansList.add(studentBean);
                }

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

            mainPanel.removeAll();
            mainPanel.repaint();
            StudentModel model = new StudentModel();
            StudentJInternalFrame studentFrame = new StudentJInternalFrame();
            studentFrame.getStudentMsgLabel().setText("");
            studentFrame.getAddStudentButton().setVisible(true);
            studentFrame.getUpdateStudentButton().setVisible(false);
            studentFrame.getDeleteStudentButton().setVisible(false);
            //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (StudentBean studentBean : studentBeansList) {
                try {
                    model.addStudent(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());
        }

    }
}