next »

[Gary C.]

Hello

I am trying to get to grips with the Apache POI framework and so far so good using POI 4.0.1.

However, I wish to correctly format the cells for the data they hold: text, integer, date etc. and I am struggling with doing so with dates. For some reason I am getting a Java java.lang.NullPointerException. I would appreciate any input from others who may have already done this and any comments on my approach.

My approach is to create an array of available cell styles added to the workbook which can then be reused. The code for this looks like this:

Code:

private function xls_prepareWorkBookStyles(workBook)

    define workBook workBookType,
        cellStyle cellStyleType,
        font fontType,
        dataFormat dataFormatType

    let font = xls_createFont(workBook)
    let dataFormat = workBook.createDataFormat()

    call xls_setFontBold(font, workbookR.headingR.bBold)

    let cellStyle = xls_createStyle(workBook)
    call xls_setCellStyleAlignment(cellStyle, ALIGNMENTCENTER)
    call xls_setCellStyleColour(cellStyle, INDEXEDCOLOURGREY25PERCENT)
    call xls_setCellStyleFillPattern(cellStyle, FILLPATTERNSOLIDFOREGROUND)
    call xls_setCellStyleFont(cellStyle, font)
    let stylesA[stylesA.getLength() + 1].styleName = CELLSTYLEHEADER
    let stylesA[stylesA.getLength()].cellStyle = cellStyle


    let cellStyle = xls_createStyle(workBook)
    call xls_setCellStyleAlignment(cellStyle, ALIGNMENTGENERAL)
    call xls_setCellStyleColour(cellStyle, INDEXEDCOLOURBLACK)
    call xls_setCellStyleFillPattern(cellStyle, FILLPATTERNNOFILL)
    call cellStyle.setDataFormat(dataFormat.getFormat("dd/mm/yyyy"))
    let stylesA[stylesA.getLength() + 1].styleName = CELLSTYLEDATE
    let stylesA[stylesA.getLength()].cellStyle = cellStyle
    
end function

Note that the uppercase elements are local constants.

With that done I then have a function to write the data to a newly created cell which looks like this:

Code:

function xls_writeRowCell(sheetRow, columnNumber, cellStyleName, cellDate)

    define sheetRow rowType,
        columnNumber integer,
        cellStyleName, 
        cellDate date,
        idx smallint,
        cell cellType,
        cellStyle cellStyleType,
        cellPattern,
        cellString string,
        javaDate java.util.Date,
        sdf SimpleDateFormat,
        i integer

# New cell at specified row and column

    let cell = xls_createRowCell(sheetRow, columnNumber)

# Set the date format required

    let cellPattern = "dd/MM/yyyy"
    call sdf.applyPattern(cellPattern)

# Convert passed date to string and convert to a Java date

    let cellString = cellDate using "dd/mm/yyyy"
    let javaDate = sdf.parse(cellString)
    
# Get the Excel representation of this date

    let i = DateUtil.getExcelDate(javaDate)

# Write the value to the cell

    call cell.setCellValue(i)
    
# Apply the style

    let idx = stylesA.search("stylename", cellStyleName)
    let cellStyle = stylesA[idx].cellStyle
    call cell.setCellStyle(cellStyle)

end function

No matter what I do,

Code:

    call sdf.applyPattern(cellPattern)

results in the null pointer exception.

This is likely not a POI issue but my lack of Java knowledge! Any help would be appreciated.

Gary

[Gary C.]

Just as a follow up - it was indeed my Java knowledge (or lack of).

I was not initiating the objects and needed to call:

Code:

    let javaDate = java.util.Date.create()
    let sdf = java.text.SimpleDateFormat.create()

before I started to make reference to them.

Schoolboy error!

Gary