List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellValue
@Override public void setCellValue(boolean value)
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java
License:Open Source License
private static int createTitle(XSSFWorkbook workbook, XSSFSheet worksheet, Service service) { XSSFCellStyle titleStyle = workbook.createCellStyle(); titleStyle.setAlignment(HorizontalAlignment.CENTER); titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFFont txtFont = workbook.createFont(); txtFont.setFontName("calibri"); txtFont.setFontHeightInPoints((short) 11); txtFont.setBold(true);/*from w w w. j a v a 2 s. co m*/ titleStyle.setFont(txtFont); XSSFRow row = worksheet.createRow(0); XSSFCell cell = row.createCell(0); cell.setCellValue( "Inventaire " + service.getDirection().getLibelleCourt() + " - " + service.getLibelleCourt()); cell.setCellStyle(titleStyle); worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); return 2; }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java
License:Open Source License
private static int generateHeader(XSSFSheet worksheet, XSSFWorkbook workbook, int rowNum) { // Now add// ww w . ja va 2s. co m XSSFRow row = worksheet.createRow(rowNum); XSSFCell cell; XSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.index); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerStyle.setBorderBottom(BorderStyle.MEDIUM); headerStyle.setBorderLeft(BorderStyle.MEDIUM); headerStyle.setBorderRight(BorderStyle.MEDIUM); headerStyle.setBorderTop(BorderStyle.MEDIUM); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFFont txtFont = workbook.createFont(); txtFont.setFontName("calibri"); txtFont.setFontHeightInPoints((short) 9); txtFont.setBold(true); headerStyle.setFont(txtFont); cell = row.createCell(0); cell.setCellValue("Photo"); cell.setCellStyle(headerStyle); worksheet.setColumnWidth(0, ConvertImageUnits.pixel2WidthUnits(COLUMN_WIDTH_PX));//4387 cell = row.createCell(1); cell.setCellValue("Rfrence"); cell.setCellStyle(headerStyle); cell = row.createCell(2); cell.setCellValue("Libell"); cell.setCellStyle(headerStyle); cell = row.createCell(3); cell.setCellValue("Stock\n Appock"); cell.setCellStyle(headerStyle); cell.getCellStyle().setWrapText(true); cell = row.createCell(4); cell.setCellValue("Stock\n rel"); cell.setCellStyle(headerStyle); cell.getCellStyle().setWrapText(true); row.setHeight((short) 600); return rowNum + 1; }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java
License:Open Source License
private static void createRow(XSSFSheet worksheet, XSSFWorkbook workbook, ArticleStock article, CatalogueService catalogueService, int rowNumber) throws IOException { int col = 0;// w ww.j a va 2 s . c om XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFCellStyle cellImageStyle = workbook.createCellStyle(); cellImageStyle.setBorderBottom(BorderStyle.THIN); cellImageStyle.setBorderLeft(BorderStyle.THIN); cellImageStyle.setBorderRight(BorderStyle.THIN); cellImageStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellImageStyle.setAlignment(HorizontalAlignment.CENTER); if (rowNumber != 1) { cellStyle.setBorderTop(BorderStyle.THIN); } XSSFFont txtFont = workbook.createFont(); txtFont.setFontName("calibri"); txtFont.setFontHeightInPoints((short) 9); txtFont.setBold(false); cellStyle.setFont(txtFont); XSSFRow row = worksheet.createRow(rowNumber); row.setHeight((short) ROW_HEIGHT_TWIPS);//80px 1600 // Photo File image = null; try { image = catalogueService.getFilePieceJointe(article.getArticleCatalogue().getPhotoArticleCatalogue()); } catch (IllegalArgumentException e) { log.warn("No image to display for article " + article.getArticleCatalogue().getLibelle()); } XSSFCell cell = row.createCell(col); cell.setCellStyle(cellImageStyle); if (image != null) addImage(workbook, worksheet, image, rowNumber); col = col + 1; // Rfrence cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getReferenceArticleStock()); // Libell cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getArticleCatalogue().getLibelle()); // Appock Stock cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getQuantiteStock()); cell.setCellType(CellType.NUMERIC); // Stock reel cell = row.createCell(col); cell.setCellStyle(cellStyle); cell.setCellType(CellType.NUMERIC); }
From source file:net.clementlevallois.gradingpics.io.Excel.java
public void writeFGradeForOneStudent(String name, String grade) throws FileNotFoundException, IOException { XSSFWorkbook workbook;/*w w w.j a va2 s .c o m*/ FileInputStream file = null; if (Files.exists(Paths.get("grades_2018.xlsx"))) { file = new FileInputStream(new File("grades_2018.xlsx")); workbook = new XSSFWorkbook(file); } else { workbook = new XSSFWorkbook(); } while (workbook.getNumberOfSheets() < 4) { workbook.createSheet(String.valueOf(String.valueOf(workbook.getNumberOfSheets() + 1))); } XSSFSheet sheet = workbook.getSheetAt(1); int lastRowNumber = sheet.getLastRowNum(); //Create a new row in current sheet XSSFRow row = sheet.createRow(lastRowNumber + 1); //Create a new cell in current row XSSFCell cellName = row.createCell(0); //Set value to new value cellName.setCellValue(name); XSSFCell cellGrade = row.createCell(1); cellGrade.setCellValue(grade); //close the excel file when done if (file != null) { file.close(); } FileOutputStream fos = new FileOutputStream(new File("grades_2018.xlsx")); workbook.write(fos); fos.close(); }
From source file:net.mcnewfamily.rmcnew.model.excel.CellEssence.java
License:Open Source License
public XSSFCell toXSSFCell(XSSFRow row, int columnIndex) { if (row != null && columnIndex >= 0) { XSSFCell cell = row.createCell(columnIndex); cell.setCellType(this.cellType.toPoiCellType()); if (this.cellStyleEssence != null) { XSSFCellStyle cellStyle = this.cellStyleEssence.toXSSFCellStyle(row.getSheet().getWorkbook()); cell.setCellStyle(cellStyle); }/*from ww w.ja v a 2 s .c o m*/ cell.setCellValue(this.value); return cell; } else { throw new IllegalArgumentException( "Cannot create XSSFCell on null XSSFRow cannot be null or using invalid columnIndex!"); } }
From source file:net.mcnewfamily.rmcnew.shared.Util.java
License:Open Source License
public static void copyXSSFCell(XSSFCell srcCell, XSSFCell destCell) { if (srcCell != null && destCell != null) { switch (srcCell.getCellType()) { case Cell.CELL_TYPE_STRING: destCell.setCellType(Cell.CELL_TYPE_STRING); destCell.setCellValue(srcCell.getRichStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: destCell.setCellType(Cell.CELL_TYPE_NUMERIC); if (DateUtil.isCellDateFormatted(srcCell)) { destCell.setCellValue(srcCell.getDateCellValue()); } else { destCell.setCellValue(srcCell.getNumericCellValue()); }//from www. ja v a2 s.c o m break; case Cell.CELL_TYPE_BOOLEAN: destCell.setCellType(Cell.CELL_TYPE_BOOLEAN); destCell.setCellValue(srcCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: destCell.setCellType(Cell.CELL_TYPE_FORMULA); destCell.setCellValue(srcCell.getCellFormula()); break; } copyXSSFCellStyle(srcCell, destCell); } else { throw new IllegalArgumentException("Cannot copy from / to null XSSFCell!"); } }
From source file:nl.architolk.ldt.processors.ExcelSerializer.java
License:Open Source License
protected void readInput(final PipelineContext pipelineContext, final ProcessorInput input, Config config, OutputStream outputStream) { try {//from www. j a v a2 s.c o m // Read the input as a DOM final Document domDocument = readInputAsDOM(pipelineContext, input); // create workbook (xlsx) XSSFWorkbook wb = new XSSFWorkbook(); //iterate through sheets; NodeList sheetNodes = domDocument.getElementsByTagName("sheet"); if (sheetNodes.getLength() == 0) { throw new OXFException("At least one sheet should be present"); } for (short i = 0; i < sheetNodes.getLength(); i++) { Node sheetNode = sheetNodes.item(i); if (sheetNode.getNodeType() == Node.ELEMENT_NODE) { Element sheetElement = (Element) sheetNode; XSSFSheet sheet = wb.createSheet(sheetElement.getAttribute("name")); //iterate through rows; NodeList rowNodes = sheetNode.getChildNodes(); short rownr = 0; for (short r = 0; r < rowNodes.getLength(); r++) { Node rowNode = rowNodes.item(r); if (rowNode.getNodeType() == Node.ELEMENT_NODE) { XSSFRow row = sheet.createRow(rownr++); //iterate through columns; NodeList columnNodes = rowNode.getChildNodes(); short colnr = 0; for (short c = 0; c < columnNodes.getLength(); c++) { Node columnNode = columnNodes.item(c); if (columnNode.getNodeType() == Node.ELEMENT_NODE) { XSSFCell cell = row.createCell(colnr++); cell.setCellValue(columnNode.getTextContent()); } } } } } } // write workbook to stream wb.write(outputStream); outputStream.close(); } catch (Exception e) { throw new OXFException(e); } }
From source file:nmap.parser.ExcelLayout.java
public void Layouting(String nmap, String folderpath) throws FileNotFoundException, IOException { MainList ml = new MainList(); ml.Mainlist(nmap, folderpath);/*w ww. java2s . c o m*/ // System.out.println(ml.getHeadertemp()); //System.out.println(ml.getPopulatefield()); XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet spreadsheet = workbook.createSheet(" PORT STATUS "); //Create row object XSSFRow row; //This data needs to be written (Object[]) //setHeadertemp(header); // setPopulatefield(fieldlayout); // String s = fieldlayout.get(0).toString(); //System.out.println("MainList class: "+s); Map<String, Object[]> empinfo = new TreeMap<String, Object[]>(); empinfo.put("1", ml.getHeadertemp().toArray()); int ind = 2; // empinfo.put("2", fieldlayout.get(0).toArray()); for (int h = 0; h < ml.getPopulatefield().size(); h++) { String index = Integer.toString(ind); empinfo.put(index, ml.getPopulatefield().get(h).toArray()); ind++; } Set<String> keyid = empinfo.keySet(); int rowid = 0; for (String key : keyid) { row = spreadsheet.createRow(rowid++); Object[] objectArr = empinfo.get(key); int cellid = 0; for (Object obj : objectArr) { XSSFCell cell = row.createCell(cellid++); cell.setCellValue((String) obj); } } // Write the workbook in file system String hhaha = "Kingkunta.xlsx"; FileOutputStream out = new FileOutputStream(new File(hhaha)); workbook.write(out); out.close(); System.out.println(hhaha + ".xlsx written successfully"); }
From source file:offishell.excel.Excel.java
License:MIT License
public Excel calculate(Object model) { Map<CellAddress, XSSFComment> cellComments = sheet.getCellComments(); VariableContext context = new VariableContext(path, false, model); for (Iterator<Entry<CellAddress, XSSFComment>> iterator = cellComments.entrySet().iterator(); iterator .hasNext();) {//from w w w . j a v a2 s .c o m Entry<CellAddress, XSSFComment> entry = iterator.next(); CellAddress address = entry.getKey(); String comment = entry.getValue().getString().getString().strip(); entry.getValue().setVisible(false); XSSFCell cell = sheet.getRow(address.getRow()).getCell(address.getColumn()); cell.setCellValue(context.apply(comment)); cell.removeCellComment(); } try { return save(Files.createTempFile("calculated", ".xlsx")); } catch (IOException e) { throw I.quiet(e); } }
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();/*from ww w. jav a2 s.com*/ 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; }