· 10 years ago · Apr 22, 2016, 10:45 PM
1/**
2 * Parses through the Customer Data excel file and creates objects for each
3 * Customer and Major within the excel file and adds them to the database.
4 *
5 * @author Donald Kirk
6 * @version 1.0
7 *
8 */
9public class Runner {
10
11 /**
12 * Create the main method to execute the program.
13 *
14 * @param args
15 * @throws IOException
16 * file exception
17 */
18 public static void main(String[] args) throws IOException {
19
20 // Create an ArrayList to store the data read from excel sheet.
21 List<List<XSSFCell>> sheetData = new ArrayList<List<XSSFCell>>();
22
23 try {
24 // Create an excel workbook from the file system.
25 XSSFWorkbook workbook = new XSSFWorkbook("Lab5Dataset.xlsx");
26
27 // Get the first sheet on the workbook.
28 XSSFSheet sheet = workbook.getSheetAt(0);
29
30 /*
31 * When we have a sheet object in hand we can iterator on each
32 * sheet's rows and on each row's cells. We store the data read on
33 * an ArrayList so that we can printed the content of the excel to
34 * the console.
35 */
36 Iterator<?> rows = sheet.rowIterator();
37 while (rows.hasNext()) {
38 XSSFRow row = (XSSFRow) rows.next();
39 Iterator<?> cells = row.cellIterator();
40
41 List<XSSFCell> data = new ArrayList<XSSFCell>();
42 while (cells.hasNext()) {
43 XSSFCell cell = (XSSFCell) cells.next();
44 data.add(cell);
45 }
46
47 sheetData.add(data);
48 }
49 } catch (IOException e) {
50 e.printStackTrace();
51 }
52 showExelData(sheetData);
53 excelToDatabaseTable();
54
55 }
56
57 /**
58 * Iterates the data and print it out to the console.
59 *
60 * @param sheetData
61 * being passed in.
62 */
63 private static void showExelData(List<List<XSSFCell>> sheetData) {
64
65 for (int i = 0; i < sheetData.size(); i++) {
66 List<?> list = (List<?>) sheetData.get(i);
67 for (int j = 0; j < list.size(); j++) {
68 Cell cell = (Cell) list.get(j);
69 if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
70 } else if (cell.getCellType() == Cell.CELL_TYPE_STRING
71 || cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
72 } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
73 }
74 if (j < list.size() - 1) {
75 }
76 }
77 }
78 }
79
80 /**
81 * Import the excel file into the Database.
82 */
83 private static void excelToDatabaseTable() {
84 Statement stmt = null;
85 Statement stmt2 = null;
86 Statement stmt3 = null;
87
88 try {
89
90 /*
91 * CREATE DATABASE
92 */
93 // Register JDBC driver.
94 Class.forName("com.mysql.jdbc.Driver");
95 // Open a connection.
96 Connection con = DriverManager.getConnection("jdbc:mysql://localhost/", "root", "root");
97 // Execute a query
98 System.out.println("Creating database...");
99 stmt = (Statement) con.createStatement();
100 String database = "CREATE DATABASE IF NOT EXISTS Lab5";
101 stmt.executeUpdate(database);
102 System.out.println("Database created successfully...");
103 stmt.close();
104 con.close();
105
106 /*
107 * CREATE TABLE IN DATABASE
108 */
109 // Open a connection.
110 Connection con2 = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/Lab5", "root", "root");
111
112 // Execute a query
113 System.out.println("Creating customer table...");
114 stmt2 = (Statement) con2.createStatement();
115 String table = "CREATE TABLE IF NOT EXISTS `Customer` (`name` varChar(255) DEFAULT NULL, `phone` varChar(255) DEFAULT NULL,`email` varChar(255) DEFAULT NULL,`id_num` varChar(255),`street` varChar(255) DEFAULT NULL,`city` varChar(255) DEFAULT NULL,`zip` varChar(255) DEFAULT NULL,`region` varChar(255) DEFAULT NULL,`lat_long` varChar(255) DEFAULT NULL,`gpa` varChar(255) DEFAULT NULL,`major` varChar(255) DEFAULT NULL,`title` varChar(255) DEFAULT NULL,PRIMARY KEY (`id_num`))";
116 stmt2.executeUpdate(table);
117 stmt2.close();
118
119 /*
120 * CREATE MAJOR TABLE
121 */
122 Connection con3 = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/Lab5", "root", "root");
123 System.out.println("Creating major table...");
124 stmt3 = (Statement) con3.createStatement();
125 String majorTable = "CREATE TABLE IF NOT EXISTS `Major` (`major` varChar(255) DEFAULT 'N/A', `classification` varChar(255) DEFAULT 'N/A',`degree_level` varChar(255) DEFAULT 'N/A',`college` varChar(255) DEFAULT 'N/A',PRIMARY KEY (`major`,`classification`,`degree_level`,`college`))";
126 stmt3.executeUpdate(majorTable);
127 stmt3.close();
128
129 /*
130 * ACCESS FILE
131 */
132 PreparedStatement pstm = null;
133 PreparedStatement pstm2 = null;
134 OPCPackage pkg = OPCPackage.open(new File("Lab5Dataset.xlsx"));
135 POIXMLProperties props = new POIXMLProperties(pkg);
136
137 XSSFWorkbook wb = new XSSFWorkbook("Lab5Dataset.xlsx");
138 XSSFSheet sheet = wb.getSheetAt(0);
139 XSSFSheet majorSheet = wb.getSheetAt(1);
140 Row row;
141
142 for (int i = 1; i <= sheet.getLastRowNum(); i++) {
143 DataFormatter formatter = new DataFormatter(Locale.US);
144 row = (Row) sheet.getRow(i);
145 if (row != null) {
146 String name = formatter.formatCellValue(row.getCell(0));
147 String phone = formatter.formatCellValue(row.getCell(1));
148 String email = formatter.formatCellValue(row.getCell(2));
149 String id_num = formatter.formatCellValue(row.getCell(3));
150 String street = formatter.formatCellValue(row.getCell(4));
151 String city = formatter.formatCellValue(row.getCell(5));
152 String zip = formatter.formatCellValue(row.getCell(6));
153 String region = formatter.formatCellValue(row.getCell(7));
154 String lat_long = formatter.formatCellValue(row.getCell(8));
155 String gpa = formatter.formatCellValue(row.getCell(9));
156 String major = row.getCell(10).getRichStringCellValue().getString();
157 String title = formatter.formatCellValue(row.getCell(11));
158
159 String sql = "INSERT INTO Customer (name, phone, email, id_num, street, city,zip,region,lat_long,gpa,major,title) VALUES (?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE `name`=?,`phone`=?,`email`=?,`street`=?,`city`=?,`zip`=?,`region`=?,`lat_long`=?,`gpa`=?,`major`=?,`title`=?";
160 pstm = con2.prepareStatement(sql);
161
162 // Set the Insert
163 pstm.setString(1, name);
164 pstm.setString(2, phone);
165 pstm.setString(3, email);
166 pstm.setString(4, id_num);
167 pstm.setString(5, street);
168 pstm.setString(6, city);
169 pstm.setString(7, zip);
170 pstm.setString(8, region);
171 pstm.setString(9, lat_long);
172 pstm.setString(10, gpa);
173 pstm.setString(11, major);
174 pstm.setString(12, title);
175
176 // Set the Update
177 pstm.setString(13, name);
178 pstm.setString(14, phone);
179 pstm.setString(15, email);
180 pstm.setString(16, street);
181 pstm.setString(17, city);
182 pstm.setString(18, zip);
183 pstm.setString(19, region);
184 pstm.setString(20, lat_long);
185 pstm.setString(21, gpa);
186 pstm.setString(22, major);
187 pstm.setString(23, title);
188
189 pstm.executeUpdate();
190 pstm.close();
191
192 }
193 }
194
195 for (int i = 1; i <= majorSheet.getLastRowNum(); i++) {
196 DataFormatter formatter = new DataFormatter(Locale.US);
197 row = (Row) majorSheet.getRow(i);
198 if (row != null) {
199 String major = formatter.formatCellValue(row.getCell(0));
200 String classification = formatter.formatCellValue(row.getCell(1));
201 String degreeLevel = formatter.formatCellValue(row.getCell(2));
202 String college = formatter.formatCellValue(row.getCell(3));
203
204 String sql = "INSERT INTO Major (major, classification, degree_level, college) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE `major`=?,`classification`=?,`degree_level`=?,`college`=?";
205
206 // Set on the Insert.
207 pstm2 = con3.prepareStatement(sql);
208 pstm2.setString(1, major);
209 pstm2.setString(2, classification);
210 pstm2.setString(3, degreeLevel);
211 pstm2.setString(4, college);
212
213 // Set on the Update.
214 pstm2.setString(5, major);
215 pstm2.setString(6, classification);
216 pstm2.setString(7, degreeLevel);
217 pstm2.setString(8, college);
218
219 pstm2.executeUpdate();
220 pstm2.close();
221
222 }
223 }
224
225 con2.close();
226 con3.close();
227 pkg.close();
228 System.out.println("Success import excel to mysql table");
229 } catch (Exception e) {
230 e.printStackTrace();
231 }
232
233 }
234
235}