You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Bengt Rodehav <be...@rodehav.com> on 2016/03/07 16:52:57 UTC
Detect if a cell is date formatted
I have a situation where an Excel template I get from a third party (this
is for EIOPA's Solvency II reporting) wrongly has set date formatting in a
number of cells. I want to fix this programatically.
I use code like this to try to detect date formatting:
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(" Date at sheet: " + sheetName + ", row: " +
row.getRowNum() + ", column: " + cell.getColumnIndex());
}
}
However, when the cells are empty (remember this is a template), the cell
type always seems to be Cell.CELL_TYPE_STRING not Cell.CELL_TYPE_NUMERIC so
the above code doesn't find any date cells.
If I change the code to...
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(" Date at sheet: " + sheetName + ", row: " +
row.getRowNum() + ", column: " + cell.getColumnIndex());
}
}
...then I get an exceptions since the DateUtil.isCellDateFormatted()
requires the cell to contain a numerice value.
So, I conclude that the cell's type seems to be determined by the value in
the cell - not the metadata for the cell. This seems a bit strange to me.
The problem is that if the user enters a numeric value in the cell, then it
will be date formatted (which it shouldn't). The user is allowed to enter
any string (even a numeric value). So, I want to remove the date formatting
regardless of the cell type. But I cannot detect that it is a date unless I
enter a numeric value in the cell.
Does anyone have a tip as to how I can detect date formatting without
requiring a value in the cell?
/Bengt
Re: Detect if a cell is date formatted
Posted by Bengt Rodehav <be...@rodehav.com>.
It seems to work so I think I'll stick with this solution for now.
/Bengt
2016-03-09 18:47 GMT+01:00 Blake Watson <bl...@pnmac.com>:
> That's cool! I'd be concerned about whether it would work in a situation
> where a column has been defined as a numeric one (i.e., so the cells could
> easily be nil), but I hadn't thought of using "IsADateFormat" before...
>
> On Wed, Mar 9, 2016 at 1:16 AM, Bengt Rodehav <be...@rodehav.com> wrote:
>
> > Thanks for your reply Blake,
> >
> > Yeah I was actually considering this (put a number in, check if date,
> then
> > clear the cell) but I instead did this:
> >
> > I copied the following method from DateUtil and removed the check against
> > the value of the cell.
> >
> > public static boolean isCellDateFormatted(Cell cell) {
> > if (cell == null) return false;
> > boolean bDate = false;
> >
> > // double d = cell.getNumericCellValue();
> > // if ( DateUtil.isValidExcelDate(d) ) {
> > CellStyle style = cell.getCellStyle();
> > if(style==null) return false;
> > int i = style.getDataFormat();
> > String f = style.getDataFormatString();
> > bDate = DateUtil.isADateFormat(i, f);
> > // }
> > return bDate;
> > }
> >
> > Now only the actual format is checked, not the value. I would prefer not
> > having to duplicate and maintain this code but I think it's still better
> > than having to put temporary values in cells. I think the method is not
> > properly named since it doesn't only check the formatting but also throws
> > in a check concerning the cell value. I think it would be wise to add an
> > extra parameter indicating whether the cell's value should be checked as
> > well or if it is just a format check. The old version (with just the Cell
> > parameter) could still exist but call the new method with a second
> > parameter indicating that the cell's value should be checked.
> >
> > /Bengt
> >
> > 2016-03-07 18:53 GMT+01:00 Blake Watson <bl...@pnmac.com>:
> >
> > > It may not be possible, in the sense that Excel may not decide that a
> > cell
> > > is a date unless it has a numeric value in it and is so formatted. I've
> > had
> > > a lot of what seem to be odd phenomena around that, which make sense if
> > you
> > > consider how spreadsheets evolved and are commonly used.
> > >
> > > I realize it's an extra step but if you can't without putting a number
> > in,
> > > why not put a number in?
> > >
> > > On Mon, Mar 7, 2016 at 7:52 AM, Bengt Rodehav <be...@rodehav.com>
> wrote:
> > >
> > > > I have a situation where an Excel template I get from a third party
> > (this
> > > > is for EIOPA's Solvency II reporting) wrongly has set date formatting
> > in
> > > a
> > > > number of cells. I want to fix this programatically.
> > > >
> > > > I use code like this to try to detect date formatting:
> > > >
> > > > if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
> > > > if (DateUtil.isCellDateFormatted(cell)) {
> > > > System.out.println(" Date at sheet: " + sheetName + ", row: " +
> > > > row.getRowNum() + ", column: " + cell.getColumnIndex());
> > > > }
> > > > }
> > > >
> > > > However, when the cells are empty (remember this is a template), the
> > cell
> > > > type always seems to be Cell.CELL_TYPE_STRING not
> > Cell.CELL_TYPE_NUMERIC
> > > so
> > > > the above code doesn't find any date cells.
> > > >
> > > > If I change the code to...
> > > >
> > > > if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
> > > > if (DateUtil.isCellDateFormatted(cell)) {
> > > > System.out.println(" Date at sheet: " + sheetName + ", row: " +
> > > > row.getRowNum() + ", column: " + cell.getColumnIndex());
> > > > }
> > > > }
> > > >
> > > > ...then I get an exceptions since the DateUtil.isCellDateFormatted()
> > > > requires the cell to contain a numerice value.
> > > >
> > > > So, I conclude that the cell's type seems to be determined by the
> value
> > > in
> > > > the cell - not the metadata for the cell. This seems a bit strange to
> > me.
> > > > The problem is that if the user enters a numeric value in the cell,
> > then
> > > it
> > > > will be date formatted (which it shouldn't). The user is allowed to
> > enter
> > > > any string (even a numeric value). So, I want to remove the date
> > > formatting
> > > > regardless of the cell type. But I cannot detect that it is a date
> > > unless I
> > > > enter a numeric value in the cell.
> > > >
> > > > Does anyone have a tip as to how I can detect date formatting without
> > > > requiring a value in the cell?
> > > >
> > > > /Bengt
> > > >
> > >
> > >
> > >
> > > --
> > >
> > > *Blake Watson*
> > >
> > > *PNMAC*
> > > Application Development Manager
> > > 5898 Condor Drive
> > > Moorpark, CA 93021
> > > (805) 330.4911 x7742
> > > blake.watson@pnmac.com <me...@pnmac.com>
> > > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> > >
> >
>
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742
> blake.watson@pnmac.com <me...@pnmac.com>
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>
Re: Detect if a cell is date formatted
Posted by Blake Watson <bl...@pnmac.com>.
That's cool! I'd be concerned about whether it would work in a situation
where a column has been defined as a numeric one (i.e., so the cells could
easily be nil), but I hadn't thought of using "IsADateFormat" before...
On Wed, Mar 9, 2016 at 1:16 AM, Bengt Rodehav <be...@rodehav.com> wrote:
> Thanks for your reply Blake,
>
> Yeah I was actually considering this (put a number in, check if date, then
> clear the cell) but I instead did this:
>
> I copied the following method from DateUtil and removed the check against
> the value of the cell.
>
> public static boolean isCellDateFormatted(Cell cell) {
> if (cell == null) return false;
> boolean bDate = false;
>
> // double d = cell.getNumericCellValue();
> // if ( DateUtil.isValidExcelDate(d) ) {
> CellStyle style = cell.getCellStyle();
> if(style==null) return false;
> int i = style.getDataFormat();
> String f = style.getDataFormatString();
> bDate = DateUtil.isADateFormat(i, f);
> // }
> return bDate;
> }
>
> Now only the actual format is checked, not the value. I would prefer not
> having to duplicate and maintain this code but I think it's still better
> than having to put temporary values in cells. I think the method is not
> properly named since it doesn't only check the formatting but also throws
> in a check concerning the cell value. I think it would be wise to add an
> extra parameter indicating whether the cell's value should be checked as
> well or if it is just a format check. The old version (with just the Cell
> parameter) could still exist but call the new method with a second
> parameter indicating that the cell's value should be checked.
>
> /Bengt
>
> 2016-03-07 18:53 GMT+01:00 Blake Watson <bl...@pnmac.com>:
>
> > It may not be possible, in the sense that Excel may not decide that a
> cell
> > is a date unless it has a numeric value in it and is so formatted. I've
> had
> > a lot of what seem to be odd phenomena around that, which make sense if
> you
> > consider how spreadsheets evolved and are commonly used.
> >
> > I realize it's an extra step but if you can't without putting a number
> in,
> > why not put a number in?
> >
> > On Mon, Mar 7, 2016 at 7:52 AM, Bengt Rodehav <be...@rodehav.com> wrote:
> >
> > > I have a situation where an Excel template I get from a third party
> (this
> > > is for EIOPA's Solvency II reporting) wrongly has set date formatting
> in
> > a
> > > number of cells. I want to fix this programatically.
> > >
> > > I use code like this to try to detect date formatting:
> > >
> > > if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
> > > if (DateUtil.isCellDateFormatted(cell)) {
> > > System.out.println(" Date at sheet: " + sheetName + ", row: " +
> > > row.getRowNum() + ", column: " + cell.getColumnIndex());
> > > }
> > > }
> > >
> > > However, when the cells are empty (remember this is a template), the
> cell
> > > type always seems to be Cell.CELL_TYPE_STRING not
> Cell.CELL_TYPE_NUMERIC
> > so
> > > the above code doesn't find any date cells.
> > >
> > > If I change the code to...
> > >
> > > if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
> > > if (DateUtil.isCellDateFormatted(cell)) {
> > > System.out.println(" Date at sheet: " + sheetName + ", row: " +
> > > row.getRowNum() + ", column: " + cell.getColumnIndex());
> > > }
> > > }
> > >
> > > ...then I get an exceptions since the DateUtil.isCellDateFormatted()
> > > requires the cell to contain a numerice value.
> > >
> > > So, I conclude that the cell's type seems to be determined by the value
> > in
> > > the cell - not the metadata for the cell. This seems a bit strange to
> me.
> > > The problem is that if the user enters a numeric value in the cell,
> then
> > it
> > > will be date formatted (which it shouldn't). The user is allowed to
> enter
> > > any string (even a numeric value). So, I want to remove the date
> > formatting
> > > regardless of the cell type. But I cannot detect that it is a date
> > unless I
> > > enter a numeric value in the cell.
> > >
> > > Does anyone have a tip as to how I can detect date formatting without
> > > requiring a value in the cell?
> > >
> > > /Bengt
> > >
> >
> >
> >
> > --
> >
> > *Blake Watson*
> >
> > *PNMAC*
> > Application Development Manager
> > 5898 Condor Drive
> > Moorpark, CA 93021
> > (805) 330.4911 x7742
> > blake.watson@pnmac.com <me...@pnmac.com>
> > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> >
>
--
*Blake Watson*
*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
blake.watson@pnmac.com <me...@pnmac.com>
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Re: Detect if a cell is date formatted
Posted by Bengt Rodehav <be...@rodehav.com>.
Thanks for your reply Blake,
Yeah I was actually considering this (put a number in, check if date, then
clear the cell) but I instead did this:
I copied the following method from DateUtil and removed the check against
the value of the cell.
public static boolean isCellDateFormatted(Cell cell) {
if (cell == null) return false;
boolean bDate = false;
// double d = cell.getNumericCellValue();
// if ( DateUtil.isValidExcelDate(d) ) {
CellStyle style = cell.getCellStyle();
if(style==null) return false;
int i = style.getDataFormat();
String f = style.getDataFormatString();
bDate = DateUtil.isADateFormat(i, f);
// }
return bDate;
}
Now only the actual format is checked, not the value. I would prefer not
having to duplicate and maintain this code but I think it's still better
than having to put temporary values in cells. I think the method is not
properly named since it doesn't only check the formatting but also throws
in a check concerning the cell value. I think it would be wise to add an
extra parameter indicating whether the cell's value should be checked as
well or if it is just a format check. The old version (with just the Cell
parameter) could still exist but call the new method with a second
parameter indicating that the cell's value should be checked.
/Bengt
2016-03-07 18:53 GMT+01:00 Blake Watson <bl...@pnmac.com>:
> It may not be possible, in the sense that Excel may not decide that a cell
> is a date unless it has a numeric value in it and is so formatted. I've had
> a lot of what seem to be odd phenomena around that, which make sense if you
> consider how spreadsheets evolved and are commonly used.
>
> I realize it's an extra step but if you can't without putting a number in,
> why not put a number in?
>
> On Mon, Mar 7, 2016 at 7:52 AM, Bengt Rodehav <be...@rodehav.com> wrote:
>
> > I have a situation where an Excel template I get from a third party (this
> > is for EIOPA's Solvency II reporting) wrongly has set date formatting in
> a
> > number of cells. I want to fix this programatically.
> >
> > I use code like this to try to detect date formatting:
> >
> > if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
> > if (DateUtil.isCellDateFormatted(cell)) {
> > System.out.println(" Date at sheet: " + sheetName + ", row: " +
> > row.getRowNum() + ", column: " + cell.getColumnIndex());
> > }
> > }
> >
> > However, when the cells are empty (remember this is a template), the cell
> > type always seems to be Cell.CELL_TYPE_STRING not Cell.CELL_TYPE_NUMERIC
> so
> > the above code doesn't find any date cells.
> >
> > If I change the code to...
> >
> > if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
> > if (DateUtil.isCellDateFormatted(cell)) {
> > System.out.println(" Date at sheet: " + sheetName + ", row: " +
> > row.getRowNum() + ", column: " + cell.getColumnIndex());
> > }
> > }
> >
> > ...then I get an exceptions since the DateUtil.isCellDateFormatted()
> > requires the cell to contain a numerice value.
> >
> > So, I conclude that the cell's type seems to be determined by the value
> in
> > the cell - not the metadata for the cell. This seems a bit strange to me.
> > The problem is that if the user enters a numeric value in the cell, then
> it
> > will be date formatted (which it shouldn't). The user is allowed to enter
> > any string (even a numeric value). So, I want to remove the date
> formatting
> > regardless of the cell type. But I cannot detect that it is a date
> unless I
> > enter a numeric value in the cell.
> >
> > Does anyone have a tip as to how I can detect date formatting without
> > requiring a value in the cell?
> >
> > /Bengt
> >
>
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742
> blake.watson@pnmac.com <me...@pnmac.com>
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>
Re: Detect if a cell is date formatted
Posted by Blake Watson <bl...@pnmac.com>.
It may not be possible, in the sense that Excel may not decide that a cell
is a date unless it has a numeric value in it and is so formatted. I've had
a lot of what seem to be odd phenomena around that, which make sense if you
consider how spreadsheets evolved and are commonly used.
I realize it's an extra step but if you can't without putting a number in,
why not put a number in?
On Mon, Mar 7, 2016 at 7:52 AM, Bengt Rodehav <be...@rodehav.com> wrote:
> I have a situation where an Excel template I get from a third party (this
> is for EIOPA's Solvency II reporting) wrongly has set date formatting in a
> number of cells. I want to fix this programatically.
>
> I use code like this to try to detect date formatting:
>
> if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
> if (DateUtil.isCellDateFormatted(cell)) {
> System.out.println(" Date at sheet: " + sheetName + ", row: " +
> row.getRowNum() + ", column: " + cell.getColumnIndex());
> }
> }
>
> However, when the cells are empty (remember this is a template), the cell
> type always seems to be Cell.CELL_TYPE_STRING not Cell.CELL_TYPE_NUMERIC so
> the above code doesn't find any date cells.
>
> If I change the code to...
>
> if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
> if (DateUtil.isCellDateFormatted(cell)) {
> System.out.println(" Date at sheet: " + sheetName + ", row: " +
> row.getRowNum() + ", column: " + cell.getColumnIndex());
> }
> }
>
> ...then I get an exceptions since the DateUtil.isCellDateFormatted()
> requires the cell to contain a numerice value.
>
> So, I conclude that the cell's type seems to be determined by the value in
> the cell - not the metadata for the cell. This seems a bit strange to me.
> The problem is that if the user enters a numeric value in the cell, then it
> will be date formatted (which it shouldn't). The user is allowed to enter
> any string (even a numeric value). So, I want to remove the date formatting
> regardless of the cell type. But I cannot detect that it is a date unless I
> enter a numeric value in the cell.
>
> Does anyone have a tip as to how I can detect date formatting without
> requiring a value in the cell?
>
> /Bengt
>
--
*Blake Watson*
*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
blake.watson@pnmac.com <me...@pnmac.com>
www.PennyMacUSA.com <http://www.pennymacusa.com/>