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:
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:
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,
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
Just as a follow up - it was indeed my Java knowledge (or lack of).
I was not initiating the objects and needed to call:
let javaDate = java.util.Date.create()
let sdf = java.text.SimpleDateFormat.create()
before I started to make reference to them.
Schoolboy error!
Gary