
/* ====================================================================
 * The Apache Software License, Version 1.1
 *
 * Copyright (c) 2003 The Apache Software Foundation.  All rights
 * reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 *
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 *
 * 2. Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in
 *    the documentation and/or other materials provided with the
 *    distribution.
 *
 * 3. The end-user documentation included with the redistribution,
 *    if any, must include the following acknowledgment:
 *       "This product includes software developed by the
 *        Apache Software Foundation (http://www.apache.org/)."
 *    Alternately, this acknowledgment may appear in the software itself,
 *    if and wherever such third-party acknowledgments normally appear.
 *
 * 4. The names "Apache" and "Apache Software Foundation" and
 *    "Apache POI" must not be used to endorse or promote products
 *    derived from this software without prior written permission. For
 *    written permission, please contact apache@apache.org.
 *
 * 5. Products derived from this software may not be called "Apache",
 *    "Apache POI", nor may "Apache" appear in their name, without
 *    prior written permission of the Apache Software Foundation.
 *
 * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
 * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
 * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
 * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
 * SUCH DAMAGE.
 * ====================================================================
 *
 * This software consists of voluntary contributions made by many
 * individuals on behalf of the Apache Software Foundation.  For more
 * information on the Apache Software Foundation, please see
 * <http://www.apache.org/>.
 */

package org.apache.poi.hssf.usermodel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

import junit.framework.TestCase;

import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.BackupRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.aggregates.ValueRecordsAggregate;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * Class to test Workbook functionality
 *
 * @author Andrew C. Oliver
 * @author Greg Merrill
 * @author Siggi Cherem
 */

public class TestWorkbook
    extends TestCase
{
    private static final String LAST_NAME_KEY        = "lastName";
    private static final String FIRST_NAME_KEY       = "firstName";
    private static final String SSN_KEY              = "ssn";
    private static final String REPLACE_ME           = "replaceMe";
    private static final String REPLACED             = "replaced";
    private static final String DO_NOT_REPLACE       = "doNotReplace";
    private static final String EMPLOYEE_INFORMATION = "Employee Info";
    private static final String LAST_NAME_VALUE      = "Bush";
    private static final String FIRST_NAME_VALUE     = "George";
    private static final String SSN_VALUE            = "555555555";
    private SanityChecker sanityChecker = new SanityChecker();

    /**
     * Constructor TestWorkbook
     *
     * @param name
     */

    public TestWorkbook(String name)
    {
        super(name);
    }

    /**
     * TEST NAME:  Test Write Sheet Simple <P>
     * OBJECTIVE:  Test that HSSF can create a simple spreadsheet with numeric and string values.<P>
     * SUCCESS:    HSSF creates a sheet.  Filesize matches a known good.  HSSFSheet objects
     *             Last row, first row is tested against the correct values (99,0).<P>
     * FAILURE:    HSSF does not create a sheet or excepts.  Filesize does not match the known good.
     *             HSSFSheet last row or first row is incorrect.             <P>
     *
     */

    public void testWriteSheetSimple()
        throws IOException
    {
        File             file = File.createTempFile("testWriteSheetSimple",
                                                    ".xls");
        FileOutputStream out  = new FileOutputStream(file);
        HSSFWorkbook     wb   = new HSSFWorkbook();
        HSSFSheet        s    = wb.createSheet();
        HSSFRow          r    = null;
        HSSFCell         c    = null;

        for (short rownum = ( short ) 0; rownum < 100; rownum++)
        {
            r = s.createRow(rownum);

            // r.setRowNum(( short ) rownum);
            for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
            {
                c = r.createCell(cellnum);
                c.setCellValue(rownum * 10000 + cellnum
                               + ((( double ) rownum / 1000)
                                  + (( double ) cellnum / 10000)));
                c = r.createCell(( short ) (cellnum + 1));
                c.setCellValue("TEST");
            }
        }
        wb.write(out);
        out.close();
        sanityChecker.checkHSSFWorkbook(wb);
        assertEquals("LAST ROW == 99", 99, s.getLastRowNum());
        assertEquals("FIRST ROW == 0", 0, s.getFirstRowNum());

        // assert((s.getLastRowNum() == 99));
    }

    /**
     * TEST NAME:  Test Write/Modify Sheet Simple <P>
     * OBJECTIVE:  Test that HSSF can create a simple spreadsheet with numeric and string values,
     *             remove some rows, yet still have a valid file/data.<P>
     * SUCCESS:    HSSF creates a sheet.  Filesize matches a known good.  HSSFSheet objects
     *             Last row, first row is tested against the correct values (74,25).<P>
     * FAILURE:    HSSF does not create a sheet or excepts.  Filesize does not match the known good.
     *             HSSFSheet last row or first row is incorrect.             <P>
     *
     */

    public void testWriteModifySheetSimple()
        throws IOException
    {
        File             file = File.createTempFile("testWriteSheetSimple",
                                                    ".xls");
        FileOutputStream out  = new FileOutputStream(file);
        HSSFWorkbook     wb   = new HSSFWorkbook();
        HSSFSheet        s    = wb.createSheet();
        HSSFRow          r    = null;
        HSSFCell         c    = null;

        for (short rownum = ( short ) 0; rownum < 100; rownum++)
        {
            r = s.createRow(rownum);

            // r.setRowNum(( short ) rownum);
            for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
            {
                c = r.createCell(cellnum);
                c.setCellValue(rownum * 10000 + cellnum
                               + ((( double ) rownum / 1000)
                                  + (( double ) cellnum / 10000)));
                c = r.createCell(( short ) (cellnum + 1));
                c.setCellValue("TEST");
            }
        }
        for (short rownum = ( short ) 0; rownum < 25; rownum++)
        {
            r = s.getRow(rownum);
            s.removeRow(r);
        }
        for (short rownum = ( short ) 75; rownum < 100; rownum++)
        {
            r = s.getRow(rownum);
            s.removeRow(r);
        }
        wb.write(out);
        out.close();

        sanityChecker.checkHSSFWorkbook(wb);
        assertEquals("LAST ROW == 74", 74, s.getLastRowNum());
        assertEquals("FIRST ROW == 25", 25, s.getFirstRowNum());
    }

    /**
     * TEST NAME:  Test Read Simple <P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet (Simple.xls).<P>
     * SUCCESS:    HSSF reads the sheet.  Matches values in their particular positions.<P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF cannot identify values
     *             in the sheet in their known positions.<P>
     *
     */

    public void testReadSimple()
        throws IOException
    {
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/Simple.xls";
        FileInputStream stream   = new FileInputStream(filename);
        POIFSFileSystem fs       = new POIFSFileSystem(stream);
        HSSFWorkbook    workbook = new HSSFWorkbook(fs);
        HSSFSheet       sheet    = workbook.getSheetAt(0);

        assertEquals(REPLACE_ME,
                     sheet.getRow(( short ) 0).getCell(( short ) 0)
                         .getStringCellValue());
        stream.close();
    }

    /**
     * TEST NAME:  Test Read Simple w/ Data Format<P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet (SimpleWithDataFormat.xls).<P>
     * SUCCESS:    HSSF reads the sheet.  Matches values in their particular positions and format is correct<P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF cannot identify values
     *             in the sheet in their known positions.<P>
     *
     */

    public void testReadSimpleWithDataFormat()
        throws IOException
    {
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/SimpleWithDataFormat.xls";
        FileInputStream stream   = new FileInputStream(filename);
        POIFSFileSystem fs       = new POIFSFileSystem(stream);
        HSSFWorkbook    workbook = new HSSFWorkbook(fs);
        HSSFSheet       sheet    = workbook.getSheetAt(0);
        HSSFDataFormat  format   = workbook.createDataFormat();
	HSSFCell	cell	 = 
                     sheet.getRow(( short ) 0).getCell(( short ) 0);

        assertEquals(1.25,cell.getNumericCellValue(), 1e-10);

	assertEquals(format.getFormat(cell.getCellStyle().getDataFormat()), "0.0");
        stream.close();
    }

/**
     * TEST NAME:  Test Read/Write Simple w/ Data Format<P>
     * OBJECTIVE:  Test that HSSF can write a sheet with custom data formats and then read it and get the proper formats.<P>
     * SUCCESS:    HSSF reads the sheet.  Matches values in their particular positions and format is correct<P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF cannot identify values
     *             in the sheet in their known positions.<P>
     *
     */

    public void testWriteDataFormat()
        throws IOException
    {
	File             file = File.createTempFile("testWriteDataFormat",
                                                    ".xls");
        FileOutputStream out  = new FileOutputStream(file);
        HSSFWorkbook     wb   = new HSSFWorkbook();
        HSSFSheet        s    = wb.createSheet();
        HSSFRow          r    = null;
        HSSFCell         c    = null;
	HSSFDataFormat format = wb.createDataFormat();
	HSSFCellStyle    cs   = wb.createCellStyle();
	
	short df = format.getFormat("0.0");
	cs.setDataFormat(df);
	
	r = s.createRow((short)0);
	c = r.createCell((short)0);
	c.setCellStyle(cs);
	c.setCellValue(1.25);

        wb.write(out);
        out.close();

        FileInputStream stream   = new FileInputStream(file);
        POIFSFileSystem fs       = new POIFSFileSystem(stream);
        HSSFWorkbook    workbook = new HSSFWorkbook(fs);
        HSSFSheet       sheet    = workbook.getSheetAt(0);
	HSSFCell	cell	 = 
                     sheet.getRow(( short ) 0).getCell(( short ) 0);
	format = workbook.createDataFormat();

        assertEquals(1.25,cell.getNumericCellValue(), 1e-10);

	assertEquals(format.getFormat(df), "0.0");

	assertEquals(format, workbook.createDataFormat());
	
        stream.close();
    }

    /**
     * TEST NAME:  Test Read Employee Simple <P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet (Employee.xls).<P>
     * SUCCESS:    HSSF reads the sheet.  Matches values in their particular positions.<P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF cannot identify values
     *             in the sheet in their known positions.<P>
     *
     */

    public void testReadEmployeeSimple()
        throws IOException
    {
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/Employee.xls";
        FileInputStream stream   = new FileInputStream(filename);
        POIFSFileSystem fs       = new POIFSFileSystem(stream);
        HSSFWorkbook    workbook = new HSSFWorkbook(fs);
        HSSFSheet       sheet    = workbook.getSheetAt(0);

        assertEquals(EMPLOYEE_INFORMATION,
                     sheet.getRow(1).getCell(( short ) 1)
                         .getStringCellValue());
        assertEquals(LAST_NAME_KEY,
                     sheet.getRow(3).getCell(( short ) 2)
                         .getStringCellValue());
        assertEquals(FIRST_NAME_KEY,
                     sheet.getRow(4).getCell(( short ) 2)
                         .getStringCellValue());
        assertEquals(SSN_KEY,
                     sheet.getRow(5).getCell(( short ) 2)
                         .getStringCellValue());
        stream.close();
    }

    /**
     * TEST NAME:  Test Modify Sheet Simple <P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet with a string value and replace
     *             it with another string value.<P>
     * SUCCESS:    HSSF reads a sheet.  HSSF replaces the cell value with another cell value. HSSF
     *             writes the sheet out to another file.  HSSF reads the result and ensures the value
     *             has been properly replaced.    <P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF does not write the sheet or excepts.
     *             HSSF does not re-read the sheet or excepts.  Upon re-reading the sheet the value
     *             is incorrect or has not been replaced. <P>
     *
     */

    public void testModifySimple()
        throws IOException
    {
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/Simple.xls";
        FileInputStream instream = new FileInputStream(filename);
        POIFSFileSystem fsin     = new POIFSFileSystem(instream);
        HSSFWorkbook    workbook = new HSSFWorkbook(fsin);
        HSSFSheet       sheet    = workbook.getSheetAt(0);
        HSSFCell        cell     =
            sheet.getRow(( short ) 0).getCell(( short ) 0);

        cell.setCellValue(REPLACED);
        File             destination = File.createTempFile("SimpleResult",
                                           ".xls");
        FileOutputStream outstream   = new FileOutputStream(destination);

        workbook.write(outstream);
        instream.close();
        outstream.close();
        instream = new FileInputStream(destination);
        workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
        sheet    = workbook.getSheetAt(0);
        cell     = sheet.getRow(( short ) 0).getCell(( short ) 0);
        assertEquals(REPLACED, cell.getStringCellValue());
        instream.close();
    }

    /**
     * TEST NAME:  Test Modify Sheet Simple With Skipped cells<P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet with string values and replace
     *             them with other string values while not replacing other cells.<P>
     * SUCCESS:    HSSF reads a sheet.  HSSF replaces the cell value with another cell value. HSSF
     *             writes the sheet out to another file.  HSSF reads the result and ensures the value
     *             has been properly replaced and unreplaced values are still unreplaced.    <P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF does not write the sheet or excepts.
     *             HSSF does not re-read the sheet or excepts.  Upon re-reading the sheet the value
     *             is incorrect or has not been replaced or the incorrect cell has its value replaced
     *             or is incorrect. <P>
     *
     */

    public void testModifySimpleWithSkip()
        throws IOException
    {
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/SimpleWithSkip.xls";
        FileInputStream instream = new FileInputStream(filename);
        POIFSFileSystem fsin     = new POIFSFileSystem(instream);
        HSSFWorkbook    workbook = new HSSFWorkbook(fsin);
        HSSFSheet       sheet    = workbook.getSheetAt(0);
        HSSFCell        cell     =
            sheet.getRow(( short ) 0).getCell(( short ) 1);

        cell.setCellValue(REPLACED);
        cell = sheet.getRow(( short ) 1).getCell(( short ) 0);
        cell.setCellValue(REPLACED);
        File             destination =
            File.createTempFile("SimpleWithSkipResult", ".xls");
        FileOutputStream outstream   = new FileOutputStream(destination);

        workbook.write(outstream);
        instream.close();
        outstream.close();
        instream = new FileInputStream(destination);
        workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
        sheet    = workbook.getSheetAt(0);
        cell     = sheet.getRow(( short ) 0).getCell(( short ) 1);
        assertEquals(REPLACED, cell.getStringCellValue());
        cell = sheet.getRow(( short ) 0).getCell(( short ) 0);
        assertEquals(DO_NOT_REPLACE, cell.getStringCellValue());
        cell = sheet.getRow(( short ) 1).getCell(( short ) 0);
        assertEquals(REPLACED, cell.getStringCellValue());
        cell = sheet.getRow(( short ) 1).getCell(( short ) 1);
        assertEquals(DO_NOT_REPLACE, cell.getStringCellValue());
        instream.close();
    }

    /**
     * TEST NAME:  Test Modify Sheet With Styling<P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet with string values and replace
     *             them with other string values despite any styling.  In this release of HSSF styling will
     *             probably be lost and is NOT tested.<P>
     * SUCCESS:    HSSF reads a sheet.  HSSF replaces the cell values with other cell values. HSSF
     *             writes the sheet out to another file.  HSSF reads the result and ensures the value
     *             has been properly replaced.    <P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF does not write the sheet or excepts.
     *             HSSF does not re-read the sheet or excepts.  Upon re-reading the sheet the value
     *             is incorrect or has not been replaced. <P>
     *
     */

    public void testModifySimpleWithStyling()
        throws IOException
    {
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/SimpleWithStyling.xls";
        FileInputStream instream = new FileInputStream(filename);
        POIFSFileSystem fsin     = new POIFSFileSystem(instream);
        HSSFWorkbook    workbook = new HSSFWorkbook(fsin);
        HSSFSheet       sheet    = workbook.getSheetAt(0);

        for (int k = 0; k < 4; k++)
        {
            HSSFCell cell = sheet.getRow(( short ) k).getCell(( short ) 0);

            cell.setCellValue(REPLACED);
        }
        File             destination =
            File.createTempFile("SimpleWithStylingResult", ".xls");
        FileOutputStream outstream   = new FileOutputStream(destination);

        workbook.write(outstream);
        instream.close();
        outstream.close();
        instream = new FileInputStream(destination);
        workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
        sheet    = workbook.getSheetAt(0);
        for (int k = 0; k < 4; k++)
        {
            HSSFCell cell = sheet.getRow(( short ) k).getCell(( short ) 0);

            assertEquals(REPLACED, cell.getStringCellValue());
        }
        instream.close();
    }

    /**
     * TEST NAME:  Test Modify Employee Sheet<P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet with string values and replace
     *             them with other string values despite any styling.  In this release of HSSF styling will
     *             probably be lost and is NOT tested.<P>
     * SUCCESS:    HSSF reads a sheet.  HSSF replaces the cell values with other cell values. HSSF
     *             writes the sheet out to another file.  HSSF reads the result and ensures the value
     *             has been properly replaced.    <P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF does not write the sheet or excepts.
     *             HSSF does not re-read the sheet or excepts.  Upon re-reading the sheet the value
     *             is incorrect or has not been replaced. <P>
     *
     */

    public void testModifyEmployee()
        throws IOException
    {
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/Employee.xls";
        FileInputStream instream = new FileInputStream(filename);
        POIFSFileSystem fsin     = new POIFSFileSystem(instream);
        HSSFWorkbook    workbook = new HSSFWorkbook(fsin);
        HSSFSheet       sheet    = workbook.getSheetAt(0);
        HSSFCell        cell     =
            sheet.getRow(( short ) 3).getCell(( short ) 2);

        cell.setCellValue(LAST_NAME_VALUE);
        cell = sheet.getRow(( short ) 4).getCell(( short ) 2);
        cell.setCellValue(FIRST_NAME_VALUE);
        cell = sheet.getRow(( short ) 5).getCell(( short ) 2);
        cell.setCellValue(SSN_VALUE);
        File             destination = File.createTempFile("EmployeeResult",
                                           ".xls");
        FileOutputStream outstream   = new FileOutputStream(destination);

        workbook.write(outstream);
        instream.close();
        outstream.close();
        instream = new FileInputStream(destination);
        workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
        sheet    = workbook.getSheetAt(0);
        assertEquals(EMPLOYEE_INFORMATION,
                     sheet.getRow(1).getCell(( short ) 1)
                         .getStringCellValue());
        assertEquals(LAST_NAME_VALUE,
                     sheet.getRow(3).getCell(( short ) 2)
                         .getStringCellValue());
        assertEquals(FIRST_NAME_VALUE,
                     sheet.getRow(4).getCell(( short ) 2)
                         .getStringCellValue());
        assertEquals(SSN_VALUE,
                     sheet.getRow(5).getCell(( short ) 2)
                         .getStringCellValue());
        instream.close();
    }

    /**
     * TEST NAME:  Test Read Sheet with an RK number<P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet with and RKRecord and correctly
     *             identify the cell as numeric and convert it to a NumberRecord.  <P>
     * SUCCESS:    HSSF reads a sheet.  HSSF returns that the cell is a numeric type cell.    <P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF incorrectly indentifies the cell<P>
     *
     */

    public void testReadSheetWithRK()
        throws IOException
    {
        String filename = System.getProperty("HSSF.testdata.path");

        filename = filename + "/rk.xls";

        // a.xls has a value on position (0,0)
        FileInputStream in = new FileInputStream(filename);
        POIFSFileSystem fs = new POIFSFileSystem(in);
        HSSFWorkbook    h  = new HSSFWorkbook(fs);
        HSSFSheet       s  = h.getSheetAt(0);
        HSSFRow         r  = s.getRow(0);
        HSSFCell        c  = r.getCell(( short ) 0);
        int             a  = c.getCellType();

        assertEquals(a, c.CELL_TYPE_NUMERIC);
    }

    /**
     * TEST NAME:  Test Write/Modify Sheet Simple <P>
     * OBJECTIVE:  Test that HSSF can create a simple spreadsheet with numeric and string values,
     *             remove some rows, yet still have a valid file/data.<P>
     * SUCCESS:    HSSF creates a sheet.  Filesize matches a known good.  HSSFSheet objects
     *             Last row, first row is tested against the correct values (74,25).<P>
     * FAILURE:    HSSF does not create a sheet or excepts.  Filesize does not match the known good.
     *             HSSFSheet last row or first row is incorrect.             <P>
     *
     */

    public void testWriteModifySheetMerged()
        throws IOException
    {
        File             file = File.createTempFile("testWriteSheetMerged",
                                                    ".xls");
        FileOutputStream out  = new FileOutputStream(file);
        FileInputStream  in   = null;
        HSSFWorkbook     wb   = new HSSFWorkbook();
        HSSFSheet        s    = wb.createSheet();
        HSSFRow          r    = null;
        HSSFCell         c    = null;

        for (short rownum = ( short ) 0; rownum < 100; rownum++)
        {
            r = s.createRow(rownum);

            // r.setRowNum(( short ) rownum);
            for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
            {
                c = r.createCell(cellnum);
                c.setCellValue(rownum * 10000 + cellnum
                               + ((( double ) rownum / 1000)
                                  + (( double ) cellnum / 10000)));
                c = r.createCell(( short ) (cellnum + 1));
                c.setCellValue("TEST");
            }
        }
        s.addMergedRegion(new Region(( short ) 0, ( short ) 0, ( short ) 10,
                                     ( short ) 10));
        s.addMergedRegion(new Region(( short ) 30, ( short ) 5, ( short ) 40,
                                     ( short ) 15));
        wb.write(out);
        out.close();
        sanityChecker.checkHSSFWorkbook(wb);
        in = new FileInputStream(file);
        wb = new HSSFWorkbook(new POIFSFileSystem(in));
        s  = wb.getSheetAt(0);
        Region r1 = s.getMergedRegionAt(0);
        Region r2 = s.getMergedRegionAt(1);

        in.close();

        // System.out.println(file.length());
        // assertEquals("FILE LENGTH == 87552",file.length(), 87552);
        // System.out.println(s.getLastRowNum());
        assertEquals("REGION1 = 0,0,10,10", 0,
                     new Region(( short ) 0, ( short ) 0, ( short ) 10,
                                ( short ) 10).compareTo(r1));
        assertEquals("REGION2 == 30,5,40,15", 0,
                     new Region(( short ) 30, ( short ) 5, ( short ) 40,
                                ( short ) 15).compareTo(r2));
    }

    /**
     * Test the backup field gets set as expected.
     */

    public void testBackupRecord()
        throws Exception
    {
        HSSFWorkbook wb       = new HSSFWorkbook();
        wb.createSheet();
        Workbook     workbook = wb.getWorkbook();
        BackupRecord record   = workbook.getBackupRecord();

        assertEquals(0, record.getBackup());
        wb.setBackupFlag(true);
        assertEquals(1, record.getBackup());
    }

    /**
     * This tests is for bug [ #506658 ] Repeating output.
     *
     * We need to make sure only one LabelSSTRecord is produced.
     */

    public void testRepeatingBug()
        throws Exception
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet    sheet    = workbook.createSheet("Design Variants");
        HSSFRow      row      = sheet.createRow(( short ) 2);
        HSSFCell     cell     = row.createCell(( short ) 1);

        cell.setCellValue("Class");
        cell = row.createCell(( short ) 2);

        // workbook.write(new FileOutputStream("/a2.xls"));
        ValueRecordsAggregate valueAggregate =
            ( ValueRecordsAggregate ) sheet.getSheet()
                .findFirstRecordBySid(ValueRecordsAggregate.sid);
        int                   sstRecords     = 0;
        Iterator              iterator       = valueAggregate.getIterator();

        while (iterator.hasNext())
        {
            if ((( Record ) iterator.next()).getSid() == LabelSSTRecord.sid)
            {
                sstRecords++;
            }
        }
        assertEquals(1, sstRecords);
    }


    public void testManyRows()
        throws Exception
    {
        String testName = "TestManyRows";
        File file = File.createTempFile(testName, ".xls");
        FileOutputStream out  = new FileOutputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = null;
        HSSFCell cell = null;
        int i, j;
        for ( i = 0, j = 32771; j > 0; i++, j-- )
        {
            row = sheet.createRow(i);
            cell = row.createCell((short) 0);
            cell.setCellValue(i);
        }
        workbook.write(out);
        out.close();
        sanityChecker.checkHSSFWorkbook(workbook);
        assertEquals("LAST ROW == 32770", 32770, sheet.getLastRowNum());
        double lastVal = cell.getNumericCellValue();

        FileInputStream in      = new FileInputStream(file);
        POIFSFileSystem fs       = new POIFSFileSystem(in);
        HSSFWorkbook    wb = new HSSFWorkbook(fs);
        HSSFSheet       s    = wb.getSheetAt(0);
        row = s.getRow(32770);
        cell = row.getCell(( short ) 0);
        assertEquals("Value from last row == 32770", lastVal, cell.getNumericCellValue(), 0);
        assertEquals("LAST ROW == 32770", 32770, s.getLastRowNum());
        in.close();
        file.deleteOnExit();
    }
    
    /**
     * Generate a file to visually/programmatically verify repeating rows and cols made it
     */
    public void testRepeatingColsRows() throws IOException
    {
		HSSFWorkbook workbook = new HSSFWorkbook();        
		HSSFSheet sheet = workbook.createSheet("Test Print Titles");                
		String sheetName = workbook.getSheetName(0);
	     
	    HSSFRow row = sheet.createRow(0);
	    
	    HSSFCell cell = row.createCell((short)1);
	    cell.setCellValue("hi");
	     
	     
		workbook.setRepeatingRowsAndColumns(0, 0, 1, 0, 0); 
	     
		File file = File.createTempFile("testPrintTitles",".xls");        
	     
		FileOutputStream fileOut = new FileOutputStream(file);
		workbook.write(fileOut);
		fileOut.close();
	     
		assertTrue("file exists",file.exists());
	     
    	
    }
  
    
    public static void main(String [] ignored_args)
    {
        String filename = System.getProperty("HSSF.testdata.path");

        // assume this is relative to basedir
        if (filename == null)
        {
            System.setProperty(
                "HSSF.testdata.path",
                "src/testcases/org/apache/poi/hssf/data");
        }
        System.out
            .println("Testing org.apache.poi.hssf.usermodel.HSSFWorkbook");
        junit.textui.TestRunner.run(TestWorkbook.class);
    }
}