Apache POI ułatwia przetwarzanie plików Microsoft Excel. W tym artykule sprawdzimy, jak realizować operacje odczytu i zapisu takich plików. Rzucimy również okiem na problemy, które mogą po drodze wystąpić.
Lecimy!
Co zrobimy z Apache POI?
Tym razem zajmiemy się biblioteką Apache POI na praktycznym przykładzie. Przede wszystkim interesuje nas, jak Apache POI pozwala na odczytywanie i zapisywanie plików Microsoft Excel zgodnie z Office Open XML (xlsx).
Żeby przykład był ciekawy przyjmijmy, że:
- Na wejściu chcielibyśmy dostarczyć plik Excel, który będzie zawierał dwa arkusze. Pierwszy z bieżącymi cenami akcji. Drugi z informacją, ile danych akcji posiadamy w naszym portfelu.
- Naszym wyjściem będzie prosty raport. Zawrzemy w nim informacje o tym, ile środków pieniężnych mamy w naszym portfelu w podziale na poszczególne akcje. Dodamy również wiersz podsumowujący wartość całego portfela, tak żeby przy okazji zobaczyć, jak ustawić formuły Excela z kodu.
Zrealizujmy teraz ten scenariusz.
Materiał wejściowy
Całą przygodę zaczniemy od przygotowania sobie pliku Excel. Ja skorzystam w tym miejscu z Google Sheets. Plik zawiera dwa arkusze:
- stocks z listą kodów NASDAQ firm oraz aktualną ceną jednostkową ich akcji wyrażoną w dolarach amerykańskich,
- wallet z naszym portfelem, czyli kodami NASDAQ firm w naszym posiadaniu oraz liczbą posiadanych akcji.
Ogólna struktura kodu
Prześledźmy teraz strukturę naszego kodu:
@Override
public void run(ApplicationArguments args) throws Exception {
String inputFile = "stock.xlsx";
List<StockPrice> stockPrices;
List<SharesNumber> sharesNumber;
try (Workbook workbook = new XSSFWorkbook(inputFile)) { // (1)
Sheet stocks = workbook.getSheet("stocks"); // (2)
stockPrices = stockPrices(stocks);
Sheet wallet = workbook.getSheet("wallet"); // (3)
sharesNumber = sharesNumber(wallet);
}
Workbook report = report(stockPrices, sharesNumber); // (4)
saveWorkbook(report, "report.xlsx");
}
Przede wszystkim początek naszego programu musi otworzyć plik Excel. Korzystamy przy tej okazji z wyrażenia try-with-resource i abstrakcji Workbook (1).
Następnie odczytujemy wartości akcji z arkusza stocks (2) i zawartość portfela z arkusza wallet (3).
Na zakończenie tworzymy nowy plik Excel z wygenerowanym przez nas raportem (4).
Odczytywanie danych
Kiedy mamy już dostęp do abstrakcji zeszytu w postaci interfejsu Workbook, możemy zacząć przetwarzać poszczególne arkusze.
private List<StockPrice> stockPrices(Sheet stocks) {
List<StockPrice> stockPrices = ofAll(stocks) // (1)
.tail() // (2)
.map(row -> new StockPrice(
text(row, 0),
decimal(row, 1)
) // (3)
).toJavaList();
return stockPrices;
}
Arkusz Sheet implementuje interfejs Iterable<Row>. Oznacza to, że możemy w łatwy sposób przetwarzać kolejne wiesze przy pomocy streamów (1). Pamiętamy przy tym, że pierwszy wiersz jest nagłówkiem. Więc go pomijamy (2).
To ładne API przetwarzające stream w (1) i (2) (a w każdym razie ładniejsze niż standardowe w JDK) to zasługa biblioteki Vavr. Warto ją stosować. W przypadku zabawy z Apache POI jest to bardzo pomocne, bo przetwarzamy tutaj dużo elementów z interfejsem Iterable.
Dane zbieramy do rekordu StockPrice (3). Dokładnie rekordu. Korzystamy w tym przypadku z Java 16, co pozwala nam na bardzo krótki zapis tej struktury:
public record StockPrice(String company, BigDecimal currentValue) {
}
Podobne podejście stosujemy przy odczycie zawartości portfela:
private List<SharesNumber> sharesNumber(Sheet sharesInWallet) {
List<SharesNumber> shares = ofAll(sharesInWallet)
.tail()
.map(row -> new SharesNumber(
text(row, 0),
integer(row, 1)
)
).toJavaList();
return shares;
}
A poniżej rekord z wystąpieniem akcji w naszym portfelu z ich liczbą:
public record SharesNumber(String code, int quantity) {
}
Typy danych
Aby dotrzeć do interesujących nas danych w wybranym wierszu, musimy skorzystać z pojedynczej komórki Cell (1, 3, 5).
Jednym z największych problemów w przetwarzaniu plików Excel, niezależnie od języka, jest temat typów danych. Ten problem jest zwłaszcza widoczny, gdy nasz Excel wejściowy jest zarządzany przez człowieka. W przypadku plików zarządzanych przez inne aplikacje zwykle mamy jasno ustalone, że np. dane są w wybranym formacie, określone kolumny zawierają ustalony typ, etc.
Rzecz ma się zupełnie inaczej, gdy Excelem zarządzają ludzie. Wówczas zwykle okazuje się, że tam, gdzie spodziewamy się tekstu czasem pojawi się data. Tam, gdzie miała być data, jest faktycznie tekst, choć z wartością tekstową daty, etc.
Mam nadzieję, że rozumiesz, o co mi chodzi 😊
Znając te problemy mogłem przygotować plik wejściowy tak, żeby nie było większych kłopotów z ich przetwarzaniem. Dodałem metody pomocnicze, które nie sprawdzają specjalnie typów danych. Mówimy tylko, że bierzemy tekst, wartość zmiennoprzecinkową lub zmienną całkowitą z wybranego wiersza i wybranej w tym wierszy komórki (2, 4, 6).
private String text(Row row, int cellIndex) {
Cell cell = row.getCell(cellIndex); // (1)
return cell.getStringCellValue(); // (2)
}
private BigDecimal decimal(Row row, int cellIndex) {
Cell cell = row.getCell(cellIndex); // (3)
double value = cell.getNumericCellValue(); // (4)
return BigDecimal.valueOf(value);
}
private int integer(Row row, int cellIndex) { // (5)
Cell cell = row.getCell(cellIndex);
double value = cell.getNumericCellValue();
return (int) value; // (6)
}
Gdybyśmy potrzebowali bardziej uniwersalnego pobierania wartości, musielibyśmy sprawdzać typ komórki i na tej podstawie pobierać dane.
Budowanie raportu wyjściowego z Apache POI
Ok, na tym etapie mamy już wczytane wszystkie dane. Dlatego czas zająć się za budowanie raportu.
private Workbook report(List<StockPrice> stockPrices, List<SharesNumber> sharesNumber) {
Workbook report = new XSSFWorkbook(); // (1)
CellStyle dollarStyle = report.createCellStyle();
dollarStyle.setDataFormat((short) 7); // (2)
Sheet sheet = report.createSheet("report"); // (3)
ofAll(sharesNumber)
.zipWithIndex()
.forEach((tuple) -> { // (4)
SharesNumber share = tuple._1();
int index = tuple._2();
Row row = sheet.createRow(index); // (5)
Cell shareCell = row.createCell(0, CellType.STRING); // (6)
shareCell.setCellValue(share.code()); // (7)
Cell valueCell = row.createCell(1, CellType.NUMERIC); // (8)
valueCell.setCellStyle(dollarStyle); // (9)
valueCell.setCellValue(shareValue(stockPrices, share.code(), share.quantity())); // (10)
});
Row summary = sheet.createRow(sharesNumber.size());
Cell summaryLeft = summary.createCell(0, CellType.STRING);
summaryLeft.setCellValue("Summary");
Cell summaryRight = summary.createCell(1, CellType.FORMULA);
summaryRight.setCellStyle(dollarStyle);
summaryRight.setCellFormula("sum(B1:B" + sharesNumber.size() + ")"); // (11)
return report;
}
Zaczynamy od utworzenia nowego zeszytu Worksheet (1). Następnie przygotujemy styl formatowania danych wyjściowych. Chcemy, żeby był to również dolar amerykański. Musimy trochę poszukać w dokumentacji biblioteki, jak ustawić to konkretne formatowanie (2). Dalej w zeszycie tworzymy arkusz report (3).
Następnie dla każdej naszej akcji SharesNumber chcemy utworzyć wiersz w arkuszu. Wiersze tworzymy podając indeks, dlatego w ramach przetwarzania korzystamy z metody zipWithIndex() (4), aby mieć informację o indeksie i pojedynczej akcji SharesNumber. zipWithIndex() zbiera obie informacje w postaci krotki (ang. tuple). Dzięki temu wiemy, który wiersz tworzymy w danej chwili (5).
Dalej zajmujemy się utworzeniem konkretnej komórki w wierszu (6). Przypisujemy jej wartość tekstową – kod naszej akcji (7). Jest to kolumna A w naszym arkuszu.
Podobnie czynimy dla wartości pieniężnej naszych akcji o danym kodzie (8, 9, 10). Pamiętamy tu ustawieniu formatowania dolarowego (9), które było przygotowane wcześniej. Dane znajdują się w kolumnie B arkusza.
Koniec raportu zawiera wiersz z podsumowaniem. W tym przypadku ciekawe będzie wykorzystanie formuły (11). Sama formuła jest prosta. Jest to suma wartości akcji z naszego portfela – czyli łączna wartość naszego portfela w danej chwili.
Tworzenie plików Excel
Na podstawie naszego zeszytu Workbook z raportem, możemy utworzyć finalny plik na dysku. Tu również korzystamy z wyrażenia try-with-resource i metody write(OutputStream stream) dostępnej w ramach interfejsu Workbook. Kod nie jest skomplikowany:
private void saveWorkbook(Workbook workbook, String outputName) {
try (FileOutputStream outputStream = new FileOutputStream(outputName)) {
workbook.write(outputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
Efektem będzie następujący plik:
Podsumowanie
Przeszliśmy przez odczytywanie i zapisywanie plików Excel z wykorzystaniem Apache POI. Poniżej kilka rad:
- Z pozoru prosta czynność odczytywania i zapisywania plików Excel jest wbrew pozorom dosyć skomplikowanym procesem. Apache POI nakładając pewne abstrakcje ułatwia nam te działania. Z tych abstrakcji należy korzystać.
- Odczytywanie wartości bywa problematyczne w plikach obsługiwanych przez zwykłych ludzi. Podatność Excela na wprowadzenie zmian, które wizualnie są ok, ale zmieniają formatowanie, typ, etc. jest duża. Nasz kod musi sobie z tym poradzić.
- Należy pamiętać o typach danych. Jak również o tym, że wartości liczbowe są reprezentowane przez… typ double. Wydaje się to dziwne. Jednak nie jest to wina Apache POI. Taka jest reprezentacja takich danych w plikach XLSX.
- Jeśli interesuje Cię skąd w Apache POI pojawiają się prefiksy klas typu XSSF, to rzuć okiem na artykuł Dobra nazwa, czy zła – na przykładzie Apache POI.
A do czego Ty używasz Apache POI? Czy miałeś do czynienia z innymi problemami z Apache POI?
W ramach cyklu o bibliotekach Javy możesz przeczytać i skomentować następujące artykuły: