0

I have to export large amount of data (>100000 documents) from to . I use java apache poi and for this function, however the server crashes after 4-5000 douments.
can not be installed on the server.
format is not an option.
Creating more files with predefined limited number of records is not an option.
What can be the proper method/technology for exporting every field in all documents in a view?

var maxDocs:Integer=6001; 
function ExcExp(docIDArray:Array, sVieNam:String, proFie, filTip:String,logEve:String){
var sUzenet='xpExcel ExcExp ';
var bRetVal=false;
var docId:String;
var doc:NotesDocument=null;
var tmpDoc:NotesDocument=null;
var aIteNam:array=new Array();
var aIteLab:array=new Array();
var aIteHin:array=new Array();
var sIteNam:String;
var category:String;
var y=0;
aIteNam=@Explode(proFie.getString('fieNam'),'~');
aIteLab=@Explode(proFie.getString('fieLab'),'~');
aIteHin=@Explode(proFie.getString('fieHin'),'~');
var rowInd=new java.lang.Integer(0);
var rowInd1=new java.lang.Integer(3);
try{
var fileName='c:\\Temp\\'+renFile('_'+filTip+'_','xls');    
var fileOut = new java.io.FileOutputStream(fileName);
var wb = new org.apache.poi.hssf.usermodel.HSSFWorkbook();
var sheet=wb.createSheet('CRM'+filTip+'Export');
var createHelper = wb.getCreationHelper();
var drawing = sheet.createDrawingPatriarch();
var anchor = createHelper.createClientAnchor();
var row=sheet.createRow(rowInd);        
for (x=0;x<aIteNam.length;x++){
cellInd=new java.lang.Integer(x);
colInd1=new java.lang.Integer(x);
colInd2=new java.lang.Integer(x+5);
var cell=row.createCell(cellInd);
cell.setCellValue(aIteNam[cellInd]);
anchor.setCol1(colInd1);
anchor.setCol2(colInd2);
anchor.setRow1(rowInd);
anchor.setRow2(rowInd1);
var comment = drawing.createCellComment(anchor);
var str = createHelper.createRichTextString(aIteLab[cellInd]+": "+fieldHint.getString(aIteHin[cellInd]));
comment.setString(str);
comment.setAuthor(@Name('[Abbreviate]',@UserName()));
cell.setCellComment(comment);           
}
for (x=0;x<aIteNam.length;x++){
cellInd=new java.lang.Integer(x);
sheet.autoSizeColumn(cellInd);          
}
if (docIDArray.length>0){
for(y=0;y<docIDArray.length;y++){
docId=docIDArray[y];
doc=database.getDocumentByID(docId);
if (doc!=null){                         
bRetVal=false;
rowInd=new java.lang.Integer(y+1);
row=sheet.createRow(rowInd);    
for (x=0;x<aIteNam.length;x++){
cellInd=new java.lang.Integer(x);                       
cell=row.createCell(cellInd);
sIteNam=aIteNam[cellInd];
if (doc.hasItem(sIteNam)){
if (doc.getFirstItem(sIteNam).getType()!=1){
cell.setCellValue(doc.getItemValueString(sIteNam));
}else{
cell.setCellValue(doc.getFirstItem(sIteNam).getFormattedText(true, 0,0));
}
}else{
cell.setCellValue('');
}
}
bRetVal=true;       
if (bRetVal){       
}
}
for (x=0;x<aIteNam.length;x++){
cellInd=new java.lang.Integer(x);
sheet.autoSizeColumn(cellInd);          
}
wb.write(fileOut);            
}else{
if (viewScope.query && viewScope.query.length>0){
bRetVal=false;
var vView=database.getView(sVieNam);
if (vView.FTSearch(viewScope.query,maxDocs)>0){             
doc=vView.getFirstDocument();
y=1;
while (doc!=null && y<maxDocs){                 
tmpDoc=vView.getNextDocument(doc);
rowInd=new java.lang.Integer(y);
row=sheet.createRow(rowInd);
for (x=0;x<aIteNam.length;x++){                         
cellInd=new java.lang.Integer(x);                       
cell=row.createCell(cellInd);
sIteNam=aIteNam[cellInd];
if (doc.hasItem(sIteNam)){
cell.setCellValue(doc.getItemValueString(sIteNam));
}else{
cell.setCellValue('');
}
}
bRetVal=true;       
doc.recycle();
doc=tmpDoc;
y=y+1;
}
}               
for (x=0;x<aIteNam.length;x++){
cellInd=new java.lang.Integer(x);
sheet.autoSizeColumn(cellInd);          
}
wb.write(fileOut);  
}else{
bRetVal=false;
var vView=database.getView(sVieNam);
doc=vView.getFirstDocument();
var y=1;
while (doc!=null && y<maxDocs){                 
tmpDoc=vView.getNextDocument(doc);
rowInd=new java.lang.Integer(y);
row=sheet.createRow(rowInd);
for (x=0;x<aIteNam.length;x++){                         
cellInd=new java.lang.Integer(x);                       
cell=row.createCell(cellInd);
sIteNam=aIteNam[cellInd];
if (doc.hasItem(sIteNam)){
cell.setCellValue(doc.getItemValueString(sIteNam));
}else{
cell.setCellValue('');
}
}

bRetVal=true;       
doc.recycle();
doc=tmpDoc;
y=y+1;
}               
for (x=0;x<aIteNam.length;x++){
cellInd=new java.lang.Integer(x);
sheet.autoSizeColumn(cellInd);          
}
wb.write(fileOut);  
}
}       
fileOut.close();
if (y>0){
doc=database.createDocument();
doc.replaceItemValue('Form','ExcelExport');
doc.replaceItemValue('From',@Name('[Abbreviate]',@UserName()));
doc.replaceItemValue('Subject',logEve+' Export');
doc.replaceItemValue('Records',y);      
doc.replaceItemValue('categories',logEve);
var rtitem:NotesRichTextItem = doc.createRichTextItem('Body');
rtitem.embedObject(NotesEmbeddedObject.EMBED_ATTACHMENT, fileName,fileName, null);    doc.replaceItemValue('fileSize',doc.getFirstItem('Body').getEmbeddedObjects()[0].getFileSize()/1000);
doc.save();
}
delFile(fileName);
}catch(e){
fileOut.close();
delFile(fileName);
bRetVal=false;      
sUzenet+=' hiba: '+e;
msgVScope(sUzenet);
}finally{       
return bRetVal;
}
}
Jozsef Lehocz
  • 330
  • 3
  • 21
  • Are you recycling objects as you process them? If not, this might explain why the server crashes. – Per Henrik Lausten Sep 28 '12 at 07:32
  • I uses recycle, but somithing can be wrong. I upload the code, just a few minutes. – Jozsef Lehocz Sep 28 '12 at 07:49
  • Can you show us the messages the server returns just before it crashes? Maybe you should also do a check on the memory that is being consumed by the HTTP task. – jjtbsomhorst Sep 28 '12 at 08:38
  • 1
    Don't use Apache POI in XPages! Use a Agent instead for generating bigger Excel sheets. I had have a lot of fun with server crashes, until I used an agent instead. The same code in a Java agent worked perfectly. – Sven Hasselbach Sep 28 '12 at 08:53
  • Instead of using an agent directly.. maybe a set of managed beans (factory pattern??) would also solve the issues. But without the memory usage of the http task, the message of the server just before it crashed its just a wild guess... – jjtbsomhorst Sep 28 '12 at 09:00
  • Get @Name('[Abbreviate]',@UserName()) out of loop. Just a thought - any Notes object should be recycled, and NotesName is one of the silent killers (with NotesDate, for example) - it recycles itself with session only, so NotesName and loop may be the reason. – Frantisek Kossuth Sep 28 '12 at 13:31
  • Argh, I had to copy your source to designer, didn't see that @Username is at the end... So it seems - go after Sven's advice. – Frantisek Kossuth Sep 28 '12 at 13:41

1 Answers1

1

As suggested in the comments:

  • Move all the code to a managed bean. I suggest you pass the profileDocument, the database, the session and an OutputStream to the function that renders the spreadsheet. This way you can use the class from a command line, an agent or pass back the Sheet directly in the browser response (XAgent style), The command line is actually invaluable for debugging.
  • Watch your variables and recycle everything properly (check for the shred(base ... moreturi) function on OpenNTF.
  • Unless you actually want to pass back the spreadsheet in the browser.... use an Agent (with the bean approach you can change your mind anytime)

Let us know how it goes

stwissel
  • 20,110
  • 6
  • 54
  • 101