edit and save the spreadsheet

asked 2013-04-23 13:06:38 +0800

sivaprasadjvs
1 ● 2

updated 2013-04-23 13:15:19 +0800

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

Hi All,

I am successfully viewing the spread sheet application from my program...

My question was how to edit and save the information to the same spread sheet that i'm viewing.. for your reference i'm pasting the code and what i have did...

dbconnect.java:

this file has to take the data from the database and need to save it in the database so far it is working good for the XL file i'm viewing..

here is the code found from the google...

package tutorial;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
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.zhtml.Filedownload;
import org.zkoss.zk.ui.event.Event;
import org.zkoss.zk.ui.select.SelectorComposer;
import org.zkoss.zk.ui.select.annotation.Listen;
import org.zkoss.zss.model.Book;
import org.zkoss.zss.model.Exporter;
import org.zkoss.zss.model.Exporters;
import org.zkoss.zss.ui.Spreadsheet;
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 java.io.File("").getAbsolutePath()+"/webapps/ZKTestApp/WEB-INF/test.xls");
                /* 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();
        }

    public void onClick$exportBtn(Event evt) throws IOException {
        Spreadsheet spreadsheet= new Spreadsheet();
        Book wb = spreadsheet.getBook();
        Exporter c = Exporters.getExporter("test");
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        c.export(wb, baos);
        Filedownload.save(baos.toByteArray(), "application/file",
                wb.getBookName());
    }

}

dbconnect.zul:

this will have a button on the top submit when ever user clicks submit button then it has to get the data from the excel and save it... it is working fine till now...

here is the code:

<?page title="ZSS Export to Different File Format" contentType="text/html;charset=UTF-8"?>
<window width="100" height="100%"    apply="tutorial.DBConnect">
   <button label="submit"></button>
    <spreadsheet id="spreadsheet"
        src="/WEB-INF/test.xls" maxrows="200" maxcolumns="40"
        vflex="1" width="100%">
    </spreadsheet>
</window>

Now the requirement is how to edit the excel fiel and save the excel sheet so that if i click the submit button it will get stored back into the database...

pls help me in saving the same excel file after editing...

regards siva prasad