Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getCreationHelper

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getCreationHelper

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getCreationHelper

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getCreationHelper.

Prototype

@Override
    public HSSFCreationHelper getCreationHelper()

Source Link

Usage

From source file:org.openconcerto.erp.importer.DataImporter.java

License:Open Source License

public ArrayTableModel createModelFromXLS(File xlsFile, int sheetNumber) throws IOException {
    final InputStream inputStream = new FileInputStream(xlsFile);
    final POIFSFileSystem fileSystem = new POIFSFileSystem(new BufferedInputStream(inputStream));
    final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
    final HSSFSheet sheet = workBook.getSheetAt(sheetNumber);
    Iterator<Row> rowsIterator = sheet.rowIterator();
    int columnCount = 0;
    int rowCount = 0;
    while (rowsIterator.hasNext()) {
        Row row = rowsIterator.next();/*from  w  w w.ja va  2  s .c  om*/
        int i = row.getPhysicalNumberOfCells();
        if (i > columnCount) {
            columnCount = i;
        }
        rowCount++;
    }
    // Extract data
    rowsIterator = sheet.rowIterator();
    int start = 0;
    if (skipFirstLine) {
        start = 1;
        rowsIterator.next();
    }
    final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
    FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();

    while (rowsIterator.hasNext()) {
        final Row row = rowsIterator.next();
        final List<Object> rowData = new ArrayList<Object>();
        for (int i = 0; i < columnCount; i++) {
            final Cell cell = row.getCell(i);

            if (cell == null) {
                rowData.add("");
            } else {
                CellValue cellValue = evaluator.evaluate(cell);
                if (cellValue == null) {
                    rowData.add("");
                } else {
                    switch (cellValue.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        rowData.add(Boolean.valueOf(cellValue.getBooleanValue()));
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        rowData.add(Double.valueOf(cellValue.getNumberValue()));
                        break;
                    case Cell.CELL_TYPE_STRING:
                        rowData.add(cellValue.getStringValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        rowData.add(cell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        rowData.add("");
                        break;
                    default:
                        rowData.add(cellValue.getStringValue());
                        break;

                    }
                }
            }
        }

        rows.add(rowData);

    }
    inputStream.close();
    return new ArrayTableModel(rows);

}

From source file:org.openelis.bean.WorksheetExcelHelperBean.java

License:Open Source License

private void createStyles(HSSFWorkbook wb) {
    CellStyle dateTimeEditStyle, dateTimeNoEditStyle, headerStyle, rowEditStyle, rowNoEditStyle;
    CreationHelper helper;//from   www  .  jav a2  s  .  co m
    Font font;

    helper = wb.getCreationHelper();
    styles = new HashMap<String, CellStyle>();

    font = wb.createFont();
    font.setColor(IndexedColors.WHITE.getIndex());
    headerStyle = wb.createCellStyle();
    headerStyle.setAlignment(CellStyle.ALIGN_LEFT);
    headerStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
    headerStyle.setFont(font);
    headerStyle.setLocked(true);
    styles.put("header", headerStyle);

    rowEditStyle = wb.createCellStyle();
    rowEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    rowEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    rowEditStyle.setLocked(false);
    styles.put("row_edit", rowEditStyle);

    rowNoEditStyle = wb.createCellStyle();
    rowNoEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    rowNoEditStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    rowNoEditStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    rowNoEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    rowNoEditStyle.setLocked(true);
    styles.put("row_no_edit", rowNoEditStyle);

    dateTimeEditStyle = wb.createCellStyle();
    dateTimeEditStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd hh:mm"));
    dateTimeEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    dateTimeEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    dateTimeEditStyle.setLocked(false);
    styles.put("datetime_edit", dateTimeEditStyle);

    dateTimeNoEditStyle = wb.createCellStyle();
    dateTimeNoEditStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd hh:mm"));
    dateTimeNoEditStyle.setAlignment(CellStyle.ALIGN_LEFT);
    dateTimeNoEditStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    dateTimeNoEditStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    dateTimeNoEditStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    dateTimeNoEditStyle.setLocked(true);
    styles.put("datetime_no_edit", dateTimeNoEditStyle);
}

From source file:org.sevenorcas.style.app.mod.ss.SpreadsheetCell.java

/**
 * Get cell format/*  w  ww  .j  a v  a 2  s  . c  o  m*/
 * @param wb
 * @return
 */
public HSSFCellStyle getCellStyle(HSSFWorkbook wb) {

    //EX1
    if (sheet.containsStyleId(styleId)) {
        return sheet.getStyle(styleId);
    }

    HSSFCellStyle style = wb.createCellStyle();
    Integer clazzX = clazz != null ? clazz : (headerCell != null ? headerCell.clazz : null);

    switch (clazzX != null ? clazzX : CLASS_STRING) {
    case CLASS_DATE:
        if (!isHeader()) {
            CreationHelper createHelper = wb.getCreationHelper();
            style = wb.createCellStyle();
            style.setDataFormat(createHelper.createDataFormat()
                    .getFormat(sheet.getDateFormat() != null ? sheet.getDateFormat() : "m/d/yy"));
        }
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_PERCENTAGE:
        style = wb.createCellStyle();
        style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_INTEGER:
    case CLASS_LONG:
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_DOUBLE:
    case CLASS_UKURS:
        //Needs work
        //              if (numberFormat != null){
        //                  createHelper = wb.getCreationHelper();
        //                  style = wb.createCellStyle();
        //                  style.setDataFormat(createHelper.createDataFormat().getFormat(numberFormat));
        //              }
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        break;

    case CLASS_INTEGER_LEFT:
    case CLASS_STRING:
    case CLASS_BOOLEAN:
    case CLASS_CHARACTER:
    default:
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    }

    sheet.setCellStyle(wb, style, this, styleId);
    return style;
}

From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.ExcelUtils.java

License:Open Source License

public ExcelUtils(final HSSFWorkbook wb) {
    this.wb = wb;
    createHelper = wb.getCreationHelper();
    numberFormat = wb.createDataFormat();
}

From source file:org.teiid.translator.excel.ExcelExecution.java

License:Open Source License

private Iterator<Row> readXLSFile(File xlsFile) throws TranslatorException {
    try {/*from  w w  w  .  j  a v a2s .  c  om*/
        this.xlsFileStream = new FileInputStream(xlsFile);
        Iterator<Row> rowIter = null;
        String extension = ExcelMetadataProcessor.getFileExtension(xlsFile);
        if (extension.equalsIgnoreCase("xls")) { //$NON-NLS-1$
            HSSFWorkbook workbook = new HSSFWorkbook(this.xlsFileStream);
            HSSFSheet sheet = workbook.getSheet(this.visitor.getSheetName());
            this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            rowIter = sheet.iterator();

        } else if (extension.equalsIgnoreCase("xlsx")) { //$NON-NLS-1$
            XSSFWorkbook workbook = new XSSFWorkbook(this.xlsFileStream);
            XSSFSheet sheet = workbook.getSheet(this.visitor.getSheetName());
            this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            rowIter = sheet.iterator();
        } else {
            throw new TranslatorException(ExcelPlugin.Event.TEIID23000,
                    ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23000));
        }

        // skip up to the first data row
        if (this.visitor.getFirstDataRowNumber() > 0 && rowIter != null) {
            while (rowIter.hasNext()) {
                this.currentRow = rowIter.next();
                if (this.currentRow.getRowNum() >= this.visitor.getFirstDataRowNumber()) {
                    break;
                }
            }
        }
        return rowIter;
    } catch (IOException e) {
        throw new TranslatorException(e);
    }
}

From source file:servlets.UploadAccountingServlet.java

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    System.out.println("TEST");

    InputStream inputStream = null;
    Part filePart = request.getPart("file");

    inputStream = filePart.getInputStream();

    POIFSFileSystem fs = new POIFSFileSystem(inputStream);

    //       FileInputStream fichier = new FileInputStream(new File("countries.xls"));
    //create workbook instance that refers to xlsx file
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);//from   w  ww  .  j  a  v a2  s. c o m

    FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
    ArrayList<Accounting> arrAccounting = new ArrayList<>();

    ReportDB reportDB = new ReportDB();
    reportDB.addReport(new Report("Accounting", "Pending"));
    ArrayList<Report> reportList = new ArrayList();
    reportList = reportDB.getAllReports();
    int ReportRef = reportList.size();
    for (Row ligne : sheet) {//iterate rows
        Accounting accounting = new Accounting();
        for (Cell cell : ligne) {//iterate columns
            //cell type
            /* Sales sales;
            (lagay sa sys out)
            sales.setSalesID(cell.getNumericCellValue());
            --- WALA TO */

            switch (formulaEvaluator.evaluateInCell(cell).getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                //   sales.setName(cell.getNumericCellValue());
                System.out.print("cell 1  " + cell.getNumericCellValue() + " \t");

                if (cell.getColumnIndex() == 1) {

                    accounting.setPoNo((int) cell.getNumericCellValue());
                }
                if (cell.getColumnIndex() == 4) {

                    accounting.setAgingDays((int) cell.getNumericCellValue());
                }
                if (cell.getColumnIndex() == 5) {

                    accounting.setCustomerID((int) cell.getNumericCellValue());
                }

                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print("cell 2  " + cell.getStringCellValue() + " \t");
                if (cell.getColumnIndex() == 0) {

                    accounting.setStartDate(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 2) {

                    accounting.setTerms(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 3) {

                    accounting.setDueDate(cell.getStringCellValue());
                }
                break;
            }
        }
        accounting.setReportRef(ReportRef);
        arrAccounting.add(accounting);
        System.out.println();
    }

    request.setAttribute("arrAccounting", arrAccounting);
    AccountingDB accountingDB = new AccountingDB();
    request.setAttribute("arrAccounting", arrAccounting);
    for (int j = 0; j < arrAccounting.size(); j++) {
        accountingDB.addAccounting(arrAccounting.get(j));
    }

    ServletContext context = getServletContext();
    RequestDispatcher rd = context.getRequestDispatcher("/isrAfteruploadAccounting.jsp");
    rd.forward(request, response);
    processRequest(request, response);
}

From source file:servlets.UploadInventoryServlet.java

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    System.out.println("TEST");

    InputStream inputStream = null;
    Part filePart = request.getPart("file");

    inputStream = filePart.getInputStream();

    POIFSFileSystem fs = new POIFSFileSystem(inputStream);

    //       FileInputStream fichier = new FileInputStream(new File("countries.xls"));
    //create workbook instance that refers to xlsx file
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);/*from   w  w  w.j av a 2 s.  com*/

    FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
    ArrayList<Inventory> arrInventory = new ArrayList<>();

    ReportDB reportDB = new ReportDB();
    reportDB.addReport(new Report("Inventory", "Pending"));
    ArrayList<Report> reportList = new ArrayList();
    reportList = reportDB.getAllReports();
    int ReportRef = reportList.size();
    for (Row ligne : sheet) {//iterate rows
        Inventory inventory = new Inventory();
        for (Cell cell : ligne) {//iterate columns
            //cell type
            /* Sales sales;
            (lagay sa sys out)
            sales.setSalesID(cell.getNumericCellValue());
            --- WALA TO */

            switch (formulaEvaluator.evaluateInCell(cell).getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                //   sales.setName(cell.getNumericCellValue());
                System.out.print("cell 1  " + cell.getNumericCellValue() + " \t");

                if (cell.getColumnIndex() == 1) {

                    inventory.setQuantityOnHand((int) cell.getNumericCellValue());
                }

                if (cell.getColumnIndex() == 2) {

                    inventory.setGrandTotal((int) cell.getNumericCellValue());
                }

                if (cell.getColumnIndex() == 3) {

                    inventory.setBatchNo((int) cell.getNumericCellValue());
                }
                if (cell.getColumnIndex() == 6) {

                    inventory.setAvemonTO(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print("cell 2  " + cell.getStringCellValue() + " \t");
                if (cell.getColumnIndex() == 0) {

                    inventory.setBrandName(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 4) {

                    inventory.setExpDate(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 5) {

                    inventory.setShelfLife(cell.getStringCellValue());
                }

                if (cell.getColumnIndex() == 7) {

                    inventory.setInventoryMonths(cell.getStringCellValue());
                }
                break;
            }
        }
        inventory.setReportRef(ReportRef);
        arrInventory.add(inventory);
        System.out.println();
    }

    request.setAttribute("arrInventory", arrInventory);
    InventoryDB inventoryDB = new InventoryDB();
    request.setAttribute("arrInventory", arrInventory);
    for (int j = 0; j < arrInventory.size(); j++) {
        inventoryDB.addInventory(arrInventory.get(j));
    }

    ServletContext context = getServletContext();
    RequestDispatcher rd = context.getRequestDispatcher("/isrAfteruploadInventory.jsp");
    rd.forward(request, response);
    processRequest(request, response);
}

From source file:servlets.UploadSalesServlet.java

/**
 * Handles the HTTP <code>POST</code> method.
 *
 * @param request servlet request//from w ww.  ja  v  a 2 s  .c  o  m
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    System.out.println("TEST");

    InputStream inputStream = null;
    Part filePart = request.getPart("file");

    inputStream = filePart.getInputStream();

    POIFSFileSystem fs = new POIFSFileSystem(inputStream);

    //       FileInputStream fichier = new FileInputStream(new File("countries.xls"));
    //create workbook instance that refers to xlsx file
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);

    FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
    ArrayList<Sales> arrSales = new ArrayList<>();
    ReportDB reportDB = new ReportDB();
    reportDB.addReport(new Report("Sales", "Pending"));
    ArrayList<Report> reportList = new ArrayList();
    reportList = reportDB.getAllReports();
    int ReportRef = reportList.size();
    for (Row ligne : sheet) {//iterate rows
        Sales sales = new Sales();
        for (Cell cell : ligne) {//iterate columns
            //cell type

            switch (formulaEvaluator.evaluateInCell(cell).getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                //   sales.setName(cell.getNumericCellValue());
                System.out.print("cell 1  " + cell.getNumericCellValue() + " \t");
                if (cell.getColumnIndex() == 0) {

                    sales.setSalesAmmount(cell.getNumericCellValue());
                }
                if (cell.getColumnIndex() == 1) {

                    sales.setCreatedBy((int) cell.getNumericCellValue());
                }
                if (cell.getColumnIndex() == 2) {

                    sales.setLocation((int) cell.getNumericCellValue());
                }

                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print("cell 2  " + cell.getStringCellValue() + " \t");
                break;
            }
        }
        sales.setReportRef(ReportRef);
        arrSales.add(sales);
        System.out.println();
    }
    System.out.println("\t");
    System.out.println("Size: " + arrSales.size());
    System.out.println("\t");
    for (int i = 0; i < arrSales.size(); i++) {
        System.out.println("Amount: " + arrSales.get(i).getSalesAmmount());
        System.out.println("Created By: " + arrSales.get(i).getCreatedBy());
        System.out.println("Location: " + arrSales.get(i).getLocation());
        System.out.println("ReportRef: " + arrSales.get(i).getReportRef());
        System.out.println("\t");
    }
    request.setAttribute("arrSales", arrSales);
    SalesDB salesdb = new SalesDB();
    request.setAttribute("arrSales", arrSales);
    for (int j = 0; j < arrSales.size(); j++) {
        salesdb.addSales(arrSales.get(j));
    }
    ServletContext context = getServletContext();
    RequestDispatcher rd = context.getRequestDispatcher("/isrAfteruploadSales.jsp");

    rd.forward(request, response);

    processRequest(request, response);
}

From source file:swift.selenium.WebHelper.java

License:Open Source License

/**
 * This method reads and returns data from each cell of a provided worksheet
 * /*from   w  w  w.j a va  2  s.  c  om*/
 * @param reqValue
 * @param reqSheet
 * @param rowIndex
 * @param inputHashTable
 * @return
 * @throws IOException
 */
@SuppressWarnings("null")
public static String getCellData(String reqValue, HSSFSheet reqSheet, int rowIndex,
        HashMap<String, Object> inputHashTable) throws IOException {
    HSSFCell reqCell = null;
    Object actualvalue = null;
    String req = "";
    DataFormatter fmt = new DataFormatter();

    if (inputHashTable.isEmpty() == true) {
        inputHashTable = getValueFromHashMap(reqSheet);
    }
    HSSFRow rowActual = reqSheet.getRow(rowIndex);
    if (inputHashTable.get(reqValue) == null) {

        TransactionMapping.report.setStrMessage("Column " + reqValue + " not Found. Please Check input Sheet");
        TransactionMapping.pauseFun("Column " + reqValue + " not Found. Please Check input Sheet");
    } else {
        actualvalue = inputHashTable.get(reqValue);//rowHeader.getCell(colIndex).toString();         
        if (actualvalue != null) {
            int colIndex = Integer.parseInt(actualvalue.toString());
            reqCell = rowActual.getCell(colIndex);
            //TM 27-04-2015: Updated the code for formula in cells
            if (reqCell == null) {
                System.out.println(reqValue + " is Null");
            } else {
                HSSFWorkbook wb = reqCell.getSheet().getWorkbook(); //TM-05/05/2015: Get workbook instance from the worksheet
                HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); //TM-05/05/2015: To refresh all the formulas in the worksheet
                FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

                CellValue cellValue = evaluator.evaluate(reqCell);
                int type = 0;
                if (cellValue != null) {
                    type = cellValue.getCellType();
                } else {
                    type = reqCell.getCellType();
                }

                switch (type) {
                case HSSFCell.CELL_TYPE_BLANK:
                    req = "";
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(reqCell)) {
                        SimpleDateFormat form = new SimpleDateFormat(
                                Automation.configHashMap.get("DATEFORMAT").toString());
                        req = form.format(reqCell.getDateCellValue());
                    } else
                        req = fmt.formatCellValue(reqCell, evaluator);
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    req = reqCell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    req = Boolean.toString(reqCell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    req = "error";
                    break;
                }
            }
        }

        else {
            req = reqCell.getStringCellValue();
            System.out.println("null");
        }
    }
    return req;
}