Java excel границы ячеек
в начале я хочу сказать, что я совершенно новый в мире разработчиков.
Я попытался создать лист excel, содержащий таблицу Mutiplication с границами и установить цвет фона, но только для 1-го столбца и строки.
Я написал что-то подобное, но в результирующем файле цветные ячейки не имеют границ :(.
пожалуйста, объясните мне, как установить цвет фона и границы на в то же время.
изменить backgroundStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); to
и вы можете установить границы, как показано ниже :
Это даст вам желтый цвет и границу по мере необходимости
ваша реальная проблема заключается в том, что у вас есть два стиля, один с именем backgroundStyle, а другой с именем borderStyle. Затем вы применяете оба стиля к одной ячейке, но ячейка может иметь только один стиль, поэтому вместо добавления второго стиля вы перезаписываете первый стиль вторым стилем.
просто создайте один стиль, как это:
затем примените это к своему ячейка:
Примечание: как упоминалось в других ответах здесь, цвет фона игнорируется для FillPattern = SOLID_FOREGROUND, вы должны установить цвет переднего плана для этого шаблона. Это может быть запутанным, потому что вы пытаетесь установить фон ячейки в сплошной цвет. Но!--3--> - это не то же самое как background color . Cell background это то же самое, что Fill Pattern , который имеет два цвета Foreground Color и Background Color они отображаются на основе конкретных Fill Pattern выбранные. Этот SOLID_FOREGROUND fill использует только Foreground Color .
по состоянию на POI 3.x, цвет заливки ячейки устанавливается следующим образом:
CellStyle cs = wb.createCellStyle(); цезий.setFillForegroundColor (IndexedColors.GREY_25_PERCENT.метода getIndex()); цезий.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Я нашел один способ, как решить эту проблему, но я почти уверен, что есть другой более короткий путь.
Свойства и методы взаимодействия 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 представлены свойства и методы взаимодействия 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 представляет собой 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 и определение стилей ячеек рассмотрены здесь.
Apache POI-это популярная библиотека Java с открытым исходным кодом, которая предоставляет программистам API для создания, изменения и отображения файлов MS Office .
Он использует Workbook для представления файла Excel и его элементов. Ячейка в файле Excel может иметь различные типы, такие как ФОРМУЛА .
Чтобы увидеть POI Apache в действии, мы установим формулу для вычитания суммы значений в столбцах A и B в файле Excel . Связанный файл содержит следующие данные:
3. Зависимости
Во-первых, нам нужно добавить зависимость POI в ваш проект pom.xml файл. Для работы с книгами Excel 2007+ мы должны использовать poi-ooxml :
Обратите внимание, что для более ранних версий Excel мы должны использовать точка вместо этого зависимость.
4. Поиск ячеек
Для начала давайте сначала откроем наш файл и создадим соответствующую рабочую книгу:
Затем нам нужно создать или найти ячейку, которую мы будем использовать . Используя данные, которыми мы поделились ранее, мы хотим отредактировать ячейку C1.
Это на первом листе и в первой строке, и мы можем запросить POI для первого пустого столбца:
5. Формулы
Затем мы хотим задать формулу для ячейки, которую мы искали.
Как было сказано ранее, давайте вычтем сумму столбца B из суммы столбца A. В Excel это было бы:
И мы можем записать это в нашу ячейку формулы с помощью метода setCellFormula :
Теперь это не будет оценивать формулу. Для этого нам нужно будет использовать POI XSSFFormulaEvaluator :
Результат будет установлен в первой ячейке следующего пустого столбца:
Как мы видим, результат вычисляется и сохраняется в первой ячейке столбца C. Также формула отображается в строке формул.
Обратите внимание, что класс Formula Evaluator предоставляет нам другие методы для оценки FORMULA в книгах Excel , такие как evaluateAll , которые будут перебирать все ячейки и оценивать их.
6. Заключение
В этом уроке мы показали, как задать формулу для ячейки в файле Excel на Java с помощью API Apache POI.
Читайте также: