Java таблица как в excel

Время на прочтение
3 мин

Количество просмотров 44K

Работа с таблицей Excel из Java

Собственно возникла проблема — обработать данные из таблицы и на их основе получить другую таблицу.

Вариантов решения этой проблемы несколько:

1. Макрос — единственной проблемой является VBA, на изучение которого времени нет совершенно, да и не нравится его синтаксис
2. Приложение на C# тут вроде все хорошо, но к машине на которой будет выполняться данное приложение сразу предъявляется много дополнительных требований:
   • .NET Framework
   • Установленный офис
   • установленная основная сборка взаимодействия (PIA) для приложения Office

3. связка Java и библиотека Apache POI—на этом способе я и хочу остановиться подробнее

Apache POI — это Java API для доступа к документам формата Microsoft.

Собственно для работы с таблицами Excel предназначен проект POI-HSSF. На данный момент есть 2 варианта библиотеки:

1. POI 3.5 beta 5, and Office Open XML Support (2009-02-19)—идет работа над поддержкой формата Office 2007
2. POI 3.2-FINAL Released (2008-10-19) — последний стабильный релиз

Я расскажу о работе с версией 3.2
Основным классом для работы с таблицей Excel является класс HSSFWorkbook пакета org.apache.poi.hssf.usermodel, представляющий книгу Excel.

Для чтения книги из файла можно применить следующий код:

public static HSSFWorkbook readWorkbook(String filename) {
       try {
               POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
               HSSFWorkbook wb = new HSSFWorkbook(fs);
               return wb;
       }
       catch (Exception e) {
               return null;
       }
}

Метод возвращает объект класса HSSFWorkbook если все удачно и null в другом случае.

Для сохранения изменений можно применить следующий метод:

public static void writeWorkbook(HSSFWorkbook wb, String fileName) {
       try {
               FileOutputStream fileOut = new FileOutputStream(fileName);
               wb.write(fileOut);
               fileOut.close();
       }
       catch (Exception e) {
               //Обработка ошибки
       }
}        

Метод записывает книгу wb в файл fileName

Для работы с листами рабочей книги предназначен класс HSSFSheet.
Объект класса HSSFSheet можно получить следующими способами:

• По имени
  HSSFSheet sheet= wb.getSheet("Лист 3")
• По номеру (нумерация начинается с 0)
  HSSFSheet sheet= wb.getSheet(0)
• Создание нового листа
  HSSFSheet sheet= wb.createSheet(["имя листа"])

Следующим объектом является строка — HSSFRow.
Получить доступ к объекту строки можно также 3 способами:

• По индексу (индексация начинается с 0)
  HSSFRow row = sheet.getRow(index)
• Через итератор
  

  Iterator rowIter = sheet.rowIterator();
  while (rowIter.hasNext()) {
         HSSFRow row = (HSSFRow) rowIter.next();
  }

• Создание новой строки
  HSSFRow row=sheet.createRow(0);

Ну и последним объектом на пути доступа к данным является объект класса HSSFCell — ячейка строки.
Получить доступ к ячейке можно из объекта строки следующими способами:

• По индексу ячейки (индексация начинается с 0)
  HSSFCell cell = row.getCell(0);
• Через итератор
  

  Iterator cellIter = row.cellIterator();
  while (cellIter.hasNext()) {
         HSSFCell cell = (HSSFCell) cellIter.next();
  }
  

• Создание новой ячейки
  HSSFCell cell = row.createCell(0);

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

• Логическое значение
  boolean b = cell.getBooleanCellValue();
cell.setCellValue(b);

• Дата
  Date date = cell.getDateCellValue();
cell.setCellValue(date);

• Числовое значение
  double d = cell.getNumericCellValue();
cell.setCellValue(d);

• Строковое значение
  String str = cell.getRichStringCellValue().getString();
cell.setCellValue(new HSSFRichTextString(str));

• Формула
  String formula = cell.getCellFormula();
cell.setCellFormula(formula);


Этих знаний достаточно чтобы обрабатывать простые таблицы.
Библиотека также предоставляет богатые возможности по форматированию ячеек, по их слиянию, заморозке и т.д.
Подробное описание функций можно найти на их сайте.
Данный способ прежде всего ценен тем, что не требует установки самого офиса и пакета PIA.

Свойства и методы взаимодействия Java приложений с файлами Excel представлены на странице
библиотеки Apache POI. Здесь рассматриваются примеры использования
Apache POI для создания файлов Excel 2007 или более поздней версии. Если необходимо создать
Excel 1997-2003, то следует использовать классы, наименования которых начинаются с символа ‘H’.
Использование библиотеки Apache POI для чтения файлов Excel с примером рассмотрен
здесь.

Создание книги Excel

// Создание книги Excel
XSSFWorkbook book = new XSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");

// создания страниц
// создание строк
// создание и форматирование ячеек
// запись информации в ячейки

// Закрытие
book.write(fileOut);
fileOut.close();

Создание страницы

XSSFSheet sheet2 = book.createSheet("Sheet 1");
XSSFSheet sheet3 = book.createSheet("Страница 2");

Наименование страницы не должно превышать 31 символ. Следующие символы недопустимы в
наименовании страницы :

   0x0 — нулевое значение;
   ‘:’ — двоеточие;
   » — обратный слэш;
   ‘/’ — прямой слэш;
   ‘*’ — «звездочка»;
   ‘?’ — вопросительный знак;
   ‘[‘ — открывающаяся квадратная скобка;
   ‘]’ — закрывающаяся квадратная скобка.

Можно использовать утилиту WorkbookUtil для получения наименования страницы книги. Метод
createSafeSheetName данной утилиты заменит «неправильные» символы на пробелы (‘ ‘).

import org.apache.poi.ss.util.WorkbookUtil;

String caption  = "[O'Brien's sales*?]";
String safeName = WorkbookUtil.createSafeSheetName(caption);

// safeName = " O'Brien's sales   ";
XSSFSheet sheet3 = book.createSheet(safeName);

Определение размера колонки

При определении размера колонки необходимо учитывать максимальное количество символов в колонке,
коэффициент размера символа для заданного шрифта (для «Sans Sherif» равен 1.14388) и коэффициент
EXCEL_COLUMN_WIDTH_FACTOR, равный 256. Метод autoSizeColumn(idx_column) позволяет автоматически
установить размер колонки по максимальному значению.

// Определение размера колонки
int width = (int) (6 * 1.14388) * 256; // 1757;
sheet.setColumnWidth(0, width);

// Автоматическая настройка размера колонки
sheet.autoSizeColumn(1);

Метод setColumnWidth(column, width) в качестве параметров принимает номер колонки (отсчет от 0) и
размер колонки. Методу autoSizeColumn(column) необходимо передать только номер колонки.

Создание строки

При создании строки в метод createRow в качестве параметра необходимо передать номер строки
(отсчет от 0). Для определения размера строки можно использовать методы setHeight (short) и
setHeightInPoints (float). При использовании метода setHeight также, как и с определением размера колонки,
необходимо учитывать коэффициенты. Поэтому метод setHeightInPoints оказывается более предпочтительным.

XSSFRow row = sheet.createRow((short)0);
row.setHeightInPoints(80.0f);

Создание ячейки, определение типа значения

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

XSSFCell cell = row.createCell(0);
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(1.2);

cell = row.createCell(1);
cell.setCellType(CellType.STRING);
cell.setCellValue("Строковое представление");

cell = row.createCell(2);
cell.setCellType(CellType.FORMULA);
cell.setCellValue("SUM(B3:B5)");

Класс CellType включает свойства [_NONE, BLANK, BOOLEAN, ERROR, FORMULA, NUMERIC, STRING],
которые можно использовать для определения типа значения ячейки.

Определение формата даты ячейки

В следующем примере создаются две ячейки с записью текущей даты. Вторая ячейка форматируется.

CreationHelper createHelper = book.getCreationHelper();

XSSFCellStyle cellStyle = book.createCellStyle();
cellStyle.setDataFormat(createHelper
                       .createDataFormat()
                       .getFormat("m/d/yy h:mm"));
XSSFCell cell = row.createCell(0);
cell.setCellValue(new Date());

cell = row.createCell(1);
cell.setCellStyle(cellStyle);
cell.setCellValue(new Date());

Слияние ячеек

Для слияния ячеек используется метод addMergedRegion.

// Создание строки
XSSFRow row = sheet.createRow((short) 1);

// Создание ячейки
XSSFCell cell = row.createCell((short) 1);
cell.setCellValue("This is a test of merging");

// Слияние 2-х ячеек в одной строке
sheet.addMergedRegion(
        new CellRangeAddress(1, // первая строка
                             1, // последняя строка
                             1, // первая колонка
                             2) // последняя колонка
);

Определение шрифта

// Создание шрифта
XSSFFont font = book.createFont();

font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);
// цвет шрифта
font.setColor(new XSSFColor(new java.awt.Color(16,64,255)));

// Создание стиля с определением в нем шрифта
XSSFCellStyle style = book.createCellStyle();
style.setFont(font);

// Создание ячейки с определением ее стиля
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(1);
cell.setCellValue("Тестовый шрифт");
cell.setCellStyle(style);

Помните, что максимальное количество создаваемых шрифтов ограничено значением 32767. Необходимо
использовать объекты шрифтов/стилей.

// Неправильно
for (int i = 0; i < 100; i++) {
    XSSFRow row = sheet.createRow(i);
    for (int j = 0; j < 100; j++) {
        XSSFCell cell = row.createCell((short) j);

        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        cell.setCellStyle(style);
    }
}

// Правильно
XSSFFont font = workbook.createFont();
font.setBold(true);

XSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);

for (int i = 0; i < 100; i++) {
    XSSFRow row = sheet.createRow(i);
    for (int j = 0; j < 100; j++) {
        XSSFCell cell = row.createCell((short) 0);
        cell.setCellStyle(style);
    }
}

Определение цвета фона ячейки

Color COLOR_light_gray  = new java.awt.Color(232, 232, 232);

XSSFCellStyle style = book.createCellStyle();

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

style.setFillForegroundColor(new XSSFColor(COLOR_light_gray));

ПРИМЕЧАНИЕ : для выделения цвета значения настраивайте шрифт (см. выше).

Выравнивание значения

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

createCell(wb, row, (short) 0, CellStyle.ALIGN_CENTER, 
                               CellStyle.VERTICAL_BOTTOM);
createCell(wb, row, (short) 1, CellStyle.ALIGN_CENTER_SELECTION,
                               CellStyle.VERTICAL_BOTTOM);
createCell(wb, row, (short) 2, CellStyle.ALIGN_FILL,
                               CellStyle.VERTICAL_CENTER);
createCell(wb, row, (short) 3, CellStyle.ALIGN_GENERAL,
                               CellStyle.VERTICAL_CENTER);
createCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY,
                               CellStyle.VERTICAL_JUSTIFY);
createCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT,
                               CellStyle.VERTICAL_TOP);
createCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT,
                               CellStyle.VERTICAL_TOP);

/**
 * Creates a cell and aligns it a certain way.
 *
 * @param book   книга Excel
 * @param row    строка
 * @param column колонка
 * @param halign горизонтальное выравнивание
 * @param valign вертикальное выравнивание
 */
private void createCell(Workbook book,
                        Row row, short column,
                        short halign, short valign)
{
    XSSFCell cell = row.createCell(column);
    cell.setCellValue("Значение");
    // Определение стиля
    XSSFCellStyle cellStyle = book.createCellStyle();
    // Настройка выравнивания стиля
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);
    // Стиль ячейки
    cell.setCellStyle(cellStyle);
}

Границы ячейки, Border

// Создание строки
XSSFRow row = sheet.createRow(1);
// Создание ячейки
XSSFCell cell = row.createCell(1);
cell.setCellValue("Значение");

// Создание стиля
XSSFCellStyle style = book.createCellStyle();

// Определение граничных значений стиля
style.setBorderTop   (CellStyle.BORDER_MEDIUM_DASHED);
style.setBorderRight (CellStyle.BORDER_THIN         );
style.setBorderBottom(CellStyle.BORDER_THIN         );
style.setBorderLeft  (CellStyle.BORDER_THIN         );
// Определение цвета граничных значений стиля
style.setTopBorderColor   (IndexedColors.BLACK.getIndex());
style.setRightBorderColor (IndexedColors.BLUE.getIndex ());
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor  (IndexedColors.GREEN.getIndex());

// Определение стиля ячейки
cell.setCellStyle(style);

Многострочные ячейки

Для фиксированного разделения/переноса текста в ячейке необходимо в стиле определить
свойство WrapText=true, и в тексте установить разделители ‘n’.

String text = "Фиксированное n разделение n текста";

XSSFCellStyle style = book.createCellStyle();
style.setWrapText(true);

XSSFRow row = sheet.createRow(2);
XSSFCell cell = row.createCell(2);
cell.setCellValue(text);
cell.setCellStyle(style);

Использование формулы

Создадим две ячейки. В первую ячейку cell1 запишем сумму значений колонки, а во второй ячейке
cell2 разместим формулу «СУММА()». Для вычисления значения первой ячейки дополнительно используются
методы getColsSummary и getCellValue.

int row = 5;
int summa = getColsSummary (1, 2, row);
XSSFCell cell1 = row.createCell(2);
if (summa > 0)
    cell1.setCellValue(summa);

XSSFCell cell2 = row.createCell(2);
String formula = String.format("SUM(B3:B%d)", row);
cell2.setCellFormula(formula);
//-------------------------------------------------------
private int getCellValue (final int cl, final int rw)
{
    XSSFRow  row  = sheet.getRow(rw);
    XSSFCell cell = row.getCell(cl);

    return (int)cell.getNumericCellValue(); 
}
//-------------------------------------------------------
private int getColsSummary (int col, int row1, int row2)
{
    int summary = 0;
    for (int i = row1; i < row2; i++)
        summary += getCellValue (col, i);
    return summary;
}

import com.gembox.spreadsheet.*;
import com.gembox.spreadsheet.tables.*;

class Program {

    public static void main(String[] args) throws java.io.IOException {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.setLicense("FREE-LIMITED-KEY");

        ExcelFile workbook = new ExcelFile();
        ExcelWorksheet worksheet = workbook.addWorksheet("Tables");

        // Add some data.
        Object[][] data = {
            { "Worker", "Hours", "Price" },
            { "John Doe", 25, 35.0 },
            { "Jane Doe", 27, 35.0 },
            { "Jack White", 18, 32.0 },
            { "George Black", 31, 35.0 }
        };

        for (int i = 0; i < 5; i++)
            for (int j = 0; j < 3; j++)
                worksheet.getCell(i, j).setValue(data[i][j]);

        // Set column widths.
        worksheet.getColumn(0).setWidth(100, LengthUnit.PIXEL);
        worksheet.getColumn(1).setWidth(70, LengthUnit.PIXEL);
        worksheet.getColumn(2).setWidth(70, LengthUnit.PIXEL);
        worksheet.getColumn(3).setWidth(70, LengthUnit.PIXEL);
        worksheet.getColumn(2).getStyle().setNumberFormat(""$"#,##0.00");
        worksheet.getColumn(3).getStyle().setNumberFormat(""$"#,##0.00");

        // Create table and enable totals row.
        Table table = worksheet.addTable("Table1", "A1:C5", true);
        table.setHasTotalsRow(true);

        // Add new column.
        TableColumn column = table.addColumn();
        column.setName("Total");

        // Populate column.
        for (ExcelCell cell : column.getDataRange())
            cell.setFormula("=Table1[Hours] * Table1[Price]");

        // Set totals row function for newly added column and calculate it.
        column.setTotalsRowFunction(TotalsRowFunction.SUM);
        column.getRange().calculate();

        // Set table style.
        table.setBuiltInStyle(BuiltInTableStyleName.TABLE_STYLE_MEDIUM_2);

        workbook.save("Tables.xlsx");
    }
}