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

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

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

Introduction

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

Prototype

CreationHelper getCreationHelper();

Source Link

Document

Returns an object that handles instantiating concrete classes of the various instances one needs for HSSF and XSSF.

Usage

From source file:com.skt.adcas.lte.action.DownLinkBySTDStatsAction.java

public String selectCellTrafficStatsCompExcelDownload() {

    this.log.debug("selectCellTrafficStatsCompExcelDownload Start");
    FileOutputStream fileOut = null;

    try {//from   w  ww. j  a  va2 s .com
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);
        Map<String, Object> mapAfter = gson.fromJson(this.JSONDATA2, type);

        log.debug("json data : " + this.JSONDATA);
        log.debug("json data : " + this.JSONDATA2);

        String searchType = this.SEARCHTYPE;

        Workbook wb = new HSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();

        String sheetName = "";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet sheet = wb.createSheet(safeName);

        createCellTrafficStatsExcelSheet(sheet, map, searchType);

        sheetName = "";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet sheetAfter = wb.createSheet(safeName);

        createCellTrafficStatsExcelSheet(sheetAfter, mapAfter, searchType);

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkStatsCompData(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficStatsCompExcelDownload End");
    return SUCCESS;
}

From source file:com.ssy.havefun.f3d.F3DDaoImpl.java

public String getCellValue(Cell cell) {
    String ret = "";
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        ret = "";
        break;//from w  w  w .j  a  v  a 2 s  .c om
    case Cell.CELL_TYPE_BOOLEAN:
        ret = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        ret = null;
        break;
    case Cell.CELL_TYPE_FORMULA:
        Workbook wb = cell.getSheet().getWorkbook();
        CreationHelper crateHelper = wb.getCreationHelper();
        FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
        ret = getCellValue(evaluator.evaluateInCell(cell));
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            //                Date theDate = cell.getDateCellValue();  
            //                ret = simpleDateFormat.format(theDate);  
        } else {
            ret = NumberToTextConverter.toText(cell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        ret = cell.getRichStringCellValue().getString();
        break;
    default:
        ret = null;
    }

    return ret; //?trim  
}

From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java

License:Apache License

public WorkbookParser(WorkbookParserSettings settings, Context context, Workbook workbook, String offsetId)
        throws DataParserException {
    this.settings = requireNonNull(settings);
    this.context = requireNonNull(context);
    this.workbook = requireNonNull(workbook);
    this.rowIterator = iterate(this.workbook);
    this.offset = requireNonNull(offsetId);
    this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    this.currentSheet = null; // default to blank.   Used to figure out when sheet changes and get new field names from header row

    if (!rowIterator.hasNext()) {
        throw new DataParserException(Errors.EXCEL_PARSER_04);
    }// www .  j  a  v a  2s  .co  m

    headers = new HashMap<>();

    // If Headers are expected, go through and get them from each sheet
    if (settings.getHeader() == ExcelHeader.WITH_HEADER) {
        Sheet sheet;
        String sheetName;
        Row hdrRow;
        for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
            sheet = workbook.getSheetAt(s);
            sheetName = sheet.getSheetName();
            hdrRow = sheet.rowIterator().next();
            List<Field> sheetHeaders = new ArrayList<>();
            // if the table happens to have blank columns in front of it, loop through and artificially add those as headers
            // This helps in the matching of headers to data later as the indexes will line up properly.
            for (int columnNum = 0; columnNum < hdrRow.getFirstCellNum(); columnNum++) {
                sheetHeaders.add(Field.create(""));
            }
            for (int columnNum = hdrRow.getFirstCellNum(); columnNum < hdrRow.getLastCellNum(); columnNum++) {
                Cell cell = hdrRow.getCell(columnNum);
                try {
                    sheetHeaders.add(Cells.parseCell(cell, this.evaluator));
                } catch (ExcelUnsupportedCellTypeException e) {
                    throw new DataParserException(Errors.EXCEL_PARSER_05, cell.getCellTypeEnum());
                }
            }
            headers.put(sheetName, sheetHeaders);
        }
    }

    Offsets.parse(offsetId).ifPresent(offset -> {
        String startSheetName = offset.getSheetName();
        int startRowNum = offset.getRowNum();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            int rowNum = row.getRowNum();
            String sheetName = row.getSheet().getSheetName();
            // if a sheet has blank rows at the top then the starting row number may be higher than a default offset of zero or one, thus the >= compare
            if (startSheetName.equals(sheetName) && rowNum >= startRowNum) {
                if (rowIterator.hasPrevious()) {
                    row = rowIterator.previous();
                    this.currentSheet = row.getRowNum() == row.getSheet().getFirstRowNum() ? null
                            : row.getSheet().getSheetName(); // used in comparison later to see if we've moved to new sheet
                } else {
                    this.currentSheet = null;
                }
                break;
            }
        }
    });
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

void setInternalWorkbook(Workbook workbook) {
    this.workbook = workbook;
    formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
    styler = createSpreadsheetStyleFactory();

    reloadActiveSheetData();/*w ww .  j a v a 2 s  .c om*/
    if (workbook instanceof HSSFWorkbook) {
        getState().workbookProtected = ((HSSFWorkbook) workbook).isWriteProtected();
    } else if (workbook instanceof XSSFWorkbook) {
        getState().workbookProtected = ((XSSFWorkbook) workbook).isStructureLocked();
    }
    // clear all tables from memory
    tables.clear();

    getState().verticalScrollPositions = new int[getState().sheetNames.length];
    getState().horizontalScrollPositions = new int[getState().sheetNames.length];

    conditionalFormatter = createConditionalFormatter();

    getState().workbookChangeToggle = !getState().workbookChangeToggle;
}

From source file:com.wantdo.stat.excel.poi_src.formula.CheckFunctionsSupported.java

License:Apache License

public CheckFunctionsSupported(Workbook workbook) {
    this.workbook = workbook;
    this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
}

From source file:com.wantdo.stat.excel.poi_src.formula.UserDefinedFunctionExample.java

License:Apache License

public static void main(String[] args) {

    if (args.length != 2) {
        System.out.println("usage: UserDefinedFunctionExample fileName cellId");
        return;/* w  w w.  jav  a 2s  .c om*/
    }

    System.out.println("fileName: " + args[0]);
    System.out.println("cell: " + args[1]);

    File workbookFile = new File(args[0]);

    try {
        FileInputStream fis = new FileInputStream(workbookFile);
        Workbook workbook = WorkbookFactory.create(fis);
        fis.close();

        String[] functionNames = { "calculatePayment" };
        FreeRefFunction[] functionImpls = { new CalculateMortgage() };

        UDFFinder udfToolpack = new DefaultUDFFinder(functionNames, functionImpls);

        // register the user-defined function in the workbook
        workbook.addToolPack(udfToolpack);

        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        CellReference cr = new CellReference(args[1]);
        String sheetName = cr.getSheetName();
        Sheet sheet = workbook.getSheet(sheetName);
        int rowIdx = cr.getRow();
        int colIdx = cr.getCol();
        Row row = sheet.getRow(rowIdx);
        Cell cell = row.getCell(colIdx);

        CellValue value = evaluator.evaluate(cell);

        System.out.println("returns value: " + value);

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:com.webbfontaine.valuewebb.report.utils.cellStyles.CellStyleUtils.java

License:Open Source License

public static CellStyle cellStyle(Workbook workBook, CellType cellType) {
    CellStyle cellStyle = workBook.createCellStyle();

    if (!Color.NO_COLOR.equals(cellType.getColor())) {
        Font font = workBook.createFont();
        font.setFontName("Arial");
        font.setColor(cellType.getColor().index());
        cellStyle.setFont(font);// w ww  . j a v  a 2  s . c  o m
    }

    if (!Alignment.NO_ALIGNMENT.equals(cellType.getHorizontalAlignment())) {
        cellStyle.setAlignment(cellType.getHorizontalAlignment().index());
    }

    if (!Alignment.NO_ALIGNMENT.equals(cellType.getVerticalAlignment())) {
        cellStyle.setVerticalAlignment(cellType.getVerticalAlignment().index());
    }

    if (!Format.NO_FORMAT.equals(cellType.getFormat())) {
        CreationHelper creationHelper = workBook.getCreationHelper();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(cellType.getFormat().text()));
    }

    setCellBorders(cellStyle);
    return cellStyle;
}

From source file:com.wuliu.biz.util.export.strategy.WholeOrderExport.java

License:Open Source License

public String export(String folderPath, String templateName, List<WuliuMergedOrderModel> mergedOrders)
        throws Exception {
    File folder = createFolder(folderPath);
    List<List<WuliuMergedOrderModel>> mergedOrderLists = split(mergedOrders);

    if (CollectionUtils.isEmpty(mergedOrderLists)) {
        return null;
    }/*from ww w .j  av a 2  s.co m*/

    for (List<WuliuMergedOrderModel> item : mergedOrderLists) {
        File template = new File(this.getClass().getClassLoader().getResource(templateName).getFile());
        InputStream inp = new FileInputStream(template);
        Workbook wb = WorkbookFactory.create(inp);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        fillSheet(sheet, item);
        evaluate(sheet, evaluator);

        File file = new File(folder, getName(item));
        try {
            FileOutputStream outputStream = new FileOutputStream(file);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
            wb.close();
            System.out.println("success");
        } catch (Exception e) {
            System.out.println("It cause Error on WRITTING excel workbook: ");
            e.printStackTrace();
        }
    }
    return folder.getAbsolutePath();
}

From source file:com.zlfun.framework.excel.ExcelUtils.java

private static <T> void fill(Class<T> clazz, List<T> result, String fileName, InputStream is) {

    try {/*from w w  w  .  ja  v a2s  .c  o  m*/
        Workbook excel = null;
        if (fileName.indexOf(".xlsx") > 0) {
            excel = new XSSFWorkbook(is);// Excel2007
        } else if (fileName.indexOf(".xls") > 0) {
            excel = new HSSFWorkbook(is);// Excel2003

        } else {
            return;
        }
        FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = excel.getSheetAt(0);// ?0
        // ????1
        List<String> header = new ArrayList<String>();
        if (sheet.getLastRowNum() >= 0) {
            Row row = sheet.getRow(0);// ?

            for (int i = 0; i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);// ??

                if (cell != null) {// ?cellStr

                    header.add(cell.getStringCellValue());
                }

            }

        }

        //
        if (sheet.getLastRowNum() > 1) {

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {

                Row row = sheet.getRow(i);// ?
                if (row == null) {// ??
                    continue;
                }
                Map<String, String> map = genRowMap(row, header, evaluator);
                T t = fill(map, clazz.newInstance());
                result.add(t);
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InstantiationException ex) {
        Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IllegalAccessException ex) {
        Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
    } finally {// ?
        if (is != null) {
            try {
                is.close();

            } catch (IOException ex) {
                Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    return;
}

From source file:Compras.altaCompras.java

   private void b_muestraActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_b_muestraActionPerformed
    // TODO add your handling code here:
    javax.swing.JFileChooser jF1= new javax.swing.JFileChooser();
    jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" }));
    String ruta = null;/*from  www .jav  a 2 s .  c  o  m*/
    if(jF1.showSaveDialog(null)==jF1.APPROVE_OPTION)
    {
        ruta = jF1.getSelectedFile().getAbsolutePath();
        File archivoXLS = new File(ruta+".xls");
        try
        {
            if(archivoXLS.exists())
            archivoXLS.delete();
            archivoXLS.createNewFile();
            Workbook libro = new HSSFWorkbook();
            FileOutputStream archivo = new FileOutputStream(archivoXLS);
            Sheet hoja = libro.createSheet("Muestras");
                
            org.apache.poi.ss.usermodel.Font font10 = libro.createFont();
            font10.setFontHeightInPoints((short)10);
            font10.setFontName("Arial");
            font10.setItalic(false);
            font10.setBold(false);
            font10.setColor(new HSSFColor.YELLOW().getIndex());
                
                
            CellStyle titulo = libro.createCellStyle();
            CellStyle contenido = libro.createCellStyle();
            CellStyle firma = libro.createCellStyle();
            CellStyle costado = libro.createCellStyle();
            CellStyle derecha = libro.createCellStyle();
            CellStyle derecha_borde = libro.createCellStyle();
                
            titulo.setFont(font10);
            titulo.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            titulo.setFillBackgroundColor(new HSSFColor.GREEN().getIndex());
            titulo.setAlignment(CellStyle.ALIGN_CENTER);
                
            contenido.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderTop(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderRight(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                
            derecha_borde.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderTop(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderRight(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setAlignment(CellStyle.ALIGN_RIGHT);
                
            derecha.setAlignment(CellStyle.ALIGN_RIGHT);
                
            firma.setBorderTop(HSSFCellStyle.BORDER_THIN);
            firma.setAlignment(CellStyle.ALIGN_CENTER);
                
            //costado.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                
            hoja.setColumnWidth(0, 3000);
            hoja.setColumnWidth(2, 3000);
            hoja.setColumnWidth(3, 8000);
            hoja.setColumnWidth(4, 5000);
            try
            {
                InputStream is = new FileInputStream("imagenes/grande300115.jpg");
                byte[] bytes = IOUtils.toByteArray(is);
                int pictureIdx = libro.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                is.close();
                CreationHelper helper = libro.getCreationHelper();
                Drawing drawing = hoja.createDrawingPatriarch();
                ClientAnchor anchor = helper.createClientAnchor();
                anchor.setCol1(0);
                anchor.setRow1(0);
                Picture pict = drawing.createPicture(anchor, pictureIdx);
                pict.resize();
            }catch(Exception e){e.printStackTrace();}
            Row r7 = hoja.createRow(7);
            r7.createCell(0).setCellValue("ORDEN:");
            r7.createCell(1).setCellValue(String.valueOf(ord.getIdOrden()));
            r7.createCell(2).setCellValue("Hrs. Entrega:");
            r7.createCell(3).setCellValue("");
            hoja.addMergedRegion(new CellRangeAddress(7,7,4,7));
            r7.createCell(4).setCellValue("ORDEN PARA SURTIR MUESTRAS");
            r7.getCell(4).setCellStyle(derecha);
                
            Date fecha = new Date();
            DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");//YYYY-MM-DD HH:MM:SS
            String valor=dateFormat.format(fecha);
            Row r8 = hoja.createRow(8);
            r8.createCell(0).setCellValue("Supervisor:");
            hoja.addMergedRegion(new CellRangeAddress(8,8,1,3));
            r8.createCell(1).setCellValue("");
            r8.createCell(4).setCellValue("F. Elaboracin:");
            r8.createCell(5).setCellValue(valor);
                
            Row r9 = hoja.createRow(9);
            r9.createCell(0).setCellValue("Comprador:");
            hoja.addMergedRegion(new CellRangeAddress(9,9,1,3));
            r9.createCell(1).setCellValue("");
            r9.createCell(4).setCellValue("F. Entrega:");
            r9.createCell(5).setCellValue("");
                
            Row r10 = hoja.createRow(10);
            r10.createCell(0).setCellValue("Cantidad");
            r10.getCell(0).setCellStyle(titulo);
            hoja.addMergedRegion(new CellRangeAddress(10,10,1,7));
            r10.createCell(1).setCellValue("Descripcin");
            r10.getCell(1).setCellStyle(titulo);
                
            int ren=11;
            for(int r=0;r<(t_datos.getRowCount());r++)
            {
                if((boolean)t_titulos.getValueAt(r, 3)==true)
                {
                    Row fila = hoja.createRow(ren);
                    Cell celda = fila.createCell(0);
                    celda.setCellStyle(derecha_borde);
                    Cell celda1 = fila.createCell(1);
                    celda1.setCellStyle(contenido);
                    fila.createCell(2).setCellStyle(contenido);
                    fila.createCell(3).setCellStyle(contenido);
                    fila.createCell(4).setCellStyle(contenido);
                    fila.createCell(5).setCellStyle(contenido);
                    fila.createCell(6).setCellStyle(contenido);
                    fila.createCell(7).setCellStyle(contenido);
                    //Cell celda8 = fila.createCell(8);
                    //celda8.setCellStyle(costado);
                    try{
                        celda.setCellValue(t_datos.getValueAt(r, 14).toString());
                        hoja.addMergedRegion(new CellRangeAddress(ren,ren,1,7));
                        celda1.setCellValue(t_titulos.getValueAt(r, 2).toString());
                        //celda8.setCellValue("");
                    }catch(Exception e){
                        celda.setCellValue("");
                    }
                    ren++;
                }
            }
                
            Row rx = hoja.createRow(ren+5);
            hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,0,2));
            rx.createCell(0).setCellValue("Recibe Muestras");
            rx.getCell(0).setCellStyle(firma);
            rx.createCell(1).setCellStyle(firma);
            rx.createCell(2).setCellStyle(firma);
            hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,5,7));
            rx.createCell(5).setCellValue("Entrega Muestras");
            rx.getCell(5).setCellStyle(firma);
            rx.createCell(6).setCellStyle(firma);
            rx.createCell(7).setCellStyle(firma);
                
            libro.write(archivo);
            archivo.close();
            Desktop.getDesktop().open(archivoXLS);
        }catch(Exception e)
        {
            e.printStackTrace();
            JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte si el archivo esta abierto");
        }
    }
}