Search code examples
excelxpageslotus-dominolotusxpages-ssjs

Export From Lotus To MS EXCEL

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

  • 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