jsp - Compare specific row or column of xls sheet using java and Apache POI -
my jsp file (to upload excel file) :
<%@ page language="java" contenttype="text/html; charset=iso-8859-1" pageencoding="iso-8859-1"%> <!doctype html public "-//w3c//dtd html 4.01 transitional//en" "http://www.w3.org/tr/html4/loose.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1"> <title>insert title here</title> </head> <body> <form method="post" action="uploadaction" enctype="multipart/form-data"> upload *.xls file: <input type="file" name="file" value="file"> <br><br> <input type="submit" value="submit"> </body> </html> my servlet code ( process excel file)
import javax.servlet.servletexception; import javax.servlet.http.*; import java.io.fileinputstream; import java.io.filenotfoundexception; import java.io.fileoutputstream; import java.io.ioexception; import java.io.file; import java.io.printwriter; import java.sql.connection; import java.sql.drivermanager; import java.sql.preparedstatement; import java.util.list; import java.util.iterator; import org.apache.commons.fileupload.fileitemfactory; import org.apache.commons.fileupload.fileitem; import org.apache.commons.fileupload.servlet.servletfileupload; import org.apache.commons.fileupload.disk.diskfileitemfactory; import org.apache.poi.hssf.usermodel.hssfcell; import org.apache.poi.hssf.usermodel.hssfrow; 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; public class uploadaction extends httpservlet { public void doget(httpservletrequest request, httpservletresponse response) throws servletexception, ioexception { dopost(request, response); } public void dopost(httpservletrequest request,httpservletresponse response) throws servletexception, ioexception { boolean ismultipart = servletfileupload.ismultipartcontent(request); printwriter out = response.getwriter(); connection conn = null; preparedstatement pstmt = null; string query = "insert mydoct(id) values (?)"; if(ismultipart) { try { fileitemfactory factory = new diskfileitemfactory(); servletfileupload upload = new servletfileupload(factory); list items = upload.parserequest(request); iterator iter = items.iterator(); while (iter.hasnext()) { fileitem item = (fileitem) iter.next(); if(!item.isformfield()) { string filename = item.getname(); string root = getservletcontext().getrealpath("/"); file path = new file(root + "/mydoctors"); if (!path.exists()) { boolean status = path.mkdirs(); } file uploadedfile = new file(path + "/" + filename); string fpath = uploadedfile.getabsolutepath(); //out.println(fpath); int mid = filename.lastindexof("."); string ext = filename.substring(mid+1,filename.length()); //out.println(ext); if((ext.tolowercase().equals("xls"))||(ext.tolowercase().equals("xlsx"))) { item.write(uploadedfile); try { class.forname("com.mysql.jdbc.driver"); string url = "jdbc:mysql://localhost:3306/test"; conn = drivermanager.getconnection(url,"root","root"); pstmt = conn.preparestatement(query); fileinputstream file = new fileinputstream(new file(fpath)); hssfworkbook wb = new hssfworkbook(file); //get first sheet workbook hssfsheet sheet = wb.getsheetat(0); //find total number of rows. int rows = sheet.getphysicalnumberofrows(); out.println(rows); //iterate through each rows first sheet iterator<row> rowiterator = sheet.iterator(); //while(rowiterator.hasnext()) { for(int = 1; i<=rows; i++){ row row = rowiterator.next(); //find total no columns in particular raw. hssfrow rcol = sheet.getrow(i); int cells = 0; if (row != null) { cells = row.getphysicalnumberofcells(); //out.println(cells); } //for each row, iterate through each columns iterator<cell> celliterator = row.celliterator(); //while(celliterator.hasnext()) { for(int j = 0; j<cells; j++){ cell cell = celliterator.next(); switch(cell.getcelltype()) { case cell.cell_type_boolean: out.print(cell.getbooleancellvalue() + "\t\t"); break; case cell.cell_type_numeric: out.print(cell.getnumericcellvalue() + "\t\t"); break; case cell.cell_type_string: out.print(cell.getstringcellvalue() + "\t\t"); break; } } } file.close(); fileoutputstream fos = new fileoutputstream(new file(fpath)); wb.write(fos); out.close(); } catch (filenotfoundexception e) { e.printstacktrace(); } catch (ioexception e) { e.printstacktrace(); } } }else { out.println("invalid file"); } } }catch(exception ex) { ex.printstacktrace(); } } } } i want read 2nd , 3rd columns of 5th, 6th , 7th rows. please me..........
as shown in apache poi documentation, there 2 broad ways access rows , cells. 1 iterate on of them (well suited cases when want everything), other fetch rows , cells index (works when want specific cells)
you've said want 2nd , 3rd columns of rows 5, 6 , 7. that's easy:
sheet s = workbook.getsheetat(0); // rows , cells 0 based not 1 based row r5 = s.getrow(4); row r6 = s.getrow(5); row r7 = s.getrow(6); if (r5 == null || r6 == null || r7 == null) { throw new illegalargumentexception("at least 1 required row empty"); } cell c5_2 = r5.getcell(1); cell c5_3 = r5.getcell(2); cell c6_2 = r6.getcell(1); cell c6_3 = r6.getcell(2); cell c7_2 = r7.getcell(1); cell c7_3 = r7.getcell(2); // use them // eg (cell cell : new cell[] { c5_2, c5_3, c6_2, c6_3 } ) { switch(cell.getcelltype()) { case cell.cell_type_boolean: out.print(cell.getbooleancellvalue() + "\t\t"); break; case cell.cell_type_numeric: out.print(cell.getnumericcellvalue() + "\t\t"); break; case cell.cell_type_string: out.print(cell.getstringcellvalue() + "\t\t"); break; } }
Comments
Post a Comment