Apache POI HSSF - как "приручить" EXCEL

Опубликовано чт, 06/01/2017 - 22:46 пользователем root

Опубликовано: журнал "Системный администратор" №4, 2017г. Привожу статью полностью (с некоторыми изменениями).

К теме генерации отчетов время от времени приходится обращаться практически каждому программисту. Получив очередное техзадание - сделать отчет для информационной системы предприятия именно в Microsoft Excel и имея некоторый опыт построения отчетов с помощью технологий, описанных в [1], решено было исследовать - какой из современных генераторов отчетов (желательно бесплатный) подошел бы для решения этой задачи. При этом совершенно не хотелось лезть в поднадоевшие механизмы OLE-DDE, привязываясь к одной из операционных систем.

Использование электронных таблиц для формирования отчетов трюк давний и вполне оправданный. Во-первых, современные динамические таблицы - мощные программные комплексы, способные на математические вычисления вплоть до аппроксимации. Во-вторых, рабочие единицы в электронных таблицах - ячейки, строки, столбцы и с ними легко работать алгоритмически, например, используя циклы. И, напоследок, подобные программы установлены практически на любом персональном компьютере.

В процессе поиска информации заставил "потереть руки" в предвкушении чего-то интересного один проект - Apache POI, выпущенный под лицензией APACHE LICENSE. Официальный сайт проекта, в котором, кстати, может принять участие любой желающий, - https://poi.apache.org [2]. Аббревиатура POI (и другие - см. ниже) на титульной странице никак не объясняется, однако в [3], [8] даются некоторые разъяснения. На официальном сайте также читаем: "Вы можете использовать HSSF если Вам необходимо прочитать или записать файл Excel (XLS) с помощью Java. Вы можете использовать XSSF, если вам нужно прочитать или записать файл OOXML Excel (XLSX) с помощью Java. Комбинированный интерфейс SS позволяет легко считывать и записывать все виды файлов Excel (XLS и XLSX) с использованием той же Java. Кроме того, существует специализированная реализация SXSSF, которая позволяет работать с очень большими Excel (XLSX) файлами в памяти с оптимизацией."

Описанные возможности HSSF с лихвой охватывали потребности нашего (кстати сказать, небольшого по объему - около 10 страниц) отчета. После анализа техзадания, выполнение задачи было разбито на два этапа: 1 этап - данные из информационной системы с помощью ее инструментов, должны быть выгружены в текстовый файл с разделителями, затем 2 этап - файл должен незаметно для пользователя подхватиться программой на Java и, после обработки, вывестись пользователю на экран уже в Excel. Подобные поэтапные решения применяются в так называемой "лоскутной" автоматизации, описаны в [1] и довольно часто применяются в информационных системах.

Целью статьи будет описание процесса создания программы на языке программирования Java для решения задач 2 этапа, а именно - открытие текстового файла с разделителями и формирование многостраничного отчета с помощью библиотеки POI, способного открыться в Microsoft Excel. Программировать будем в JDK 6 (Linux Mint 13 LTE, версия Java - OpenJDK 1.6), сама программа, что естественно для языка программирования Java, после компиляции должна выполняться и в Linux и в Windows. Если Вы не знакомы с языком программирования Java, вот ссылка на вводный курс по языку.

Итак, скачиваем стабильную версию POI - 3.15 на момент написания статьи (см. рис. 1 и ссылку [4])


Рис.1 Сайт проекта POI poi_slide_1.png

Когда файл скачан (для версии 3.15 это poi-bin-3.15-20160924.tar.gz), распаковываем его, у меня на компьютере в домашней директории место нашлось в папке java/poi-3.15
$ cd ~/java
$ tar xzvf poi-bin-3.15-20160924.tar.gz

Для работы с библиотекой нужно показать путь к ее файлам в переменной окружения CLASSPATH, для этого заходим в файл .profile в домашнем каталоге
$ vim .profile

в конце файла пишем

export POI_HOME=$HOME/java/poi-3.15
export POI_CLASSES=$POI_HOME/poi-3.15.jar
export CLASSPATH=$CLASSPATH:$POI_CLASSES:.

следите за путями, они зависят от того, куда Вы разместите файл библиотеки poi-x.xx.jar.

Далее, нужно применить изменения в .profile

$ source .profile

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

Теперь необходимо убедиться что все работает, библиотека установлена правильно, т.е. файлы скопированы и нужные пути прописаны в переменной окружения CLASSPATH.
Повторив пример "Writing a new file" см. ссылку [5], убеждаемся что это так. Чтобы этот пример был работоспособен, необходимо добавить открытый (public) класс, функцию main() и следующий импорт:

import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.util.CellRangeAddress;

При компиляции этого примера в том виде, в котором он есть на сайте, правда, получаем предупреждения об использовании устаревших (deprecated) методов (см. рис. 2), однако для любителей "чистой" компиляции есть информация [6], [7], где указано чем эти методы заменить.


Рис.2 Компиляция примера с сайта https://poi.apache.org/spreadsheet/how-to.html

После запуска программы (допустим программный файл назывался Generator.java) командой
$ java Generator
в текущем каталоге обнаруживается файл workbook.xls (см. рис. 3), просматривая который можно понять, что делает пробная программа и какие свойства ячеек Excel она изменяет.

Что еще может POI как генератор отчета? Да практически все, что нужно самому требовательному заказчику: от изменения параметров ячейки таблицы (высоты, ширины, используемого в ячейке шрифта, выравнивания) до размещения диаграмм на листе рабочей книги.


Рис.3 Файл workbook.xls

Ну и, конечно, приведем часть кода, необходимую для решения нашей технической задачи, в котором читается файл с разделителями и значения оттуда попадают в ячейки Excel. Программа из листинга 1 может послужить заготовкой для нужного вашему руководству или коллеге отчета и использует в качестве входного текстовый файл с символом "#" в качестве разделителя.

Листинг 1. класс POIExample.java, формирующий книгу example.xls из текстового файла с разделителями в кодировке Windows 1251

public class POIExample {

    private int n;
    private StringTokenizer st;
    // вспомогательные переменные
    // для строки текстового файла
    private String line = null;
    // для строки и ячейки Excel
    private short rownum;
    private short cellnum;

  // Конструктор класса
  POIExample(String fileName) throws Exception {

    // выходной поток - новый файл .xls
    FileOutputStream out = new FileOutputStream("example.xls");
    // создаем новую книгу
    HSSFWorkbook wb = new HSSFWorkbook();
    // создаем новый лист
    HSSFSheet s = wb.createSheet();
    // объявляем объект строки
    HSSFRow r = null;
    // объявляем объект ячейки
    HSSFCell c = null;
    // создаем 3 объекта стилей
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFCellStyle cs2 = wb.createCellStyle();
    HSSFCellStyle cs3 = wb.createCellStyle();
   
    // создаем 2 объекта шрифта
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    // устанавливаем размер первого шрифта 14 пунктов
    f.setFontHeightInPoints((short)14);
    // тип шрифта
    f.setFontName("TimesNewRoman");
    // делаем шрифт полужирным
    f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    // Устанавливаем размер второго шрифта 10 пунктов
    ...

    // для первого стиля устанавливаем шрифт f2
    cs.setFont(f2);
    // выравнивание
    cs.setAlignment(cs.ALIGN_LEFT);
    // обрамление
    cs.setBorderBottom(cs2.BORDER_THIN);
    cs.setBorderTop(cs2.BORDER_THIN);
    cs.setBorderLeft(cs2.BORDER_THIN);
    cs.setBorderRight(cs2.BORDER_THIN);
    // в этом стиле формат ячейки - по умолчанию

    // стиль cs2
    // задаем обрамление
    ...
    // для примера - зададим формат ячейки "text"
    cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    //выравнивание - по центру
    cs2.setAlignment(cs.ALIGN_CENTER);
    // для стиля cs2 установим шрифт
    cs2.setFont(f2);
   
    // стиль cs3
    ...

    // задаем имя листа
    wb.setSheetName(0, "Отчет за V квартал" );

    // Открываем файл в кодировке Windows 1251
    BufferedReader in = new BufferedReader(new InputStreamReader(
             new FileInputStream(fileName), Charset.forName("CP1251")));

    rownum = (short) 0;
   
    // создаем ячейку для заголовка
    r = s.createRow(rownum);
    cellnum = (short) 0;
    c = r.createCell(cellnum);
    // устанавливаем высоту ячейки заголовка
    r.setHeight((short) 450);
    // устанавливаем стиль для ячейки
    c.setCellStyle(cs3);
    // текст для заголовка
    c.setCellValue("Заголовок отчета за V квартал");
   
    rownum++;
   
    // идем по строкам текстового файла
    while( (line = in.readLine()) != null) {
      if(line.trim().length()==0) break;
     
      // создаем новую строку
      r = s.createRow(rownum);
      //уст. высоту
      r.setHeight((short) 400);
     
      // разбиваем строку на токены, разделитель "#"
      st = new StringTokenizer(line, "#");
     
      n = st.countTokens();
      String[] a = new String[n];
     
      for (int j = 0; j < n; j++) {
       
        a[j] = st.nextToken();
        cellnum = (short) j;
        // создаем ячейку
        c = r.createCell(cellnum);
        // первая ячейка пошире и выравниваем шрифт по центру
        if (j == 0) {
                c.setCellStyle(cs);
                s.setColumnWidth((short) cellnum, (short) 14000);
        }
        //остальные используют стиль cs2
        else {
                c.setCellStyle(cs2);
                s.setColumnWidth((short) cellnum, (short) 3500);
        }
        // устанавливаем значение ячейки
        c.setCellValue(a[j]);
       
      }
      // переходим к следующей строке
      rownum++;
     
    }
   
    // Закрываем поток чтения файла
    in.close();
   
    // записываем информацию и закрываем выходной поток
    wb.write(out);
    out.close();
       
    return;
  }

  public static void main (String args[]) throws Exception {
        String file = "";
               
        for(int n = 0; n < args.length; n++) {
            if (args[n].equals("-f")) file = args[++n];
            else throw new IllegalArgumentException("Неверный аргумент!");
        }
        new POIExample (file);
       
  }

}

В ОС Windows, например, программу можно запустить таким командным файлом, задав имя файла для обработки в параметре командной строки

cd path\to\program
SET CLASSPATH=.;path\to\poi-3.15\poi-3.15.jar
java your.packet.POIExample -f %1
start /b path\to\program\example.xls

Полный код POIExample.java можно скачать здесь.
Еще пример создания отчета по такой технологии (настройки отчета выведены в текстовый файл) - https://github.com/situla/POIMultilineReport. Отчет успешно применяется при создания спецификации из .bom-файла (Bill of Materials - перечень элементов) программы P-CAD для организации, в которой я в настоящее время работаю.

Мы рассмотрели ключевые моменты использования технологии POI для формирования отчета и выяснили - эта библиотека с открытым исходным кодом помогает быстро и качественно "приручить" Excel. За остальным обращайтесь к справке [8], которая, кстати сказать, хорошо документирована. Отмечу также скорость формирования отчета с помощью POI, она приятно удивляет! А Вам желаю удивить коллег отличным отчетом!

1. http://www.learn2prog.ru/ooo-report, "Java - отчет с помощью OpenOffice.org Writer"
2. Сайт проекта POI https://poi.apache.org
3. https://en.wikipedia.org/wiki/Talk%3AApache_POI
4. https://poi.apache.org/download.html#POI-3.15
5. https://poi.apache.org/spreadsheet/how-to.html
6. https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Font.html
7. https://poi.apache.org/apidocs/deprecated-list.html
8. https://poi.apache.org/apidocs/index.html

Tags: