Mailing-List: contact user-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <user@poi.apache.org>
Received-SPF: pass (herse.apache.org: local policy)
From: Ilya Kasnacheev <ilyak@office.uw.ru>
Organization: UW
To: user@poi.apache.org
Subject: HSSF Excel date/number formatter
Date: Mon, 23 Jul 2007 20:55:34 +0400
User-Agent: KMail/1.6.2
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: Multipart/Mixed;
  boundary="Boundary-00=_G2NpGpYeDCnp9dl"
Message-Id: <200707232055.34605.ilyak@office.uw.ru>

--Boundary-00=_G2NpGpYeDCnp9dl
Content-Type: text/plain;
  charset="koi8-r"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 :)

--Boundary-00=_G2NpGpYeDCnp9dl
Content-Type: text/x-java;
  charset="koi8-r";
  name="ExcelDataFormatter.java"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="ExcelDataFormatter.java"

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);
	}
	
}


--Boundary-00=_G2NpGpYeDCnp9dl
Content-Type: text/plain; charset=us-ascii

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