Solved

how to write excel files using java

Posted on 2008-06-23
10
8,170 Views
Last Modified: 2013-11-23
I would like to write excel file using java code. i need to suitable class for writing excel file and sample codes.
0
Comment
Question by:Ravi_Jaganathan
10 Comments
 
LVL 13

Accepted Solution

by:
MicheleMarcon earned 66 total points
ID: 21846288
You could  set the excel file as a data source and use JDBC-ODBC bridge for reading/writing it.
0
 
LVL 6

Assisted Solution

by:manuel_m
manuel_m earned 62 total points
ID: 21846381
Go to http://poi.apache.org/overview.html

Here you will find example code http://poi.apache.org/hssf/how-to.html

Here is also a list of available libs: http://schmidt.devlib.org/java/libraries-excel.html

manuel
0
 
LVL 9

Assisted Solution

by:sanjooz
sanjooz earned 62 total points
ID: 21851025
If you need support for formula in Excel use Apache POI (http://poi.apache.org/)

For other reporting purposes, you can use Jaspter Reports (http://www.jasperforge.org/jaspersoft/opensource/business_intelligence/jasperreports/)

If you just need the tabular data format without the native binary format, you can create a text file separated by tab with xls file extension. This will be opened by Excel as excel sheet.
0
 
LVL 92

Assisted Solution

by:objects
objects earned 62 total points
ID: 21851429
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Assisted Solution

by:shaz_
shaz_ earned 62 total points
ID: 21853287
i would rather use .csv files which u can open in excel and more managble with native java code..
package net.xxxxxxxx;
 

/**

 * Created by IntelliJ IDEA.

 * User: xxxxx

 * Date: xxxxx

 * Time: xxxxx

 * To change this template use File | Settings | File Templates.

 */

import java.io.BufferedReader;

import java.io.EOFException;

import java.io.FileReader;

import java.io.IOException;

import java.io.Reader;

import java.util.ArrayList;

import java.util.Date;

import java.sql.DriverManager;

import java.sql.Connection;

import java.sql.Statement;

import java.text.SimpleDateFormat;
 

public final class CSVReader {
 
 

    private static final boolean DEBUGGING = false;
 

    /**

     * e.g. \n \r\n or \r, whatever system uses to separate lines in a text

     * file. Only used inside multiline fields. The file itself should use

     * Windows format \r \n, though \n by itself will also work.

     */

    static String lineSeparator = System.getProperty( "line.separator" );
 

    /**

     * parser: We have just hit a quote, might be doubled or might be last one.

     */

    private static final int AFTER_END_QUOTE = 3;
 

    /**

     * category of end of line char.

     */

    private static final int EOL = 0;
 

    /**

     * parser: We are in the middle of an ordinary field.

     */

    private static final int IN_PLAIN = 1;
 

    /**

     * parser: e are in middle of field surrounded in quotes.

     */

    private static final int IN_QUOTED = 2;
 

    /**

     * category of ordinary character

     */

    private static final int ORDINARY = 1;
 

    /**

     * categotory of the quote mark "

     */

    private static final int QUOTE = 2;
 

    /**

     * parser: We are in blanks before the field.

     */

    private static final int SEEKING_START = 0;
 

    /**

     * category of the separator, e.g. comma, semicolon or tab.

     */

    private static final int SEPARATOR = 3;
 

    /**

     * parser: We are in blanks after the field looking for the separator

     */

    private static final int SKIPPING_TAIL = 4;
 

    /**

     * category of characters treated as white space.

     */

    private static final int WHITESPACE = 4;
 

    /**

     * Reader source of the CSV fields to be read.

     */

    private BufferedReader r;
 
 

    private String line = null;
 

    /**

     * false means next EOL marks an empty field true means next EOL marks the

     * end of all fields.

     */

    private boolean allFieldsDone = true;
 

    /**

     * true if reader should allow quoted fields to span more than one line.

     * Microsoft Excel sometimes generates files like this.

     */

    private final boolean allowMultiLineFields;
 

    /**

     * true if reader should trim lead/trail whitespace from fields returned.

     */

    private final boolean trim;
 

    /**

     * quote character, usually '\"' '\'' for SOL used to enclose fields

     * containing a separator character.

     */

    private final char quote;
 

    /**

     * field separator character, usually ',' in North America, ';' in Europe

     * and sometimes '\t' for tab.

     */

    private final char separator;
 

    /**

     * How many lines we have read so far. Used in error messages.

     */

    private int lineCount = 0;
 

    // -------------------------- PUBLIC INSTANCE  METHODS --------------------------

    /**

     * convenience Constructor, default to comma separator, " for quote, no

     * multiline fields, with trimming.

     *

     * @param r input Reader source of CSV Fields to read.

     */

    public CSVReader( Reader r )

        {

        this( r, ',', '\"', false, true );

        }
 

    /**

     * Constructor

     *

     * @param r                    input Reader source of CSV Fields to read.

     * @param separator            field separator character, usually ',' in

     *                             North America, ';' in Europe and sometimes

     *                             '\t' for tab.

     * @param quote                char to use to enclose fields containing a

     *                             separator, usually '\"'

     * @param allowMultiLineFields true if reader should allow quoted fields to

     *                             span more than one line. Microsoft Excel

     *                             sometimes generates files like this.

     * @param trim                 true if reader should trim lead/trailing

     *                             whitespace e.g. blanks, Cr, Lf. Tab off

     *                             fields.

     */

    public CSVReader( Reader r,

                      char separator,

                      char quote,

                      boolean allowMultiLineFields,

                      boolean trim )

        {

        /* convert Reader to BufferedReader if necessary */

        if ( r instanceof BufferedReader )

            {

            this.r = (BufferedReader) r;

            }

        else

            {

            this.r = new BufferedReader( r );

            }

        if ( this.r == null )

            {

            throw new IllegalArgumentException( "invalid Reader" );

            }
 

        this.separator = separator;

        this.quote = quote;

        this.allowMultiLineFields = allowMultiLineFields;

        this.trim = trim;

        }
 

    /**

     * Close the Reader.

     *

     * @throws IOException

     */

    public void close() throws IOException

        {

        if ( r != null )

            {

            r.close();

            r = null;

            }

        }
 

    /**

     * Read one field from the CSV file

     *

     * @return String value, even if the field is numeric. Surrounded and

     *         embedded double quotes are stripped. possibly "". null means end

     *         of line.

     *

     * @throws EOFException at end of file after all the fields have been read.

     * @throws IOException  Some problem reading the file, possibly malformed

     *                      data.

     */

    public String get() throws EOFException, IOException

        {

        StringBuffer field =

                new StringBuffer( allowMultiLineFields ? 512 : 64 );

        /*  implementing the parser as a finite state automaton with five states. */
 

        int state = SEEKING_START;/*

                                     * start seeking, even if partway through a

                                     * line

                                     */

        /* don't need to maintain state between fields. */
 

        lineLoop:

        while ( true )

            {

            getLineIfNeeded();
 

            charLoop:

            /* loop for each char in the line to find a field */

            /* guaranteed to leave early by hitting EOL */

            for ( int i = 0; i < line.length(); i++ )

                {

                char c = line.charAt( i );

                int category = categorise( c );

                if ( false )

                    {

                    // for debugging

                    System.out

                            .println( "char:"

                                      + c

                                      + " state:"

                                      + state

                                      + " field:"

                                      + field.length() );

                    }

                switch ( state )

                    {

                    case SEEKING_START:

                    {/* in blanks before field */

                    switch ( category )

                        {

                        case WHITESPACE:

                            /* ignore */

                            break;
 

                        case QUOTE:

                            state = IN_QUOTED;

                            break;
 

                        case SEPARATOR:

                            /* end of empty field */

                            line = line.substring( i + 1 );

                            return "";
 

                        case EOL:

                            /* end of line */

                            if ( allFieldsDone )

                                {

                                /* null to mark end of line */

                                line = null;

                                return null;

                                }

                            else

                                {

                                /* empty field, usually after a comma */

                                allFieldsDone = true;

                                line = line.substring( i );

                                return "";

                                }
 

                        case ORDINARY:

                            field.append( c );

                            state = IN_PLAIN;

                            break;

                        }

                    break;

                    }

                    case IN_PLAIN:

                    {/* in middle of ordinary field */

                    switch ( category )

                        {

                        case QUOTE:

                            throw new IOException(

                                    "Malformed CSV stream. Missing quote at start of field on line "

                                    + lineCount );
 

                        case SEPARATOR:

                            /* done */

                            line = line.substring( i + 1 );

                            return maybeTrim( field.toString() );
 

                        case EOL:

                            line = line.substring( i );/* push EOL back */

                            allFieldsDone = true;

                            return maybeTrim( field.toString() );
 

                        case WHITESPACE:

                            field.append( ' ' );

                            break;
 

                        case ORDINARY:

                            field.append( c );

                            break;

                        }

                    break;

                    }
 

                    case IN_QUOTED:

                    {/* in middle of field surrounded in quotes */

                    switch ( category )

                        {

                        case QUOTE:

                            state = AFTER_END_QUOTE;

                            break;
 

                        case EOL:

                            if ( allowMultiLineFields )

                                {

                                field.append( lineSeparator );

                                // we are done with that line, but not with

                                // the

                                // field.

                                // We don't want to return a null

                                // to mark the end of the line.

                                line = null;

                                // will read next line and seek the end of

                                // the

                                // quoted field.

                                // with state = IN_QUOTED.

                                break charLoop;

                                }

                            else

                                {

                                // no multiline fields allowed

                                allFieldsDone = true;

                                throw new IOException(

                                        "Malformed CSV stream. Missing quote (\") after field on line "

                                        + lineCount );

                                }

                        case WHITESPACE:

                            field.append( ' ' );

                            break;
 

                        case SEPARATOR:

                        case ORDINARY:

                            field.append( c );

                            break;

                        }

                    break;

                    }
 

                    case AFTER_END_QUOTE:

                    {

                    /*

                    * In situation like this "xxx" which may turn out to be

                    * xxx""xxx" or "xxx", We find out here.

                    */

                    switch ( category )

                        {

                        case QUOTE:

                            /* was a double quote, e.g. a literal " */

                            field.append( c );

                            state = IN_QUOTED;

                            break;
 

                        case SEPARATOR:

                            /* we are done with field. */

                            line = line.substring( i + 1 );

                            return maybeTrim( field.toString() );
 

                        case EOL:

                            line = line.substring( i );/* push back eol */

                            allFieldsDone = true;

                            return maybeTrim( field.toString() );
 

                        case WHITESPACE:

                            /* ignore trailing spaces up to separator */

                            state = SKIPPING_TAIL;

                            break;
 

                        case ORDINARY:

                            throw new IOException(

                                    "Malformed CSV stream, missing separator after fieldon line "

                                    + lineCount );

                        }

                    break;

                    }
 

                    case SKIPPING_TAIL:

                    {

                    /* in spaces after field seeking separator */
 

                    switch ( category )

                        {

                        case SEPARATOR:

                            /* we are done. */

                            line = line.substring( i + 1 );

                            return maybeTrim( field.toString() );
 

                        case EOL:

                            line = line.substring( i );/* push back eol */

                            allFieldsDone = true;

                            return maybeTrim( field.toString() );
 

                        case WHITESPACE:

                            /* ignore trailing spaces up to separator */

                            break;
 

                        case QUOTE:

                        case ORDINARY:

                            throw new IOException(

                                    "Malformed CSV stream, missing separator after field on line "

                                    + lineCount );

                        }

                    break;

                    }

                    }// end switch(state)

                }// end charLoop

            }// end lineLoop

        }// end get
 

    /**

     * Get all fields in the line

     *

     * @return Array of strings, one for each field. Possibly empty, but never

     *         null.

     *

     * @throws EOFException

     * @throws IOException

     */

    public String[] getAllFieldsInLine() throws EOFException, IOException

        {

        ArrayList al = new ArrayList( 30 );

        do

            {

            String field = get();

            if ( field == null )

                {

                break;

                }

            al.add( field );

            }

        while ( true );

        return (String[]) ( al.toArray( new String[al.size()] ) );

        }
 

    /**

     * Read one double field from the CSV file.

     *

     * @return houble value, empty field returns 0, as does end of line.

     *

     * @throws EOFException           at end of file after all the fields have

     *                                been read.

     * @throws IOException            Some problem reading the file, possibly

     *                                malformed data.

     * @throws NumberFormatException, if field does not contain a well-formed

     *                                int.

     */

    public double getDouble() throws EOFException, IOException, NumberFormatException

        {

        String s = get();

        if ( s == null )

            {

            return 0;

            }

        if ( !trim )

            {

            s = s.trim();

            }

        if ( s.length() == 0 )

            {

            return 0;

            }

        return Double.parseDouble( s );

        }
 

    /**

     * Read one float field from the CSV file.

     *

     * @return float value, empty field returns 0, as does end of line.

     *

     * @throws EOFException           at end of file after all the fields have

     *                                been read.

     * @throws IOException            Some problem reading the file, possibly

     *                                malformed data.

     * @throws NumberFormatException, if field does not contain a well-formed

     *                                int.

     */

    public float getFloat() throws EOFException, IOException, NumberFormatException

        {

        String s = get();

        if ( s == null )

            {

            return 0;

            }

        if ( !trim )

            {

            s = s.trim();

            }

        if ( s.length() == 0 )

            {

            return 0;

            }

        return Float.parseFloat( s );

        }
 

    /**

     * Read one integer field from the CSV file

     *

     * @return int value, empty field returns 0, as does end of line.

     *

     * @throws EOFException           at end of file after all the fields have

     *                                been read.

     * @throws IOException            Some problem reading the file, possibly

     *                                malformed data.

     * @throws NumberFormatException, if field does not contain a well-formed

     *                                int.

     */

    public int getInt() throws EOFException, IOException, NumberFormatException

        {

        String s = get();

        // end of line returns 0

        if ( s == null )

            {

            return 0;

            }

        if ( !trim )

            {

            s = s.trim();

            }

        if ( s.length() == 0 )

            {

            return 0;

            }

        return Integer.parseInt( s );

        }
 

    /**

     * Read one long field from the CSV file

     *

     * @return long value, empty field returns 0, as does end of line.

     *

     * @throws EOFException           at end of file after all the fields have

     *                                been read.

     * @throws IOException            Some problem reading the file, possibly

     *                                malformed data.

     * @throws NumberFormatException, if field does not contain a well-formed

     *                                int.

     */

    public long getLong() throws EOFException, IOException, NumberFormatException

        {

        String s = get();

        if ( s == null )

            {

            return 0;

            }

        if ( !trim )

            {

            s = s.trim();

            }
 

        if ( s.length() == 0 )

            {

            return 0;

            }

        return Long.parseLong( s );

        }
 

    /**

     * Skip over fields you don't want to process.

     *

     * @param fields How many field you want to bypass reading. The newline

     *               counts as one field.

     *

     * @throws EOFException at end of file after all the fields have been read.

     * @throws IOException  Some problem reading the file, possibly malformed

     *                      data.

     */

    public void skip( int fields ) throws EOFException, IOException

        {

        if ( fields <= 0 )

            {

            return;

            }

        for ( int i = 0; i < fields; i++ )

            {

            // throw results away

            get();

            }

        }
 

    /**

     * Skip over remaining fields on this line you don't want to process.

     *

     * @throws EOFException at end of file after all the fields have been read.

     * @throws IOException  Some problem reading the file, possibly malformed

     *                      data.

     */

    public void skipToNextLine() throws EOFException, IOException

        {

        if ( line == null )

            {

            getLineIfNeeded();

            }

        line = null;

        }
 

    // -------------------------- STATIC METHODS --------------------------
 

    static

        {

        if ( lineSeparator == null )

            {

            lineSeparator = "\015012";/* crlf in octal */

            }

        }
 

    // -------------------------- OTHER METHODS --------------------------
 

    /**

     * categorise a character for the finite state machine.

     *

     * @param c the character to categorise

     *

     * @return integer representing the character's category.

     */

    private int categorise( char c )

        {

        switch ( c )

            {

            case ' ':

            case '\r':

            case 0xff:

                return WHITESPACE;
 

            case '\n':

                return EOL;/* artificially applied to end of line */
 

            default:

                if ( c == quote )

                    {

                    return QUOTE;

                    }

                else if ( c == separator/*

                                             * dynamically determined so can't

                                             * use as case label

                                             */ )

                    {

                    return SEPARATOR;

                    }

                /* do our tests in crafted order, hoping for an early return */

                else if ( '!' <= c && c <= '~' )

                    {

                    return ORDINARY;

                    }

                else if ( 0x00 <= c && c <= 0x20 )

                    {

                    return WHITESPACE;

                    }

                else if ( Character.isWhitespace( c ) )

                    {

                    return WHITESPACE;

                    }

                else

                    {

                    return ORDINARY;

                    }

            }

        }
 

    /**

     * Make sure a line is available for parsing. Does nothing if there already

     * is one.

     *

     * @throws EOFException

     */

    private void getLineIfNeeded() throws EOFException, IOException

        {

        if ( line == null )

            {

            if ( r == null )

                {

                throw new IllegalArgumentException(

                        "attempt to use a closed CSVReader" );

                }

            allFieldsDone = false;

            line = r.readLine();/* this strips platform specific line ending */

            if ( line == null )/*

                                 * null means EOF, yet another inconsistent Java

                                 * convention.

                                 */

                {

                throw new EOFException();

                }

            else

                {

                line += '\n';/* apply standard line end for parser to find */

                lineCount++;

                }

            }

        }
 

    /**

     * Trim the string, but only if we are in trimming mode.

     *

     * @param s String to be trimmed.

     *

     * @return String or trimmed string.

     */

    private String maybeTrim( String s )

        {

        if ( trim )

            {

            return s.trim();

            }

        else

            {

            return s;

            }

        }
 

    // --------------------------- main() method ---------------------------
 

    /**

     * Test driver

     * To insert into a table values from a cvs file just modify the filereader path and the insert statement

     * for the appropriate file and table. and run

     * @param args not used

     */

    public static void main( String[] args )

        {

            Connection conn = null;

            System.out.println("Program initiated");

            try {

                 String userName = "xxxx";

                String password = "xxxx";

                String url = "jdbc:mysql:/xxx.dev.xxxx.net:3306/xxxxx";

                System.out.println("passet point 1");

                Class.forName("com.mysql.jdbc.Driver").newInstance();

                System.out.println("passed point 2");

                conn = DriverManager.getConnection(url, userName, password);

                System.out.println("Database connection established");

            }

            catch (Exception e) {

                System.err.println("Cannot connect to database server because : "+e);

            }

            finally {

                if (conn != null) {

                    try {

                        try {

                            // read test file

                            CSVReader csv =

                                    new CSVReader(new FileReader("/opt/testfiles/dropdown_dynamic.csv"),

                                            ',',

                                            '\"',

                                            true,

                                            false);

                            try {

                                int count = 1;

                                while (true) {

                                    String[] line = csv.getAllFieldsInLine();

                                    Statement s = conn.createStatement();

                                    System.out.println(line.length);

                                    try{

                                        System.out.println("BOOGIE VALUE IS : "+new SimpleDateFormat("yyyy-MM-dd").format(new SimpleDateFormat("dd-MM-yyyy").parse((line[6]))));
 

                                    s.executeUpdate(

                                            "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

                                                    + " VALUES"

                                                    + "("+"'"+(new Integer(count)).toString()+"','"+line[0]+"','"+line[1] +"','"+ line[2]+"','"+line[3] +"','"+line[4]+"','"+line[5] +"','"+new SimpleDateFormat("yyyy-MM-dd").format(new SimpleDateFormat("dd-MM-yyyy").parse((line[6])))+"','"+line[7] +"','"+line[8]+"','"+line[9] +"','"+line[10]+"','"+line[11] +"',"+"' '"+",'"+line[12]+"'"+")");

                                   // System.out.println("--> " + csv.get());

                                    count++;

                                    }

                                    catch(Exception e){

                                        System.out.println(e);

                                    }

                                }

                            }

                            catch (EOFException e) {

                                System.out.println(e);

                            }

                            csv.close();

                        }

                        catch (IOException e) {

                            e.printStackTrace();

                            System.out.println(e.getMessage());

                        }

                        conn.close();

                        System.out.println("Database connection terminated");

                    }

                    catch (Exception e) { /* ignore close errors */ }

                }

            }

          /*  if (DEBUGGING) {

                try {

                    // read test file

                    CSVReader csv =

                            new CSVReader(new FileReader("test.csv"),

                                    ',',

                                    '\"',

                                    true,

                                    false);

                    try {

                        while (true) {

                            System.out.println("--> " + csv.get());

                        }

                    }

                    catch (EOFException e) {

                    }

                    csv.close();

                }

                catch (IOException e) {

                    e.printStackTrace();

                    System.out.println(e.getMessage());

                }

            }// end if*/

        }// end main
 

}// end CSVReader class.

Open in new window

0
 
LVL 3

Assisted Solution

by:sreehariharan
sreehariharan earned 62 total points
ID: 21946368
Hi ravi
u can Use the apache POI.
use HSSF package u can create and save the excel files

http://mirrors.enquira.com/apache/poi/

in this link u can get the dev folder to have source code and samples
u can get the binary package itself using release folder
0
 

Assisted Solution

by:fighter_007
fighter_007 earned 62 total points
ID: 22103260
Please take a look at this link:

http://jexcelapi.sourceforge.net/

Java Excel API allows developers to read, write and modify excel files. Refer to link for more information.
0
 
LVL 9

Assisted Solution

by:darkapple
darkapple earned 62 total points
ID: 22287479
Here is the link with executing example to create, read, write data to Excel documents at runtime

http://www.java-tips.org/other-api-tips/jexcel/how-to-create-an-excel-file.html
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now