I am taking data from xlsx file, The data of xlsx file like below.
1 1 13/11/15 00:00:02 3 42.61 xxxx to xxxx 9 2 2 Axle Truck or Bus
2 1 13/11/15 00:00:12 1 27.11 xxxx to xxxx 3 2 3 Wheeler
3 1 13/11/15 00:00:15 3 22.65 xxxx to xxxx 7 2 LMV 2 Axle Light Motor Vehicle
4 1 13/11/15 00:00:21 2 18.76 xxxx to xxxx 7 2 LMV 2 Axle Light Motor Vehicle
5 1 13/11/15 00:00:21 3 18.76 xxxx to xxxx 9 2 2 Axle Truck or Bus
from this data i am reading row wise and inserting into DataBase by using below code.
but the problem is i am not able to get date of 3rd column from xlsx file but am getting remaining to print.
below one is my code:
processOneSheet("C:/Users/Penchalaiah/Desktop/New folder/"+hs.getAttribute("filename1"));
System.out.println(hs.getAttribute("filename1"));
System.out.println("clossing the connnection");
ps.close();
con1.close();
System.out.println("execution completed");
//request.setAttribute("message","THE XLSX DATA TRANSFERRED SUCCEFULLY");
// request.getRequestDispatcher("/HomePage.jsp").forward(request, response);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
/*try {
ps.close();
con1.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
}
public void processOneSheet(String filename) throws Exception {
System.out.println("executing Process Method");
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader( pkg );
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// To look up the Sheet Name / Sheet Order / rID,
// you need to process the core Workbook stream.
// Normally it's of the form rId# or rSheet#
InputStream sheet2 = r.getSheet("rId2");
System.out.println("Sheet2");
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
ContentHandler handler = new SheetHandler(sst);
parser.setContentHandler(handler);
return parser;
}
/**
* See org.xml.sax.helpers.DefaultHandler javadocs
*/
private class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
String TxnNo;
String SurveyId;
Date date;
String Time;
String Lane;
String Avspeed;
String Direction;
String VehicleCategory;
String Axle_count;
String Vehicle;
int i = 1;
private SheetHandler(SharedStringsTable sst) {
this.sst = sst;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => cell
if(name.equals("c")) {
// Print the cell reference
//System.out.print(attributes.getValue("r") + " - ");
// Figure out if the value is an index in the SST
String cellType = attributes.getValue("t");
if(cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// Clear contents cache
lastContents = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
// Process the last contents as required.
// Do now, as characters() may be called more than once
if(nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
nextIsString = false;
}
// v => contents of a cell
// Output after we've seen the string contents
if(name.equals("v")) {
if(i == 1){
TxnNo = lastContents;
}
if(i == 2){
SurveyId = lastContents;
}
if(i == 3){
try {
//@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
date = new SimpleDateFormat("dd/MM/yyyy",Locale.ENGLISH).parse(lastContents);
//@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
System.out.println("The Date is: "+date);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//System.out.print(lastContents+"("+i+")");
}
if(i == 4){
Time = lastContents;
//System.out.print(lastContents+"("+i+")");
}
if(i == 5){
Lane = lastContents;
//System.out.print(lastContents+"("+i+")");
}
if(i == 6){
Avspeed = lastContents;
//System.out.print(lastContents+"("+i+")");
}
if(i == 7){
Direction = lastContents;
//System.out.print(lastContents+"("+i+")");
}
if(i == 8){
VehicleCategory = lastContents;
//System.out.print(lastContents+"("+i+")");
}
if(i == 9){
Axle_count = lastContents;
//System.out.print(lastContents+"("+i+")");
}
if(i == 10){
Vehicle = lastContents;
//System.out.print(lastContents+"("+i+")");
//System.out.print(lastContents+"("+i+")");
insertInToDb(TxnNo, SurveyId, date, Time, Lane, Avspeed,Direction, VehicleCategory, Axle_count, Vehicle);
i = 0;
}
i++;
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
lastContents += new String(ch, start, length);
}
}
int gcc = 0;
public void insertInToDb(String TxnNo,String SurveyId,Date date, String Time,String Lane,String Avspeed,String Direction, String VehicleCategory, String Axle_count, String Vehicle){
try {
ps.setString(1, TxnNo);
ps.setString(2, SurveyId);
ps.setDate(3, (java.sql.Date) date);
ps.setString(4, Time);
ps.setString(5, Lane);
ps.setString(6, Avspeed);
ps.setString(7, Direction);
ps.setString(8, VehicleCategory);
ps.setString(9, Axle_count);
ps.setString(10, Vehicle);
ps.setString(11, (String)hs.getAttribute("zoneId1"));
ps.setString(12, (String)hs.getAttribute("location1"));
ps.executeUpdate();
i am getting exception in between //@@@@@@@@@@@@
//@@@@@@@@@@@
of code shown above
when I am printing date column of string I am getting 42321 this value.
how to convert to date of that column value and insert to database?
