Category Archives: java/jsp

Apache POI – Formatting of ‘General’ data extracted from Excel

I just fixed an issue that has to do with pulling data out of excel using the Apache POI framework into Java and having the data’s format change along the way. It is not a particularly difficult issue, but when researching it I found that there were a couple of other posts on it that didn’t really solve it, or solved it in a round-about way. So my solution is here:

The issue I ran into was that there were cells in Excel that were meant to be strings but had the default Excel cell format of “General.” If a normal string was in one of the cells in this column, then it would be correctly read into the Java code as a string. However, if a value was like ’123123123,’ it would be read in as an HSSFCell.CELL_TYPE_NUMERIC and would convert to a string in scientific notation. These needed to be read in exactly as there were seen in the spreadsheet.

Other solutions I saw included additional reformatting once the data was inside Java, but that seemed too dirty to me. I would have to make assumptions on what the user put into the spreadsheet. There should be a way to pull the data from the Excel file and receive a string that looks exactly like what is shown in Excel.

A quick solution to this would be to change the formatting of the cell in Excel to ‘Text’ so that the fields were always designated correctly. But, in my case, I cannot always control what is in the Excel file. A user might change the formatting to whatever they want, but my system expects a String. So this was not a solution.

What I can control is the java code, of course. So after some digging I found two keys that would help me solve this issue.  If I am trying to convert a HSSFCell.CELL_TYPE_NUMERIC value to a string, I need to use these two things:

  1. I can get the format of the cell that is being used by Excel with cell.getCellStyle( ).getDataFormat( ). This returns a short.  The Excel format for “General” is 0 and the format for Scientific Notation is 11 (I found this through debugging, but a table exists at least here in the ‘Value’ column).  So I know if the user really wanted scientific notation, or if the value was just formatted like that when passed to me.
  2. Once I know the user did not mean for the value to be in scientific notation, I can check the string for the existence of an “E,” and if it exists I will pull the string from Excel using Excel’s formatting instead of my own.  I do this with value = new HSSFDataFormatter().formatCellValue( cell ).  This converts the cell to a string using the Excel cell format pattern instead of trying to reformat the numeric value in the HSSFCell in Java.
I could simply use this formatCellValue all of the time instead of even checking I suppose, but i’m doing some other custom formatting as well so this is treated as a special case.
So this is the solution I ended up using.  The key is to check the format that is set up in Excel, and then use it directly if it produces a cleaner result in the Java code.  Hope that helps someone.

I’m not maintaining this blog here so commenting is closed… Please see this same post on my new site to ask questions: http://mpickell.com/blog/2011/06/03/apache-poi-formatting-of-data-extracted-from-excel/


jQuery plugin: working scrollbar on left side of div

I’ve been building JSF views and creating custom components for a while and one issue we worked on was making scrollable tables, where the header and footer stay stationary and the data scrolls.

I’m still cleaning this up, but the general idea is that I wrapped the table in DIVs, which are set up to scroll vertically or horizontally based on the dynamic content of the table. This seems to be the only solution that is cross-browser, and it uses jQuery to dissect the table and move the header and footer so that the scrolling works correctly. Once I finalize that, i’ll post it.

But one thing was missing: the content of the tables sometimes pushes the table off the right side of the view, and the horizontal scrollbar appears. At this point, the vertical scrollbar is way off to the right and pretty much makes the table useless. So I wanted to move the vertical scrollbar to the left side, and I had trouble finding a cross-browser solution … until now.

I just found a jQuery plugin written by Brian Reavis at his website: 3rd Route. I tested it out and it worked immediately. The only thing i had to do to it was swap out all of the ‘$’ for ‘jQuery’ because of my use of the jQuery.noConflict() flag. I originally found it via stackoverflow, but the version on the website looks like it was cleaned up to enable chaining.

I tried other things, like multiple versions of style="direction:trl", but these felt messy when i read about them, and did not work when I implemented them.

Thanks Brian!

Update: I ended up doing a lot of changes because of some issues i found:

  • For very large tables, this code was really slow in IE.  I narrowed it down to the jQuery ‘append’ function and refactored the plugin to not use it at all
  • The original copied all components into the poser div.  I removed all of that and the poser div is basically now just the scrollbar.  Something major was required for using the plugin with JSF, because it was causing duplicate ID issues.  I decided to get rid of all of the extra components instead of just renaming the IDs.
  • I created a second version that has the scrollbar on both sides
  • I added a unique identifier class so that multiple instances could be on the same page.
  • I changed how the scroll updating worked, but the old way wasn’t broken so it wasn’t really an improvement.

So, based on Brian’s original design and implementation, here is what I am using now:

/**
 * jQuery plugin to add a scrollbar to the left side of a div.  It does this by
 * creating a false div on the left side of the table, and then having that div's scroll
 * position set on the original div every scroll event.
 * 
 * @link edited from http://thirdroute.com/css-js-left-vertical-scrollbars/, but heavily changed from the original version.
 */
jQuery.fn.leftAndRightScrollBar = function(){
	var items = jQuery(this);
	
	var randomString = function() {
		var chars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXTZabcdefghiklmnopqrstuvwxyz";
		var string_length = 8;
		var randomstring = '';
		for (var i=0; i<string_length; i++) {
			var rnum = Math.floor(Math.random() * chars.length);
			randomstring += chars.substring(rnum,rnum+1);
		}
		return randomstring;
	}

	jQuery(function(){
		items.each(function(){
			// create unique classes for targeting the poser div
			var poserTargetingClass = randomString();
			
			var e = jQuery(this);
			var contentHeight = e.children('table:first').height();
			var content = e.html();
			var ie = !jQuery.support.boxModel;
			var w = e[ie?'innerWidth':'width'](), h = e[ie?'innerHeight':'height']();
			
			//calculate paddings
			var pad = {};
			jQuery(['top', 'right', 'bottom', 'left']).each(function(i, side){
				pad[side] = parseInt(e.css('padding-' + side).replace('px',''));
			});
			//detect scrollbar width
			var xfill = jQuery('<div class="xFill">').css({margin:0, padding:0, height:'1px'});
			e.append(xfill);
			var contentWidth = xfill.width();
			var scrollerWidth = e.innerWidth() - contentWidth - pad.left - pad.right;
			e.css('padding', '0');
			e.children('.xFill').remove();
			
			var poserHeight = h - pad.top - pad.bottom;
			var poser = jQuery('<div class="leftAndRightScrollPoser '+poserTargetingClass+'">')
				// create a div that forces height without copying the content to do it.
				.html('<div style="visibility:hidden;height:'+contentHeight+'px">.</div>')
				.css('overflow','auto')
				.height(poserHeight+(ie?pad.top+pad.bottom:0))
				.width(scrollerWidth-(ie?0:pad.left*2)) // only as wide as the scrollbar.
			;
			
			e
				.css({
					width: w+(ie?0:scrollerWidth)-(ie?0:pad.right+pad.left),
					height: h-(ie?0:pad.bottom+pad.top),
					marginTop: -poserHeight-pad.top*2,
					marginLeft: scrollerWidth
				})
				.css('overflow-y', 'auto')
				.css('overflow-x', 'hidden')
			;
				
			jQuery(['top', 'right', 'bottom', 'left']).each(function(i, side){
				 poser.css('padding-'+side, pad[side]);
				 e.css('padding-'+side, pad[side]);
			});
			poser.insertBefore(e);
			
			var hRatio = (e.innerHeight()+pad.bottom) / poser.innerHeight();
			// Set up scrolling update events
			jQuery("." + poserTargetingClass).scroll(function(){e.scrollTop(poser.scrollTop()*hRatio)});
			e.scroll(function(){poser.scrollTop(e.scrollTop()*hRatio)});
		});
	});
	return items;
};


/**
 * jQuery plugin to move the scrollbar to the left side of a div -- no right scrollbar
 * @link http://thirdroute.com/css-js-left-vertical-scrollbars/, but heavily changed from the original version.
 */
jQuery.fn.leftScrollbar = function(){
	var items = jQuery(this);
	
	var randomString = function() {
		var chars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXTZabcdefghiklmnopqrstuvwxyz";
		var string_length = 8;
		var randomstring = '';
		for (var i=0; i<string_length; i++) {
			var rnum = Math.floor(Math.random() * chars.length);
			randomstring += chars.substring(rnum,rnum+1);
		}
		return randomstring;
	}
	
	jQuery(function(){
		items.each(function(){
			// create unique classes for targeting the poser div
			var poserTargetingClass = randomString();
			
			var e = jQuery(this);
			var content = e.html();
			var contentHeight = e.children('table:first').height();
			var ie = !jQuery.support.boxModel;
			var w = e[ie?'innerWidth':'width'](), h = e[ie?'innerHeight':'height']();
			//calculate paddings
			var pad = {};
			jQuery(['top', 'right', 'bottom', 'left']).each(function(i, side){
				pad[side] = parseInt(e.css('padding-' + side).replace('px',''));
			});
			//detect scrollbar width
			var xfill = jQuery('<div>').css({margin:0, padding:0, height:'1px'});
			e.append(xfill);
			var contentWidth = xfill.width();
			var scrollerWidth = e.innerWidth() - contentWidth - pad.left - pad.right;
			e.css('padding', '0');
			e.children('.xFill').remove();
			
			var poserHeight = h - pad.top - pad.bottom;
			var poser = jQuery('<div class="leftScrollPoser '+poserTargetingClass+'">')
				.html('<div style="visibility:hidden;height:'+contentHeight+'px">.</div>')
				.css('overflow','auto')
				.height(poserHeight+(ie?pad.top+pad.bottom:0))
				.width(scrollerWidth-(ie?0:pad.left*2)) // only as wide as the scrollbar
			;
			
			e
				.css({
					width: w/*-scrollerWidth*/-(ie?0:pad.right+pad.left),
					height: h-(ie?0:pad.bottom+pad.top),
					overflow: 'hidden',
					marginTop: -poserHeight-pad.top*2,
					marginLeft: scrollerWidth
				});
				
			jQuery(['top', 'right', 'bottom', 'left']).each(function(i, side){
				 poser.css('padding-'+side, pad[side]);
				 e.css('padding-'+side, pad[side]);
			});
			poser.insertBefore(e);
			
			var hRatio = (e.innerHeight()+pad.bottom) / poser.innerHeight();
			// Set up scrolling update events
			jQuery("." + poserTargetingClass).scroll(function(){e.scrollTop(poser.scrollTop()*hRatio)});
			e.scroll(function(){poser.scrollTop(e.scrollTop()*hRatio)}); // so mouse wheel scrolls table
		});
	});
	return items;
};


Intelligent Mail barcode – iText and barcode4j

I use a java library called iText to create PDFs and do other PDF related tasks when I am building web applications, and i embed a lot of barcodes for various purposes. iText handles most of these very well, but I recently needed to replace the old USPS barcode (postnet) with the new barcode (intelligent mail). iText doesn’t support it, but with some helpful direction I implemented it using another library called barcode4j.  Here’s the method I wrote that you can pretty much copy and use directly:

/**
* Create a USPS Intelligent Barcode using Barcode4J and add it to iText document.
* @param code barcode value
* @param cb overcontent from stamper
* @param fieldPositions stamper.getAcroFields().getFieldPositions(“…”) for the placeholder field in PDF template
*/
public static void createUspsIntelligentBarcode(String code, PdfContentByte cb, float[] fieldPositions) {

if ((code.length() != 20) && (code.length() != 25) && (code.length() != 29) && (code.length() != 31)) {
throw new RuntimeException(“UspsIntelligentBarcode: code length of ” + code.length() + ” is invalid.”);
}

//Note: fieldPositions data = [page, llx, lly, urx, ury]
float height = (fieldPositions[4]-fieldPositions[2]);
float width = (fieldPositions[3]-fieldPositions[1]);

PdfTemplate tp = cb.createTemplate(width, height);

// Create the graphics and canvas objects that will contain the barcode.
Graphics2D g2 = tp.createGraphics(width, height);
g2.setRenderingHint(RenderingHints.KEY_FRACTIONALMETRICS, RenderingHints.VALUE_FRACTIONALMETRICS_ON);
g2.scale(2.5, 2.835); // (1mm == 2.835 points) + tweaking on x to make bar width correct
Java2DCanvasProvider provider = new Java2DCanvasProvider(g2, 0);

// Create Barcode4J barcode on canvas
USPSIntelligentMailBean barcode = new USPSIntelligentMailBean();
// use 70% of converted values.  Measurements on printouts needed to be tweaked.
barcode.setAscenderHeight(UnitConv.in2mm(0.1f) * 0.7f);  // height of ascender/descender per USPS spec
barcode.setTrackHeight(UnitConv.in2mm(0.05f)* 0.7f);      // height of track bar per USPS spec (smallest bar of barcode)
barcode.setBarHeight(UnitConv.in2mm(0.145f)* 0.7f);
barcode.setIntercharGapWidth(UnitConv.in2mm(0.04f)* 0.7f * 1.13f); // adjust again for scaling
barcode.generateBarcode(provider, code);

g2.dispose();

// adjust x/y to perfect location for envelope window
float inch = 72f; // itext manual pg 33
float xCoordinate = fieldPositions[1] – ((1f/8f) * inch);
float yCoordinate = fieldPositions[2] – ((3f/32f) * inch);

cb.addTemplate(tp, xCoordinate, yCoordinate);
}

This lets you put the intelligent mail barcode (or any barcode in barcode4j) directly into iText.  The key here is that the PdfTemplate in iText has a method that creates a generic Java Graphics2D object that is accessible for drawing by anything.  This generic object is exactly what is needed to be passed into the barcode4j canvas provider, and then any barcode4j barcode is output to the canvas.  A couple tweaks to position it however it is needed, and that is it.  It was a lot simpler than I expected when I realized I couldn’t just use iText for the whole thing.  Gotta love open source!


jsp tomahawk HtmlInputCalendar

I had an ongoing issue recently with pop-up javascript calendars in the tomahawk jsp library.  Hopefully this will save some trouble for someone.  The problems I was having were:

  1. sometimes the calendar pop-up was not selectable, and
  2. when it was selectable, the pop-up would show up at a distorted location on the page and the tables would be messed up.

Being new to debugging this, my first assumption was that there would be protections built into this packaged code so that my CSS and whatever else would not affect it.  So, to keep this post short and to the point, here is how I fixed it:

  1. An eventual look at the W3C HTML 4.1 Reference showed that this element was difficult to put directly inside a label, inside a fieldset.  The problem is that the label element only supports one input inside, so the additional calendar pop-up button is unusable and simply ignores any click on it and instead puts focus on the input element.  I ended up fixing this one by simulating a label element using a DIV and CSS.  (Note: at B6 Systems, we have developed an in-house jsp framework that we call PureFaces that allows writing JSP pages completely in Java code.  So I wrote my own components for the fieldsets and labels.  It is intended to be open source eventually and will be on the B6 website)
  2. The second problem took a little longer, and eventually I fixed it without really knowing why what I did worked.  I used a lot of the same techniques for debugging CSS that are shown in many places.  Eventually I found that I had a position: relative; style on my content wrapper that was totally throwing off the built-in CSS for the pop-up.  The reason I don’t know why that fixed it was that I could not find anything in the built-in CSS that seemed to care about position.  But, results speak loudly.  I was able remove and work around that style.  The second part of this one (the bad formatting of the pop-up) was related to having the table element way too globally defined in the CSS.  Adding more specific selectors quickly solved that one.

Follow

Get every new post delivered to your Inbox.