StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExport From Lotus To MS EXCEL
text
Body
copied!<p>I have to export large amount of data (>100000 documents) from <a href="/questions/tagged/lotus" class="post-tag" title="show questions tagged 'lotus'" rel="tag">lotus</a> to <a href="/questions/tagged/excel" class="post-tag" title="show questions tagged 'excel'" rel="tag">excel</a>. I use java <strong><a href="http://poi.apache.org/spreadsheet/index.html" rel="nofollow">apache poi</a></strong> and <a href="/questions/tagged/ssjs" class="post-tag" title="show questions tagged 'ssjs'" rel="tag">ssjs</a> for this function, however the server crashes after 4-5000 douments.<br /> <a href="/questions/tagged/excel" class="post-tag" title="show questions tagged 'excel'" rel="tag">excel</a> can not be installed on the <a href="/questions/tagged/lotus-domino" class="post-tag" title="show questions tagged 'lotus-domino'" rel="tag">lotus-domino</a> server.<br /><a href="/questions/tagged/csv" class="post-tag" title="show questions tagged 'csv'" rel="tag">csv</a> format is not an option.<br /> Creating more files with predefined limited number of records is not an option.<br /> What can be the proper method/technology for exporting every field in all documents in a view? <hr> var maxDocs:Integer=6001;</p> <pre><code>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; } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload