List of usage examples for org.apache.poi.ss.usermodel Workbook getCellStyleAt
CellStyle getCellStyleAt(int idx);
From source file:com.github.jferard.spreadsheetwrapper.xls.poi.XlsPoiStyleHelper.java
License:Open Source License
/** * @param workbook// www . ja v a2s .c o m * internal workbook * @param styleName * the name of the style * @return the internal style, null if none */ public/*@Nullable*/CellStyle getCellStyle(final Workbook workbook, final String styleName) { CellStyle cellStyle = this.cellStyleAccessor.getCellStyle(styleName); if (cellStyle == null && styleName.startsWith("ssw")) { try { final int idx = Integer.valueOf(styleName.substring(3)); cellStyle = workbook.getCellStyleAt((short) idx); } catch (final NumberFormatException e) { // NOPMD by Julien on // 22/11/15 07:15 // do nothing } } return cellStyle; }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
/** * Updates the cell value and type, causes a recalculation of all the values * in the cell.// w w w. j a v a 2s. co m * * If there is a {@link CellValueHandler} defined, then it is used. * * Cells starting with "=" or "+" will be created/changed into FORMULA type. * * Cells that are existing and are NUMERIC type will be parsed according to * their existing format, or if that fails, as Double. * * Cells not containing any letters and containing at least one number will * be created/changed into NUMERIC type (formatting is not changed). * * Existing Boolean cells will be parsed as Boolean. * * For everything else and if any of the above fail, the cell will get the * STRING type and the value will just be a string, except empty values will * cause the cell type to be BLANK. * * @param col * Column index of target cell, 1-based * @param row * Row index of target cell, 1-based * @param value * The new value to set to the target cell, formulas will start * with an extra "=" or "+" */ public void onCellValueChange(int col, int row, String value) { Workbook workbook = spreadsheet.getWorkbook(); // update cell value final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); Row r = activeSheet.getRow(row - 1); if (r == null) { r = activeSheet.createRow(row - 1); } Cell cell = r.getCell(col - 1); String formattedCellValue = null; int oldCellType = -1; // capture cell value to history CellValueCommand command = new CellValueCommand(spreadsheet); command.captureCellValues(new CellReference(row - 1, col - 1)); spreadsheet.getSpreadsheetHistoryManager().addCommand(command); boolean updateHyperlinks = false; if (getCustomCellValueHandler() == null || getCustomCellValueHandler().cellValueUpdated(cell, activeSheet, col - 1, row - 1, value, getFormulaEvaluator(), formatter)) { Exception exception = null; try { // handle new cell creation SpreadsheetStyleFactory styler = spreadsheet.getSpreadsheetStyleFactory(); final Locale spreadsheetLocale = spreadsheet.getLocale(); if (cell == null) { cell = r.createCell(col - 1); } else { // modify existing cell, possibly switch type formattedCellValue = getFormattedCellValue(cell); final String key = SpreadsheetUtil.toKey(col, row); oldCellType = cell.getCellType(); if (!sentCells.remove(key)) { sentFormulaCells.remove(key); } // Old value was hyperlink => needs refresh if (cell.getCellType() == Cell.CELL_TYPE_FORMULA && cell.getCellFormula().startsWith("HYPERLINK")) { updateHyperlinks = true; } } if (formulaFormatter.isFormulaFormat(value)) { if (formulaFormatter.isValidFormulaFormat(value, spreadsheetLocale)) { spreadsheet.removeInvalidFormulaMark(col, row); getFormulaEvaluator().notifyUpdateCell(cell); cell.setCellType(Cell.CELL_TYPE_FORMULA); cell.setCellFormula( formulaFormatter.unFormatFormulaValue(value.substring(1), spreadsheetLocale)); getFormulaEvaluator().notifySetFormula(cell); if (value.startsWith("=HYPERLINK(") && cell.getCellStyle().getIndex() != hyperlinkStyleIndex) { // set the cell style to link cell CellStyle hyperlinkCellStyle; if (hyperlinkStyleIndex == -1) { hyperlinkCellStyle = styler.createHyperlinkCellStyle(); hyperlinkStyleIndex = -1; } else { hyperlinkCellStyle = workbook.getCellStyleAt(hyperlinkStyleIndex); } cell.setCellStyle(hyperlinkCellStyle); styler.cellStyleUpdated(cell, true); updateHyperlinks = true; } } else { // it's formula but invalid cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); spreadsheet.markInvalidFormula(col, row); } } else { spreadsheet.removeInvalidFormulaMark(col, row); Double percentage = SpreadsheetUtil.parsePercentage(value, spreadsheetLocale); Double numVal = SpreadsheetUtil.parseNumber(cell, value, spreadsheetLocale); if (value.isEmpty()) { cell.setCellType(Cell.CELL_TYPE_BLANK); } else if (percentage != null) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); CellStyle cs = cell.getCellStyle(); if (cs == null) { cs = workbook.createCellStyle(); cell.setCellStyle(cs); } if (cs.getDataFormatString() != null && !cs.getDataFormatString().contains("%")) { cs.setDataFormat(workbook.createDataFormat() .getFormat(spreadsheet.getDefaultPercentageFormat())); styler.cellStyleUpdated(cell, true); } cell.setCellValue(percentage); } else if (numVal != null) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numVal); } else if (oldCellType == Cell.CELL_TYPE_BOOLEAN) { cell.setCellValue(Boolean.parseBoolean(value)); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); } getFormulaEvaluator().notifyUpdateCell(cell); } } catch (FormulaParseException fpe) { try { exception = fpe; // parses formula cell.setCellFormula(value.substring(1).replace(" ", "")); } catch (FormulaParseException fpe2) { exception = fpe2; /* * We could force storing the formula even if it is invalid. * Instead, just store it as the value. Clearing the formula * makes sure the value is displayed as-is. */ cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); spreadsheet.markInvalidFormula(col, row); } } catch (NumberFormatException nfe) { exception = nfe; cell.setCellValue(value); } catch (Exception e) { exception = e; cell.setCellValue(value); } if (cell != null) { markCellForUpdate(cell); if (formattedCellValue == null || !formattedCellValue.equals(getFormattedCellValue(cell)) || oldCellType != cell.getCellType()) { fireCellValueChangeEvent(cell); } } if (exception != null) { LOGGER.log(Level.FINE, "Failed to parse cell value for cell at col " + col + " row " + row + " (" + exception.getMessage() + ")", exception); } } spreadsheet.updateMarkedCells(); if (updateHyperlinks) { spreadsheet.loadHyperLinks(); } }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.java
License:Open Source License
/** * Reloads all sheet and cell styles from the current Workbook. *///from w w w .ja v a 2 s . com public void reloadWorkbookStyles() { final Workbook workbook = spreadsheet.getWorkbook(); if (spreadsheet.getState().cellStyleToCSSStyle == null) { spreadsheet.getState().cellStyleToCSSStyle = new HashMap<Integer, String>(workbook.getNumCellStyles()); } else { spreadsheet.getState().cellStyleToCSSStyle.clear(); } shiftedBorderLeftStyles.clear(); shiftedBorderTopStyles.clear(); mergedCellBorders.clear(); // get default text alignments CellStyle cellStyle = workbook.getCellStyleAt((short) 0); defaultTextAlign = cellStyle.getAlignment(); // defaultVerticalAlign = cellStyle.getVerticalAlignment(); // create default style (cell style 0) StringBuilder sb = new StringBuilder(); borderStyles(sb, cellStyle); defaultFontStyle(cellStyle, sb); colorConverter.defaultColorStyles(cellStyle, sb); spreadsheet.getState().cellStyleToCSSStyle.put((int) cellStyle.getIndex(), sb.toString()); // 0 is default style, create all styles indexed from 1 and upwards for (short i = 1; i < workbook.getNumCellStyles(); i++) { cellStyle = workbook.getCellStyleAt(i); addCellStyleCSS(cellStyle); } reloadActiveSheetColumnRowStyles(); }
From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *//*from ww w . ja v a2 s .co m*/ @Test public void testWriteExcelFileAttribute() throws Exception { try { LOGGER.debug("testWriteExcelFileAttribute start...."); short rowheight = 40 * 10; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xls"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(CellStyle.ALIGN_RIGHT); cs.setFillPattern(CellStyle.DIAMONDS); // ? // ? ? cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment()); LOGGER.debug("getFillPattern : {}", cs1.getFillPattern()); assertEquals(CellStyle.DIAMONDS, cs1.getFillPattern()); LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor()); LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor()); assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor()); assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testWriteExcelFileAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java
License:Apache License
/** * [Flow #-6] ? : ? ?(?, ? )? // w w w. j a v a 2s .c om */ @Test public void testModifyCellAttribute() throws Exception { try { LOGGER.debug("testModifyCellAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellAttribute.xls"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new HSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? Workbook wb = excelService.loadWorkbook(sb.toString()); LOGGER.debug("testModifyCellAttribute after loadWorkbook...."); Sheet sheet = wb.createSheet("cell test sheet2"); // sheet.setColumnWidth((short) 3, (short) 200); // column Width CellStyle cs = wb.createCellStyle(); Font font = wb.createFont(); font.setFontHeight((short) 16); font.setBoldweight((short) 3); font.setFontName("fixedsys"); cs.setFont(font); cs.setAlignment(CellStyle.ALIGN_RIGHT); // cell cs.setWrapText(true); for (int i = 0; i < 100; i++) { Row row = sheet.createRow(i); // row.setHeight((short)300); // row? height for (int j = 0; j < 5; j++) { Cell cell = row.createCell(j); cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sb.toString()); Sheet sheetT = wbT.getSheet("cell test sheet2"); LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles()); CellStyle cs1 = wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1)); Font fontT = ((HSSFCellStyle) cs1).getFont(wbT); LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight()); LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight()); LOGGER.debug("font getFontName : {}", fontT.getFontName()); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); LOGGER.debug("getWrapText : {}", cs1.getWrapText()); for (int i = 0; i < 100; i++) { Row row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { Cell cell1 = row1.getCell(j); LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue()); assertEquals(16, fontT.getFontHeight()); assertEquals(3, fontT.getBoldweight()); assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment()); assertTrue(cs1.getWrapText()); } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *//*from ww w . j a v a2s .c o m*/ @Test public void testWriteExcelFileAttribute() throws Exception { try { log.debug("testWriteExcelFileAttribute start...."); short rowheight = 40; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } SXSSFWorkbook wb = new SXSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cs.setFillPattern(HSSFCellStyle.DIAMONDS); // ? // ? ? cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); log.debug("getAlignment : " + cs1.getAlignment()); assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); log.debug("getFillPattern : " + cs1.getFillPattern()); assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern()); log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor()); log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor()); assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor()); assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testWriteExcelFileAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *//*from www. j av a2 s .c o m*/ @Test public void testWriteExcelFileAttribute() throws Exception { try { LOGGER.debug("testWriteExcelFileAttribute start...."); short rowheight = 40 * 10; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); XSSFCellStyle cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(CellStyle.ALIGN_RIGHT); cs.setFillPattern(CellStyle.DIAMONDS); // ? XSSFRow r1 = sheet1.createRow(0); r1.createCell(0); // ? ? cs.setFillForegroundColor(IndexedColors.BLUE.getIndex()); // cs.setFillBackgroundColor(IndexedColors.RED.getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); LOGGER.debug("getFillPattern : {}", cs1.getFillPattern()); assertEquals(XSSFCellStyle.DIAMONDS, cs1.getFillPattern()); LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor()); LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor()); LOGGER.debug( "XSSFWorkbook.getFillBackgroundColor(), XSSFColor().getIndexed() ? ? 0 ? ?"); assertEquals(IndexedColors.BLUE.getIndex(), cs1.getFillForegroundColor()); assertEquals(IndexedColors.RED.getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testWriteExcelFileAttribute end...."); } }
From source file:org.formulacompiler.spreadsheet.internal.excel.xls.saver.ExcelXLSSaver.java
License:Open Source License
private void extractCellFormatsFrom(Workbook _xwb) { final short styleCount = _xwb.getNumCellStyles(); for (short idx = 0; idx < styleCount; idx++) { final HSSFCellStyle cellStyle = (HSSFCellStyle) _xwb.getCellStyleAt(idx); try {//from ww w . j ava2 s . c o m final String styleName = cellStyle.getParentStyle().getUserStyleName(); if (styleName != null) { this.cellStyles.put(styleName, cellStyle); } } catch (Exception e) { // Do nothing, we use only styles with parents } } }
From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.HSSFCellStyleProducer.java
License:Open Source License
/** * The class does the dirty work of creating the HSSF-objects. * * @param workbook/*from w ww .ja v a 2 s .com*/ * the workbook for which the styles should be created. */ public HSSFCellStyleProducer(final Workbook workbook, final boolean hardLimit, final ExcelColorProducer colorProducer, final ExcelColorProducer fontColorProducer) { this.fontColorProducer = fontColorProducer; if (workbook == null) { throw new NullPointerException(); } if (colorProducer == null) { throw new NullPointerException(); } this.colorProducer = colorProducer; this.styleCache = new HashMap<HSSFCellStyleKey, CellStyle>(); this.workbook = workbook; this.fontFactory = new ExcelFontFactory(workbook, fontColorProducer); this.dataFormat = workbook.createDataFormat(); this.hardLimit = hardLimit; if (workbook instanceof XSSFWorkbook) { final XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook; final int predefinedStyles = workbook.getNumCellStyles(); for (int i = 0; i < predefinedStyles; i++) { final XSSFCellStyle cellStyleAt = xssfWorkbook.getCellStyleAt(i); this.styleCache.put(new HSSFCellStyleKey(cellStyleAt), cellStyleAt); } } else { // Read in the styles ... final int predefinedStyles = workbook.getNumCellStyles(); for (int i = 0; i < predefinedStyles; i++) { final CellStyle cellStyleAt = workbook.getCellStyleAt(i); this.styleCache.put(new HSSFCellStyleKey(cellStyleAt), cellStyleAt); } } }