I am making a program to read data from excel files and store them in tables by their IDs. I have managed to read all the data from excel files as a string and store them in a table. But my project is to store them in table by ascending IDs. I have created the comparator in another class but when i call it in the main class nothing happened.Can someone help me why this is happened? The data that I have to store is like the below:
ID Name Salary
50 christine 2349000
43 paulina 1245874
54 laura 4587894
23 efi 3456457
43 jim 4512878
The codes are below:
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;
import java.util.TreeMap;
import java.util.Map.Entry;
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.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import com.mysql.jdbc.DatabaseMetaData;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class suma {
static LinkedHashMap<String, Integer> tFields = new LinkedHashMap();
static LinkedHashMap[] tData;
public static void main(String[] args) throws Exception {
try {
System.out.println("get the connection");
}
catch( Exception e )
{
System.out.println( "SQLException: " + e.getMessage() );
}
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection) DriverManager.getConnection(
"jdbc:mysql://localhost:3306/kainourgia", "root", "root");
DatabaseMetaData meta = (DatabaseMetaData) con.getMetaData();
ResultSet res = meta.getCatalogs();
System.out.println("List of the databases: ");
while (res.next()){
System.out.println (" " +res.getString(1));
}
String strfullPath = "";
Scanner scanner = new Scanner(System.in);
System.out.println("Please enter the fullpath of the file");
strfullPath = scanner.nextLine();
String file = strfullPath.substring(strfullPath.lastIndexOf('/') + 1);
System.out.println(file.substring(0, file.indexOf('.')));
@SuppressWarnings("unused")
String filename = strfullPath.substring(strfullPath.lastIndexOf('\\') +1);
System.out.println(filename);
String[] parts = filename.split("\\.");
String tablename = parts[0];
System.out.println(tablename);
//Create an ArrayList to store the data read from excel sheet.
List sheetData = new ArrayList();
FileInputStream fis = null;
try {
//Create a FileInputStream that will be use to read the
// excel file.
fis = new FileInputStream(strfullPath);
//Create an excel workbook from the file system
HSSFWorkbook workbook = new HSSFWorkbook(fis);
//Get the first sheet on the workbook.
HSSFSheet sheet = workbook.getSheetAt(0);
//store the data read on an ArrayList so that we can printed the
// content of the excel to the console.
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();
List data = new ArrayList();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
data.add(cell);
}
sheetData.add(data);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis != null) {
fis.close();
}
}
showExcelData(sheetData);
tFields = parseExcelColumnTitles(sheetData);
String str = getCreateTable(con, tablename, tFields);
tData = parseExcelColumnData(sheetData);
ID ids = new ID();
fillTable(con, tablename, tData);
}
private static void showExcelData(List sheetData) {
// LinkedHashMap<String, String> tableFields = new LinkedHashMap();
for (int i = 0; i < sheetData.size(); i++) {
List list = (List) sheetData.get(i);
for (int j = 0; j < list.size(); j++) {
Cell cell = (Cell) list.get(j);
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.print(cell.getNumericCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
System.out.print(cell.getRichStringCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
System.out.print(cell.getBooleanCellValue());
}
if (j < list.size() - 1) {
System.out.print(", ");
}
}
System.out.println("");
}
}
private static LinkedHashMap parseExcelColumnTitles(List sheetData) {
List list = (List) sheetData.get(0);
LinkedHashMap<String, Integer> tableFields = new LinkedHashMap(list.size());
for (int j = 0; j < list.size(); j++) {
Cell cell = (Cell) list.get(j);
tableFields.put(cell.getStringCellValue(), cell.getCellType());
}
return tableFields;
}
private static LinkedHashMap[] parseExcelColumnData(List sheetData) {
LinkedHashMap[] tousRows = new LinkedHashMap[sheetData.size() - 1];
for (int rowCounter = 1; rowCounter < sheetData.size(); rowCounter++) {
List list = (List) sheetData.get(rowCounter);
LinkedHashMap<String, Integer> tableFields = new LinkedHashMap(list.size());
String str;
String[] tousFields = new String[list.size()];
int i = 0;
for (int j = 0; j < list.size(); j++) {
Cell cell = (Cell) list.get(j);
if (cell != null) {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
tableFields.put(String.valueOf(cell
.getNumericCellValue()), cell.getCellType());
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
tableFields.put(cell.getStringCellValue(), cell
.getCellType());
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
tableFields.put(String.valueOf(cell
.getBooleanCellValue()), cell.getCellType());
}
}
}
tousRows[rowCounter - 1] = tableFields;
}
return tousRows;
}
private static String getCreateTable(Connection con, String tablename,
LinkedHashMap<String, Integer> tableFields) {
Iterator iter = tableFields.keySet().iterator();
Iterator cells = tableFields.keySet().iterator();
String str = "";
String[] allFields = new String[tableFields.size()];
int i = 0;
while (iter.hasNext()) {
String fieldName = (String) iter.next();
Integer fieldType = (Integer) tableFields.get(fieldName);
switch (fieldType) {
case Cell.CELL_TYPE_NUMERIC:
str = fieldName + " INTEGER";
break;
case Cell.CELL_TYPE_STRING:
str = fieldName + " VARCHAR(255)";
break;
case Cell.CELL_TYPE_BOOLEAN:
str = fieldName + " INTEGER";
break;
default:
str = "";
break;
}
allFields[i++] = str;
}
try {
Statement stmt = con.createStatement();
try {
String all = org.apache.commons.lang3.StringUtils.join(
allFields, ",");
String createTableStr = "CREATE TABLE IF NOT EXISTS "
+ tablename + " ( " + all + ")";
System.out.println("Create a new table in the database");
stmt.executeUpdate(createTableStr);
} catch (SQLException e) {
System.out.println("SQLException: " + e.getMessage());
System.out.println("SQLState: " + e.getSQLState());
System.out.println("VendorError: " + e.getErrorCode());
}
} catch (Exception e)
{
System.out.println( ((SQLException) e).getSQLState() );
System.out.println( e.getMessage() );
e.printStackTrace();
}
return str;
}
private static void fillTable(Connection con, String fieldname,
LinkedHashMap[] tableData) {
for (int row = 0; row < tableData.length; row++) {
LinkedHashMap<String, Integer> rowData = tableData[row];
Iterator iter = rowData.entrySet().iterator();
String str;
String[] tousFields = new String[rowData.size()];
int i = 0;
while (iter.hasNext()) {
Map.Entry pairs = (Map.Entry) iter.next();
Integer fieldType = (Integer) pairs.getValue();
String fieldValue = (String) pairs.getKey();
switch (fieldType) {
case Cell.CELL_TYPE_NUMERIC:
str = fieldValue;
break;
case Cell.CELL_TYPE_STRING:
str = "\'" + fieldValue + "\'";
break;
case Cell.CELL_TYPE_BOOLEAN:
str = fieldValue;
break;
default:
str = "";
break;
}
tousFields[i++] = str;
}
try {
Statement stmt = con.createStatement();
String all = org.apache.commons.lang3.StringUtils.join(
tousFields, ",");
String sql = "INSERT INTO " + fieldname + " VALUES (" + all
+ ")";
stmt.executeUpdate(sql);
System.out.println("Fill table...");
} catch (SQLException e) {
System.out.println("SQLException: " + e.getMessage());
System.out.println("SQLState: " + e.getSQLState());
System.out.println("VendorError: " + e.getErrorCode());
}
}
// return str;
}
}
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class ID implements Comparable<ID> {
private Long ID;
private String Name;
private Double Salary;
private static LinkedHashMap<String, Integer>myMap;
public Long getID(){
return ID;
}
public void setID(Long ID){
this.ID = ID;
}
public String getName(){
return Name;
}
public void setName(String Name){
this.Name = Name;
}
public Double getSalary(){
return Salary;
}
public void setSalary(Double Salary){
this.Salary = Salary;
}
@Override
public int compareTo(ID o) {
if (o.getID() < this.ID) {
return -1;
} else if (o.getID() > this.ID) {
return 1;
}
return 0;
}
@SuppressWarnings("unused")
private static void listbyID (){
List<Map.Entry<String, Integer>> entryList = new ArrayList<Map.Entry<String, Integer>>(myMap.entrySet());
Collections.sort(entryList,
new Comparator<Map.Entry<String, Integer>>()
{
public int compare(Map.Entry<String, Integer> o1, Map.Entry<String, Integer> o2)
{
return o1.getValue().compareTo(o2.getValue());
}
});
Map<String,Integer> sortedMap = new LinkedHashMap< String, Integer>();
for (Map.Entry<String, Integer>entry : entryList){
sortedMap.put(entry.getKey(),entry.getValue());
}
}
}
Edited by: 998913 on May 8, 2013 5:59 AM