ExcelToDb Project

by discus12

Задача:
Имеем excel таблицу с данными (автогенерация). Распарсить данные из Excel и сохранить в MySQL. Предварительно создав таблицы, использовать PRIMARY KEY, AUTO_INCREMENT.
Все файлы будут доступны в конце проекта во вложении. В excel удобная функция для автогенерации: =СЛУЧМЕЖДУ(“01.01.2008″;”31.12.2018”)

Использовал:

Spring context Spring Jdbc NamedParameterJdbc
Apache POI MySQL MAMP

 

Основной момент в этой задаче, использование Apache POI API для парсинга из Excel в Java.
Импортируем необходимые библиотеки для работы с Excel.

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;

Вычитываем файл и указываем страницу (getSheetAt) с которой будем работать.

HSSFWorkbook work  = new HSSFWorkbook(new FileInputStream(EXCEL_FILE));
HSSFSheet hssfSheet = work.getSheetAt(0);

Проходим в цикле (for (int rn=1; rn<=200; rn++)). Начиная с второй строки rn=1, так как первая строка = 0 и она содержит названия столбцов.

Далее проходим по ячейкам.

HSSFRow row2 = hssfSheet.getRow(rn);
// processing row
HSSFCell first_name = row2.getCell(0);
HSSFCell second_name = row2.getCell(1);
HSSFCell phone = row2.getCell(2);
HSSFCell phone_code = row2.getCell(3);

Есть интересный момент. Работа с форматом даты. Выбрал решение DateFormat с использованием pattern.

DateFormat format = new SimpleDateFormat("dd-MMM-yyyy");
Date formatBirthday = format.parse(birthday.toString());
 
//12.08.1968
DateFormat format2 = new SimpleDateFormat("yyyy");
Date formatYear = format2.parse(year.toString());

Затем, используем Spring.

ClassPathXmlApplicationContext contextdb = new ClassPathXmlApplicationContext("db.xml");
NamedParameterJdbcTemplate nquOracle = new NamedParameterJdbcTemplate(contextdb.getBean(DataSource.class));

Собираем в HashMap коллекцию. Для удобства работы в дальнейшем с NamedParameterJdbcTemplate и обновлением БД.
Немного учебника:
Класс HashMap использует хеш-таблицу для хранения карточки, обеспечивая быстрое время выполнения запросов get() и put() при больших наборах. Класс реализует интерфейс Map (хранение данных в виде пар ключ/значение). Ключи и значения могут быть любых типов, в том числе и null. При этом все ключи обязательно должны быть уникальны, а значения могут повторяться. Данная реализация не гарантирует порядка элементов.

HashMap<String, Object> param = new HashMap<>();
param.put("first_name", first_name.toString());
param.put("second_name", second_name.toString());
param.put("phone", phone.toString());
param.put("phone_code", phone_code.toString());

Заполняем таблицу.

String insertSql = "INSERT INTO PERSON_TEMP (id, First_name, Second_name, Phone, Phone_code, Passport_number, Born_country, " +
     "Birthday, Email, Company, Job, Salary, State, Car_mark, Car_model, Vin, Power, Year,  Car_price, Hire_date) VALUES " +
     "(id, :first_name, :second_name, :phone, :phone_code, :passport_number, :born_country, :formatBirthday, :email, :company, :job, :salary, :state, :car_mark, :car_model, :vin, :power,  :formatYear, :car_price, :hire_date)";
      // execute insert + count number of records / records processed by the executed query
      nquOracle.update(insertSql, param);
      update ++;

Поработаем с созданием тестовых таблиц. Пригодятся в дальнейшем для создания и тестирования web service. Несколько скриптов.

-- Remove old tables
DROP TABLE IF EXISTS PERSON_TEMP;
DROP TABLE IF EXISTS Documents;
DROP TABLE IF EXISTS Vehicle;
DROP TABLE IF EXISTS Employer;
DROP TABLE IF EXISTS Contacts;
DROP TABLE IF EXISTS Person;
 
-- Create TABLE
CREATE TABLE PERSON_TEMP (
  id INT(11) NOT NULL AUTO_INCREMENT,
  First_name VARCHAR(150),
  Second_name VARCHAR(150),
  Phone VARCHAR(150),
  Phone_code INT(150),
  Passport_number INT(150),
  Born_country VARCHAR(150),
  Birthday DATE,
  Email VARCHAR(150),
  Company VARCHAR(150),
  Job VARCHAR(150),
  Salary INT(150),
  State VARCHAR(150),
  Car_mark VARCHAR(150),
  Car_model VARCHAR(150),
  Vin VARCHAR(150),
  POWER INT(150),
  YEAR DATE,
  Car_price INT(150),
  Hire_date DATE,
  PRIMARY KEY (id)
);
 
-- Create other table
CREATE TABLE Person (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  First_name VARCHAR(150),
  Second_name VARCHAR(150),
  Birthday DATE
);
 
CREATE TABLE Contacts (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Phone VARCHAR(150),
  Phone_code INT(150),
  Email VARCHAR(150),
  FOREIGN KEY (id) REFERENCES Person(id)
);
 
CREATE TABLE Employer (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Company VARCHAR(150),
  Job VARCHAR(150),
  Hire_date DATE,
  Salary INT(150),
  FOREIGN KEY (id) REFERENCES Person(id)
);
 
CREATE TABLE Vehicle (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Car_mark VARCHAR(150),
  Car_model VARCHAR(150),
  Vin VARCHAR(150),
  POWER INT(150),
  YEAR DATE,
  Car_price INT(150),
  FOREIGN KEY (id) REFERENCES Person(id)
);
 
CREATE TABLE Documents (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Passport_number INT(150),
  Born_country VARCHAR(150),
  State VARCHAR(150),
  FOREIGN KEY (id) REFERENCES Person(id)
);

После заполнения таблицы PERSON_TEMP, раскидываем данные по нашим тестовым таблицам.

INSERT INTO Person (id, First_name, Second_name, Birthday)
  SELECT id, First_name, Second_name, Birthday FROM PERSON_TEMP;
 
INSERT INTO Contacts (id, Phone, Phone_code, Email)
  SELECT id, Phone, Phone_code, Email FROM PERSON_TEMP;
 
INSERT INTO Employer (id, Company, Job, Hire_date, Salary)
  SELECT id, Company, Job, Hire_date, Salary FROM PERSON_TEMP;
 
INSERT INTO Vehicle (id, Car_mark, Car_model, Vin, POWER, YEAR, Car_price)
  SELECT id, Car_mark, Car_model, Vin, POWER, YEAR, Car_price FROM PERSON_TEMP;
 
INSERT INTO Documents (id, Passport_number, Born_country, State)
  SELECT id, Passport_number, Born_country, State FROM PERSON_TEMP;

В итоге имеем 5 хороших тестовых таблиц для написания тестов и использования в будущих проектов.

SELECT * FROM Person;
SELECT * FROM Contacts;
SELECT * FROM Employer;
SELECT * FROM Vehicle;
SELECT * FROM Documents;

 
Attached files:
Test data – Excel file
Github ExcelToDbProject

Releated Post