List of usage examples for org.apache.poi.ss.usermodel Cell setCellValue
void setCellValue(boolean value);
From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java
License:Apache License
/** * {@inheritDoc}/*from w ww .ja v a 2 s .com*/ */ public void setCellValue(Cell cell, Object value) { if (value == null) { cell.setCellType(Cell.CELL_TYPE_BLANK); } else { if (value instanceof Date) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue((Date) value); } else if (value instanceof String) { cell.setCellValue(value.toString()); } else if (value instanceof Boolean) { cell.setCellValue((Boolean) value); } else if (value instanceof Number) { cell.setCellValue(Double.valueOf(value.toString())); } else if (value instanceof Calendar) { cell.setCellValue((Calendar) value); } } }
From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java
License:Apache License
/** * /*from ww w.j a v a 2 s.co m*/ * @param rowIndex */ public void addHeaderRow(int rowIndex) { Sheet sheet = excelSheet.getSheet(); Row row = null; Cell cell = null; row = sheet.createRow(rowIndex + 1); List<String> headers = excelSheet.getHeaders(); for (int i = 0; i < headers.size(); i++) { cell = row.createCell(i); cell.setCellValue(headers.get(i)); } }
From source file:com.funtl.framework.smoke.core.commons.excel.ExportExcel.java
License:Apache License
/** * ?/*from w ww . j a v a 2s .c o m*/ * * @param row * @param column ? * @param val * @param align ??1?23?? * @return ? */ public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) { Cell cell = row.createCell(column); String cellFormatString = "@"; try { if (val == null) { cell.setCellValue(""); } else if (fieldType != Class.class) { cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val)); } else { if (val instanceof String) { cell.setCellValue((String) val); } else if (val instanceof Integer) { cell.setCellValue((Integer) val); cellFormatString = "0"; } else if (val instanceof Long) { cell.setCellValue((Long) val); cellFormatString = "0"; } else if (val instanceof Double) { cell.setCellValue((Double) val); cellFormatString = "0.00"; } else if (val instanceof Float) { cell.setCellValue((Float) val); cellFormatString = "0.00"; } else if (val instanceof Date) { cell.setCellValue((Date) val); cellFormatString = "yyyy-MM-dd HH:mm"; } else { cell.setCellValue((String) Class .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype." + val.getClass().getSimpleName() + "Type")) .getMethod("setValue", Object.class).invoke(null, val)); } } if (val != null) { CellStyle style = styles.get("data_column_" + column); if (style == null) { style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data" + (align >= 1 && align <= 3 ? align : ""))); style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString)); styles.put("data_column_" + column, style); } cell.setCellStyle(style); } } catch (Exception ex) { log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString()); cell.setCellValue(val.toString()); } return cell; }
From source file:com.gezipu360.cashier.bean.word.UpdateEmbeddedDoc.java
License:Apache License
/** * Called to update the embedded Excel workbook. As the format and structire * of the workbook are known in advance, all this code attempts to do is * write a new value into the first cell on the first row of the first * worksheet. Prior to executing this method, that cell will contain the * value 1./*from ww w . j ava2 s . com*/ * * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException * Rather * than use the specific classes (HSSF/XSSF) to handle the embedded * workbook this method uses those defeined in the SS stream. As * a result, it might be the case that a SpreadsheetML file is * opened for processing, throwing this exception if that file is * invalid. * @throws java.io.IOException Thrown if a problem occurs in the underlying * file system. */ public void updateEmbeddedDoc() throws OpenXML4JException, IOException { Workbook workbook = null; Sheet sheet = null; Row row = null; Cell cell = null; PackagePart pPart = null; Iterator<PackagePart> pIter = null; List<PackagePart> embeddedDocs = this.doc.getAllEmbedds(); if (embeddedDocs != null && !embeddedDocs.isEmpty()) { pIter = embeddedDocs.iterator(); while (pIter.hasNext()) { pPart = pIter.next(); if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION) || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) { // Get an InputStream from the pacage part and pass that // to the create method of the WorkbookFactory class. Update // the resulting Workbook and then stream that out again // using an OutputStream obtained from the same PackagePart. workbook = WorkbookFactory.create(pPart.getInputStream()); sheet = workbook.getSheetAt(SHEET_NUM); row = sheet.getRow(ROW_NUM); cell = row.getCell(CELL_NUM); cell.setCellValue(NEW_VALUE); workbook.write(pPart.getOutputStream()); } } // Finally, write the newly modified Word document out to file. this.doc.write(new FileOutputStream(this.docFile)); } }
From source file:com.github.autoprimer3.Primer3ResultViewController.java
License:Open Source License
private void writePrimersToExcel(final File f) throws IOException { final Service<Void> service = new Service<Void>() { @Override/* w ww .j a v a 2 s . c om*/ protected Task<Void> createTask() { return new Task<Void>() { @Override protected Void call() throws IOException { BufferedOutputStream bo = new BufferedOutputStream(new FileOutputStream(f)); Workbook wb = new XSSFWorkbook(); CellStyle hlink_style = wb.createCellStyle(); Font hlink_font = wb.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font); CreationHelper createHelper = wb.getCreationHelper(); Sheet listSheet = wb.createSheet(); Sheet detailsSheet = wb.createSheet(); Row row = null; int rowNo = 0; int sheetNo = 0; wb.setSheetName(sheetNo++, "List"); wb.setSheetName(sheetNo++, "Details"); row = listSheet.createRow(rowNo++); String header[] = { "Primer", "Sequence", "Product Size (bp)" }; for (int col = 0; col < header.length; col++) { Cell cell = row.createCell(col); cell.setCellValue(header[col]); } updateMessage("Writing primers . . ."); updateProgress(0, data.size() * 3); int n = 0; for (Primer3Result r : data) { n++; updateMessage("Writing primer list " + n + " . . ."); row = listSheet.createRow(rowNo++); int col = 0; Cell cell = row.createCell(col++); cell.setCellValue(r.getName() + "F"); cell = row.createCell(col++); cell.setCellValue(r.getLeftPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getProductSize()); updateProgress(n, data.size() * 3); updateMessage("Writing primer list " + n + " . . ."); row = listSheet.createRow(rowNo++); col = 0; cell = row.createCell(col++); cell.setCellValue(r.getName() + "R"); cell = row.createCell(col++); cell.setCellValue(r.getRightPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getProductSize()); n++; updateProgress(n, data.size() * 3); } rowNo = 0; row = detailsSheet.createRow(rowNo++); ArrayList<String> detailsHeader = new ArrayList<>(Arrays.asList("Name", "Other IDs", "Left Primer", "Right Primer", "Product Size (bp)", "Region", "in-silico PCR")); if (ispcrResCol.isVisible()) { detailsHeader.add("in-silico PCR Results"); } for (int col = 0; col < detailsHeader.size(); col++) { Cell cell = row.createCell(col); cell.setCellValue(detailsHeader.get(col)); } int m = 0; for (Primer3Result r : data) { m++; updateMessage("Writing details for pair " + m + " . . ."); row = detailsSheet.createRow(rowNo++); int col = 0; Cell cell = row.createCell(col++); cell.setCellValue(r.getName()); cell = row.createCell(col++); cell.setCellValue(r.getTranscripts()); cell = row.createCell(col++); cell.setCellValue(r.getLeftPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getRightPrimer()); cell = row.createCell(col++); cell.setCellValue(r.getProductSize()); cell = row.createCell(col++); cell.setCellValue(r.getRegion()); cell = row.createCell(col++); if (r.getIsPcrUrl() != null) { cell.setCellValue("isPCR"); org.apache.poi.ss.usermodel.Hyperlink hl = createHelper .createHyperlink(org.apache.poi.ss.usermodel.Hyperlink.LINK_URL); hl.setAddress(r.getIsPcrUrl()); cell.setHyperlink(hl); cell.setCellStyle(hlink_style); } else { cell.setCellValue(""); } if (ispcrResCol.isVisible()) { cell = row.createCell(col++); if (r.getIsPcrResults() != null) { cell.setCellValue(r.getIsPcrResults()); } else { cell.setCellValue(""); } } updateProgress(n + m, data.size() * 3); } updateMessage("Wrote " + data.size() + " primer pairs to file."); wb.write(bo); bo.close(); return null; } }; } }; service.setOnSucceeded(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Action response = Dialogs.create().title("Done").masthead("Finished writing") .message("Primers successfully written to " + f.getAbsolutePath() + "\n\nDo you want to open " + "this file now?") .actions(Dialog.ACTION_YES, Dialog.ACTION_NO).styleClass(Dialog.STYLE_CLASS_NATIVE) .showConfirm(); if (response == Dialog.ACTION_YES) { try { openFile(f); } catch (IOException ex) { Action openFailed = Dialogs.create().title("Open failed") .masthead("Could not open output file") .message("Exception encountered when attempting to open " + "the saved file. See below:") .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(ex); } } progressBar.progressProperty().unbind(); progressBar.setVisible(false); summaryLabel.textProperty().unbind(); summaryLabel.setText(summary); closeButton.setDisable(false); closeMenuItem.setDisable(false); setCheckIsPcrButton(); } }); service.setOnCancelled(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Dialogs writeCancelled = Dialogs.create().title("Writing Cancelled") .masthead("Cancelled writing to file").message("User cancelled writing primers to file.") .styleClass(Dialog.STYLE_CLASS_NATIVE); writeCancelled.showInformation(); progressBar.progressProperty().unbind(); progressBar.setVisible(false); summaryLabel.textProperty().unbind(); summaryLabel.setText(summary); closeButton.setDisable(false); closeMenuItem.setDisable(false); setCheckIsPcrButton(); } }); service.setOnFailed(new EventHandler<WorkerStateEvent>() { @Override public void handle(WorkerStateEvent e) { Action writeFailed = Dialogs.create().title("Writing failed") .masthead("Could not write primers to file") .message("Exception encountered when attempting to write " + "primers to file. See below:") .styleClass(Dialog.STYLE_CLASS_NATIVE).showException(e.getSource().getException()); progressBar.progressProperty().unbind(); progressBar.setVisible(false); summaryLabel.textProperty().unbind(); summaryLabel.setText(summary); closeButton.setDisable(false); closeMenuItem.setDisable(false); setCheckIsPcrButton(); } }); progressBar.setVisible(true); progressBar.progressProperty().bind(service.progressProperty()); summaryLabel.textProperty().bind(service.messageProperty()); closeButton.setDisable(true); closeMenuItem.setDisable(true); checkIsPcrButton.setText("Cancel"); checkIsPcrButton.setOnAction(new EventHandler<ActionEvent>() { @Override public void handle(ActionEvent actionEvent) { service.cancel(); } }); service.start(); }
From source file:com.github.camaral.sheeco.exceptions.OriginalCellValue.java
License:Apache License
public void setCellValue(final Cell cell) { switch (type) { case DATE://from ww w .j av a 2 s .c om cell.setCellValue((Date) value); break; case NUMERIC: cell.setCellValue(((Number) value).doubleValue()); break; case BOOLEAN: cell.setCellValue((Boolean) value); break; case STRING: cell.setCellValue(String.valueOf(value)); break; case ERROR: cell.setCellErrorValue((Byte) value); break; case FORMULA: cell.setCellFormula((String) value); break; default: } }
From source file:com.github.camaral.sheeco.Sheeco.java
License:Apache License
private void createCell(final CreationHelper creationHelper, final Row row, Attribute attribute) { if (row.getCell(attribute.getColumnIndex()) == null) { final Cell cell = row.createCell(attribute.getColumnIndex()); RichTextString cellName = creationHelper.createRichTextString(attribute.getColumnName()); cell.setCellValue(cellName); }// w w w. ja v a 2s. c o m }
From source file:com.github.crab2died.handler.SheetTemplateHandler.java
License:Open Source License
private static void clearCell(Cell cell) { cell.setCellStyle(null); cell.setCellValue(""); }
From source file:com.github.crab2died.handler.SheetTemplateHandler.java
License:Open Source License
/** * ?map??Map??#/*w w w . j a va 2 s. c om*/ * * @param data ? */ public static void extendData(SheetTemplate template, Map<String, String> data) { if (data == null) return; for (Row row : template.sheet) { for (Cell c : row) { if (c.getCellTypeEnum() != CellType.STRING) continue; String str = c.getStringCellValue().trim(); if (str.startsWith("#") && data.containsKey(str.substring(1))) { c.setCellValue(data.get(str.substring(1))); } } } }
From source file:com.github.crab2died.handler.SheetTemplateHandler.java
License:Open Source License
/** * ????????// w w w . j av a2 s . c o m * * @param styleKey ? */ public static void insertSerial(SheetTemplate template, String styleKey) { if (template.serialNumberColumnIndex < 0) return; template.serialNumber++; Cell c = template.currentRow.createCell(template.serialNumberColumnIndex); setCellStyle(template, c, styleKey); c.setCellValue(template.serialNumber); }