I am getting java.lang.ClassNotFoundException: org.apache.poi.ss.usermodel.Workbook by using poi-3.16 library jar files,can anyone help me to solve this issue.
UploadExcel(Servlet) (this is used to call the methods of UploadDao class based on UploadBean class object)
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.tomcat.util.http.fileupload.FileItem;
import org.apache.tomcat.util.http.fileupload.FileItemFactory;
import org.apache.tomcat.util.http.fileupload.FileUploadException;
import org.apache.tomcat.util.http.fileupload.RequestContext;
import org.apache.tomcat.util.http.fileupload.disk.DiskFileItemFactory;
import org.apache.tomcat.util.http.fileupload.servlet.ServletFileUpload;
import org.apache.tomcat.util.http.fileupload.servlet.ServletRequestContext;
import com.model.dao.UploadDao;
import com.mvc.bean.UploadBean;
import com.upload.excel.InsertDataToDB;
import javax.servlet.annotation.WebServlet;
@WebServlet("/UploadExcel")
public class UploadExcel extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
if (isMultipart) {
// Create a factory for disk-based file items
FileItemFactory factory = new DiskFileItemFactory();
// Create a new file upload handler
ServletFileUpload upload = new ServletFileUpload(factory);
try {
// Parse the request
List /* FileItem */ items = upload.parseRequest(new ServletRequestContext(request));
Iterator iterator = items.iterator();
while (iterator.hasNext()) {
FileItem item = (FileItem) iterator.next();
if (!item.isFormField()) {
String fileName = item.getName();
System.out.println(fileName);
String fileN = fileName.substring(fileName.lastIndexOf("\\") + 1);
System.out.println(fileN);
// String root = getServletContext().getRealPath("/");
String root1 = "E:/eclipse/workspace/GSTR_Test0/WebContent";
File path = new File(root1 + "/uploads");
if (!path.exists()) {
boolean status = path.mkdirs();
}
File uploadedFile = new File(path + "/" + fileN);
System.out.println(uploadedFile.getAbsolutePath());
item.write(uploadedFile);
String filePath = uploadedFile.toString();
UploadBean uploadbean = new UploadBean();
uploadbean.setFilepath(filePath);
// creating object for uploadDao class and calling
// methods in it
UploadDao uploadDao = new UploadDao();
boolean b = uploadDao.getDataFromExcel();
if (b == true) {
System.out.println("excel data is assigned to bean class");
} else {
System.out.println("unable to assign data to bean class");
}
String s = uploadDao.uploadDataToDB(uploadbean);
if (s.equals("SUCCESS")) {
System.out.println("data inserted successfully into DB");
} else {
System.out.println(" unable to insert data into DB");
}
}
}
} catch (FileUploadException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
UploadDao(it has two methods, reads data from uploaded excel file and sets the file cell values to UploadBean class object and gets the value from same bean class in another method)
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import com.db.util.DBConnection;
import com.mvc.bean.UploadBean;
public class UploadDao {
public boolean getDataFromExcel() {
// List excel = new ArrayList();
FileInputStream fis = null;
UploadBean uploadbean = new UploadBean();
String FILE_PATH = uploadbean.getFilepath();
try {
fis = new FileInputStream(FILE_PATH);
// Using XSSF for xlsx format, for xls use HSSF
Workbook workbook = new XSSFWorkbook(fis);
int numberOfSheets = workbook.getNumberOfSheets();
// looping over each workbook sheet
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
Iterator rowIterator = sheet.iterator();
// iterating over each row
while (rowIterator.hasNext()) {
Row row = (Row) rowIterator.next();
Iterator cellIterator = row.cellIterator();
// Iterating over each cell (column wise) in a particular
// row.
while (cellIterator.hasNext()) {
Cell cell = (Cell) cellIterator.next();
// The Cell Containing String will is name.
if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
uploadbean.setSupplyplace(cell.getStringCellValue());
// The Cell Containing numeric value will contain
// marks
} else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
// Cell with index 1 contains marks in Maths
if (cell.getColumnIndex() == 0) {
uploadbean.setSerialno(cell.getNumericCellValue());
}
// Cell with index 2 contains marks in Science
else if (cell.getColumnIndex() == 1) {
uploadbean.setGstin(cell.getNumericCellValue());
}
// Cell with index 3 contains marks in English
else if (cell.getColumnIndex() == 2) {
uploadbean.setInvoiceno(cell.getNumericCellValue());
} else if (cell.getColumnIndex() == 3) {
uploadbean.setInvoicedate(cell.getNumericCellValue());
} else if (cell.getColumnIndex() == 4) {
uploadbean.setInvoicevalue(cell.getNumericCellValue());
} else if (cell.getColumnIndex() == 5) {
uploadbean.setRate(cell.getNumericCellValue());
} else if (cell.getColumnIndex() == 6) {
uploadbean.setTaxablevalue(cell.getNumericCellValue());
} else if (cell.getColumnIndex() == 7) {
uploadbean.setIntegratedtax(cell.getNumericCellValue());
} else if (cell.getColumnIndex() == 8) {
uploadbean.setCentraltax(cell.getNumericCellValue());
} else if (cell.getColumnIndex() == 9) {
uploadbean.setStatetax(cell.getNumericCellValue());
} else if (cell.getColumnIndex() == 10) {
uploadbean.setCess(cell.getNumericCellValue());
}
}
}
// end iterating a row, add all the elements of a row in
// list
// studentList.add(student);
}
}
fis.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return true;
}
public String uploadDataToDB(UploadBean uploadBean) {
double serialNo = uploadBean.getSerialno();
double gstin = uploadBean.getGstin();
double invoiceNo = uploadBean.getInvoiceno();
double invoiceDate = uploadBean.getInvoicedate();
double invoiceValue = uploadBean.getInvoicevalue();
double rate = uploadBean.getRate();
double taxableValue = uploadBean.getTaxablevalue();
double integratedTax = uploadBean.getIntegratedtax();
double centralTax = uploadBean.getCentraltax();
double stateTax = uploadBean.getStatetax();
double cess = uploadBean.getCess();
String supplyPlace = uploadBean.getSupplyplace();
Connection conn = null;
PreparedStatement preparedstatement = null;
try {
conn = DBConnection.createConnection();
String query = "insert into table4a(gstin,invoiceno,invoicedate,invoicevalue,rate,taxablevalue,integratedtax,centraltax,statetax,cess,supplyplace) values(?,?,?,?,?,?,?,?,?,?,?)"; // Insert
// user
// details
// into
// the
// table
// 'registration'
preparedstatement = conn.prepareStatement(query); // Making use of
// prepared
// statements
// here to
// insert bunch
// of data
preparedstatement.setDouble(1, gstin);
preparedstatement.setDouble(2, invoiceNo);
preparedstatement.setDouble(3, invoiceDate);
preparedstatement.setDouble(4, invoiceValue);
preparedstatement.setDouble(5, rate);
preparedstatement.setDouble(6, taxableValue);
preparedstatement.setDouble(7, integratedTax);
preparedstatement.setDouble(8, centralTax);
preparedstatement.setDouble(9, stateTax);
preparedstatement.setDouble(10, cess);
preparedstatement.setString(11, supplyPlace);
int i = preparedstatement.executeUpdate();
if (i != 0) // Just to ensure data has been inserted into the
// database
return "SUCCESS";
} catch (SQLException e) {
e.printStackTrace();
}
return "This data was already uploaded by you";
}
}
UploadBean
import java.io.File;
import javax.servlet.http.Part;
public class UploadBean {
private String filepath;
private File file;
private double serialno;
private double gstin;
private double invoiceno;
private double invoicedate;
private double invoicevalue;
private double rate;
private double taxablevalue;
private double Integratedtax;
private double centraltax;
private double statetax;
private double cess;
private String supplyplace;
public File getFile() {
return file;
}
public void setFile(File file) {
this.file = file;
}
public String getFilepath() {
return filepath;
}
public void setFilepath(String filepath) {
this.filepath = filepath;
}
public double getSerialno() {
return serialno;
}
public void setSerialno(double serialno) {
this.serialno = serialno;
}
public double getGstin() {
return gstin;
}
public void setGstin(double gstin) {
this.gstin = gstin;
}
public double getInvoiceno() {
return invoiceno;
}
public void setInvoiceno(double invoiceno) {
this.invoiceno = invoiceno;
}
public double getInvoicedate() {
return invoicedate;
}
public void setInvoicedate(double invoicedate) {
this.invoicedate = invoicedate;
}
public double getInvoicevalue() {
return invoicevalue;
}
public void setInvoicevalue(double invoicevalue) {
this.invoicevalue = invoicevalue;
}
public double getRate() {
return rate;
}
public void setRate(double rate) {
this.rate = rate;
}
public double getTaxablevalue() {
return taxablevalue;
}
public void setTaxablevalue(double taxablevalue) {
this.taxablevalue = taxablevalue;
}
public double getIntegratedtax() {
return Integratedtax;
}
public void setIntegratedtax(double Integratedtax) {
this.Integratedtax = Integratedtax;
}
public double getCentraltax() {
return centraltax;
}
public void setCentraltax(double centraltax) {
this.centraltax = centraltax;
}
public double getStatetax() {
return statetax;
}
public void setStatetax(double statetax) {
this.statetax = statetax;
}
public double getCess() {
return cess;
}
public void setCess(double cess) {
this.cess = cess;
}
public String getSupplyplace() {
return supplyplace;
}
public void setSupplyplace(String supplyplace) {
this.supplyplace = supplyplace;
}
}
Exception
java.lang.ClassNotFoundException: org.apache.poi.ss.usermodel.Workbook
at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1892)
at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1735)
at UploadExcel.doPost(UploadExcel.java:77)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:506)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1115)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
