Apache POI HSSF — как «приручить» EXCEL
Опубликовано: журнал «Системный администратор» №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.
Итак, скачиваем стабильную версию 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.
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 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.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.
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];
a[j] = st.nextToken();
cellnum = (short) j;
// создаем ячейку
c = r.

// первая ячейка пошире и выравниваем шрифт по центру
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
Работа с Excel-файлами в Scala
В сей заметке рассматривается код на Scala, генерирующий электронную таблицу в формате xlsx, содержащую, помимо данных в ячейках, формулы, графики и украшательства типа выделения текста жирным шрифтом. Как обычно, по ходу изложения объясняются особенности языка Scala, которые ранее не были рассмотрены в этом блоге.
В мире Java для работы с разными офисными форматами файлов существует замечательная библиотека POI. Помимо хорошей документации имеется масса примеров использования этой библиотеки. Есть несколько оберток над POI для Scala (например, раз и два), но они мне что-то не очень понравились, поэтому в этой заметке будет использована просто POI, безо всяких оберток.
Нам понадобится build.sbt примерно следующего содержания:
name := «poi-example»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. К разработке stat.nic.ru, кстати, в свое время я имел непосредственное отношение.
package me.eax.poi_exampleimport 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. Наше приложение может получить содержимое этих файлов, сказав:
getClass.getResourceAsStream(«/путь-к-файлу»)
С помощью этого кода мы получаем экземпляр класса InputStream или null в случае, если ресурс не найден. Было бы странно таскать template.xlsx отдельно от standalone jar, поэтому мы включаем template.xlsx в наш jar описанным выше способом.
Еще в приведенном коде не совсем понятной для нас является строка:
for(key @ (rowNumber, rowName) <- data.keys) {
Вызов 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, а также других функциональных языков программирования
Метки: Scala, Функциональное программирование.
Spring MVC — загрузка файлов, создание (генерация) Excel и PDF документов
<?xml version=»1.0″ encoding=»UTF-8″?>
<project xmlns=»http://maven.apache.org/POM/4.0.0″
xmlns:xsi=»http://www.w3.org/2001/XMLSchema-instance»
xsi:schemaLocation=»http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd»>
<modelVersion>4.0.0</modelVersion>
<groupId>ru.javastudy</groupId>
<artifactId>mvc_html5_angular</artifactId>
<version>1.0</version>
<properties>
<!— Generic properties —>
<java.version>1.8</java.version>
<!— Web —>
<jsp.version>2.2</jsp.version>
<jstl.version>1.2</jstl.version>
<servlet.version>3.1.0</servlet.version>
<!— Spring —>
<spring-framework. version>4.2.4.RELEASE</spring-framework.version>
</properties>
<dependencyManagement>
<!—all spring dependencies —>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-framework-bom</artifactId>
<version>${spring-framework.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<!—bootstrap webjars.org—>
<dependencies>
<!— Spring MVC —>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
</dependency>
<!— Other Servlet Web dependencies —>
<dependency>
<groupId>javax. servlet</groupId>
<artifactId>jstl</artifactId>
<version>${jstl.version}</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>${servlet.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>${jsp.version}</version>
<scope>provided</scope>
</dependency>
<!— Apache Commons File Upload —>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1. 3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<!— Excel view —>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.12</version>
</dependency>
<!— PDF view —>
<dependency>
<groupId>com.lowagie</groupId>
<artifactId>itext</artifactId>
<version>2.1.5</version>
</dependency>
</dependencies>
<!—http://startbootstrap.com/template-overviews/modern-business/ this is theme—>
<build>
<plugins>
<plugin>
<groupId>org. apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.0</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<compilerArgument>-Xlint:all</compilerArgument>
<showWarnings>true</showWarnings>
<showDeprecation>true</showDeprecation>
</configuration>
</plugin>
</plugins>
</build>
</project>
Примеры HSSF и XSSF
Apache POI поставляется с рядом примеров, демонстрирующих, как вы
может использовать POI API для создания документов из «реальной жизни».
Приведенные ниже примеры основаны на общих интерфейсах XSSF-HSSF, чтобы вы
может генерировать либо * . xls, либо * .xlsx вывод, просто установив
аргумент командной строки:
BusinessPlan -xls
или же
BusinessPlan -xlsx
Все исходники сэмплов доступны в SVN
Кроме того, есть несколько Только HSSF и XSSF также только примеры.
Доступные примеры
Доступны следующие примеры:
Бизнес-план
Бизнес-план приложение создает образец бизнес-плана с тремя фазами, еженедельными итерациями и выделением времени. Демонстрирует расширенное форматирование ячеек (форматы чисел и даты, выравнивания, заливки, границы) и различные настройки для организации данных на листе (закрепленные панели, сгруппированные строки).
Календарь
Календарь demo создает календарь из нескольких листов.Каждый месяц на отдельном листе.
Кредитный калькулятор
Кредитный калькулятор
demo создает простой калькулятор кредита. Демонстрирует расширенное использование формул ячеек и именованных диапазонов.
Табель учета рабочего времени
Табель учета рабочего времени demo создает еженедельное расписание с автоматическим подсчетом общего количества часов. Демонстрирует передовое использование формул ячеек.
Условные форматы
Условные форматы demo — это набор коротких примеров, показывающих, что вы можете делать с условным форматированием Excel в POI:
- Выделение ячеек на основе их значений
- Выделить диапазон ячеек на основе формулы
- Скрыть ошибки
- Скрыть повторяющиеся значения
- Выделить повторяющиеся записи в столбце
- Выделить элементы, которые находятся в списке на листе
- Выделите платежи, подлежащие оплате в ближайшие тридцать дней
- Заштриховать чередующиеся строки на листе
- Заштриховать полосы строк на листе
Примеры формул
Расчет ипотеки
пример демонстрирует простую определяемую пользователем функцию для вычисления
основная сумма и проценты.
The CheckFunctionsSupported пример показывает, как проверить, какие функции и формулы не поддерживается из данного файла.
The SettingExternalFunction пример демонстрирует, как использовать предоставленные извне (сторонние) надстройки формул.
Пример UserDefinedFunction пример демонстрирует, как вызвать пользовательскую функцию для данный экземпляр Workbook с использованием реализации POI UDFFinder.
Добавить размерное изображение
AddDimensionedImage пример демонстрирует, как добавить изображение на рабочий лист и установить, что размер изображения до определенного количества миллиметров независимо от ширина столбцов или высота строк.
Выровненные ячейки
Выравнивающие клетки пример демонстрирует, как работают различные варианты выравнивания.
Детали стиля ячейки
The CellStyleDetails
пример демонстрирует, как читать стили Excel для ячеек.
Связанные раскрывающиеся списки
LinkedDropDownLists пример демонстрирует один метод, который можно использовать для создания связанных или зависимые раскрывающиеся списки.
Общий тест производительности SS
SSPerformanceTest example предоставляет способ создания простых файлов-примеров различных размеров и рассчитать, сколько времени они займут.Полезно для сравнительного анализа ваша система, а также проверить, не вызвана ли низкая производительность Apache POI или свой собственный код.
ToHtml
The ToHtml В примере показано, как отображать электронную таблицу в HTML, используя классы для отображения электронной таблицы.
ToCSV
ToCSV Пример демонстрирует один из способов преобразовать электронную таблицу Excel в файл CSV.
Как писать файлы Excel на java с помощью Apache POI
package org. arpit.java2blog;
импорт java.io.File;
импорт java.io.FileOutputStream;
import java.io.IOException;
импорт java.util.ArrayList;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
импорт org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
открытый класс WriteExcelMain {
public static void main (String [] args) выдает исключение IOException {
writeFileUsingPOI ();
}
public static void writeFileUsingPOI () выдает исключение IOException
{
// создать пустую книгу
XSSFWorkbook workbook = new XSSFWorkbook ();
// Создайте пустой лист
XSSFSheet sheet = workbook.createSheet («Страна»);
ArrayList
data.add (new String [] {«Страна», «Столица», «Население»});
data. add (новый Объект [] {«Индия», «Дели», 10000});
data.add (новый Объект [] {«Франция», «Париж», 40000});
data.add (новый Объект [] {«Германия», «Берлин», 20000});
data.add (новый Объект [] {«Англия», «Лондон», 30000});
// Обход данных и запись на лист
int rownum = 0;
для (Объект [] страны: данные)
{
Строка строка = лист.createRow (rownum ++);
int cellnum = 0;
для (Объект объекта: страны)
{
Cell cell = row.createCell (cellnum ++);
if (obj instanceof String)
cell.setCellValue ((String) obj);
иначе, если (экземпляр объекта Double)
cell.setCellValue ((Double) obj);
иначе, если (obj instanceof Integer)
cell.setCellValue ((Integer) obj);
}
}
try
{
// Запишите книгу в файловую систему
FileOutputStream out = new FileOutputStream (новый файл («CountryDetails.xlsx «));
workbook. write (out);
out.close ();
System.out.println (» CountryDetails.xlsx был успешно создан «);
}
catch (исключение e )
{
e.printStackTrace ();
}
finally {
workbook.close ();
}
}
}
apache poi О проекте Apache POI: Задача проекта Apache POI заключается в создании и поддержке API Java для управления различными форматами файлов на основе стандартов Office Open XML (OOXML) и формата составных документов Microsoft OLE 2 (OLE2).Мы используем модуль парсера CSV, представленный в соответствующей статье. Инженеры Apache должны попытаться изучить это, и они видят, что Microsoft создала сложные форматы без необходимости. Ячейка Apache POI Excel. По крайней мере, Java 8, Eclipse 4.12, Apache POI 4.1.1, Gradle 5.6, maven 3.6.1. 0. HSSFWorkbook, HSSFSheet. 5. Khi lập trình với bất cứ hệ thống nào thì việc thao tác với các file Excel luôn là iều bắt buộc mọi developer phải nắm được.

















вызывает ли олмесартан выпадение волос, Восхождение на Маттерхорн, Обзоры на Pepsi, Холодное сердце 2 DVD Asda, Apache Poi Excel, Где смотреть One Piece UK, Апартаменты Bay Villa — Bay Minette, Al, Белый кот и монах Pdf,
Чтение файлов Microsoft Excel XLSX на Java | by Sylvain Saurel
В предыдущем уроке вы узнали, как создавать файлы Microsoft Excel XLSX на Java.Сегодня вы узнаете, как читать файлы Microsoft Excel XLSX в приложении Java.
Для этого мы собираемся использовать отличный API Apache POI, доступный прямо здесь: https://poi. apache.org/download.html
Обратите внимание, что вы можете посмотреть это руководство в виде видео на YouTube:
Сначала Шаг состоит в том, чтобы добавить Apache POI в качестве зависимости Maven в файл сборки нашего проекта Java:
Мы выбрали чтение файлов Excel XLSX в этом руководстве, но логика такая же, как и для чтения файлов более старого формата Excel XLS.Итак, мы добавили обе зависимости в наш Maven POM, но для нашего примера нужен только poi-ooxml.
Сначала нам нужно загрузить файл Excel XLSX для чтения. Наш файл будет называться contacts.xlsx и будет содержать данные, записанные в нашем предыдущем руководстве, в котором вы узнаете, как создавать файлы Excel XLSX на Java.
Итак, мы создаем объект File с именем файла в параметре, а затем мы создаем экземпляр FileInputStream для этого файла. Этот экземпляр FileInputStream будет передан в параметре объекта XSSFWorkbook во время его создания. XSSFWorbook — это корневой объект, моделирующий файл Excel XLSX в библиотеке Apache POI.
Это дает нам следующий код для загрузки файла:
Теперь мы читаем данные файла Excel XLSX. Сначала мы получаем первый лист в XSSFWorkbook , вызывая метод getSheetAt для экземпляра XSSFWorkbook , созданного ранее.
Затем мы получаем итератор для перебора строк этого листа. Обратите внимание, что лист представлен в API объектом XSSFSheet .Чтобы выполнить итерацию по каждой строке нашего листа, нам нужно использовать объект Iterator , который мы получили, как обычно в Java.
Таким же образом для каждой строки мы будем перебирать каждую ячейку. Для этого мы вызываем метод cellIterator текущего экземпляра объекта Row . И мы перебираем объекты Cell текущего экземпляра Row . Для каждой ячейки мы получаем значение в виде строки и отображаем его в консоли.
Наконец, мы не забываем закрыть открытые потоки, вызвав методы close объектов XSSFWorkbook и FileInputStream .
Он дает нам следующий полный код для нашего класса ReadExcel :
При выполнении класса ReadExcel на JVM файл contacts.xlsx должен быть прочитан на вашем компьютере, и вы должны получить следующий результат:
Это все для этого учебника.
Если вы хотите узнать, как создавать файлы Microsoft Excel XLSX на Java, вы можете прочитать наше специальное руководство на Medium:
. Чтобы узнать больше руководств по разработке Java и Android, вы можете подписаться на канал SSaurel:
How to чтение / запись файла Excel с помощью Apache POI »grokonez
package com.javasampleapproach.excelpoi;
импорт java.io.FileOutputStream;
import java.io.IOException;
импорт java.util.Arrays;
импорт java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org. apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
импорт орг.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
открытый класс WriteExcel {
частный статический String [] COLUMNs = {«Id», «Name», «Address», «Age»};
частный статический список
новых клиентов («1», «Джек Смит», «Массачусетс», 23),
новых клиентов («2», «Адам Джонсон», » Нью-Йорк », 27),
новый клиент (« 3 »,« Кэтрин Картер »,« Вашингтон, округ Колумбия », 26),
новый клиент (« 4 »,« Джек Лондон »,« Невада », 33),
новый Клиент («5», «Джейсон Борн», «Калифорния», 36));
public static void main (String [] args) выдает исключение IOException {
Workbook workbook = new XSSFWorkbook ();
CreationHelper createHelper = workbook. getCreationHelper ();
Sheet sheet = workbook.createSheet («Клиенты»);
Заголовок шрифтаFont = workbook.createFont ();
headerFont.setBold (true);
headerFont.setColor (IndexedColors.BLUE.getIndex ());
Заголовок CellStyleCellStyle = workbook.createCellStyle ();
headerCellStyle.setFont (headerFont);
// Строка для заголовка
Заголовок строкиRow = sheet.createRow (0);
// Заголовок
для (int col = 0; col Cell cell = headerRow.createCell (col); cell.setCellValue (COLUMNs [col]); cell.setCellStyle (headerCellStyle); } // CellStyle для возраста CellStyle ageCellStyle = workbook.createCellStyle (); ageCellStyle.setDataFormat (createHelper.createDataFormat ().getFormat («#»)); int rowIdx = 1; для (Клиент-клиент: клиенты) { Row row = sheet. row.createCell (0) .setCellValue (customer.getId ()); row.createCell (1) .setCellValue (customer.getName ()); row.createCell (2) .setCellValue (customer.getAddress ()); Cell ageCell = row.createCell (3); ageCell.setCellValue (клиент.getAge ()); ageCell.setCellStyle (ageCellStyle); } FileOutputStream fileOut = новый FileOutputStream («customers.xlsx»); workbook.write (fileOut); fileOut.close (); workbook.close (); } } В этом сообщении в блоге я предлагаю шаблон дизайна кода (на Scala), который я придумал в очень коротком, но полноценном опыте работы над небольшой проект, основанный на Apache POI. Если вы когда-либо работали с такой библиотекой, как Apache POI, вы могли убедиться, как быстро ваш код превращается в спагетти-код. Хотя у всех нас есть свои способы рефакторинга и решения такой проблемы, в этом сообщении в блоге я хотел бы поделиться своим личным путешествием. Представьте, что в вашей организации есть биометрический сканер, который позволяет только сотрудникам получить доступ через ворота офиса, и теперь вы также хотите использовать журналы, созданные биометрическим сканером, в качестве журналов посещаемости сотрудников, а затем создавать отчет о посещаемости в формате Excel, который должен что-то искать как это: Чтобы добиться того же, мы можем использовать библиотеку Apache POI. Это отличная библиотека для управления различными форматами файлов, основанная на стандартах Office Open XML (OOXML) и формате Microsoft OLE 2 Compound Document (OLE2). Мы можем в основном разделить шаблон дизайна кода на пять разумных шагов при записи в файлы Excel с использованием библиотеки Apache POI: Идея состоит в том, чтобы прежде всего определить все независимые разделы, которые вы хотели бы написать. Обратите внимание, что, например, После того, как секции определены, нам нужно определить размеры этих секций. Далее, соблюдая принцип дизайна SRP, мы можем определить стили для каждого раздела, как показано ниже. Определение подобных стилей позволило бы отделить логику представления от логики рендеринга. Это также делает стили подключаемой функцией. Писатели могли перейти на новые стили, просто расширив черту других стилей. Теперь вы можете реализовать писателей для каждого раздела, расширяя соответствующие характеристики стилей и используя размеры, предопределенные для каждого раздела. После определения всех писателей и соответствующих стилей вы можете объединить их все вместе в одно определение. Еще одна важная вещь, на которую следует обратить внимание, — это то, что вы не можете переопределить уже определенные строки, объединенные области и аналогичные компоненты, которые библиотека Apache POI помогает вам создать.Это влияет на порядок, в котором вы можете вызывать авторов разделов. Однако я решил эту проблему в своем решении, убедившись, что я не пытаюсь воссоздать строку, которая уже определена так: Окончательная структура модуля будет выглядеть примерно так: Здесь все заканчивается. Надеюсь, я смогу достаточно хорошо изложить эту идею! Полную рабочую реализацию этой утилиты можно найти здесь, на github. Apache POI — это API-интерфейсы Java для управления различными форматами файлов на основе стандартов Office Open XML (OOXML) и формата составных документов Microsoft OLE 2 (OLE2).Короче говоря, вы можете читать и писать файлы MS Excel с помощью Java. Если вы попытаетесь сгенерировать Excel с помощью класса XSSFWorkbook, вы можете столкнуться с крайней медлительностью, потому что, по всей видимости, все ячейки, добавленные в книгу, хранятся в памяти до тех пор, пока Excel не будет сохранен. Вы можете легко найти множество тем (например, это) об этой проблеме, и вместо этого для решения используется класс SXSSFWorkbook.Обе реализации реализуют интерфейс Workbook. SXSSFWorkbook — это потоковая версия XSSFWorkbook , реализующая стратегию BigGridDemo. Это позволяет записывать очень большие файлы без нехватки памяти, поскольку только настраиваемая часть строк хранится в памяти в любой момент. При создании экземпляра класса SXSSFWorkbook вы предоставляете параметр размера окна, который представляет количество строк, которые будут храниться в памяти. Все лишние строки будут сброшены на диск. Хотя решение простое, просто переключив класс книги, я хотел проанализировать, насколько плоха производительность XSSFWorkbook и как SXSSFWorkbook ее улучшает. Убедившись, что SXSSFWorkbook имеет лучшую производительность, я также зарегистрировал время генерации Excel со следующими окнами строк в памяти: 100, 200, 500, 1000, 5000, 10000 и 50000. Чтобы проверить производительность, я запустил код, представленный в разделе «Код», сначала не указав аргументов для запуска генерации Excel в памяти, а затем передав аргумент Как видно из приведенного ниже результата реализации в памяти, время выполнения увеличивается линейно, и хуже того, является недостаточный размер кучи. С другой стороны, реализация на основе окна способна работать за значительно меньшее время.Как видите, нет значительной разницы между разными размерами окон строк, за исключением большего окна в 50000 строк. В последнем окне приложение показывает более высокую загрузку кучи и процессора. Игнорировать первые два выполнения, которые использовались для прогрева JVM. Что касается используемой памяти, Java VisualVM показывает, что максимального размера кучи в 2 ГБ было достаточно для создания всех книг Excel. createRow (rowIdx ++);
Шаблон дизайна кода для программ записи Excel на основе Apache POI
Хотя я не утверждаю, что это идеальный подход, но IMO шаблон дизайна кода, который я собираюсь представить, определенно сохранит ваш дизайн в высокой степени модульным, поддерживаемым и читаемым. Кроме того, я хотел бы сразу указать, что, поскольку я не считаю написание Excel очень ресурсоемкой задачей, я пожертвовал модульностью, удобством обслуживания и удобочитаемостью с учетом временной сложности.
Другими словами, он предоставляет API-интерфейсы Java для чтения и записи файлов MS Excel помимо других форматов.
1. Определите разделы для записи
EmployeeInfo
и Attendance
попадают в один набор строк и фактически могут быть записаны вместе; Я настоятельно рекомендую держать такие разделы, которые связаны с различной логикой рендеринга, отдельно.Хотя, как уже упоминалось во вступлении к этому блогу, временная сложность снижается (и даже библиотека, кажется, поощряет иное), вы увидите дальше, насколько этот подход повлияет на модульность, ремонтопригодность и удобочитаемость кода.
2. Определите размеры идентифицированных секций
Цель здесь состоит в том, чтобы абстрагироваться от логики, связанной с определением размеров разделов в одном месте, вместо того, чтобы оставлять ее для всех авторов разделов.Авторы разделов могли бы не знать, как были определены размеры соответствующих разделов, и вместо этого работать непосредственно с размерами.
3. Абстрактные стили для каждого раздела в отдельных чертах
4. Реализация писателей для каждого раздела
5. Скомпонуйте все вместе
Решение медленной генерации Excel с использованием Apache POI
Кроме того, вы можете читать и писать файлы MS Word и MS PowerPoint с помощью Java.
Для этого я зарегистрировал время, которое требуется обоим классам для создания Excel с 20 столбцами и следующим количеством строк: 10000, 50000, 100000, 200000, 500000, 1000000 и 1048575 (максимальное поддерживаемое количество строк).
Результаты
stream
для запуска генерации Excel на основе окон. Основным различием между исполнениями был максимальный размер кучи, составлявший -Xmx4096m
и -Xmx2046m
соответственно.
Построение книги в обычном режиме
Прошло 3671 миллис для 10000 строк
Прошло 15416 миллисекунд для 50000 строк
Прошло 28284 миллисекунды для 100000 строк
Исключение в потоке "main" java.lang.OutOfMemoryError: превышен предел накладных расходов GC
в java.util.Arrays.copyOfRange (Arrays.java:3664)
в java.lang.String.
createCell (XSSFRow.java:45)
Построение книги в обычном режиме
Прошло 82 миллисекунды для 1 ряда
Прошло 11 миллисекунд для 2 рядов
Прошло 14 миллисекунд для 3 рядов
...
Прошло 19 миллисекунд для 98 рядов
Прошло 19 миллисекунд для 99 рядов
Прошло 19 миллисекунд для 100 рядов
Создание книги в потоковом режиме с окном на 1000 строк
Прошло 165 миллисекунд для 1 ряда
Прошло 2 миллиса на 2 ряда
Прошло 2 миллиса на 3 ряда
...
Прошло 2 миллиса для 98 рядов
Прошло 2 миллиса для 99 рядов
Прошло 2 миллиса на 100 рядов
Создание книги в потоковом режиме с окном 100 строк
Прошло 516 миллисекунд для 10000 строк
Прошло 2194 миллисекунды для 50000 строк
Прошло 4431 миллис для 100000 строк
Прошло 8665 миллисекунд для 200000 строк
Прошло 23958 миллисекунд для 500000 строк
Прошло 44459 миллисекунд для 1000000 строк
Прошло 48679 миллисекунд для 1048575 строк
Создание книги в потоковом режиме с окном на 200 строк
Прошло 459 миллисекунд для 10000 строк
Прошло 2294 миллисекунды для 50000 строк
Прошло 4606 миллисекунд для 100000 строк
Прошло 9404 миллисекунды для 200000 строк
Прошло 23654 миллисекунды для 500000 строк
Прошло 44025 миллисекунд для 1000000 строк
Прошло 46341 миллисекунд для 1048575 строк
Создание книги в потоковом режиме с окном на 500 строк
Прошло 430 миллисекунд для 10000 строк
Прошло 2183 миллисекунды для 50000 строк
Прошло 4682 миллисекунды для 100000 строк
Прошло 8966 миллисекунд для 200000 строк
Прошло 22455 миллисекунд для 500000 строк
Прошло 44293 миллисекунды для 1000000 строк
Прошло 53461 миллисекунд для 1048575 строк
Создание книги в потоковом режиме с окном на 1000 строк
Прошло 445 миллисекунд для 10000 строк
Прошло 2433 миллисекунды для 50000 строк
Прошло 4481 миллис для 100000 строк
Прошло 9141 миллисекунды для 200000 строк
Прошло 22924 миллисекунды для 500000 строк
Прошло 44966 миллисекунд для 1000000 строк
Прошло 50804 миллисекунды для 1048575 строк
Создание книги в потоковом режиме с окном 5000 строк
Прошло 292 миллисекунды для 10000 строк
Прошло 2126 миллисекунд для 50000 строк
Прошло 4433 миллисекунды для 100000 строк
Прошло 8866 миллисекунд для 200000 строк
Прошло 22163 миллисекунды для 500000 строк
Прошло 43857 миллисекунд для 1000000 строк
Прошло 50241 миллисекунд для 1048575 строк
Создание книги в потоковом режиме с окном 10000 строк
Прошло 107 миллисекунд для 10000 строк
Прошло 2317 миллисекунд для 50000 строк
Прошло 4308 миллисекунд для 100000 строк
Прошло 8898 миллисекунд для 200000 строк
Прошло 22792 миллисекунды для 500000 строк
Прошло 44153 миллисекунды для 1000000 строк
Прошло 50700 миллисекунд для 1048575 строк
Создание книги в потоковом режиме с окном 50000 строк
Прошло 98 миллисекунд для 10000 строк
Прошло 604 миллисекунды для 50000 строк
Прошло 3426 миллисекунд для 100000 строк
Прошло 8415 миллисекунд для 200000 строк
Прошло 22838 миллисекунд для 500000 строк
Прошло 185916 миллисекунд для 1000000 строк
Прошло 235439 миллисекунд для 1048575 строк
Имейте в виду, что эти тесты представляют собой наихудший сценарий, когда перед построением книги у нас уже есть сетка (массив массивов), выделенная на объектах кучи, где каждый объект содержит значение ячейки. Оптимизированная реализация с использованием итераторов (если возможно), которая выбирает каждую строку из источника только тогда, когда это необходимо для заполнения книги, будет иметь очень мало памяти. Это в сочетании с реализацией книги на основе скользящего окна приведет к быстрой и легкой генерации Excel.
Код
. импорт java.io.FileOutputStream
импорт java.util.UUID
импортировать org.apache.poi.ss.usermodel.Workbook
импортировать org.apache.poi.xssf.streaming.SXSSFWorkbook
импортировать org.apache.poi.xssf.usermodel.XSSFWorkbook
object TestExcelGeneration {
частный val rowNumberValues = Seq (10000, 50000, 100000, 200000, 500000, 1000000, 1048575)
частный поток val streamWindowValues = Seq (100, 200, 500, 1000, 5000, 10000, 50000)
val currentMillis = System.
currentTimeMillis ()
def main (args: Array [String]): Unit = {
val streamMode =! args.isEmpty && args (0) .toLowerCase (). startWith ("поток")
// Разминка JIT
buildWorkbook (от 1 до 100)
buildWorkbook (от 1 до 100, streamMode = true)
if (streamMode) {
streamWindowValues.foreach {streamWindow =>
buildWorkbook (rowNumberValues, streamMode = true, streamWindow)
}
}
еще {
buildWorkbook (rowNumberValues)
}
}
def buildWorkbook (rowNumberValues: Seq [Int], streamMode: Boolean = false, streamWindow: Int = 1000): Unit = {
println (s "\ nСоздание книги в режиме потока $ {if (streamMode) s" с окном строки $ streamWindow "else" нормальный режим "}")
rowNumberValues.foreach {numRows =>
val content: Seq [Seq [Any]] = (от 0 до numRows) .map {row =>
Seq (
row, Math.random () * 1000000, новый java.util.Date (currentMillis), UUID.randomUUID (), UUID.randomUUID (),
UUID.randomUUID (), UUID.randomUUID (), UUID.
randomUUID (), UUID.randomUUID (), UUID.randomUUID (),
UUID.randomUUID (), UUID.randomUUID (), UUID.randomUUID (), UUID.randomUUID (), UUID.randomUUID (),
UUID.randomUUID (), UUID.randomUUID (), UUID.randomUUID (), UUID.randomUUID (), UUID.randomUUID ()
)
}
val workbook = if (streamMode) new SXSSFWorkbook (streamWindow) else new XSSFWorkbook ()
val thenMillis = System.currentTimeMillis ()
addSheet (книга, s "name_ $ numRows", содержание)
val nowMillis = System.currentTimeMillis ()
val elapsedMillis = nowMillis - thenMillis
workbook.write (новый FileOutputStream (s "/tmp/excel/excel_$numRows.xlsx"))
println (s "Прошло $ elapsedMillis миллис для строк $ numRows")
}
}
def addSheet (workbook: Workbook, name: String, content: Seq [Seq [Any]]) = {
val wkbSheet = книга.createSheet (имя)
val totalRows = content.size
val totalCells = content.head.size
(От 0 до totalRows) .foreach {rowId =>
val row = wkbSheet.
createRow (rowId)
(От 0 до totalCells) .foreach {cellId =>
val ячейка = row.createCell (cellId)
content (rowId) (cellId) соответствует {
case v if v == null => cell.setBlank ()
case v: Int => cell.setCellValue (v)
case v: Long => cell.setCellValue (v)
case v: Double => cell.setCellValue (v)
case v: Float => ячейка.setCellValue (v)
case v: Boolean => cell.setCellValue (v)
case v: String => cell.setCellValue (v)
case v => cell.setCellValue (v.toString) // Для других типов просто установите их строковое представление
}
}
}
}
}