Java создание excel файла
На странице описания библиотеки Apache POI представлены свойства и методы взаимодействия Java приложений с файлами Excel. Здесь рассматривается пример использования Apache POI для чтения файлов Excel 2007 или более поздней версии. Если необходимо обрабатывать Excel 1997-2003, то следует использовать классы, наименования которых начинаются с символа 'H' (см. наименование классов на странице описания Apache POI).
Открытие книги Excel
Чтобы «открыть» файл Excel как HSSFWorkbook (.xls), так и XSSFWorkbook (.xlsx) можно использовать либо File, либо InputStream. При использовании InputStream требуется больше памяти для загрузки файла в буффер.
Метод openBook демонстрирует использование WorkbookFactory для открытия Excel файла. Строки использования InputStream закомментированы.
Если нужно обойти использование WorkbookFactory, т.е. использовать XSSFWorkbook (HSSFWorkbook) напрямую, то следует использовать OPCPackage (.xlsx) или NPOIFSFileSystem (.xls).
Открытие страницы
При открытии страницы слеудет использовать метод getSheet с указанием в качестве параметра наименования страницы. Здесь необходимо быть внимательным, поскольку наименования страницы по умолчанию могут быть как «Sheet», так и «Лист», в зависимости от локализации операционной системы и Excel.
Перебор строк и ячеек
Чтобы «пройтись» по всем страницам книги и перебрать все значения в ячейках можно использовать итераторы. Следующий код показывает использование итераторов для перебора всех ячеек страницы.
Итераторы доступны по вызовам workbook.sheetIterator(), sheet.rowIterator() и row.cellIterator(). Но необходимо помнить, что rowIterator и cellIterator перебирают только строки и ячейки, которые созданы, пропуская пустые строки и ячейки.
Если необходимо проверить все строки и все ячейки определенной области, то можно воспользоваться следующим кодом.
В представленном коде для чтения ячейки был использован метод getCell(int). Можно использовать метод getCell(int, MissingCellPolicy), где MissingCellPolicy, определяет условие возвращения пустых и отсутствующих ячеек. Однако IDE Eclipse, где работоспособность кода проверялась, показывает, что MissingCellPolicy упразднена (deprecated), а метод getCell(int) для ячейки типа XSSFCell вернул правильные значения. Определение значений ячеек выполнялось в методе printCell(row, cell).
Чтение содержимого ячейки
Чтобы получить значение ячейки, необходимо знать тип её значения. Полагаю, что Вы не раз сталкивались с числовым представлением значения даты. Если у текстовой ячейки попытаться получить числовое значение, то будет вызвано NumberFormatException. Поэтому, необходимо первоначально определиться с типом, чтобы использовать соответствующий метод.
Следующий код в цикле проходит по ячейкам в строке и выводит в консоль ссылочную информацию на ячейку (например $A$3) и содержимое ячейки. Причем, сначала отображается значение, полученное методом formatCellValue класса DataFormatter, а после символа слеша '/' отображается значение, получаемое методами getRichStringCellValue(), getDateCellValue(), getNumericCellValue(), getBooleanCellValue() и getCellFormula() класса XSSFCell.
Пример чтения файла Excel
Работоспособность представленного на странице кода (методов), была проверена в примере, структура которого изображена на следующем скриншоте. Это проект Eclipse, включающий перечень необходимых библиотек для чтения файла Excel. Основной класс ExcelRead реализует все представленные выше методы.
На следующем скриншоте представлена страница простенького файла Excel, содержащая ячейки со значениями типа Date, String, Numeric и ячейки с формулами.
Результат чтения файла
Значения ячеек книги Excel в примере отображаются в консоли. Обратите внимание на отличия значений до слеша и после него для типов Date и Numeric. При необходимости можно использовать в приложении переменные соответствующего типа (Date, Integer) и должным образом отформатировать значения.
Исходный пример, рассмотренный в тексте страницы, можно скачать здесь (11.7 Мб).
Если Вы еще не знакомы с Apache POI, то рекомендую вкратце ознакомится с ее возможностями и способами ее подключения в проект по этой ссылке.
Создание нового Excel файла в Java
Внимание, код не очень красив и оптимизирован. Я хотел просто продемонстрировать возможности этой удобной библиотеки.
Для начала давайте создадим простой xls файл и запишем в него какие-то данные. А далее будем применять к нему стили и добавлять формулы.
Для удобной работы с данными нам потребуется дополнительный класс, который будет представлять собой модель данных, которую мы будем записывать в файл:
Как видим, это простой класс с полями для имени, фамилии, города и зарплаты какого-то человека.
Ниже представлен листинг класса, в котором создается сам Excel файл:
try ( FileOutputStream out = new FileOutputStream ( new File ( "F:\\Apache POI Excel File.xls" ) ) )
Обратите внимание, что мы использовали try with resources — одну из особенностей Java 7. А это значит, что нам не нужно беспокоится о закрытии файла вручную. В Java 7 конструкцию try-catch-finally можно не использовать, так как ей на смену пришла try with resources, которая сама закрывает открытые файлы или потоки без вашего вмешательства.
После запуска приведенной выше программы, в корне проекта создастся файл с названием Apache POI Excel File.xls. Давайте посмотрим на его содержимое:
Лист Excel файла называется «Просто лист», как мы и называли, а данные расположены правильно.
Добавление стилей в Excel документ на Java
Теперь давайте поупражняемся с обновлением файла, а именно добавлением стилей. Например, выделим имя столбцов из первой строки жирным.
Для этого нам понадобится еще один метод setBoldStyle():
Свойства и методы взаимодействия Java приложений с файлами Excel представлены на странице библиотеки Apache POI. Здесь рассматриваются примеры использования Apache POI для создания файлов Excel 2007 или более поздней версии. Если необходимо создать Excel 1997-2003, то следует использовать классы, наименования которых начинаются с символа 'H'. Использование библиотеки Apache POI для чтения файлов Excel с примером рассмотрен здесь.
Создание книги Excel
Создание страницы
Наименование страницы не должно превышать 31 символ. Следующие символы недопустимы в наименовании страницы :
0x0 - нулевое значение;
':' - двоеточие;
'\' - обратный слэш;
'/' - прямой слэш;
'*' - «звездочка»;
'?' - вопросительный знак;
'[' - открывающаяся квадратная скобка;
']' - закрывающаяся квадратная скобка.
Можно использовать утилиту WorkbookUtil для получения наименования страницы книги. Метод createSafeSheetName данной утилиты заменит «неправильные» символы на пробелы (' ').
Определение размера колонки
При определении размера колонки необходимо учитывать максимальное количество символов в колонке, коэффициент размера символа для заданного шрифта (для "Sans Sherif" равен 1.14388) и коэффициент EXCEL_COLUMN_WIDTH_FACTOR, равный 256. Метод autoSizeColumn(idx_column) позволяет автоматически установить размер колонки по максимальному значению.
Метод setColumnWidth(column, width) в качестве параметров принимает номер колонки (отсчет от 0) и размер колонки. Методу autoSizeColumn(column) необходимо передать только номер колонки.
Создание строки
При создании строки в метод createRow в качестве параметра необходимо передать номер строки (отсчет от 0). Для определения размера строки можно использовать методы setHeight (short) и setHeightInPoints (float). При использовании метода setHeight также, как и с определением размера колонки, необходимо учитывать коэффициенты. Поэтому метод setHeightInPoints оказывается более предпочтительным.
Создание ячейки, определение типа значения
В следующем примере создаются ячейки, устанавливается их тип и записываются значения.
Класс CellType включает свойства [_NONE, BLANK, BOOLEAN, ERROR, FORMULA, NUMERIC, STRING], которые можно использовать для определения типа значения ячейки.
Определение формата даты ячейки
В следующем примере создаются две ячейки с записью текущей даты. Вторая ячейка форматируется.
Слияние ячеек
Для слияния ячеек используется метод addMergedRegion.
Определение шрифта
Помните, что максимальное количество создаваемых шрифтов ограничено значением 32767. Необходимо использовать объекты шрифтов/стилей.
Определение цвета фона ячейки
ПРИМЕЧАНИЕ : для выделения цвета значения настраивайте шрифт (см. выше).
Выравнивание значения
В следующем примере создаются ячейки с различным выравниванием значений по горизонтали и вертикали.
Границы ячейки, Border
Многострочные ячейки
Для фиксированного разделения/переноса текста в ячейке необходимо в стиле определить свойство WrapText=true, и в тексте установить разделители '\n'.
Использование формулы
Создадим две ячейки. В первую ячейку cell1 запишем сумму значений колонки, а во второй ячейке cell2 разместим формулу "СУММА()". Для вычисления значения первой ячейки дополнительно используются методы getColsSummary и getCellValue.
Apache POI представляет собой API, который позволяет использовать файлы MS Office в Java приложениях. Данная библиотека разрабатывается и распространяется Apache Software Foundation и носит открытый характер. Apache POI включает классы и методы для чтения и записи информации в документы MS Office.
Компоненты Apache POI
Описание компонентов
HSSF | Horrible Spreadsheet Format | Компонент чтения и записи файлов MS-Excel, формат XLS |
XSSF | XML Spreadsheet Format | Компонент чтения и записи файлов MS-Excel, формат XLSX |
HPSF | Horrible Property Set Format | Компонент получения наборов свойств файлов MS-Office |
HWPF | Horrible Word Processor Format | Компонент чтения и записи файлов MS-Word, формат DOC |
XWPF | XML Word Processor Format | Компонент чтения и записи файлов MS-Word, формат DOCX |
HSLF | Horrible Slide Layout Format | Компонент чтения и записи файлов PowerPoint, формат PPT |
XSLF | XML Slide Layout Format | Компонент чтения и записи файлов PowerPoint, формат PPTX |
HDGF | Horrible DiaGram Format | Компонент работы с файлами MS-Visio, формат VSD |
XDGF | XML DiaGram Format | Компонент работы с файлами MS-Visio, формат VSDX |
Список компонентов
Наименование (артeфакт) | Необходимые компоненты |
---|---|
poi | commons-logging, commons-codec, commons-collections, log4j |
poi-scratchpad | poi |
poi-ooxml | poi, poi-ooxml-schemas |
poi-ooxml-schemas | xmlbeans |
poi-examples | poi, poi-scratchpad, poi-ooxml |
ooxml-schemas | xmlbeans |
ooxml-security | xmlbeans |
Подключение Apache POI к проекту
Если в проекте используется фреймворк maven, то необходимо установить одну из следующих зависимостей (версия может быть более новой) :
На странице описания примера чтения файлов Excel приведен проект, включающий необходимый набор файлов библиотеки Apache POI (poi-3.16.jar, poi-ooxml-3.16.jar, poi-ooxml-schemas-3.16.jar) и сопутствующих библиотек.
На странице рассматриваются следующие классы, используемые для работы с файлами Excel из приложений Java.
-
- HSSFWorkbook, XSSFWorkbook - HSSFSheet, XSSFSheet - HSSFRow, XSSFRow - HSSFCell, XSSFCell - стили ячеек HSSFCellStyle, XSSFCellStyle - шрифт ячеек HSSFFont, XSSFFont
Поскольку описание всех классов и методов не разместить на одной странице, то ниже по тексту приводятся ссылки для перехода к исходной документации.
Классы и методы Apache POI для работы с файлами Excel
Рабочая книга HSSFWorkbook, XSSFWorkbook
Конструкторы класса HSSFWorkbook
preservenodes является необязательным параметром, который определяет необходимость сохранения узлов типа макросы.
Конструкторы класса XSSFWorkbook
Основные методы HSSFWorkbook, XSSFWorkbook
Метод | Описание |
---|---|
createSheet () | Создание страницы книги HSSFSheet, XSSFSheet |
createSheet (String name) | Создание страницы с определенным наименованием |
CreateFont () | Создание шрифта |
createCellStyle () | Создание стиля |
Классы листов книги, HSSFSheet, XSSFSheet
Классы HSSFSheet, XSSFSheet включают свойства и методы создания строк, определения размера колонок, слияния ячеек в одну область и т.д.
Основные методы классов работы с листами
Метод | Описание |
---|---|
addMergedRegion (CellRangeAddress) | Определение области слияния ячеек страницы |
autoSizeColumn (int column) | Автоматическая настройка ширины колонки column (отсчет от 0) |
setColumnWidth (int column, int width) | Настройка ширины колонки column (отсчет от 0) |
createRow (int row) | Создание строки row (отсчет от 0) |
getRow (int row) | Получение ссылки на строку row (отсчет от 0) |
Классы строк HSSFRow, XSSFRow
Классы HSSFRow, XSSFRow включают свойства и методы работы со строками, создания ячеек в строке и т.д.
Основные методы классов HSSFRow, XSSFRow
Метод | Описание |
---|---|
setHeight (short) | Определение высоты строки |
getHeight() | Получение значения высоты в twips'ах (1/20) |
getHeightInPoints() | Получение значение высоты |
createCell (int) | Создание ячейки в строке (отсчет от 0) |
getCell(int) | Получение ссылки на ячейку |
getFirstCellNum() | Получение номера первой ячейки в строке |
setRowStyle(CellStyle) | Определение стиля всей строки |
Классы ячеек HSSFCell, XSSFCell
Ячейки электронной таблицы используются для размещения информации. В ячейке может быть представлено числовое значение, текст или формула. Также ячейка может содержать комментарий.
Классы HSSFCell, XSSFCell включают свойства и методы работы с ячейками таблицы.
Основные методы классов HSSFCell, XSSFCell
Метод | Описание |
---|---|
getBooleanCellValue() | Чтение логического значения ячейки |
getDateCellValue() | Чтение значения ячейки типа java.util.Date |
getNumericCellValue() | Чтение числового значения ячейки типа double |
getStringCellValue() | Чтение текстового значения ячейки (java.lang.String) |
setCellValue(boolean) | Определение логического значения ячейки |
setCellValue(java.util.Calendar) | Определение значения ячейки типа даты |
setCellValue(java.util.Date) | Определение значения ячейки типа даты |
getCellTypeEnum() | Чтение типа значения ячейки CellType |
setCellComment(Comment) | Запись комментария в ячейку |
getCellComment() | Чтение комментария ячейки |
removeCellComment() | Удаление комментария ячейки |
setHyperlink(Hyperlink) | Запись гиперссылки в ячейку |
getHyperlink() | Чтение гиперссылки XSSFHyperlink в ячейке |
removeHyperlink() | Удаления гиперссылки ячейки |
getCellFormula() | Чтение формулы, например SUM(C4:E4) |
setCellFormula(String) | Определение формулы, например =SUM(C4:E4) |
getCellStyle() | Чтение стиля ячейки (XSSFCellStyle) |
setCellStyle(CellStyle) | Определение стиля ячейки |
getColumnIndex() | Определение индекса ячейки |
setAsActiveCell() | Определение активности ячейки |
Классы стилей ячеек HSSFCellStyle, XSSFCellStyle
Ниже в качестве примера представлен метод, формирующий стиль ячейки, в которой :
- текст центрируется по вертикали и горизонтали;
- обрамление ячейки представляет тонкую черную линию по периметру;
- текст переносится на следующую строку (не ячейку), если не вмещается в размер ячейки.
Метод setWrapText позволяет определить флаг переноса текста в ячейке согласно ее размеру (ширине). Чтобы перенести текст принудительно, можно в текстовой строке установить символы CRCL, например "Разделитель\r\nтекста".
Классы шрифтов HSSFFont, XSSFFont
Ниже в качестве примера представлен метод, формирующий шрифт типа "Times New Roman" :
Примеры создания файлов Excel и определение стилей ячеек рассмотрены здесь.
Working with Excel documents is a frequently used feature in a software application.
In this article, a way to generalize the writing to an Excel file for any type of Objects with both single and composite(array) types of fields has been achieved using Apache POI with the support of Java Reflection feature. The need for custom code for writing each type of fields and row-column processing has been reduced, you are good to go with minimum customization.
Apache POI (Poor Obfuscation Implementation) is a popular open source library run by the Apache Software Foundation which is developed for reading and writing files in Microsoft Office formats, such as Word, PowerPoint, and Excel.
The objective of Apache POI is to design a cross-platform API that can manipulate various file formats of Microsoft Office and Open Office Documents. Since we are focusing on writing Excel files, we will be using the following file formats of Apache POI for spreadsheets.
HSSF (Horrible Spreadsheet Format) − It is used for xlsx file format of MS-Excel (97–2007) files.
XSSF (XML Spreadsheet Format) − It is used for xlsx file format of MS-Excel (2007 and later) files.
Each of the Apache POI libraries are dedicated to manipulate each particular type of file. The XSSF library contains the classes for handling the xlsx Excel format. The figure below shows the Apache POI related interfaces and classes for manipulating xlsx Excel files.
Furthermore it provides excellent support for additional excel features such as working with Formulas, creating cell styles by filling colors and borders, fonts, headers and footers, data validations, images, hyperlinks etc.
Lets dive in to the work.
These main steps will be followed for the implementation :
Setting up a Spring Boot project with Apache POI dependencies and other necessary dependencies
Defining the Java Annotation interfaces which will be used for the dynamic class reflection
Defining the Java POJO class which will be used to write into the Excel sheet
Here for the demonstration purpose, a POJO which includes User details with a list of DietPlan is used :
As shown above, the previously defined Annotations are used to provide metadata for the POJO class and its fields. This metadata will be evaluated at runtime for the POJO class reflection and populating the workbook in the generic writer.
Setting up a POJO class to hold metadata for each field of the above class
If we observe it, the possible dataset class might most probably consist of following data structures.
- Single fields (Integer, String, Float, Double, Boolean types)
- Array fields (List of single fields)
- Composite fields (List of objects that consists of single fields)
So each field is mapped to a XlsxField instance which holds metadata about the POJO class field which will be useful later.
Implementing the generic Xlsx writer with dynamic class reflection and POI spreadsheet data population into workbook
Fetching the list of POJO class objects and passing to the writer and get the response as a byte array
For the demonstration purpose, some dummy data will be used to create the sample POJO records list.
Then an instances of ByteArrayOutputStream and XSSFWorkbook are created. The titles of the spreadsheet are defined as an String array. These are passed to the writer as parameters. The passed instance of ByteArrayOutputStream is contained the byte stream of the data written into the workbook. Inside the Finally clause the ByteArrayOutputStream is closed and the byte array is returned.
Sample Output
Summary
Читайте также: