List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setCellFormula
@Override public final void setCellFormula(String formula) throws FormulaParseException, IllegalStateException
From source file:net.chaosserver.timelord.data.ExcelDataReaderWriter.java
License:Open Source License
/** * Adds all of the tasks to sheets that have already been created inside * the workbook and adds any notes associated with the tasks to the * sheetToNotes map./*from w ww. j a v a2 s.com*/ * * @param workbook the workbook to create sheets on * @param timelordData the timelord data used for the sheets. * @param sheetToNotes the map of sheets to the notes associated with it * @param styleMap the map of styles * * @return the total rows added to Excel after adding all the tasks */ protected int addAllTasks(HSSFWorkbook workbook, TimelordData timelordData, Map<String, List<String>> sheetToNotes, Map<String, HSSFCellStyle> styleMap) { // Start on row number two. The first row contains the header data. int rowNum = 2; List<TimelordTask> taskCollection = timelordData.getTaskCollection(); Iterator<TimelordTask> taskIterator = taskCollection.iterator(); while (taskIterator.hasNext()) { TimelordTask timelordTask = (TimelordTask) taskIterator.next(); if (timelordTask.isExportable()) { if (logger.isDebugEnabled()) { logger.debug("Processing exportable task named [" + timelordTask.getTaskName() + "]"); } String taskName = timelordTask.getTaskName(); List<TimelordTaskDay> taskDayList = timelordTask.getTaskDayList(); Iterator<TimelordTaskDay> taskDayIterator = taskDayList.iterator(); while (taskDayIterator.hasNext()) { TimelordTaskDay timelordTaskDay = (TimelordTaskDay) taskDayIterator.next(); Date timelordDate = timelordTaskDay.getDate(); double hours = timelordTaskDay.getHours(); if (logger.isDebugEnabled()) { logger.debug("Processing task named [" + timelordTask.getTaskName() + "] for date [" + timelordDate + "] with hours [" + hours + "]"); } if (hours > 0) { Date weekStartDate = convertToWeekStart(timelordDate); String sheetName = sheetNameFormat.format(weekStartDate); HSSFSheet sheet = workbook.getSheet(sheetName); if (sheet == null) { throw new NullPointerException( "Failed to find " + "sheet with name [" + sheetName + "]"); } List<String> noteList = sheetToNotes.get(sheetName); HSSFRow row = sheet.getRow(rowNum); if (row == null) { row = sheet.createRow(rowNum); // First create the left column "header" with the // name of the task on column 0. HSSFCell cell = row.createCell((short) 0); cell.setCellStyle((HSSFCellStyle) styleMap.get("taskNameStyle")); cell.setCellValue(taskName); // Over in the far right column create the sum // column cell = row.createCell(MAX_COLUMN); cell.setCellStyle((HSSFCellStyle) styleMap.get("totalColumnStyle")); cell.setCellFormula("SUM(B" + (rowNum + 1) + ":H" + (rowNum + 1) + ")"); } // Process the task day and add the hours into the // given row. addTaskDay(row, taskName, timelordTaskDay, noteList, styleMap); } } rowNum++; } else { if (logger.isDebugEnabled()) { logger.debug("Skipping non-exportable task named [" + timelordTask.getTaskName() + "]"); } } } return rowNum; }
From source file:net.chaosserver.timelord.data.ExcelDataReaderWriter.java
License:Open Source License
/** * Creates the footer rows for a given sheet. * * @param sheet the sheet to create the rows for. * @param rowNum the rownum that is the footer row * @param styleMap the map of styles// w ww . j a v a 2 s .c om */ protected void createFooterRows(HSSFSheet sheet, int rowNum, Map<String, HSSFCellStyle> styleMap) { HSSFRow row = sheet.createRow(rowNum); HSSFCell cell = row.createCell((short) 0); cell.setCellStyle((HSSFCellStyle) styleMap.get("bottomLeftStyle")); cell.setCellValue("Total"); cell = row.createCell((short) 1); cell.setCellStyle((HSSFCellStyle) styleMap.get("bottomStyle")); cell.setCellFormula("SUM(B3:B" + rowNum + ")"); cell = row.createCell((short) 2); cell.setCellStyle((HSSFCellStyle) styleMap.get("bottomStyle")); cell.setCellFormula("SUM(C3:C" + rowNum + ")"); cell = row.createCell((short) 3); cell.setCellStyle((HSSFCellStyle) styleMap.get("bottomStyle")); cell.setCellFormula("SUM(D3:D" + rowNum + ")"); cell = row.createCell((short) 4); cell.setCellStyle((HSSFCellStyle) styleMap.get("bottomStyle")); cell.setCellFormula("SUM(E3:E" + rowNum + ")"); cell = row.createCell((short) 5); cell.setCellStyle((HSSFCellStyle) styleMap.get("bottomStyle")); cell.setCellFormula("SUM(F3:F" + rowNum + ")"); cell = row.createCell((short) 6); cell.setCellStyle((HSSFCellStyle) styleMap.get("bottomStyle")); cell.setCellFormula("SUM(G3:G" + rowNum + ")"); cell = row.createCell((short) 7); cell.setCellStyle((HSSFCellStyle) styleMap.get("bottomStyle")); cell.setCellFormula("SUM(H3:H" + rowNum + ")"); cell = row.createCell(MAX_COLUMN); cell.setCellStyle((HSSFCellStyle) styleMap.get("bottomRightStyle")); cell.setCellFormula("SUM(I3:I" + rowNum + ")"); }
From source file:net.sourceforge.fenixedu.presentationTier.Action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidates.java
License:Open Source License
private void addCellFormula(HSSFRow row, String value, int cellNumber) { HSSFCell cell = row.createCell(cellNumber); cell.setCellFormula(value); }
From source file:org.gbif.portal.io.ExcelResultsOutputter.java
License:Open Source License
@Override public void write(Map beanMap) throws IOException { for (int j = 0; j < selectedFieldNames.size(); j++) { String propertyName = selectedFieldNames.get(j); String propertyValue = getSelectedFieldValue(beanMap, propertyName); HSSFCell cell = getCell(currentRow, j); try {//from w ww .j a va 2s . c om short textWidth = defaultColumnWidth; if (propertyValue != null) setText(cell, propertyValue); if (propertyValue != null && propertyValue.startsWith("http")) { if (propertyValue != null && propertyValue.length() < urlMaxLength) { String linkFormula = "HYPERLINK(\"" + propertyValue + "\")"; cell.setCellFormula(linkFormula); cell.setCellStyle(hyperlinkStyle); } } if (propertyValue != null) { int textWidthInt = propertyValue.length() * charWidth; if (textWidthInt > 32768) { textWidth = Short.MAX_VALUE; } else { textWidth = (short) textWidthInt; } } //update column width for long columns short columnWidth = sheet.getColumnWidth((short) j); if (textWidth > columnWidth) { sheet.setColumnWidth((short) j, (short) textWidth); } } catch (Exception e) { logger.error(e.getMessage(), e); } } //move to next row in the sheet currentRow++; }
From source file:org.gbif.portal.web.view.ExcelView.java
License:Open Source License
/** * @see org.springframework.web.servlet.view.document.AbstractExcelView#buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse) *//*from w ww. j a va 2 s. c o m*/ @SuppressWarnings("unchecked") protected void buildExcelDocument(Map model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) { Locale locale = RequestContextUtils.getLocale(request); //create results sheet String sheetTitle = messageSource.getMessage(resultsSheetTitleI18nKey, null, locale); HSSFSheet resultsSheet = workbook.createSheet(sheetTitle); resultsSheet.setDefaultColumnWidth((short) (defaultColumnWidth)); //create a titles style HSSFCellStyle titlesStyle = workbook.createCellStyle(); titlesStyle.setFillPattern((short) HSSFCellStyle.SOLID_FOREGROUND); titlesStyle.setFillBackgroundColor(HSSFColor.DARK_GREEN.index); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.WHITE.index); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titlesStyle.setFont(font); //create a hyperlink style HSSFCellStyle hyperlinkStyle = workbook.createCellStyle(); HSSFFont hyperLinkFont = workbook.createFont(); hyperLinkFont.setColor(HSSFColor.BLUE.index); hyperlinkStyle.setFont(hyperLinkFont); //reused cell reference HSSFCell cell = null; //write results sheet List<Field> fields = (List<Field>) request.getAttribute("requestedFields"); List results = (List) request.getAttribute("results"); int currentRow = 0; //column headings for (int i = 0; i < fields.size(); i++) { cell = getCell(resultsSheet, currentRow, i); cell.setCellStyle(titlesStyle); String title = messageSource.getMessage(fields.get(i).getFieldI18nNameKey(), null, locale); setText(cell, title); short titleLength = (short) (title.length() * charWidth); short columnWidth = resultsSheet.getColumnWidth((short) i); //update column width for long columns if (columnWidth < titleLength) { resultsSheet.setColumnWidth((short) i, (short) (titleLength)); } } currentRow++; //results for (int i = 0; i < results.size(); i++) { Object result = results.get(i); for (int j = 0; j < fields.size(); j++) { Field field = fields.get(j); cell = getCell(resultsSheet, currentRow, j); try { short textWidth = defaultColumnWidth; String propertyValue = field.getRenderValue(request, messageSource, locale, result); if (propertyValue != null) setText(cell, propertyValue); if (field instanceof UrlField) { if (propertyValue != null && propertyValue.length() < urlMaxLength) { String linkFormula = "HYPERLINK(\"" + propertyValue + "\")"; cell.setCellFormula(linkFormula); cell.setCellStyle(hyperlinkStyle); } } if (propertyValue != null) { int textWidthInt = propertyValue.length() * charWidth; if (textWidthInt > 32768) { textWidth = Short.MAX_VALUE; } else { textWidth = (short) textWidthInt; } } //update column width for long columns short columnWidth = resultsSheet.getColumnWidth((short) j); if (textWidth > columnWidth) { resultsSheet.setColumnWidth((short) j, (short) textWidth); } } catch (Exception e) { logger.warn(e.getMessage(), e); } } currentRow++; } //set up details sheet HSSFSheet detailsSheet = workbook .createSheet(messageSource.getMessage(detailsSheetTitleI18nKey, null, locale)); detailsSheet.setColumnWidth((short) 0, (short) 6000); detailsSheet.setColumnWidth((short) 1, (short) Short.MAX_VALUE); ; List<FilterDTO> filters = (List) request.getAttribute("filters"); CriteriaDTO criteria = (CriteriaDTO) request.getAttribute("criteria"); String query = FilterUtils.getQueryDescription(filters, criteria, messageSource, locale); cell = getCell(detailsSheet, 0, 0); cell.setCellStyle(titlesStyle); setText(cell, messageSource.getMessage("occurrence.search.description", null, locale)); cell = getCell(detailsSheet, 0, 1); setText(cell, query); //add url for search cell = getCell(detailsSheet, 1, 0); cell.setCellStyle(titlesStyle); setText(cell, messageSource.getMessage("occurrence.search.url", null, locale)); cell = getCell(detailsSheet, 1, 1); cell.setCellStyle(hyperlinkStyle); String url = "http://" + request.getHeader("host") + request.getContextPath() + "/occurrences/search.htm?" + CriteriaUtil.getUrl(criteria); setText(cell, url); //there is a formula limit in Excel of 255 characters if (url != null && url.length() < urlMaxLength) { String link = "HYPERLINK(\"" + url + "\")"; cell.setCellFormula(link); } //add url for download page cell = getCell(detailsSheet, 2, 0); cell.setCellStyle(titlesStyle); setText(cell, messageSource.getMessage("occurrence.search.download.url", null, locale)); cell = getCell(detailsSheet, 2, 1); cell.setCellStyle(hyperlinkStyle); String downloadurl = "http://" + request.getHeader("host") + request.getContextPath() + "/occurrences/download.htm?" + CriteriaUtil.getUrl(criteria); setText(cell, downloadurl); if (downloadurl != null && downloadurl.length() < urlMaxLength) { String link = "HYPERLINK(\"" + downloadurl + "\")"; cell.setCellFormula(link); } //add date for this download cell = getCell(detailsSheet, 3, 0); cell.setCellStyle(titlesStyle); setText(cell, messageSource.getMessage("occurrence.search.download.date", null, locale)); cell = getCell(detailsSheet, 3, 1); SimpleDateFormat sdf = new SimpleDateFormat("dd MMM yyyy"); setText(cell, sdf.format(new Date(System.currentTimeMillis()))); }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row HSSFRow newRow = worksheet.getRow(destinationRowNum); HSSFRow 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 av a 2 s.com 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 HSSFCell oldCell = sourceRow.getCell(i); HSSFCell 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 HSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } }
From source file:org.kopsox.spreadsheet.data.excel.ExcelSheet.java
License:Open Source License
@Override public void setFormualaAt(int row, int column, String formula) { HSSFRow excelRow = sheet.getRow(row); if (excelRow == null) { excelRow = sheet.createRow(row); }/*w w w. j a va 2 s. c o m*/ HSSFCell excelCell = excelRow.getCell(column); if (excelCell == null) { excelCell = excelRow.createCell(column); } excelCell.setCellFormula(formula); excelCell.setCellType(Cell.CELL_TYPE_FORMULA); }
From source file:org.seasar.fisshplate.core.element.Formula.java
License:Apache License
void mergeImpl(FPContext context, HSSFCell out) throws FPMergeException { String value = super.getCellValue().toString(); Matcher matcher = patternFormula.matcher(value); matcher.find();//from w w w . j a v a 2 s.c o m String formula = matcher.group(1); if (isWritePicture(formula)) { out.setCellType(HSSFCell.CELL_TYPE_FORMULA); out.setCellFormula(formula); } }
From source file:poi.hssf.usermodel.examples.HyperlinkFormula.java
License:Apache License
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); HSSFRow row = sheet.createRow(0);/*from ww w .j a va 2 s . c o m*/ HSSFCell cell = row.createCell(0); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula( "HYPERLINK(\"http://127.0.0.1:8080/toto/truc/index.html?test=aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa\", \"test\")"); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); }
From source file:ro.nextreports.engine.exporter.util.XlsUtil.java
License:Apache License
/** * Copy a cell to another cell//from w ww .j av a2 s . c o m * * @param oldCell cell to be copied * @param newCell cell to be created * @param styleMap style map */ public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) { newCell.setCellStyle(oldCell.getCellStyle()); } else { int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); } newCell.setCellStyle(newCellStyle); } } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }