List of usage examples for org.apache.poi.ss.usermodel Sheet getWorkbook
Workbook getWorkbook();
From source file:org.tiefaces.components.websheet.utility.CommandUtility.java
License:MIT License
/** * Insert each template./*from ww w .j av a 2s . c o m*/ * * @param sourceConfigRange * the source config range * @param configBuildRef * the config build ref * @param index * the index * @param insertPosition * the insert position * @param unitRowsMapping * the unit rows mapping */ public static void insertEachTemplate(final ConfigRange sourceConfigRange, final ConfigBuildRef configBuildRef, final int index, final int insertPosition, final RowsMapping unitRowsMapping) { int srcStartRow = sourceConfigRange.getFirstRowAddr().getRow(); int srcEndRow = sourceConfigRange.getLastRowPlusAddr().getRow() - 1; Sheet sheet = configBuildRef.getSheet(); Workbook wb = sheet.getWorkbook(); // excel sheet name has limit 31 chars String copyName = TieConstants.COPY_SHEET_PREFIX + sheet.getSheetName(); if (copyName.length() > TieConstants.EXCEL_SHEET_NAME_LIMIT) { copyName = copyName.substring(0, TieConstants.EXCEL_SHEET_NAME_LIMIT); } Sheet srcSheet = wb.getSheet(copyName); if (index > 0) { CellUtility.copyRows(srcSheet, sheet, srcStartRow, srcEndRow, insertPosition, false, true); } for (int rowIndex = srcStartRow; rowIndex <= srcEndRow; rowIndex++) { if (configBuildRef.getWatchList().contains(rowIndex) && (ConfigurationUtility.isStaticRow(sourceConfigRange, rowIndex))) { unitRowsMapping.addRow(rowIndex, sheet.getRow(insertPosition + rowIndex - srcStartRow)); } } }
From source file:org.tiefaces.components.websheet.utility.ConfigurationUtility.java
License:MIT License
/** * Builds the cell formula for shifted rows. * * @param sheet/*from www .jav a 2s . co m*/ * the sheet * @param wbWrapper * the wb wrapper * @param shiftFormulaRef * the shift formula ref * @param cell * the cell * @param originFormula * the origin formula */ public static void buildCellFormulaForShiftedRows(final Sheet sheet, final XSSFEvaluationWorkbook wbWrapper, final ShiftFormulaRef shiftFormulaRef, final Cell cell, final String originFormula) { // only shift when there's watchlist exist. if ((shiftFormulaRef.getWatchList() != null) && (!shiftFormulaRef.getWatchList().isEmpty())) { Ptg[] ptgs = FormulaParser.parse(originFormula, wbWrapper, FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet)); Ptg[] convertedFormulaPtg = ShiftFormulaUtility.convertSharedFormulas(ptgs, shiftFormulaRef); if (shiftFormulaRef.getFormulaChanged() > 0) { // only change formula when indicator is true cell.setCellFormula(FormulaRenderer.toFormulaString(wbWrapper, convertedFormulaPtg)); } } }
From source file:org.wicketstuff.poi.excel.XlsStreamTest.java
License:Apache License
public void testXlsStream() { Sheet sheet = new HSSFWorkbook().createSheet(); tester.startResource(new ResourceStreamResource(new XlsStream(sheet.getWorkbook()))); assertTrue(tester.getLastResponse().getContentType().contains("excel")); }
From source file:packtest.ConditionalFormats.java
License:Apache License
/** * DataBars / Data-Bars allow you to have bars shown vary * based on the values, from full to empty *//*w ww . j a va2 s. co m*/ static void dataBars(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Data Bars"); Row r = sheet.createRow(1); r.createCell(1).setCellValue("Green Positive"); r.createCell(2).setCellValue("Blue Mix"); r.createCell(3).setCellValue("Red Negative"); r = sheet.createRow(2); r.createCell(1).setCellValue(0); r.createCell(2).setCellValue(0); r.createCell(3).setCellValue(0); r = sheet.createRow(3); r.createCell(1).setCellValue(5); r.createCell(2).setCellValue(-5); r.createCell(3).setCellValue(-5); r = sheet.createRow(4); r.createCell(1).setCellValue(10); r.createCell(2).setCellValue(10); r.createCell(3).setCellValue(-10); r = sheet.createRow(5); r.createCell(1).setCellValue(5); r.createCell(2).setCellValue(5); r.createCell(3).setCellValue(-5); r = sheet.createRow(6); r.createCell(1).setCellValue(20); r.createCell(2).setCellValue(-10); r.createCell(3).setCellValue(-20); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 5000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 5000); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); ExtendedColor color = sheet.getWorkbook().getCreationHelper().createExtendedColor(); color.setARGBHex("FF63BE7B"); CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:B7") }; ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(color); DataBarFormatting db1 = rule1.getDataBarFormatting(); db1.getMinThreshold().setRangeType(RangeType.MIN); db1.getMaxThreshold().setRangeType(RangeType.MAX); sheetCF.addConditionalFormatting(regions, rule1); color = sheet.getWorkbook().getCreationHelper().createExtendedColor(); color.setARGBHex("FF5A8AC6"); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("C2:C7") }; ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(color); DataBarFormatting db2 = rule2.getDataBarFormatting(); db2.getMinThreshold().setRangeType(RangeType.MIN); db2.getMaxThreshold().setRangeType(RangeType.MAX); sheetCF.addConditionalFormatting(regions, rule2); color = sheet.getWorkbook().getCreationHelper().createExtendedColor(); color.setARGBHex("FFF8696B"); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("D2:D7") }; ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule(color); DataBarFormatting db3 = rule3.getDataBarFormatting(); db3.getMinThreshold().setRangeType(RangeType.MIN); db3.getMaxThreshold().setRangeType(RangeType.MAX); sheetCF.addConditionalFormatting(regions, rule3); }
From source file:pl.softech.knf.ofe.opf.members.xls.export.XlsMembersWritter.java
License:Apache License
private void buildHeader(final List<Date> dates, final Sheet sheet, final int rowIdx, final int colIdx) { final Workbook wb = sheet.getWorkbook(); final CreationHelper createHelper = wb.getCreationHelper(); final CellStyle dateCellStyle = wb.createCellStyle(); dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm-yyyy")); Row row = sheet.createRow(rowIdx);/*w ww .j a v a2s. c om*/ Cell cell = row.createCell(colIdx); cell.setCellValue("Open Pension Fund"); final CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setFont(createHeaderFont(wb, (short) 12)); cell.setCellStyle(cellStyle); cell = row.createCell(colIdx + 1); cell.setCellValue("Number of members"); cell.setCellStyle(cellStyle); row = sheet.createRow(rowIdx + 1); sheet.addMergedRegion(new CellRangeAddress(// merge Open Pension Fund rowIdx, // first row (0-based) rowIdx + 1, // last row (0-based) colIdx, // first column (0-based) colIdx // last column (0-based) )); sheet.addMergedRegion(new CellRangeAddress(// merge Number of members rowIdx, // first row (0-based) rowIdx, // last row (0-based) colIdx + 1, // first column (0-based) colIdx + dates.size() // last column (0-based) )); int colIt = colIdx + 1; for (final Date date : dates) { cell = row.createCell(colIt++); cell.setCellValue(date); cell.setCellStyle(dateCellStyle); } }
From source file:se.sll.invoicedata.price.GeneratePriceList.java
License:Open Source License
private List<String> getServicePrice(Sheet sheet, int startRow, int serviceType) { FormulaEvaluator formulaEval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); List<String> priceList = new ArrayList<String>(); for (int i = startRow; i < sheet.getPhysicalNumberOfRows(); i++) { Cell cell = sheet.getRow(i).getCell(serviceType); if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: priceList.add(DECIMAL_FORMAT.format(cell.getNumericCellValue())); break; case Cell.CELL_TYPE_FORMULA: double d = formulaEval.evaluate(cell).getNumberValue(); priceList.add(DECIMAL_FORMAT.format(d)); break; case Cell.CELL_TYPE_BLANK: priceList.add(DECIMAL_FORMAT.format(0)); break; default: StringBuffer errorMsg = new StringBuffer("This type of cell is not handled by the program!"); errorMsg.append(" cell type:").append(cell.getCellType()); errorMsg.append(" cell row:").append(cell.getRowIndex()); errorMsg.append(" cell column:").append(cell.getColumnIndex()); errorMsg.append(" cell value:").append(cell.getStringCellValue()); throw new IllegalStateException(errorMsg.toString()); }/*from w ww. j a v a 2 s . c o m*/ } else { priceList.add(DECIMAL_FORMAT.format(0)); } } return priceList; }
From source file:summary.GenotypeSummary.java
License:LGPL
public void writeToWorkbook(Workbook wb) { Sheet sheet = getSheet(wb); Row header = sheet.createRow(0);// w ww . j av a 2s . co m header.createCell(0).setCellValue("Metabolizer Group based on Genotype Only"); header.createCell(1).setCellValue("Weak"); header.createCell(2).setCellValue("Potent"); header.createCell(3).setCellValue("Count"); int rowNum = 1; for (String key : countMap.keySet()) { String[] fields = key.split("\\|"); Row data = sheet.createRow(rowNum); data.createCell(0).setCellValue(fields[0]); data.createCell(1).setCellValue(fields[1]); data.createCell(2).setCellValue(fields[2]); data.createCell(3).setCellValue(countMap.get(key)); rowNum++; } // Tumor source table Row row = sheet.createRow(++rowNum); row.createCell(0).setCellValue("*4 Status by Sample Source"); row = sheet.createRow(++rowNum); row.createCell(0).setCellValue("Source"); row.createCell(1).setCellValue("Count"); row.createCell(2).setCellValue("*4 Homozygous"); row.createCell(3).setCellValue("*4 Heterozygous"); row.createCell(4).setCellValue("Non-*4"); for (Subject.SampleSource source : Subject.SampleSource.values()) { row = sheet.createRow(++rowNum); row.createCell(0).setCellValue(source.toString()); row.createCell(1).setCellValue(sourceMap.get(source)[fourTotal]); row.createCell(2).setCellValue(sourceMap.get(source)[fourHomo]); row.createCell(3).setCellValue(sourceMap.get(source)[fourHeto]); row.createCell(4).setCellValue(sourceMap.get(source)[fourNon]); } rowNum++; row = sheet.createRow(++rowNum); row.createCell(0).setCellValue("Sample Source by Site"); row = sheet.createRow(++rowNum); row.createCell(0).setCellValue("Site"); int colMarker = 0; for (Subject.SampleSource source : Subject.SampleSource.values()) { row.createCell(colMarker * 2 + 1).setCellValue(source.name() + " N"); row.createCell(colMarker * 2 + 2).setCellValue(source.name() + " %"); colMarker++; } int[] totals = new int[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 }; CellStyle pctStyle = sheet.getWorkbook().createCellStyle(); DataFormat format = sheet.getWorkbook().createDataFormat(); pctStyle.setDataFormat(format.getFormat("0.0%")); for (Integer i : tumorFreqMap.keySet()) { row = sheet.createRow(++rowNum); Integer siteTotal = tumorFreqMap.get(i)[Subject.SampleSource.TUMOR_FFP.ordinal()] + tumorFreqMap.get(i)[Subject.SampleSource.TUMOR_FROZEN.ordinal()] + tumorFreqMap.get(i)[Subject.SampleSource.BLOOD.ordinal()] + tumorFreqMap.get(i)[Subject.SampleSource.BUCCAL.ordinal()] + tumorFreqMap.get(i)[Subject.SampleSource.NORMAL_PARAFFIN.ordinal()] + tumorFreqMap.get(i)[Subject.SampleSource.UNKNOWN.ordinal()]; Cell cell; row.createCell(0).setCellValue(i + 1); colMarker = 0; for (Subject.SampleSource source : Subject.SampleSource.values()) { Integer total = tumorFreqMap.get(i)[source.ordinal()]; Float pct = (float) tumorFreqMap.get(i)[source.ordinal()] / (float) siteTotal; row.createCell(colMarker * 2 + 1).setCellValue(total); cell = row.createCell(colMarker * 2 + 2); cell.setCellValue(pct); cell.setCellStyle(pctStyle); totals[source.ordinal()] += total; colMarker++; } } row = sheet.createRow(++rowNum); int projectTotal = totals[Subject.SampleSource.TUMOR_FFP.ordinal()] + totals[Subject.SampleSource.TUMOR_FROZEN.ordinal()] + totals[Subject.SampleSource.NORMAL_PARAFFIN.ordinal()] + totals[Subject.SampleSource.BLOOD.ordinal()] + totals[Subject.SampleSource.BUCCAL.ordinal()] + totals[Subject.SampleSource.UNKNOWN.ordinal()]; colMarker = 0; for (Subject.SampleSource source : Subject.SampleSource.values()) { row.createCell(colMarker * 2 + 1).setCellValue(totals[source.ordinal()]); Cell cell = row.createCell(colMarker * 2 + 2); cell.setCellValue((float) totals[source.ordinal()] / (float) projectTotal); cell.setCellStyle(pctStyle); colMarker++; } }
From source file:uk.ac.liverpool.spreadsheet.ExcelFeatureAnalysis.java
License:Apache License
private static void analyseSheet(Sheet ss, Element s, Namespace n, ExcelFeatureAnalysis efa) { // generic part boolean costumFormatting = false; boolean formulae = false; boolean UDF = false; boolean hasComments = false; Set<String> udfs = new HashSet<String>(); FormulaEvaluator evaluator = ss.getWorkbook().getCreationHelper().createFormulaEvaluator(); s.setAttribute("name", ss.getSheetName()); s.setAttribute("firstRow", "" + ss.getFirstRowNum()); s.setAttribute("lastRow", "" + ss.getLastRowNum()); try {/*from w w w . j a va2s. c o m*/ s.setAttribute("forceFormulaRecalc", "" + ss.getForceFormulaRecalculation()); } catch (Throwable x) { //x.printStackTrace(); } // shapes in detail? Footer footer = ss.getFooter(); if (footer != null) { s.setAttribute("footer", "true"); } Header header = ss.getHeader(); if (header != null) { s.setAttribute("header", "true"); } PaneInformation paneInformation = ss.getPaneInformation(); if (paneInformation != null) { s.setAttribute("panels", "true"); } HSSFSheet hs = null; XSSFSheet xs = null; if (ss instanceof HSSFSheet) { hs = (HSSFSheet) ss; try { if (hs.getDrawingPatriarch() != null) { if (hs.getDrawingPatriarch().containsChart()) s.addContent(new Element("charts", sn)); if (hs.getDrawingPatriarch().countOfAllChildren() > 0) s.addContent(new Element("shapes", sn)); } } catch (Exception x) { x.printStackTrace(); } if (hs.getSheetConditionalFormatting().getNumConditionalFormattings() > 0) { s.setAttribute("conditionalFormatting", "true"); } } if (ss instanceof XSSFSheet) { xs = (XSSFSheet) ss; } Iterator<Row> rows = ss.rowIterator(); int firstColumn = (rows.hasNext() ? Integer.MAX_VALUE : 0); int endColumn = 0; while (rows.hasNext()) { Row row = rows.next(); short firstCell = row.getFirstCellNum(); if (firstCell >= 0) { firstColumn = Math.min(firstColumn, firstCell); endColumn = Math.max(endColumn, row.getLastCellNum()); } } s.setAttribute("firstColumn", "" + firstColumn); s.setAttribute("lastColumn", "" + endColumn); rows = ss.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) if (cell != null) { try { if (!cell.getCellStyle().getDataFormatString().equals("GENERAL")) costumFormatting = true; } catch (Throwable t) { } if (cell.getCellComment() != null) hasComments = true; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: // System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: // if (DateUtil.isCellDateFormatted(cell)) { // // System.out.println(cell.getDateCellValue()); // } else { // // System.out.println(cell.getNumericCellValue()); // } break; case Cell.CELL_TYPE_BOOLEAN: // System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: // System.out.println(cell.getCellFormula()); formulae = true; if (!UDF) try { evaluator.evaluate(cell); } catch (Exception x) { if (x instanceof NotImplementedException) { Throwable e = x; //e.printStackTrace(); while (e != null) { for (StackTraceElement c : e.getStackTrace()) { if (c.getClassName().contains("UserDefinedFunction")) { UDF = true; System.out.println("UDF " + e.getMessage()); udfs.add(e.getMessage()); } } e = e.getCause(); } } } break; default: } } } if (costumFormatting) { Element cf = new Element("customisedFormatting", sn); s.addContent(cf); } if (formulae) { Element cf = new Element("formulae", sn); s.addContent(cf); } if (UDF) { Element cf = new Element("userDefinedFunctions", sn); for (String sss : udfs) cf.addContent(new Element("userDefinedFunction", sn).setAttribute("functionName", sss)); s.addContent(cf); } if (hasComments) { Element cf = new Element("cellComments", sn); s.addContent(cf); } }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.FontsReportTest.java
License:Open Source License
private void assertFontCell(Sheet sheet, int row, int col, String contents, String fontName, int fontHeight) { Cell cell = sheet.getRow(row).getCell(col); CellStyle style = cell.getCellStyle(); assertEquals(contents, cell.getStringCellValue()); assertEquals(fontName, sheet.getWorkbook().getFontAt(style.getFontIndex()).getFontName().replace("\"", "")); assertEquals(fontHeight, sheet.getWorkbook().getFontAt(style.getFontIndex()).getFontHeightInPoints()); }