[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

how to write excel files using java

Posted on 2008-06-23
10
Medium Priority
?
8,215 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 264 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 248 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 248 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 92

Assisted Solution

by:objects
objects earned 248 total points
ID: 21851429
0
 
LVL 3

Assisted Solution

by:shaz_
shaz_ earned 248 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 248 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 248 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 248 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
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…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

649 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