Example usage for org.apache.poi.ss.usermodel Workbook getNumCellStyles

List of usage examples for org.apache.poi.ss.usermodel Workbook getNumCellStyles

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getNumCellStyles

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getNumCellStyles.

Prototype

int getNumCellStyles();

Source Link

Document

Get the number of styles the workbook contains

Usage

From source file:com.canoo.webtest.plugins.exceltest.ExcelStructureFilter.java

License:Open Source License

public void doExecute() throws Exception {
    final Workbook excelWorkbook = getExcelWorkbook();
    final DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
    final Document doc = builder.newDocument();
    final Element root = doc.createElement("excelWorkbook");
    doc.appendChild(root);//  w  ww. j a v  a 2 s.  c o m
    //root.setAttribute("backup", String.valueOf(excelWorkbook.getBackupFlag()));
    root.setAttribute("backup", String.valueOf(Boolean.FALSE));
    root.setAttribute("numberOfFonts", String.valueOf(excelWorkbook.getNumberOfFonts()));
    root.setAttribute("numberOfCellStyles", String.valueOf(excelWorkbook.getNumCellStyles()));
    root.setAttribute("numberOfNames", String.valueOf(excelWorkbook.getNumberOfNames()));
    final Element sheets = doc.createElement("sheets");
    for (int i = 0; i < excelWorkbook.getNumberOfSheets(); i++) {
        final Sheet sheetAt = excelWorkbook.getSheetAt(i);
        final Element sheetElement = doc.createElement("sheet");
        sheetElement.setAttribute("index", String.valueOf(i));
        sheetElement.setAttribute("name", excelWorkbook.getSheetName(i));
        sheetElement.setAttribute("firstRow", String.valueOf(sheetAt.getFirstRowNum()));
        sheetElement.setAttribute("lastRow", String.valueOf(sheetAt.getLastRowNum()));
        sheetElement.setAttribute("physicalRows", String.valueOf(sheetAt.getPhysicalNumberOfRows()));
        sheetElement.setAttribute("defaultRowHeight", String.valueOf(sheetAt.getDefaultRowHeight()));
        sheetElement.setAttribute("defaultColumnWidth", String.valueOf(sheetAt.getDefaultColumnWidth()));
        sheetElement.setAttribute("fitToPage", String.valueOf(sheetAt.getFitToPage()));
        sheets.appendChild(sheetElement);
    }
    root.appendChild(sheets);
    final StringWriter sw = new StringWriter();
    writeXmlFile(doc, sw);
    ContextHelper.defineAsCurrentResponse(getContext(), sw.toString(), "text/xml", getClass());
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.java

License:Open Source License

/**
 * Reloads all sheet and cell styles from the current Workbook.
 *///from   w  w w .java 2  s  .c  o m
public void reloadWorkbookStyles() {
    final Workbook workbook = spreadsheet.getWorkbook();
    if (spreadsheet.getState().cellStyleToCSSStyle == null) {
        spreadsheet.getState().cellStyleToCSSStyle = new HashMap<Integer, String>(workbook.getNumCellStyles());
    } else {
        spreadsheet.getState().cellStyleToCSSStyle.clear();
    }
    shiftedBorderLeftStyles.clear();
    shiftedBorderTopStyles.clear();
    mergedCellBorders.clear();

    // get default text alignments
    CellStyle cellStyle = workbook.getCellStyleAt((short) 0);
    defaultTextAlign = cellStyle.getAlignment();
    // defaultVerticalAlign = cellStyle.getVerticalAlignment();

    // create default style (cell style 0)
    StringBuilder sb = new StringBuilder();
    borderStyles(sb, cellStyle);
    defaultFontStyle(cellStyle, sb);
    colorConverter.defaultColorStyles(cellStyle, sb);
    spreadsheet.getState().cellStyleToCSSStyle.put((int) cellStyle.getIndex(), sb.toString());

    // 0 is default style, create all styles indexed from 1 and upwards
    for (short i = 1; i < workbook.getNumCellStyles(); i++) {
        cellStyle = workbook.getCellStyleAt(i);
        addCellStyleCSS(cellStyle);
    }
    reloadActiveSheetColumnRowStyles();
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.java

License:Open Source License

public void reloadActiveSheetColumnRowStyles() {
    final Workbook workbook = spreadsheet.getWorkbook();

    if (spreadsheet.getState().rowIndexToStyleIndex == null) {
        spreadsheet.getState().rowIndexToStyleIndex = new HashMap<Integer, Integer>(
                workbook.getNumCellStyles());
    } else {/*from   ww  w . j ava 2  s  .  c om*/
        spreadsheet.getState().rowIndexToStyleIndex.clear();
    }
    if (spreadsheet.getState().columnIndexToStyleIndex == null) {
        spreadsheet.getState().columnIndexToStyleIndex = new HashMap<Integer, Integer>(
                workbook.getNumCellStyles());
    } else {
        spreadsheet.getState().columnIndexToStyleIndex.clear();
    }
    if (spreadsheet.getState().lockedColumnIndexes == null) {
        spreadsheet.getState().lockedColumnIndexes = new HashSet<Integer>();
    } else {
        spreadsheet.getState().lockedColumnIndexes.clear();
    }
    if (spreadsheet.getState().lockedRowIndexes == null) {
        spreadsheet.getState().lockedRowIndexes = new HashSet<Integer>();
    } else {
        spreadsheet.getState().lockedRowIndexes.clear();
    }

    Sheet activeSheet = spreadsheet.getActiveSheet();
    for (int i = 0; i < spreadsheet.getRows(); i++) {
        Row row = activeSheet.getRow(i);
        if (row != null && row.getRowStyle() != null) {
            int styleIndex = row.getRowStyle().getIndex();
            spreadsheet.getState().rowIndexToStyleIndex.put(i + 1, styleIndex);
            if (row.getRowStyle().getLocked()) {
                spreadsheet.getState().lockedRowIndexes.add(i + 1);
            }
        } else {
            if (spreadsheet.isActiveSheetProtected()) {
                spreadsheet.getState().lockedRowIndexes.add(i + 1);
            }
        }
    }

    for (int i = 0; i < spreadsheet.getColumns(); i++) {
        if (activeSheet.getColumnStyle(i) != null) {
            int styleIndex = activeSheet.getColumnStyle(i).getIndex();
            spreadsheet.getState().columnIndexToStyleIndex.put(i + 1, styleIndex);
            if (activeSheet.getColumnStyle(i).getLocked()) {
                spreadsheet.getState().lockedColumnIndexes.add(i + 1);
            }
        }
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *//*ww  w.ja va  2  s .  co m*/
@Test
public void testWriteExcelFileAttribute() throws Exception {

    try {
        LOGGER.debug("testWriteExcelFileAttribute start....");

        short rowheight = 40 * 10;
        int columnwidth = 30;

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xls");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        Workbook wb = new HSSFWorkbook();

        Sheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");

        // ? ?
        sheet1.setDefaultRowHeight(rowheight);
        sheet1.setDefaultColumnWidth(columnwidth);

        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        cs.setWrapText(true);

        // 
        cs.setAlignment(CellStyle.ALIGN_RIGHT);

        cs.setFillPattern(CellStyle.DIAMONDS); //  ?

        // ? ?
        cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); //  
        cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // 

        sheet1.setDefaultColumnStyle((short) 0, cs);

        Workbook tmp = excelService.createWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());

        CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1));

        LOGGER.debug("getAlignment : {}", cs1.getAlignment());
        assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment());

        LOGGER.debug("getFillPattern : {}", cs1.getFillPattern());
        assertEquals(CellStyle.DIAMONDS, cs1.getFillPattern());

        LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor());
        LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor());
        assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor());
        assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor());

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testWriteExcelFileAttribute end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-6]  ?  :  ? ?(?, ? )? /*  ww w. j a  va 2  s  . co  m*/
 */
@Test
public void testModifyCellAttribute() throws Exception {

    try {
        LOGGER.debug("testModifyCellAttribute start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellAttribute.xls");

        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        Workbook wbTmp = new HSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        Workbook wb = excelService.loadWorkbook(sb.toString());
        LOGGER.debug("testModifyCellAttribute after loadWorkbook....");

        Sheet sheet = wb.createSheet("cell test sheet2");
        //           sheet.setColumnWidth((short) 3, (short) 200);   // column Width

        CellStyle cs = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontHeight((short) 16);
        font.setBoldweight((short) 3);
        font.setFontName("fixedsys");

        cs.setFont(font);
        cs.setAlignment(CellStyle.ALIGN_RIGHT); // cell 
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            Row row = sheet.createRow(i);
            //              row.setHeight((short)300); // row? height 

            for (int j = 0; j < 5; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sb.toString());
        Sheet sheetT = wbT.getSheet("cell test sheet2");
        LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles());

        CellStyle cs1 = wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1));

        Font fontT = ((HSSFCellStyle) cs1).getFont(wbT);
        LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight());
        LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight());
        LOGGER.debug("font getFontName : {}", fontT.getFontName());
        LOGGER.debug("getAlignment : {}", cs1.getAlignment());
        LOGGER.debug("getWrapText : {}", cs1.getWrapText());

        for (int i = 0; i < 100; i++) {
            Row row1 = sheetT.getRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell1 = row1.getCell(j);
                LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue());
                assertEquals(16, fontT.getFontHeight());
                assertEquals(3, fontT.getBoldweight());
                assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment());
                assertTrue(cs1.getWrapText());
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testModifyCellAttribute end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *///from   ww w . java2s.com
@Test
public void testWriteExcelFileAttribute() throws Exception {

    try {
        log.debug("testWriteExcelFileAttribute start....");

        short rowheight = 40;
        int columnwidth = 30;

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            log.debug("Delete file...." + sb.toString());
        }

        SXSSFWorkbook wb = new SXSSFWorkbook();

        Sheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");

        // ? ?
        sheet1.setDefaultRowHeight(rowheight);
        sheet1.setDefaultColumnWidth(columnwidth);

        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        cs.setWrapText(true);

        // 
        cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

        cs.setFillPattern(HSSFCellStyle.DIAMONDS); //  ?

        // ? ?
        cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); //  
        cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // 

        sheet1.setDefaultColumnStyle((short) 0, cs);

        Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());

        CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1));

        log.debug("getAlignment : " + cs1.getAlignment());
        assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());

        log.debug("getFillPattern : " + cs1.getFillPattern());
        assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern());

        log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor());
        log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor());
        assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor());
        assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor());

    } catch (Exception e) {
        log.error(e.toString());
        throw new Exception(e);
    } finally {
        log.debug("testWriteExcelFileAttribute end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *///from  w ww .  j a v  a 2  s.c o m
@Test
public void testWriteExcelFileAttribute() throws Exception {

    try {
        LOGGER.debug("testWriteExcelFileAttribute start....");

        short rowheight = 40 * 10;
        int columnwidth = 30;

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        XSSFWorkbook wb = new XSSFWorkbook();

        XSSFSheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");

        // ? ?
        sheet1.setDefaultRowHeight(rowheight);
        sheet1.setDefaultColumnWidth(columnwidth);

        Font f2 = wb.createFont();
        XSSFCellStyle cs = wb.createCellStyle();

        cs.setFont(f2);
        cs.setWrapText(true);

        // 
        cs.setAlignment(CellStyle.ALIGN_RIGHT);
        cs.setFillPattern(CellStyle.DIAMONDS); //  ?

        XSSFRow r1 = sheet1.createRow(0);
        r1.createCell(0);

        // ? ?
        cs.setFillForegroundColor(IndexedColors.BLUE.getIndex()); //  
        cs.setFillBackgroundColor(IndexedColors.RED.getIndex()); // 

        sheet1.setDefaultColumnStyle((short) 0, cs);

        Workbook tmp = excelService.createWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());

        CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1));

        LOGGER.debug("getAlignment : {}", cs1.getAlignment());
        assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());

        LOGGER.debug("getFillPattern : {}", cs1.getFillPattern());
        assertEquals(XSSFCellStyle.DIAMONDS, cs1.getFillPattern());

        LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor());
        LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor());

        LOGGER.debug(
                "XSSFWorkbook.getFillBackgroundColor(), XSSFColor().getIndexed() ? ? 0 ? ?");

        assertEquals(IndexedColors.BLUE.getIndex(), cs1.getFillForegroundColor());
        assertEquals(IndexedColors.RED.getIndex(), cs1.getFillBackgroundColor());

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testWriteExcelFileAttribute end....");
    }
}

From source file:org.formulacompiler.spreadsheet.internal.excel.xls.saver.ExcelXLSSaver.java

License:Open Source License

private void extractCellFormatsFrom(Workbook _xwb) {
    final short styleCount = _xwb.getNumCellStyles();
    for (short idx = 0; idx < styleCount; idx++) {
        final HSSFCellStyle cellStyle = (HSSFCellStyle) _xwb.getCellStyleAt(idx);
        try {/* w  w  w . jav  a  2 s  . c o  m*/
            final String styleName = cellStyle.getParentStyle().getUserStyleName();
            if (styleName != null) {
                this.cellStyles.put(styleName, cellStyle);
            }
        } catch (Exception e) {
            // Do nothing, we use only styles with parents
        }
    }
}

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

License:Open Source License

@Test
public void testExcelExport() throws Exception {
    URL resource = getClass().getResource("Prd-4968.prpt");
    ResourceManager mgr = new ResourceManager();
    MasterReport report = (MasterReport) mgr.createDirectly(resource, MasterReport.class).getResource();
    ByteArrayOutputStream bout = new ByteArrayOutputStream();
    ExcelReportUtil.createXLS(report, bout);
    Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray()));
    assertEquals(34, workbook.getNumCellStyles());
    assertEquals(9, workbook.getNumberOfFonts());
}

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

License:Open Source License

@Test
public void testExcel2007Export() throws Exception {
    URL resource = getClass().getResource("Prd-4968.prpt");
    ResourceManager mgr = new ResourceManager();
    MasterReport report = (MasterReport) mgr.createDirectly(resource, MasterReport.class).getResource();
    ByteArrayOutputStream bout = new ByteArrayOutputStream();
    ExcelReportUtil.createXLSX(report, bout);
    Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray()));
    assertEquals(14, workbook.getNumCellStyles());
    assertEquals(6, workbook.getNumberOfFonts());

    //    File testOutputFile = DebugReportRunner.createTestOutputFile();
    //    ExcelReportUtil.createXLSX(report, "test-output/Prd-4988.xlsx");

}