?
Solved

Write to excel file

Posted on 2001-08-22
10
Medium Priority
?
1,079 Views
Last Modified: 2008-03-17
Hi,

I need to export my JTable to an excel file,

can ayone help me?
0
Comment
Question by:wolfs
[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
 

Expert Comment

by:hquoc
ID: 6413271
Hi wolfs,
I see, for ur question, u need to export data in JTable to Excel. If may be, u inform clearly for exporting by the following ways:
1. Copy data in Your JTable, and then paste them to excel file opened.
2. Collect all data in the specified format in Your JTable, and then write to excel file opened.
3. Including item 2, with the excel file is created entirely.
wolfs, which number can you choose?
best regards,
huuquoc
0
 

Author Comment

by:wolfs
ID: 6413307
Item 3.

I have a JTable with data, this data needs to be exported to a new Excel file.
0
 
LVL 1

Expert Comment

by:googlyralph
ID: 6413330
Do you need to export it to .xls file, or can you export to something else, then import it into excel. If this is the case, you could iterate through the table, row by row, writing each cell out to a .cvs file (comma deliminated file), xls supports importing of cvs files.

Also, i think that JDBC can write to excel files directly. I've never done it, but you would use the JDBC <-> ODBC bridge, the the XLS driver from microsoft. I assume that it works just like a normal database table via teh JDBC.

GR.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:wolfs
ID: 6413436
I've also thought of csv, but the problem with csv is that the delimeter (when opening in Excel) is either a comma or a semicolon (;) depending on the regional settings.

I've also thought of JDBC but the JTable is actually on an applet so I have to work around the security manager.
I think I can handle working around the security manager writing a file, but working around it for JDBC looks a bit trickier to me.
0
 

Author Comment

by:wolfs
ID: 6413465
If someone can help be create a excel file from scratch I would increase points seriously
0
 
LVL 1

Accepted Solution

by:
giamtrot earned 800 total points
ID: 6413658
The subsequent example create an Excel file version 2.1 (very old) so that is compatible with all newer excel version.

import java.io.ByteArrayOutputStream;
import java.io.DataOutputStream;
import java.io.FileOutputStream;

/**
 * This is an example class to use the functionalities of the other
 * classes here presented.
 * It builds a simple Excel file, version 2.1, which shuold be compatible
 * with almost all the excel programs.
 * Rows and columns numbers start from 0.
 */
public class excelWriter {
   
    public static void main(String args[]) throws Exception
    {
        FileOutputStream fos = new FileOutputStream("d:\\tmp\\prova.xls");
       
        // writes the page start tag
        fos.write(InizioPagina.getBytenized());    
       
        // writes the formats
        fos.write(Formati.getBytenized());    
       
        // writes the width of columns
        LarghezzaColonne lc = new LarghezzaColonne(0, 255, 10);
        fos.write(lc.getBytenized());
       
       
        // writes some data
        TextData td = new TextData("Ciao", 1, 1);
        td.setAlignment(ExcelConstants.ALIGNMENT_CENTER + ExcelConstants.ALIGNMENT_RIGHT_BORDER + ExcelConstants.ALIGNMENT_SHADED);
        fos.write(td.getBytenized());
       
       
        NumberData nd = new NumberData(13.777, 3, 7);
        nd.setFormat(2);
        fos.write(nd.getBytenized());
       
       
        // writes the end page tag
        fos.write(FinePagina.getBytenized());
    }
}

/**
 * Utility class to write bytes in the format Excel waits.
 */
class bytesUtilities
{
    /**
     * Writes an integer value in two bytes inverting the bytes order.
     * @param bfr The byte buffer to write on
     * @param numero The int value to write
     */
    public static void integerToBytes(ByteArrayOutputStream bfr, int numero)
    {
        if (numero <= 256) {
            bfr.write(numero);
            bfr.write(0);
        }
        else
        {
            bfr.write(numero-256);
            bfr.write(numero/256);
        }
    }
   
   
    /**
     * Writes a double value in four bytes inverting the bytes order.
     * @param bfr The byte buffer to write on
     * @param numero The double value to write
     */
    public static void doubleToBytes(ByteArrayOutputStream bfr, double numero)
    {
       
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        DataOutputStream dos = new DataOutputStream(baos);
        try
        {
            dos.writeDouble(numero);
            byte[] tmp = baos.toByteArray();
            int numValori = tmp.length;
            for ( int i=0; i < numValori; i++)
                bfr.write(tmp[numValori - i - 1]);
        }
        catch ( Exception e )
        {
            e.printStackTrace();
        }
    }
}
   
/**
 * Class containing useful constants
 */
class ExcelConstants
{
    /**
     * Constant for default alignment
     */
    public static int ALIGNMENT_GENERAL = 0;
   
    /**
     * Constant for left alignment
     */
    public static int ALIGNMENT_LEFT = 1;
   
    /**
     * Constant for center alignment
     */
    public static int ALIGNMENT_CENTER = 2;

   
    /**
     * Constant for right alignment
     */
    public static int ALIGNMENT_RIGHT = 3;

    /**
     * Constant for fill
     */
    public static int ALIGNMENT_FILL = 4;


    /**
     * Constant for left border
     */
    public static int ALIGNMENT_LEFT_BORDER = 8;


    /**
     * Constant for right border
     */
    public static int ALIGNMENT_RIGHT_BORDER = 16;
   
    /**
     * Constant for top border
     */
    public static int ALIGNMENT_TOP_BORDER = 32;

    /**
     * Constant for bottom border
     */
    public static int ALIGNMENT_BOTTOM_BORDER = 64;


    /**
     * Constant for shaded
     */
    public static int ALIGNMENT_SHADED = 128;
}
   

/**
 * Class to write text data.
 * The attributes (as alignment) are fixed.
 */
class TextData {
    private int opcode      = 4;
   
    private static final int hiddenLocked   = 0;
    private int cellFormat     = 0;
    private int alignment   = 0;
   
    private int textLength;
    private int length;
   
    private int row;
    private int col;
   
    private String text;
   
    /**
     * Construct a new read only text holder
     * @param text The text to write into the cell
     * @param row The row of the cell (0-65535)
     * @param col The col of the cell (0-255)
     */
    public TextData(String text, int row, int col)
    {
       
        if (row < 0)
            row=0;

        if (col < 0)
            col=0;

        if (row > 65535)
            row=65535;
           
        if (col > 255)
            col=255;
           
        this.text=text;
        this.row =row;
        this.col =col;

        this.textLength = text.length();
        this.length     = this.textLength + 8;
    }
   
    /**
     * Set the alignment
     * @param alignment The alignment (as in ExcelConstants)
     */
    public void setAlignment(int alignment)
    {
        this.alignment = alignment;
    }
   
    /**
     * Get the alignment
     * @return The alignment (as in ExcelConstants)
     */
    public int getAlignment()
    {
        return this.alignment;
    }
   
   
    /**
     * Set the format
     * @param format The format
     */
    public void setFormat(int format)
    {
        this.cellFormat = format;
    }

    /**
     * Get the format
     * @return The format
     */
    public int getFormat()
    {
        return this.cellFormat;
    }
   
    /**
     * Return this record as a byte array
     * @param The array representing this record
     */
    public byte[] getBytenized() {
        ByteArrayOutputStream bfr=new ByteArrayOutputStream();
        bytesUtilities.integerToBytes(bfr, this.opcode);
        bytesUtilities.integerToBytes(bfr, this.length);
        bytesUtilities.integerToBytes(bfr, this.row-1);
        bytesUtilities.integerToBytes(bfr, this.col-1);
        bfr.write(this.hiddenLocked);
        bfr.write(this.cellFormat);
        bfr.write(this.alignment);
        bfr.write(this.textLength);
       
        byte tmp[]=this.text.getBytes();
        bfr.write(tmp, 0, tmp.length);
       
        return bfr.toByteArray();
    }
}


/**
 * Class to write Number data.
 * The attributes (as alignment) are fixed.
 */
class NumberData {
    private int opcode      = 3;
   
    private static final int hiddenLocked   = 0;
    private int cellFormat     = 0;
    private int alignment   = 0;
   
    private int length;
   
    private int row;
    private int col;
   
    private double numero;
    private byte[] bytenized;
   
    /**
     * Construct a new read only number holder
     * @param numero The number to write into the cell
     * @param row The row of the cell (0-65535)
     * @param col The col of the cell (0-255)
     */
    public NumberData(double numero, int row, int col)
    {
        if (row < 0)
            row=0;
           
        if (col < 0)
            col=0;
           
        if (row > 65535)
            row=65535;

        if (col > 255)
            col=255;
       
        this.row =row;
        this.col =col;

        this.length = 15;
        this.numero = numero;
       
    }

    /**
     * Set the alignment
     * @param alignment The alignment (as in ExcelConstants)
     */
    public void setAlignment(int alignment)
    {
        this.alignment = alignment;
    }
   
    /**
     * Get the alignment
     * @return The alignment (as in ExcelConstants)
     */
    public int getAlignment()
    {
        return this.alignment;
    }
   
   
    /**
     * Set the format
     * @param format The format
     */
    public void setFormat(int format)
    {
        this.cellFormat = format;
    }

    /**
     * Get the format
     * @return The format
     */
    public int getFormat()
    {
        return this.cellFormat;
    }  
   
   
    /**
     * Return this record as a byte array
     * @param The array representing this record
     */
    public byte[] getBytenized()
    {
        ByteArrayOutputStream bfr=new ByteArrayOutputStream();
        bytesUtilities.integerToBytes(bfr, opcode);
        bytesUtilities.integerToBytes(bfr, length);
        bytesUtilities.integerToBytes(bfr, row-1);
        bytesUtilities.integerToBytes(bfr, col-1);
        bfr.write(hiddenLocked);
        bfr.write(cellFormat);
        bfr.write(alignment);
       
        bytesUtilities.doubleToBytes(bfr, numero);
       
        return bfr.toByteArray();
    }
   
}


/**
 * Class to write start page tag
 */
class InizioPagina {
    private static int opcode  = 9;
    private static int length  = 4;
    private static int version = 7;
    private static int ftype   = 16;
   
    /**
     * Returns a byte array representing this record
     * @return The byte array
     */
    public static byte[] getBytenized()
    {
        ByteArrayOutputStream bfr=new ByteArrayOutputStream();
        bytesUtilities.integerToBytes(bfr, opcode);
        bytesUtilities.integerToBytes(bfr, length);
        bytesUtilities.integerToBytes(bfr, version);
        bytesUtilities.integerToBytes(bfr, ftype);
       
        return bfr.toByteArray();
    }
   
}



/**
 * Class to write width of column tag
 */
class LarghezzaColonne
{
    private static int opcode       = 36;
    private static int length       = 4;
    private static int col1         ;
    private static int col2         ;
    private static int width        ;

    /**
     * Creates a new column width holder
     * @param col1 The first column to apply this width (0-255)
     * @param col2 The last column to apply this width (0-255)
     * @param width The width to apply (in number of char)
     */
    public LarghezzaColonne(int col1, int col2, int width)
    {
        if (col1 < 0)
            col1=0;
       
        if (col2 < 0)
            col2=0;
       
        if (col1 > 255)
            col1=255;
               
        if (col2 > 255)
            col2=255;
       
        this.col1 = col1;
        this.col2 = col2;
        this.width = width*256;
    }
   
    /**
     * Returns a byte array representing this record
     * @return The byte array
     */
    public byte[] getBytenized()
    {
   
        ByteArrayOutputStream bfr=new ByteArrayOutputStream();
        bytesUtilities.integerToBytes(bfr, opcode);
        bytesUtilities.integerToBytes(bfr, length);
        bfr.write(col1);
        bfr.write(col2);
        bytesUtilities.integerToBytes(bfr, width);
       
        return bfr.toByteArray();
    }        
}

/**
 * Class to write formats
 */
class Formati
{
    private static String formati[] = { "General", "0", "0.00" };
   
    private static int opcodeHeader = 31; //1f
    private static int lengthHeader = 2;
    private static int countFormats = formati.length;
   
    private static int opcodeFormat = 30; // 1e
       
    public static byte[] getBytenized()
    {
        ByteArrayOutputStream bfr=new ByteArrayOutputStream();
        bytesUtilities.integerToBytes(bfr, opcodeHeader);
        bytesUtilities.integerToBytes(bfr, lengthHeader);
        bytesUtilities.integerToBytes(bfr, countFormats);
       
        for (int frm = 0; frm < countFormats; frm++ )
        {
            int lengthFormat = formati[frm].length();
            byte frmt[]      = formati[frm].getBytes();
           
            bytesUtilities.integerToBytes(bfr, opcodeFormat);
            bytesUtilities.integerToBytes(bfr, lengthFormat + 1);
            bfr.write(lengthFormat);
           
            bfr.write(frmt, 0, frmt.length);
        }

        return bfr.toByteArray();
    }  
   
   
}

 
/**
 * Class to write end page tag
 */
class FinePagina
{
    private static int opcode       = 10;
    //private static int length       = 0;
       
    /**
     * Returns a byte array representing this record
     * @return The byte array
     */
    public static byte[] getBytenized()
    {
        ByteArrayOutputStream bfr=new ByteArrayOutputStream();
        bytesUtilities.integerToBytes(bfr, opcode);
       
        return bfr.toByteArray();
    }        
}

I've used it and I think it's rather stable.
0
 
LVL 1

Expert Comment

by:dviji
ID: 6413673
It is possible by using Jdbc Odbc Driver. For that you should have Excel driver. Create the DataSource and the rest is same.

There are some limitations. i.e You can create an Excel file, insert them. But you can't do delete, update or alter operations. That is not allowed. i.e ;-((((.
Bur for your case just export you JTable data to Excel SHeet there is no problem... ;-))))) (Because you have to do only insertion.). Suppose if you imported an table and you did some correction... then you have to delete the entire sheet and export (Create and insert) the new one.
(for deleting the Excel use Runtime and execute an dos command for deletion i.e
Runtime.getRuntime().exec("del yourExcelSheetpath");)

Here is the sample program. For this create an DataSource with the name MyExcel. While DSN creation select the version as Excel 4.0. For this type you have to point some directory which will contains that Excel file.

Here is the sample Code

-----------------------------------
import java.sql.*;

public class ExcelTest
{
     public void store(String product[][])
     {
          try
          {
               Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
               Connection con = DriverManager.getConnection("jdbc:odbc:MyExcel");

               System.out.println("Connection Created");

               Statement statement = con.createStatement();
               statement.executeUpdate("CREATE TABLE MyProducts (ProductID TEXT, ProductName TEXT, Qnty INT)");

               for(int i=0;i<product.length;i++)
               {
                    statement.executeUpdate("insert into MyProducts values ('"+product[i][0]+"','"+product[i][1]+"',"+Integer.parseInt(product[i][2])+")");
               }

               ResultSet rs = statement.executeQuery("select sum(Qnty) from MyProducts where ProductName like 'Java%'");
               int totalQnty = 0;
               while(rs.next())
               {
                    totalQnty = rs.getInt(1)     ;
               }

               System.out.println("Total Number of Products = "+totalQnty);
          }
          catch(Exception e)
          {
               e.printStackTrace();
          }
     }

     public static void main(String[] args)
     {
          String products[][] = {{"1","Java 1.1","10"},{"2","Java 1.2","12"},{"3","Oracle 8i","25"},{"4","Vaj 4.0","8"},{"5","Java 1.3","30"},{"6","Rational Rose JE","26"},{"7","Java 1.4","40"}};
          new ExcelTest().store(products);
     }
}
------------------------------------
If you wants to know more about the Driver add this following piece..

DatabaseMetaData dmd = connection.getMetaData();
System.out.println("SQL Keywords= "+dmd.getSQLKeywords());
System.out.println("String Functions = " +  
                    dmd.getStringFunctions());
System.out.println("Numeric Functions = " +              
                    dmd.getNumericFunctions());

---------------------------------------

We can  extract the values from the JTable.
Sample Code
----------------------------------------------
public String[][] getTableValues(JTable myJTable)
{
    TableModel myTableModel = myJTable.getModel();
    int rowCount = myTableModel.getRowCount();
    int columnCount = myTableModel.getColumnCount();

    String values[][] = new String[rowCount][columnCount];

    for(int i=0;i<rowCount;i++)
    {
     for(int j=0;j<columnCount;j++)
     {
     values[i][j] = myTableModel.getValueAt  (i,j).toString();
     }
    }
    return values;
}

//passing the table and extracting the values not good. If you have your own cutomized class just add the method with out the parameter. Here I did it just for understanding the piece.
----------------------------------------
//for more details about the Excel driver goto http://support.microsoft.com/support/kb/articles/Q178/7/17.ASP

Best of Luck..

.....dviji

0
 
LVL 1

Expert Comment

by:dviji
ID: 6413766
Hi wolfs,
         Just now I saw your new comments...
From Applet if you want to create an Excel file... You mean you want to store that file in client side or just you want to export that file...If it is on client side...  you should sign your applet....And if you wants to use JDBC then what is the surity that the client have that driver...If you want to export the detail to server side hen no problem (With the help of servlet you can achive it). My comments is just to Create an Excelsheet from an application....

Suppose if you need any help let me know.

...dviji







..dviji

             
0
 
LVL 1

Expert Comment

by:sureshkumar
ID: 6779571
hey diviji,

i have the same problem i mean i have to write to client side not at server side.So can u give me some suggestion how can write code for that i need it very urgently...
pls u can contact me at

suresh_tavvala@yahoo.com


thanks in advance
0
 
LVL 3

Expert Comment

by:Kobold
ID: 7443339
Message for giamtrot
Do you have the code to read from an excel file?
I can do it from your code, but if the code exist, that will accelerate my project...

If you have it, i can give you 200 points

Thanks

Richard
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
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:
Suggested Courses
Course of the Month14 days, 22 hours left to enroll

770 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