Inhalt aus Excel-Datei Performant auslesen

[JeFi]

Hallo.

meine Aufgabe:

Ich sollte ein Programm schreiben, welches Daten aus ca 10-50 csv-Dateien(Größe je Datei ca 1,3 MB und 11000 Zeilen mit 16 Spalten benötigt werden 6 Spalten) ließt und miteinander vergleicht.

Diese Variante lief halbwegs Performant und Speicherschonend. Da nun aber die CSV Dateien durch Exceldateien ersetzt werden sollen muß ich also mein Programm auf Excel umstellen.

Dies bedeutet ich muß also den Inhalt aus ca. 10-50 Exceldateien(Größe je Datei ca 2 MB und 11000 Zeilen mit 16 Spalten benötigt werden 6 Spalten) auslesen und vergleichen!

mein Problem:

Die Wartezeiten und der Speicherverbrauch sind im Vergleich zu der ersten csv-Variante einfach viel zu hoch und deshalb möchte ich euch fragen ob Ihr vielleicht einen guten Lösungsvorschlag habe.

getestete Varianten(bei 10 Dateien):

POI -> Speicherverbrauch ca. 65 MB und Dauer ca 10 Sek

JExcel-> Speicherverbrauch ca. 60 MB und Dauer ca 10 Sek

ODBC -> Speicherverbrauch ca. 40 MB und Dauer ca 20 Sek

csv -> Speicherverbrauch ca. 40 MB und Dauer ca 5 Sek

Vielen Dank im Voraus

MfG

JeFi

[BlackCoin]

Hallo JeFi,

Warum nicht einfach Excel anweisen die Daten nach csv zu exportieren ??

und dann ganz normal wieder einlesen

mfg

[JeFi]

Hi!

Diese wäre einfach zu aufwendig und noch langsamer für den Benutzer, da die Anzahl der Excel-Dateien im Extremfall über 300 liegen kann!

Außerdem wäre es ja auch eine Speicherplatzverschwendung von mehreren 100 MB.

[bigredeyes]

zum speicherverbrauch: da räumt java irgendwann alleine auf.

WIE liest du die dinger ein und was machst du mit ihnen?

legst du in schleifen immer artig neue strings an[das sollte man unterlassen!]?

kannst du etwas code posten?

bigredeyes

[JeFi]

Hallo, habe leider nur noch die Varianten mit ODBC und JExcel(und diese habe ich nochmal etwas aufs wesentliche reduziert!).

Was bei ODBC so wahnsinnig lange Dauert ist der Aufruf der getString Methode!

Das mit der Speicherbereinigung kenne ich! Aber ich wollte eigentlich für jede Datei einen extra Thread läufen lassen (bei der csv Version hatte mir dies ca. 30% gebracht).

Wenn ich aber JExcel mit mehrere Thread laufen lasse bekomme ich eine out of Memory Exception(Was ich mir damit erkläre das er bei getSheet die ganze Datei in den Speicherliest und wenn das 10 Threads "gleichzeitig" tun ist dieser natürlich schnell voll)! Oder habe ich vielleicht sogar ein Speicherleck im Programm?

JExcel:

try {

 Workbook workbook = Workbook.getWorkbook(tmpFile);

 Sheet sheet = workbook.getSheet(0);


 String serialNo;

 String tid;

 for (int y = 1; y < sheet.getRows(); y++) {

   serialNo = sheet.getCell(3, y).getContents() + sheet.getCell(4,y).getContents();

  tid = sheet.getCell(5, y).getContents();


  if (!tid.equals(serialNo)) {

    new Terminal(sheet.getCell(0, y).getContents(), sheet.getCell(1, y).getContents(), tid, new TerminalSerialNo(sheet.getCell(9, y).getContents(), serialNo));

  }

 }

}

workbook.close();

ODBC

try {

  Connection conn = DriverManager.getConnection("jdbc:odbc:EC-ToolDBXls" + counter, "", "");


  String sql = "SELECT ges, filiale, term_id, last_used, sm_id, pp_id FROM [Tabelle1$] WHERE term_id <> ((sm_id*10000)+pp_id)";


  PreparedStatement pStmt = conn.prepareStatement(sql);

  ResultSet rSet = pStmt.executeQuery();

  final String[] lineArray = new String[5];

  while (rSet.next()) {


	lineArray[0] = rSet.getString(1);

	lineArray[1] = rSet.getString(2);

	lineArray[2] = rSet.getString(3);

	lineArray[3] = rSet.getString(4);

	lineArray[4] = rSet.getString(5) + rSet.getString(6);


	content.add(new Terminal(lineArray[0], lineArray[1], lineArray[2], new TerminalSerialNo(lineArray[3], lineArray[4])));


  }

  pStmt.close();

  conn.close();


} catch (final Exception e) {

  JOptionPane.showMessageDialog(null, e.getStackTrace(), "FEHLER", JOptionPane.ERROR_MESSAGE);

  e.printStackTrace();

}

[Jaraz]

Und was passiert im Terminal Konstruktor?

[JeFi]

Guten Morgen!!!

Also im Terminal Konstruktor passiert nicht viel!(nur inizialisieren der Eigenschaften)

Außerdem wurde der Konstruktor im vergleich zur csv Version nicht geändert und dürfte deshalb ja auch nicht zu den Performance einbußen führen.

PS. Ups ich sehe gerade, dass ich in der JExcel die Terminal Objekte nicht zu content hinzufüge!(ist aber nur hier so! In der Variante die ich getestet habe läuft dies mit dem Hinzufügen ändert aber nichts am Problem!)

Das was ich nicht verstehe ist, warum bei ODBC der Aufruf von getString so lange dauert?

Und warum bei JExcel und POI so wahnsinnig viel Speicher verbraucht wird? Mache ich da so grobe Fehler oder ist das einfach so?

[Jaraz]

CSV kannste einfach sequenziell abarbeiten.

Die Exceldatei wird bei POI und JExcel wahrscheinlich erst einmal komplett eingelesen.

[Lieblingszitat Datenbanken]

ODBC zieht tote Hamster durch Strohhalme.

Massendaten so zu verarbeiten halte ich für sehr schlecht.

Kannste die Daten nicht anders bekommen?

Es gibt auch Tools die aus xls wieder csv machen. Vielleicht kommste so schneller ans Ziel.

Du kannst ja mal eine Beispiel xls Datei erstellen und wir machen einen Contest daraus wer die am schnellsten einliest.

Gruß Jaraz

[bigredeyes]

Wenn ich aber JExcel mit mehrere Thread laufen lasse bekomme ich eine out of Memory Exception(Was ich mir damit erkläre das er bei getSheet die ganze Datei in den Speicherliest und wenn das 10 Threads "gleichzeitig" tun ist dieser natürlich schnell voll)! Oder habe ich vielleicht sogar ein Speicherleck im Programm?

ist JExcel thread-save?

verwendest du bei deinen methoden "synchronized" bzw. lock(..){.... ?

bigredeyes

[JeFi]

@Jaraz ja ein Contest wäre echt nicht schlecht !!!

Denke aber, dass das konvertieren von xls zu csv länger dauern dürfte!(der Schreibvorgang dauert sicherlich schon fast so lange wie mein auslesen)

@bigredeyes warum sollte es denn Thread-safe sein? Arbeite doch auf unterschiedlichen Exceldateien mit unterschiedlichen JExcel-Workebookobjekten!

Das einzige was ich syncronisiere ist:

content.add(new Terminal(lineArray[0], lineArray[1], lineArray[2], new TerminalSerialNo(lineArray[3], lineArray[4])));

Aber was ich jetzt auf der Apacheseite gelesen habe ist, dass man via eventmodel schneller und Speicher schonender arbeiten kann!

Hier wäre dann mal die Eventmodel-Variante von mir!(wenn jemand gobe Fehler sieht bitte melden!Contest ist eröffnet :bimei )

POI

public static ArrayList<Terminal> checkTerminalChanges(final String sWHNo, final String sStoreNo, final GregorianCalendar gcFrom, final GregorianCalendar gcTo) {


final HashSet<Terminal> content = new HashSet<Terminal>(12000);

final ArrayList<Terminal> alResult = new ArrayList<Terminal>();

final ArrayList<File> files = new ArrayList<File>();

final String FILE_NAME_FROM = "SNr" + SDF_SNO.format(gcFrom.getTime()) + ".001.xls";

final String FILE_NAME_TO = "SNr" + SDF_SNO.format(gcTo.getTime()) + ".001.xls";


for (int i = gcFrom.get(GregorianCalendar.YEAR); i <= gcTo.get(GregorianCalendar.YEAR); i++) {


  final File dir = new File(hmFilePath.get(PathType.SERIALNR_PATH) + "\\" + i);


  final File[] f = dir.listFiles(new FilenameFilter() {

	public boolean accept(File dir, String name) {

	  return ((name.length() == 19) && (name.compareTo(FILE_NAME_FROM) >= 0) && (name.compareTo(FILE_NAME_TO) <= 0));

	}

  });


  if (f != null) {

	files.addAll(Arrays.asList(f));

  }

}


try {


  final String STD_WH_NO = "00";

  final String STD_STORE_NO = "0000";


  final boolean IS_STD_WH = sWHNo.equals(STD_WH_NO);

  final boolean IS_STD_STORE = sStoreNo.equals(STD_STORE_NO);

  final byte WH_NO = 0;

  final byte STORE_NO = 1;

  final byte SM_ID = 3;

  final byte PP_ID = 4;

  final byte TID = 5;


  final int fileCount = files.size();

  final Thread[] aThreads = new Thread[fileCount];

  for (int i = 0; i < fileCount; i++) {


	final File tmpFile = files.get(i);

	aThreads[i] = new Thread() {


	  public void run() {

		try {


		  [B]HSSFEventFactory eventFactory = new HSSFEventFactory();

		  HSSFRequest request = new HSSFRequest();

		  request.addListenerForAllRecords(new HSSFListener() {

			// request.addListener(new HSSFListener(){

			SSTRecord sstRecord;


			LabelSSTRecord textRecord;


			NumberRecord numberRecord;


			final String[] lineArray = new String[] { "", "", "", "" };


			public void processRecord(Record arg0) {


			  switch (arg0.getSid()) {

				case SSTRecord.sid:

				  sstRecord = (SSTRecord) arg0;


				  break;

				case NumberRecord.sid:


				  numberRecord = (NumberRecord) arg0;

				  switch (numberRecord.getColumn()) {

					case WH_NO:

					  lineArray[0] = Integer.toString((int) numberRecord.getValue());

					  break;

					case STORE_NO:

					  lineArray[1] = Integer.toString((int) numberRecord.getValue());

					  break;

					case SM_ID:

					  lineArray[3] = Integer.toString((int) numberRecord.getValue());

					  break;

					case PP_ID:

					  lineArray[3] += Integer.toString((int) numberRecord.getValue());

					  break;

					case TID:

					  lineArray[2] = Integer.toString((int) numberRecord.getValue());

					  break;

				  }


				  break;

				case LabelSSTRecord.sid:

				  textRecord = (LabelSSTRecord) arg0;


				  if (textRecord.getColumn() == 11 && (IS_STD_WH || sWHNo.equals(lineArray[0])) && (IS_STD_STORE || sStoreNo.equals(lineArray[1])) && !lineArray[2].equals(lineArray[3])) {

					synchronized (content) {

					  content.add(new Terminal(lineArray[0], lineArray[1], lineArray[2], new TerminalSerialNo(sstRecord.getString(textRecord.getSSTIndex()).toString(), lineArray[3])));

					}

				  }

				  break;

			  }

			}

		  });

		  eventFactory.processWorkbookEvents(request, new POIFSFileSystem(new FileInputStream(tmpFile)));[/B]

		} catch (final Exception e) {

		  JOptionPane.showMessageDialog(null, e.getStackTrace(), "FEHLER", JOptionPane.ERROR_MESSAGE);

		  e.printStackTrace();

		}

	  }

	};

	aThreads[i].start();

  }


  for (final Thread myThread : aThreads) {

	myThread.join();

  }

  for (final Terminal terminal : content) {

	if (terminal.getAlSerialNo().size() > 1) {

	  terminal.sort();

	  alResult.add(terminal);

	}

  }


} catch (final Exception e) {

  JOptionPane.showMessageDialog(null, e.getStackTrace(), "FEHLER", JOptionPane.ERROR_MESSAGE);

  e.printStackTrace();

}


return alResult;

}