List of usage examples for org.apache.poi.ss.usermodel CellType FORMULA
CellType FORMULA
To view the source code for org.apache.poi.ss.usermodel CellType FORMULA.
Click Source Link
From source file:com.catexpress.util.FormatosPOI.java
public void formatoSolicitud(Solicitud solicitud, Set<Proveedor> proveedores) throws FileNotFoundException, IOException { Workbook wb = new HSSFWorkbook(); Sheet sheet;/*from ww w .ja va 2s . c om*/ int cont = 0; for (Proveedor proveedor : proveedores) { sheet = wb.createSheet(proveedor.getNombre()); Row rTitulo = sheet.createRow(0); CellRangeAddress craTitulo = new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 6 //last column (0-based) ); sheet.addMergedRegion(craTitulo); Cell titulo = rTitulo.createCell(0); titulo.setCellValue("SOLICITUD DE MERCANC?A"); titulo.setCellStyle(estiloHeader(wb, TITULO)); rTitulo.setHeightInPoints(20); Row rUsuario = sheet.createRow(1); CellRangeAddress craUsuario = new CellRangeAddress(1, 1, 0, 6); sheet.addMergedRegion(craUsuario); Cell usuario = rUsuario.createCell(0); usuario.setCellValue((solicitud.getUsuario().getNombre() + " " + solicitud.getUsuario().getApPaterno() + " " + solicitud.getUsuario().getApMaterno()).toUpperCase()); usuario.setCellStyle(estiloHeader(wb, USUARIO)); rUsuario.setHeightInPoints(25); Row rSucursal = sheet.createRow(2); CellRangeAddress craSucursal = new CellRangeAddress(2, 2, 0, 6); sheet.addMergedRegion(craSucursal); Cell sucursal = rSucursal.createCell(0); sucursal.setCellValue("Sucursal: " + solicitud.getSucursal().getNombre()); sucursal.setCellStyle(estiloHeader(wb, SUCURSAL)); RegionUtil.setBorderTop(sucursal.getCellStyle().getBorderTop(), craSucursal, sheet, wb); RegionUtil.setBorderLeft(sucursal.getCellStyle().getBorderLeft(), craSucursal, sheet, wb); RegionUtil.setBorderRight(sucursal.getCellStyle().getBorderRight(), craSucursal, sheet, wb); RegionUtil.setBorderBottom(sucursal.getCellStyle().getBorderBottom(), craSucursal, sheet, wb); rSucursal.setHeightInPoints(20); Row rBlank = sheet.createRow(3); Cell blank; for (int i = 0; i <= 6; i++) { blank = rBlank.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rFecha = sheet.createRow(4); Cell labelFecha = rFecha.createCell(0); labelFecha.setCellValue("FECHA:"); labelFecha.setCellStyle(estiloHeader(wb, LABEL)); CellRangeAddress craFecha = new CellRangeAddress(4, 4, 1, 3); sheet.addMergedRegion(craFecha); Cell fecha = rFecha.createCell(1); fecha.setCellValue(solicitud.getFechaSolicitud()); fecha.setCellStyle(estiloHeader(wb, FECHA)); for (int i = 4; i <= 6; i++) { blank = rFecha.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rVigencia = sheet.createRow(5); Cell labelVigencia = rVigencia.createCell(0); labelVigencia.setCellValue("VIGENCIA:"); labelVigencia.setCellStyle(estiloHeader(wb, LABEL)); CellRangeAddress craVigencia = new CellRangeAddress(5, 5, 1, 3); sheet.addMergedRegion(craVigencia); Cell vigencia = rVigencia.createCell(1); Calendar clndr = Calendar.getInstance(); clndr.setTime(solicitud.getFechaSolicitud()); clndr.add(Calendar.DAY_OF_MONTH, 3); vigencia.setCellValue(clndr.getTime()); vigencia.setCellStyle(estiloHeader(wb, FECHA)); blank = rVigencia.createCell(4); blank.setCellStyle(estiloVacio(wb)); Cell labelNoPedido = rVigencia.createCell(5); labelNoPedido.setCellValue("PEDIDO No:"); labelNoPedido.setCellStyle(estiloCuadro(wb, AMARILLO)); Cell noPedido = rVigencia.createCell(6); noPedido.setCellValue(solicitud.getId()); noPedido.setCellStyle(estiloCuadro(wb, AMARILLO)); Row rHoja = sheet.createRow(6); for (int i = 0; i <= 4; i++) { blank = rHoja.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Cell labelHoja = rHoja.createCell(5); labelHoja.setCellValue("HOJA:"); labelHoja.setCellStyle(estiloCuadro(wb, LABEL)); Cell hoja = rHoja.createCell(6); hoja.setCellValue(++cont + "/" + proveedores.size()); hoja.setCellStyle(estiloCuadro(wb, LABEL)); Row rProveedor = sheet.createRow(7); CellRangeAddress craProveedor = new CellRangeAddress(7, 8, 0, 2); sheet.addMergedRegion(craProveedor); Cell prov = rProveedor.createCell(0); prov.setCellValue(proveedor.getNombre()); prov.setCellStyle(estiloProveedor(wb)); for (int i = 3; i <= 6; i++) { blank = rProveedor.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rProveedor2 = sheet.createRow(8); for (int i = 3; i <= 6; i++) { blank = rProveedor2.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rTotales = sheet.createRow(9); for (int i = 0; i <= 1; i++) { blank = rTotales.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Cell labelTotales = rTotales.createCell(2); labelTotales.setCellValue("TOTALES: "); labelTotales.setCellStyle(estiloTotales(wb)); blank = rTotales.createCell(3); blank.setCellStyle(estiloTotales(wb)); Cell totalSolicitado = rTotales.createCell(4); totalSolicitado.setCellStyle(estiloTotales(wb)); totalSolicitado.setCellType(CellType.FORMULA); totalSolicitado.setCellFormula("SUM(E12:E" + (11 + solicitud.getDetalles().size()) + ")"); Cell totalSurtido = rTotales.createCell(5); totalSurtido.setCellStyle(estiloTotales(wb)); totalSurtido.setCellType(CellType.FORMULA); totalSurtido.setCellFormula("SUM(F12:F" + (11 + solicitud.getDetalles().size()) + ")"); Cell totalNegado = rTotales.createCell(6); totalNegado.setCellStyle(estiloTotales(wb)); totalNegado.setCellType(CellType.FORMULA); totalNegado.setCellFormula("SUM(G12:G" + (11 + solicitud.getDetalles().size()) + ")"); Row rColumnas = sheet.createRow(10); Cell labelCodigo = rColumnas.createCell(0); labelCodigo.setCellValue("CODIGO"); labelCodigo.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelOpciones = rColumnas.createCell(1); labelOpciones.setCellValue("OPCIONES"); labelOpciones.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelModelo = rColumnas.createCell(2); labelModelo.setCellValue("MODELO / MATERIAL / COLOR"); labelModelo.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelTalla = rColumnas.createCell(3); labelTalla.setCellValue("TALLA"); labelTalla.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelSolicitado = rColumnas.createCell(4); labelSolicitado.setCellValue("SOLICITADO"); labelSolicitado.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelSurtido = rColumnas.createCell(5); labelSurtido.setCellValue("SURTIDO"); labelSurtido.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelNegado = rColumnas.createCell(6); labelNegado.setCellValue("NEGADO"); labelNegado.setCellStyle(estiloColumnas(wb, COLUMNA)); Row rValues = sheet.createRow(11); Cell codigo; Cell opciones; Cell modelo; Cell talla; Cell solicitado; Cell surtido; Cell negado; for (Dsolicitud detalle : solicitud.getDetalles()) { if (detalle.getProducto().getProvedor().equals(proveedor)) { codigo = rValues.createCell(0); codigo.setCellValue(detalle.getProducto().getCBarras()); codigo.setCellStyle(estiloColumnas(wb, 0)); opciones = rValues.createCell(1); opciones.setCellValue(" - "); opciones.setCellStyle(estiloColumnas(wb, 0)); modelo = rValues.createCell(2); modelo.setCellValue(detalle.getProducto().getModelo().getNombre() + " / " + detalle.getProducto().getColor().getNombre()); modelo.setCellStyle(estiloColumnas(wb, 0)); talla = rValues.createCell(3); talla.setCellValue(detalle.getProducto().getTalla().getNombre()); talla.setCellStyle(estiloColumnas(wb, 0)); solicitado = rValues.createCell(4); solicitado.setCellValue(detalle.getCantidad()); solicitado.setCellStyle(estiloColumnas(wb, 0)); surtido = rValues.createCell(5); surtido.setCellStyle(estiloColumnas(wb, SURTIDO)); negado = rValues.createCell(6); negado.setCellStyle(estiloColumnas(wb, 0)); } } for (int i = 0; i <= 6; i++) { sheet.autoSizeColumn(i, true); } } // Write the output to a file FileOutputStream fileOut = new FileOutputStream("Solicitud" + solicitud.getId() + ".xls"); wb.write(fileOut); fileOut.close(); }
From source file:com.chlq.fileprocessor.ToCSV.java
License:Apache License
/** * Called to convert a row of cells into a line of data that can later be * output to the CSV file.//from w ww . j av a 2 s .c om * * @param row An instance of either the HSSFRow or XSSFRow classes that * encapsulates information about a row of cells recovered from * an Excel workbook. */ private void rowToCSV(Row row) { Cell cell = null; int lastCellNum = 0; ArrayList<String> csvLine = new ArrayList<String>(); // Check to ensure that a row was recovered from the sheet as it is // possible that one or more rows between other populated rows could be // missing - blank. If the row does contain cells then... if (row != null) { // Get the index for the right most cell on the row and then // step along the row from left to right recovering the contents // of each cell, converting that into a formatted String and // then storing the String into the csvLine ArrayList. lastCellNum = row.getLastCellNum(); for (int i = 0; i <= lastCellNum; i++) { cell = row.getCell(i); if (cell == null) { csvLine.add(""); } else { if (cell.getCellTypeEnum() != CellType.FORMULA) { csvLine.add(this.formatter.formatCellValue(cell)); } else { csvLine.add(this.formatter.formatCellValue(cell, this.evaluator)); } } } // Make a note of the index number of the right most cell. This value // will later be used to ensure that the matrix of data in the CSV file // is square. if (lastCellNum > this.maxRowWidth) { this.maxRowWidth = lastCellNum; } } this.csvData.add(csvLine); }
From source file:com.cloudera.sa.ExcelRecordReader.java
License:Apache License
private Text getCellValue(Cell cell) { Text out = new Text(); CellType cellType = cell.getCellTypeEnum(); if (cellType == CellType.STRING) { out.set(cell.getStringCellValue()); } else if (cellType == CellType.NUMERIC) { out.set(String.valueOf(cell.getNumericCellValue())); } else if (cellType == CellType.FORMULA) { out.set(cell.getCellFormula());//from w ww . ja v a 2s.c o m } else if (cellType == CellType.ERROR) { out.set(String.valueOf(cell.getErrorCellValue())); } else if (cellType == CellType.BOOLEAN) { out.set(String.valueOf(cell.getBooleanCellValue())); } else { out.set(""); } return out; }
From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java
License:Open Source License
protected void readColumnNames() { // read the schema from the first row or the named row. _headerRange = null;/*from w ww . jav a2s .c o m*/ Row headerRow = null; if (StringUtilities.isNotNullOrEmpty(_columnNameAddress)) { _headerRange = new ExcelRange(_columnNameAddress); CellReference cr = _headerRange.getStartCell(); headerRow = _sheet.getRow(cr.getRow()); } else { Iterator<Row> iterator = _sheet.iterator(); headerRow = iterator.next(); } _columnCount = 0; int endColumnIndex = (_headerRange == null) ? -1 : _headerRange.getEndColumn(); String value = ""; _columnLabels = new ArrayList<String>(); _columnAddress = new ArrayList<String>(); List<String> usedLabels = new ArrayList<String>(); for (Cell cell : headerRow) { String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex()); if ((endColumnIndex != -1) && (cell.getColumnIndex() > endColumnIndex)) { break; } CellType ct = cell.getCellTypeEnum(); if (ct == CellType.FORMULA) ct = cell.getCachedFormulaResultTypeEnum(); switch (ct) { case STRING: value = cell.getStringCellValue(); if (usedLabels.contains(value.toLowerCase())) value = String.format("%s_%s%d", value, columnLetter, cell.getRowIndex() + 1); else usedLabels.add(value.toLowerCase()); _columnLabels.add(value); _columnAddress.add(columnLetter); break; case BOOLEAN: case NUMERIC: case FORMULA: case BLANK: _columnLabels.add(String.format("Column_%s", columnLetter)); _columnAddress.add(columnLetter); break; default: break; } } _columnCount = _columnLabels.size(); _dataSchema = new String[_columnCount][2]; _dataTypes = new DataType[_columnCount]; for (int i = 0; i < _columnCount; i++) { _dataSchema[i][0] = _columnLabels.get(i); } }
From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java
License:Open Source License
protected void readRowSchema(Row dataRow, int endColumnIndex) { String cellAddress = ""; String schemaColumnType = null; String currentCellDataType = null; try {//from w ww . j a va2s .co m for (Cell cell : dataRow) { cellAddress = cell.getAddress().toString(); String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex()); int columnIndex = _columnAddress.indexOf(columnLetter); if (columnIndex == -1) { continue; } currentCellDataType = "String"; CellType ct = cell.getCellTypeEnum(); if (ct == CellType.FORMULA) ct = cell.getCachedFormulaResultTypeEnum(); switch (ct) { case STRING: currentCellDataType = "String"; break; case BOOLEAN: currentCellDataType = "Boolean"; break; case NUMERIC: currentCellDataType = "Double"; break; case FORMULA: currentCellDataType = "Object"; break; case BLANK: currentCellDataType = "String"; break; default: break; } // Object, String, Numeric, Boolean schemaColumnType = _dataSchema[columnIndex][1]; if (schemaColumnType == null) { _dataSchema[columnIndex][1] = currentCellDataType; } else if ("Object".equals(schemaColumnType)) { // no change } else if ("String".equals(schemaColumnType) && "Object".equals(currentCellDataType)) { _dataSchema[columnIndex][1] = currentCellDataType; } else if ("Double".equals(schemaColumnType) && "Object|String".contains(currentCellDataType)) { _dataSchema[columnIndex][1] = currentCellDataType; } else if ("Boolean".equals(schemaColumnType) && "Object|String|Double".contains(currentCellDataType)) { _dataSchema[columnIndex][1] = currentCellDataType; } _dataTypes[columnIndex] = DataUtilities.dataTypeToEnum(_dataSchema[columnIndex][1]); columnIndex++; } } catch (Exception ex) { throw new PieException(String.format("Error while reading Excel cell %s for its data type (%s). %s", cellAddress, currentCellDataType, ex.getMessage()), ex); } }
From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java
License:Open Source License
protected Object[] readExcelData(Row excelDataRow) { Object[] data = new Object[_columnCount]; String cellAddress = ""; int dataIndex = 0; try {/* w w w . jav a 2 s .c o m*/ for (Cell cell : excelDataRow) { cellAddress = cell.getAddress().toString(); String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex()); int columnIndex = _columnAddress.indexOf(columnLetter); if (columnIndex == -1) { continue; } CellType ct = cell.getCellTypeEnum(); if (ct == CellType.FORMULA) ct = cell.getCachedFormulaResultTypeEnum(); switch (ct) { case STRING: data[dataIndex] = cell.getStringCellValue(); break; case BOOLEAN: data[dataIndex] = _allTypesStrings ? Boolean.toString(cell.getBooleanCellValue()) : cell.getBooleanCellValue(); break; case NUMERIC: data[dataIndex] = _allTypesStrings ? Double.toString(cell.getNumericCellValue()) : cell.getNumericCellValue(); break; default: data[dataIndex] = _allTypesStrings ? "" : null; break; } dataIndex++; } if (_addFilename) data[data.length - 1] = _filenameOnly; } catch (Exception ex) { throw new PieException( String.format("Error while reading Excel data from cell %s. %s", cellAddress, ex.getMessage()), ex); } return data; }
From source file:com.funtl.framework.smoke.core.commons.excel.ImportExcel.java
License:Apache License
/** * ??/* w w w . j av a2s.c om*/ * * @param row ? * @param column ??? * @return ? */ @SuppressWarnings("deprecation") public Object getCellValue(Row row, int column) { Object val = ""; try { Cell cell = row.getCell(column); if (cell != null) { if (cell.getCellTypeEnum() == CellType.NUMERIC) { val = cell.getNumericCellValue(); } else if (cell.getCellTypeEnum() == CellType.STRING) { val = cell.getStringCellValue(); } else if (cell.getCellTypeEnum() == CellType.FORMULA) { val = cell.getCellFormula(); } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) { val = cell.getBooleanCellValue(); } else if (cell.getCellTypeEnum() == CellType.ERROR) { val = cell.getErrorCellValue(); } } } catch (Exception e) { return val; } return val; }
From source file:com.streamsets.pipeline.lib.parser.excel.Cells.java
License:Apache License
static Field parseCell(Cell cell, FormulaEvaluator evaluator) throws ExcelUnsupportedCellTypeException { CellType cellType = cell.getCellTypeEnum(); // set the cellType of a formula cell to its cached formula result type in order to process it as its result type boolean isFormula = cell.getCellTypeEnum().equals(CellType.FORMULA); if (isFormula) { cellType = cell.getCachedFormulaResultTypeEnum(); }// w w w. j a v a2 s . c o m switch (cellType) { case STRING: return Field.create(cell.getStringCellValue()); case NUMERIC: Double rawValue = cell.getNumericCellValue(); // resolves formulas automatically and gets value without cell formatting String displayValue = isFormula ? evaluator.evaluate(cell).formatAsString() : dataFormatter.formatCellValue(cell); boolean numericallyEquivalent = false; try { numericallyEquivalent = Double.parseDouble(displayValue) == rawValue; } catch (NumberFormatException e) { } if (DateUtil.isCellDateFormatted(cell)) { // It's a date, not a number java.util.Date dt = cell.getDateCellValue(); // if raw number is < 1 then it's a time component only, otherwise date. return rawValue < 1 ? Field.createTime(dt) : Field.createDate(dt); } // some machinations to handle integer values going in without decimal vs. with .0 for rawValue return Field .create(numericallyEquivalent ? new BigDecimal(displayValue) : BigDecimal.valueOf(rawValue)); case BOOLEAN: return Field.create(cell.getBooleanCellValue()); case BLANK: return Field.create(""); default: throw new ExcelUnsupportedCellTypeException(cell, cellType); } }
From source file:com.wuliu.biz.util.export.strategy.WholeOrderExport.java
License:Open Source License
private void evaluate(Sheet sheet, FormulaEvaluator evaluator) { for (int i = 0; i < ROW_MAX; i++) { Row row = sheet.getRow(i);/*from w w w . j av a 2 s . c o m*/ if (row == null) { continue; } for (int j = 0; j < COL_MAX; j++) { Cell cell = row.getCell(j); if (cell == null) { continue; } if (cell.getCellTypeEnum() == CellType.FORMULA) { evaluator.evaluateFormulaCell(cell); } } } }
From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java
License:Apache License
private String getStringCellValue(Cell cell, int originalColumnIndex) throws Exception { String value = null;// w ww. jav a2s.co m if (cell != null) { CellType cellType = cell.getCellTypeEnum(); if (cellType == CellType.FORMULA) { try { value = getDataFormatter().formatCellValue(cell, getFormulaEvaluator()); } catch (Exception e) { if (useCachedValuesForFailedEvaluations) { cellType = cell.getCachedFormulaResultTypeEnum(); if (cellType == CellType.STRING) { if (returnURLInsteadOfName) { Hyperlink link = cell.getHyperlink(); if (link != null) { if (concatenateLabelUrl) { String url = link.getAddress(); if (url == null) { url = ""; } String label = link.getLabel(); if (label == null) { label = ""; } value = label + "|" + url; } else { value = link.getAddress(); } } else { value = cell.getStringCellValue(); } } else { value = cell.getStringCellValue(); } } else if (cellType == CellType.NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { if (defaultDateFormat != null) { Date d = cell.getDateCellValue(); if (d != null) { value = defaultDateFormat.format(d); } } else { value = getDataFormatter().formatCellValue(cell); } } else { if (overrideExcelNumberFormat) { value = getNumberFormat(originalColumnIndex).format(cell.getNumericCellValue()); } else { value = getDataFormatter().formatCellValue(cell); } } } else if (cellType == CellType.BOOLEAN) { value = cell.getBooleanCellValue() ? "true" : "false"; } } else { throw e; } } } else if (cellType == CellType.STRING) { if (returnURLInsteadOfName) { Hyperlink link = cell.getHyperlink(); if (link != null) { if (concatenateLabelUrl) { String url = link.getAddress(); if (url == null) { url = ""; } String label = link.getLabel(); if (label == null) { label = ""; } value = label + "|" + url; } else { value = link.getAddress(); } } else { value = cell.getStringCellValue(); } } else { value = cell.getStringCellValue(); } } else if (cellType == CellType.NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { value = getDataFormatter().formatCellValue(cell); } else { if (overrideExcelNumberFormat) { value = getNumberFormat(originalColumnIndex).format(cell.getNumericCellValue()); } else { value = getDataFormatter().formatCellValue(cell); } } } else if (cellType == CellType.BOOLEAN) { value = cell.getBooleanCellValue() ? "true" : "false"; } else if (cellType == CellType.BLANK) { value = null; } } return value; }