Solved

java creating xls (excell file) issue

Posted on 2006-11-13
13
236 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
triangle challenge 4 77
count7 challenge 12 70
countAbc challenge 9 49
mockito example issue 8 35
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…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This video teaches viewers about errors in exception handling.

757 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

19 Experts available now in Live!

Get 1:1 Help Now