
Here is my requirement, I want to read the records from a .xlsx file and store that records in database table. I tried like this
public class StoreFile {
public int storeFileRecords(String saveFile,ServletContext application){
Connection con=null;
Statement st=null;
Properties props=null;
String bytesToString=null;
try {
props=new Properties();
props.load(application.getResourceAsStream("databasedetails.properties"));
String url=props.getProperty("url");
String userName=props.getProperty("userName");
String password=props.getProperty("password");
String driverClassName=props.getProperty("driverClassName");
Class.forName(driverClassName);
con = DriverManager.getConnection(url,userName,password);
st=con.createStatement();
FileInputStream fstream = new FileInputStream(saveFile);
DataInputStream din = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(din));
String strLine;
ArrayList<String> list=new ArrayList<String>();
while ((strLine = br.readLine()) != null){
if(saveFile.endsWith(".xlsx")){
byte[] b=strLine.getBytes();
System.out.println("b: "+b);
bytesToString=new String(b);
System.out.println("bytesToString: "+bytesToString);
list.add(strLine);
}else{
strLine=strLine.toString();
System.out.println("strLine: "+strLine);
list.add(strLine);
}
}
Iterator<String> itr=null;
System.out.println("list: "+list);
int k=0;
for (itr=list.iterator(); itr.hasNext(); ){
String str=itr.next().toString();
String [] splitSt=null;
if(saveFile.endsWith(".csv"))
splitSt =str.split(",");
if(saveFile.endsWith(".txt"))
splitSt =str.split(" ");
if(saveFile.endsWith(".xlsx"))
splitSt =str.split(",");
String id=splitSt[0];
String name=splitSt[1];
String age=splitSt[2];
System.out.println("id: "+id);
System.out.println("name: "+name);
System.out.println("age: "+age);
String query="insert into student values("+id.trim()+",'"+name.trim()+"',"+age.trim()+")";
System.out.println("query: "+query);
if(id.equalsIgnoreCase("sid")) continue;
k+=st.executeUpdate(query);
}
return k;
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return 0;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return 0;
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return 0;
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return 0;
}
finally{
try {
st.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Here the problem is after reading the records line by line the data is not converted to String. And i want to know what is the default seperator of xlsx file. Which seperator we have to use in case of xlsx file. I have used , as a seperator.

Try the following code:
import java.io.*;
import java.sql.*;
import java.text.*;
import org.apache.poi.hssf.usermodel.HSSFSheet ;
import org.apache.poi.hssf.usermodel.HSSFWorkbook ;
import org.apache.poi.hssf.usermodel.HSSFRow ;
import org.apache.poi.hssf.usermodel.HSSFCell ;
import org.apache.poi.ss.usermodel.*;
public class ReadExcelFile{
public static void main(String[] args)throws Exception{
short a=0;
short b=1;
short c=2;
short d=3;
int i=0;
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
Statement st=conn.createStatement();
String value1=" ", value2=" ",value3 =" ", value4 =" ";
String filename ="C:/hello.xlsx";
if(filename != null && !filename.equals("")){
try{
FileInputStream fs =new FileInputStream(filename);
HSSFWorkbook wb = new HSSFWorkbook(fs);
for(int k = 0; k < wb.getNumberOfSheets(); k++){
int j=i+1;
HSSFSheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
for(int r = 1; r < rows; r++){
HSSFRow row = sheet.getRow(r);
int cells = row.getPhysicalNumberOfCells();
HSSFCell cell1 = row.getCell(a);
value1 = cell1.getStringCellValue();
HSSFCell cell2 = row.getCell(b);
value2 = cell2.getStringCellValue();
HSSFCell cell3 = row.getCell(c);
value3 = cell3.getStringCellValue();
HSSFCell cell4 = row.getCell(d);
value4 = cell4.getStringCellValue();
System.out.println(value1+"\t"+value2+"\t"+value3+"\t"+value4);
st.executeUpdate("insert into student(firstname,lastname,email,address) values('"+value1+"','"+value2+"','"+value3+"','"+value4+"')");
}
i++;
}
}
catch(Exception e){
System.out.println(e);
}
}
}
}
If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.
Ask your questions, our development team will try to give answers to your questions.