FW: Formatting a cell as date while preserving other formatting associated with that cell.

[Rohan Grover <rg...@fisci.com>]

 

Hello all,

 

I am trying to update a Cell in a Sheet in a XSSFWorkbook with
GregorianCalendar value. The cell is already formatted with an inbuilt
'Format as table' style. I know I have to setDataFormat for that cell to get
the date displayed as 'm/d/yy h:mm' and I would like to do that while
preserving other formatting  the cell carries from the 'Format as table'
style.  Below I provide a Pseudo code for the approach I have tried and its
result. I have not been successfully is achieving my goal. I was hoping
someone would have done something similar. Any help is greatly appreciated.

 

 

Workbook templateWorkBook = WorkbookFactory.create(new
FileInputStream("Template.xlsx"));

Sheet sheet = templateWorkBook.getSheetAt(0);

Row row = sheet.getRow(0)

Cell cell = row.getCell(0,Row.CREATE_NULL_AS_BLANK);

 

GregorianCalendar calendarObj = new GregorianCalendar();

 

XSSFCellStyle dateCellStyle =
(XSSFCellStyle)templateWorkBook.createCellStyle();

dateCellStyle.cloneStyleFrom((XSSFCellStyle)cell.getCellStyle());

dateCellStyle.setDataFormat((short)BuiltinFormats.getBuiltinFormat("m/d/yy
h:mm"));

 

cell.setCellValue(calendarObj);

cell.setCellStyle(dateCellStyle);

 

 

Though the above code sets the display format of that cell as "m/d/yy h:mm",
it deletes the rest of the formatting associated with that cell.I have tried
setting the cell value only, i.e without formatting it as "m/d/yy h:mm", and
the other formatting stays with the cell but the date is displayed as
numeric value.  Again, any help is greatly appreciated.

 

-Rohan Grover

 

Re: FW: Formatting a cell as date while preserving other formatting associated with that cell.

[Aram Mirzadeh <aw...@mbcli.com>]

I just did this in 3.7-release and it worked for me. The cell kept the 
existing style (which was just bolded).   Just a note that I use SS 
style to be consistent, rather than switch back to XSSF as you have done 
here.

Aram

On 12/8/2010 4:14 PM, Rohan Grover wrote:
>
>
> Hello all,
>
>
>
> I am trying to update a Cell in a Sheet in a XSSFWorkbook with
> GregorianCalendar value. The cell is already formatted with an inbuilt
> 'Format as table' style. I know I have to setDataFormat for that cell to get
> the date displayed as 'm/d/yy h:mm' and I would like to do that while
> preserving other formatting  the cell carries from the 'Format as table'
> style.  Below I provide a Pseudo code for the approach I have tried and its
> result. I have not been successfully is achieving my goal. I was hoping
> someone would have done something similar. Any help is greatly appreciated.
>
>
>
>
>
> Workbook templateWorkBook = WorkbookFactory.create(new
> FileInputStream("Template.xlsx"));
>
> Sheet sheet = templateWorkBook.getSheetAt(0);
>
> Row row = sheet.getRow(0)
>
> Cell cell = row.getCell(0,Row.CREATE_NULL_AS_BLANK);
>
>
>
> GregorianCalendar calendarObj = new GregorianCalendar();
>
>
>
> XSSFCellStyle dateCellStyle =
> (XSSFCellStyle)templateWorkBook.createCellStyle();
>
> dateCellStyle.cloneStyleFrom((XSSFCellStyle)cell.getCellStyle());
>
> dateCellStyle.setDataFormat((short)BuiltinFormats.getBuiltinFormat("m/d/yy
> h:mm"));
>
>
>
> cell.setCellValue(calendarObj);
>
> cell.setCellStyle(dateCellStyle);
>
>
>
>
>
> Though the above code sets the display format of that cell as "m/d/yy h:mm",
> it deletes the rest of the formatting associated with that cell.I have tried
> setting the cell value only, i.e without formatting it as "m/d/yy h:mm", and
> the other formatting stays with the cell but the date is displayed as
> numeric value.  Again, any help is greatly appreciated.
>
>
>
> -Rohan Grover
>
>
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org