Работа с Microsoft Excel в Java

Програмиране

1. Въведение

В този урок ще демонстрираме използването на API на Apache POI и JExcel за работа с електронни таблици на Excel .

И двете библиотеки могат да се използват за динамично четене, писане и модифициране на съдържанието на електронна таблица на Excel и осигуряват ефективен начин за интегриране на Microsoft Excel в Java приложение.

2. Зависимости на Maven

За начало ще трябва да добавим следните зависимости към нашия файл pom.xml :

 org.apache.poi poi 3.15   org.apache.poi poi-ooxml 3.15

Най-новите версии на poi-ooxml и jxls-jexcel могат да бъдат изтеглени от Maven Central.

3. Apache POI

В библиотеката Apache POI поддържа както .xls и .xlsx файлове и е по-сложен, отколкото библиотека други Java библиотеки за работа с Excel файлове.

Той осигурява интерфейс на работната книга за моделиране на файл на Excel , както и интерфейси Sheet , Row и Cell, които моделират елементите на файл на Excel, както и реализации на всеки интерфейс за двата файлови формата.

Когато се работи с по-новата .xlsx файлов формат, трябва да използвате най- XSSFWorkbook, XSSFSheet, XSSFRow и XSSFCell класове .

За да работите с по-старата .xls формат, използвайте HSSFWorkbook, HSSFSheet, HSSFRow, и HSSFCell класове .

3.1. Четене от Excel

Нека създадем метод, който отваря .xlsx файл, след което чете съдържанието от първия лист на файла.

Методът за четене на съдържанието на клетката варира в зависимост от вида на данните в клетката. Типът на съдържанието на клетката може да бъде определен с помощта на метода getCellTypeEnum () на интерфейса на клетката .

Първо, нека отворим файла от дадено място:

FileInputStream file = new FileInputStream(new File(fileLocation)); Workbook workbook = new XSSFWorkbook(file);

След това нека извлечем първия лист от файла и да прегледаме всеки ред:

Sheet sheet = workbook.getSheetAt(0); Map
    
      data = new HashMap(); int i = 0; for (Row row : sheet) { data.put(i, new ArrayList()); for (Cell cell : row) { switch (cell.getCellTypeEnum()) { case STRING: ... break; case NUMERIC: ... break; case BOOLEAN: ... break; case FORMULA: ... break; default: data.get(new Integer(i)).add(" "); } } i++; }

Apache POI има различни методи за четене на всеки тип данни. Нека разширим съдържанието на всеки случай на превключване по-горе.

Когато стойността на изброяването на типа клетка е STRING , съдържанието ще бъде прочетено с помощта на метода getRichStringCellValue () на интерфейса на клетката :

data.get(new Integer(i)).add(cell.getRichStringCellValue().getString());

Клетките с тип съдържание NUMERIC могат да съдържат дата или число и се четат по следния начин:

if (DateUtil.isCellDateFormatted(cell)) { data.get(i).add(cell.getDateCellValue() + ""); } else { data.get(i).add(cell.getNumericCellValue() + ""); }

За стойности BOOLEAN имаме метода getBooleanCellValue () :

data.get(i).add(cell.getBooleanCellValue() + "");

И когато типът клетка е FORMULA , можем да използваме метода getCellFormula () :

data.get(i).add(cell.getCellFormula() + "");

3.2. Писане в Excel

Apache POI използва същите интерфейси, представени в предишния раздел за писане във файл на Excel и има по-добра поддръжка за стилизиране от JExcel.

Нека създадем метод, който записва списък с лица на лист със заглавие „Лица“ . Първо ще създадем и стилизираме заглавен ред, който съдържа клетки „Име“ и „Възраст“ :

Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("Persons"); sheet.setColumnWidth(0, 6000); sheet.setColumnWidth(1, 4000); Row header = sheet.createRow(0); CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont font = ((XSSFWorkbook) workbook).createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 16); font.setBold(true); headerStyle.setFont(font); Cell headerCell = header.createCell(0); headerCell.setCellValue("Name"); headerCell.setCellStyle(headerStyle); headerCell = header.createCell(1); headerCell.setCellValue("Age"); headerCell.setCellStyle(headerStyle);

След това нека напишем съдържанието на таблицата с различен стил:

CellStyle style = workbook.createCellStyle(); style.setWrapText(true); Row row = sheet.createRow(2); Cell cell = row.createCell(0); cell.setCellValue("John Smith"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(20); cell.setCellStyle(style);

И накрая, нека напишем съдържанието във файл 'temp.xlsx' в текущата директория и затворим работната книга:

File currDir = new File("."); String path = currDir.getAbsolutePath(); String fileLocation = path.substring(0, path.length() - 1) + "temp.xlsx"; FileOutputStream outputStream = new FileOutputStream(fileLocation); workbook.write(outputStream); workbook.close();

Нека тестваме горните методи в тест JUnit , който записва съдържание във файла temp.xlsx, след което чете същия файл, за да провери дали съдържа текста, който сме написали:

public class ExcelTest { private ExcelPOIHelper excelPOIHelper; private static String FILE_NAME = "temp.xlsx"; private String fileLocation; @Before public void generateExcelFile() throws IOException { File currDir = new File("."); String path = currDir.getAbsolutePath(); fileLocation = path.substring(0, path.length() - 1) + FILE_NAME; excelPOIHelper = new ExcelPOIHelper(); excelPOIHelper.writeExcel(); } @Test public void whenParsingPOIExcelFile_thenCorrect() throws IOException { Map
    
      data = excelPOIHelper.readExcel(fileLocation); assertEquals("Name", data.get(0).get(0)); assertEquals("Age", data.get(0).get(1)); assertEquals("John Smith", data.get(1).get(0)); assertEquals("20", data.get(1).get(1)); } }

4. JExcel

Библиотеката JExcel е лека библиотека, имаща предимството, че е по-лесна за използване от Apache POI, но с недостатъка е, че предоставя само поддръжка за обработка на Excel файлове във формат .xls (1997-2003).

В момента .xlsx файловете не се поддържат.

4.1. Четене от Excel

За да работи с файлове на Excel, тази библиотека предоставя серия от класове, които представляват различните части на файл на Excel. Класът Workbook представя цялата колекция от листове. Класът Sheet представлява един лист, а клас Cell представлява една клетка на електронна таблица.

Let's write a method that creates a workbook from a specified Excel file, gets the first sheet of the file, then traverses its content and adds each row in a HashMap:

public class JExcelHelper { public Map
    
      readJExcel(String fileLocation) throws IOException, BiffException { Map
     
       data = new HashMap(); Workbook workbook = Workbook.getWorkbook(new File(fileLocation)); Sheet sheet = workbook.getSheet(0); int rows = sheet.getRows(); int columns = sheet.getColumns(); for (int i = 0; i < rows; i++) { data.put(i, new ArrayList()); for (int j = 0; j < columns; j++) { data.get(i) .add(sheet.getCell(j, i) .getContents()); } } return data; } }

4.2. Writing to Excel

For writing to an Excel file, the JExcel library offers classes similar to the ones used above, that model a spreadsheet file: WritableWorkbook, WritableSheet, and WritableCell.

The WritableCell class has subclasses corresponding to the different types of content that can be written: Label, DateTime, Number, Boolean, Blank, and Formula.

This library also provides support for basic formattings, such as controlling font, color and cell width.

Let's write a method that creates a workbook called ‘temp.xls' in the current directory, then writes the same content we wrote in the Apache POI section.

First, let's create the workbook:

File currDir = new File("."); String path = currDir.getAbsolutePath(); String fileLocation = path.substring(0, path.length() - 1) + "temp.xls"; WritableWorkbook workbook = Workbook.createWorkbook(new File(fileLocation));

Next, let's create the first sheet and write the header of the excel file, containing “Name” and “Age” cells:

WritableSheet sheet = workbook.createSheet("Sheet 1", 0); WritableCellFormat headerFormat = new WritableCellFormat(); WritableFont font = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD); headerFormat.setFont(font); headerFormat.setBackground(Colour.LIGHT_BLUE); headerFormat.setWrap(true); Label headerLabel = new Label(0, 0, "Name", headerFormat); sheet.setColumnView(0, 60); sheet.addCell(headerLabel); headerLabel = new Label(1, 0, "Age", headerFormat); sheet.setColumnView(0, 40); sheet.addCell(headerLabel);

With a new style, let's write the content of the table we've created:

WritableCellFormat cellFormat = new WritableCellFormat(); cellFormat.setWrap(true); Label cellLabel = new Label(0, 2, "John Smith", cellFormat); sheet.addCell(cellLabel); Number cellNumber = new Number(1, 2, 20, cellFormat); sheet.addCell(cellNumber);

It's very important to remember to write to the file and close it at the end so it can be used by other processes, using the write() and close() methods of Workbook class:

workbook.write(); workbook.close();

5. Заключение

Този урок илюстрира как да използвате API на Apache POI и API на JExcel за четене и запис на файл на Excel от програма Java.

Пълният изходен код за тази статия може да бъде намерен в проекта GitHub.