List of usage examples for org.apache.poi.ss.usermodel Cell getCellStyle
CellStyle getCellStyle();
From source file:midas.sheeco.type.adapter.SpreadsheetDateAdapterTest.java
License:Apache License
@Test public void testNumericTypeDate() { Date expected = new Date(111111); CellStyle style = mock(CellStyle.class); when(style.getDataFormat()).thenReturn((short) 0x0e); Cell cell = mock(Cell.class); when(cell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC); when(cell.getDateCellValue()).thenReturn(expected); when(cell.getCellStyle()).thenReturn(style); Date value = adapter.fromSpreadsheet(cell); Assert.assertEquals(expected, value); }
From source file:net.illustrato.ctrl.CtrlCore.java
private Row copyRow(Workbook workbook, Sheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row Row newRow = worksheet.getRow(destinationRowNum); Row sourceRow = worksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {//from w w w. j a v a 2 s .c om newRow = worksheet.createRow(destinationRowNum); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell CellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); //Si tenemos que modificar la formulario lo podemos hacer como string //oldCell.getCellFormula().replace("A"+sourceRowNum, "A"+destinationRowNum) break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } return newRow; }
From source file:net.sf.excelutils.tags.EachTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { String expr = ""; String each = curCell.getStringCellValue(); LOG.debug("EachTag:" + each); StringTokenizer st = new StringTokenizer(each, " "); String widthstr = ""; String onstr = ""; int pos = 0;//from w ww. jav a 2 s .c o m while (st.hasMoreTokens()) { String str = st.nextToken(); if (pos == 1) { expr = str; } if (pos == 2 && !"on".equals(str)) { widthstr = str; } if (pos == 3 && !"on".equals(str)) { onstr = str; } if (pos == 4) { onstr = str; } pos++; } int[] widths = new int[0]; if (null != widthstr && !"".equals(widthstr)) { Object o = ExcelParser.parseStr(context, widthstr); if (null != o) { String[] s = o.toString().split(","); widths = new int[s.length]; for (int i = 0; i < widths.length; i++) { widths[i] = Integer.parseInt(s[i]); } } } Object obj = ExcelParser.parseExpr(context, expr); if (null == obj) return new int[] { 0, 0, 0 }; // by onstr get the property if (!"".equals(onstr)) { obj = ExcelParser.parseExpr(context, onstr); if (null == obj) return new int[] { 0, 0, 0 }; } // iterator properties Iterator it = ExcelParser.getIterator(obj); if (null == it) { if (obj instanceof DynaBean) { it = ExcelParser.getIterator(ExcelParser.getBeanProperties(((DynaBean) obj).getDynaClass())); } else { it = ExcelParser.getIterator(ExcelParser.getBeanProperties(obj.getClass())); } } if (null == it) { return new int[] { 0, 0, 0 }; } int index = 0; int arrayIndex = 0; int eachPos = curCell.getColumnIndex(); String modelName = expr.substring(ExcelParser.VALUED_DELIM.length(), expr.length() - ExcelParser.VALUED_DELIM2.length()); // restore the obj obj = ExcelParser.parseExpr(context, expr); while (it.hasNext()) { Object o = it.next(); String property = ""; if (o instanceof Field) { property = ((Field) o).getName(); } else if (o instanceof Map.Entry) { property = ((Map.Entry) o).getKey().toString(); } else if (o instanceof DynaProperty) { property = ((DynaProperty) o).getName(); } else if (null != o) { property = o.toString(); } // test the object is array/list or other if (obj.getClass().isArray() || obj instanceof Collection) { property = modelName + "[" + arrayIndex++ + "]"; } else { property = modelName + "." + property; } Object value = ExcelParser.getValue(context, property); if (null == value) value = ""; if (ExcelUtils.isCanShowType(value)) { // get cell merge count int width = 1; if (index < widths.length) { width = widths[index]; } else if (1 == widths.length) { width = widths[0]; } // get row merged of the curCell int rowMerged = 1; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == curCell.getColumnIndex() && r.getLastColumn() == curCell.getColumnIndex()) { rowMerged = r.getLastRow() - r.getFirstRow() + 1; break; } } Cell cell = WorkbookUtils.getCell(curRow, eachPos); // shift the after cell if (index > 0) { WorkbookUtils.shiftCell(sheet, curRow, cell, 1, rowMerged); } if (width > 1) { Cell nextCell = WorkbookUtils.getCell(curRow, eachPos + 1); WorkbookUtils.shiftCell(sheet, curRow, nextCell, width - 1, rowMerged); } // copy the style of curCell for (int rownum = curRow.getRowNum(); rownum < curRow.getRowNum() + rowMerged; rownum++) { for (int i = 0; i < width; i++) { Row r = WorkbookUtils.getRow(rownum, sheet); Cell c = WorkbookUtils.getCell(r, eachPos + i); Cell cc = WorkbookUtils.getCell(r, curCell.getColumnIndex()); c.setCellStyle(cc.getCellStyle()); } } // merge cells if (width > 1 || rowMerged > 1) { sheet.addMergedRegion( new CellRangeAddress(curRow.getRowNum(), curRow.getRowNum() + rowMerged - 1, cell.getColumnIndex(), cell.getColumnIndex() + width - 1)); } cell.setCellValue("${" + property + "}"); ExcelParser.parseCell(context, sheet, curRow, cell); eachPos += width; index++; } } return new int[] { 0, 0, 0 }; }
From source file:net.sf.excelutils.WorkbookUtils.java
License:Apache License
/** * copy row/*from www .j a v a2 s.c om*/ * * @param sheet * @param from begin of the row * @param to destination fo the row * @param count count of copy */ public static void copyRow(Sheet sheet, int from, int to, int count) { for (int rownum = from; rownum < from + count; rownum++) { Row fromRow = sheet.getRow(rownum); Row toRow = getRow(to + rownum - from, sheet); if (null == fromRow) return; toRow.setHeight(fromRow.getHeight()); toRow.setHeightInPoints(fromRow.getHeightInPoints()); int lastCellNum = fromRow.getLastCellNum(); lastCellNum = lastCellNum > 255 ? 255 : lastCellNum; for (int i = fromRow.getFirstCellNum(); i <= lastCellNum && i >= 0; i++) { Cell fromCell = getCell(fromRow, i); Cell toCell = getCell(toRow, i); // toCell.setEncoding(fromCell.getEncoding()); toCell.setCellStyle(fromCell.getCellStyle()); toCell.setCellType(fromCell.getCellType()); switch (fromCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: toCell.setCellFormula(fromCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: toCell.setCellValue(fromCell.getStringCellValue()); break; default: } } } // copy merged region List shiftedRegions = new ArrayList(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= from && r.getLastRow() < from + count) { CellRangeAddress n_r = new CellRangeAddress(r.getFirstRow() + to - from, r.getLastRow() + to - from, r.getFirstColumn(), r.getLastColumn()); shiftedRegions.add(n_r); } } // readd so it doesn't get shifted again Iterator iterator = shiftedRegions.iterator(); while (iterator.hasNext()) { CellRangeAddress region = (CellRangeAddress) iterator.next(); sheet.addMergedRegion(region); } }
From source file:net.sf.excelutils.WorkbookUtils.java
License:Apache License
public static void shiftCell(Sheet sheet, Row row, Cell beginCell, int shift, int rowCount) { if (shift == 0) return;/*from www .ja va2 s. c om*/ // get the from & to row int fromRow = row.getRowNum(); int toRow = row.getRowNum() + rowCount - 1; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == row.getRowNum()) { if (r.getLastRow() > toRow) { toRow = r.getLastRow(); } if (r.getFirstRow() < fromRow) { fromRow = r.getFirstRow(); } } } for (int rownum = fromRow; rownum <= toRow; rownum++) { Row curRow = WorkbookUtils.getRow(rownum, sheet); int lastCellNum = curRow.getLastCellNum(); for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) { Cell fromCell = WorkbookUtils.getCell(curRow, cellpos); Cell toCell = WorkbookUtils.getCell(curRow, cellpos + shift); toCell.setCellType(fromCell.getCellType()); toCell.setCellStyle(fromCell.getCellStyle()); switch (fromCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: toCell.setCellFormula(fromCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: toCell.setCellValue(fromCell.getStringCellValue()); break; case Cell.CELL_TYPE_ERROR: toCell.setCellErrorValue(fromCell.getErrorCellValue()); break; } fromCell.setCellValue(""); fromCell.setCellType(Cell.CELL_TYPE_BLANK); // Workbook wb = new Workbook(); // CellStyle style = wb.createCellStyle(); // fromCell.setCellStyle(style); } // process merged region for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) { Cell fromCell = WorkbookUtils.getCell(curRow, cellpos); List shiftedRegions = new ArrayList(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == fromCell.getColumnIndex()) { r.setFirstColumn((short) (r.getFirstColumn() + shift)); r.setLastColumn((short) (r.getLastColumn() + shift)); // have to remove/add it back shiftedRegions.add(r); sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } // readd so it doesn't get shifted again Iterator iterator = shiftedRegions.iterator(); while (iterator.hasNext()) { CellRangeAddress region = (CellRangeAddress) iterator.next(); sheet.addMergedRegion(region); } } } }
From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java
License:Open Source License
private void updateCellColor(Cell cell, short color) { CellStyle style = workbook.createCellStyle(); ;//from w w w .jav a 2 s. com style.cloneStyleFrom(cell.getCellStyle()); style.setFillForegroundColor(color); style.setFillPattern(PatternFormatting.SOLID_FOREGROUND); cell.setCellStyle(style); }
From source file:nl.meine.scouting.solparser.writer.ExcelWriterTest.java
License:Open Source License
@Test public void testUpdates() throws Throwable { instance.write();// w w w .ja va 2 s .c o m instance.postprocess(sorter.getOrder()); instance.closeWriter(); File twopersons = ParserTest.getResource("twopersons_update.csv"); Parser p = new Parser(twopersons, SorterFactory.createSorter(SorterFactory.SORTER_ONLYALL)); p.read(true); persons = p.getAllPersons(); sortedPersons = p.getSortedPersons(); instance = new ExcelWriter("dummy.xls"); instance.setAllPersons(allPersons); instance.setSortedPersons(sortedPersons); instance.init(); instance.write(); instance.postprocess(sorter.getOrder()); instance.closeWriter(); Workbook wb = instance.workbook; Sheet s = wb.getSheetAt(0); assertEquals(4, s.getPhysicalNumberOfRows()); // lidnummer: 161616 is nieuw // lidnummer 16: straat geupdatet // lidnummer 1616 alles gelijk for (int i = 1; i < s.getPhysicalNumberOfRows(); i++) { Row r = s.getRow(i); Cell lidnummer = r.getCell(0); if (lidnummer.getStringCellValue().equals("16")) { Cell straat = r.getCell(6); Cell huisnummer = r.getCell(7); assertEquals(ExcelWriter.COLOR_UPDATED, straat.getCellStyle().getFillForegroundColor()); assertEquals(ExcelWriter.COLOR_UPDATED, huisnummer.getCellStyle().getFillForegroundColor()); } else if (lidnummer.getStringCellValue().equals("1616")) { for (int j = 0; j < r.getPhysicalNumberOfCells(); j++) { Cell c = r.getCell(j); assertEquals(IndexedColors.AUTOMATIC.index, c.getCellStyle().getFillForegroundColor()); } } else if (lidnummer.getStringCellValue().equals("161616")) { for (int j = 0; j < r.getPhysicalNumberOfCells(); j++) { Cell c = r.getCell(j); assertEquals(ExcelWriter.COLOR_NEW, c.getCellStyle().getFillForegroundColor()); } } } }
From source file:opn.greenwebs.FXMLDocumentController.java
private File createStockFile(List<ItemDB> list) { int nSize = list.size(); XSSFWorkbook wbs = createStockWorkbook(); XSSFSheet sheetStock = wbs.getSheet("Digital Version"); List<XSSFTable> lTables = sheetStock.getTables(); // Create a FormulaEvaluator to use FormulaEvaluator mainWorkbookEvaluator = sheetStock.getWorkbook().getCreationHelper() .createFormulaEvaluator();// w w w . j av a 2s . c o m File fStock = createFilename("STK", ""); Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault())); Row rowed = sheetStock.getRow(6); Cell celled = rowed.getCell(10); CellStyle cellStyle = celled.getCellStyle(); XSSFFont font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); cellStyle.setFont(font); celled.setCellValue(Date.from(instant)); celled.setCellStyle(cellStyle); rowed = sheetStock.getRow(10); celled = rowed.getCell(2); celled.setCellValue(fStock.getName().substring(0, fStock.getName().length() - 5)); if (!lTables.isEmpty()) { XSSFTable table = lTables.get(0); table.getCTTable() .setRef(new CellRangeAddress(table.getStartCellReference().getRow(), table.getEndCellReference().getRow() + nSize, table.getStartCellReference().getCol(), table.getEndCellReference().getCol()).formatAsString()); XSSFRow row; XSSFCell cell; font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); int nCellRef = table.getStartCellReference().getRow() + 1; for (ItemDB itemdb : list) { row = sheetStock.createRow(nCellRef++); cell = row.createCell(0); cellStyle = cell.getCellStyle(); cell.setCellValue(itemdb.getDblQty()); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(itemdb.getStrMfr()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(itemdb.getStrSKU()); cell.setCellStyle(cellStyle); cell = row.createCell(3); cell.setCellValue(itemdb.getStrDescrip()); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellValue(itemdb.getStrSupplier()); cell.setCellStyle(cellStyle); cell = row.createCell(5); cell.setCellValue(itemdb.getStrSupPart()); cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(cellStyle); cell = row.createCell(6); cell.setCellValue(itemdb.getDblSalePrice()); cell.setCellStyle(cellStyle); cell = row.createCell(7); cell.setCellValue(itemdb.getDblCost()); cell.setCellStyle(cellStyle); /*cell = row.createCell(8); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula("IF(A" + nCellRef + ">0,IF(G" + nCellRef + ">0,IF(H" + nCellRef + ">0,A" + nCellRef + "*G" + nCellRef + "-A" + nCellRef + "*H" + nCellRef + ",\"\"),\"\"),\"\")"); mainWorkbookEvaluator.evaluateFormulaCell(cell); cell.setCellStyle(cellStyle); cell = row.createCell(9); cell.setCellFormula("IF(I" + nCellRef + "<>\"\",I" + nCellRef + "/(A" + nCellRef + "*G" + nCellRef + "),\"\")"); mainWorkbookEvaluator.evaluateFormulaCell(cell); CellStyle style = wbs.createCellStyle(); style.setDataFormat(wbs.createDataFormat().getFormat("0%")); cell.setCellStyle(style);*/ mainWorkbookEvaluator.evaluateAll(); } try { try (FileOutputStream fileOut = new FileOutputStream(fStock)) { wbs.write(fileOut); return fStock; } } catch (FileNotFoundException ex) { logger.info(ex.getLocalizedMessage()); } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } } return null; }
From source file:opn.greenwebs.FXMLDocumentController.java
private void inject(XSSFWorkbook wb, Object obj, int row, int col) { if (wb == null) { System.out.println("wb is null"); }//from w w w .j a va 2 s. co m XSSFSheet sheet = wb.getSheet("Digital Version"); Row rowed = sheet.getRow(row); Cell cell = rowed.getCell(col); CellStyle cellStyle = cell.getCellStyle(); XSSFFont font = sheet.getWorkbook().createFont(); font.setFontHeight(14); cellStyle.setFont(font); if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof Date) { CreationHelper createHelper = wb.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy")); cell.setCellValue((Date) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } else if (obj instanceof Integer) { cell.setCellValue((int) obj); } cell.setCellStyle(cellStyle); }
From source file:org.ado.minesync.translation.ExportFile.java
License:Open Source License
private boolean isCellFormatted(Cell cell) { return cell.getCellStyle().getFillBackgroundColorColor() != null; }