В сей заметке рассматривается код на Scala, генерирующий электронную таблицу в формате xlsx, содержащую, помимо данных в ячейках, формулы, графики и украшательства вроде выделения текста жирным шрифтом. Как обычно, по ходу изложения объясняются особенности языка Scala, которые ранее не были рассмотрены в этом блоге.
В мире Java для работы с разными офисными форматами файлов существует замечательная библиотека POI . Помимо хорошей документации имеется масса примеров использования этой библиотеки. Есть несколько оберток над POI для Scala (например, раз и два ). Но они мне что-то не очень понравились, поэтому в этой заметке будет использована просто POI, безо всяких оберток.
Нам понадобится build.sbt примерно следующего содержания:
version := «0.1»
scalaVersion := «2.10.1»
libraryDependencies ++ = Seq (
«org.apache.poi» % «poi» % «3.9» ,
«org.apache.poi» % «poi-ooxml» % «3.9»
)
scalacOptions ++ = Seq ( «-unchecked» , «-deprecation» )
Следующий код строит отчет, аналогичный отчету «Продление регистрации всех доменов» для зоны RU на сайте stat.nic.ru. К разработке последнего в свое время я имел непосредственное отношение.
import java. io . _
import org. apache . poi . ss . util . _
import org. apache . poi . xssf . usermodel . _
object ReportTemplateWriter extends App {
// Данные для построения отчета
val months = Array ( «октябрь 2012» , «ноябрь 2012» , «декабрь 2012» ,
«январь 2013» , «февраль 2013» , «март 2013» )
val data = Map (
( 1 , «RU-CENTER» ) — > Array ( 83318 , 80521 , 83048 , 73638 , 82014 , 93982 ) ,
( 2 , «REGRU» ) — > Array ( 35621 , 37013 , 36515 , 41595 , 45042 , 49101 ) ,
( 3 , «R01» ) — > Array ( 44155 , 44356 , 43199 , 39629 , 42754 , 48528 ) ,
( 4 , «REGTIME» ) — > Array ( 19999 , 18587 , 18630 , 18627 , 19886 , 20496 )
)
// Открываем шаблон отчета
val wb = new XSSFWorkbook (
getClass. getResourceAsStream ( «/template.xlsx» )
)
val sheet = wb. getSheetAt ( 0 )
// Заполняем шапку таблицы
val headerRow = sheet. getRow ( 0 )
for ( idx < — 1 to months. size ) {
headerRow. getCell ( idx ) . setCellValue ( months ( idx — 1 ) )
}
// Заполняем тело таблицы
for ( key @ ( rowNumber, rowName ) < — data. keys ) {
val row = sheet. getRow ( rowNumber )
row. getCell ( 0 ) . setCellValue ( rowName )
for ( idx < — 1 to data ( key ) . size ) {
row. getCell ( idx ) . setCellValue ( data ( key ) ( idx — 1 ) )
}
}
// Также нужно заполнить подвал, иначе он не пересчитается
val footerRow = sheet. getRow ( data. size + 1 )
for ( idx < — 1 to months. size ) {
val cell = footerRow. getCell ( idx )
val range = new CellRangeAddress ( 1 , data. size , idx, idx )
cell. setCellFormula ( s «SUM(${range.formatAsString})» )
}
// Сохраняем отчет
val resultFile = new FileOutputStream ( «report.xlsx» )
wb. write ( resultFile )
resultFile. close
}
Файл template.xlsx представляет собой шаблон отчета, с таблицей, графиком и так далее, только вместо реальных цифр в нем записаны «заглушки» вроде 123. Приведенный код открывает этот шаблон, подставляет в него реальные цифры, после чего сохраняет отчет в файле report.xlsx. Причины, по которым мы использовали шаблон, будут рассмотрены ниже.
Чтобы пользователь мог запустить наше приложение без установки всяких там сторонних библиотек, мы собираем standalone jar c помощью плагина sbt-assembly. Есть возможность включить в этот jar различные сторонние файлы, положив их в src/main/resources. Наше приложение может получить содержимое этих файлов, сказав:
С помощью этого кода мы получаем экземпляр класса InputStream или null в случае, если ресурс не найден. Было бы странно таскать template.xlsx отдельно от standalone jar, поэтому мы включаем template.xlsx в наш jar описанным выше способом.
Еще в приведенном коде не совсем понятной для нас является строка:
Вызов data. keys возвращает список пар (номер_строки, название_строки) . Первый элемент пары кладется в переменную rowNumber, второй — в rowName, а в переменную key помещается пара целиком.
В остальном код предельно прост и очевиден. В результате его выполнения получается примерно такой отчет:
Вы наверняка ломаете голову, зачем нам понадобилось использовать какой-то там шаблон. Почему нельзя полностью создать электронную таблицу на Scala? Отчасти здесь имеет место ограничение POI. До недавнего времени эта библиотека вообще не умела строить диаграммы. Сейчас она умеет строить один из видов диаграмм (точечные диаграммы), но результат выглядят… ммм… не очень красиво.
Даже если вам не нужны диаграммы, все равно имеет смысл прибегнуть к использованию шаблона. В этом случае вам придется писать меньше кода и будет намного проще редактировать внешний вид электронной таблицы. А также добавлять в нее графики, когда и если в них все-таки возникнет потребность.
Также, как вариант, можно строить отчет полностью на Scala, а графики рисовать с помощью Scala Chart и включать их в отчет в виде статических картинок. Само собой разумеется, в этом случае при внесении пользователем изменений в отчет, графики не будут перестроены.
Относительно Excel-отчетов есть еще один тонкий момент. Сделать так, чтобы отчет был совместим с различными редакторами, не так-то просто. Например, электронная таблица, построенная с помощью приведенного выше кода, без каких-либо проблем открывается в LibreOffice и просматривается в Google Drive. Однако при попытке отредактировать ее в Google Drive возникает ошибка. При этом отчет, построенный с помощью POI без использования шаблона, одинаково хорошо редактируется как в LibreOffice, так и в Google Drive.
Может оказаться, например, что в LibreOffice все выглядит ОК, а в Microsoft Office отображаются некрасивые шрифты или не посчитались формулы. В общем, будьте бдительны. Если вам нужно, чтобы данные гарантированно открывались везде и всеми, используйте лучше формат CSV.
Все исходники к этой заметке вы найдете в этом архиве . Помимо кода, приведенного выше, в нем вы также найдете пример построения отчета без использования шаблона.
Дополнение: Использование IntelliJ IDEA в качестве IDE для Scala, а также других функциональных языков программирования
