Soru Apache POI ile büyük bir xlsx dosyası nasıl yüklenir?


Büyük bir .xlsx dosyası var (her biri 62 sütun ile 293413 satır içeren 141 MB). Bazı işlemleri gerçekleştirmem gerekiyor.

Bu dosyayı yüklemeyle ilgili sorunlar yaşıyorum (OutOfMemoryError), POI'nin XSSF (xlsx) çalışma kitaplarında büyük bir bellek izi olduğu için.

Bu so soru benzerdir ve sunulan çözüm VM'nin ayrılmış / maksimum belleğini arttırmaktır.

Bu tür bir dosya boyutu (9MB) için çalışıyor gibi görünüyor, ancak benim için, tüm mevcut sistem belleği tahsis olsa bile sadece çalışmaz. (Dosya 15 kat daha büyük göz önüne alındığında sürpriz değil)

Çalışma kitabını, tüm belleği tüketmeyecek bir şekilde yüklemek için herhangi bir yol olup olmadığını bilmek istiyorum, ve yine de, işlemi XSSF'nin altta yatan XML'sine dayanarak yapmadan (ister). (Bir başka deyişle, bir puritan POI çözümünü korumak)

Sert değilse, ("Yok") demeyi ve "XML" çözümünün yollarını göstermeyi kabul edersiniz.


28
2017-08-09 20:58


Menşei


SO sorusu, sormak istediklerinize daha çok "Java'da büyük xlsx dosyası işleniyor" diye düşünüyorum. Bu API'yi dene poi.apache.org/spreadsheet/how-to.html#xssf_sax_api Ben sadece verileri ayrıştırdığını düşündüğümden, bu şeyleri saklamaz. - Bob Kuhar
Onunla çalışmak için dosyayı okumak gerekiyor mu? Veya tam okuma / düzenleme / yazma döngüsü yapman gerekiyor mu? - Gagravarr
@Gagravarr - Teorik olarak tam bir döngüye ihtiyacım var, ama POI'nin kapsamı uzadıkça, sadece okuma yeterlidir, çünkü bu özel durum için son dosya .txt (sekmeyle ayrılmış), .csv veya benzeri olarak kaydedilebilir; Başka bir deyişle, veriler çıkarıldıktan sonra POI olmadan yönetebileceğim düz metin. - XenoRo


Cevaplar:


Bir web sunucusu ortamı ile benzer bir durumdaydım. Yüklemelerin tipik boyutu ~ 150k satırdı ve tek bir istekte bir ton bellek tüketmek iyi olmazdı. Apache POI Streaming API'si bunun için iyi çalışır, ancak okuma mantığınızın tamamen yeniden tasarlanmasını gerektirir. Yinelemek istemediğim standart API'yi kullanarak bir grup okuyucuyu zaten kullandım, bu yüzden şunu yazdım: https://github.com/monitorjbl/excel-streaming-reader

Standart için tamamen bir ikame değişimi değil XSSFWorkbook sınıf, ancak sadece satırlar arasında yineleme yapıyorsanız, benzer şekilde davranır:

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());
  }
}     

Bunu kullanmak için bazı uyarılar vardır; XLSX sayfalarının yapılandırılma biçimi nedeniyle, tüm veriler akımın mevcut penceresinde mevcut değildir. Ancak, sadece hücrelerden basit verileri okumak için çalışıyorsanız, bunun için oldukça iyi çalışıyor.


39
2018-02-08 17:58



Geri döndürülmeyen ve bana bir sorun neden olan boş hücre hariç doğru gidiyor, çünkü her satırda hangi sütunun gerçekten eksik olduğunu belirleyemiyorum (yalnızca bildiğim kadarıyla geri döndüğümü biliyorum) - darkman97i
Bu çözüm harika. Git sayfasında tanımlı yeni bir kullanım var: InputStream is = new FileInputStream(new File("/path/to/workbook.xlsx")); Workbook workbook = StreamingReader.builder().rowCacheSize(100).bufferSize(4096).sheetIndex(0).open(is);                      Yapmanız gereken tek şey satırların üzerinde yineleme yapıyorsa, bu durum çalışma sayfasını bellekte yüklüyekiyle aynı şekilde ele almanızı sağlar. - Anthony Ruffino
@ Darkman97i: Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK) şimdi destekleniyor ve sorunu çözüyor. Görmek İşte - Dennis Briner
Bu çözüm, dosya içeriğini okumak için iyi çalışır, ancak çalışma kitabından hücre stillerini almak istiyorsanız işe yaramaz. - Arthur bauer


Bellek kullanımında bir gelişme, bir Akış yerine bir Dosya kullanılarak yapılabilir. (Akış API'sini kullanmak daha iyidir, ancak Akış API'sinin sınırlamaları vardır, bkz. http://poi.apache.org/spreadsheet/index.html)

Yerine

Workbook workbook = WorkbookFactory.create(inputStream);

yap

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

Bu göre: http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream

Dosyalar vs InputStreams

"Bir çalışma kitabı, bir .xls HSSFWorkbook veya bir .xlsx XSSFWorkbook açıldığında, Çalışma Kitabı bir Dosya veya bir InputStream'den yüklenebilir. Bir File nesnesinin kullanılması düşük bellek tüketimine izin verirken, bir InputStream daha fazla bellek gerektiriyorsa tüm dosyayı arabelleğe almak.


11
2017-07-09 07:38



Hatırladığım gibi .create dosyası değil, bir çalışma kitabı yaratır. Eğer Yanlışsam beni düzelt. - rjdkolb
Evet haklısın. Üzgünüm. - Xdg
Bu bir aldatıcı API adı :) - rjdkolb


Apache POI, HSSF ve XSSF'deki Excel desteği, 3 farklı modu destekler.

Bunlardan biri, hem okuma hem de yazmayı destekleyen tam bir DOM-Like bellek "UserModel" dir. Ortak SS (SpreadSheet) arayüzlerini kullanarak, hem HSSF (.xls) hem de XSSF (.xlsx) için temel olarak saydam olarak kodlayabilirsiniz. Ancak, çok fazla belleğe ihtiyacı var.

POI ayrıca dosyaları, EventModel'i işlemek için salt okunur bir yolu destekler. Bu, UserModel'den çok daha düşük düzeydedir ve sizi dosya biçimine çok yaklaştırır. HSSF (.xls) için bir kayıt akışı ve isteğe bağlı olarak bunları işlemek için bazı yardım (eksik hücreler, biçim izleme vb.) Alırsınız. XSSF (.xlsx) için dosyanın farklı bölümlerinden SAX olaylarının akışlarını alırsınız, dosyanın doğru kısmını elde etmek ve aynı zamanda dosyanın genel ama küçük bitlerini kolayca işlemek için.

Yalnızca XSSF (.xlsx) için, POI ayrıca, düşük seviyeli fakat düşük bellekli yazma için uygun, sadece yazma akışlı bir yazmayı destekler. Büyük ölçüde sadece yeni dosyaları destekler (belirli türdeki eklentiler mümkündür). HSSF eşdeğeri yoktur ve birçok kayıttaki ileri-geri bayt ofsetleri ve dizin ofsetleri sayesinde ...

Özel durumunuz için, açıklayıcı yorumlarınızda açıklandığı gibi, XSSF EventModel kodunu kullanmak isteyeceğinizi düşünüyorum. Görmek POI belgeleri Başlamak için bunlar  üç  sınıflar daha fazla bilgi için bunu kullanan POI ve Tika'da.


7
2017-11-12 19:37





POI şimdi bu durumlar için bir API içerir. SXSSF http://poi.apache.org/spreadsheet/index.html Bu, bellekte her şeyi yüklemez, bu nedenle bu tür dosyaları işlemenize izin verebilir.

Not: SXSSF'in bir yazma API'si olarak çalıştığını okudum. Yükleme, dosyaya giriş yapmadan XSSF kullanılarak yapılmalıdır (belleğin tam yüklenmesini önlemek için)


5
2017-08-09 22:12



Varolan bir dosyayı yüklemek için herhangi bir kaynak verebilir misiniz? Bu bağlantıda gördüğüm tek şey, benim durumumda kullanılmayan amaçlar yaratmaktır. - XenoRo
SXSSF'in bir yazma API'si olarak çalıştığını okudum. Yükleme, dosyaya giriş yapmadan XSSF kullanılarak yapılmalıdır (belleğin tam yüklenmesini önlemek için) - Alfabravo
Dosyayı ImputStream’ing olmadan nasıl yükleyebilirim? Yük (okuma) problemdir, kaydetme (yazma) değil. - XenoRo
Bu konu okunurken çözülebilir. İnşallah stackoverflow.com/questions/11154678/... - Alfabravo
SXSSF okumak için değil, sadece dosyayı yazmak için - Panther


Bu yazıyı kontrol et. Bir XLSX dosyasını işlemek için SAX ayrıştırıcısının nasıl kullanılacağını gösteriyorum.

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

Kısacası uzattım org.xml.sax.helpers.DefaultHandler xih, XLSX filez için XML yapısını işler. t olay ayrıştırıcısı - 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;
    }
}

Ve sonra XLSX dosyasını bekleyen XML ayrıştırıyorum

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;
}

3
2017-07-07 10:54



fetchSheetParser nerede? - abr
@abr Düzenlendi, tekrar kontrol edin. - Yan Khonski
teşekkürler beklendiği gibi çalışıyor. Soru: Aşırı uzun bir xlsx dosyasını okumak için kodunuzu kullanıyorum ve 60 sütun gazı oluyor. Bununla birlikte, her bir satır 48 ve bazı 59 döndürülür (hücrelerdeki boş değerleri göz ardı eder ve bunları dizi listesine yerleştirmez). Startelement'te nasıl çıkılacağını anlamaya çalıştı ama şimdiye kadar başarı yok. Bir fikrin var mı? - abr
Şu anda, boş hücre yok sayar. Bu senin için bir ev ödevi - bu hatayı düzeltmek için. Zamanım olduğunda, düzelteceğim ve kodu güncelleyeceğim. - Yan Khonski
Çalışmayı başarabilirsem, yazacağım bir düzenleme isteği göndereceğim :) Bunu yapmadan önce beni yönetirseniz bana etiket gönderir - abr


HSSF kullanmak yerine SXXSF kullanabilirsiniz. 200000 sıra ile excel oluşturabilirim.


0
2018-01-19 19:57





Poj den araştırılan monitorjbl'ın cevap ve test paketine dayanarak, aşağıdaki, çok sayfalı xlsx dosyası üzerinde 200K kayıtlarla çalıştım (boyut> 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);
            }
        }
    }
}

0
2017-08-14 17:13