Comment charger un grand fichier xlsx avec Apache POI?

j'étais dans une situation similaire avec un environnement webserver. La taille typique des téléchargements ont été ~150k lignes et il n'aurait pas été bon de consommer une tonne de mémoire à partir d'une seule demande. L'API de Streaming Apache POI fonctionne bien pour cela, mais elle nécessite une refonte totale de votre logique de lecture. J'ai déjà eu un tas de logique de lecture en utilisant l'API standard que je ne voulais pas avoir à refaire, donc j'ai écrit ceci à la place: https://github.com/monitorjbl/excel-streaming-reader

ce n'est pas tout à fait un remplacement direct pour la classe standard XSSFWorkbook , mais si vous êtes juste itérer à travers les lignes il se comporte de la même manière:

import com.monitorjbl.xlsx.StreamingReader;

InputStream is = new FileInputStream(new File("/path/to/workbook.xlsx"));
StreamingReader reader = StreamingReader.builder()
        .rowCacheSize(100)    // number of rows to keep in memory (defaults to 10)
        .bufferSize(4096)     // buffer size to use when reading InputStream to file (defaults to 1024)
        .sheetIndex(0)        // index of sheet to use (defaults to 0)
        .read(is);            // InputStream or File for XLSX file (required)

for (Row r : reader) {
  for (Cell c : r) {
    System.out.println(c.getStringCellValue());
  }
}     

il y a quelques réserves à l'utiliser; en raison de la façon dont les feuilles XLSX sont structurées, toutes les données ne sont pas disponibles dans la fenêtre courante du flux. Cependant, si vous êtes juste en train d'essayer de lire des données simples à partir des cellules, il fonctionne assez bien pour cela.

une amélioration de l'utilisation de la mémoire peut être faite en utilisant un fichier au lieu d'un flux. (Il est préférable d'utiliser une API de streaming, mais les API de Streaming ont des limites, voir http://poi.apache.org/spreadsheet/index.html )

donc au lieu de

Workbook workbook = WorkbookFactory.create(inputStream);

faire

Workbook workbook = WorkbookFactory.create(new File("yourfile.xlsx"));

c'est selon: http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream

Files vs InputStreams

" lors de l'ouverture d'un classeur, A.xls HSSFWorkbook, ou un .xlsx XSSFWorkbook, le classeur peut être chargé à partir d'un fichier ou D'une entrée. L'utilisation d'un objet File permet de réduire la consommation de mémoire, tandis qu'une entrée nécessite plus de mémoire puisqu'elle doit amortir l'ensemble du fichier."

le support Excel dans Apache POI, HSSF et XSSF, prend en charge 3 modes différents.

On est complet, dans les DOM-Comme en mémoire "UserModel", qui prend en charge à la fois la lecture et de l'écriture. En utilisant les interfaces communes SS (tableur), vous pouvez coder pour les deux HSSF (.xls) et XSSF (.xlsx) essentiellement transparente. Cependant, il a besoin de beaucoup de mémoire.

POI prend également en charge un mode de lecture en continu pour traiter les fichiers, le modèle D'événement. Ce qui est beaucoup plus faible niveau que le modèle UserModel, et vous obtient très près du format de fichier. Pour HSSF (.xls) vous obtenez un flux d'enregistrements, et optionnellement de l'aide pour les manipuler (cellules manquantes, suivi de format, etc.). Pour XSSF (.xlsx) vous obtenez des flux D'événements SAX À partir des différentes parties du fichier, avec de l'aide pour obtenir la bonne partie du fichier et aussi le traitement facile des parties communes mais petites du fichier.

pour XSSF (.xlsx) seulement, POI prend également en charge un streaming en écriture uniquement, adapté aux mais peu de mémoire écrit. Il supporte en grande partie de nouveaux fichiers (certains types d'ajout sont possibles). Il n'y a pas d'équivalent HSSF, et en raison des décalages de byte et d'index dans de nombreux enregistrements, ce serait assez difficile à faire...

pour votre cas spécifique, comme décrit dans vos commentaires explicatifs, je pense que vous voudrez utiliser le code Xssf EventModel. Voir la PVE de la documentation pour commencer, puis essayez de regarder ces trois classes POI et Tika qui l'utilisent pour plus de détails.

comprend maintenant une IPA pour ces cas. SXSSF http://poi.apache.org/spreadsheet/index.html Il ne charge pas tout sur la mémoire de sorte qu'il pourrait vous permettre de gérer un tel fichier.

Note: j'ai lu que SXSSF fonctionne comme une API d'écriture. Le chargement doit être fait en utilisant XSSF sans entrer le fichier (pour éviter une pleine charge en mémoire)

vérifiez ce post. Je montre comment utiliser Sax parser pour traiter un fichier XLSX.

https://stackoverflow.com/a/44969009/4587961

en bref, j'ai étendu org.xml.sax.helpers.DefaultHandler qui traite la structure XML pour les fichiers XLSX. t est l'événement par l'analyseur SAX.

class SheetHandler extends DefaultHandler {

    private static final String ROW_EVENT = "row";
    private static final String CELL_EVENT = "c";

    private SharedStringsTable sst;
    private String lastContents;
    private boolean nextIsString;

    private List<String> cellCache = new LinkedList<>();
    private List<String[]> rowCache = new LinkedList<>();

    private SheetHandler(SharedStringsTable sst) {
        this.sst = sst;
    }

    public void startElement(String uri, String localName, String name,
                             Attributes attributes) throws SAXException {
        // c => cell
        if (CELL_EVENT.equals(name)) {
            String cellType = attributes.getValue("t");
            if(cellType != null && cellType.equals("s")) {
                nextIsString = true;
            } else {
                nextIsString = false;
            }
        } else if (ROW_EVENT.equals(name)) {
            if (!cellCache.isEmpty()) {
                rowCache.add(cellCache.toArray(new String[cellCache.size()]));
            }
            cellCache.clear();
        }

        // Clear contents cache
        lastContents = "";
    }

    public void endElement(String uri, String localName, String name)
            throws SAXException {
        // Process the last contents as required.
        // Do now, as characters() may be called more than once
        if(nextIsString) {
            int idx = Integer.parseInt(lastContents);
            lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
            nextIsString = false;
        }

        // v => contents of a cell
        // Output after we've seen the string contents
        if(name.equals("v")) {
            cellCache.add(lastContents);
        }
    }

    public void characters(char[] ch, int start, int length)
            throws SAXException {
        lastContents += new String(ch, start, length);
    }

    public List<String[]> getRowCache() {
        return rowCache;
    }
}

et ensuite je passe le fichier XLSX de présentation XML

private List<String []> processFirstSheet(String filename) throws Exception {
    OPCPackage pkg = OPCPackage.open(filename, PackageAccess.READ);
    XSSFReader r = new XSSFReader(pkg);
    SharedStringsTable sst = r.getSharedStringsTable();

    SheetHandler handler = new SheetHandler(sst);
    XMLReader parser = fetchSheetParser(handler);
    Iterator<InputStream> sheetIterator = r.getSheetsData();

    if (!sheetIterator.hasNext()) {
        return Collections.emptyList();
    }

    InputStream sheetInputStream = sheetIterator.next();
    BufferedInputStream bisSheet = new BufferedInputStream(sheetInputStream);
    InputSource sheetSource = new InputSource(bisSheet);
    parser.parse(sheetSource);
    List<String []> res = handler.getRowCache();
    bisSheet.close();
    return res;
}

public XMLReader fetchSheetParser(ContentHandler handler) throws SAXException {
    XMLReader parser = new SAXParser();
    parser.setContentHandler(handler);
    return parser;
}

vous pouvez utiliser SXXSF au lieu d'utiliser HSSF. Je pourrais générer excel avec 200000 lignes.

basé sur la réponse de monitorjbl et la suite de test explorée à partir de poi, a travaillé pour moi sur le fichier XLSX multi-feuilles avec 200K enregistrements (taille > 50 MB):

import com.monitorjbl.xlsx.StreamingReader;
. . .
try (
        InputStream is = new FileInputStream(new File("sample.xlsx"));
        Workbook workbook = StreamingReader.builder().open(is);
) {
    DataFormatter dataFormatter = new DataFormatter();
    for (Sheet sheet : workbook) {
        System.out.println("Processing sheet: " + sheet.getSheetName());
        for (Row row : sheet) {
            for (Cell cell : row) {
                String value = dataFormatter.formatCellValue(cell);
            }
        }
    }
}