Traitement de gros fichiers xlsx

je dois auto-ajuster toutes les lignes dans le grand (30K+ rows) fichier xlsx.

le code suivant Via apache poi fonctionne sur les petits fichiers, mais sort avec OutOfMemoryError sur les gros:

Workbook workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheetAt(0);

for (Row row : sheet) {
    row.setHeight((short) -1);
}

workbook.write(outputStream);

mise à Jour: Malheureusement, l'augmentation de la taille du segment n'est pas une option OutOfMemoryError s'affiche à -Xmx1024m et 30k lignes n'est pas une limite supérieure.

31
demandé sur Paolo Forgia 2011-02-04 15:05:51

10 réponses

essayez L'API event. Voir Event API (hssf only) et XSSF and SAX (Event API) dans la documentation de L'IPE pour plus de détails. Quelques citations de cette page:

HSSF:

L'API event est plus récente que l'API utilisateur. Il est destiné aux développeurs intermédiaires qui sont prêts à apprendre un peu des structures D'API de bas niveau. Son relativement simple, mais nécessite un compréhension de base des parties D'un fichier Excel (ou volonté d'apprendre). L'avantage fourni est que vous pouvez lire un XLS avec une empreinte mémoire relativement petite.

XSSF:

si l'empreinte mémoire est un problème, alors pour XSSF, vous pouvez obtenir les données XML sous-jacentes, et le traiter vous-même. Ceci est destiné aux développeurs intermédiaires qui sont prêts à apprendre un peu de structure de bas niveau de .fichiers xlsx, et qui sont heureux de traiter XML en java. Son relativement simple, mais nécessite une compréhension de base de la structure du fichier. L'avantage fourni est que vous pouvez lire un fichier XLSX avec une empreinte mémoire relativement petite.

pour la sortie, une approche possible est décrite dans le billet de blog Streaming fichiers xlsx . (Essentiellement, utilisez XSSF pour générer un fichier XML de conteneur, puis le contenu réel comme le texte en clair dans le partie xml appropriée de l'archive zip xlsx.)

32
répondu markusk 2015-02-27 13:48:13

Une amélioration spectaculaire de l'utilisation de la mémoire peut être fait 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."

10
répondu rjdkolb 2013-07-09 07:36:39

j'avais le même problème avec beaucoup moins de rang, mais de grandes cordes.

comme je n'ai pas à garder mes données chargées, j'ai découvert que je peux utiliser SXSSF au lieu de XSSF.

ils ont des interfaces similaires, ce qui aide si vous avez beaucoup de code déjà écrit. Mais avec SXSSF il est possible de définir le nombre de lignes que vous gardez chargées.

Voici le lien. http://poi.apache.org/spreadsheet/how-to.html#sxssf

3
répondu DomLavoie 2013-05-23 04:34:19

si vous voulez auto-ajuster ou définir des styles ou écrire toutes les lignes dans le grand (30K+ rows) fichier xlsx,utilisez SXSSFWorkbook.Voici un exemple de code qui vous aide...

SXSSFWorkbook wb = new SXSSFWorkbook();
            SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("writetoexcel");
            Font font = wb.createFont();
                font.setBoldweight((short) 700);
                // Create Styles for sheet.
                XSSFCellStyle Style = (XSSFCellStyle) wb.createCellStyle();
                Style.setFillForegroundColor(new XSSFColor(java.awt.Color.LIGHT_GRAY));
                Style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
                Style.setFont(font);
                //iterating r number of rows
            for (int r=0;r < 30000; r++ )
            {
                Row row = sheet.createRow(r);
                //iterating c number of columns
                for (int c=0;c < 75; c++ )
                {
                    Cell cell = row.createCell(c);
                    cell.setCellValue("Hello"); 
                    cell.setCellStyle(Style);
                }
    }
            FileOutputStream fileOut = new FileOutputStream("E:" + File.separator + "NewTest.xlsx");
2
répondu Santosh Gdr 2015-02-28 06:54:21

j'ai utilisé L'API Event pour un fichier HSSF (.xls), et j'ai découvert un terrible manque de documentation sur l'ordre des dossiers.

1
répondu Arturo Tena 2011-03-17 01:28:24

voici un exemple que j'ai trouvé qui traitera de très gros fichiers XLSX. Mon test, jusqu'à présent semble bon. Il est capable de gérer de très gros fichiers sans problèmes de mémoire.

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java

0
répondu user392243 2012-11-11 19:11:55

Si vous êtes écrit pour XLSX, j'ai trouvé une amélioration par écrit à différentes feuilles du même fichier Excel. Vous pouvez également trouver une amélioration en écrivant à différents fichiers Excel. Mais d'abord, essayez d'écrire des différentes feuilles.

0
répondu Alexander Mills 2013-08-04 18:00:37

le meilleur exemple pour ceci est décrit dans le fil de débordement de cheminée suivant: erreur lors de la lecture de grands fichiers Excel (xlsx) Via Apache POI

le code snippet dans la réponse principale dans ce sujet illustre les enveloppements de POI Apache autour de Sax XML parsing, et comment vous pouvez boucler trivialement sur toutes les feuilles et puis sur chaque cellule individuelle.

le code est périmé avec l'implémentation actuelle du POI Apache API, comme l'api endRow () fournit le numéro de ligne courant qui a fini d'être traité.

avec cet extrait de code, il devrait être trivial pour votre analyse d'une grande cellule de fichier XLSX par cellule. Par exemple: pour chaque feuille; pour chaque cellule de rangée; la rangée a terminé l'événement. Vous pouvez créer trivial app logic où à la de chaque ligne vous créez une carte de columneName à cellValue.

0
répondu 99Sono 2017-05-23 10:31:15

j'ai eu le même problème avec 800.000 cellules et 3m caractères où XSSF attribue 1 Go de tas!

j'ai utilisé Python avec openpyxl et numpy pour lire le fichier xlsx (du code Java) et le convertir d'abord en un texte normal. Puis j'ai chargé le fichier texte en java. Il peut semble avoir grand frais généraux, mais il est vraiment rapide.

le script python ressemble à

import openpyxl as px
import numpy as np

# xlsx file is given through command line foo.xlsx
fname = sys.argv[1]
W = px.load_workbook(fname, read_only = True)
p = W.get_sheet_by_name(name = 'Sheet1')

a=[]
# number of rows and columns
m = p.max_row
n = p.max_column

for row in p.iter_rows():
    for k in row:
        a.append(k.value)

# convert list a to matrix (for example maxRows*maxColumns)
aa= np.resize(a, [m, n])

# output file is also given in the command line foo.txt
oname = sys.argv[2]
print (oname)
file = open(oname,"w")
mm = m-1
for i in range(mm):
    for j in range(n):
        file.write( "%s " %aa[i,j]  )
    file.write ("\n")

# to prevent extra newline in the text file
for j in range(n):
    file.write("%s " %aa[m-1,j])

file.close()

puis dans mon code java, j'ai écrit

try {
  // `pwd`\python_script  foo.xlsx  foo.txt
  String pythonScript =  System.getProperty("user.dir") + "\exread.py ";
  String cmdline = "python " + pythonScript +
                    workingDirectoryPath + "\" + fullFileName + " " + 
                    workingDirectoryPath + "\" + shortFileName + ".txt";
  Process p = Runtime.getRuntime().exec(cmdline);
  int exitCode = p.waitFor();
  if (exitCode != 0) {
    throw new IOException("Python command exited with " + exitCode);
  }
} catch (IOException e) {
  System.out.println( e.getMessage() );
} catch (InterruptedException e) {
  ReadInfo.append(e.getMessage() );
}

après ça, vous aurez foo.txt qui est similaire à toto.xlsx, mais en format texte.

0
répondu mahmood 2017-05-06 05:10:32

j'ai utilisé SAX parser pour traiter la structure XML. Il fonctionne pour les fichiers XLSX.

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

0
répondu Yan Khonski 2017-07-07 10:57:07