List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getFontAt
@Override
public HSSFFont getFontAt(int idx)
From source file:org.orbeon.oxf.processor.serializer.legacy.XLSSerializer.java
License:Open Source License
protected void readInput(final PipelineContext pipelineContext, ProcessorInput input, Config config, OutputStream outputStream) { try {// w ww . j ava 2 s . co m Document dataDocument = readInputAsDOM4J(pipelineContext, INPUT_DATA); final DocumentWrapper wrapper = new DocumentWrapper(dataDocument, null, XPathCache.getGlobalConfiguration()); Document configDocument = readInputAsDOM4J(pipelineContext, INPUT_CONFIG); // Read template sheet String templateName = configDocument.getRootElement().attributeValue("template"); //String fileName = configDocument.getRootElement().attributeValue("filename"); InputStream templateInputStream = URLFactory.createURL(templateName).openStream(); final HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(templateInputStream)); final HSSFDataFormat dataFormat = workbook.createDataFormat(); templateInputStream.close(); int sheetIndex = 0; PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper, "/workbook/sheet", getLocationData()); List<Object> nodes = expr.evaluateToJavaReturnToPool(); for (Iterator i = nodes.iterator(); i.hasNext();) { final Element sheetElement = (Element) i.next(); HSSFSheet sheet = workbook.cloneSheet(0); workbook.setSheetName(sheetIndex + 1, sheetElement.attributeValue("name")); // Duplicate rows if we find a "repeat-row" in the config for (Iterator j = configDocument.selectNodes("/config/repeat-row").iterator(); j.hasNext();) { // Get info about row to repeat Element repeatRowElement = (Element) j.next(); final int rowNum = Integer.parseInt(repeatRowElement.attributeValue("row-num")); final String forEach = repeatRowElement.attributeValue("for-each"); HSSFRow templateRow = sheet.getRow(rowNum); int repeatCount = ((Double) sheetElement.selectObject("count(" + forEach + ")")).intValue(); // Move existing rows lower int lastRowNum = sheet.getLastRowNum(); for (int k = lastRowNum; k > rowNum; k--) { HSSFRow sourceRow = sheet.getRow(k); HSSFRow newRow = sheet.createRow(k + repeatCount - 1); XLSUtils.copyRow(workbook, newRow, sourceRow); } // Create rows, copying the template row for (int k = rowNum + 1; k < rowNum + repeatCount; k++) { HSSFRow newRow = sheet.createRow(k); XLSUtils.copyRow(workbook, newRow, templateRow); } // Modify the XPath expression on each row for (int k = rowNum; k < rowNum + repeatCount; k++) { HSSFRow newRow = sheet.getRow(k); for (short m = 0; m <= newRow.getLastCellNum(); m++) { HSSFCell cell = newRow.getCell(m); if (cell != null) { String currentFormat = dataFormat.getFormat(cell.getCellStyle().getDataFormat()); final Matcher matcher = FORMAT_XPATH.matcher(currentFormat); if (matcher.find()) { String newFormat = matcher.group(1) + "\"" + forEach + "[" + (k - rowNum + 1) + "]/" + matcher.group(2) + "\""; cell.getCellStyle().setDataFormat(dataFormat.getFormat(newFormat)); } } } } } // Set values in cells with an XPath expression XLSUtils.walk(dataFormat, sheet, new XLSUtils.Handler() { public void cell(HSSFCell cell, String sourceXPath, String targetXPath) { if (sourceXPath.charAt(0) == '/') sourceXPath = sourceXPath.substring(1); // Set cell value PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper.wrap(sheetElement), "string(" + sourceXPath + ")", getLocationData()); String newValue = (String) expr.evaluateSingleToJavaReturnToPoolOrNull(); if (newValue == null) { throw new OXFException("Nothing matches the XPath expression '" + sourceXPath + "' in the input document"); } try { cell.setCellValue(Double.parseDouble(newValue)); } catch (NumberFormatException e) { cell.setCellValue(newValue); } // Set cell format Object element = sheetElement.selectObject(sourceXPath); if (element instanceof Element) { // NOTE: We might want to support other properties here String bold = ((Element) element).attributeValue("bold"); if (bold != null) { HSSFFont originalFont = workbook.getFontAt(cell.getCellStyle().getFontIndex()); HSSFFont newFont = workbook.createFont(); XLSUtils.copyFont(newFont, originalFont); if ("true".equals(bold)) newFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cell.getCellStyle().setFont(newFont); } } } }); sheetIndex++; } workbook.removeSheetAt(0); // Write out the workbook workbook.write(outputStream); } catch (IOException e) { throw new OXFException(e); } }
From source file:org.orbeon.oxf.util.XLSUtils.java
License:Open Source License
public static void copyCell(HSSFWorkbook workbook, HSSFCell destination, HSSFCell source) { // Copy cell content destination.setCellType(source.getCellType()); switch (source.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: destination.setCellValue(source.getBooleanCellValue()); break;/*from w w w . j a v a 2 s .com*/ case HSSFCell.CELL_TYPE_FORMULA: case HSSFCell.CELL_TYPE_STRING: destination.setCellValue(source.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: destination.setCellValue(source.getNumericCellValue()); break; } // Copy cell style HSSFCellStyle sourceCellStyle = source.getCellStyle(); HSSFCellStyle destinationCellStyle = workbook.createCellStyle(); destinationCellStyle.setAlignment(sourceCellStyle.getAlignment()); destinationCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom()); destinationCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft()); destinationCellStyle.setBorderRight(sourceCellStyle.getBorderRight()); destinationCellStyle.setBorderTop(sourceCellStyle.getBorderTop()); destinationCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor()); destinationCellStyle.setDataFormat(sourceCellStyle.getDataFormat()); destinationCellStyle.setFillBackgroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillPattern(sourceCellStyle.getFillPattern()); destinationCellStyle.setFont(workbook.getFontAt(sourceCellStyle.getFontIndex())); destinationCellStyle.setHidden(sourceCellStyle.getHidden()); destinationCellStyle.setIndention(sourceCellStyle.getIndention()); destinationCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor()); destinationCellStyle.setLocked(sourceCellStyle.getLocked()); destinationCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor()); destinationCellStyle.setRotation(sourceCellStyle.getRotation()); destinationCellStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor()); destinationCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment()); destinationCellStyle.setWrapText(sourceCellStyle.getWrapText()); destination.setCellStyle(destinationCellStyle); }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.NestedTables2ReportTest.java
License:Open Source License
@Test public void testRunReportXls() throws BirtException, IOException { InputStream inputStream = runAndRenderReport("NestedTables2.rptdesign", "xls"); assertNotNull(inputStream);/* ww w .j a va2 s . c o m*/ try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals("Nested Tables Test Report", workbook.getSheetAt(0).getSheetName()); Sheet sheet = workbook.getSheetAt(0); assertEquals(12, firstNullRow(sheet)); assertEquals(1, sheet.getRow(0).getCell(0).getNumericCellValue(), 0.0); assertEquals(1, sheet.getRow(0).getCell(1).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(1).getCell(0).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(1).getCell(1).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(2).getCell(0).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(2).getCell(1).getNumericCellValue(), 0.0); assertEquals(1, sheet.getRow(3).getCell(0).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(3).getCell(1).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(3).getCell(2).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(4).getCell(0).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(4).getCell(1).getNumericCellValue(), 0.0); assertEquals(4, sheet.getRow(5).getCell(0).getNumericCellValue(), 0.0); assertEquals(4, sheet.getRow(5).getCell(1).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(6).getCell(0).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(6).getCell(1).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(7).getCell(0).getNumericCellValue(), 0.0); assertEquals(4, sheet.getRow(7).getCell(1).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(7).getCell(2).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(8).getCell(0).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(8).getCell(1).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(9).getCell(0).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(9).getCell(1).getNumericCellValue(), 0.0); assertEquals(9, sheet.getRow(10).getCell(0).getNumericCellValue(), 0.0); assertEquals(9, sheet.getRow(10).getCell(1).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(11).getCell(0).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(11).getCell(1).getNumericCellValue(), 0.0); assertEquals(9, sheet.getRow(11).getCell(2).getNumericCellValue(), 0.0); short bgColour = ((HSSFCell) sheet.getRow(0).getCell(0)).getCellStyle().getFillBackgroundColor(); assertEquals("0:0:0", workbook.getCustomPalette().getColor(bgColour).getHexString()); short baseColour = workbook .getFontAt(((HSSFCell) sheet.getRow(0).getCell(0)).getCellStyle().getFontIndex()).getColor(); assertEquals("FFFF:FFFF:FFFF", workbook.getCustomPalette().getColor(baseColour).getHexString()); } finally { inputStream.close(); } }