CODEWITHSUNDEEP
javaapache-poi
Cyril
Cyril

Reputation: 31

Apache POI 4.0.0 compilation error on CellType

Compilation fails with this error :

cannot find symbol
case FORMULA:
symbol: variable FORMULA

The same error occurs on next cases.

I'm using Apache POI 4.0.0 with Java 8.

My code is :

try (Workbook wb = WorkbookFactory.create(inputStream)) {
    Sheet sheet = wb.getSheetAt(0);
        for (Row row : sheet) {
            for (Cell cell : row) {
                switch (cell.getCellType()) {
                    case FORMULA:
                        [code]
                        break;
                }
            }
        }
}

Note 1 : deployment to local GlassFish 4.1.2 from NetBeans 8.2 works, but build fails.

Note 2 : If I replace "getCellType" with "getCellTypeEnum" (deprecated), build completes without error.

Versions:

Full code

POIUtils.java

package fr.mycompany.config.files;

import fr.mycompany.config.exception.MyAppException;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import javax.faces.application.FacesMessage;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.CellType;

public class POIUtils {

    public static List<List<String>> parseExcelFile(InputStream is) throws MyAppException {

        List<List<String>> lines = new ArrayList<>();

        try (Workbook wb = WorkbookFactory.create(is)) {
            Sheet sheet = wb.getSheetAt(0);
            DataFormatter df = new DataFormatter(true);
            int numRow = 0;
            for (Row row : sheet) {
                int numCol = 0;
                List<String> currentLine = new ArrayList<>();
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                        case FORMULA:
                            currentLine.add(numCol, "");
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                LocalDate ld = cell.getDateCellValue().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                                String frLocalDate = ld.format(DateTimeFormatter.ofPattern("dd/MM/yyyy", Locale.FRANCE));
                                currentLine.add(numCol, frLocalDate);
                            } else {
                                String cellValue = df.formatCellValue(cell);
                                currentLine.add(numCol, cellValue);
                            }
                            break;
                        default:
                            String cellValue = df.formatCellValue(cell);
                            currentLine.add(numCol, cellValue);
                            break;
                    }
                    numCol++;
                }
                while (row.getRowNum() > numRow) {
                    lines.add(new ArrayList<>());
                    numRow++;
                }
                lines.add(row.getRowNum(), currentLine);
                numRow++;
            }
            if (lines.isEmpty()) {
                throw new MyAppException(FacesMessage.SEVERITY_WARN, "Le fichier est vide.");
            }
        } catch (IOException ex) {
            throw new MyAppException(FacesMessage.SEVERITY_FATAL, "Une erreur est survenue lors du traitement du fichier.", ex);
        } catch (EncryptedDocumentException ex) {
            throw new MyAppException(FacesMessage.SEVERITY_FATAL, "Le fichier est protégé par un mot de passe et n'a pas pu être lu.", ex);
        }

        return lines;

    }    

}

FileImportBean.java

package fr.mycompany.coordination;

import fr.mycompany.config.exception.MyAppException;
import fr.mycompany.config.files.POIUtils;
import java.io.Serializable;
import java.io.IOException;
import javax.faces.application.FacesMessage;
import javax.inject.Named;
import javax.faces.view.ViewScoped;
import java.util.List;
import java.util.ArrayList;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.primefaces.event.FileUploadEvent;
import org.primefaces.event.UploadedFile;

@Named
@ViewScoped
public class FileImportBean implements Serializable {

    private transient UploadedFile file;
    private List<List<String>> parsedContent = new ArrayList<>();

    public void handleFileUpload (FileUploadEvent event) {
        try {
            file = event.getFile();
            if (null != file) {
                switch (FileMagic.valueOf(FileMagic.prepareToCheckMagic(file.getInputstream()))) {
                    case OOXML:
                        parsedContent = POIUtils.parseExcelFile(file.getInputstream());
                        break;
                    default:
                        (new MyAppException(FacesMessage.SEVERITY_ERROR, "Type de fichier non géré.", ex)).doFacesMessage();
                        break;
                }
            } else {
            }
        } catch (IOException ex) {
            (new MyAppException(FacesMessage.SEVERITY_FATAL, "Une erreur est survenue lors du traitement du fichier.", ex)).doFacesMessage();
        } catch (MyAppException ex) {
            ex.doFacesMessage();
        }
    }

}

fileImport.xhtml

<h:form enctype="multipart/form-data">
    <p:messages showDetail="true" />
    <p:fileUpload
        mode="advanced"
        skinSimple="true"
        auto="true"
        multiple="false"
        required="true"
        update="@form"
        fileUploadListener="#{fileImportBean.handleFileUpload}"
</h:form>

Upvotes: 1

Views: 7641

Answers (2)

Abhineet Mishra
Abhineet Mishra

Reputation: 1

use this version of library it will work:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

Upvotes: 0

Cyril
Cyril

Reputation: 31

Thanks to idea suggested by @AxelRichter in comments section, I found that I included a JAR (made by a colleague of mine) using an older version of POI (3.15). I upgraded this one to 4.0.0 too and voila ! At least i learned something today... Thanks !

Upvotes: 2

Related Questions