Solved

Write to excel file

Posted on 2001-08-22
10
1,070 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
10 Comments
 

Expert Comment

by:hquoc
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:wolfs
Comment Utility
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
Comment Utility
If someone can help be create a excel file from scratch I would increase points seriously
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Accepted Solution

by:
giamtrot earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
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 basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

763 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

7 Experts available now in Live!

Get 1:1 Help Now