Re: Negative Zero

[Mirko Raner <mi...@parasoft.com>]

A. Rick Anderson wrote:
> I thought that Excel had the capability of displaying a zero as a 
> negative. Typically by use of parenthesis or the color red. (I must 
> confess, I can't remember how though)
>
> Any idea how you get a zero value to display as negative in HSSF?
The following program will create and read a spreadsheet with an IEEE 
754 negative zero:

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
* The class {@link NegativeZero} demonstrates the use of the IEEE 754 
"negative zero" concept with
* Apache POI HSSF.
*
* @author Mirko Raner
*/
public class NegativeZero {

private final static double NEGATIVE_ZERO = 
Double.longBitsToDouble(0x8000000000000000L);
private final static String SHEET = "sheet"; //$NON-NLS-1$
private final static String FILENAME = "NegativeZero.xls"; //$NON-NLS-1$

/**
* Runs the program.
* @param argument "create" to create the test file, "verify" to verify it
*/
public static void main(String[] argument) throws IOException
{
if (argument.length == 1)
{
if (argument[0].equals("create")) //$NON-NLS-1$
{
create();
}
else if (argument[0].equals("verify")) //$NON-NLS-1$
{
verify();
}
}
else
{
create();
verify();
}
}

private static void create() throws IOException
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(SHEET);
HSSFRow row1 = sheet.createRow(0);
row1.createCell((short)0).setCellValue(NEGATIVE_ZERO);
FileOutputStream xlsFile = new FileOutputStream(FILENAME);
workbook.write(xlsFile);
xlsFile.close();
System.out.println(FILENAME + " created."); //$NON-NLS-1$
}

private static void verify() throws IOException
{
FileInputStream xlsFile = new FileInputStream(FILENAME);
HSSFWorkbook workbook = new HSSFWorkbook(xlsFile, false);
HSSFSheet sheet = workbook.getSheet(SHEET);
HSSFRow row = sheet.getRow(0);
HSSFCell cell = row.getCell((short)0);
double value = cell.getNumericCellValue();
System.out.println("Value: " + value); //$NON-NLS-1$
}
}

If you run the program, you'll see that the negative zero gets properly 
stored and read back. Displaying that negative zero as a negative value 
in your spreadsheet application is s whole different issue. When I open 
the resulting spreadsheet with OOo 2.0.4 the value is displayed just as 
a regular "0". I changed the cell format to one that uses red for 
negative numbers but at least OOo seems to ignore the difference between 
0 and -0 and the negative zero still gets displayed in black. Maybe 
Excel behaves differently here. If you only need to make sure that -0 is 
stored properly and distinguishable from +0 in the .xls file then it 
looks like there is no problem. As far as displaying it as a negative 
number in the spreadsheet application you're probably at the mercy of 
whatever program you use for viewing the spreadsheet. There is probably 
little to nothing that POI can do for you in that case.

HTH,

Mirko


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/