Pompeii - 2007-07-12

I am generating an excel document through jxl. I am having problem when I try to adjust the column width for each column in the sheet.

My intention is to arrange all columns in the sheet in a way that the longest literal in each column has complete visibility, after the sheet has been populated with data.

What I observe is this that the columns aren't arranged correctly i.e. the column widths are somewhat closer to what I expect them to, but they aren't uniform, some of the column width are less then what they should be & some a little more.

I have tried a number of other ways but I couldn't fix it. I think something is happening behind the scenes, which I am unable to understand like something related to font size, bold weight, or font faces or probably something even more sinister. Besides, I don't find the 'character-width-multiplied-width-256-idea' to set cell view/column view size much helpful, because it suggests that the column width would be adjusted to proper size, if characters in that column are known, regardless of the font size/face/bold weight.

I am attaching my code (oops I couldn't find file attachments facility in the form, so I will be appending my code at the end).

Please, lemme know if I am making some mistake or doing it wrong altogether. Please, suggest me if you find anything wrong in my code. Also pardon my English, if you could :).

-------------------------------------------------------------------------------------------
CODE Starts
-------------------------------------------------------------------------------------------
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Locale;
import java.util.Map;

import jxl.Cell;
import jxl.CellView;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
* This class generates XLS files & CSV files for given data. Currently it
* generates CSV files for XLS file. It accepts data in a definite format.
*/
public class WorksheetGenerator
{

    /**
     * Locale of the Worksheet
     */
    private Locale    worksheetLocale    = null;

    private String    encoding        = null;

    /**
     * Parameterless Constructor
     */
    public WorksheetGenerator()
    {
        this( WorksheetGenerator.WSLocale.En, WorksheetGenerator.WSLocale.US,
                WorksheetGenerator.WSLocale.UTF_8 );
    }

    /**
     * Construct an object of Worksheet with given locale.
     *
     * @param p_locale
     *            Locale of the generated Worksheet.
     * @param p_encoding
     *            Encoding of the generated Worksheet.
     */
    public WorksheetGenerator( Locale p_locale, String p_encoding )
    {
        this.worksheetLocale = p_locale;
        this.encoding = p_encoding;
    }

    /**
     * Construct an object of Worksheet with given locale information
     *
     * @param p_language
     *            Language of the Worksheet locale.
     * @param p_country
     *            Countries of the Worksheet locale.
     * @param p_encoding
     *            Encoding of the Worksheet.
     */
    public WorksheetGenerator( String p_language, String p_country,
            String p_encoding )
    {
        this( new Locale( p_language, p_country ), p_encoding );
    }

    /**
     * This method is responsible for creating an XLS file.
     *
     * @param p_directory
     *            Directory to create the file in.
     * @param p_fileName
     *            File will be created with this name.
     * @param p_data
     *            Data for creating file.
     * @param p_xAxis
     *            Values on xAxis for file.
     * @param p_yAxis
     *            Values on yAxis for file.
     * @param p_zAxis
     *            Values on zAxis (against x-y pair) for file.
     *
     * @return Fully qualified name of generated file.
     *
     * @throws Exception
     *             Propagate any exceptions to the caller.
     */
    public String createXLSFile( String p_directory, String p_fileName,
            Hashtable [] p_data, String p_xAxis, String p_yAxis, String p_zAxis )
            throws Exception
    {
        WritableWorkbook book;

        WorkbookSettings bookSettings = new WorkbookSettings();
        bookSettings.setLocale( worksheetLocale );
        bookSettings.setEncoding( encoding );

        File file = new File( p_directory + "/" + p_fileName + ".xls" );

        book = Workbook.createWorkbook( file, bookSettings );

        WritableSheet sheet1 = book.createSheet( "Report Sheet", 0 );

        // Modify the colour palette to bright red for the lime colour
        book.setColourRGB( Colour.LIME, 0xff, 0, 0 );

        WritableFont headingFont = new WritableFont( WritableFont.TIMES, 12,
                WritableFont.BOLD );

        WritableFont textFont = new WritableFont( WritableFont.TIMES, 12 );

        WritableCellFormat headingFormat = new WritableCellFormat( headingFont );
        headingFormat.setBorder( Border.ALL, BorderLineStyle.MEDIUM );

        WritableCellFormat textFormat = new WritableCellFormat( textFont );
        textFormat.setAlignment( Alignment.RIGHT );

        WritableCellFormat decimalFormat = new WritableCellFormat( textFont,
                new NumberFormat( "0.0000" ) );

        DecimalFormat nativeDecimalFormat = new DecimalFormat( "0.0000" );

        int xIndex = 1;
        int yIndex = 1;

        Cell [] xHeaders = sheet1.getRow( 0 );
        Cell [] yHeaders = sheet1.getColumn( 0 );

        Map<Integer, Integer> adjustedColumnWidths = new HashMap<Integer, Integer>();

        for ( Hashtable dataUnit : p_data )
        {
            String xValue = ( String ) dataUnit.get( p_xAxis );
            String yValue = ( String ) dataUnit.get( p_yAxis );
            double zValue = ( ( BigDecimal ) dataUnit.get( p_zAxis ) )
                    .doubleValue();

            int xHeaderOffset = getHeader( xHeaders, xValue );

            if ( xHeaderOffset == -1 )
            {
                Label labelX = new Label( xIndex, 0, xValue, headingFormat );
                sheet1.addCell( labelX );

                adjustedColumnWidths = updateAdjustedColumnWidth(
                        adjustedColumnWidths, xIndex, xValue, true );

                xHeaders = sheet1.getRow( 0 );
                xHeaderOffset = xIndex++ ;

                int yHeaderOffset = getHeader( yHeaders, yValue );

                if ( yHeaderOffset == -1 )
                {
                    Label labelY = new Label( 0, yIndex, yValue, headingFormat );
                    sheet1.addCell( labelY );

                    adjustedColumnWidths = updateAdjustedColumnWidth(
                            adjustedColumnWidths, 0, yValue, true );

                    yHeaders = sheet1.getColumn( 0 );
                    yHeaderOffset = yIndex++ ;

                    Number valueXY = new Number( xHeaderOffset, yHeaderOffset,
                            zValue, decimalFormat );
                    sheet1.addCell( valueXY );

                    adjustedColumnWidths = updateAdjustedColumnWidth(
                            adjustedColumnWidths, xHeaderOffset, String
                                    .valueOf( nativeDecimalFormat
                                            .format( zValue ) ), false );
                }
                else
                {
                    Number valueXY = new Number( xHeaderOffset, yHeaderOffset,
                            zValue, decimalFormat );
                    sheet1.addCell( valueXY );

                    adjustedColumnWidths = updateAdjustedColumnWidth(
                            adjustedColumnWidths, xHeaderOffset, String
                                    .valueOf( nativeDecimalFormat
                                            .format( zValue ) ), false );
                }
            }
            else
            {
                int yHeaderOffset = getHeader( yHeaders, yValue );

                if ( yHeaderOffset == -1 )
                {
                    Label labelY = new Label( 0, yIndex, yValue, headingFormat );
                    sheet1.addCell( labelY );

                    adjustedColumnWidths = updateAdjustedColumnWidth(
                            adjustedColumnWidths, 0, yValue, true );

                    yHeaders = sheet1.getColumn( 0 );
                    yHeaderOffset = yIndex++ ;

                    Number valueXY = new Number( xHeaderOffset, yHeaderOffset,
                            zValue, decimalFormat );
                    sheet1.addCell( valueXY );

                    adjustedColumnWidths = updateAdjustedColumnWidth(
                            adjustedColumnWidths, xHeaderOffset, String
                                    .valueOf( nativeDecimalFormat
                                            .format( zValue ) ), false );
                }
                else
                {
                    Number valueXY = new Number( xHeaderOffset, yHeaderOffset,
                            zValue, decimalFormat );
                    sheet1.addCell( valueXY );

                    adjustedColumnWidths = updateAdjustedColumnWidth(
                            adjustedColumnWidths, xHeaderOffset, String
                                    .valueOf( nativeDecimalFormat
                                            .format( zValue ) ), false );
                }
            }
        }

        // Fill out remaning cells with N/A
        for ( int i = 1; i < yIndex; i++ )
        {
            for ( int j = 1; j < xIndex; j++ )
            {
                String cellContents = sheet1.getCell( j, i ).getContents();

                if ( cellContents == null
                        || ( "" ).equals( cellContents.trim() ) )
                {
                    Label valueXY = new Label( j, i, "N/A", textFormat );
                    sheet1.addCell( valueXY );

                    adjustedColumnWidths = updateAdjustedColumnWidth(
                            adjustedColumnWidths, j, "N/A", false );
                }
            }
        }

        // Apply column width adjustments on sheet.
        Integer [] columnKeys = adjustedColumnWidths.keySet().toArray(
                new Integer [0] );

        for ( Integer columnKey : columnKeys )
        {
            CellView cView = sheet1.getColumnView( columnKey.intValue() );

            cView.setSize( adjustedColumnWidths.get( columnKey ).intValue() );

            sheet1.setColumnView( columnKey.intValue(), cView );
        }

        book.write();
        book.close();

        return file.getAbsolutePath();
    }

    /**
     * This method suggests adjusted widths for columns. Applying adjusted
     * column widths would arrange the columns in a way that they are expanded
     * to their fullest i.e. revealing even the longest value.
     *
     * @param p_adjustedColumnWidths
     *            A map containing last adjusted values for the column widths.
     * @param p_columnIndex
     *            Index of the column to suggest adjustment.
     * @param p_contents
     *            The contents that would be placed in the cell (it would affect
     *            the adjusted width of the column in which the cell lies).
     * @param p_bold
     *            A boolean value representing wether the font is bold.
     *
     * @return A map containing suggested adjusted values for column widths.
     */
    private Map<Integer, Integer> updateAdjustedColumnWidth(
            Map<Integer, Integer> p_adjustedColumnWidths, int p_columnIndex,
            String p_contents, boolean p_bold )
    {
        Integer columnWidth = p_adjustedColumnWidths.get( Integer
                .valueOf( p_columnIndex ) );

        if ( columnWidth != null )
        {
            if ( p_contents.length() > ( columnWidth.intValue() * 256 ) )
            {
                p_adjustedColumnWidths.put( Integer.valueOf( p_columnIndex ),
                        Integer.valueOf( p_contents.length() * 256 ) );
            }
        }
        else
        {
            p_adjustedColumnWidths.put( Integer.valueOf( p_columnIndex ),
                    Integer.valueOf( p_contents.length() * 256 ) );
        }

        return p_adjustedColumnWidths;
    }

    /**
     * This utility method finds given header name in already defined headers,
     * if a match is found the index of that header is returned, otherwise -1 is
     * returned.
     *
     * @param p_headers
     *            An array of Cell objects containing headers (x or y axis).
     * @param p_header
     *            A String object containing header for lookup.
     *
     * @return Index of the header in headers or -1.
     */
    private int getHeader( Cell [] p_headers, String p_header )
    {
        for ( int i = 0; i < p_headers.length; i++ )
        {
            if ( p_header.equals( p_headers[i].getContents() ) )
            {
                return i;
            }
        }
        return -1;
    }

    /**
     * This method is responsible for creating a CSV file from a XLS file.
     *
     * @param p_directory
     *            Directory to create the file in.
     * @param p_filename
     *            File will be created with this name.
     *
     * @return Fully qualified name of generated file.
     *
     * @throws Exception
     *             Propagate any exceptions to the caller.
     */
    public String convertXLStoCSV( String p_directory, String p_filename )
            throws Exception
    {
        String xlsFile = p_directory + "/" + p_filename + ".xls";
        String csvFile = p_directory + "/" + p_filename + ".csv";

        File file = new File( csvFile );

        OutputStream outStream = ( OutputStream ) new FileOutputStream( file );

        OutputStreamWriter outWriter = new OutputStreamWriter( outStream,
                encoding );

        BufferedWriter bufferedWriter = new BufferedWriter( outWriter );

        // Excel document to be imported

        WorkbookSettings bookSettings = new WorkbookSettings();
        bookSettings.setLocale( worksheetLocale );
        bookSettings.setEncoding( encoding );

        Workbook book = Workbook
                .getWorkbook( new File( xlsFile ), bookSettings );

        // Gets the sheets from workbook
        for ( int sheetN = 0; sheetN < book.getNumberOfSheets(); sheetN++ )
        {
            Sheet sheet = book.getSheet( sheetN );

            bufferedWriter.write( sheet.getName() );
            bufferedWriter.newLine();

            Cell [] row = null;

            // Gets the cells from sheet
            for ( int i = 0; i < sheet.getRows(); i++ )
            {
                row = sheet.getRow( i );

                if ( row.length > 0 )
                {
                    bufferedWriter.write( row[0].getContents() );
                    for ( int j = 1; j < row.length; j++ )
                    {
                        bufferedWriter.write( ',' );
                        bufferedWriter.write( row[j].getContents() );
                    }
                }
                bufferedWriter.newLine();
            }
        }
        bufferedWriter.flush();
        bufferedWriter.close();

        return file.getAbsolutePath();
    }

    /**
     * This static innner class contains constants for setting Worksheet Locale
     * information.
     */
    static final class WSLocale
    {
        // TODO: Introduce additional locale information

        /**
         * Locale countries
         */
        public static final String    England    = "EN";

        public static final String    US        = "US";

        /**
         * Locale languages
         */
        public static final String    En        = "en";

        /**
         * Locale encoding
         */
        public static final String    UTF_8    = "UTF8";
    }

//    /**
//     * Temporary testing method (main entrypoint)
//     *
//     * @param args
//     *            Of course you know, it's an array of commandline arguments.
//     */
//    public static void main( String [] args ) throws Exception
//    {
//        Hashtable [] ht_data = new Hashtable [10];
//
//        for ( int i = 0; i < ht_data.length; i++ )
//        {
//            Hashtable ht_datum = new Hashtable();
//
//            ht_datum.put( "studentName", "Student "
//                    + System.currentTimeMillis() );
//            ht_datum.put( "subjectName", "Subject " + System.currentTimeMillis() );
//            ht_datum.put( "marksSecured", BigDecimal.valueOf( 25.34343 * new Date(
//                    System.currentTimeMillis() ).getSeconds() ) );
//
//            ht_data[i] = ht_datum;
//
//            Thread.currentThread().sleep(
//                    new Date( System.currentTimeMillis() ).getSeconds() % 2 == 0 ? 2000L : 1000L );
//        }
//
//        WorksheetGenerator wsGen = new WorksheetGenerator();
//        wsGen.createXLSFile( "C:\\&quot;, "myFile", ht_data, "studentName",
//                "subjectName", "marksSecured" );
//    }
   
}
-------------------------------------------------------------------------------------------
CODE Ends
-------------------------------------------------------------------------------------------