Example usage for org.apache.poi.hssf.usermodel HSSFCell getCellStyle

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. getCellStyle

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFCell getCellStyle.

Prototype

public HSSFCellStyle getCellStyle()

Source Link

Document

get the style for the cell.

Usage

From source file:org.jxstar.report.util.ReportXlsUtil.java

/**
 * ?PageSize?/*from  w w  w .  j  ava2  s.  c om*/
 * @param sheet -- ?
 * @param startRow -- ???PageSize
 * @param rows -- ?
 * @return
 */
public static HSSFSheet insertSheetRow(HSSFSheet sheet, int startRow, int rows) {
    if (sheet == null)
        return null;
    sheet.shiftRows(startRow, sheet.getLastRowNum(), rows, true, false);

    HSSFCell sourcell = null, descell = null;
    HSSFRow sourow = sheet.getRow(startRow - 1);

    for (int i = 0; i < rows; i++) {
        HSSFRow descrow = sheet.createRow(startRow + i);

        descrow.setHeight(sourow.getHeight());
        descrow.setHeightInPoints(sourow.getHeightInPoints());

        java.util.Iterator<Cell> iter = sourow.cellIterator();
        while (iter.hasNext()) {
            sourcell = (HSSFCell) iter.next();
            int column = sourcell.getColumnIndex();
            descell = descrow.createCell(column);

            descell.setCellType(sourcell.getCellType());
            descell.setCellStyle(sourcell.getCellStyle());
        }
    }
    //??
    insertSheetRegions(sheet, startRow, rows);

    return sheet;
}

From source file:org.jxstar.report.util.XlsToHtml.java

/**
 * ?xls?html?//from ww w.  j a v  a2  s. c  o  m
 * @param cell -- xls?
 * @return
 */
private String getCellStyle(HSSFCell cell) {
    HSSFCellStyle style = cell.getCellStyle();
    HSSFSheet sheet = cell.getSheet();
    String value = getCellValue(cell);

    //?
    StringBuilder sbStyle = new StringBuilder();

    //??
    sbStyle.append(getBorderStyle(style));

    //???
    sbStyle.append(getAlignStyle(style));

    //??
    HSSFFont font = style.getFont(sheet.getWorkbook());
    sbStyle.append(getFontStyle(font));

    //cell
    if (value == null || value.length() == 0) {
        sbStyle.append("color:red;");
    }

    return sbStyle.toString();
}

From source file:org.opensprout.osaf.util.ExcelUtils.java

License:Open Source License

/**
 * Copy sheet to sheet, from start row to end row.
 * @param from from Sheet/*ww  w. java 2 s  .  co m*/
 * @param to to Sheet
 * @param fromRowCnt start row number
 * @param toRowCnt length of copying rows
 */
@SuppressWarnings("unchecked")
public static void copySheet(HSSFSheet from, HSSFSheet to, int fromRowCnt, int toRowCnt) {
    HSSFRow fromRow = null;
    HSSFRow toRow = null;

    for (int i = fromRowCnt; i <= toRowCnt; i++) {
        fromRow = from.getRow(i);
        toRow = to.createRow(i);
        Iterator<HSSFCell> iterator = fromRow.cellIterator();
        short col = 0;
        while (iterator.hasNext()) {
            HSSFCell cell = iterator.next();
            addCell(toRow, col++, cell.getStringCellValue(), cell.getCellStyle());
        }
    }
}

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  w w . j  av a2  s .c  om
        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

private static void walk(boolean[][] merged, HSSFDataFormat dataFormat, HSSFRow row, Handler handler) {
    if (row != null) {
        for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
            HSSFCell cell = row.getCell((short) cellNum);
            if (cell != null && !merged[row.getRowNum()][cellNum]) {
                short dataFormatId = cell.getCellStyle().getDataFormat();
                if (dataFormatId > 0) {
                    String format = dataFormat.getFormat(dataFormatId);
                    final Matcher matcher = FORMAT_XPATH.matcher(format);
                    if (matcher.find()) {
                        // Found XPath expression
                        String xpath = matcher.group(1);
                        int separtorPosition = xpath.indexOf('|');
                        String sourceXPath = separtorPosition == -1 ? xpath
                                : xpath.substring(0, separtorPosition);
                        String targetXPath = separtorPosition == -1 ? null
                                : xpath.substring(separtorPosition + 1);
                        handler.cell(cell, sourceXPath, targetXPath);
                    }/*from   www.  j av a  2 s .c o  m*/
                }
            }
        }
    }
}

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 ww w.jav a 2  s.  c  o  m
    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:org.pentaho.reporting.engine.classic.core.bugs.Prd3899IT.java

License:Open Source License

public void testBug() throws ResourceException, IOException, ReportProcessingException {
    final MasterReport report = DebugReportRunner.parseGoldenSampleReport("Prd-3889.prpt");
    final ByteArrayOutputStream bout = new ByteArrayOutputStream();
    ExcelReportUtil.createXLS(report, bout);

    final HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(bout.toByteArray()));
    final HSSFSheet sheetAt = wb.getSheetAt(0);
    final HSSFRow row = sheetAt.getRow(0);
    final HSSFCell cell0 = row.getCell(0);

    // assert that we are in the correct export type ..
    final HSSFCellStyle cellStyle = cell0.getCellStyle();
    final HSSFColor fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
    final HSSFColor fillForegroundColorColor = cellStyle.getFillForegroundColorColor();
    assertEquals("0:0:0", fillBackgroundColorColor.getHexString());
    assertEquals("FFFF:FFFF:9999", fillForegroundColorColor.getHexString());

    // assert that there are no extra columns ..
    final HSSFRow row8 = sheetAt.getRow(7);
    assertNull(row8);//www.  j  av a  2 s  . c  o m

}

From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd4434IT.java

License:Open Source License

public void testExcelExport() throws Exception {
    final MasterReport report = DebugReportRunner.parseGoldenSampleReport("Prd-3625.prpt");

    final ByteArrayOutputStream bout = new ByteArrayOutputStream();
    ExcelReportUtil.createXLS(report, bout);

    final HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(bout.toByteArray()));
    final HSSFSheet sheetAt = wb.getSheetAt(0);
    final HSSFRow row = sheetAt.getRow(0);
    final HSSFCell cell0 = row.getCell(0);
    assertEquals(Cell.CELL_TYPE_NUMERIC, cell0.getCellType());
    assertEquals("yyyy-MM-dd", cell0.getCellStyle().getDataFormatString());
    final HSSFCell cell1 = row.getCell(1);
    assertEquals(Cell.CELL_TYPE_NUMERIC, cell1.getCellType());
    assertEquals("#,###.00;(#,###.00)", cell1.getCellStyle().getDataFormatString());
}

From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5391.java

License:Open Source License

@Test
public void testSlowExport() throws ResourceException, ReportProcessingException, IOException {
    // This establishes a baseline for the second test using the slow export.

    final MasterReport report = DebugReportRunner.parseLocalReport("Prd-5391.prpt", Prd5391.class);
    final ByteArrayOutputStream bout = new ByteArrayOutputStream();
    ExcelReportUtil.createXLS(report, bout);

    final HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(bout.toByteArray()));
    final HSSFSheet sheetAt = wb.getSheetAt(0);
    final HSSFRow row = sheetAt.getRow(0);
    final HSSFCell cell0 = row.getCell(0);

    // assert that we are in the correct export type ..
    final HSSFCellStyle cellStyle = cell0.getCellStyle();
    final HSSFColor fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
    final HSSFColor fillForegroundColorColor = cellStyle.getFillForegroundColorColor();
    Assert.assertEquals("0:0:0", fillBackgroundColorColor.getHexString());
    Assert.assertEquals("FFFF:8080:8080", fillForegroundColorColor.getHexString());

    HSSFFont font = cellStyle.getFont(wb);
    Assert.assertEquals("Times New Roman", font.getFontName());
}

From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5391.java

License:Open Source License

@Test
public void testFastExport() throws ResourceException, ReportProcessingException, IOException {
    // This establishes a baseline for the second test using the slow export.

    final MasterReport report = DebugReportRunner.parseLocalReport("Prd-5391.prpt", Prd5391.class);
    final ByteArrayOutputStream bout = new ByteArrayOutputStream();
    FastExcelReportUtil.processXls(report, bout);

    final HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(bout.toByteArray()));
    final HSSFSheet sheetAt = wb.getSheetAt(0);
    final HSSFRow row = sheetAt.getRow(0);
    final HSSFCell cell0 = row.getCell(0);

    // assert that we are in the correct export type ..
    final HSSFCellStyle cellStyle = cell0.getCellStyle();
    final HSSFColor fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
    final HSSFColor fillForegroundColorColor = cellStyle.getFillForegroundColorColor();
    Assert.assertEquals("0:0:0", fillBackgroundColorColor.getHexString());
    Assert.assertEquals("FFFF:8080:8080", fillForegroundColorColor.getHexString());

    HSSFFont font = cellStyle.getFont(wb);
    Assert.assertEquals("Times New Roman", font.getFontName());
}