Перейти к основному содержимому

XLS - Excel

Обработка пользовательских файлов Excel.

Введение​

Netuno генерирует файлы EXCEL, совместимые с LibreOffice, OpenOffice, Microsoft Office, Google Drive и т. д., через ресурс XLS, который предоставляет low-code-абстракцию над Apache POI для различных языков программирования, поддерживаемых Netuno.

Можно генерировать файлы XLSX, XLS или ODS.

Приложение, поставляемое с Netuno и называемое demo (демонстрационное приложение), содержит множество примеров возможных реализаций кода на разных языках, включая демонстрацию экспорта электронных таблиц через сервис export-excel, который находится в:

  • netuno/apps/demo/server/services/samples/javascript/export-excel.js

Инициализация​

Для инициализации ресурса XLS:

const excel = _xls.create()

Создаёт объект, содержащий контекст нового файла, который будет построен, с пустым листом.

Форматирование​

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

Как показано в примере из приложения demo, форматирования определяются в начале кода.

Для форматирования дат:

const стильДата = excel.cellStyleFormat("dd/mm/yy")

Для форматирования времени:

const стильВремя = excel.cellStyleFormat("hh:mm:ss")

Для форматирования дат со временем:

const стильДатаВремя = excel.cellStyleFormat("dd/mm/yyyy hh:mm:ss")

Для форматирования денежных значений в евро:

const стильДеньгиЕвро = excel.cellStyleFormat("€#,##0.00;\-€#,##0.00")

Для форматирования денежных значений в реалах:

const стильДеньгиРеал = excel.cellStyleFormat("R$#,##0.00;\-R$#,##0.00")

Для форматирования значений в процентах:

const стильПроцент = excel.cellStyleFormat("0.00%")

Внешний вид​

Определение стилей визуального оформления, то есть внешнего вида ячеек, такого как цвета, шрифт, границы, выравнивание и другие, должно выполняться до определения значений ячеек, потому что при задании значений ячеек можно связать с ними стиль оформления.

Как показано в примере из приложения demo, оформление определяется в начале кода.

Пример определения цветов и выравнивания:

const стильЗаголовка = excel.cellStyle()
стильЗаголовка.setFillPattern(_xls.fillPattern("solid-foreground"))
стильЗаголовка.setFillBackgroundColor(excel.color("black"))
стильЗаголовка.setAlignment(_xls.horizontalAlignment("center"))

Документация со всеми методами объекта Стили в Apache POI.

Определение стиля границ:

const стильДанных = excel.cellStyle()
стильДанных.setBorderBottom(_xls.borderStyle("thin"))
стильДанных.setBorderTop(_xls.borderStyle("thin"))
стильДанных.setBorderLeft(_xls.borderStyle("thin"))
стильДанных.setBorderRight(_xls.borderStyle("thin"))

Документация со всеми методами объекта Стили в Apache POI.

Создаёт новый стиль шрифта для текста и связывает определения шрифта с определённым уже существующим стилем ячейки:

const шрифтЗаголовка = excel.font()
шрифтЗаголовка.setBold(true)
шрифтЗаголовка.setFontHeightInPoints(14)
шрифтЗаголовка.setColor(excel.color("yellow"))
// Задаёт шрифт для стильЗаголовка
стильЗаголовка.setFont(шрифтЗаголовка)

Документация со всеми методами объекта Шрифт в Apache POI.

Изображение​

Чтобы вставить изображение в лист, которое находится в storage/samples/export-excel/logo.png внутри приложения:

excel.insertPicture(
    _storage.filesystem("server", "samples/export-excel", "logo.png"),
    1, // Номер столбца
    1  // Номер строки
).resize(2.3)

Чтобы вставить изображение, которое находится в приложении по пути public/images/logo.png:

excel.insertPicture(
    _app.file("public/images/logo.png"),
    10, // Номер столбца
    1  // Номер строки
).resize(1.5)

Документация со всеми методами объекта Изображение в Apache POI.

Объединение ячеек​

Для объединения ячеек в области используется метод mergedRegion.

Порядок параметров следующий:

  1. Начальная строка
  2. Начальный столбец
  3. Конечная строка
  4. Конечный столбец

Числовые ссылки позиционирования всегда начинаются с нуля.

В примере ниже будут объединены все ячейки с 10-го по 15-й столбец, находящиеся в строке 3:

excel.mergedRegion(2, 9, 2, 14)

Ячейки​

Чтобы получить объект, представляющий ячейку:

// Получает ячейку в столбце B и строке 2:
const клетка = excel.cell(1, 1)

Числовые ссылки позиционирования всегда начинаются с нуля.

Пример вывода в лог формулы ячейки:

// Записывает в лог формулу строки 2 и столбца B.
_log.info(
    "Формула ячейки B2: "+ excel.cell(1, 1).getCellFormula()
)

Пример получения текстового значения ячейки:

// Записывает в лог текстовое значение ячейки строки 2 и столбца B.
_log.info(
    "Значение ячейки B2: "+ excel.cell(1, 1).getStringCellValue()
)

Документация со всеми методами объекта Ячейка в Apache POI.

Строки​

Чтобы получить объект, представляющий строку:

// Получает объект, представляющий строку 2.
const строка = excel.row(1)

Числовые ссылки позиционирования всегда начинаются с нуля.

В примере выше, если строка не существует, новая строка будет создана автоматически.

Документация со всеми методами объекта Строка в Apache POI.

Вставка табличных данных​

Можно вставить таблицу данных с помощью метода addDataTable.

Первые два параметра определяют позицию, в которую должна быть вставлена таблица данных: это номера строк и столбцов.

Обратите внимание, что позиционирование строк и столбцов в коде всегда начинается с нуля.

То есть строка 1 — это позиция 0, и столбец A также является позицией 0.

Далее передаётся список строк, где каждая строка содержит другой список для столбцов, и только затем добавляется карта со значением ячейки.

Полный пример:

excel.addDataTable(
    6, 1,
    _val.list() // Строки
        .add(
            _val.list() // Столбец
                .add(
                    _val.map() // Ячейка
                        .set("value", "Имя")
                )
                .add(
                    _val.map()
                        .set("value", "Возраст")
                )
        )
        .add( // Другая строка
            _val.list()
                .add( // Первый столбец
                    _val.map()
                        .set("value", "Мария")
                )
                .add( // Второй столбец
                    _val.map()
                        .set("value", 26)
                )
        )
)

Внешний вид​

В карте ячеек можно связать уже созданные стили, используя, например, стили, определённые в разделе о внешнем виде и форматировании, которые рассматривались ранее.

Ниже пример того, как связать стили с ячейками:

excel.addDataTable(
    6, 1,
    _val.list()
        .add(
            _val.list()
                .add(
                    _val.map()
                        .set("value", "Имя")
                        .set("style", стильЗаголовка)
                )
                .add(
                    _val.map()
                        .set("value", "Возраст")
                        .set("style", стильЗаголовка)
                )
        )
)

Формулы​

Для вставки формул используется ключ formula в карте определений ячейки.

В этом примере в последней ячейке будет выполнено суммирование всех значений других ячеек:

excel.addDataTable(
    6, 1,
    _val.list()
        .add(
            _val.list()
                .add(_val.map().set("value", 45))
                .add(_val.map().set("value", 26))
        )
        .add(
            _val.list()
                .add(_val.map().set("value", 51))
                .add(_val.map().set("value", 30))
        )
        .add(
            _val.list()
                .add(
                    _val.map().set("value", "Итого")
                )
                .add(
                    _val.map().set("formula", "SUM(B7:C8)")
                )
        )
)

Для получения буквы столбца из номера столбца можно использовать метод columnReference, что может помочь при определении формул. Этот же метод также можно использовать для получения номера столбца из буквенной ссылки, например:

excel.addDataTable(
    6, _xls.columnReference('B'),
    _val.list()
        .add(
            _val.list()
                .add(_val.map().set("value", 45))
                .add(_val.map().set("value", 26))
        )
        .add(
            _val.list()
                .add(_val.map().set("value", 51))
                .add(_val.map().set("value", 30))
        )
        .add(
            _val.list()
                .add(
                    _val.map().set("value", "Итого")
                )
                .add(
                    _val.map().set(
                        "formula",
                        "SUM("+ _xls.columnReference(1) +"7:"+ _xls.columnReference(2) +"8)"
                )
            )
        )
)

Листы​

Чтобы создать новый лист (sheet) в самом документе, рассмотрите следующий пример:

const новыйЛист = excel.createSheet("Новый лист")

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

В зависимости от того, какой лист активен, можно изменить активный лист с помощью метода activeSheet.

Пример вставки изображения в новый лист:

excel.insertPicture(
    новыйЛист,
    _app.file("public/images/logo.png"),
    10, // Номер столбца
    1  // Номер строки
).resize(1.5)

Пример вставки таблицы данных в новый лист:

excel.addDataTable(
    новыйЛист,
    6, 1,
    _val.list()
        .add(
            _val.list()
                .add(_val.map().set("value", 45))
                .add(_val.map().set("value", 26))
        )
        .add(
            _val.list()
                .add(_val.map().set("value", 51))
                .add(_val.map().set("value", 30))
        )
        .add(
            _val.list()
                .add(
                    _val.map().set("value", "Итого")
                )
                .add(
                    _val.map().set("formula", "SUM(B7:C8)")
                )
        )
)

Активация​

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

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

Ниже два способа активации конкретного листа: сначала по ссылке на объект, затем по индексу листа:

excel.activeSheet(мойЛист) // Объект-ссылка на лист.
excel.activeSheet(1) // Индекс листа, который будет активирован.

Полезные возможности​

Примеры других полезных методов для работы с листами.

Получение листа по имени:

// Получает лист по его имени.
const мойЛист = excel.getSheet("Имя листа")

Получение листа по индексу:

// Получает лист по его номеру.
const мойЛист = excel.getSheet(1)

Для получения общего количества листов:

// Общее количество листов.
const всегоЛистов = excel.getNumberOfSheets()

Для вывода списка всех существующих в документе листов.

// Список всех листов.
const всеЛисты = excel.getAllSheets()
for (const лист of всеЛисты) {
    ...
}

Документация со всеми методами объекта Лист в Apache POI.

Генерация файла​

В методах save и output создаётся двоичное содержимое итогового файла.

Отправляет сгенерированный файл как вывод данных (output) сервиса:

excel.output("файл.xlsx")

Чтобы сохранить сгенерированный файл в папку storage/filesystem приложения:

excel.save(_storage.filesystem("server", "файл.xlsx"))

Чтобы сохранить сгенерированный файл в папку /tmp файловой системы жёсткого диска:

excel.save(_os.file("/tmp/файл.xlsx"))

Редактирование файла​

Можно редактировать файлы, открывая их, внося необходимые изменения и затем сохраняя новый отредактированный файл.

Ниже приведены некоторые примеры того, как можно открывать файлы.

Открытие файла в корне приложения:

const excel = _xls.open(_app.file("файл.xlsx"))

Чтобы открыть файл, сгенерированный в папке storage/filesystem приложения:

const excel = _xls.open(_storage.filesystem("server", "файл.xlsx"))

А также чтобы открыть файл в папке /tmp файловой системы жёсткого диска:

const excel = _xls.open(_os.file("/tmp/файл.xlsx"))

В примерах выше константа excel будет содержать объект редактирования с контекстом соответствующего файла, поэтому можно выполнять любые виды манипуляций.

После завершения изменений выполните генерацию нового файла.

Невозможно напрямую изменить тот же открытый файл; необходимо сгенерировать новый файл с другим путём.

Чтобы напрямую изменить тот же файл, это можно сделать с помощью inputStream.

Ниже пример изменения значения ячейки:

const файлВвода = _app.file("файл.xlsx").inputStream()
const excel = _xls.open(файлВвода)

excel.setCellData(
    10, 1,
    _val.map()
        .set(
            "value",
            excel.getCellData(10, 1).getInt("value") + 10
        )
)

файлВвода.close() // Закрывает открытый файл.
excel.save(_app.file("файл.xlsx")) // Сохраняет новую версию.

Чтение файла​

Извлечение данных из файлов Excel выполняется методом read.

const данные = _xls.read(_app.file("файл.xlsx"))

Данные получаются в структуре объектов типа Values, которая содержит списки для листов, строк и столбцов и карты (ключ и значение) для ячеек.

Чтобы просмотреть все данные, можно вывести их как вывод данных сервиса:

_out.json(данные)

Для выполнения итерации нужно учитывать, что структура данных организована в следующем порядке:

  1. sheets — Листы
  2. rows — Строки
  3. columns — Столбцы

Пример полной итерации:

for (const лист of данные.getValues("sheets")) {
    _out.println(`<h4>Лист: ${лист.getInt("index")} - ${лист.getString("name")}</h4>`)
    for (const строка of лист.getValues("rows")) {
        _out.println(`<h4>Строка: ${строка.getInt("row")}</h4>`)
        _out.println("<p>")
        for (const столбец of строка.getValues("columns")) {
            _out.print(`${столбец.getInt("row")}:${столбец.getInt("column")}`)
            _out.print(` # ${столбец.getString("address")}`)
            _out.print(` # ${столбец.getString("type")}`)
            _out.print(` # ${столбец.getString("value")}`)
            _out.println("<br>")
        }
        _out.println("</p>")
    }
}

Тип ячейки получается с помощью кода:

  • столбец.getString("type")

Поддерживаемые типы:

  • string — Текстовое содержимое.
  • numeric — Числовое содержимое или дата и/или время.
  • boolean — Истина или ложь.
  • blank — Пустая ячейка.
  • formula — Ячейка, содержащая формулу.
  • error — Ошибка при обработке ячейки.

Дополнительные параметры​

Для каждого типа ячейки есть некоторые дополнительные параметры.

string — Тип ячейки с текстовым содержимым.

  • value — Текст ячейки.
  • richValue — Форматирование ячейки.

numeric — Тип ячейки с числовым содержимым, а также датами и временем.

  • value — Значение числового содержимого ячейки.

В случае дат и времени есть ещё следующие дополнительные параметры:

  • localDateTime — Объект Java Time LocalDateTime, представляющий значение даты и времени ячейки.
  • localDate — Объект Java Time LocalDate, представляющий значение даты ячейки.
  • localTime — Объект Java Time LocalTime, представляющий значение времени ячейки.
  • instant — Объект Java Time Instant, представляющий значение даты и/или времени ячейки.
  • date — Объект Date из Java, представляющий значение даты и/или времени ячейки.

boolean — Тип ячейки с логическим содержимым, то есть истина или ложь.

  • value — Значение типа boolean, true или false.

blank — Тип ячейки без содержимого, поэтому не содержит дополнительных параметров.

formula — Тип ячейки, содержащей формулы вычисления.

  • value — Итоговое значение результата вычисления.
  • formula — Содержит выражение формулы, использованной для вычисления.

error — Тип ячейки, содержащей ошибку.

  • value — Байтовое значение возникшей ошибки.
  • code — Если при вычислении формулы возникли ошибки, здесь указывается код возникшей ошибки.

Видео​

Ниже несколько примеров видео о том, как программно генерировать файлы Excel/XLSX: