I've made a date formatter, and I'm posting it here in hopes it will be useful
to anyone.
It tries to transform excel cell format into either DecimalFormat or
SimpleDateFormat and formats doubles and Dates into strings.
It handles most features, except ?/?'s, string formats, conditional
formatting. It handles colouring and zero/negative patterns. It tries to
detect certain formatting errors, but it's laxier than Excel itself.
Maybe it could be integrated into usermodel.utils?
Feedback is always welcome :)
package ru.sbtc.util;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
public class ExcelDataFormatter
{
private boolean isDate = false;
private boolean malformed = false;
private boolean niy = false;
private String colorPOSSNG, colorNEG, colorZERO;
private DateFormat df;
private NumberFormat nfPOSSNG, nfNEG, nfZERO;
public ExcelDataFormatter(String format)
{
if(format.equalsIgnoreCase("General")
|| format.equalsIgnoreCase("@"))
{
nfPOSSNG = new DecimalFormat();
}
else
{
setupFormat(format + ";");
}
}
private void setupFormat(String format)
{
final int POSSNG = 1, NEG = 2, ZERO = 3;
final int TUNK = 0, TDATE = 1, TNUM = 2;
boolean quot = false, square = false, hour = false;
boolean ampm = isAmPm(format);
boolean shield = false, end = false;
int section = POSSNG, type = TUNK;
StringBuffer cut = new StringBuffer();
StringBuffer formatString = new StringBuffer();
String low = format.toLowerCase();
for(int i = 0; i < format.length(); i++)
{
char ch = format.charAt(i);
if(shield)
{
if(ch == '\'')
{
formatString.append('\'');
formatString.append('\'');
}
else if(formatString.charAt(formatString.length() - 1) == '\'')
{
formatString.setCharAt(formatString.length() - 1, ch);
}
else
{
formatString.append('\'');
formatString.append(ch);
}
formatString.append('\'');
shield = false;
continue;
}
if((quot || square)
&& ch != '"' && ch != ']')
{
cut.append(ch);
continue;
}
ch = Character.toLowerCase(ch);
switch(ch)
{
case '"':
if(quot)
{
formatString.append("'" + cut.toString() + "'");
}
else
{
cut = new StringBuffer();
}
quot = ! quot;
break;
case '[':
cut = new StringBuffer();
square = true;
break;
case ']':
if(! square)
{
malformed = true;
break;
}
String squared = cut.toString();
if(squared.length() == 1)
{
char tl = Character.toLowerCase(squared.charAt(0));
if(tl == 'h'
|| tl == 'm'
|| tl == 's')
{
if(type == TNUM)
{
malformed = true;
break;
}
type = TDATE;
if(tl == 'h')
{
formatString.append('H');
}
else
{
formatString.append(tl);
}
niy = true;
}
}
else
{
if(section == ZERO)
{
colorZERO = squared.toUpperCase();
}
else if(section == NEG)
{
colorNEG = squared.toUpperCase();
}
else
{
colorPOSSNG = squared.toUpperCase();
}
}
square = false;
break;
// Datetimes
case 'm':
if(type == TNUM)
{
malformed = true;
break;
}
type = TDATE;
if(low.charAt(i + 1) == 'm')
{
if(low.charAt(i + 2) == 'm')
{
if(low.charAt(i + 3) == 'm')
{
formatString.append("MMMM");
i++;
}
else
{
formatString.append("MMM");
}
i++;
}
else
{
formatString.append(hour ? "mm" : "MM");
}
i++;
}
else
{
formatString.append(hour ? "m" : "M");
}
hour = false;
break;
case 'h':
if(type == TNUM)
{
malformed = true;
break;
}
type = TDATE;
if(low.charAt(i + 1) == 'h')
{
formatString.append(ampm ? "hh" : "HH");
i++;
}
else
{
formatString.append(ampm ? "hh" : "HH");
}
hour = true;
break;
case 'd':
if(type == TNUM)
{
malformed = true;
break;
}
type = TDATE;
if(low.charAt(i + 1) == 'd')
{
if(low.charAt(i + 2) == 'd')
{
if(low.charAt(i + 3) == 'd')
{
// FIXME: Will this correctly output full day-in-week name?
formatString.append("EEEE");
i++;
}
else
{
formatString.append("EEE");
}
i++;
}
else
{
formatString.append("dd");
}
i++;
}
else
{
formatString.append("d");
}
hour = false;
break;
case 'y':
if(type == TNUM)
{
malformed = true;
break;
}
type = TDATE;
if(low.charAt(i + 1) == 'y')
{
if(low.charAt(i + 2) == 'y')
{
if(low.charAt(i + 3) == 'y')
{
formatString.append("yyyy");
i++;
}
else
{
malformed = true;
formatString.append("yy");
}
i++;
}
else
{
formatString.append("yy");
}
i++;
}
else
{
malformed = true;
}
hour = false;
break;
case 'a':
if(type == TNUM)
{
malformed = true;
break;
}
if(format.charAt(i + 1) == '/'
&& low.charAt(i + 2) == 'p')
{
formatString.append("a");
i += 2;
}
else if(low.charAt(i + 1) == 'm'
&& format.charAt(i + 2) == '/'
&& low.charAt(i + 3) == 'p'
&& low.charAt(i + 4) == 'm')
{
formatString.append("a");
i += 4;
}
else
{
malformed = true;
}
hour = false;
break;
// Numeric
case '#':
if(type == TDATE)
{
malformed = true;
break;
}
type = TNUM;
formatString.append('#');
break;
case '?':
case '0':
if(type == TDATE)
{
malformed = true;
break;
}
type = TNUM;
formatString.append('0');
break;
case 'e':
if(type == TDATE)
{
malformed = true;
break;
}
type = TNUM;
if(format.charAt(i + 1) == '+'
|| format.charAt(i + 1) == '-')
{
formatString.append('E');
i++;
}
else
{
malformed = true;
}
break;
case '.':
case ',':
case '-':
case '%':
case ':':
formatString.append(ch);
break;
case '_':
break;
case ';':
if(type == TUNK)
{
type = TNUM;
}
end = true;
break;
case '/':
if(type == TNUM)
{
niy = true;
int j = i;
while(format.charAt(--j) == '?')
{
formatString.deleteCharAt(formatString.length() - 1);
}
while(format.charAt(i + 1) == '?'
|| Character.isDigit(format.charAt(i + 1)))
{
i++;
}
}
else
{
formatString.append(ch);
}
break;
case '\\':
shield = true;
break;
default:
formatString.append(ch);
break;
}
if(end == true)
{
if(type == TDATE)
{
try
{
df = new SimpleDateFormat(formatString.toString());
}
catch(IllegalArgumentException iae)
{
df = new SimpleDateFormat();
}
isDate = true;
return;
}
if(type == TNUM)
{
NumberFormat nf;
try
{
nf = new DecimalFormat(formatString.toString());
}
catch(IllegalArgumentException iae)
{
nf = new DecimalFormat();
}
formatString = new StringBuffer();
if(section == POSSNG)
{
nfPOSSNG = nf;
section = NEG;
}
else if(section == NEG)
{
nfNEG = nf;
section = ZERO;
}
else
{
nfZERO = nf;
return;
}
}
end = false;
}
}
}
// Will lie when am/pm is quoted in format string. Let it lie.
private boolean isAmPm(String format)
{
String f = format.toLowerCase();
return f.contains("am/pm")
|| f.contains("a/p");
}
public String format(Date date)
{
if(isDate)
{
return df.format(date);
}
else
{
return format(HSSFDateUtil.getExcelDate(date));
}
}
public String format(double number)
{
return format(number, false);
}
public String format(double number, boolean windowing)
{
if(isDate)
{
return df.format(HSSFDateUtil.getJavaDate(number, windowing));
}
else
{
if(number < 0 && nfNEG != null)
{
return nfNEG.format(number);
}
else if(number == 0 && nfZERO != null)
{
return nfZERO.format(number);
}
else
{
return nfPOSSNG.format(number);
}
}
}
public boolean isDate()
{
return isDate;
}
/* WRONG! */
/* public static boolean isDateFormat(String df)
{
if(df.indexOf('D') >= 0
|| df.indexOf('M') >= 0
|| df.indexOf('Y') >= 0
|| df.indexOf('H') >= 0
|| df.indexOf('S') >= 0
|| df.indexOf('d') >= 0
|| df.indexOf('m') >= 0
|| df.indexOf('y') >= 0
|| df.indexOf('h') >= 0
|| df.indexOf('s') >= 0)
return true;
return false;
} */
public boolean isMalformed()
{
return malformed;
}
public boolean isNiy()
{
return niy;
}
public String getColor(double number)
{
if(number == 0 && nfZERO != null)
{
return Util.notNull(colorZERO);
}
if(number < 0 && nfNEG != null)
{
return Util.notNull(colorNEG);
}
return Util.notNull(colorPOSSNG);
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
