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

Introduction

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

Prototype

public XSSFWorkbook(PackagePart part) throws IOException 

Source Link

Document

Constructs a XSSFWorkbook object using Package Part.

Usage

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * @param filename//from  ww w .ja  v  a2  s  .c  o m
 * @param sheetNo
 * @param startindex
 * @param importDao
 * @return
 * @throws ServiceException
 */
public static void dumpXLSXFileData(String filename, int sheetNo, int startindex, ImportDAO importDao,
        HibernateTransactionManager txnManager) throws ServiceException {
    boolean commitedEx = false;
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("import_Tx");
    def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
    TransactionStatus status = txnManager.getTransaction(def);
    Session session = txnManager.getSessionFactory().getCurrentSession();
    try {
        String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
        FileInputStream fs = new FileInputStream(destinationDirectory + "/" + filename);
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sheet = wb.getSheetAt(sheetNo);
        //DateFormat sdf = new SimpleDateFormat(df_full);
        int maxRow = sheet.getLastRowNum();
        int maxCol = 0;
        String tableName = importDao.getTableName(filename);
        int flushCounter = 0;
        for (int i = startindex; i <= maxRow; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (i == startindex) {
                maxCol = row.getLastCellNum(); //Column Count
            }
            ArrayList<String> dataArray = new ArrayList<String>();
            JSONObject dataObj = new JSONObject();
            for (int j = 0; j < maxCol; j++) {
                XSSFCell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    dataArray.add(val);
                    continue;
                }
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        val = Long.toString(cell.getDateCellValue().getTime());
                    } else {
                        val = dfmt.format(cell.getNumericCellValue());
                    }
                    break;
                case XSSFCell.CELL_TYPE_STRING:
                    val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                    break;
                }
                dataObj.put(colHeader, val);
                dataArray.add(val); //Collect row data
            }
            //Insert Query
            if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty)
                importDao.dumpFileRow(tableName, dataArray.toArray());
                if (flushCounter % 30 == 0) {
                    session.flush();
                    session.clear();
                }
                flushCounter++;
            }

        }
        try {
            txnManager.commit(status);
        } catch (Exception ex) {
            commitedEx = true;
            throw ex;
        }
    } catch (IOException ex) {
        throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
    } catch (Exception ex) {
        if (!commitedEx) { //if exception occurs during commit then dont call rollback
            txnManager.rollback(status);
        }
        throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
    }
}

---

From source file:com.ksa.myanmarlottery.service.parser.ExcelFileParser.java

@Override
public List<Result> getResult(InputStream in) throws FileNotFoundException, IOException, ParseException {
    List<Prize> prizes = null;
    List<Result> resultList = new ArrayList<>();
    SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy"); // 01-May-2017
    try {/*from  w ww .jav  a  2s.  c  o m*/
        Workbook workbook = new XSSFWorkbook(in);
        Sheet datatypeSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = datatypeSheet.iterator();

        while (iterator.hasNext()) {

            Row currentRow = iterator.next();
            Cell cell0 = currentRow.getCell(0); // get first cell.

            if (cell0.getCellTypeEnum() == CellType.NUMERIC) {
                int numberic = (int) cell0.getNumericCellValue();
                log.info("Numberic - " + numberic);

                // check lottery type
                if (ConstantUtil.OLD_LOTTERY_TYPE == numberic || ConstantUtil.NEW_LOTTERY_TYPE == numberic) { // for lottery type result
                    Result result = new Result();
                    result.setType(numberic);
                    result.setNumberOfTimes((int) currentRow.getCell(1).getNumericCellValue());
                    //                        result.setResultFor(format.parse(currentRow.getCell(2).toString()));
                    result.setResultFor(currentRow.getCell(2).getDateCellValue());
                    result.setDataProvider(currentRow.getCell(3).getStringCellValue());
                    result.setCompanyName(currentRow.getCell(4).getStringCellValue());

                    prizes = new ArrayList<>();
                    result.setPrizes(prizes);
                    resultList.add(result);
                }

            } else if (cell0.getCellTypeEnum() == CellType.STRING) { // result data
                String character = cell0.getStringCellValue();
                log.info("character - " + character);

                // check validation for character.
                String value = charMap.get(character);
                if (value == null) {
                    throw new ParseException(
                            "Character is Not valid at Row: " + currentRow.getRowNum() + " > column:" + 0, 400);
                }
                Cell cell1 = currentRow.getCell(1);
                if (cell1.getCellTypeEnum() != CellType.NUMERIC) {
                    throw new ParseException(
                            "Should be Number at Row: " + currentRow.getRowNum() + " > column:" + 1, 400);
                }
                log.info("Cell Type " + cell1.getCellTypeEnum());
                int code = (int) cell1.getNumericCellValue();
                log.info("code - " + code + " Row:" + currentRow.getRowNum() + " > column:" + 1);
                String prizeTitle = currentRow.getCell(2).getStringCellValue();
                log.info("prizeTitle - " + prizeTitle);
                String prizeDesc = currentRow.getCell(4).getStringCellValue();
                log.info("prizeDesc - " + prizeDesc);
                prizes.add(new Prize(character, code, prizeTitle, prizeDesc));
            }
        }
        log.info("resultList size: " + resultList.size());
        for (Result r : resultList) {
            log.info("prizeList size: " + r.getPrizes().size());
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
        throw e;
    } catch (IOException e) {
        e.printStackTrace();
        throw e;
    }
    return resultList;
}

---

From source file:com.kybelksties.excel.ExcelWorkbookTableModel.java

License:Open Source License

/**
 * Read a workbook from file./*w w  w. j ava  2 s  .c o m*/
 *
 * @param filename currently supported extensions are *.xls for classic
 *                 Excel, *.xlsx for current version and *.csv for comma
 *                 separated value files
 * @throws IOException thrown when file doesn't exist or cannot be read from
 */
public final void read(String filename) throws IOException {
    try {
        String lwrFilename = filename.toLowerCase();
        try (FileInputStream fis = new FileInputStream(filename)) {
            if (lwrFilename.endsWith(".xls")) {
                setWorkbook(new XSSFWorkbook(fis));
            } else if (lwrFilename.endsWith(".xlsx")) {
                setWorkbook(new HSSFWorkbook(fis));
            } else if (lwrFilename.endsWith(".csv")) {
                setWorkbook(new XSSFWorkbook());
                addSheet();
                String contents = FileUtilities.readText(filename);
                String[] lines = contents.split(System.getProperty("line.separator"));
                ExcelSheetTableModel model = sheet2Model.get(currentSheet);
                int row = 0;
                for (String line : lines) {
                    String[] cellValues = line.split(",");
                    int col = 0;
                    for (String value : cellValues) {
                        model.setValueAt(value.trim(), row, col);
                    }
                }
            }

        }
    } catch (IOException ex) {
        LOGGER.log(Level.SEVERE, null, ex);
    }
}

---

From source file:com.kysoft.cpsi.audit.service.SelfCheckServiceImpl.java

@Override
@Transactional/*from  w  w  w .j  a  v  a 2s  .c om*/
public void uploadSelfCheckData(InputStream is, String hcrwId, String fileName, Integer nd) throws Exception {
    Hcrw hcrw = hcrwMapper.selectByPrimaryKey(hcrwId);
    Workbook workbook = null;
    if (fileName.endsWith("xls")) {
        POIFSFileSystem fs = new POIFSFileSystem(is);
        workbook = new HSSFWorkbook(fs);
    } else if (fileName.endsWith("xlsx")) {
        workbook = new XSSFWorkbook(is);
    }

    //?
    validateExcel(hcrwId, workbook);
    if (importFlag.equals("1")) {
        //?
        nianbaoWangzhiwangdian(hcrw, workbook.getSheet("??"), nd);
        //?
        nianbao(hcrw, workbook.getSheet(""), workbook.getSheet("??"),
                workbook.getSheet(""), nd);

        //
        gudongchuzi(hcrw, workbook.getSheet("??"), nd);

        //??
        guquanbiangeng(hcrw, workbook.getSheet("????"), nd);

        //
        duiwaitouzi(hcrw, workbook.getSheet("?????"), nd);

        //?
        duiwandanbao(hcrw, workbook.getSheet("??"), nd);

        //?
        xingzhengxuke(hcrw, workbook.getSheet("??????"), nd);

        jsGudongchuzhi(hcrw, workbook.getSheet("??"));
        jsGuquanbiangeng(hcrw, workbook.getSheet("????"));
        jsXingzhengxuke(hcrw, workbook.getSheet("??????"));
        jsZhishichanquan(hcrw, workbook.getSheet("??"));
        jsXingzhengchufa(hcrw, workbook.getSheet("??"));
    }
    workbook.close();
}

---

From source file:com.kysoft.cpsi.audit.service.SelfCheckServiceImpl.java

@Override
public void judgeRepeatExcle(InputStream is, int firstRowNum, int colNum, String fileName) throws Exception {
    Map<String, Object> sheetValues = new HashedMap();
    Workbook workbook = null;/* w w  w.j  a va2s .com*/
    if (fileName.endsWith("xls")) {
        POIFSFileSystem fs = new POIFSFileSystem(is);
        workbook = new HSSFWorkbook(fs);
    } else if (fileName.endsWith("xlsx")) {
        workbook = new XSSFWorkbook(is);
    }
    Sheet sheet = workbook.getSheetAt(0);
    for (int i = firstRowNum; i < sheet.getLastRowNum() + 1; i++) {
        Row row = sheet.getRow(i - 1);
        System.out.println(i);
        Cell cell = row.getCell(colNum - 1);
        if (null != cell && null != POIUtils.getStringCellValue(cell)
                && !POIUtils.getStringCellValue(cell).equals("")) {
            if (sheetValues.containsKey(POIUtils.getStringCellValue(cell))) {
                throw new RuntimeException(
                        POIUtils.getStringCellValue(cell) + "??????");
            } else {
                sheetValues.put(POIUtils.getStringCellValue(cell), i);
            }
        }
    }
}

---

From source file:com.larasolution.serverlts.FileUploadHandler.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    //     tablename=request.getParameter(tablename)
    //process only if its multipart content
    FileOutputStream fos = new FileOutputStream("C:\\uploads\\data.csv");
    String list = "";
    List<List> allData = new ArrayList<List>();

    List<String> parameters = new ArrayList<String>();
    if (ServletFileUpload.isMultipartContent(request)) {

        try {//from   w  w  w  .j  ava2s.com

            StringBuilder data = new StringBuilder();
            List<FileItem> multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
            System.out.println(multiparts);
            for (FileItem item : multiparts) {
                if (item.isFormField()) {
                    parameters.add(item.getFieldName());
                    System.out.println(parameters);
                }
                if (!item.isFormField()) {
                    String name = new File(item.getName()).getName();

                    item.write(new File(UPLOAD_DIRECTORY + File.separator + name));
                    //System.out.println(File.separator);
                    // Get the workbook object for XLSX file
                    XSSFWorkbook wBook = new XSSFWorkbook(
                            new FileInputStream(UPLOAD_DIRECTORY + File.separator + name));

                    XSSFSheet zz = wBook.getSheetAt(0);
                    FormulaEvaluator formulaEval = wBook.getCreationHelper().createFormulaEvaluator();

                    Row row;
                    Cell cell;

                    // Iterate through each rows from first sheet
                    Iterator<Row> rowIterator = zz.iterator();
                    while (rowIterator.hasNext()) {
                        row = rowIterator.next();

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

                        while (cellIterator.hasNext()) {

                            cell = cellIterator.next();

                            switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_BOOLEAN:
                                data.append(cell.getBooleanCellValue()).append(",");
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    data.append(
                                            com.larasolution.modle.getDate.getDate5(cell.getDateCellValue()))
                                            .append(",");
                                } else {
                                    data.append(cell.getNumericCellValue()).append(",");
                                }

                                break;
                            case Cell.CELL_TYPE_STRING:
                                data.append(cell.getStringCellValue()).append(",");
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                data.append("" + ",");
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                Double value = Double.parseDouble(formulaEval.evaluate(cell).formatAsString());

                                data.append(String.format("%.2f", value)).append(",");
                                break;
                            default:
                                data.append(cell).append("");

                            }

                        }
                        data.append("\r\n");
                        //String k = data.substring(0, data.length() - 3);
                        //ls.add(k);

                        // data.setLength(0);
                    }

                    fos.write(data.toString().getBytes());
                    fos.close();

                    //
                }
            }

            savetosql();
            request.setAttribute("message", "successfully uploaded ");
        } catch (Exception ex) {
            request.setAttribute("message", "File Upload Failed due to " + ex);
        }

    } else {
        request.setAttribute("message", "Sorry this Servlet only handles file upload request");
    }

    request.setAttribute("arrayfile", allData);
    request.setAttribute("names", parameters);
    RequestDispatcher disp = getServletContext().getRequestDispatcher("/FileUploadResult.jsp");
    disp.forward(request, response);

    // System.out.println(allData.size());
    // response.sendRedirect("send.jsp?arrayfile=" + list + "");
    //request.getRequestDispatcher("/send.jsp?arrayfile='"+ls+"'").forward(request, response);
}

---

From source file:com.lw.common.utils.ExcelUtil.java

/**
 * ???/* ww w  .j  a v  a2 s.c  o m*/
 * @param <T>
 * @throws Exception
 */
public <T> void parseImportFile(InputStream inputStream, String fileName, List<T> modelList,
        Class<T> modelClass, Map<String, String> columnMap) throws Exception {
    Workbook workbook = null;
    // ????Workbook??HSSFWorkbookXSSFWorkbook
    if (fileName.endsWith(SUFFIX_XLS)) {
        workbook = new HSSFWorkbook(inputStream);
    } else if (fileName.endsWith(SUFFIX_XLSX)) {
        workbook = new XSSFWorkbook(inputStream);
    }

    Sheet sheet = workbook.getSheetAt(0);
    if (sheet != null) {
        parseSheet(sheet, modelList, modelClass, columnMap);
    }
}

---

From source file:com.lw.common.utils.ExcelUtil.java

public <T> Workbook batchImportFailList(String modelPath, List<T> objectList, Class<T> modelClass,
        Map<String, String> columnMap) throws Exception {
    //??excel//from   ww  w  .j a  v a  2 s . co  m
    File localfile = new File("D:\\lw7068\\Desktop\\ (4)\\??.xlsx");
    InputStream in = new FileInputStream(localfile);
    // ?
    Workbook book = new XSSFWorkbook(in);
    // ?sheet
    Sheet sheet = book.getSheetAt(0);
    //
    Row titleRow = sheet.getRow(0);
    //???index
    Map<String, Integer> rowIndex = getTitleRowIndex(titleRow);
    //???method
    Map<Integer, Method> methodsIndex = getRowIndexAndGetMethod(titleRow, rowIndex, modelClass, columnMap);
    for (int i = 0; i < objectList.size(); i++) {
        //excelindex   ??? 
        Map<Integer, String> dataMap = getRowIndexAndData(titleRow, objectList.get(i), methodsIndex);
        //
        Row row = sheet.createRow(i + 1);
        //
        for (Map.Entry<Integer, String> entry : dataMap.entrySet()) {
            Cell cell = row.createCell(entry.getKey());
            cell.setCellValue(entry.getValue());
        }
    }
    return book;
}

---

From source file:com.MainGui.java

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
    // JOptionPane.showMessageDialog(null, evt.getActionCommand());
    if (evt.getActionCommand().equals("Save Details")) {
        try {// ww  w  . j  av a 2  s. c o m
            FuntionLibrary fb = new FuntionLibrary();
            File file = new File(excelFileName);
            XSSFWorkbook wb;
            XSSFSheet sheet;
            if (file.exists()) {
                FileInputStream fis = new FileInputStream(file);
                wb = new XSSFWorkbook(fis);
                sheet = wb.getSheet(sheetName);
            } else {
                wb = new XSSFWorkbook();
                sheet = wb.createSheet(sheetName);
            }
            mailId = fb.validateEmail(jTextField1.getText(), sheet);
            pass = fb.validatePassword(jTextField3.getText());
            sheet = wb.getSheet(sheetName);
            mob = fb.validateMobileNO(jFormattedTextField1.getText(), sheet);
            if (mob != null && mailId != null && FuntionLibrary.Pflag == true && FuntionLibrary.Mflag == true
                    && FuntionLibrary.Eflag == true) {
                fb.writetoExcel(jTextField4.getText(), mailId, pass, mob, sheet, jTextField2.getText());
                FileOutputStream fileOut = new FileOutputStream(excelFileName);
                //write this workbook to an Outputstream.
                wb.write(fileOut);
                fileOut.flush();
                fileOut.close();
                System.out.println("Your excel file has been generated!");
                JOptionPane.showMessageDialog(null,
                        " Data Base Updated Successfully !!\n Path of data file is " + excelFileName);
            } else {
                JOptionPane.showMessageDialog(null, "Please enter required fields correctly. Thanks !!");
            }
            jTextField1.setText("");
            jTextField3.setText("");
            jTextField2.setText("");
            jTextField4.setText("");
            jFormattedTextField1.setText("");
        } catch (InvalidFormatException | IOException ex) {
            Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(null, "Error in Saving Data");
        }
    }
}

---

From source file:com.MainGui.java

private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {
    EditExistingButtonClicked = true;/*from w w w.j  av  a2 s .  c o m*/
    jButton1.setVisible(false);
    jButton3.setVisible(true);
    jButton4.setVisible(true);
    FileInputStream fis = null;
    try {
        File file = new File(FuntionLibrary.excelFileName);
        if (!(file.exists())) {
            JOptionPane.showMessageDialog(null, "Please Create the DataBase !! DataBase not found.");
        }
        fis = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        Mob_Number = JOptionPane.showInputDialog(null, "Enter Mobile Number to find details");
        FuntionLibrary fb = new FuntionLibrary();
        fb.validateMobileNO(Mob_Number, wb.getSheet(sheetName));
        rowNum = FuntionLibrary.findRow(wb.getSheet(FuntionLibrary.sheetName), Mob_Number);
        String arr[] = FuntionLibrary.getData(rowNum, wb.getSheet(FuntionLibrary.sheetName));
        jTextField4.setText(arr[0]);
        jTextField1.setText(arr[1]);
        jTextField3.setText(arr[2]);
        jFormattedTextField1.setText(arr[3]);
        jTextField2.setText(arr[4]);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            fis.close();
        } catch (IOException ex) {
            Logger.getLogger(MainGui.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• »