[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

java creating xls (excell file) issue

Posted on 2006-11-13
13
Medium Priority
?
249 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 1000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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…
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 learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses

656 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