[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

java creating xls (excell file) issue

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
princehyderabad
Asked:
princehyderabad
  • 7
  • 6
1 Solution
 
SamsonChungCommented:
put an extra counter there.

when you count the rows.

while (counter < 50000) {
  //XLS row writing codes goes here
}
0
 
princehyderabadAuthor Commented:
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
 
SamsonChungCommented:
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!

 
princehyderabadAuthor Commented:
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
 
SamsonChungCommented:
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
 
princehyderabadAuthor Commented:
I dont understand where am I misleading ? having change the method names variable from what templates provide is that misleading ????
0
 
princehyderabadAuthor Commented:
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
 
SamsonChungCommented:
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
 
princehyderabadAuthor Commented:
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
 
SamsonChungCommented:
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
 
princehyderabadAuthor Commented:
>>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
 
SamsonChungCommented:
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
 
princehyderabadAuthor Commented:
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

Industry Leaders: 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!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now