Solved

java creating xls (excell file) issue

Posted on 2006-11-13
13
240 Views
Last Modified: 2010-05-18
hi,

I hv this piece of classes file which create/writes xls file taking input as data etc., Only thing I need to modify here is: I want to create xls only with first 50,000 records rest i wanna ignore.  Any idea how to do it ????

-----------------------------------------------------------------------------
ImportData Java file
----------------------------------------------------------------------------
...
int aRow = 0;
int aRowCount;
....
public XLSImport(String theFileName) throws Exception {
        try {
           
            File ifile = new File(theFileName);
            mWorkbook = Workbook.getWorkbook(ifile);
            mSheet = mWorkbook.getSheet(0);
            mRowCount = mSheet.getRows();
            mName = theFileName;
        }
        catch (Exception e) {
           ...          
        }
    }
....
public boolean next() {
        if (aRow < (aRowCount - 1)) {
            aRow++;
            return true;
        }
       
        return false;
    }
-----------------------------------------------------------------------------
Insert Java file
----------------------------------------------------------------------------
...
 public InsertStatement(ImportData xData,
....
....
public int insertRow() throws SQLException {
        if (xData.next()) {
            ret = 0;     // not end of data
           
            mStatement.clearParameters();
            String[] vals = mData.getColumns();
           
            boolean rowempty = true;
           
            if (vals != null) {
                 // Set values
                  for (int i = 0; i < mParamMap.size(); i++) {
                    String v = null;
                    if (i < vals.length && vals[i] != null) {
                        v = vals[((Integer)mParamMap.get(i)).intValue()- 1];
                    }
                    if (v != null && v.length() == 0) {
                        rowempty = false;
                    }
                    setParameter(i, v);
                }
            }
                       
            if (!rowempty)
                ret = mStatement.executeUpdate();
        }
       
        return ret;
    }
....
........................................................................

thx,
PH
0
Comment
Question by:princehyderabad
  • 7
  • 6
13 Comments
 
LVL 6

Expert Comment

by:SamsonChung
ID: 17933671
put an extra counter there.

when you count the rows.

while (counter < 50000) {
  //XLS row writing codes goes here
}
0
 

Author Comment

by:princehyderabad
ID: 17933685
I'm replacement of that person who wrote this classes, still I'm trying to understand. But this is kind of emergency came up... so thought if someone can help me solve this. Can U tell where to put that counter.... thanks "SamsonChung"
0
 
LVL 6

Expert Comment

by:SamsonChung
ID: 17933716
well, u got the wrong java file, (at first I was assuming you intentionally rename it this way....)

the top one, as the name suggest is an XLS import method. where it takes in an XLS, and read from it. not write to.

2nd, method/class, writes to DB, not read.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:princehyderabad
ID: 17933846
Yes I intenttionally renamed files, but can u tell where to put your while loop please....
Also I hv Q: if records are less than 50000 still it runs ur while....yeah...
0
 
LVL 6

Expert Comment

by:SamsonChung
ID: 17933872
Sorry bro,

that doing that intentionally to mislead is against my organization's code of ethics........ 'Documentation violation'

so, unless you show the full proper code, I can't help you anymore.
0
 

Author Comment

by:princehyderabad
ID: 17933926
I dont understand where am I misleading ? having change the method names variable from what templates provide is that misleading ????
0
 

Author Comment

by:princehyderabad
ID: 17934192
here is the full code:

public class InsertStatement {
   
    private ImportData mData;
    private Connection mConnection;
   
    private String[] mFields;
    private Vector mParamMap = new Vector();
    private String mSql;
    private PreparedStatement mStatement;
   
    private ParameterMetaData mParamTypes;
   
    private InsertMetaData mInsertMetaData;
       
    public InsertStatement(ImportData theData,
            String theSql,
            Connection theConnection) throws SQLException, ImportFileException {
        mData = theData;
        mConnection = theConnection;
       
        // Get all the fields from the source
       
        mFields = theData.getColumnNames();
       
        mInsertMetaData = new InsertMetaData(theConnection, theSql);
       
       
        if (mFields.length < mInsertMetaData.getFieldCount()) {
            String msg = "Import file has too few columns: " + mFields.length
                        + " should have " + mInsertMetaData.getFieldCount();
            throw new ImportFileException(msg);
        }
       
             
        Pattern vpattern = Pattern.compile(":V([0-9]*)");
        Matcher mvals = vpattern.matcher(theSql);
        StringBuffer sql = new StringBuffer();
       
        while (mvals.find()) {
            String vindex = mvals.group(1);
            int vi = Integer.parseInt(vindex);
            mParamMap.add(new Integer(vi));
            mvals.appendReplacement(sql, "?");
        }
       
        mvals.appendTail(sql);
        mSql = sql.toString();
       
        if (log.isDebugEnabled()) {
            log.debug("Created SQL for " + mData.getName() + ":" + mSql);
        }      
               
        mStatement = theConnection.prepareStatement(mSql);            
    }
   
    /**
     * Insert the next row (if any) into the database. Return number of records inserted
     * or -1 for end of data    
     */
    public int insertRow() throws SQLException, ImportFileException, ImportRowException {
        int ret = -1;            // default is end of data

        if (mData.next()) {
            ret = 0;     // not end of data, but maybe empty row
           
            mStatement.clearParameters();
            String[] vals = mData.getColumns();
                       
            // Ignore blank lines
           
            boolean rowempty = true;
           
            if (vals != null) {
                 // Set values
               
               for (int i = 0; i < mParamMap.size(); i++) {
                    String v = null;
                    if (i < vals.length && vals[i] != null) {
                        v = vals[((Integer)mParamMap.get(i)).intValue()- 1];
                    }
                    if (v != null && v.length() == 0) {
                        rowempty = false;
                    }
                    setParameter(i, v);
                }
            }
           
            // Do the insert if the row isn't empty
           
            if (!rowempty)
                ret = mStatement.executeUpdate();
        }
       
        return ret;
    }
   
   
    public void setParameter(int theParameter, String theVal)
    throws ImportFileException, ImportRowException {
       
        String verr = mInsertMetaData.validate(theParameter, theVal);
        if (verr != null) {
            throw new ImportRowException(verr);
        }
       
        int pindex = theParameter + 1;
        try {
            mStatement.setString(pindex, theVal);
        } catch (SQLException e) {
            String msg = "SQLException setting param " + pindex + " to '" + theVal + "':" + e.getMessage();
            log.error(msg, e);
            throw new ImportFileException(msg);
        }
    }
   
0
 
LVL 6

Expert Comment

by:SamsonChung
ID: 17938093
Like I said before,

The methods you have provided are not doing the task you want it to do. (ie, are you absolutly sure this is the right java file?)

You said you want to read from DATA SOURCE and output to XLS.

but the code is saying, read from XLS and INSERT to DATASOURCE
0
 

Author Comment

by:princehyderabad
ID: 17940694
Okay I found the right class its XLSExportData

...
public void addRow(Object[] theValues) throws SqlJobException {      
        try {                        
            mCurrentRow++;
            if (mCurrentRow < 5000) -----------------------// I added this if
            {
            mSheet.insertRow(mCurrentRow);
           
            for (int i = 0; i < theValues.length; i++) {                  
                Object v = theValues[i];                
                if (v != null) {                    
                    WritableCell cell = new Label(i, mCurrentRow, v.toString());
                    mSheet.addCell(cell);                    
                }                
            }
            }//end if
        }
...

Can U verify is that the write way to implement what I want. Also can U tell me how to put some session value here.
0
 
LVL 6

Expert Comment

by:SamsonChung
ID: 17941560
that looks right.... assuming your mCurrentRow is set at the class level and properly initialized.

Also, 5000? not 50000?



What you mean by Session Value? As in a counter to how many times this session has been ran?
to do that, either keep your program on standby all the time and store it in a Thread. or simply save it to a file.
0
 

Author Comment

by:princehyderabad
ID: 17941692
>>What you mean by Session Value? As in a counter to how many times this session has been ran?

Idea is whenever there are records more then 50,000 we are ignorning rest correct. So in that case we want to display some customer msg to user saying only 50,000 are put some msg. If they are less than 50,000 records no need to display msg, as all records will be in excel.

and currently flow is:

JSP ----------> Action Class  ---------->UserDisplayJSP.
                        __|__
                      |         |
       XLDataImport      OtherClass
0
 
LVL 6

Accepted Solution

by:
SamsonChung earned 250 total points
ID: 17941909
So, basically, you want to run a Session variable via JSP.

Now I see what you are doing....

go and read the following page.

http://www.jsptut.com/Sessions.jsp

just make sure your Webserver have Session turned on.

Also, if you get stuck more, go to JSP section or Web Dev section for more help.
0
 

Author Comment

by:princehyderabad
ID: 17941972
Comeon SamsonChung I know how to handle session in JSP. The issue I have is how can I set session value in 'XLDataImport' class. Becuase this is general class not servlet. If it were to do in Action class I would have done easily. got my point.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Android studio getdrawable(int) is deprecated 4 88
Java Server Faces parameter pass? 6 50
eclipse buid path vs tomcat lib path 10 24
going to wrong jsp page 2 22
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…
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 about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

825 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