Solved

how to write excel files using java

Posted on 2008-06-23
10
8,191 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 92

Assisted Solution

by:objects
objects earned 62 total points
ID: 21851429
0
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
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:

717 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