saving spread sheet data into database?

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

sivaprasadjvs gravatar image sivaprasadjvs
1 2

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

sjoshi gravatar image sjoshi flag of India
3493 1 8
<?page title="My First ZK Spreadsheet application" contentType="text/html;charset=UTF-8"?>
    <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>

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

public void submit() throws Exception{ 

                /* Create Connection objects */
                Class.forName ("org.postgresql.Driver"); 
                Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "postgres",
                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());                                                                                     
                                        case Cell.CELL_TYPE_NUMERIC: //handle double data
                                                sql_statement.setDouble(2,cell.getNumericCellValue() );

                //we can execute the statement before reading the next row
                /* Close input stream */
                /* Close prepared statement */
                /* COMMIT transaction */
                /* Close connection */

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

delete flag offensive retag edit


Hi Joshi, Any suggestions or inputs on saving/writing the current spreadsheet data to the database? In the above example, a new empty excel file is opened which will not have the current data.

Thanks, Jitesh

jitesh ( 2013-06-09 23:33:40 +0800 )edit

@Jitesh i did not work in the Spreadsheet component so no any idea abt it.

sjoshi ( 2013-06-11 06:25:04 +0800 )edit

2 Answers

Sort by ยป oldest newest most voted

answered 2013-04-23 07:46:46 +0800

sjoshi gravatar image sjoshi flag of India
3493 1 8

You have to given a Id to button

<button label="submit" id="button"></button>

try this code and let me know if you still have UI Exception

link publish delete flag offensive edit

answered 2013-06-11 00:04:37 +0800

phasthal gravatar image phasthal
5 3

one way to access the current sheet is Book wb = spreadsheet.getBook(); where spreadsheet is "id" declared in zul file ex: <spreadsheet id="spreadsheet" ....=""/> then you can iterate the sheet by getting respective sheet by index/name and save it to db

another way is to export the sheet to another file, read it and store (not a better approach, but you can try this) Exporter exporter = Exporters.getExporter("excel"); exporter.export(wb, outputStream);

link publish delete flag offensive edit
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!

[hide preview]

Question tools




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

Seen: 87 times

Last updated: Jun 11 '13

Support Options
  • Email Support
  • Training
  • Consulting
  • Outsourcing
Learn More