Подробное руководство по использованию easyexcel для импорта и экспорта данных Excel в базу данных и из базы данных

@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
private Date date;
```Таким образом, аннотация `@JsonFormat` позволяет задать формат даты и часового пояса. ~~В данном случае для быстрого отображения без перезапуска проекта я использовал фронтенд. Формат данных, возвращаемых фронтендом, — это строка JSON. Поскольку это строка, её можно разбить, используя метод slice(). Как показано на следующем рисунке, после list[i].birthday добавляем метод slice(), чтобы получить нужный формат даты. Таким образом, дата рождения будет иметь формат, аналогичный базе данных. Также можно попробовать обрезать строку на серверной стороне.~~   ![Обработка даты](README.assets/обработка_даты.png)

   ![Срез](README.assets/slice.png)

   

8.  ##### Ошибка при импорте Excel из-за неправильного формата данных

    В пункте 7 упоминалось, что ошибка при импорте возникает из-за неправильного типа данных, определенного на сервере. В данном пункте мы рассмотрим ошибки, связанные с неправильным форматом данных в Excel. Основной проблемой является дата, поскольку другие типы данных редко вызывают ошибки. Если столбцы таблицы Excel не соответствуют столбцам сущности, возникают ошибки, такие как отсутствие необходимых столбцов или несоответствие типов данных между таблицей Excel и сущностью. (Я действительно сталкивался с такой проблемой, когда таблица, отправленная мне другим человеком, содержала скрытые столбцы без ширины, что привело к отсутствию необходимых столбцов и невозможности импорта. Только после того, как я выбрал все столбцы и адаптировал их ширину, я обнаружил скрытые столбцы.)

    При открытии Excel в WPS, типы данных даты могут быть длинной датой, короткой датой или временем. Также можно записать дату в виде текста. Давайте сравним эти данные на следующих рисунках.

    ![Дата и текст](README.assets/Дата_и_текст.png)

    ![Ошибка текста](README.assets/Ошибка_текста.png)    Как видно из первого рисунка, это короткая дата, формат данных — yyyy/MM/dd. Остальные две строки имеют текстовый формат, текстовый формат yyyy-MM-dd можно импортировать, а формат даты yyyy/MM/dd — нет.    ![Текстовое время](README.assets/Текстовое_время.png)

    Далее добавим данные в текстовом формате yyyy-MM-dd hh:mm:ss и добавим данные в формате времени yyyy/MM/dd hh:mm:ss, включая год, месяц и день 1999/10/9. Как видно, после преобразования в таблице отображается только время, что указывает на успешное преобразование. ![Успешный импорт](README.assets/Успешный_импорт.png) Можно увидеть, что все добавленные данные успешно добавлены.

Формат даты может быть длинным, но не коротким. Длинный формат можно усечь до необходимой части, но короткий формат не может быть автоматически дополнен, поэтому возникают ошибки. Например, формат yyyy-MM-dd может быть записан как 2021-11-11, или как 2021-11-11 12:23:34, но не может быть записан как 2021 или 2021-11.

Длинный формат даты — это yyyy年MM月dd日, как показано на рисунке. Даже если используются китайские символы, такие как "год", "месяц" и "день", формат даты всё равно успешно сохраняется, так как на самом деле это yyyy/MM/dd.

![Длинный формат даты](README.assets/长日期.png)

![Успешное сохранение длинного формата даты](README.assets/长日期成功.png)

Импорт выполнен успешно!

9. ##### Всепоглощающий string

В вышеприведённом примере используется сущность с типом Date. В реальной практике, если не учитывать строгое соответствие формату базы данных, можно использовать тип String для описания параметров сущности.![Успешное сохранение строки](README.assets/string成功.png)

![Всепоглощающая строка](README.assets/万能string.png)

Время больше не используется как Date, а как String, что делает его всепоглощающим. Ранее неудачные текстовые форматы, такие как yyyy/MM/dd, теперь успешно сохраняются.

![Успешное сохранение ранее неудачных текстовых форматов](README.assets/失败文本成功.png)

![Текстовый формат строки](README.assets/string文本.png)

**Учитывая, что такие построчные демонстрации могут быть не очень наглядными, я составил таблицу и очистил данные базы данных для наблюдения за вставкой данных:**

![Всепоглощающая строка](README.assets/1647851423771.png)

Конечно, чтобы избежать неизвестных ошибок, я, как начинающий, вынужден использовать простой метод и тестировать данные по одной.

java.sql.date 	все ошибки

![1647852964010](README.assets/1647852964010.png)

java.util.date 	первые пять записей успешно добавлены, последующие данные не могут быть распознаны

![1647853367944](README.assets/1647853367944.png)

![1647853407791](README.assets/1647853407791.png)

![1647853472185](README.assets/1647853472185.png)

![1647853542482](README.assets/1647853542482.png)

![1647853669235](README.assets/1647853669235.png)

![1647853723728](README.assets/1647853723728.png)

![1647853965758](README.assets/1647853965758.png)

Строка 	текстовые форматы с китайскими символами не могут быть преобразованы

![1647854092217](README.assets/1647854092217.png)

С формата "2000年9月2日" начинаются ошибки, что приводит к невозможности сохранения данных. Однако строка может распознать все данные о времени, но текстовые форматы с китайскими символами не могут быть успешно преобразованы.Тогда почему длинный формат даты с китайскими символами успешно преобразуется? На самом деле, длинный формат даты в Excel, хотя и содержит китайские символы, по сути является форматом yyyy/MM/dd.   ![1647940745168](README.assets/1647940745168.png)

   ![1647854423876](README.assets/1647854423876.png)

   ![1647854549591](README.assets/1647854549591.png)

   ![1647854628948](README.assets/1647854628948.png)

   ![1647854863154](README.assets/1647854863154.png)

   Ниже приведен результат, полученный после хранения данных с использованием String, аналогичен предыдущему рисунку.

   ![1647855614267](README.assets/1647855614267.png)

   

   ###### Заключение:

   String, хотя и универсален, но не является истинно универсальным. Важно учитывать формат времени, не использовать кириллицу, а также использовать формат yyyy/MM/dd для длинных дат в Excel.

   Кроме того, следует учитывать java.util.Date и тип String. При чтении данных из базы данных поле util.Date автоматически заполняется часами, минутами и секундами, но при чтении и записи данных из Excel в базу данных автоматическое заполнение не происходит. Длина даты должна быть больше, чем короткая. В сущности класса есть аннотация @DateTimeFormat(value = "yyyy-MM-dd"), которая определяет формат времени для преобразования. Дата должна быть длиннее этого формата, а не короче. Если формат определен как yyyy-MM-dd, то в Excel можно использовать 2000-10-10 00:00:00, но не 2000-10. Короткая дата по сравнению с определенным форматом времени приведет к ошибке.   В случае, когда в базе данных хранятся числа, а в таблице — текст, все равно можно использовать String. Например, поле пола в базе данных хранится как int, где 0 означает "мужчина", а 1 — "женщина". При экспорте в Excel требуется преобразование текста "мужчина" и "женщина". Это можно сделать с помощью String, получив данные и используя методы сущности для преобразования в "мужчина" и "женщина". При импорте также используется String для получения данных, а затем с помощью if-условий преобразование "мужчина" и "женщина" в числа 0 и 1, которые затем сохраняются в базе данных. В этом случае String в сущности соответствует тексту в Excel, а не числу в базе данных. Если String соответствует числу в базе данных, то при чтении из Excel возникнет ошибка, так как поле int не может принимать текст "мужчина" и "женщина". Если String не преобразован, то оно может принять текст "мужчина" и "женщина" из Excel, но при сохранении этих текстов в базу данных возникнет ошибка из-за типа int. Преобразование String в "0" и "1" позволит сохранить данные без ошибок.

   

10. ##### Экспорт из нескольких баз данных в одну таблицу (несколько листов)

    На основе существующего Excel-таблицы создадим еще одну таблицу phone, которая будет представлять мобильные телефоны. Пользователь может иметь один или несколько телефонов, а также может не иметь телефонов. Данные таблиц user и phone изменены следующим образом:    ![1647941100648](README.assets/1647941100648.png)    ![1647941120847](README.assets/1647941120847.png)

    Мы реализовали аналогичную систему для телефонов, как и для пользователей, сначала реализовав отдельное экспортирование пользователей и телефонов, а затем экспортирование в один файл с несколькими листами. Результаты представлены ниже:

    ![1647943072937](README.assets/1647943072937.png)

    ![1647943113832](README.assets/1647943113832.png)

    Методы utils представлены ниже:

    ```java
    public static void allExportToWeb(HttpServletResponse response, String excelName, String sheetName, String sheetName1, List data1, List data2) throws Exception {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // Здесь URLEncoder.encode можно использовать для предотвращения кириллических ошибок
            excelName = URLEncoder.encode(excelName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new Custemhandler()).build();
            WriteSheet writeSheet1 = EasyExcel.writerSheet(0, sheetName).head(User.class).build();
            WriteSheet writeSheet2 = EasyExcel.writerSheet(1, sheetName1).head(Phone.class).build();
            excelWriter.write(data1, writeSheet1);
            excelWriter.write(data2, writeSheet2);
            excelWriter.finish();
        }
    ```

    Прямое вызов в контроллере:    ```java
    // Экспорт в несколько листов
        @GetMapping("/exportWeb2")
        public void exportToWeb2(HttpServletResponse response) {
            try {
                UserphoneUtils.allExportToWeb(response, "Таблица пользователей и телефонов",   // имя Excel-таблицы
                        "Лист пользователей", "Лист телефонов",           // имена листов Excel-таблицы
                        userService.getAll(), phoneService.getAllPhones());    // данные для двух листов
            } catch (Exception e) {
                log.error("Ошибка при экспорте отчета:", e);
            }
        }
    ```    Поскольку экспортирование нескольких листов означает запись данных в несколько листов, это выглядит следующим образом:

    ```java
    WriteSheet writeSheet1 = EasyExcel.writerSheet(0, sheetName).head(User.class).build();
    WriteSheet writeSheet2 = EasyExcel.writerSheet(1, sheetName1).head(Phone.class).build();
    ```

    Тогда мы все листы запишем в лист sheet0, это значит, что мы импортируем в один лист sheet? Очевидно, что нет.

    Вы ожидаете такой результат: Zhao Feiyan имеет два телефона, Zhang San имеет один телефон, Li Si имеет два телефона. Экспортированный excel-таблица выглядит следующим образом:

    ![shili1](README.assets/shili1.png)

    Однако, если вставить все в один лист, результат может быть таким (этот результат получен мной после тестирования, другие случаи тоже возможны), и данные могут быть потеряны, а даже заголовки могут отсутствовать:

    ![shijijieguo](README.assets/shijijieguo.png)

    Поэтому этот метод не работает!

    

11. ##### Экспорт из нескольких баз данных в один лист (один sheet)

    В действительности, импорт нескольких таблиц в один лист можно выполнить с помощью объединения таблиц. С помощью поля user_id в таблицах user и phone можно выполнить объединение таблиц, sql-запрос будет таким:

    ```sql
    SELECT a.*,b.phone_name,b.brand,b.phone_color,b.ram,b.rom FROM user a,phone b WHERE a.id = b.user_id
    ```

    Результат запроса будет таким:

    ![1647945855568](README.assets/1647945855568.png)    На основе этих полей можно создать сущностные классы, которые будут принимать такие данные. Если сущностные классы созданы таким образом, то естественно, что можно экспортировать данные в один лист. Подробные методы смотри в коде, результат экспорта в один лист будет таким:    ![1647946019829](README.assets/1647946019829.png)

    

12. ##### Импорт из одного листа в несколько баз данных

    Импорт из одного листа в одну базу данных довольно прост, но импорт из одного листа в несколько баз данных сложнее. Сначала нам нужно создать сущностные классы, соответствующие вашим таблицам. Конечно, в пункте 11 мы экспортировали таблицы на основе уже существующих сущностных классов, поэтому нам не нужно создавать новые классы. Если таблицы не соответствуют существующим классам, нам нужно будет создать новые классы для приема данных из Excel-таблицы.

    Когда у нас была только таблица user, метод чтения данных пользователя из Excel-таблицы и сохранения их был таким:

    ![1647946500123](README.assets/1647946500123.png)    список представляет собой данные, полученные из Excel-таблицы, и теперь каждая запись в списке будет выглядеть следующим образом    !  [1647946640958](README.  assets/1647946640958.  png)
       Включает в себя пользователя и телефон, нам нужно извлечь эти данные и сохранить их отдельно, чтобы реализовать хранение в нескольких таблицах базы данных.
       Методы следующие:
       ```java
      private void saveData() {
              log.  info("{}条数据，开始存储数据库！  ", list.  size());
              List<User> users = new ArrayList<>();   //Создаем список объектов User для сохранения
              List<Phone> phones = new ArrayList<>();   //Создаем список объектов Phone для сохранения
              for (int i = 0; i < list.  size(); i++) {
                  try {
                      User user = new User();
                      user.  setName(list.  get(i).  getName()); //Извлекаем необходимые данные пользователя
                      user.  setSex(list.  get(i).  getSex());
                      user.  setAge(list.  get(i).  getAge());
                      user.  setBirthday(list.  get(i).  getBirthday());
                      users.  add(user);                    //Добавляем в список
                      Phone phone = new Phone();
                      phone.  setPhone_name(list.  get(i).  getPhone_name()); //Извлекаем необходимые данные телефона
                      phone.  setBrand(list.  get(i).  getBrand());
                      phone.  setPhone_color(list.  get(i).  getPhone_color());
                      phone.  setRam(list.  get(i).  getRam());
                      phone.  setRom(list.  get(i).  getRom());
                      phone.  setUser_id(list.  get(i).  getUser_id());
                      phones.  add(phone);                  //Добавляем в список
                  } catch (Exception e) {
                      e.  printStackTrace();
                  }
              }
              if (!  CollectionUtils.  isEmpty(users)) {
                  userphoneService.```java
saveUsers(users);   // Если список пользователей не пуст, сохраняем их
                    }
                    if (!CollectionUtils.isEmpty(phones)) {
                        userphoneService.savePhones(phones);   // Если список телефонов не пуст, сохраняем их
                    }
                    log.info("Сохранение в базу данных завершено успешно!");
                }
            ```
            Создаем списки и соответствующие им объекты для извлечения необходимых данных, затем вызываем методы сохранения для каждого типа данных. Однако следует учитывать, что здесь приведены четыре данных, однако фактически пользователей только три, а из таблицы Excel получены четыре пользователя. В реальном использовании необходимо удалять дубликаты, но для удобства я этого не сделал. Кроме того, поскольку я установил автоматическое увеличение id, вставляемые данные не содержат id, что приводит к повторному хранению уже существующих данных. В реальном использовании необходимо удалять дубликаты и учитывать id.

13. ##### Импорт нескольких листов в несколько таблиц базы данных
Обработка данных из Excel-файла с несколькими листами может быть сложной, требуя создания нового Listener. В целом, процесс похож на обработку данных из одного листа, но есть некоторые небольшие различия.
```Контроллер:

```java
// Импорт файла Excel с несколькими листами с помощью EasyExcel
@PostMapping("/uploadmany")
@ResponseBody
public String uploadMany(MultipartFile file) throws IOException {
    ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build();
    // Чтение листа 0
    ReadSheet sheet0 = EasyExcel.readSheet(0).head(User.class).registerReadListener(new UpDataListener<User>(userService)).build();
    // Чтение листа 1
    ReadSheet sheet1 = EasyExcel.readSheet(1).head(Phone.class).registerReadListener(new UpDataListener<Phone>(phoneService)).build();
    excelReader.read(sheet0, sheet1);
    // Не забудьте закрыть объект, иначе временные файлы могут заполнить ваш диск
    excelReader.finish();
    return "Файл успешно загружен";
}
``````java
@Slf4j
public class UpDataListener<T> extends AnalysisEventListener<T> {
    private UserService userService;
    private PhoneService phoneService;

    public UpDataListener(UserService userService) {
        this.userService = userService;
    }

    public UpDataListener(PhoneService phoneService) {
        this.phoneService = phoneService;
    }

    /**
     * Каждые 5 записей сохраняются в базу данных. В реальных условиях можно сохранять каждые 3000 записей,
     * после чего очищать список для удобства сборки мусора.
     */
    private static final int BATCH_COUNT = 5;
    List<T> list = new ArrayList<T>();
    private Integer sheetNo; // Получение номера листа sheetNo, также можно получить имя листа sheetName

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        log.info("Обработана одна запись:{}", JSON.toJSONString(t));
        list.add(t);
        sheetNo = analysisContext.readSheetHolder().getSheetNo();
        // Если размер списка достиг BATCH_COUNT, необходимо сохранить данные в базе данных, чтобы избежать
        // переполнения памяти (OOM).
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // Здесь также необходимо сохранить данные, чтобы убедиться, что все оставшиеся данные были сохранены в базе данных.
        if (list.size() != 0) {
            saveData();
        }
        log.info("Все данные обработаны! ");
    }

    /**
     * Сохранение данных в базу данных
     */
    private void saveData() {
        log.info("Данные сохранены в базу данных");
    }
}
``````markdown
info("{} записей, начало сохранения в базу данных! ", list.size());
if(sheetNo == 0){
    List<User> users = new ArrayList<>();
    for (T t : list) {
        User user = (User) t;
        users.add(user);
    }
    if (!CollectionUtils.isEmpty(users)) {
        userService.saveBatch(users);   // Если коллекция не пустая, сохраняем пользователей
    }
}
if(sheetNo == 1){
    List<Phone> phones = new ArrayList<>();
    for (T t : list) {
        Phone phone = (Phone) t;
        phones.add(phone);
    }
    if (!CollectionUtils.isEmpty(phones)) {
        phoneService.saveAllPhones(phones);   // Сохраняем телефоны после того, как коллекция не будет пустой
    }
}

saveAllPhones(phones);   // Сохранить все телефоны после того, как коллекция не будет пустой.
                          }
                      }
                       log.info("Успешное сохранение в базу данных! ");
                   }
               }
                ```
```markdown
## Как участвовать в вкладе

1. Fork этот репозиторий
2. Создать ветку Feat_xxx
3. Подать пулл-запрос

#### Фичи

1. Использовать Readme_XXX.md для поддержки различных языков, например Readme_en.md, Readme_zh.md
2. Официальный блог Gitee [blog.gitee.com](https://blog.gitee.com)
3. Вы можете [https://gitee.com/explore](https://gitee.com/explore) для обзора лучших открытых проектов на Gitee
4. [GVP](https://gitee.com/gvp) — это сокращение от "Gitee Most Valuable Projects", что означает оцененные и отобранные лучшие открытые проекты
5. Официальная документация Gitee [https://gitee.com/help](https://gitee.com/help)
6. Секция "Обложка Gitee" представляет собой серию, посвященную профилям членов Gitee [https://gitee.com/gitee-stars/](https://gitee.com/gitee-stars/)