Posts Tagged With: Ariwan Susey

Configurable notification agent in #OldNotes

Shockingly, when I arrived at my current company, they had basically NO scheduled agents at all. Apparently, someone had decided long ago that scheduled agents were dangerous, that they would overwhelm and crash the servers. So, whenever anything was done, it was done manually. This even extended to user notifications. That is, if I submitted a document for approval, there was some formula language that would populate a new notification message in the client and the user would fill in any extra details before clicking send. I was shocked. As I’ve modified designs, I’ve been adding background notifications and also scheduled agents. Our main project management database, which our field offices use copies of to manage their projects, hasn’t been mine to modify, since it’s already working and there is a team that customizes the design for each field office.

As we’ve been delving further into XPages and as I’ve been spreading the good word about scheduled agents and notifications, we’re now finally putting them into those project management databases. One hurdle though. Our admin team has, quite rightly, limited who can sign agents that will run on production servers.

Concept

Now, I’ve designed dozens or even hundreds of notification and reminder agents in many databases over the decades, but I always designed them from scratch, customizing it to the particular database and the particular recipients. I’d created a basic one and Ariwan Susey, who’s really coming up to speed on LotusScript and XPages, modified it for use in that project management database. This was nice, and Virginia Tauss had started creating copies of it, customized for each notice type. However, every time someone made a change to the half-dozen agents, I had to sign them. Since they were customized for their particular database and the particular recipients, this meant that eventually, I might spend all day signing agents instead of writing code.

Since the agents were almost the same, except for what view they used and who received the message, I realized that if I created a basic agent, they could use configuration documents to customize as many notices as they wanted and I’d never have to sign that configurable agent again!

Configuration Choices

There were a few basic things I knew would be different between each notification: the view, the recipients, the subject, the server to run on and the time to run. After creating some tests, I also realized that I wanted to emulate the scheduling choices of agents themselves and allow the user to select weekly or monthly notifications instead of just daily. I also remembered that sometimes, they would want to mark the document after they sent the notice, so I made that a configuration choice as well. Based on my recent experience in my Excel series (part 1, part 2, and the sample database) and with full-text queries, I realized we could use those full-text queries in these notifications as well.

So, here’s my form:

AutoNotify Configuration

Since I’ve been fiddling with DXL editing of forms lately, let me include the DXL for that third row for your review. The right cell contains a table for displaying the weekday or day of the month choices, with the hide-whens appropriately.

<tablerow>
	<tablecell><par def='4'>Day(s) to run:</par></tablecell>
	<tablecell>
		<par def='5'>
			<field borderstyle='none' lookupeachchar='false' lookupaddressonrefresh='false'
			type='keyword' kind='editable' name='frequency'>
				<keywords helperbutton='false' recalconchange='true' columns='3' ui='radiobutton'>
					<textlist><text>Daily</text><text>Weekly</text><text>Monthly</text></textlist>
				</keywords>
			</field>
		</par>
		<table leftmargin='0' widthtype='fixedleft' refwidth='2.5000in'>
			<tablecolumn width='1in'/><tablecolumn width='1.5000in'/>
			<tablerow>
				<tablecell valign='center' borderwidth='0px'>
					<pardef id='6' spacebefore='1.5' keepwithnext='true' keeptogether='true'>
						<code event='hidewhen'><formula>frequency != "Weekly"</formula></code>
					</pardef>
					<par def='6'>Day of week: </par>
				</tablecell>
				<tablecell valign='center' borderwidth='0px'>
					<pardef id='7' spacebefore='1.5' keepwithnext='true' keeptogether='true'>
						<code event='hidewhen'><formula>frequency != "Weekly"</formula></code>
					</pardef>
					<par def='7'>
						<field usenotesstyle='false' height='0.2500in' width='1in' multiline='true'
						borderstyle='none' lookupeachchar='false' lookupaddressonrefresh='false'
						type='keyword' kind='editable' name='weekdayToRun'>
							<keywords helperbutton='false' columns='1' ui='combobox'>
								<textlist>
									<text>Sunday|1</text>
									<text>Monday|2</text>
									<text>Tuesday|3</text>
									<text>Wednesday|4</text>
									<text>Thursday|5</text>
									<text>Friday|6</text>
									<text>Saturday|7</text>
								</textlist>
							</keywords>
						</field>
					</par>
				</tablecell>
			</tablerow>
			<tablerow>
				<tablecell valign='center' borderwidth='0px'>
					<pardef id='8' keepwithnext='true' keeptogether='true'>
						<code event='hidewhen'><formula>frequency != "Monthly"</formula></code>
					</pardef>
					<par def='8'>Day of month:</par>
				</tablecell>
				<tablecell valign='center' borderwidth='0px'>
					<pardef id='9' keepwithnext='true' keeptogether='true'>
						<code event='hidewhen'><formula>frequency != "Monthly"</formula></code>
					</pardef>
					<par def='9'>
						<field type='number' kind='editable' name='monthdayToRun'>
							<numberformat format='general' digits='2' punctuated='false' parens='false' percent='false'
							bytes='false'/>
							<code event='defaultvalue'><formula>1</formula></code>
							<code event='inputvalidation'><formula>@If ( frequency != "Monthly"; @Success; @ThisValue > 1 
								& @ThisValue < 29; @Success; @Failure ( "Must be in the first 28 days of the month"))</formula>
							</code>
						</field>
					</par>
				</tablecell>
			</tablerow>
		</table>
		<pardef id='10' keepwithnext='true' keeptogether='true'>
			<code event='hidewhen'><formula>frequency != "Monthly"</formula></code>
		</pardef>
		<par def='10'><run><font size='1pt'/></run></par>
	</tablecell>
</tablerow>

As I use the source view more in XPages, I get more and more comfortable with just editing code, and checking appearances occasionally. While I have only done a little of that in forms, I have used it several times in views. When I created this form, my initial design of it was done by creating a single in the normal designer form, then saving it, and re-opening it in DXL. Then I added several fields to a form with cut-and-paste for field names. Using the properties boxes just seemed like it would take so much longer – after all, I had the field names in my notepad already.

The Agent

Our agent is set to run hourly, on every server. If there are no autoNotify documents, it doesn’t do anything, but if there are, it checks each one for whether it runs on that server, on that day and at that hour.

Sub Initialize
Dim session As New NotesSession
' thisdb is declared in my utilities library, so not declared here '
Dim autoNotifyView As NotesView
Dim autoNotifyDoc As NotesDocument
Dim serverToRunOn As Variant
Dim hourToRun As Variant
Dim frequency As Variant
Dim weekdayToRun As Variant
Dim monthdayToRun As Variant
Dim noticeName As Variant
Dim hourNow As Integer
Dim weekdayToday As Integer
Dim monthdayToday As Integer
Dim reason As String

On Error GoTo errorhandler

Set thisdb = session.Currentdatabase
Call StartAgentLogging ( session )

Dim serverName As New NotesName ( thisdb.Server )

' get view of autonotify documents '
Set autoNotifyView = thisdb.Getview("AutoNotify")
Set autoNotifyDoc = autoNotifyView.Getfirstdocument()

While Not autoNotifyDoc Is Nothing
	' check server to run on '
	serverToRunOn = autoNotifyDoc.Getitemvalue("serverToRunOn")
	If ( Ucase ( serverToRunOn (0) ) = Ucase ( serverName.Common ) ) Then
		' check frequency and day '
		frequency = autoNotifyDoc.Getitemvalue("frequency")
		weekdayToRun = autoNotifyDoc.Getitemvalue("weekdayToRun")
		If ( weekdayToRun (0) = "" ) Then
			weekdayToRun (0) = "0"
		End If
		weekdayToday = Weekday ( Today )
		monthdayToRun = autoNotifyDoc.Getitemvalue("monthdayToRun")
		monthdayToday = Day ( Today )
		If ( frequency (0) = "Daily" or ( frequency (0) = "Weekly" And CInt (weekdayToRun (0)) = weekdayToday ) Or ( frequency (0) = "Weekly" And CInt ( monthdayToRun (0) ) = monthdayToday ) ) Then
			' check hour to run '
			hourToRun = autoNotifyDoc.Getitemvalue("schedule")
			hourNow = Hour (Now)
			If ( CInt ( hourToRun (0) ) = hourNow ) Then
				noticeName = autoNotifyDoc.Getitemvalue("NoticeName")
				If ( sendNotices ( autoNotifyDoc ) ) Then
					Call agentLog.LogAction ( noticeName (0) & " sent")
				Else
					Call agentLog.LogAction ( noticeName (0) & " FAILED")
				End If
			End If
		End If
	End If

	Set autoNotifyDoc = autoNotifyView.Getnextdocument(autoNotifyDoc)
Wend
Call agentLog.LogAction ( "Completed" )

exiting:
	Exit Sub
errorhandler:' report all errors in a messagebox '
	reason = "Error #" & CStr (Err) & " (" & Error & ") on line " & CStr (Erl)
	MessageBox reason, 16, "Error"
	Call agentLog.LogAction ( reason )
	Resume exiting ' transfers control to the exiting label

End Sub

The actual notification builds off the values from the configuration document. At MWLUG, speakers recommended making sure to use functions instead of subroutines, partly because functions return a value and partly for forward compatible with other programming languages. So, my sendNotices function is a boolean, indicating success or failure.

The simplest, yet most powerful part of the script is the application of the querystring. By using that, I could create dozens of notifications from a single view, saving myself disk space by avoiding unnecessary view indices.

Ariwan’s great contribution to the basic agent that made it so useful in this configurable design was the use of columnvalues. The agent simply spits out the contents of the view, populating the message with the details of the document regardless of which fields are used. I’d never thought of doing that!

You’ll notice that in the loop, we get a handle to the nextdoc before processing. If the document would be removed from the view by marking one of the fields “Yes” and saving the document, we need to already have a handle to the next document. If we don’t do that, the view won’t be able to find the next document by referring to the current document, as it has no position in the view any more.

Now, since I want each notification to be processed even if I encounter some errors, I added error-handling in the function as well. If I had not, an error would bubble up to the Initialize routine and stop my agent. This way, it only stops that particular notification, but continues to the next one.

Function sendNotices ( autoNotifyDoc As NotesDocument ) As Boolean
Dim viewName As Variant
Dim recipientGroup As Variant
Dim subjectLine As Variant
Dim introText As Variant
Dim queryString As Variant
Dim flagField As Variant

Dim workingView As NotesView
Dim workingCollection As NotesDocumentCollection
Dim doc As NotesDocument
Dim nextdoc As NotesDocument
Dim memo As NotesDocument
Dim body As NotesRichTextItem
Dim reason As String
Dim count As Integer

sendNotices = false

' get viewName '
viewName = autoNotifyDoc.Getitemvalue( "viewName" )
Set workingView = thisdb.Getview ( viewName (0) )
' apply query string, if there is one '
queryString = autoNotifyDoc.Getitemvalue( "queryString" )
If ( queryString (0)<> "" ) Then
	Call workingView.Ftsearch(queryString(0), 0)
End If

Set doc = workingView.Getfirstdocument()

count = 0

Set memo = thisdb.Createdocument()
Set body = memo.Createrichtextitem(&quot;Body&quot;)
memo.Principal = thisdb.Title

' copy the introductory text from the autoNotify document into the email '
introText = autoNotifyDoc.Getitemvalue( "introText" )
Call body.Appendtext ( introText(0) )
Call body.Addnewline(2)

While Not doc Is Nothing
	Set nextdoc = workingView.Getnextdocument(doc)
	count = count + 1
	Call body.Appendtext( CStr ( count ) & "." )
	Call body.Addtab(1)
	ForAll thing In doc.Columnvalues
		If ( IsArray ( thing ) ) Then
			Call body.Appendtext( Implode (thing, ", " ) )
		Else
			Call body.Appendtext( thing )    
		End If
		Call body.Addtab(1)
	End ForAll
	Call body.Appenddoclink(doc, "Open the doc", "Link")
	Call body.Addnewline(1)

	' if field to mark, then modify field and save doc '
	flagField = autoNotifyDoc.Getitemvalue( "flagField" )
	If ( Trim ( flagField (0) ) <> "" ) Then
		Call agentLog.LogAction ( flagField (0) & " field #" & CStr ( count ) )
		Call doc.ReplaceItemValue ( flagField (0), "Yes" )
		Call doc.Save ( True, False )
	End If

	Set doc = nextdoc

Wend

subjectLine = autoNotifyDoc.Getitemvalue( "subjectLine" )
memo.Subject = CStr (count) & " " & subjectLine (0)
recipientGroup = autoNotifyDoc.Getitemvalue("recipientGroup" )
Call memo.Send(False, DetermineKeyword ( recipientGroup(0)) )

sendNotices = True

exiting:
	Exit Function
errorhandler:' report all errors in a messagebox '
	reason = "Error #" & CStr (Err) & " (" & Error & ") on line " & CStr (Erl)
	MessageBox reason, 16, "Error"
	Call agentLog.LogAction ( reason )
	Resume exiting ' transfers control to the exiting label '

End Function

It’s not quite perfect because if the server is down, it won’t run the notification later. I might take that into account in a future version, since many our project servers are in locations where power may not always be 24×7. Similarly, if someone puts too many notifications to run at the same time, the agent could time out, failing to run all of them.

Hopefully, this exercise proves useful to someone else. I can’t believe I spent more than a decade constantly re-writing the same code when I could have saved myself considerable time by just creating a customizable, reusable piece of code back in the day. Live and learn!

Update:

ThingFailsTurns out there was a bug in the code. The simple loop through the columnValues didn’t take into account multi-value fields. So, when the agent ran on a view with a document that had multiple values, it was trying to print a variant as text. So, I added a simple check for IsArray and imploded the multi-value field to build a comma-delimited string. That avoids the type mismatch that our script was throwing when it found those multi-value fields as shown at right in the debugger.

Categories: Old Notes, Utilities | Tags: , , , , | 1 Comment

Java still not refreshing correctly in #XPages 8.5.3 UP 1

You know, I had started on a post on the great power of comparing design elements, but all I’d written so far was:

With the move to an Eclipse-based Domino Designer Environment, there was at least one significant improvement – the native ability to compare design elements.

I was a big fan of TeamStudio‘s Delta, since I often needed to compare versions of my designs either to document the changes I’d made or, in rare circumstances, do a little troubleshooting to see where I’d accidentally screwed up my designs. It was a great tool, with a lot of smarts about how to report on the designs and it even allowed you to compare documents. They have, of course, updated Delta to provide more capability than is native in Notes – allowing graphical display of design differences rather than just code variances. That said, I don’t have a current license, so I’m using the native tools.

In one of my hotter posts, I’d noted that Java is not behaving well in my environment. The basic behavior is that I’d make changes to my template, do a build and then, when I refreshed the design on the server, things would not refresh properly in Java. In the last few days, the behavior has become very consistent. For some reason, it plagues Mark Leusink‘s DebugToolbar, which is an excellent tool that works fine when my environment behaves.

Basically, the end-user experience of the problem was that, every once in a while, without code changes to the DebugToolbar, I’d start getting Error 500’s that it could not find that class. So, I started to get in the habit of checking in the WebContent/WEB-INF/classes folders using the Navigator view in my designer client to see if the classes were all there.

When builds worked, it would create the DebugToolbar and Message classes, plus the three private classes within DebugToolbar (FieldComparable, FileInfo and MethodComparable). When things didn’t work, sometimes it wouldn’t create the two main classes. Refreshing the design on the server again would never repair it. It would often create 2 or 3 additional copies of each of the private classes, appending what looks like a UNID to the class name. Those tend to have file sizes like -1kb.

Bad Class List

This started in August, as I was making changes to some other Java files. I never changed DebugToolbar, but it would often get corrupted. It settled down as my Java changes ended. I went on vacation for two weeks (Avignon!) and returned to a stable environment. Of course, no one had touched the Java while I was gone and I saw no need until a few weeks ago to make any changes myself.

Now, I had also updated to v3.01 of the DebugToolbar and moved all my Java files into Code/Java from the WebContent/WEB-INF/src folder so that access to them was simpler. However, having been back and forth between versions and back and forth with where the code was stored, I think I can eliminate those as independent problem sources.

Nonetheless, I can consistently see the extra classes and error 500s every time I refresh the design. Deleting the bad classes doesn’t solve anything. Replacing all the class files with the ones from the template also doesn’t work.

If I build the database on the server, the problem goes away.

I’ve been getting frustrated. This might work for me if I’m the only one who makes changes, since the Bethesda server is always a few short hops from me. However, as I may have mentioned before, I work for a very global-oriented company and sometimes our developers are out at project sites where connectivity is slow on good days. Quite frankly, doing a build on the Bethesda server while sitting in Jalalabad is never going to finish in an acceptable time frame. One can build a local template quickly and refresh design far faster.

Fortunately, today I got a new clue.dojo complaint

I started to wonder about corrupt files or problems with the server. So, I created a completely blank file and applied my template (which showed the 5 class files, named normally) to that file. The class files get all corrupted. So, I created a blank file and built as a template from my template. It got corrupt files and also didn’t work as template after building (the same result in the class files). Then, I decided to copy over design elements ad paste them into a blank file. I did a build and got some errors because I’d forgotten to copy over all the libraries and other files hidden under WEB-INF. So, I decided to compare the two databases so I could see what I needed to copy still. Then, I looked at the Java files produced by the XPage builds, just it showed up in the differences between the files and I was shocked. The design on the one I was ‘creating from scratch’ showed all kinds of old versions.

In each of the ‘old’ builds, the Java file created for the XPage would have super(“8.5.3”); but, as shown above, my cut-and-paste built ones would have various complaints, naming other versions and usually with a comment on why. The different line displayed would usually be one of the following:

super("3.0");

super("8.5.2"); // version of xp:view dojoForm

super("8.5.2"); // version of xp:eventHandler disableValidators

super("8.5.1"); // version of xp:eventHandler script

I’d never have noticed if I wasn’t using Compare To Each Other. Now, I need to reinstall Notes and see if the problem goes away or continues. I had Ariwan try and it only worked once for him – and I had to rebuild it on the server – so my hopes are not real high. Since Eric Tomenga has submitted a PMR on the issue, we’re hoping some IBM minds add to my few readers in trying to puzzle out why this is happening.

 

Categories: IDE, Java, Utilities, Xpages | Tags: , , , , , , , , , | 3 Comments

Exporting from #XPages to Excel without Excel, Part 2

Yesterday, I posted about exporting to Excel without having Excel on the destination device, which I found particularly useful when using my Android tablet and the Chrome browser. It was a relatively long post, necessitating a two-part post.

Today, I want to spend some time showing the server-side javascript library that I’ve developed (based on the one Russ Maher presented at the AdminDev 2012 conference). So, we’re going to delve into some Apache POI, examining how we pass the configuration information in and which lines of code implement that. I expect this examination will be useful for me as well, since it will open my eyes wider to the potential within the code. Russ assures me that while he dabbles in POI, Bill Buchan actually speaks POI, so there is at least one Notes genius who knows far more than both of us….

The Function Call

Before we delve into the code, lets first examine what we’re passing to the function. Each of these values is acquired either from the XPage or from the report configuration. Using these, we have a lot of flexibility to create and format our spreadsheet.

function createWorkbookStreamWithLabels(workbookName,sheetName,fieldList,viewName,colLabels,totalLabels)

workbookName: From the XPage, which defaults the value to the report name selected. It will be used as the filename for our spreadsheet.

sheetName: Also from the XPage, defaulting to ‘Report’, but editable by the user to place as the worksheet name for the single worksheet of our spreadsheet.

fieldList: From the report configuration. This is a list of the field names, in order, to be used as values in the columns. This implementation uses field values instead of column values, but it could easily be modified to use those instead.

viewName: From the report configuration. View containing the documents to be exported.

colLabels: From the report configuration. These will end up in the first row of the spreadsheet and be styled for emphasis.

totalLabels: From the report configuration. These are used to identify which columns will get totaled. This was clearer and more reliable than using column numbers.

Creating the Stream

I actually want to start by examining the last piece of code in the library, the download of the file to the client. To me, the great strength of this whole undertaking is that I can get a spreadsheet to my device without any Excel software being loaded there. As I noted yesterday, when I used the XPage and this script library to create a spreadsheet on my tablet, the usefulness skyrocketed in the estimation of my office-mate, Ariwan. “You could download the inventory to a tablet, walk around, making notes on your tablet and then return and update the database.” Of course, I started thinking one step further and wanting to import the marked up spreadsheet right back into the database, but that code will have to wait for a while. Nonetheless, we see an immediate impact beyond just creating spreadsheets.

By using the OutputStream, we’re able to take the export that we build in the workbook, wb, and simply prompt the user to download the file as an output stream. On my tablet, this downloaded the file without prompting, which was exactly what I wanted. In a quick test, I tried to use .xlsx, but it refused to work – that might be fixed in the later releases of the POI library (I’m using 3.6 currently, though 3.9 has already been released).

//Create the filename for the spreadsheet
var fileName = workbookName+".xls";

// The Faces Context global object provides access to the servlet environment via the external content
var extCont = facesContext.getExternalContext();
// The servlet's response object provides control to the response object
var pageResponse = extCont.getResponse();
//Get the output stream to stream binary data
var pageOutput = pageResponse.getOutputStream();

// Set the content type and headers
pageResponse.setContentType("application/x-ms-excel");
pageResponse.setHeader("Cache-Control", "no-cache");
pageResponse.setHeader("Content-Disposition","inline; filename=" + fileName);
//Write the output, flush the buffer and close the stream
wb.write(pageOutput);
pageOutput.flush();
pageOutput.close();

Error-handling

I found myself trying to trouble-shoot and, as always, being frustrated with XPages because I don’t know how to do that very well. I got real used to being able to step through my LotusScript, to being able to look at field values on a document using properties, to using print statements and popups to track what was going on, but I haven’t yet filled my toolkit with ways to trouble-shoot XPages. So, I borrowed Don Mottolo’s postValidationError function from his SSJS Form Validation XSnippet. Whenever I want to return an error, I just pass the control and some text to that function and it should post the message in my display errors control. I’ve only got a few of these in the library because it started working properly before I added all the potential error-trapping and who has the patience to add all that? (Yeah, I’ll add more later, since I’m sure I’ll run into bugs in production. Idiot-proof code is impossible because idiots are just too darn inventive!)

function postValidationError(control, msg) {
if ((typeof msg) != "string")
	return;
var msgObj = new javax.faces.application.FacesMessage(javax.faces.application.FacesMessage.SEVERITY_ERROR, msg, msg);
facesContext.addMessage(control.getClientId(facesContext), msgObj);
control.setValid(false);
}

Create some cell styles

As I mentioned yesterday, we deal with a lot of international date formats and, in any reporting, dates are all over the place. So, I added a date format to be applied to those cells. Similarly, as mentioned above, I want my headers to get some styling to emphasize them.

I’ve also designated my numeric format, using two digits after the decimal. Note that if the user has their machine set to use European number formatting, with periods separating thousands and a comma to show the decimal, the export won’t care – it passes the values and the user’s local settings display it according to their preferences. I’m not certain if the preferences at the server (if any) would have any effect on how you must code it, so am curious what experience others have there.

I’m sure that if we delve into the POI documentation, we can learn all kinds of ways to format the cells and I expect that once this deploys, we’ll be working on learning those. We start, however, with just these three.

//Create helper class and styles for dates
var createHelper:HSSFCreationHelper = wb.getCreationHelper();
var dateStyle:HSSFCellStyle = wb.createCellStyle();
dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

var headerStyle:HSSFCellStyle = wb.createCellStyle();
var headerFont:HSSFFont = wb.createFont();
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);

// set number formatting to use a thousands separator and two digits
// note that it will display using the user's thousands separator and decimal notation
// rather than forcing US-standard settings
var format:DataFormat = wb.createDataFormat();
style.setDataFormat(format.getFormat("#,##0.00"));

Handle various data types

The next important issue is that when passing the data through, you must make sure the Excel file understands the data type.

//depending on the type of value, use proper method to retrieve
if (valueType.endsWith("DateTime")) {
	var reportValue = (String) (itemValue);
} else if (valueType.endsWith("number")) {
	var reportValue = Number (itemValue);
} else if (valueType.endsWith("string")) {
	var reportValue = itemValue;
} else if (valueType.endsWith("Vector")) {
	var reportValue = (String) (itemValue); // multi-value field
	reportValue = reportValue.replace("[",""); // remove opening bracket
	reportValue = reportValue.replace("]",""); // remove closing bracket
} else {
	var reportValue = itemValue.toString;
}
if ( reportValue == null ) {
	reportValue = "Value not found"
}

The Whole Library

Everything else in here should be pretty self-explanatory. We simply walk through the view, almost using LotusScript, moving from document to document, getting the appropriate field values and passing them to the Excel file. When it works, it’s lovely.

function createWorkbookStreamWithLabels(workbookName,sheetName,fieldList,viewName,colLabels,totalLabels){
//import the appropriate java packages
importPackage(java.lang);
importPackage(org.apache.poi.hssf.usermodel);
importPackage(org.apache.poi.hssf.util);

var control = getComponent("comboBox1");

//Find the database connection document
var dbConView:NotesView = database.getView("TSDbConnectionLU");
var doc:NotesDocument = dbConView.getDocumentByKey("TAMIS II main db");
//Get the maindb object
if (doc != null){
	var maindb = session.getDatabase(doc.getItemValueString("DbServer"),doc.getItemValueString("DbPath"), false);
}
if ( maindb == null ) {
	postValidationError(control,"Main db Not Found");
}
var view:NotesView=maindb.getView(viewName);
if ( view == null ) {
	postValidationError(control,"View Not Found");
}

//Create placeholders for the notes document and temperary document
var doc:NotesDocument;
var ndoc:NotesDocument;

//Create a new workbook object from the poi library
var wb:HSSFWorkbook = new HSSFWorkbook();
//Create additional sheets using same sytnax and different sheet name
var sheet1:HSSFSheet = wb.createSheet(sheetName);

//Create helper class and styles for dates
var createHelper:HSSFCreationHelper = wb.getCreationHelper();
var dateStyle:HSSFCellStyle = wb.createCellStyle();
dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

var headerStyle:HSSFCellStyle = wb.createCellStyle();
var headerFont:HSSFFont = wb.createFont();
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);

//Create the Column Header Rows
var row:HSSFRow = sheet1.createRow(0);
for( i = 0 ; i <= colLabels.length-1 ; i++){
	var hCell:HSSFCell = row.createCell((java.lang.Integer)(i));
	hCell.setCellValue(colLabels[i]);
	hCell.setCellStyle(headerStyle);
}

// Style the cell with borders all around. GREY 25% - 22, GREY 40% - 55, GREY 50% - 23, GREY 80% - 63
var style:HSSFCellStyle= wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(55);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(55);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(55);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(55);

// set number formatting to use a thousands separator and two digits
// note that it will display using the user's thousands separator and decimal notation
// rather than forcing US-standard settings
var format:DataFormat = wb.createDataFormat();
style.setDataFormat(format.getFormat("#,##0.00"));

//initialize the row and cell counters
var rowCount=0;
var cellCount=0;

//Get the first document in the view and then cycle through
//the view to create the detail rows.
doc = view.getFirstDocument();

while(doc!=null){
var row:HSSFRow = sheet1.createRow(++rowCount);
for( f = 0 ; f <= fieldList.length-1 ; f++){
	var itemValue:NotesItem = doc.getColumnValues().elementAt(Number (fieldList[f]) - 1 );
	var valueType = typeof(itemValue);

	if (itemValue != null) {
		var reportValue;
		//depending on the type of value, use proper method to retrieve
		if (valueType.endsWith("DateTime")) {
			var reportValue = (String) (itemValue);
		} else if (valueType.endsWith("number")) {
			var reportValue = Number (itemValue);
		} else if (valueType.endsWith("string")) {
			var reportValue = itemValue;
		} else if (valueType.endsWith("Vector")) {
			var reportValue = (String) (itemValue); // multi-value field
			reportValue = reportValue.replace("[",""); // remove opening bracket
			reportValue = reportValue.replace("]",""); // remove closing bracket
		} else {
			var reportValue = itemValue.toString;
		}
		if ( reportValue == null ) {
			reportValue = "Value not found"
		}
		var dataCell:HSSFCell = row.createCell((java.lang.Integer)(f));
		dataCell.setCellValue(reportValue);
		dataCell.setCellStyle(style);
	}
}
doc = view.getNextDocument(doc);
}

// add a row for totaling and put the formula in where it is useful
var row:HSSFRow = sheet1.createRow(++rowCount);

// cycle through totalLabels, find match in colLabels, make a totalling cell
for( i = 0 ; i <= totalLabels.length-1 ; i++){
for( j = 0 ; j <= colLabels.length-1 ; j++){
if ( totalLabels[i] == colLabels[j] ) {
var summaryCell:HSSFCell = row.createCell((java.lang.Integer)(j));
// add the SUM formula to that cell
summaryCell.setCellFormula("SUM(INDIRECT(CONCATENATE(ADDRESS(2,COLUMN()),\":\")&ADDRESS("+rowCount+",COLUMN())))");
}
}
}

// auto size all the columns
for( j = 0 ; j <= colLabels.length-1 ; j++){
sheet1.autoSizeColumn (j);
}

//Create the filename for the spreadsheet
var fileName = workbookName+".xls";

// The Faces Context global object provides access to the servlet environment via the external content
var extCont = facesContext.getExternalContext();
// The servlet's response object provides control to the response object
var pageResponse = extCont.getResponse();
//Get the output stream to stream binary data
var pageOutput = pageResponse.getOutputStream();

// Set the content type and headers
pageResponse.setContentType("application/x-ms-excel");
pageResponse.setHeader("Cache-Control", "no-cache");
pageResponse.setHeader("Content-Disposition","inline; filename=" + fileName);
//Write the output, flush the buffer and close the stream
wb.write(pageOutput);
pageOutput.flush();
pageOutput.close();

//  Terminate the request processing lifecycle.
facesContext.responseComplete();
}
function postValidationError(control, msg) {
if ((typeof msg) != "string")
return;
var msgObj = new javax.faces.application.FacesMessage(javax.faces.application.FacesMessage.SEVERITY_ERROR, msg, msg);
facesContext.addMessage(control.getClientId(facesContext), msgObj);
control.setValid(false);
}

The sample database is available, as is the first post on this topic.

Categories: Server-Side Javascript, Xpages | Tags: , , , , , , , , , , , | 15 Comments

Blog at WordPress.com.

%d bloggers like this: