Workbook wb = new HSSFWorkbook(); //Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short)0); // Create a cell and put a value in it. Cell cell = row.createCell(0); cell.setCellValue(1); // Or do it on one line. row.createCell(1).setCellValue(1.2); row.createCell(2).setCellValue( createHelper.createRichTextString("This is a string")); row.createCell(3).setCellValue(true); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no changes * are made. * This is a helpful wrapper around looping over all * cells, and calling evaluateFormulaCell on each one. */ public static void evaluateAllFormulaCells(Workbook wb) { FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluateAllFormulaCells(wb, evaluator); } protected static void evaluateAllFormulaCells(Workbook wb, FormulaEvaluator evaluator) {
import java.io.*; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import au.com.bytecode.opencsv.CSVReader; class Test { public static void main(String[] args) throws IOException { Workbook wb = new HSSFWorkbook(); CreationHelper helper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); CSVReader reader = new CSVReader(new FileReader("data.csv")); String[] line; int r = 0; while ((line = reader.readNext()) != null) { Row row = sheet.createRow((short) r++); for (int i = 0; i < line.length; i++) row.createCell(i) .setCellValue(helper.createRichTextString(line[i])); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); } }
Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls") Sheet sheet = wb.getSheetAt(0); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
/** * Looks for text in the cell that should be unicode, like α and provides the * unicode version of it. * *@param cell The cell to check for unicode values *@return translated to unicode */ public static Cell translateUnicodeValues(Cell cell) { String s = cell.getRichStringCellValue().getString(); boolean foundUnicode = false; String lowerCaseStr = s.toLowerCase(Locale.ROOT); for (UnicodeMapping entry : unicodeMappings) { String key = entry.entityName; if (lowerCaseStr.contains(key)) { s = s.replaceAll(key, entry.resolvedValue); foundUnicode = true; } } if (foundUnicode) { cell.setCellValue(cell.getRow().getSheet().getWorkbook().getCreationHelper() .createRichTextString(s)); } return cell; }
protected AbstractCellRangeDataSource(Sheet sheet, CellRangeAddress cellRangeAddress) { this.sheet = sheet; // Make copy since CellRangeAddress is mutable. this.cellRangeAddress = cellRangeAddress.copy(); this.numOfCells = this.cellRangeAddress.getNumberOfCells(); this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); }
/** * Creates a cell, gives it a value, and applies a style if provided * * @param row the row to create the cell in * @param column the column index to create the cell in * @param value The value of the cell * @param style If the style is not null, then set * @return A new Cell */ public static Cell createCell(Row row, int column, String value, CellStyle style) { Cell cell = getCell(row, column); cell.setCellValue(cell.getRow().getSheet().getWorkbook().getCreationHelper() .createRichTextString(value)); if (style != null) { cell.setCellStyle(style); } return cell; }
private Comment createCellComment( String author, String comment ) { // comments only supported for XLSX if ( data.sheet instanceof XSSFSheet ) { CreationHelper factory = data.wb.getCreationHelper(); Drawing drawing = data.sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment cmt = drawing.createCellComment( anchor ); RichTextString str = factory.createRichTextString( comment ); cmt.setString( str ); cmt.setAuthor( author ); return cmt; } return null; }
void recalculateAllWorkbookFormulas() { if ( data.wb instanceof XSSFWorkbook ) { // XLSX needs full reevaluation FormulaEvaluator evaluator = data.wb.getCreationHelper().createFormulaEvaluator(); for ( int sheetNum = 0; sheetNum < data.wb.getNumberOfSheets(); sheetNum++ ) { Sheet sheet = data.wb.getSheetAt( sheetNum ); for ( Row r : sheet ) { for ( Cell c : r ) { if ( c.getCellType() == Cell.CELL_TYPE_FORMULA ) { evaluator.evaluateFormulaCell( c ); } } } } } else if ( data.wb instanceof HSSFWorkbook ) { // XLS supports a "dirty" flag to have excel recalculate everything when a sheet is opened for ( int sheetNum = 0; sheetNum < data.wb.getNumberOfSheets(); sheetNum++ ) { HSSFSheet sheet = ( (HSSFWorkbook) data.wb ).getSheetAt( sheetNum ); sheet.setForceFormulaRecalculation( true ); } } else { String forceRecalc = getVariable( STREAMER_FORCE_RECALC_PROP_NAME, "N" ); if ( "Y".equals( forceRecalc ) ) { data.wb.setForceFormulaRecalculation( true ); } } }
String link = data.inputRowMeta.getValueMeta( data.linkfieldnrs[ fieldNr ] ).getString( row[ data.linkfieldnrs[ fieldNr ] ] ); if ( !Utils.isEmpty( link ) ) { CreationHelper ch = data.wb.getCreationHelper();
public static void updateCellValue(Sheet sheet, int rowNum, int colNum, String cellValue) { org.apache.poi.ss.usermodel.Row hssfRow = sheet.getRow(rowNum); org.apache.poi.ss.usermodel.Cell hssfCell = hssfRow.getCell(colNum); hssfCell.setCellValue(hssfCell.getSheet().getWorkbook().getCreationHelper().createRichTextString(cellValue)); }
/** * Returns a formula evaluator for the current workbook. * This is for convinience. * @return the formula evaluator */ public FormulaEvaluator getFormulaEvaluator() { if (formulaEvaluator == null) { formulaEvaluator = getWorkbook().getCreationHelper().createFormulaEvaluator(); } return formulaEvaluator; }
File file = new File("/somepath/test.xls"); Workbook wb = WorkbookFactory.create(file); Sheet sheet = wb.getSheetAt(0); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // suppose your formula is in B3 CellReference cellReference = new CellReference("B3"); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); evaluator.evaluateInCell(cell);
private void openWorkbook(InputStream is) throws InvalidFormatException, IOException { this.workbook = WorkbookFactory.create(is); this.evaluator = this.workbook.getCreationHelper() .createFormulaEvaluator(); this.formatter = new DataFormatter(true); }
/** * Gets the index identifier for the date format * * @return */ public short getDateCellFormat() { if (_dateCellFormat == null) { Workbook workbook = getWorkbook(true); _dateCellFormat = workbook.getCreationHelper().createDataFormat().getFormat("m/d/yy h:mm"); } return _dateCellFormat; }
static void clearCell(Cell cell) { if (cell != null) { cell.setCellValue(cell.getSheet().getWorkbook().getCreationHelper().createRichTextString("")); cell.setCellType(Cell.CELL_TYPE_BLANK); } }
private static void replacePropertyInCell(Cell cell, String oldProperty, String newProperty) { if (cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING) { String cellValue = cell.getRichStringCellValue().getString(); String newValue = cellValue.replaceAll(oldProperty, newProperty); cell.setCellValue(cell.getSheet().getWorkbook().getCreationHelper().createRichTextString(newValue)); } }
private void setCellValue(Cell cell, String value) { if (value == null || value.length() == 0) { cell.getPoiCell().setCellType( org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK ); } else { cell.getPoiCell().setCellValue(cell.getPoiCell().getSheet().getWorkbook().getCreationHelper().createRichTextString(value)); } }
private static CellValue getEvaluatedCellValue(Cell cell){ Workbook book = cell.getSheet().getWorkbook(); CreationHelper helper = book.getCreationHelper(); FormulaEvaluator evaluator = helper.createFormulaEvaluator(); CellValue value = evaluator.evaluate(cell); return value; }
void updateCellFormula(TableData.TableCellValue tableCellValue) { tableCellValue.getTableRow().getTable().getSheet().getWorkbook() .getCreationHelper().createFormulaEvaluator() .evaluateFormulaCell(tableCellValue.getCell()); }