List of usage examples for org.apache.poi.ss.usermodel Cell getCellStyle
CellStyle getCellStyle();
From source file:com.actelion.research.spiritapp.ui.util.PDFUtils.java
License:Open Source License
private static Chunk getChunk(Workbook wb, Cell cell) { Chunk phrase = null;/*from www . j a v a 2 s .c o m*/ switch (cell.getCellType() == Cell.CELL_TYPE_FORMULA ? cell.getCachedFormulaResultType() : cell.getCellType()) { case Cell.CELL_TYPE_STRING: phrase = new Chunk("" + cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: String format = cell.getCellStyle().getDataFormatString(); if (cell.getCellStyle().getDataFormat() > 0) { try { if (format.contains("0")) { //Decimal DecimalFormat df = new DecimalFormat(format); phrase = new Chunk(df.format(cell.getNumericCellValue())); } else if (format.contains("h:")) { phrase = new Chunk(FormatterUtils.formatDateTimeShort(cell.getDateCellValue())); } else if (format.contains("yy")) { phrase = new Chunk(FormatterUtils.formatDate(cell.getDateCellValue())); } } catch (Exception e) { System.err.println(e); } } if (phrase == null) { phrase = new Chunk("" + (int) cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BLANK: phrase = new Chunk(""); break; default: phrase = new Chunk("" + cell.getCellType()); } Font font = wb.getFontAt(cell.getCellStyle().getFontIndex()); short[] rgb = HSSFColor.getIndexHash().get((int) font.getColor()).getTriplet(); phrase.setFont(new com.lowagie.text.Font(phrase.getFont().getBaseFont(), font.getFontHeightInPoints() - 3, (font.getBold() ? com.lowagie.text.Font.BOLD : com.lowagie.text.Font.NORMAL), new Color(rgb[0], rgb[1], rgb[2]))); return phrase; }
From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java
License:Open Source License
public static void autoSizeColumns(Sheet sheet, int maxColWidth, boolean resizeHeight) { ListHashMap<Integer, Integer> col2lens = new ListHashMap<>(); for (int row = sheet.getFirstRowNum(); row <= sheet.getLastRowNum(); row++) { Row r = sheet.getRow(row);/* www . j av a 2s .c o m*/ if (r == null || r.getFirstCellNum() < 0) continue; short maxH = 0; for (int col = r.getFirstCellNum(); col <= r.getLastCellNum(); col++) { Cell c = r.getCell(col); if (c == null || (c.getCellType() != Cell.CELL_TYPE_STRING && c.getCellType() != Cell.CELL_TYPE_NUMERIC)) continue; Font font = sheet.getWorkbook().getFontAt(c.getCellStyle().getFontIndex()); String s = c.getCellType() == Cell.CELL_TYPE_STRING ? c.getStringCellValue() : "" + c.getNumericCellValue(); String[] lines = MiscUtils.split(s, "\n"); int maxLen = 1; for (int i = 0; i < lines.length; i++) { maxLen = Math.max(lines[i].length(), maxLen); } if (font.getFontHeightInPoints() < 12) { col2lens.add(col, 700 + maxLen * (font.getFontHeightInPoints() + (font.getBoldweight() > 500 ? 1 : 0)) * 20); } maxH = (short) Math.max(maxH, 50 + lines.length * (font.getFontHeight() * 1.2)); } if (resizeHeight) r.setHeight(maxH); } for (int col : col2lens.keySet()) { List<Integer> lens = col2lens.get(col); Collections.sort(lens); int len = lens.get(lens.size() - 1); if (lens.size() > 10 && lens.get(lens.size() - 1) > 2 * lens.get(lens.size() - 2)) { len = lens.get(lens.size() - 2); } sheet.setColumnWidth(col, Math.max(Math.min((int) (len * 1.25), maxColWidth > 0 ? maxColWidth : 300000), 1500)); } }
From source file:com.adobe.acs.commons.data.Variant.java
License:Apache License
private void setValue(Cell cell) { int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA) { cellType = cell.getCachedFormulaResultType(); }/*w w w. ja v a 2 s . co m*/ switch (cellType) { case Cell.CELL_TYPE_BOOLEAN: setValue(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: double number = cell.getNumericCellValue(); if (Math.floor(number) == number) { setValue((long) number); } else { setValue(number); } if (DateUtil.isCellDateFormatted(cell)) { setValue(cell.getDateCellValue()); } DataFormatter dataFormatter = new DataFormatter(); if (cellType == Cell.CELL_TYPE_FORMULA) { setValue(dataFormatter.formatCellValue(cell)); } else { CellStyle cellStyle = cell.getCellStyle(); setValue(dataFormatter.formatRawCellContents(cell.getNumericCellValue(), cellStyle.getDataFormat(), cellStyle.getDataFormatString())); } break; case Cell.CELL_TYPE_STRING: setValue(cell.getStringCellValue().trim()); break; case Cell.CELL_TYPE_BLANK: default: clear(); break; } }
From source file:com.b2international.snowowl.datastore.server.importer.ExcelUtilities.java
License:Apache License
/** * Returns true if the cell (NOT the text inside) is set to bold. * @param cell//from w w w . j ava 2s . c o m * @return */ public static boolean isBold(Cell cell) { CellStyle style = cell.getCellStyle(); Workbook workBook = cell.getSheet().getWorkbook(); Font font = workBook.getFontAt(style.getFontIndex()); XSSFFont xssfFont = (XSSFFont) font; return xssfFont.getBold(); }
From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateReader.java
License:Apache License
/** * Called by the TemplateReader./*from www. j a v a2 s. c o m*/ * * @param sheet * the sheet * @param templateSheet * the template sheet * @throws Exception * the exception */ private void populateColumns(Sheet sheet, TemplateSheet templateSheet) throws Exception { Map<String, TemplateColumn> columnMap = templateSheet.getColumnMap(); Row headerRow = sheet.getRow(0); if (headerRow == null) { throw new Exception("No header row found! Please create one."); } Row styleRow = sheet.getRow(1); if (styleRow == null) { throw new Exception( "Sheet name " + templateSheet.getSheetName() + ": No style row found! Please create one."); } for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) { TemplateColumn column = new TemplateColumn(); Cell headerCell = headerRow.getCell(i); Cell styleCell = styleRow.getCell(i); if (headerCell == null) { throw new Exception("The following column appears to be empty: " + i); } if (styleCell == null) { throw new Exception("The following style position is not defined: " + i); } String columnName = headerCell.getStringCellValue(); // We want to use the style cell (row below) as the header will // always be text. Integer cellType = styleCell.getCellType(); column.setColumnPos(i); column.setCellStyle(styleCell.getCellStyle()); column.setColumnName(columnName); column.setCellType(cellType); if (cellType == Cell.CELL_TYPE_FORMULA) { column.setCellFormula(styleCell.getCellFormula()); } columnMap.put(columnName, column); } populateColumnMappings(columnMap); }
From source file:com.canoo.webtest.plugins.exceltest.ExcelVerifyCellStyle.java
License:Open Source License
public void doExecute() throws Exception { final String[] border = separateSides(getBorder()); final String[] borderColor = separateSides(getBorderColor()); for (int i = 0; i < SIDES.length; i++) { checkFormat(SIDES[i] + "Border", border[i], getCellBorder(i)); checkFormat(SIDES[i] + "BorderColor", ExcelColorUtils.lookupStandardColorName(borderColor[i]), getCellBorderColor(i));//from w w w .ja va2s .c o m } final Cell excelCell = getExcelCell(); checkFormat("type", getType(), ExcelCellUtils.getCellType(excelCell == null ? Cell.CELL_TYPE_BLANK : excelCell.getCellType())); if (excelCell == null) { if (cellNotRequired()) { return; } else { throw new StepExecutionException("Can't find cell for " + getCellReferenceStr(), this); } } final CellStyle cellStyle = excelCell.getCellStyle(); checkFormat("format", getFormat(), getExcelWorkbook().createDataFormat().getFormat(cellStyle.getDataFormat())); checkFormat("align", getAlign(), ExcelCellUtils.getAlignmentString(cellStyle.getAlignment())); checkFormat("valign", getValign(), ExcelCellUtils.getVerticalAlignmentString(cellStyle.getVerticalAlignment())); checkFormat("wrap", getWrap(), String.valueOf(cellStyle.getWrapText())); checkFormat("locked", getLocked(), String.valueOf(cellStyle.getLocked())); checkFormat("fontName", getFontName(), getFont(cellStyle).getFontName()); checkFormat("fontSize", getFontSize(), String.valueOf(getFont(cellStyle).getFontHeightInPoints())); checkFormat("fontStyle", sortElements(getFontStyle()), getFontStyle(getFont(cellStyle))); checkFormat("fillColor", ExcelColorUtils.lookupStandardColorName(getFillColor()), ExcelColorUtils.getColorName(this, cellStyle.getFillForegroundColorColor())); checkFormat("fillBackgroundColor", ExcelColorUtils.lookupStandardColorName(getFillBackgroundColor()), ExcelColorUtils.getColorName(this, cellStyle.getFillBackgroundColorColor())); checkFormat("textColor", ExcelColorUtils.lookupStandardColorName(getTextColor()), ExcelColorUtils.getColorName(this, getFont(cellStyle).getColor())); checkFormat("fillPattern", getFillPattern(), ExcelCellUtils.getFillPattern(cellStyle.getFillPattern())); }
From source file:com.canoo.webtest.plugins.exceltest.ExcelVerifyCellStyle.java
License:Open Source License
public String getCellBorder(final int dir) { final Cell excelCellAt = getExcelCell(); short border = CellStyle.BORDER_NONE; if (excelCellAt != null) { border = getBorder(excelCellAt.getCellStyle(), dir); }//from w ww . ja v a2 s . c om if (border == CellStyle.BORDER_NONE) { final Cell adjacentCell = getAdjacentCell(dir); if (adjacentCell != null) { border = getBorder(adjacentCell.getCellStyle(), (dir + 2) % 4); } } return ExcelCellUtils.getBorder(border); }
From source file:com.canoo.webtest.plugins.exceltest.ExcelVerifyCellStyle.java
License:Open Source License
public String getCellBorderColor(final int dir) { final Cell excelCellAt = getExcelCell(); short border = CellStyle.BORDER_NONE; if (excelCellAt != null) { border = getBorderColor(excelCellAt.getCellStyle(), dir); }// ww w.ja v a2 s . c o m if (border == CellStyle.BORDER_NONE) { final Cell adjacentCell = getAdjacentCell(dir); if (adjacentCell != null) { border = getBorderColor(adjacentCell.getCellStyle(), (dir + 2) % 4); } } return ExcelColorUtils.getColorName(this, border); }
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 w w w .j av a 2 s .c o m 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.common.report.util.html.ToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { // printColumnHeads(); out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next();//ww w . j a v a 2 s . c o m out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); //Set the value that is rendered for the cell //also applies the format CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (content.equals("")) content = " "; } } out.format(" <td class=%s %s>%s</td>%n", styleName(style), attrs, content); } out.format(" </tr>%n"); } out.format("</tbody>%n"); }