saving spread sheet data into database?

asked 2013-04-22 15:33:41 +0800

sivaprasadjvs
1 ● 2

updated 2013-04-23 07:44:37 +0800

sjoshi
3493 ● 1 ● 8
http://zkframeworkhint.bl...

<?page title="My First ZK Spreadsheet application" contentType="text/html;charset=UTF-8"?>
<zk>
    <window title="My First ZK Spreadsheet Application"
        border="normal" vflex="1" width="100%" apply="tutorial.DBConnect">

        <spreadsheet src="/WEB-INF/test.xls"  maxrows="200"
        maxcolumns="40" vflex="1" width="100%"></spreadsheet>
        <button label="submit"></button>
    </window>
</zk>

this is the script file i have used... my intention is when user clicks on submit then the data/information in the spread sheet must be saved....

package tutorial;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.zkoss.zk.ui.select.SelectorComposer;
import org.zkoss.zk.ui.select.annotation.Listen;
import org.zkoss.zul.Window;

public class DBConnect extends SelectorComposer<Window>{  

    @Listen("OnClick=button")
public void submit() throws Exception{ 

                /* Create Connection objects */
                Class.forName ("org.postgresql.Driver"); 
                Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "postgres",
                            "root");
                PreparedStatement sql_statement = null;
                String jdbc_insert_sql = "INSERT INTO userdetail values(?,?)";
                sql_statement = conn.prepareStatement(jdbc_insert_sql);
                /* We should now load excel objects and loop through the worksheet data */
                FileInputStream input_document = new FileInputStream(new File("C://test.xls"));**//This is wrong how to send the file which i was able to see in the browser to be sent....???**
                /* Load workbook */
                HSSFWorkbook my_xls_workbook = new HSSFWorkbook(input_document);
                /* Load worksheet */
                HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);
                // we loop through and insert data
                Iterator<Row> rowIterator = my_worksheet.iterator(); 
                while(rowIterator.hasNext()) {
                        Row row = rowIterator.next(); 
                        Iterator<Cell> cellIterator = row.cellIterator();
                                while(cellIterator.hasNext()) {
                                        Cell cell = cellIterator.next();
                                        switch(cell.getCellType()) { 
                                        case Cell.CELL_TYPE_STRING: //handle string columns
                                                sql_statement.setString(1, cell.getStringCellValue());                                                                                     
                                                break;
                                        case Cell.CELL_TYPE_NUMERIC: //handle double data
                                                sql_statement.setDouble(2,cell.getNumericCellValue() );
                                                break;
                                        }

                                }
                //we can execute the statement before reading the next row
                sql_statement.executeUpdate();
                }
                /* Close input stream */
                input_document.close();
                /* Close prepared statement */
                sql_statement.close();
                /* COMMIT transaction */
                conn.commit();
                /* Close connection */
                conn.close();
        }
}

this is the java file i have written to save the excel information in the postgres database..

when i have deployed the applicaiton i was ended up with UIException

org.zkoss.zk.ui.UiException: Illegal value of @Listen: OnClick=button

any body help me in this issue where i am wrong....

another question is how to send the same spread sheet which i can view to the java file...

any tutorial/help is needed urgently....

regards siva prasad