Search in sources :

Example 46 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project ddf by codice.

the class RrdMetricsRetriever method createSheet.

/**
     * Creates an Excel worksheet containing the metric's data (timestamps and values) for the
     * specified time range. This worksheet is titled with the trhe metric's name and added to the
     * specified Workbook.
     *
     * @param wb          the workbook to add this worksheet to
     * @param metricName  the name of the metric whose data is being rendered in this worksheet
     * @param rrdFilename the name of the RRD file to retrieve the metric's data from
     * @param startTime   start time, in seconds since Unix epoch, to fetch metric's data
     * @param endTime     end time, in seconds since Unix epoch, to fetch metric's data
     * @throws IOException
     * @throws MetricsGraphException
     */
private void createSheet(Workbook wb, String metricName, String rrdFilename, long startTime, long endTime) throws IOException, MetricsGraphException {
    LOGGER.trace("ENTERING: createSheet");
    MetricData metricData = getMetricData(rrdFilename, startTime, endTime);
    String displayableMetricName = convertCamelCase(metricName);
    String title = displayableMetricName + " for " + getCalendarTime(startTime) + " to " + getCalendarTime(endTime);
    Sheet sheet = wb.createSheet(displayableMetricName);
    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle columnHeadingsStyle = wb.createCellStyle();
    columnHeadingsStyle.setFont(headerFont);
    CellStyle bannerStyle = wb.createCellStyle();
    bannerStyle.setFont(headerFont);
    bannerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
    bannerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    int rowCount = 0;
    Row row = sheet.createRow((short) rowCount);
    Cell cell = row.createCell(0);
    cell.setCellValue(title);
    cell.setCellStyle(bannerStyle);
    rowCount++;
    // Blank row for spacing/readability
    row = sheet.createRow((short) rowCount);
    cell = row.createCell(0);
    cell.setCellValue("");
    rowCount++;
    row = sheet.createRow((short) rowCount);
    cell = row.createCell(0);
    cell.setCellValue("Timestamp");
    cell.setCellStyle(columnHeadingsStyle);
    cell = row.createCell(1);
    cell.setCellValue("Value");
    cell.setCellStyle(columnHeadingsStyle);
    rowCount++;
    List<Long> timestamps = metricData.getTimestamps();
    List<Double> values = metricData.getValues();
    for (int i = 0; i < timestamps.size(); i++) {
        String timestamp = getCalendarTime(timestamps.get(i));
        row = sheet.createRow((short) rowCount);
        row.createCell(0).setCellValue(timestamp);
        row.createCell(1).setCellValue(values.get(i));
        rowCount++;
    }
    if (metricData.hasTotalCount()) {
        // Blank row for spacing/readability
        row = sheet.createRow((short) rowCount);
        cell = row.createCell(0);
        cell.setCellValue("");
        rowCount++;
        row = sheet.createRow((short) rowCount);
        cell = row.createCell(0);
        cell.setCellValue("Total Count: ");
        cell.setCellStyle(columnHeadingsStyle);
        row.createCell(1).setCellValue(metricData.getTotalCount());
    }
    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    LOGGER.trace("EXITING: createSheet");
}
Also used : CellStyle(org.apache.poi.ss.usermodel.CellStyle) Row(org.apache.poi.ss.usermodel.Row) Sheet(org.apache.poi.ss.usermodel.Sheet) Cell(org.apache.poi.ss.usermodel.Cell) Font(org.apache.poi.ss.usermodel.Font)

Example 47 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class TestXSSFWorkbook method getCellStyleAt.

@Test
public void getCellStyleAt() throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    try {
        short i = 0;
        //get default style
        CellStyle cellStyleAt = workbook.getCellStyleAt(i);
        assertNotNull(cellStyleAt);
        //get custom style
        StylesTable styleSource = workbook.getStylesSource();
        XSSFCellStyle customStyle = new XSSFCellStyle(styleSource);
        XSSFFont font = new XSSFFont();
        font.setFontName("Verdana");
        customStyle.setFont(font);
        int x = styleSource.putStyle(customStyle);
        cellStyleAt = workbook.getCellStyleAt((short) x);
        assertNotNull(cellStyleAt);
    } finally {
        workbook.close();
    }
}
Also used : StylesTable(org.apache.poi.xssf.model.StylesTable) CellStyle(org.apache.poi.ss.usermodel.CellStyle) Test(org.junit.Test)

Example 48 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project Gargoyle by callakrsos.

the class IExcelDataSetHandler method createCell.

/**
	 * 셀 생성 
	 * 
	 * @작성자 : KYJ
	 * @작성일 : 2016. 9. 6. 
	 * @param sheet
	 * @param value
	 * @param row
	 * @param column
	 * @return
	 * @throws Exception
	 */
public default default Cell createCell(Sheet sheet, Object value, int row, int column) throws Exception {
    Row row2 = sheet.getRow(row);
    if (row2 == null) {
        row2 = sheet.createRow(row);
    }
    Cell createCell = null;
    try {
        createCell = row2.getCell(column);
        if (createCell == null) {
            createCell = row2.createCell(column);
        }
    } catch (NullPointerException e) {
        createCell = row2.createCell(column);
    }
    if (value instanceof String) {
        createCell.setCellValue((String) value);
    } else if (value instanceof Integer) {
        createCell.setCellValue((Integer) value);
    } else if (value instanceof Double) {
        createCell.setCellValue((Double) value);
    } else if (value instanceof Float) {
        createCell.setCellValue((Float) value);
    } else if (value == null) {
        createCell.setCellValue("");
    } else if (value instanceof Date) {
        createCell.setCellValue((Date) value);
    } else /*그 이외 특이 사항은 구현. else if로 구현할것.*/
    {
        createCell.setCellValue(value.toString());
    }
    /* 
		 * Border 처리시 속도 저하의 원인... 
		 * 
		 */
    Callback<CellStyle, CellStyle> cellStyleFactory = dataCellStyleFactory();
    if (cellStyleFactory != null) {
        CellStyle call = cellStyleFactory.call(sheet.getWorkbook().createCellStyle());
        if (call != null)
            createCell.setCellStyle(call);
    }
    return createCell;
}
Also used : Row(org.apache.poi.ss.usermodel.Row) CellStyle(org.apache.poi.ss.usermodel.CellStyle) Cell(org.apache.poi.ss.usermodel.Cell) Date(java.util.Date)

Example 49 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class TestXSSFCell method setUp_testCopyCellFrom_CellCopyPolicy.

private void setUp_testCopyCellFrom_CellCopyPolicy() {
    @SuppressWarnings("resource") final XSSFWorkbook wb = new XSSFWorkbook();
    final XSSFRow row = wb.createSheet("Sheet1").createRow(0);
    srcCell = row.createCell(0);
    destCell = row.createCell(1);
    srcCell.setCellFormula("2+3");
    final CellStyle style = wb.createCellStyle();
    style.setBorderTop(BorderStyle.THICK);
    style.setFillBackgroundColor((short) 5);
    srcCell.setCellStyle(style);
    destCell.setCellValue(true);
}
Also used : CellStyle(org.apache.poi.ss.usermodel.CellStyle)

Example 50 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class TestXSSFCell method testCopyCellFrom_CellCopyPolicy_copyHyperlink.

@Test
public final void testCopyCellFrom_CellCopyPolicy_copyHyperlink() throws IOException {
    setUp_testCopyCellFrom_CellCopyPolicy();
    final Workbook wb = srcCell.getSheet().getWorkbook();
    final CreationHelper createHelper = wb.getCreationHelper();
    srcCell.setCellValue("URL LINK");
    Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
    link.setAddress("http://poi.apache.org/");
    srcCell.setHyperlink(link);
    // Set link cell style (optional)
    CellStyle hlinkStyle = wb.createCellStyle();
    Font hlinkFont = wb.createFont();
    hlinkFont.setUnderline(Font.U_SINGLE);
    hlinkFont.setColor(IndexedColors.BLUE.getIndex());
    hlinkStyle.setFont(hlinkFont);
    srcCell.setCellStyle(hlinkStyle);
    // Copy hyperlink
    final CellCopyPolicy policy = new CellCopyPolicy.Builder().copyHyperlink(true).mergeHyperlink(false).build();
    destCell.copyCellFrom(srcCell, policy);
    assertNotNull(destCell.getHyperlink());
    assertSame("unit test assumes srcCell and destCell are on the same sheet", srcCell.getSheet(), destCell.getSheet());
    final List<XSSFHyperlink> links = srcCell.getSheet().getHyperlinkList();
    assertEquals("number of hyperlinks on sheet", 2, links.size());
    assertEquals("source hyperlink", new CellReference(srcCell).formatAsString(), links.get(0).getCellRef());
    assertEquals("destination hyperlink", new CellReference(destCell).formatAsString(), links.get(1).getCellRef());
    wb.close();
}
Also used : CreationHelper(org.apache.poi.ss.usermodel.CreationHelper) CellStyle(org.apache.poi.ss.usermodel.CellStyle) CellReference(org.apache.poi.ss.util.CellReference) Workbook(org.apache.poi.ss.usermodel.Workbook) Font(org.apache.poi.ss.usermodel.Font) CellCopyPolicy(org.apache.poi.ss.usermodel.CellCopyPolicy) Hyperlink(org.apache.poi.ss.usermodel.Hyperlink) Test(org.junit.Test)

Aggregations

CellStyle (org.apache.poi.ss.usermodel.CellStyle)53 Cell (org.apache.poi.ss.usermodel.Cell)31 Row (org.apache.poi.ss.usermodel.Row)27 Sheet (org.apache.poi.ss.usermodel.Sheet)26 Workbook (org.apache.poi.ss.usermodel.Workbook)25 Test (org.junit.Test)16 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)14 Font (org.apache.poi.ss.usermodel.Font)13 FileOutputStream (java.io.FileOutputStream)8 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)7 CreationHelper (org.apache.poi.ss.usermodel.CreationHelper)7 CellReference (org.apache.poi.ss.util.CellReference)5 Date (java.util.Date)4 DataFormat (org.apache.poi.ss.usermodel.DataFormat)4 IOException (java.io.IOException)3 DecimalFormat (java.text.DecimalFormat)3 HashMap (java.util.HashMap)3 CellType (org.apache.poi.ss.usermodel.CellType)3 File (java.io.File)2 OutputStream (java.io.OutputStream)2