Java JDBC + Excel to Database Code
SQL Table Creation
CREATE TABLE timetable (
id INT AUTO_INCREMENT PRIMARY KEY,
comp_code VARCHAR(20),
course_no VARCHAR(20),
course_title VARCHAR(100),
sec VARCHAR(10),
instructor VARCHAR(100),
room VARCHAR(50),
monday VARCHAR(100),
tuesday VARCHAR(100),
wednesday VARCHAR(100),
thursday VARCHAR(100),
friday VARCHAR(100),
saturday VARCHAR(100)
);
Java Code to Read Excel and Insert into DB
import [Link].*;
import [Link];
import [Link];
import [Link];
import [Link].*;
public class ExcelToTimetableDB {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/your_database";
String username = "your_username";
String password = "your_password";
String excelFilePath = "/mnt/data/TImetable database [Link]";
try (Connection connection = [Link](jdbcURL, username,
password);
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
Workbook workbook = new XSSFWorkbook(inputStream)) {
Sheet sheet = [Link](0);
String sql = "INSERT INTO timetable (comp_code, course_no, course_title,
sec, instructor, room, " +
"monday, tuesday, wednesday, thursday, friday, saturday) VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
PreparedStatement statement = [Link](sql);
for (Row row : sheet) {
if ([Link]() == 0) continue; // Skip header row
Java JDBC + Excel to Database Code
[Link](1, getCellValue([Link](0)));
[Link](2, getCellValue([Link](1)));
[Link](3, getCellValue([Link](2)));
[Link](4, getCellValue([Link](3)));
[Link](5, getCellValue([Link](4)));
[Link](6, getCellValue([Link](5)));
[Link](7, getCellValue([Link](6)));
[Link](8, getCellValue([Link](7)));
[Link](9, getCellValue([Link](8)));
[Link](10, getCellValue([Link](9)));
[Link](11, getCellValue([Link](10)));
[Link](12, getCellValue([Link](11)));
[Link]();
}
[Link]("Excel data has been inserted into the database.");
} catch (Exception e) {
[Link]();
}
}
private static String getCellValue(Cell cell) {
if (cell == null) return "";
switch ([Link]()) {
case STRING:
return [Link]().trim();
case NUMERIC:
return [Link]((int) [Link]());
case BOOLEAN:
return [Link]([Link]());
default:
return "";
}
}
}